Hi,
I am curious, then, where the AUTOINCREMENT came from, and which
specific older Bacula version you think it may have been in?
-- John
--- Begin Message ---
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
(
filenameid serial not null,
name text not 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 Debian, we received a bug report at
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=542810 regarding the
migration from sqlite2 to sqlite3. We are doing the process implied
by the make_catalog_backup command; namely:
sqlite "$DB" .dump | sqlite3 "$DB.sqlite3"
Our reporter noticed that:
This will fail on older installations of bacula-director-sqlite,
because the database may contain table schemas like the following:
CREATE TABLE Filename (
FilenameId INTEGER UNSIGNED AUTOINCREMENT,
Name TEXT DEFAULT "",
PRIMARY KEY(FilenameId)
);
Problem is, sqlite3 does not understand AUTOINCREMENT in this way,
this keyword is only allowed alongside a PRIMARY KEY statement. Besides,
every
integer primary key will autoincrement without this keyword, as per
http://sqlite.org/faq.html#q1
Kern states that:
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)
It seems very strongly that this is not the case due to Sqlite3 not
understanding AUTOINCREMENT.
Sven Hartge adds:
Quote from Kern Sibbald:
| 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)
This is incorrect.
Try to create a table with the following schema in sqlite3:
CREATE TABLE Filename (
FilenameId INTEGER UNSIGNED AUTOINCREMENT,
Name TEXT DEFAULT "",
PRIMARY KEY(FilenameId)
);
o...@ds9:/home/oweh > sqlite3 /tmp/test-db.db
SQLite version 3.6.17
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE Filename (
...> FilenameId INTEGER UNSIGNED AUTOINCREMENT,
...> Name TEXT DEFAULT "",
...> PRIMARY KEY(FilenameId)
...> );
SQL error: near "AUTOINCREMENT": syntax error
sqlite>
Only if I remove the "UNSIGNED AUTOINCREMENT" the table will be
syntactically correct _and_ behave in the correct and intended way.
----------------------------------------------------------------------
(0004524) kern (administrator) - 2009-08-21 20:04
http://bugs.bacula.org/view.php?id=1351#c4524
----------------------------------------------------------------------
Recently, before deciding not to support SQLite2, I converted an existing
SQLite2 database that was originally created in something like 2004 to
SQLite3 using the something like the following:
echo ".dump" | sqlite bacula.db >bacula.sql
sqlite3 bacula3.db
.quite
sqlite3 bacula3.db <bacula.sql
I did no editing of bacula.sql.
Bacula has never had any AUTOINCREMENT statements in any of the SQLite
scripts distributed by the project (I checked back to version 1.38), and it
certainly has never used the incorrect syntax you show above. As a
consequence, I have a hard time understanding the problem, unless it is a
hypothetical problem or someone explicitly modified the Bacula tables.
I am sorry if anyone has problems converting SQLite2 to SQLite3, but for
me it went very smoothly. If you are having real problems, I would suggest
looking at www.sqlite.org and possibly filing a bug report.
You may reopen this to post more comments, but I prefer an email to the
bacula-devel list, since I don't consider this issue a bug.
Issue History
Date Modified Username Field Change
======================================================================
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
2009-08-21 18:28 shartge Issue Monitored: shartge
2009-08-21 18:37 jgoerzen Note Added: 0004523
2009-08-21 18:37 jgoerzen Status closed => feedback
2009-08-21 18:37 jgoerzen Resolution not a bug => reopened
2009-08-21 20:04 kern Note Added: 0004524
2009-08-21 20:04 kern Status feedback => closed
2009-08-21 20:04 kern Resolution reopened => not a bug
======================================================================
--- End Message ---
