bitbake-selftest losing sqlite3 database contents!

bitbake-selftest losing sqlite3 database contents!

[Michael Opdenacker]

Hi Joshua and list.

Maybe you can give me hints on this...

I'm currently implementing bitbake-selftests for the PR server. I've got 
my first series of tests working, inserting several values into the 
database and then making queries to check that the stored values are 
correct.

However, for the second series of tests, I want to start from the 
sqlite3 database file from the first series (prtest-basic.sqlite3), and 
this time use it to start the server in read-only mode. However, the 
table is empty when I open it!

This is confirmed by making an SQL query from the command line:
$ sqlite3 prtest-basic.sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> select * from PRMAIN;
sqlite>

What could explain that the stored PR data are not flushed to the 
database file during my tests, but I have no such issue when I make an 
image with BitBake?

I also changed the prserv/db.py file to run:
self.connection.execute("PRAGMA synchronous = NORMAL;")
self.connection.execute("PRAGMA journal_mode = WAL;")

However, that didn't help.
Any clues for debugging this?
Thanks in advance
Cheers
Michael.


-- 
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com

Re: [bitbake-devel] bitbake-selftest losing sqlite3 database contents!

[Richard Purdie]

On Thu, 2024-04-18 at 21:42 +0200, Michael Opdenacker via lists.openembedded.org wrote:
> I'm currently implementing bitbake-selftests for the PR server. I've got 
> my first series of tests working, inserting several values into the 
> database and then making queries to check that the stored values are 
> correct.
> 
> However, for the second series of tests, I want to start from the 
> sqlite3 database file from the first series (prtest-basic.sqlite3), and 
> this time use it to start the server in read-only mode. However, the 
> table is empty when I open it!
> 
> This is confirmed by making an SQL query from the command line:
> $ sqlite3 prtest-basic.sqlite3
> SQLite version 3.37.2 2022-01-06 13:25:41
> Enter ".help" for usage hints.
> sqlite> select * from PRMAIN;
> sqlite>
> 
> What could explain that the stored PR data are not flushed to the 
> database file during my tests, but I have no such issue when I make an 
> image with BitBake?
> 
> I also changed the prserv/db.py file to run:
> self.connection.execute("PRAGMA synchronous = NORMAL;")
> self.connection.execute("PRAGMA journal_mode = WAL;")
> 
> However, that didn't help.
> Any clues for debugging this?

Most selftests are designed to run in isolation. There is no "run X
after Y" mechanism since you can specify a single test to run. You may
need to merge the tests if you need the same database.

Cheers,

Richard

Re: [bitbake-devel] bitbake-selftest losing sqlite3 database contents!

[Michael Opdenacker]

Hi Richard,

On 4/18/24 at 23:04, Richard Purdie wrote:
> On Thu, 2024-04-18 at 21:42 +0200, Michael Opdenacker via lists.openembedded.org wrote:
>> I'm currently implementing bitbake-selftests for the PR server. I've got
>> my first series of tests working, inserting several values into the
>> database and then making queries to check that the stored values are
>> correct.
>>
>> However, for the second series of tests, I want to start from the
>> sqlite3 database file from the first series (prtest-basic.sqlite3), and
>> this time use it to start the server in read-only mode. However, the
>> table is empty when I open it!
>>
>> This is confirmed by making an SQL query from the command line:
>> $ sqlite3 prtest-basic.sqlite3
>> SQLite version 3.37.2 2022-01-06 13:25:41
>> Enter ".help" for usage hints.
>> sqlite> select * from PRMAIN;
>> sqlite>
>>
>> What could explain that the stored PR data are not flushed to the
>> database file during my tests, but I have no such issue when I make an
>> image with BitBake?
>>
>> I also changed the prserv/db.py file to run:
>> self.connection.execute("PRAGMA synchronous = NORMAL;")
>> self.connection.execute("PRAGMA journal_mode = WAL;")
>>
>> However, that didn't help.
>> Any clues for debugging this?
> Most selftests are designed to run in isolation. There is no "run X
> after Y" mechanism since you can specify a single test to run. You may
> need to merge the tests if you need the same database.

Thanks for your help.

Ouch, I'm trying but that looks complicated in my case:

  * It seems I can't open the same database before the end of the test
    with another, read-only server. Anyway, databases are not meant to
    be shared between servers.
  * It's not straightforward  to make a copy of the current database to
    another file. It would be necessary to add a special server and
    database hook for this purpose.
  * I could export the current database, but I wouldn't be able to
    import it into a read-only server.
  * I can't make the current server  turn read-only. I could add a
    client hook to do this, but do we want to allow clients to do this?
  * I want to generate the database I use for the read-only tests,
    because a ready-made one would have to be updated manually every
    time we modify the database contents.

That's why it would have been ideal to reuse a database from previous tests.

Anyway, I'm not stuck and can implement the "upstream" tests anyway.
Cheers
Michael.

-- 
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com

Re: [bitbake-devel] bitbake-selftest losing sqlite3 database contents!

[Joshua Watt]

On Fri, Apr 19, 2024 at 4:13 AM Michael Opdenacker
<michael.opdenacker@bootlin.com> wrote:
>
> Hi Richard,
>
> On 4/18/24 at 23:04, Richard Purdie wrote:
> > On Thu, 2024-04-18 at 21:42 +0200, Michael Opdenacker via lists.openembedded.org wrote:
> >> I'm currently implementing bitbake-selftests for the PR server. I've got
> >> my first series of tests working, inserting several values into the
> >> database and then making queries to check that the stored values are
> >> correct.
> >>
> >> However, for the second series of tests, I want to start from the
> >> sqlite3 database file from the first series (prtest-basic.sqlite3), and
> >> this time use it to start the server in read-only mode. However, the
> >> table is empty when I open it!
> >>
> >> This is confirmed by making an SQL query from the command line:
> >> $ sqlite3 prtest-basic.sqlite3
> >> SQLite version 3.37.2 2022-01-06 13:25:41
> >> Enter ".help" for usage hints.
> >> sqlite> select * from PRMAIN;
> >> sqlite>
> >>
> >> What could explain that the stored PR data are not flushed to the
> >> database file during my tests, but I have no such issue when I make an
> >> image with BitBake?
> >>
> >> I also changed the prserv/db.py file to run:
> >> self.connection.execute("PRAGMA synchronous = NORMAL;")
> >> self.connection.execute("PRAGMA journal_mode = WAL;")
> >>
> >> However, that didn't help.
> >> Any clues for debugging this?
> > Most selftests are designed to run in isolation. There is no "run X
> > after Y" mechanism since you can specify a single test to run. You may
> > need to merge the tests if you need the same database.
>
> Thanks for your help.
>
> Ouch, I'm trying but that looks complicated in my case:
>
>   * It seems I can't open the same database before the end of the test
>     with another, read-only server. Anyway, databases are not meant to
>     be shared between servers.
>   * It's not straightforward  to make a copy of the current database to
>     another file. It would be necessary to add a special server and
>     database hook for this purpose.
>   * I could export the current database, but I wouldn't be able to
>     import it into a read-only server.
>   * I can't make the current server  turn read-only. I could add a
>     client hook to do this, but do we want to allow clients to do this?
>   * I want to generate the database I use for the read-only tests,
>     because a ready-made one would have to be updated manually every
>     time we modify the database contents.

I'm not exactly sure what the purpose of the read-only prserv is in
the first place; based on these requirements, it sort of sounds like
there isn't anyway to make a read-only server that has actual data in
it :)

Ostensibly, you should be able to write a test fixture that does
whatever a real-life PR server installation would do to populate a
database and then make it read-only. You would then do this process on
the test database before each test where you needed to test the read
only functionality. You have to do it for each test because as you
noticed, you should not ever have "carry-over" in state from one test
to another. Having carry-over between tests is not a good idea for a
lot of reasons, which is why the hash server tests are setup the way
that they are with an empty database for each test.

If you need help figuring out how to do this I'm more than happy to
talk though how you might do it.

>
> That's why it would have been ideal to reuse a database from previous tests.
>
> Anyway, I'm not stuck and can implement the "upstream" tests anyway.
> Cheers
> Michael.
>
> --
> Michael Opdenacker, Bootlin
> Embedded Linux and Kernel engineering
> https://bootlin.com
>

Re: [bitbake-devel] bitbake-selftest losing sqlite3 database contents!

[Jan-Simon Möller]

Am Samstag, 20. April 2024, 22:35:33 CEST schrieb Joshua Watt via lists.openembedded.org:

> I'm not exactly sure what the purpose of the read-only prserv is in
> the first place; based on these requirements, it sort of sounds like
> there isn't anyway to make a read-only server that has actual data in
> it :)

Well what about this use-case:
An CI loop would populate using a rw PR-server , those artifacts (sources, sstate, hashserv(!), deploy dir)
are then pushed to a download server. We do not want to expose that r/w PR-serv publically.
Now for a binary feed-like setup, we'd need a matching PR and Hashserv for the downstream. No ?
Thus extra r/o instances sharing the same sqlite in the back.
Isn't this then required for a downstream user to create their own 'matching' binaries for the feed ?

Correct me, but e.g. this is what we assume is the way to expose the data:

# rw prserv on port 8181
bitbake-prserv --start --file /home/yocto/pr_hash_serv-volume/prserv.db --loglevel=DEBUG --log /home/yocto/pr_hash_serv-volume/prserv-rw.log --port 8181
# ro prserv on port 8282
bitbake-prserv --start -r --file /home/yocto/pr_hash_serv-volume/prserv.db --loglevel=DEBUG --log /home/yocto/pr_hash_serv-volume/prserv-ro.log --port 8282

# rw hashserv on port 8383
bitbake-hashserv --bind :8383 --log DEBUG --database /home/yocto/pr_hash_serv-volume/hashserv.db > /home/yocto/pr_hash_serv-volume/hashserv-rw.log 2>&1 &
# ro hashserv on port 8484
bitbake-hashserv -r --bind :8484 --log DEBUG --database /home/yocto/pr_hash_serv-volume/hashserv.db > /home/yocto/pr_hash_serv-volume/hashserv-ro.log 2>&1 &


@Richard/Josh:
Let me add (yes, grumble a bit ;) ) : for me as *end user* of this, this is all the same and would be best kept in one single server that I can scale.
Having it split into two just complicates things ... and now for scarthgap, we need another instance of the hashserv due to the API change.
Please consider this from a user perspective working on multiple branches and not just one master branch!
The data might not be 'the same' or not 'connected', but they are still linked in a way and moreover we are exposing this to users
to use and usage gets mandatory if we think of speeding up builds and binary feeds. Don't get me wrong, this is good stuff, but we make it hard for general use.
Wrt scaling: - essentially we're setting us up with scaling problems wrt prserv down the road when it gets needed for binary feed.
Heck, we're just solving this for hashserv, so why not do this likewise right away - at best put both in one server?

@Michael: the 'empty file' issue rings a bell and I've seen this back when we tried this for the first time.
Export did not work either. But I have a hard time remembering the details. Might even depend on sqlite versions back then.
I found this in my local git:
sed -i -e "s#EXCLUSIVE#IMMEDIATE#g" /home/yocto/poky/bitbake/lib/prserv/db.py
It probably helped some but might cause other issues. Let's see what the experts say.


> Ostensibly, you should be able to write a test fixture that does
> whatever a real-life PR server installation would do to populate a
> database and then make it read-only. You would then do this process on
> the test database before each test where you needed to test the read
> only functionality. You have to do it for each test because as you
> noticed, you should not ever have "carry-over" in state from one test
> to another. Having carry-over between tests is not a good idea for a
> lot of reasons, which is why the hash server tests are setup the way
> that they are with an empty database for each test.

I second Josh here.


Best,
JS

Re: [bitbake-devel] bitbake-selftest losing sqlite3 database contents!

[Joshua Watt]

On Mon, Apr 22, 2024 at 4:00 AM Jan-Simon Möller <dl9pf@gmx.de> wrote:
>
> Am Samstag, 20. April 2024, 22:35:33 CEST schrieb Joshua Watt via lists.openembedded.org:
>
> > I'm not exactly sure what the purpose of the read-only prserv is in
> > the first place; based on these requirements, it sort of sounds like
> > there isn't anyway to make a read-only server that has actual data in
> > it :)
>
> Well what about this use-case:
> An CI loop would populate using a rw PR-server , those artifacts (sources, sstate, hashserv(!), deploy dir)
> are then pushed to a download server. We do not want to expose that r/w PR-serv publically.
> Now for a binary feed-like setup, we'd need a matching PR and Hashserv for the downstream. No ?
> Thus extra r/o instances sharing the same sqlite in the back.
> Isn't this then required for a downstream user to create their own 'matching' binaries for the feed ?
>
> Correct me, but e.g. this is what we assume is the way to expose the data:
>
> # rw prserv on port 8181
> bitbake-prserv --start --file /home/yocto/pr_hash_serv-volume/prserv.db --loglevel=DEBUG --log /home/yocto/pr_hash_serv-volume/prserv-rw.log --port 8181
> # ro prserv on port 8282
> bitbake-prserv --start -r --file /home/yocto/pr_hash_serv-volume/prserv.db --loglevel=DEBUG --log /home/yocto/pr_hash_serv-volume/prserv-ro.log --port 8282
>
> # rw hashserv on port 8383
> bitbake-hashserv --bind :8383 --log DEBUG --database /home/yocto/pr_hash_serv-volume/hashserv.db > /home/yocto/pr_hash_serv-volume/hashserv-rw.log 2>&1 &
> # ro hashserv on port 8484
> bitbake-hashserv -r --bind :8484 --log DEBUG --database /home/yocto/pr_hash_serv-volume/hashserv.db > /home/yocto/pr_hash_serv-volume/hashserv-ro.log 2>&1 &
>
>
> @Richard/Josh:
> Let me add (yes, grumble a bit ;) ) : for me as *end user* of this, this is all the same and would be best kept in one single server that I can scale.
> Having it split into two just complicates things ... and now for scarthgap, we need another instance of the hashserv due to the API change.
> Please consider this from a user perspective working on multiple branches and not just one master branch!
> The data might not be 'the same' or not 'connected', but they are still linked in a way and moreover we are exposing this to users
> to use and usage gets mandatory if we think of speeding up builds and binary feeds. Don't get me wrong, this is good stuff, but we make it hard for general use.
> Wrt scaling: - essentially we're setting us up with scaling problems wrt prserv down the road when it gets needed for binary feed.
> Heck, we're just solving this for hashserv, so why not do this likewise right away - at best put both in one server?

Can you clarify what you mean by "both in one server"? There are a lot
of different levels of varying complexity that could be done for that.
For example, you could:
1) Run both servers as separate processes in e.g. a shared container
2) Run both servers in a shared script
3) Combine the API into a single server API

These are generally more complex with more caveats as you go; #1 is
the "easiest", #2 isn't possible until the PR server is completely
asynchronous like the hashserver (otherwise it won't scale at all),
and #3 is basically a completely new (breaking) API.

My preference is #1 or #2, but #2 makes no sense until the PR server
has all of the features of the hash server; this patch series adds
websockets which is a good step, but it would also need the database
backend re-written to be nonblocking like the hash server, otherwise
it's just asking for scaling problems if you run them in the same
process :)

Also keep in mind that one of the goals of the websockets server is to
reduce the need for the "read-only" server, since you can have your CI
authenticate with the hash server and get rw access, but anonymous
users can be given read-only access.

In short, there is space to make it a little easier to stand up the
servers in some common ways, but I'm hesitant to force everyone to run
the servers a specific way as I don't think there is a "one size fits
all".

>
> @Michael: the 'empty file' issue rings a bell and I've seen this back when we tried this for the first time.
> Export did not work either. But I have a hard time remembering the details. Might even depend on sqlite versions back then.
> I found this in my local git:
> sed -i -e "s#EXCLUSIVE#IMMEDIATE#g" /home/yocto/poky/bitbake/lib/prserv/db.py
> It probably helped some but might cause other issues. Let's see what the experts say.
>
>
> > Ostensibly, you should be able to write a test fixture that does
> > whatever a real-life PR server installation would do to populate a
> > database and then make it read-only. You would then do this process on
> > the test database before each test where you needed to test the read
> > only functionality. You have to do it for each test because as you
> > noticed, you should not ever have "carry-over" in state from one test
> > to another. Having carry-over between tests is not a good idea for a
> > lot of reasons, which is why the hash server tests are setup the way
> > that they are with an empty database for each test.
>
> I second Josh here.
>
>
> Best,
> JS
>
>
>
>

Re: [bitbake-devel] bitbake-selftest losing sqlite3 database contents!

[Jan-Simon Moeller]

Sorry for highjacking the thread ... reply inline.

<snip>
Am Montag, 22. April 2024, 16:47:48 CEST schrieb Joshua Watt:
> On Mon, Apr 22, 2024 at 4:00 AM Jan-Simon Möller <dl9pf@gmx.de> wrote:
> > Am Samstag, 20. April 2024, 22:35:33 CEST schrieb Joshua Watt via 
lists.openembedded.org:
> > @Richard/Josh:
> > Let me add (yes, grumble a bit ;) ) : for me as *end user* of this, this
> > is all the same and would be best kept in one single server that I can
> > scale. Having it split into two just complicates things ... and now for
> > scarthgap, we need another instance of the hashserv due to the API
> > change. Please consider this from a user perspective working on multiple
> > branches and not just one master branch! The data might not be 'the same'
> > or not 'connected', but they are still linked in a way and moreover we
> > are exposing this to users to use and usage gets mandatory if we think of
> > speeding up builds and binary feeds. Don't get me wrong, this is good
> > stuff, but we make it hard for general use. Wrt scaling: - essentially
> > we're setting us up with scaling problems wrt prserv down the road when
> > it gets needed for binary feed. Heck, we're just solving this for
> > hashserv, so why not do this likewise right away - at best put both in
> > one server?
> Can you clarify what you mean by "both in one server"? There are a lot
> of different levels of varying complexity that could be done for that.
> For example, you could:
> 1) Run both servers as separate processes in e.g. a shared container
> 2) Run both servers in a shared script
> 3) Combine the API into a single server API
> 
> These are generally more complex with more caveats as you go; #1 is
> the "easiest", #2 isn't possible until the PR server is completely
> asynchronous like the hashserver (otherwise it won't scale at all),
> and #3 is basically a completely new (breaking) API.

To be really honest, only #3 makes sense from my perspective  aka 'end user 
perspective' .


#1 is what we do already for the existing pr+hashserv ... 
excuse my language, but it s**ks. You need to care for multiple API revisions 
aka double (pr+hash) the port numbers to punch through. Each breaking API 
update needs us to start more server processes and punch through more ports. 
This is such a maintenance pita.

#2 is no difference to #1 if you think of different branches being in use.

> My preference is #1 or #2, but #2 makes no sense until the PR server
> has all of the features of the hash server; this patch series adds
> websockets which is a good step, but it would also need the database
> backend re-written to be nonblocking like the hash server, otherwise
> it's just asking for scaling problems if you run them in the same
> process :)

Nack.
Just teach Hashserv to store/serv the additional values and be done ?
Thanks to your great work on scaling the hashserv, we're almost there wrt 
deployments at scale. Why do that whole setup again for prserv ?
We'd have to duplicate also all the assets we use during scale-out ?

> Also keep in mind that one of the goals of the websockets server is to
> reduce the need for the "read-only" server, since you can have your CI
> authenticate with the hash server and get rw access, but anonymous
> users can be given read-only access.

Even better, then put it in one combined pr+hashserv, on one port, so we can 
manage the whole thing easily. 
Think of it ...  why would you want to deal with auth for 2 servers (pr+hash)?
This just leads to mismatches in auth (we all make mistakes during setup) and 
having to deal with twice as much credentials ... why?
I can't think of a case where I'd r/w on one but r/o on the other. It makes no 
sense.
 
> In short, there is space to make it a little easier to stand up the
> servers in some common ways, but I'm hesitant to force everyone to run
> the servers a specific way as I don't think there is a "one size fits
> all".
In the end: This is both 'metadata' that we want to store / reuse with 
bitbake. And more so going forward. So keep it simple also for the users.
This is not PR data vs hashserv data. Both is metadata we want to keep.

One size - actually I think exactly that. This makes it easier to run a local 
instance (just one - no mismatches possible). But also scale out if required 
and not doubling the infra (and cost!)  when doing so.


Best,
Jan-Simon

Re: [bitbake-devel] bitbake-selftest losing sqlite3 database contents!

[Michael Opdenacker]

Hi Jan Simon,

Just replying to one part :)

On 4/22/24 at 12:00, Jan-Simon Möller wrote:
> @Michael: the 'empty file' issue rings a bell and I've seen this back when we tried this for the first time.
> Export did not work either. But I have a hard time remembering the details. Might even depend on sqlite versions back then.
> I found this in my local git:
> sed -i -e "s#EXCLUSIVE#IMMEDIATE#g" /home/yocto/poky/bitbake/lib/prserv/db.py
> It probably helped some but might cause other issues. Let's see what the experts say.


Thanks! What's strange is that I only have this issue with the first 
database I create with the PR server. Databases created from later tests 
don't have this issue. This will need more time to investigate...
Cheers
Michael.

-- 
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com

Re: [bitbake-devel] bitbake-selftest losing sqlite3 database contents!

[Michael Opdenacker]

On 4/20/24 at 22:35, Joshua Watt wrote:
> I'm not exactly sure what the purpose of the read-only prserv is in
> the first place; based on these requirements, it sort of sounds like
> there isn't anyway to make a read-only server that has actual data in
> it :)
>
> Ostensibly, you should be able to write a test fixture that does
> whatever a real-life PR server installation would do to populate a
> database and then make it read-only. You would then do this process on
> the test database before each test where you needed to test the read
> only functionality. You have to do it for each test because as you
> noticed, you should not ever have "carry-over" in state from one test
> to another. Having carry-over between tests is not a good idea for a
> lot of reasons, which is why the hash server tests are setup the way
> that they are with an empty database for each test.
>
> If you need help figuring out how to do this I'm more than happy to
> talk though how you might do it.

I agree with the approach. It's just that I need to stop the read-write 
server, and start it again in read-only mode, without losing the database.
For the moment, I managed to run read-only tests on the database I 
created "manually", that by testing the database functions, not by using 
a PR server client.

This being said, I now have non empty databases which I could use too 
for read-only tests. No problem to generate one of them at every new 
test series, provided I don't get an empty  database again.

I'll continue later if I can, because my priority before the end of the 
month is the image upgrade tests.
Cheers
Michael.

-- 
Michael Opdenacker, Bootlin
Embedded Linux and Kernel engineering
https://bootlin.com