MyBB Community Forums

Full Version: [SOLVED] Database problems with extended userfields.
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I have posted in the past regarding this (referential link: https://community.mybb.com/thread-221758.html)...

Our forums added in plenty of custom user fields. I believe that whatever version we started on placed these in tables named backup_userfields and backup_userfields-2. These two tables have been problematic for me for years now. Until recently I have been unable to update when security versions have come out. The 1.8.21 version has been the first I've been able to upgrade to. While it is a relief that I'm almost current, I still am unable to use the backup tools and the optimize tool also errors out.

Each of this is the case UNLESS I deselect the two above mentioned tables.

When backing up the database the output SQL file ends very quickly with the following error:

<style type="text/css">
#mybb_error_content { border: 1px solid #026CB1; background: #fff; -moz-border-radius: 3px; -webkit-border-radius: 3px; border-radius: 3px; }
#mybb_error_content a:link { color: #026CB1; text-decoration: none; }
#mybb_error_content a:visited { color: #026CB1; text-decoration: none; }
#mybb_error_content a:hover, a:active { color: #000; text-decoration: underline; }
#mybb_error_content h2 { font-size: 12px; padding: 4px; background: #026CB1; color: #fff; margin: 0; border-bottom: none; }
#mybb_error_error { padding: 6px; }
#mybb_error_footer { font-size: 12px; border-top: 1px dotted #DDDDDD; padding-top: 10px; }
#mybb_error_content dt { font-weight: bold; }
</style>
<div id="mybb_error_content">
<h2>MyBB SQL Error</h2>
<div id="mybb_error_error">
<p>MyBB has experienced an internal SQL error and cannot continue.</p><dl>
<dt>SQL Error:</dt>
<dd>1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2' at line 1</dd>
<dt>Query:</dt>
<dd>OPTIMIZE TABLE backup_userfields-2</dd>
</dl>

<p id="mybb_error_footer">Please contact the <a href="https://mybb.com">MyBB Group</a> for technical support.</p>
</div>
</div>

When optimizing I get the graphical version which is essentially the same content. Based on the rough number of rows put out in the sql file it looks like the error occurs when it's finished with backup-userfields and is choking on something in backup_userfields-2.

Using phpmyadmin and looking at that table there are almost 1200 rows of custom user data in there and the data is displaying withing phpmyadmin with no problem. I'm not sure how to go about diagnosing what could be wrong but would really love some tips on what to try. As you can see if you looked at my last attempt to fix this I didn't get much help. I'm able to query and even dump data. I control the server it's on. I've made numerous copies and backups using various techniques but the only way I could update the server was to use phpmyadmin to dump a copy of the DB out and then at the cli restore it to a new DB and then run the update. But again, myBB is still choking on that table.

Any thought on this? I'd love to make 2020 the year that this problem gets solved. Smile
If you're already familiar with phpMyAdmin, you can use Import, Export, and Optimize tools there, and verify the backups on a local MyBB installation to make sure they can be correctly restored if anything goes wrong during the upgrade (the ACP equivalent can be used for convenience, but they produce the same result).

As mentioned in the other thread, there might be a problem with special characters in table names (-; e.g. there are no ' quotes around table name here: https://github.com/mybb/mybb/blob/mybb_1....php#L1072), so using the ACP's tools might be problematic in these cases.

You can also try simply deleting the "-2" table on a test installation to see if anything breaks.
Thank you for the reply. I can see by looking at the data in the table that it would effect us in that it contains a LOT of user profile information. As for the dash (-) - I didn't create the table, myBB did when I added custom profile fields. So if I were to get rid of the dash wouldn't I need to make changes somewhere else so that myBB knows where to find the data?

I've just optimized the table from within phpmyadmin without issue. The problem only occurs when I try to optimize or backup the table from within the admin console. This was much more of a problem in the past as the upgrade process was also choking so for a long time I was stuck. Something must have changed in more recent upgrade scripts as both .21 and .22 have worked.

Still, it would be nice to fix this so if you can direct me on how to rename the table AND still have the nearly 1200 rows of data still work within our forums users profiles that would be spectacular. Or perhaps there is a bug to be reported since I didn't create the table name.

I just pulled both of the "backup_userfields" tables as CSVs to take a closer look at the data. As it turns out the backup_userfields table has far less rows (184) and the rows it does have are repeated in backup_userfields-2. In our forums, all members are showing data in these extended profile fields so I'm guessing that somehow myBB splintered it and knows to read from that table?

I don't know if that helps but it seems to me something went awry in the past.

Wait, I take that back. The data isn't the same. And when I look at my own account I see that one table has older data.

Okay... I renamed the "-2" table to "_old" and looked at our most active uses and it looks like all of the data is current. I'm still not 100% sure what happened or when and I'm sure that some of the older accounts probably are missing data but at least the utilities all work and I still have that old data if needed.

Thanks for getting me straightened out!