SQLite stopped working after upgrade
Solution 1
Extensions
According to Vendor Specific Database Extensions page on the PHP site, there are two SQLite extensions:
SQLite3 extension is a newer replacement of SQLite, which soon will become obsolete:
The SQLite extension is enabled by default as of PHP 5.0.
Beginning with PHP 5.4, the SQLite extension is available only via PECL.
Ubuntu 10.04 vs 12.04
Now, some practicalities. Let's have a look at default web server configuration that was installed using:
sudo apt-get install apache2 php5 php5-sqlite
Ubuntu 10.04:
# php --ri sqlite
SQLite
SQLite support => enabled
PECL Module version => 2.0-dev
SQLite Library => 2.8.17
SQLite Encoding => UTF-8
# php --ri sqlite3
sqlite3
SQLite3 support => enabled
SQLite3 module version => 0.7-dev
SQLite Library => 3.6.22
Ubuntu 12.04:
# php --ri sqlite
Extension 'sqlite' not present.
# php --ri sqlite3
sqlite3
SQLite3 support => enabled
SQLite3 module version => 0.7-dev
SQLite Library => 3.7.9
So, Ubuntu 12.04 default PHP installation (as of writing this - version 5.3.10) already does not have sqlite
extension as built in. The output above states that only sqlite3
extension is present, which came from php5-sqlite
package.
Solution
Pick your evil:
- Right approach in long term would be to adapt the code to get it working under SQLite3. The changes would be minor, but this might involve some unwanted copy-paste-style work on a big amount of files. If this is the case, and right is not a right word here, solution 2 is for you.
- Install obsolete extension from PECL.
Solution 2
Following Andrejs Cainikovs' answer and strongly advising you to upgrade the code, here some examples of what kind of work should be done, when migrating from SQLiteDatabase
class (sqlite library) to SQLite3
class (SQLite3 library).
For example:
-
SQLiteDatabase
->SQLite3
, -
SQLiteDatabase::unbufferedQuery
->SQLite3::query
, -
SQLiteResult::fetchAll(SQLITE_*)
->SQLite3Result::fetchArray(SQLITE3_*)
etc.
As for fetching, in old SQLite we had:
$rowsIMEI = $db->unbufferedQuery($imeiSQL)->fetchAll(SQLITE_ASSOC);
foreach($rowsIMEI as $r)
{
...
}
While, in new SQLite3 we should:
$rowsIMEI = $db->query($imeiSQL);
while($r = $rowsIMEI->fetchArray(SQLITE3_ASSOC))
{
...
}
Other changes requires similar amount of work, so this shouldn't be a life-time process.
Before you begin, you have to first use a tool like SQLite Studio to convert your database file from 2.1 to 3.0. Size can be lowered by even a half, so I think it is something worth fighting for.
Related videos on Youtube
![SteeveDroz](https://i.stack.imgur.com/7Bn1T.jpg?s=256&g=1)
SteeveDroz
Updated on September 18, 2022Comments
-
SteeveDroz almost 2 years
As many of you did, I upgraded from Lucid to Precise this week and almost everything seems to work find.
everything except… SQLite, which was working well before but not anymore.
I checked everything: phpinfo(), php.ini, sqlite3.ini.
The
sqlite
command works fine, but when I try to use PHP... *PLOP*, nothing works.I get the message
Fatal error: Class 'SQLiteDatabase' not found in /var/www/test/sqlite/index.php on line 4
Line 4 being
$db = new SQLiteDatabase('test.sqlite', 0666);
Any hint?
edit
You can see my phpinfo() here: http://pastebin.com/jQ7Bz0GN
The apache log is
* Restarting web server apache2 apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName ... waiting apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName
-
SteeveDroz about 12 yearsThanks, but that's not it, I've got php5-sqlite installed correctly. Nice try, though!
-
Andrejs Cainikovs about 12 yearsHave you defined your own
SQLiteDatabase
class? Asking this because for constructingSQLiteDatabase
normally is being used this function -sqlite_open()
. What will happen if you just replaceSQLiteDatabase
withsqlite_open
? -
SteeveDroz about 12 yearsHey! No, I didn't create my own SQLiteDatabase class.
-
Andrejs Cainikovs about 12 yearsThen just try to replace
SQLiteDatabase
withsqlite_open
. -
SteeveDroz about 12 yearsDoesn't work either:
Fatal error: Call to undefined function sqlite_open()
. -
Andrejs Cainikovs about 12 yearsWell, in this case
php5-sqlite
is present on your system, butphp
won't load it. I would try withsudo apt-get install --reinstall php5-sqlite
and thenservice apache2 restart
. Another thing - can you please post somewhere phpinfo? -
SteeveDroz about 12 yearsI just tried, it's no good. Is php5-sqlite supposed to appear in the phpinfo? Because it doesn't.
-
Andrejs Cainikovs about 12 yearsYes, all PHP modules should appear there, including
sqlite
. Can you please post it somewhere, along with Apache logs when you restart server viaservice apache2 restart
? -
SteeveDroz about 12 yearsI edited my question.
-
Andrejs Cainikovs about 12 yearsUpdated my answer. In short - your code should work. If it's not - restart your server and/or machine. Perhaps there were some misconfiguration issues during/after your system upgrade.
-
SteeveDroz about 12 years
-
Andrejs Cainikovs about 12 yearsUpdated. Personally, I would recommend you to go with solution 1. Your first step would be to replace
new SQLiteDatabase('test.sqlite', 0666);
withnew SQLite3('test.sqlite');
, and then resolve all class incompatibilities. -
Curious Apprentice about 12 yearsIs not it possible to remove sqlite an reinstall ?!.
-
SteeveDroz about 12 yearsThank you, thank you very much. The next error is
Fatal error: Call to undefined method SQLite3::queryExec()
, which is solved with what you said about class compatibility. @AndrejsCainikovs, you truely deserve your reward! Thanks for the long search. -
Andrejs Cainikovs about 12 years@Oltarus: I'm glad it helped. Welcome!
-
SteeveDroz about 11 yearsThank you. I figured it out. The one I still can't understand is why they changed
SQLiteDatabase::singleQuery
toSQLite3::querySingle
, I'm still mad at them because I have to check every time, I'm unable to remember which is which. -
trejder about 11 yearsOh, I believe that it is not them (the PHP team) who did the change. I think they've just wrote a new set of wrappers over new version of
sqlite
lib. You should go to sqlite.org to blame original SQLite's author! :]