1 Topic by pedrotuga (edited by pedrotuga 2007-11-04 20:21)

  • Registered: 2006-04-30
  • Posts: 382

Topic: Weird... i cant insert any data

I migrated a forum i had from sqlite to mysql because i moved to a server without sqlite support, i already managed to put the whole thing working, well.. almost.

I can't insert any data. It's not a user permition error, i checked that.

It's some trouble with th search index but i don't know how to fix it.

Here's the whole story:

I created a dump from my old sqlite database, then, when i was runing it on my mysql server it was throwing duplicate keys errors on the search words table. I though it was kind of weird, but i simply removed the INSERT statements for the three tables that hold the search index. I left only the CREATE table statements and plan to rebuild the index afterwords.

Now that i have the forums up again with mysql i can't rebuild the index nor create a post. This is the error i get:

Error: Unable to insert search index words.

Another strange thing... when i go the the 'rebuild search index' page it shows 0 instead of the id of the oldest post which is like 5989.

Any tips?

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Weird... i cant insert any data

Enable debug mode, paste the full error

  • Registered: 2006-04-30
  • Posts: 382

Re: Weird... i cant insert any data

PunBB reported: Unable to insert search index words

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

There is some trouble with mysql index, i think i screw up my database a little bit.

My search_words table has to indexes for the id column... is it supposed to?

phpmyadmin gives this warning:

The following indexes appear to be equal and one of them should be removed: PRIMARY, search_words_id_idx

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Weird... i cant insert any data

From install.php

            $sql = 'CREATE TABLE '.$db_prefix."search_words (
                    id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
                    word VARCHAR(20) BINARY NOT NULL DEFAULT '',
                    PRIMARY KEY (word),
                    KEY ".$db_prefix."search_words_id_idx (id)

Sounds like you're missing auto_increment, and the primary key is set incorrectly.

  • Registered: 2006-04-30
  • Posts: 382

Re: Weird... i cant insert any data

Ok... there is this error that i am not sure if is related with indexes or charsets:

INSERT INTO search_words (word) VALUES('olá'),('finalmente'),('pronto'),('novo'),('fórum'),('longo'),('periodo'),(forum),...

the words 'fórum' and 'forum' aparently violate the primary key despite being diferent.

I will try to create the database using a dump from a clean install and insert only the data . Thanks

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Weird... i cant insert any data

Did you fix both problems?

  • Registered: 2006-04-30
  • Posts: 382

Re: Weird... i cant insert any data

By the time i wrote the last message, no. The autoincrement values were messed up as well the indexes and some column proprities in almos all the tables.

But i think i fixed it all now.
I took the create table statements from a clean install ant the inserts from a mysqldump -t ( without the create table statements )

Everything appears to be working now.

This database is the most adventured one i have, it was a phorum instalarion from the begging, then i migrated it to punbb and switched it from mysql to sqlite to give it a try. Now, i am moving server again i had to migrate it back to mysql due to lack of sqlite support. Pretty amazing how everthing always works after all.

Thanks for the help everybody, i didn't even know that there was such things as debug mode or query dumps in punbb, very handy.

  • Registered: 2006-04-30
  • Posts: 382

Re: Weird... i cant insert any data

mmm... i came across another problem...
When i try to edit the forum permitions the only thing i get ( besides header and footer )
is an empty error message.

I enabled both debug mode and show queries. I don't get any error message, but this is the executed queries...

0.00086      SELECT u.*, g.*, o.logged, o.idle FROM users AS u INNER JOIN groups AS g ON u.group_id=g.g_id LEFT JOIN online AS o ON o.user_id=u.id WHERE u.id=2
0.00016     UPDATE online SET logged=1194236640 WHERE user_id=2
0.00020     SELECT * FROM online WHERE logged<1194236340
0.00027     SELECT COUNT(id) FROM reports WHERE zapped IS NULL

What can this be...?

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Weird... i cant insert any data

When you actually click the edit link, as opposed to just visiting the page?

10 Reply by pedrotuga (edited by pedrotuga 2007-11-04 20:36)

  • Registered: 2006-04-30
  • Posts: 382

Re: Weird... i cant insert any data

yes.
Can it be some token failing or so?

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Weird... i cant insert any data

I have no idea what it would be, I would need to see a screenshot.
And you haven't edited admin_forums.php, right?

  • Registered: 2006-04-30
  • Posts: 382

Re: Weird... i cant insert any data

Nop... i didn't touch it.
I also find it very weird.
I'm afraid it doesn't help so much, but here goes the screenshot.
http://img401.imageshack.us/img401/4679 … hotdr9.png

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Weird... i cant insert any data

Try uploading a fresh copy of admin_forums.php and header.php

  • Registered: 2006-04-30
  • Posts: 382

Re: Weird... i cant insert any data

will do, thanks

15 Reply by pedrotuga (edited by pedrotuga 2007-11-04 21:38)

  • Registered: 2006-04-30
  • Posts: 382

Re: Weird... i cant insert any data

ahh... it couldn't work but i tried anyway.
It has to be something with the database, cache or something. I forgot to mention that i moved to a new server with a new domain.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Weird... i cant insert any data

Try removing all the cache files then
What does the URL look like when you click that link?

  • Registered: 2006-04-30
  • Posts: 382

Re: Weird... i cant insert any data

I did that already.... it looks like this wont be easy to solve... :s
it looks like this
http://mydomain.com/admin_forums.php?edit_forum=30

I can see the forum list in the admin panel and i can click and get to the edit forum page. But when i submit i get that page i showed you.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Weird... i cant insert any data

I can't tell any more about it without actually having access to the forum. Email me, smartys at this domain.