Registration error - Automatic User-ID "0"

Started by Sofian Schattenweber, August 04, 2020, 08:26:32 AM

Previous topic - Next topic

Sofian Schattenweber

Hello,

I'm currently administering and maintaining a small forum. Now we have the following problem:

Any new user registration gets the user ID "0" from the system. I can neither delete the users nor edit their profile. Nor can the users activate or use their accounts.

Our forum runs on version 2.0.15 - Before I update it to 2.0.17, I would like to localize and fix this problem.

Any thoughts?




vbgamer45

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

Arantor

Sounds like the smf_members table has somehow lost its auto_increment tag on the id_member column.

Hersfeld

Quote from: Arantor on August 04, 2020, 08:42:50 AM
Sounds like the smf_members table has somehow lost its auto_increment tag on the id_member column.

That's exactly what happened but we have no idea how this could happen.

We checked a little more in detail and seems like also all indices for smf_members table are lost.

Is there any sort of repair script for the database or do we need to fix this manually? If this is the case I assume it would be the best to install a new instance of smf and import the data from the old faulty database into the new one? Or is there a better way to do this?

Kindred

hooo boy.....   

did the memebrs table lost the member ID column or did that column just lose the index and autoincrement assignment?

If the first, then you're in for a world of hurt... because the member ID is used as a link/association to dozens of other tables.
You best bet would be to find a backup and restore it...

if the second, then just turn autoincrement on for that column
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Hersfeld

The column just lost index and auto_increment but all other columns from members table that used to have an index also lost it. I haven't checked the other tables if they maybe also lost any index.

Hersfeld

Since most of the tables have lost their primary keys and index I have exported the data from the old database. Currently I try to import the data in the new database. But there are a lot of primary key violations, that need to be fixed manually. At the moment I am stuck with the smf_log_boards table. Since there are a lot of primary key violations in this one I asked myself what the table is for. Unfortunately I couldn't find any documentation about what the tables are for. Is there any documentation out there I wasn't able to find?

Arantor

It's part of the log of who has read what in the forum.

I'd assume PK violations can be ignored.

Hersfeld

There is not option in phpMyAdmin to ignore the PK violations.

If its just the log of who read what in the forum I assume the only impact of truncating the table would be everything will be unread for the users?

Is there any documentation about the database structure out there?

Arantor

Ignoring PK violations: drop the PK, do the import of data, readd the PK.

No one has documented the schema.

Hersfeld

I just tried this but when readding the PK I get the error "duplicate entry for primary key"

Arantor

Hrm, that used to work. Truncating the table is an inconvenience to users but not a fatal one.

Hersfeld

I deleted all duplicate PKs and only left the ones with the highest id_msg using:


DELETE lb1
FROM smf_log_boards lb1
INNER JOIN smf_log_boards lb2
WHERE
lb1.id_msg    < lb2.id_msg    AND
lb1.id_member = lb2.id_member AND
lb1.id_board  = lb2.id_board
;


After this there where still a few duplicates where even id_msg where the same.
So I had to export the table, open the file in VS Code, sort the lines using the sort line extension und then remove all duplicates by

Pressing Control+F
Toggle "Replace mode"
Toggle "Use Regular Expression" (the icon with the .* symbol)
In the search field, type ^(.*)(\n\1)+$
In the "replace with" field, type $1
Click the Replace All button

Now I could import the table and was able to successfully add back the PK.

Hersfeld

Now that I was able to upload the database backup successfully there is only one thing left.

A part of the forum statistics have been reset. I have attached 3 Screenshots that show the same.

Is there any script to run that will fix this or did I missed somethin in the database?

Kindred

Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Hersfeld

Thanks, I knew there had to be something like this. Just ran it and statistics are now as they are supposed to be.

Advertisement: