Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Database error during 2.0.18 upgrade

Started by AstroM, March 09, 2021, 07:52:28 AM

Previous topic - Next topic


Hello all folks!

Spending days trying to update SMF now and find no answer. My service provider changed to php 7.0 so our old forum that was running smf 1.1.14 died.
Thought it was just a matter of update files and so on. NOT!

I finally got things to start working using Xampp version 5.6.36 and phpMyAdmin. Imported my database and things looked fine. Next, used upgrade to upgrade to version 1.1.21.
That worked perfect. Started the 2.0.18 upgrade and got issues immediately. But it was that common old theme issues, so I got back to version 1.1.21 to reset all themes to default.
Started upgrade to 2.0.18 again. And it actually started the upgrade. And there was lots of joy and happiness.
About 74% into the first table name change i get a !!ERROR #1075.  No more info at all!

Checked on the web and its something about two keys and auto increment. I don't know where that extended error log is suppose to be, but I cant find anything.
Does the database converter starts from top in the table list or....

Guess its a database thing since you don't actually support MariaDB. 
Here's some server info:

    Server: via TCP/IP
    Server type: MariaDB
    Server connection: SSL is not being used Documentation
    Server version: 10.1.32-MariaDB - [nonactive] binary distribution
    Protocol version: 10
    User: [email protected]
    Server charset: cp1252 West European (latin1)
     Apache/2.4.33 (Win32) OpenSSL/1.0.2o PHP/5.6.36
    Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503
    PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
    PHP version: 5.6.36
    phpMyAdmin version: 4.9.7

Any help would be nice. Been running my SMF forum since 2006 and would hate to change to something different just because database incompatibility.



There is a problem with one of your existing table definitions.  The 1075 description is "ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key".

I would suggest doing a db export - structure only - and scan for a table definition that violates that rule.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp


Thank's for your answer.

I find it somewhat strange that it worked fine with earlier versions, and also, shouldn't it be any error indication on which table the error was found in??



Yes, upgrader messages can sometimes be... opaque...

If you need help looking at the structure, you can share it here (structure, not data) or pm it to me.

Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp


Hello again!

Thanks for taking time on my issue. I (probably) attached two database structure files.
One from SMF 1.1.14 and one from 1.1.21. They do both work fine running in localserver with Xampp.



Most of that looks OK, except for the members table that has numerous issues, especially around keys... 

Could you take a couple screenshots of the structure of the members table?  One from the top showing id_member, and one of all the indexes?

They should look something like the attached.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp


Hi again :)

Don't know whats going on here, but I don't seem to have an index for the members table. Is that normal...or?
This SQL crap is really not my thing....unless you didn't already noticed.

Anyway, I have attached an image of the structure anyway. (I don't know how to do those fancy thumbnails  :P)
Since my drive is getting polluted with database backups I also attached the table structure that I can confirm comes from the same database as the table screen shot.
And once again....really appreciate the help. ;D



Well, so there are layers of problems here.  The members table is missing all of its indexes.  And you are using utf8mb4, which is not supported by SMF.

My theory at the moment is that you built the DB with a default collation of utf8mb4 (it likely generously did that for you!), and the one statement in the restore that built the indexes failed due to an index size issue.

The best solution, I think, is to restore the 1.1.x environment again, fresh.  But FIRST, set the DATABASE-level default collation to utf8_general_ci before doing the restore.  After doing the restore, confirm your indexes are there, and confirm that all tables are using utf8_general_ci.

Then upgrade.

To set the database-level default collation in phpmyadmin, create an empty DB, then select it & click on the OPERATIONS tab.  Change the collation to utf8_general_ci.  No need to select the "change all columns" checkbox. 

Hope this helps,
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp


Of course it helped! ;D

I downloaded the latest backup from my site instead of using one of the zillion database copies I made during all tests.
And there was an index for the members file from the start!

Have no clue when or why it got deleted. ( Probably during one of the late night sessions. :o )

Uploaded everything and changed the settings.php and now running SMF 2.0.18 with PHP 7.4.

Just gotta fix some theme errors now, but that got nothing to do with this issue.

Thank you VERY much! I would never found that error myself. :laugh: :laugh: