I have Roundcube working with an sqlite 3.x database. I'm posting here for lack of a better idea - the approach was confirmed working for both the stable 0.2.x version and yesterday's SVN checkout.
- Download the experimental pdoSqlite MDB2 driver from Marin Ivanov, as linked in the PEAR forums (http://pear.php.net/bugs/bug.php?id=6907#1236520724).
- Extract the driver contents under 'Driver' of your existing MDB2 directory.
- Patch rcube_mdb2.php using
- Change your database driver from 'sqlite' to 'pdoSqlite' (db_dsnw in db.inc.php)
I cannot comment on the stability of the experimental driver - I guess it's not available from any standard distribution channel for a reason. It's working for me so far though.
Mamash, do You know if a patch for version "0.3-stable" is available?
In errorlog I find this:
[14-wrz-2009 15:14:28] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: DELETE FROM session WHERE sess_id='p76079uk0csekjqml8sv15b7e1']
[Native code: HY000]
[Native message: unable to open database file]
[14-Sep-2009 15:14:28 +0200]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: DELETE FROM session WHERE sess_id='p76079uk0csekjqml8sv15b7e1'] [Native code: HY000] [Native message: unable to open database file] in /var/www/roundcubemail-0.3-stable/program/include/rcube_mdb2.php on line 630 (POST /poczta2/?_task=&_action=login)
[14-wrz-2009 15:14:28] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: INSERT INTO users
(created, last_login, username, mail_host, alias, language)
VALUES (now(), now(), 'user', 'localhost', '', 'pl_PL')]
[Native code: HY000]
[Native message: unable to open database file]
[14-Sep-2009 15:14:28 +0200]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: INSERT INTO users (created, last_login, username, mail_host, alias, language) VALUES (now(), now(), 'user', 'localhost', '', 'pl_PL')] [Native code: HY000] [Native message: unable to open database file] in /var/www/roundcubemail-0.3-stable/program/include/rcube_mdb2.php on line 630 (POST /poczta2/?_task=&_action=login)
[14-wrz-2009 15:14:28] MDB2 Error: unknown error (-1): lastInsertID: [Error message: Could not get last insert ID]
[Last executed query: INSERT INTO users
(created, last_login, username, mail_host, alias, language)
VALUES (now(), now(), 'user', 'localhost', '', 'pl_PL')]
[Native code: 00000]
[Native message: unable to open database file]
[14-Sep-2009 15:14:28 +0200]: PHP Error: Failed to create new user in /var/www/roundcubemail-0.3-stable/program/include/rcube_user.php on line 452 (POST /poczta2/?_task=&_action=login)
[14-Sep-2009 15:14:28 +0200]: PHP Error: Failed to create a user record. Maybe aborted by a plugin? (POST /poczta2/?_task=&_action=login)
[14-wrz-2009 15:14:28] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: DELETE FROM session WHERE sess_id='p76079uk0csekjqml8sv15b7e1']
[Native code: HY000]
[Native message: unable to open database file]
[14-Sep-2009 15:14:28 +0200]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: DELETE FROM session WHERE sess_id='p76079uk0csekjqml8sv15b7e1'] [Native code: HY000] [Native message: unable to open database file] in /var/www/roundcubemail-0.3-stable/program/include/rcube_mdb2.php on line 630 (POST /poczta2/?_task=&_action=login
That's weird, I have the same modification running on my system under a trunk version from svn (i.e. even newer than 0.3-stable). Did the patch apply cleanly on your system?
I'll revisit and try on a vanilla 0.3-stable instance on my system.
There is no problem with the patch. How did you init the database? Sounds like you might have used 'sqlite -init' whereas you should have used 'sqlite3 -init' instead (for SQLite 3.x).
Anyway, this is an improved patch for 0.3-stable that adds SQLite 3.x support to the installer too. To install a clean RoundCube 0.3 using SQLite 3.x:
- Download the experimental pdoSqlite MDB2 driver from Marin Ivanov, as linked in the PEAR forums (http://pear.php.net/bugs/bug.php?id=6907#1236520724).
- Extract the driver contents under 'Driver' of your existing MDB2 directory.
- In the Roundcube root directory, apply the aggregate , e.g.:
patch -p0 < /path/to/rcube-0.3-stable-sqlite3.patch
- Initialize the database, e.g.:
sqlite3 -init SQL/sqlite.initial.sql /path/to/dbfile
- Run the installer and use the new 'SQLite 3.x' option for database storage
Of course I've used sqlite3, and I've used tested configuration from version 0.2.2:
$rcmail_config['db_dsnw'] = 'pdoSqlite://./sqlite.db';
File sqlite.db is located in root roundcube directory, is owned by apache user and has 0640 permissions.
I've also tried '?mode=0646' and '?mode=0640' (with the same or different filesystem permission mask).
Now error appears before login screen, not after entering login and password like last time.
In log I find this:
[16-Sep-2009 17:59:02] MDB2 Error: not found (-4): unable to find package 'MDB2_Datatype' file 'MDB2/Datatype.php'
[16-Sep-2009 17:59:02 +0200]: DB Error: MDB2 Error: not found Query: unable to find package 'MDB2_Datatype' file 'MDB2/D
atatype.php' in /var/www/roundcubemail-0.3-stable/program/include/rcube_mdb2.php on line 630 (GET /poczta3/)
My MDB2 pdoSqlite driver: pdoSqlite.php,v 1 2009/03/07 00:38:39
Quote from: jpiedel;21360Of course I've used sqlite3, and I've used tested configuration from version 0.2.2:
$rcmail_config['db_dsnw'] = 'pdoSqlite://./sqlite.db';
File sqlite.db is located in root roundcube directory, is owned by apache user and has 0640 permissions.
I've also tried '?mode=0646' and '?mode=0640' (with the same or different filesystem permission mask).
Now error appears before login screen, not after entering login and password like last time.
In log I find this:
[16-Sep-2009 17:59:02] MDB2 Error: not found (-4): unable to find package 'MDB2_Datatype' file 'MDB2/Datatype.php'
[16-Sep-2009 17:59:02 +0200]: DB Error: MDB2 Error: not found Query: unable to find package 'MDB2_Datatype' file 'MDB2/D
atatype.php' in /var/www/roundcubemail-0.3-stable/program/include/rcube_mdb2.php on line 630 (GET /poczta3/)
My MDB2 pdoSqlite driver: pdoSqlite.php,v 1 2009/03/07 00:38:39
Same Problem...
yeah it works.
check the permissions on file:
ROUNDCUBE-Folder/program/lib/MDB2/Driver/Datatype/pdoSqlite.php
if not changed there on 770. this doesn't work.
change it to 666 and RoundCube runs on SQlite 3
thx
Ah, yes, the most obvious trivial problem - the permissions. Depends on how you execute your scripts etc. Glad it's working for other people too.
Hi everybody,
I tried to apply the sqlite3 patches to my RC installation (since I had some problems with sqlite2). No matter which RC version I tried (0.3.1 and trunk-r3437) the configuration stops with this result
Check configured database settings
DSN (write): OK
DB Schema: NOT OK(Database not initialized) But my database file is initialized (using the sqlite3 binary)!?
I double checked all file permissions but they seem ok to me.
System specs:
Windows Home Server
Apache 2.2.15
PHP 5.3.0
hMailServer 5.3.2
Just don't know where to check or what to change :(
Any help very much appreciated :)
Cheers,
Seppel
Do you have the experimental pdoSqlite.php driver installed? Also, I haven't tried this patch under PHP 5.3 yet, so can't confirm on that (will try soon).
Quote from: mamash;26826Do you have the experimental pdoSqlite.php driver installed? Also, I haven't tried this patch under PHP 5.3 yet, so can't confirm on that (will try soon).
The one you've described in your step-by-step instruction? Yes - I have.
For the trunk version I had to change this line by hand:
+ 'PgSQL' => 'pgsql', 'SQLite' => 'sqlite', 'SQLite 3.x' => 'pdoSqlite');
Everything else worked like a charme ;)
I just enabled the log messages. When trying to login, I get this:
Warning: PDO::query(): SQLSTATE[HY000]:
General error: mode must be an integer in \program\lib\MDB2\Driver\pdoSqlite.php on line 572
DB Error in \program\include\rcube_mdb2.php (639): MDB2 Error: unknown error Query: _doQuery:
[Error message: Could not execute statement] [Last executed query: SELECT vars, ip,
UNIX_TIMESTAMP(changed) AS changed FROM session WHERE sess_id = 'rh276ns6vts0ipcbrch3vulde4']
[Native code: 00000] Do you have any idea whats causing this?
Thanx for your help :)
I did the patching and installed the drivers and whatnot.. but on the installer page, i see the following:
Check configured database settings
DSN (write): OK
DB Schema: NOT OK(Cannot read the schema file: ../SQL/pdoSqlite.initial.sql)
I cannot find this pdoSqlite.initial.sql
and it does not seem to like the database i made with
sqlite -init SQL/sqlite.initial.sql sqlite.db
-db
I did the patching and installed the drivers and whatnot.. but on the installer page, i see the following:
Check configured database settings
DSN (write): OK
DB Schema: NOT OK(Cannot read the schema file: ../SQL/pdoSqlite.initial.sql)
I cannot find this pdoSqlite.initial.sql
and it does not seem to like the database i made with
sqlite -init SQL/sqlite.initial.sql sqlite.db
Im getting this error too:
[05-May-2010 19:42:16 +0200]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: SELECT vars, ip, UNIX_TIMESTAMP(changed) AS changed FROM session WHERE sess_id='ldnu9ca02d5mtrj1ngtp341e03'] [Native code: 00000] in /usr/local/apache2/htdocs/roundcube/program/include/rcube_mdb2.php on line 630 (GET /)
[05-May-2010 19:42:37] PHP Warning: PDO::query(): SQLSTATE[HY000]: General error: mode must be an integer in /usr/local/apache2/htdocs/roundcube/program/lib/MDB2/Driver/pdoSqlite.php on line 572
[05-May-2010 19:42:37] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: SELECT vars, ip, UNIX_TIMESTAMP(changed) AS changed
FROM session
WHERE sess_id='ldnu9ca02d5mtrj1ngtp341e03']
[Native code: 00000]
[05-May-2010 19:42:37 +0200]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: SELECT vars, ip, UNIX_TIMESTAMP(changed) AS changed FROM session WHERE sess_id='ldnu9ca02d5mtrj1ngtp341e03'] [Native code: 00000] in /usr/local/apache2/htdocs/roundcube/program/include/rcube_mdb2.php on line 630 (GET /)
-db
patch doesn't work with new versions of roundcube
Try removing the patch part where fromunixtime() is being patched - the two lines that start with "case 'pdoSqlite':". I don't think that's needed on the latest versions anymore. I have been running this setup ever since, right now on trunk from ~ March this year, just didn't have time to try to upgrade to the latest yet.
Updated instructions and patches for roundcube-0.7:
Required files:- 0001-Fix-broken-SQL-statement.patch (http://pastebin.com/etiT0Zzt)
- MDB2_Driver_pdoSqlite.tar.bz2 (http://downloads.metala.org/pear/MDB2/MDB2_Driver_pdoSqlite.tar.bz2)
- 0003-Fix-a-typo-in-pdoSqlite-driver-for-MDB2.patch (http://pastebin.com/01VigNtz)
- 0005-Add-support-for-pdoSqlite-to-roundcube.patch (http://pastebin.com/R3Bn1WFV)
Instructions:- Download the required files
- Patch SQL/sqlite.initial.sql and SQL/sqlite.update.sql with 0001-Fix-broken-SQL-statement.patch
- Extract the MDB2 Driver (MDB2_Driver_pdoSqlite.tar.bz2) to program/lib/MDB2/Driver
- Patch program/lib/MDB2/Driver/pdoSqlite.php with 0003-Fix-a-typo-in-pdoSqlite-driver-for-MDB2.patch
- Copy SQL/sqlite.initial.sql to SQL/pdoSqlite.initial.sql
- Copy SQL/sqlite.update.sql to SQL/pdoSqlite.update.sql
- Patch program/include/rcube_mdb2.php with 0005-Add-support-for-pdoSqlite-to-roundcube.patch
- Set your database driver to pdoSqlite in config/db.inc.php
Have you submitted a ticket for this problem?
Quote from: skaero;37751Have you submitted a ticket for this problem?
It's already been submitted and fixed 1488255 (http://trac.roundcube.net/ticket/1488255)
Applied this patches against 0.7.1.
No chance, won't work.
Error:
DATABASE ERROR: CONNECTION FAILED!
Unable to connect to the database!
Please contact your server-administrator.
sqlite2 is not in the CentOS6 repositiory, is it?
Thanks a lot
Markus
Errormessage:
MDB2 Error: not found Query: unable to find package 'MDB2_Datatype' file 'MDB2/Datatype.php' in /home/www/roundcubemail-0.7.1/program/include/rcube_mdb2.php on line 717 (GET /roundcubemail-0.7.1/)
Thanks to andrew.fyfe for the corrections. I'm migrating my mail server, and starting with 0.7.2 from scratch, and still works for me just fine.
Any plans to get this patch into the SVN mainstream? lack of sqllite is a showstopper for me. I can't run full SQL on a NAS.. Sqllite2 is being removed from many distros now.
I tried to apply these patches, but it didn't work for me... It's not really clear what's been fixed already, what's part of the patch etc.
This problem was already fixed (see ticket #1488255 http://trac.roundcube.net/ticket/1488255) if your having problems with the latest version you should open up a new ticket.
Quote from: SKaero on May 08, 2012, 09:22:57 PM
This problem was already fixed (see ticket #1488255 http://trac.roundcube.net/ticket/1488255) if your having problems with the latest version you should open up a new ticket.
Are you saying Sqlite3 support is in the latest SVN? So what bits of this do I need and no longer need to do?
I have switched to Debian Wheezy from Squeeze, and all I know, is roundcube no longer works, and it's being a right old faff to get it going...
Sqlite is supposedly supported, I'm not sure what version is supported (I'm not a RoundCube developer) but the changes in this thread have already been applied to the RoundCube core.
SQlite has been supported for a long time, but only in the 2.x version (which is quite old and may not be supported at several places). I'm pretty sure nothing has happened on this front at RC, and since it depends on the MDB2 Pear modules for its database drivers, and there is no official MDB2 SQLite 3 driver yet (as far as I know...).
I see what you mean, I found this ticket http://trac.roundcube.net/ticket/1488255 with the version 3 problem. My guess is you could patch RC with the bottom 3 files listed in the first post but I think some of the code has changed.
mgillespie: i think you're looking for this: http://trac.roundcube.net/ticket/1488332
The bad news is that it doesn't look like the RC devs have any plans to write their own SQLite3 support (as there doesn't seem to be an official MDB2 driver for SQLite3 coming, and using the PHP sqlite3 extension or the PDO driver would require adding some substantial code to RC). So the current SQLite support in RC doesn't have much sense, as SQLite2 is being phased out everywhere.
The good news is that the unofficial driver (from http://pear.php.net/bugs/bug.php?id=6907) and my patches still work fine on the latest RC release. I'm using it on my live system even.