News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

Duplicate entry '0' for key 'PRIMARY'

Started by dabigben, July 20, 2017, 10:50:36 AM

Previous topic - Next topic

dabigben

Hi,

after a bug from my web host, my mysql database has been shrank from 200mb to 2mb, then I uploaded a save and I found out a problem with a topic, everytime I want to add a reply, or modify a reply, I get this message :

Duplicate entry '0' for key 'PRIMARY'
Fichier: /home/xxxxx/public_html/Sources/Subs-Post.php
Ligne: 1831


Note : Il est possible que votre base de données nécessite une mise à jour. Actuellement, la version des fichiers du forum est SMF 2.0.12, alors que votre base de données est en version SMF 2.0.4. L'erreur signalée ci-dessus pourrait peut-être corrigée en exécutant la dernière version de upgrade.php.


The part in french says that maybe I should update my smf mysql database which is in version 2.0.4, to fit with my smf files which are in version 2.0.12, but I have no idea how to update it, I have searched the admin panel in vain.

Maybe it's not the real source of the problem, I have tried every repair options, even in phpmyadmin, empty buffers and all I could find.

I know what reply got the same PRIMARY key '0' but I can't delete it cause when I do I get this error.  The involved reply was actually the last one of the topic but it became the first one getting the '0' primary key.

Maybe the problem occurs with some other topic but I didn't see so far.

I don't know what to do else, can you help me please ?

Thanks.

And sorry for my english, I hope it's understandable  ;D

vbgamer45

What does it say in the forum error log?
Any more of the message?
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Sir Osis of Liver

The database version is not important, you do not have to update the database.  How did you make the db backup, how did you restore it?
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

dabigben

I saved the database using phpmyadmin, and when the db problem appeared, I attemped to upload it the same way but everytime I tried phpmyadmin didn't make it, so I asked the host support and they uploaded it themselves. Maybe they used command line the wrong way lol.

When I look at the error log (using the admin panel, is that ok ?), there is not too much things, only this :

https://www.xxx.fr/index.php?PHPSESSID=8abt0pp1s63egtqbpm1avi58f3&scheduled=task;ts=1497222000
Duplicate entry '0' for key 'PRIMARY'
Fichier: /home/xxx/public_html/Sources/ScheduledTasks.php
Ligne: 140


And I just find out a new user has subscribed and its db record took the ID '0' too ! I was the first user of the forum and I have the ID '1' (lol, not fair). I can't even delete this user. So it seems that every new record to the db takes the ID '0' and whatever this is it can't be deleted.

Actually I also can't create new topic either cause it displays the same primary key error.

Probably a little problem somewhere that blocks everything.

Thanks.

Sir Osis of Liver

There should never be an id_member = 0.  Did you run all table maintenance options in phpmyadmin on entire database?  Looks like your host may have screwed up the import.

Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

dabigben

#5
I just used the "repair" option over all the tables of the db but it doesn't change anything. I assume you're right about the host, I will have to struggle again with the support cause the import through phpmyadmin doesn't execute correctly. I'm annoyed I thought maybe it was nothing.

I hope the problem is not in my backup  :-\

Thanks for you're help, I'll pass by here when I'll succeed.

shawnb61

Sounds like an increment setting was lost on the export/import?

I would confirm they're there in the export file...  If not, it's a do over.   
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

richardwbb

I've had this error too. I've been able to fix it with; stackoverflow. I've had to use the mysql prompt, so for a share hosted forum, this will need a local installation of Apache-php-Mysql. I'm trying to be helpful, your mileage might vary.
If my post in this topic looks ambiguous to you, then I'm with Murphy's law and General Stupidity. In other words, trial and error.

dabigben

Quote from: shawnb61 on July 20, 2017, 08:36:13 PM
Sounds like an increment setting was lost on the export/import?

I would confirm they're there in the export file...  If not, it's a do over.

Is it possible that phpmyadmin export failed and missed to record the increment settings ? If that's the case I have older backups.

dabigben

#9
Quote from: richardwbb on July 21, 2017, 04:52:19 PM
I've had this error too. I've been able to fix it with; stackoverflow. I've had to use the mysql prompt, so for a share hosted forum, this will need a local installation of Apache-php-Mysql. I'm trying to be helpful, your mileage might vary.

What should I type in the prompt please ? I have backups so I can try something without fear.

shawnb61

Quote from: dabigben on July 23, 2017, 07:11:09 AM
Is it possible that phpmyadmin export failed and missed to record the increment settings ? If that's the case I have older backups.

Yes.  If an older backup works,  I'd say go for it.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

richardwbb

Quote from: dabigben on July 23, 2017, 07:12:15 AM
What should I type in the prompt please ? I have backups so I can try something without fear.

You can use 'USE <name-of-database>;', then 'DESCRIBE <table-name>. But I hope for you that someone else will be so kind to help you find what <table-name>, you should look in to.

Here is an example output of my database, of the tabel named; smf_messages;

mysql> DESCRIBE smf_messages;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| id_msg          | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| id_topic        | mediumint(8) unsigned | NO   | MUL | 0       |                |
| id_board        | smallint(5) unsigned  | NO   | MUL | 0       |                |
| poster_time     | int(10) unsigned      | NO   |     | 0       |                |
| id_member       | mediumint(8) unsigned | NO   | MUL | 0       |                |
| id_msg_modified | int(10) unsigned      | NO   |     | 0       |                |
| subject         | varchar(255)          | NO   |     | NULL    |                |
| poster_name     | varchar(255)          | NO   |     | NULL    |                |
| poster_email    | varchar(255)          | NO   |     | NULL    |                |
| poster_ip       | varchar(255)          | NO   | MUL | NULL    |                |
| smileys_enabled | tinyint(4)            | NO   |     | 1       |                |
| modified_time   | int(10) unsigned      | NO   |     | 0       |                |
| modified_name   | varchar(255)          | NO   |     | NULL    |                |
| body            | text                  | NO   |     | NULL    |                |
| icon            | varchar(16)           | NO   |     | xx      |                |
| approved        | tinyint(3)            | NO   | MUL | 1       |                |
+-----------------+-----------------------+------+-----+---------+----------------+
16 rows in set (0.00 sec)

mysql>


I've looked in to the output you have posted in this topic, and I do not understand which table is being referred to. However, you can see above, there is such a thing as a 'primary key' ["Key = PRI']. What you have pasted, [Duplicate entry '0' for key 'PRIMARY'], is what MySQL is saying, php is showing on screen and what Simple Machines support will understand better then I do.

But I did a search in my notes, all I have written down is the following;
#[http://codeblow.com/questions/mysql-reorder-totally-reset-auto-increment-primary-key/]
SET @count = 0; 
UPDATE `<name-of-table>` SET `<name-of-table>`.`id` = @count:= @count + 1;
ALTER TABLE `<name-of-table>` AUTO_INCREMENT = 1;


But please be careful, you wrote that you have backups, do not attempt things on a live forum database. You really need to have a running Apache/php/MySQL of it's own. Also note that the field names in the MySQL command output above, isn't the same as "PRIMARY KEY (`id`)", that is what every table in the database, will show, so you really need to find a way, to know which table, MySQL showed an error. Lastly, the "UPDATE" example I've posted above, is what as far as I know, only can be done at the MySQL prompt, to recreate what MySQL uses as an id, which is a counter, that increments automatically. I've never found, where and how that was written in the MySQL database. Maybe exporting your database and import it again will help. It also can make things worse. That is why I insist to not use the live database.

I believe what Sir Osis of Liver has written is a good clue. you should also be aware, that every person that has replied to your question, has written helpful things. It is a strong advice to not only try to understand what I am trying to explain to you. Lastly, that I was able to fix my database doesn't mean it will fix your database too.
Quote from: Sir Osis of Liver on July 20, 2017, 05:51:29 PM
There should never be an id_member = 0.
If my post in this topic looks ambiguous to you, then I'm with Murphy's law and General Stupidity. In other words, trial and error.

dabigben

Thank you very much.

On my request, my host provider raised the phpmyadmin upload limit then I was able to upload my last backup which was not failed. So the problem was coming from their manual upload process lol.

Thank you everyone..

Advertisement: