sqllite doesn't support tables with names such as release or savepoint

[jfding]

sqlite3 doesn't support tables with the names release or savepoint; this notably breaks yum.

TESTCASE:
cat <<EOF | sqlite3
    CREATE TABLE savepoint(release);
    INSERT INTO savepoint(release) VALUES(10);
    UPDATE savepoint SET release = 5;
    SELECT release FROM savepoint;
EOF

fail:
SQL error near line 1: near "savepoint": syntax error
SQL error near line 2: near "savepoint": syntax error
SQL error near line 3: near "savepoint": syntax error
SQL error near line 4: near "release": syntax error

passes:
5

[Nicholas (nicholas-redhat-bugs)]

Description of problem:
Yum fails with latest sqlite 3.6.10 in rawhide.

Version-Release number of selected component (if applicable):
yum-3.2.21-3

How reproducible:
Always

Steps to Reproduce:
1. Update to new sqlite.
2. Remove /var/cache/yum directory.
3. Run a yum update.

Actual results:
It fetches the primary dbs for all the repos and then returns the following error:
Error: malformed database schema (removals) - no such table: main.packages

Expected results:
Update without error.

Additional info:
Yum works as expected if you revert to the previous sqlite-3.6.7, so it is definitely some change in the sqlite api.

There is also an issue with local repositories created with createrepo. I had a local repository in my repos.d and it causes the following error:
localrepo | 1.0 kB 00:00
localrepo/primary | 27 kB 00:00

(process:5225): GLib-CRITICAL **: g_timer_stop: assertion `timer != NULL' failed

(process:5225): GLib-CRITICAL **: g_timer_destroy: assertion `timer != NULL' failed
Traceback (most recent call last):
  File "/usr/bin/yum", line 29, in <module>
    yummain.user_main(sys.argv[1:], exit_code=True)
  File "/usr/share/yum-cli/yummain.py", line 229, in user_main
    errcode = main(args)
  File "/usr/share/yum-cli/yummain.py", line 104, in main
    result, resultmsgs = base.doCommands()
  File "/usr/share/yum-cli/cli.py", line 339, in doCommands
    self._getTs(needTsRemove)
  File "/usr/lib/python2.6/site-packages/yum/depsolve.py", line 101, in _getTs
    self._getTsInfo(remove_only)
  File "/usr/lib/python2.6/site-packages/yum/depsolve.py", line 112, in _getTsInfo
    pkgSack = self.pkgSack
  File "/usr/lib/python2.6/site-packages/yum/__init__.py", line 592, in <lambda>
    pkgSack = property(fget=lambda self: self._getSacks(),
  File "/usr/lib/python2.6/site-packages/yum/__init__.py", line 435, in _getSacks
    self.repos.populateSack(which=repos)
  File "/usr/lib/python2.6/site-packages/yum/repos.py", line 251, in populateSack
    sack.populate(repo, mdtype, callback, cacheonly)
  File "/usr/lib/python2.6/site-packages/yum/yumRepo.py", line 184, in populate
    dobj = repo_cache_function(xml, csum)
  File "/usr/lib64/python2.6/site-packages/sqlitecachec.py", line 45, in getPrimary
    self.repoid))
TypeError: Can not create packages table: near "release": syntax error

Again, when I revert to the previous sqlite 3.6.7, yum works as expected.

[Tom (tom-redhat-bugs)]

It also "works for me" if I leave the new sqlite update but revert the new fedora-release.

[James (james-redhat-bugs)]

Tom can you make sure that's true ... do a:

yum list sqlite yum fedora-release
sha1sum /var/cache/yum/rawhide/*

...both before and after?

[James (james-redhat-bugs)]

rpm -q if the yum list doesn't work.

[Tom (tom-redhat-bugs)]

Uhhh..

No, I was mistaken. Sorry.

Appears I had backed out both fedora-release and sqlite at the same time.

[Panu (panu-redhat-bugs)]

This is sqlite bug alright, see
http://<email address hidden>/msg39666.html

My bad (insufficient testing), apologies. Will fix right away.

[seth (seth-redhat-bugs)]

You're my hero.

[Panu (panu-redhat-bugs)]

Fixed in sqlite-3.6.10-2.fc11.

If I hadn't put out a broken version, there wouldn't have be anything to fix and I wouldn't get called anybody's hero. Wonder what the morale of this is ;)

[seth (seth-redhat-bugs)]

We still love you, Panu. :)

[Shane Bryan (shane-bryan)]

I can also confirm this bug, and note that this currently *blocks* the use of Ubuntu 9.04 as a viable development platform for those wanting to do Moblin2 image creation using mic2. Forces developers to use only 8.10 or completely move to F10 or other RPM based distros.

[Oliver Grawert (ogra)]

seems thats a known bug with the 3.6.10 version of sqlite (as used in 9.04)

[Loïc Minier (lool)]

Can you provide instructions to reproduce this issue with or without moblin 2 repos? Thanks

[Loïc Minier (lool)]

See upstream ticket http://www.sqlite.org/cvstrac/chngview?cn=6186

[Loïc Minier (lool)]

Impact: breaks yum and programs using yum (e.g. moblin-image-creator2, mach, mock, ...).
Bug was addressed by properly listing the release and savepoint keywords in the command parser; discussion in upstream ticket.
Patch attached.

[Loïc Minier (lool)]

Regression potential: might affect users of the savepoint/release features, but it should just work, according to upstream.

[Loïc Minier (lool)]

Uploaded a debdiff which makes the test case pass for me.

[Oliver Grawert (ogra)]

just for verification:
jaunty:
sqlite> CREATE TABLE a (id, release);
SQL error: near "release": syntax error
sqlite>

karmic:

sqlite> CREATE TABLE a (id, release);
sqlite>

[Launchpad Janitor (janitor)]

This bug was fixed in the package sqlite3 - 3.6.10-1ubuntu0.1

---------------
sqlite3 (3.6.10-1ubuntu0.1) jaunty-updates; urgency=low

  * New patch, 70_savepoint-and-release-keywords, allow savepoint and release
    in table names and add a test case; from upstream checkin 6186; see ticket
    http://www.sqlite.org/cvstrac/chngview?cn=6186; LP: #375852.

 -- Loic Minier <email address hidden> Fri, 15 May 2009 12:53:27 +0200

[Martin Pitt (pitti)]

Accepted into jaunty-proposed, the package will build now and be available in a few hours. Please test and give feedback here. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Thank you in advance!

[Shane Bryan (shane-bryan)]

Fix confirmed. Updated package installed and resolved issues blocking use of moblin-image-creator-2 on Jaunty.

[Loïc Minier (lool)]

I tested the jaunty-updates .2 binaries and they fix the issue.

[jfding (jfding)]

Great work and sorry for my absence (too busy recently).

I noticed this bug has been transfered to sqlite3, but I am very doubt the fix can resolve the problem of yum.
I have tried yum with all latest updates, it still cannot work.

An interesting thing I noticed: in Ubuntu (including Intrepid and Jaunty), yum depends on the two pkgs:
 <1> python-sqlite
 <2> python-sqlitecache
 <1> is for sqlite2, and <2> for sqlite3 !
The root cause of the compatibility?

[Loïc Minier (lool)]

jfding, your original issue "TypeError: Can not create packages table: near "release": syntax error" will be fixed with this sqlite3 update; if you have another issue with MIC after applying all the sqlite3 updates (including libsqlite3), please file a new bug. Thanks!

[jfding (jfding)]

Minier, but my origin bug is "yum cannot work". And with the updating of sqlite3 things, yum still cannot work.
The error message I pasted here is just the output of yum, to show the appearance of yum failures.
Now this bug has been changed to "sqlite3" category with fixing, but the origin problem I want to resolve is still there:)
If I need to file a new bug, it will be the same as my first one.

[Loïc Minier (lool)]

jfding, I understand it might still not work for you, but I assure you that the original issue (and the first bug you were hitting) should be resolved with this update. I could reproduce the error message you originally reported, and I could successfully use yum here after the fix. In fact several other people confirmed they were seeing the same issue and that the update fixed it for them.

Please report a new bug against yum with the new error message you're getting.

Thanks,

[jfding (jfding)]

Good news, now I can use and yum and mic2 of moblin now.
I have tried several versions of libsqlite3, and found the followings can work:
> older version 3.5.9-3 from Intrepid
> latest version from sqlite.org 3.6.14-1
but the proposed one in current Jaunty repo, 3.6.10-1 still cannot work in my box.
It's strange, I hope in next upgrade of sqlite3, this problem can be skipped.

[Launchpad Janitor (janitor)]

This bug was fixed in the package sqlite3 - 3.6.10-1ubuntu0.2

---------------
sqlite3 (3.6.10-1ubuntu0.2) jaunty-proposed; urgency=low

  * Reupload to -proposed; sorry.

sqlite3 (3.6.10-1ubuntu0.1) jaunty-updates; urgency=low

  * New patch, 70_savepoint-and-release-keywords, allow savepoint and release
    in table names and add a test case; from upstream checkin 6186; see ticket
    http://www.sqlite.org/cvstrac/chngview?cn=6186; LP: #375852.

 -- Loic Minier <email address hidden> Fri, 15 May 2009 16:49:00 +0200