1 Topic by buze

  • buze
  • Member
  • Offline
  • Registered: 2005-10-18
  • Posts: 11

Topic: Converting from sqlite to MySQL

My board has almost reached 40k posts, and the sqlite DB is about 130MB. I decided to "upgrade" to MySQL to see if it improved performance and/or fixed the "long post edit hang" problem I've been having..

I managed to dump the sqlite DB, and munch it to make MySQL accept it (after creating the SQL database with the "collate latin1_bin" ! otherwise search indexes are broken) and the new install of punbb can browser the new database succesfuly, but I can't post any new posts, it tries to re-use the "zero" topic number instead of using the last one used.

Does anyone managed to do this conversion ?

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: Converting from sqlite to MySQL

Maybe you can try running a table repair? I'm not sure it'll update the indexes, but it might.

3 Reply by buze

  • buze
  • Member
  • Offline
  • Registered: 2005-10-18
  • Posts: 11

Re: Converting from sqlite to MySQL

thanks I will try that, I think last time it was complaining some other problems as well. I'm busy doing another (slow!) mysql import and I'll report.

There is already a bug in sqlite .dump command, apparently if you have a \ at the end of a string it fails to escape it properly so you get a string like '... \' whuch confuses mysq-hell no end big_smile

4 Reply by buze

  • buze
  • Member
  • Offline
  • Registered: 2005-10-18
  • Posts: 11

Re: Converting from sqlite to MySQL

Ok, so I can convert the base to mysql, and the punbb manages to browse everything on it...

however if I try to post/edit anything, or if I try to rebuild the database I get:

Rebuilding index ? This might be a good time to put on some coffee :-)

Processing post 1 in topic 1
An error was encountered
Error: Unable to insert search index words.

The "unable to insert search word" is constant...

Any idea how to make the conversion work ?

5 Reply by buze

  • buze
  • Member
  • Offline
  • Registered: 2005-10-18
  • Posts: 11

Re: Converting from sqlite to MySQL

Bit more details with debug mode on:

Rebuilding index ? This might be a good time to put on some coffee :-)

Processing post 1 in topic 1
An error was encountered
File: /var/www/mf2/include/search_idx.php
Line: 149

PunBB reported: Unable to insert search index words

Database reported: Duplicate entry '0' for key 1 (Errno: 1062)

Failed query: INSERT INTO search_words (word) VALUES('are'),('countless'),('discussions'),('related'),('manual'),('lens'),('focus'),('picture'),('quality'),('adaptors'),('etc'),('decideed'),('create'),('friendly'),('forum'),('interested'),('try'),('forums'),('start'),('needed'),('lets'),('chatting'),('welcome')
  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Converting from sqlite to MySQL

OK, I have a feeling auto_increment isn't set on those columns. You'll need to alter the columns to add it

7 Reply by buze

  • buze
  • Member
  • Offline
  • Registered: 2005-10-18
  • Posts: 11

Re: Converting from sqlite to MySQL

Yeah thats what I'm seeing as well... I tried to re-run install.php on a clean database to have it create the table, and deleting the CREATE TABLE from the sqlite dump.. however when I re-import the data it complains immediately about "duplicate key"

I'm converting "INSERT" to "REPLACE" to see if I can finaly manage it...

8 Reply by buze

  • buze
  • Member
  • Offline
  • Registered: 2005-10-18
  • Posts: 11

Re: Converting from sqlite to MySQL

There it finaly worked. Deleted the CREATE TABLE and CREATE INDEX from the sqlite dump file, replaced the "INSERT" statements by "REPLACE" statements, converted numerics INSERTS into search_words to string literals.

I created the tables in mysql using a brand new install.php and re-imported the dump into it. I then reconstructed the indexes from the admin menu and it now all back up...

Not trivial tho !