Bug#542810: [Fwd: [bacula 0001351]: SQLite tables lack autoincrement -- also implications for upgrades from SQLite 2]
---BeginMessage--- The following issue has been CLOSED == http://bugs.bacula.org/view.php?id=1351 == Reported By:jgoerzen Assigned To: == Project:bacula Issue ID: 1351 Category: Storage Daemon Reproducibility:N/A Severity: minor Priority: normal Status: closed Resolution: not a bug Fixed in Version: == Date Submitted: 2009-08-21 16:23 BST Last Modified: 2009-08-21 17:17 BST == Summary:SQLite tables lack autoincrement -- also implications for upgrades from SQLite 2 Description: Debian received a bug report at http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=542810 On investigating it, I noticed that src/cats/make_postgresql_tables.in has: CREATE TABLE filename ( filenameidserial not null, name textnot null, primary key (filenameid) ); while make_sqlite3_tables.in has: CREATE TABLE Filename ( FilenameId INTEGER, Name TEXT DEFAULT '', PRIMARY KEY(FilenameId) ); Note that the serial type for PostgreSQL implies that filenameid is an auto-increment field, but the Sqlite3 version is not auto incrementing. MySQL also has an AUTO_INCREMENT flag on that field. So PostgreSQL and MySQL are auto-incrementing, but Sqlite3 is not. Note that this applies to many tables, not just this one; this is just an example. So the question is: why the difference? Secondly, does it hurt anything? Does Bacula generate the IDs to load manually anyhow? And if so, why mark them serial/autoincrement in PostgreSQL and MySQL? And finally, what should we do about migrating people from Sqlite v2 that had autoincrement columns in their schema? == -- (0004520) kern (administrator) - 2009-08-21 17:17 http://bugs.bacula.org/view.php?id=1351#c4520 -- This is not a bug, rather a support question. Quoting from your above statement the Sqlite3 version is not auto incrementing. and So PostgreSQL and MySQL are auto-incrementing, but Sqlite3 is not. This is incorrect the FilenameId field in SQLite2 and SQLite3 *is* autoincrementing. To understand why you must know that when SQLite was first released it did not have the AUTOINCREMENT keyword, but it had a very specific way of identifying autoincrement fields that we use. See: http://www.sqlite.org/autoinc.html for the gory details. Q: So the question is: why the difference? A: Historically AUTOINCREMENT did not exist, so we use the SQLite way of specifying autoincrement (actually specifying a ROWID item). Q: Does Bacula generate the IDs to load manually anyhow? A: No it uses a NULL as with MySQL and PostgreSQL and that automatically generates the ID. Q: And finally, what should we do about migrating people from Sqlite v2 that had autoincrement columns in their schema? A: Sorry but I do not understand the question. SQLite2 and SQLite3 both have and both had autoincrement columns in their schema, and it works. Converting from SQLite2 to SQLite3 is easy. You simply export or dump the SQLite2 database to an ASCII file (example in the default Bacula catalog backup script) then import it using SQLite3 (see comments at the bottom of the Bacula catalog backup script) Converting SQLite2 to MySQL or PostgreSQL is a bit of a pain because of the different SQL syntax that different engines use. There is a script in the examples directory that purports to do this and many examples on the web. Issue History Date ModifiedUsername FieldChange == 2009-08-21 16:23 jgoerzen New Issue 2009-08-21 16:29 jgoerzen Issue Monitored: jgoerzen 2009-08-21 17:17 kern Note Added: 0004520 2009-08-21 17:17 kern Status new = closed 2009-08-21 17:17 kern Resolution open = not a bug == ---End Message---
Bug#542810: [Fwd: [bacula 0001351]: SQLite tables lack autoincrement -- also implications for upgrades from SQLite 2]
Hi, I am curious, then, where the AUTOINCREMENT came from, and which specific older Bacula version you think it may have been in? -- John ---BeginMessage--- The following issue has been CLOSED == http://bugs.bacula.org/view.php?id=1351 == Reported By:jgoerzen Assigned To: == Project:bacula Issue ID: 1351 Category: Storage Daemon Reproducibility:N/A Severity: minor Priority: normal Status: closed Resolution: not a bug Fixed in Version: == Date Submitted: 2009-08-21 16:23 BST Last Modified: 2009-08-21 20:04 BST == Summary:SQLite tables lack autoincrement -- also implications for upgrades from SQLite 2 Description: Debian received a bug report at http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=542810 On investigating it, I noticed that src/cats/make_postgresql_tables.in has: CREATE TABLE filename ( filenameidserial not null, name textnot null, primary key (filenameid) ); while make_sqlite3_tables.in has: CREATE TABLE Filename ( FilenameId INTEGER, Name TEXT DEFAULT '', PRIMARY KEY(FilenameId) ); Note that the serial type for PostgreSQL implies that filenameid is an auto-increment field, but the Sqlite3 version is not auto incrementing. MySQL also has an AUTO_INCREMENT flag on that field. So PostgreSQL and MySQL are auto-incrementing, but Sqlite3 is not. Note that this applies to many tables, not just this one; this is just an example. So the question is: why the difference? Secondly, does it hurt anything? Does Bacula generate the IDs to load manually anyhow? And if so, why mark them serial/autoincrement in PostgreSQL and MySQL? And finally, what should we do about migrating people from Sqlite v2 that had autoincrement columns in their schema? == -- (0004520) kern (administrator) - 2009-08-21 17:17 http://bugs.bacula.org/view.php?id=1351#c4520 -- This is not a bug, rather a support question. Quoting from your above statement the Sqlite3 version is not auto incrementing. and So PostgreSQL and MySQL are auto-incrementing, but Sqlite3 is not. This is incorrect the FilenameId field in SQLite2 and SQLite3 *is* autoincrementing. To understand why you must know that when SQLite was first released it did not have the AUTOINCREMENT keyword, but it had a very specific way of identifying autoincrement fields that we use. See: http://www.sqlite.org/autoinc.html for the gory details. Q: So the question is: why the difference? A: Historically AUTOINCREMENT did not exist, so we use the SQLite way of specifying autoincrement (actually specifying a ROWID item). Q: Does Bacula generate the IDs to load manually anyhow? A: No it uses a NULL as with MySQL and PostgreSQL and that automatically generates the ID. Q: And finally, what should we do about migrating people from Sqlite v2 that had autoincrement columns in their schema? A: Sorry but I do not understand the question. SQLite2 and SQLite3 both have and both had autoincrement columns in their schema, and it works. Converting from SQLite2 to SQLite3 is easy. You simply export or dump the SQLite2 database to an ASCII file (example in the default Bacula catalog backup script) then import it using SQLite3 (see comments at the bottom of the Bacula catalog backup script) Converting SQLite2 to MySQL or PostgreSQL is a bit of a pain because of the different SQL syntax that different engines use. There is a script in the examples directory that purports to do this and many examples on the web. -- (0004523) jgoerzen (reporter) - 2009-08-21 18:37 http://bugs.bacula.org/view.php?id=1351#c4523 -- I apoligize for reopening this; I wanted to add a note and couldn't find any other way, because there is something that needs to be corrected. Kern, what you say makes sense, except one bit: The Sqlite2 to Sqlite3 migration can NOT necessarily be done that easily. I have written to the -users mailing list about it, so if you want to summarily close this report again, that's fine, but I wanted to reproduce the comments here for any future people that might be finding this page. Over at
Bug#542810: [Fwd: [bacula 0001351]: SQLite tables lack autoincrement -- also implications for upgrades from SQLite 2]
John Goerzen wrote: I am curious, then, where the AUTOINCREMENT came from, and which specific older Bacula version you think it may have been in? I really don't know. But I never manually altered the database. But, look here: o...@hild:~/apt/bacula-2.4.4/updatedb$ grep AUTOINCREMENT * update_sqlite3_tables_8_to_9: MediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite3_tables_8_to_9: MediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite3_tables_9_to_10: MediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_4_to_5: MediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_4_to_5: MediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_4_to_5: PoolId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_4_to_5: PoolId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_5_to_6: FileSetId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_5_to_6: FileSetId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_5_to_6: JobMediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_5_to_6: JobMediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_5_to_6: BaseId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_6_to_7: MediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_6_to_7: MediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_6_to_7: PoolId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_6_to_7: PoolId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_6_to_7: BaseId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_7_to_8: MediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_7_to_8: MediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_8_to_9: MediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_8_to_9: MediaId INTEGER UNSIGNED AUTOINCREMENT, update_sqlite_tables_9_to_10: MediaId INTEGER UNSIGNED AUTOINCREMENT, So there _are_ places, where AUTOINCREMENT gets added to the database. Grüße, Sven. -- To UNSUBSCRIBE, email to debian-bugs-rc-requ...@lists.debian.org with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
