I tried using the Merge system to get the contents of a MyBB MySQL 1.6 to a MyBB SQLite 3 forum.
But unfortunately I get this error when I start running the merge system.
Checking database details... success.
Creating mybb_trackers table.
Creating fields for tracking data during the Merge process (This may take a while)...
MyBB SQL Error
MyBB has experienced an internal SQL error and cannot continue.
SQL Error:
-
Query:
SQLSTATE[HY000]: General error: 1 near "KEY": syntax error
I input the details of the MySQL database correctly. I am trying to import all the data into a new forum running with SQLite 3 engine. Is it possible? Why this error is occurring?
AFAIK the merge system only supports MySQL. Moreover it is not supposed to convert a forum to a different database system.
So it is not possible to convert MySQL forum to SQLite 3 forum? I tried using a generic ruby gem: sequel to convert from MySQL to SQLite 3 and that works to display the forum, but whenever I try to create a topic or anything else I get error.
Would be glad to know if it can be done. I need SQLite 3 database support because I have to pay extra for MySQL support at my host.
Hi Nathan, I have tried the script for converting MySQL to SQLite, but unfortunately MyBB gives errors whenever I try to create a new thread or post replies. I am able to view the forum and also log in to admin panel. Please check my other thread for information.
Here is the error I get with the SQLite 3 database which I had converted from MySQL using the script:
MyBB SQL Error
MyBB has experienced an internal SQL error and cannot continue.
SQL Error:
-
Query:
SQLSTATE[23000]: Integrity constraint violation: 19 mybb_threads.closed may not be NULL
I don't get this on a newly installed SQLite 3 installation. Only on a script converted SQLite 3 one. That is why I thought it might be nice to be able to "merge" the old data into a new installation with SQLite 3 MyBB.
All running on 1.6 version.
I tried my best to solve this, but even after writing my own script to import the MySQL data into a fresh installation of MyBB SQLite 3 database, I failed. I get so many SQL errors.
How come MyBB SQLite 3 fresh installation is error free? Why even when manually using select statements to insert the data into SQLite 3 database is giving so many issues?
The best solution is to stick with MySQL really. You will find a lot of things and plugins won't work with any other DB. Because no one does any testing with them ever.
If you absolutely have to use SQLite, use the original CREATE statements MyBB has for SQLite; then only import the data (the INSERTs). It should be relatively painless to do with MyBB's backup dumps.
Expect to run into issues, even when not converting.
(2012-09-05, 05:32 PM)frostschutz Wrote: [ -> ]The best solution is to stick with MySQL really. You will find a lot of things and plugins won't work with any other DB. Because no one does any testing with them ever.
If you absolutely have to use SQLite, use the original CREATE statements MyBB has for SQLite; then only import the data (the INSERTs). It should be relatively painless to do with MyBB's backup dumps.
Expect to run into issues, even when not converting.
I wrote a (Python) script which does exactly this yesterday. I preserved the original SQLite database structure and simply imported the data over.
MyBB still responds with a bunch of SQL related errors (mainly integrity constraint violation errors and syntax errors) when I try to create a new topic or post or when viewing threads.
Seems SQLite support should be abandoned if no testing is done on it. Pity, because on smaller forums with lesser activity, MySQL is really overkill (also it costs more on my host). I preferred to avoid MySQL whenever I can because SQLite makes it easier to take backups and is a light weight library.
But going by what you say, SQLite database back end does not undergo much testing?
(2012-09-06, 02:26 AM)harishankar Wrote: [ -> ]Pity, because on smaller forums with lesser activity, MySQL is really overkill (also it costs more on my host).
Sounds like the host would suck anyway. PHP requires resources too, probably even more so with SQLite instead of PHP since SQLite is not a persistent process that could do some real caching.
SQLite is a great database but it's really just not suited to PHP, and the PHP-SQLite backends is one of the worst out there, you have to write a ton of compatibility code in PHP to make it work semi-reliably.
For example look at Drupal's includes/database/prefetch.inc which is really there only for SQLite. It does things like fetching all rows for every query because PHP-SQLite driver locks up otherwise.
(2012-09-06, 09:54 AM)frostschutz Wrote: [ -> ] (2012-09-06, 02:26 AM)harishankar Wrote: [ -> ]Pity, because on smaller forums with lesser activity, MySQL is really overkill (also it costs more on my host).
Sounds like the host would suck anyway. PHP requires resources too, probably even more so with SQLite instead of PHP since SQLite is not a persistent process that could do some real caching.
SQLite is a great database but it's really just not suited to PHP, and the PHP-SQLite backends is one of the worst out there, you have to write a ton of compatibility code in PHP to make it work semi-reliably.
For example look at Drupal's includes/database/prefetch.inc which is really there only for SQLite. It does things like fetching all rows for every query because PHP-SQLite driver locks up otherwise.
No, the host is one of the best I've found, anyway, nearlyfreespeech.net.
Anyway, I think SQLite version 3 is a great db too, too bad PHP has such poor back-end for SQLite. But I created a blog software on my own with PHP and SQLite (with PDO) and it appears not too bad actually.
Actually I've now managed to convert the database using the method of simply importing the data and settings and not the entire database.
I will share my script (After testing) if anybody is interested.
Looks like I spoke too soon. Now I get a bunch of "database locked" errors randomly with MyBB/SQLite.
I think I will use MySQL and be done with it. Lots of problems
