2019-12-31, 09:53 PM
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:
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.
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.