MyBB Community Forums

When attempting to backup the database so I can move to another server it finished way too quickly with way to small a gz so I tried again with clear text so I could see the 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>

That comes at the end of the backup_userfields portion of the SQL Insert statements. So as a follow-up I go to the optimize database and select all tables except for the userfields-2 and it works fine. When I select all or only the uf-2 I get:

Quote:MyBB has experienced an internal SQL error and cannot continue.
SQL Error: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
1Query:OPTIMIZE TABLE backup_userfields-2
Please contact the MyBB Group for technical support.

How can I go about further troubleshooting and hopefully fixing this without losing the trove of data we keep in the custom user fields?

Thanks,
gmO

---

I would like to add that my path to myBB 1.8 came from a very crazy upgrade path quite a few years ago in case that makes a difference. We came from an ASP/Access based forum called Snitz that was from a circa 2001 version. I had to run some scripts to convert to phpBB2 then upgrade to phpBB3 and then import into myBB 1.6.x We finally upgraded to myBB 1.8 about 13 months ago.

I just opened up the backup_userfields-2 table and sorted from by ufid which looks like it is the crosslink to user ID and what I see are the oldest of our members from back near 2000. Many of them are now long gone and have never been back to update their user profiles and therefore most of the fields are blank or contain NULL. Since it seems to be barfing on the very first line I can tell you that it contains nothing but blanks and NULLs.

---

Any insight to what the optimize or backup scripts do exactly? Since it seems to be bulking right at the start of the backup_userfields-2 table I could try to troubleshoot on a copy of the database to see if there's some sort of symbol or character or other junk it seems like it would be in the very first row in that table. I just don't know what determine's the "first" row. Is there an index or would it be that ufid column?

Also, I'm assuming that I can't just delete the "first" row without causing problems that would cascade elsewhere since the table is likely linked to other processes.

Even shots in the dark will be helpful to me as I have a copy of the db I can tinker with...

This is still a problem... and I haven't been able to do minor upgrades either -- I think it may be related. Any pointers as to why this is happening or how to proceed to fix it?

(2019-08-21, 02:41 PM)gmOlds Wrote: [ -> ]This is still a problem... and I haven't been able to do minor upgrades either -- I think it may be related. Any pointers as to why this is happening or how to proceed to fix it?

The table userfields-2 (in your case is backup_userfields-2 with table prefix) is not a table created by MyBB by default. It could be someone or you who created this table. The default table name for user profile fields is userfields.

For backup and database optimization in ACP, better not select tables that are not MyBB's table.

You can check the default tables created by MyBB installer listed in ./install/mysql_db_tables.php file, assuming you're using MySQL.

However, it should be a bug that MyBB cannot handle table names with hyphen. It's intentional to not quote table names:

MyBB uses the convention to not quote table names for compatibility with different SQL engines, such as MySQL, PostgreSQL and SQLite, since quoting are different in them.

So you will have potential errors inside MyBB, with any table whose name contains characters need quoting. In your case, it's the hyphen -.

I always assumed this was because of the custom profile fields we've added over the years.

in ./install/resources/ I see a mysql_db_tables.php file that doesn't even have the word user in it. I don't see that file in ./install

Is there some *safe* way I can test if that table is even needed?

(2019-08-21, 03:28 PM)gmOlds Wrote: [ -> ]I always assumed this was because of the custom profile fields we've added over the years.

It's your choice to drop that table or just leave it there. If it survives, remember to not select it when running backup or database optimization.

(2019-08-21, 03:28 PM)gmOlds Wrote: [ -> ]in ./install/resources/ I see a mysql_db_tables.php file that doesn't even have the word user in it. I don't see that file in ./install

Sorry, I miss the resources folder. 

What's your MyBB version? The latest 1821 table defines are in this file, and the userfileds table from here.

(2019-08-21, 03:28 PM)gmOlds Wrote: [ -> ]Is there some *safe* way I can test if that table is even needed?

Manually compare your tables with official released in both table names and structures.

Or try this extension, DVZ Integrity Tools by Devilshakerz: https://community.mybb.com/mods.php?action=view&pid=980 . It will save you a lot of time, but use it at your own risk.

There could also be tables created by plugins for storing their needed data. For these tables, you need to review each plugin's script file for table creating and dropping.

I edited this post to clarity that it's not really a bug of MyBB not handling hyphen in database table names.

We're stuck on 1.8.14 becasue upgrades fail... again I beleive it's because of that table.

(2019-08-23, 07:24 PM)gmOlds Wrote: [ -> ]We're stuck on 1.8.14 becasue upgrades fail... again I beleive it's because of that table.

I think MyBB's upgrade script will not touch tables that are not MyBB's. There shouldn't be any problem with upgrading. If you have errors when upgrading, please specify it or upload a screenshot.

If you have problems with backing up your database, you can do the backup using phpMyAdmin. Then rename any tables whose name has hyphens - to other.

Remember backup before any changes to your data.