MySQL query error [1406]: Data too long for column 'title'

[Dkf]

Affected version

2.2.13

XF\Db\Exception: MySQL query error [1406]: Data too long for column 'title' at row 1 in src/XF/Db/AbstractStatement.php at line 230
XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 198
XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 79
XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 96
XF\Db\AbstractAdapter->query() in src/XF/Db/AbstractAdapter.php at line 220
XF\Db\AbstractAdapter->insert() in src/XF/Import/Data/EntityEmulator.php at line 329
XF\Import\Data\EntityEmulator->insert() in src/XF/Import/Data/AbstractEmulatedData.php at line 71
XF\Import\Data\AbstractEmulatedData->write() in src/XF/Import/Data/AbstractData.php at line 129
XF\Import\Data\AbstractData->save() in src/addons/XFI/Import/Importer/vBulletin.php at line 1982
XFI\Import\Importer\vBulletin->stepPrivateMessages() in src/XF/Import/Runner.php at line 244
XF\Import\Runner->runStep() in src/XF/Import/Runner.php at line 61
XF\Import\Runner->run() in src/XF/Admin/Controller/Import.php at line 234
XF\Admin\Controller\Import->actionRun() in src/XF/Mvc/Dispatcher.php at line 352
XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 259
XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 115
XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 57
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2487
XF\App->run() in src/XF.php at line 524
XF::runApp() in admin.php at line 13

 

[Dkf]

I found the longest title and removed it.
SELECT pmtextid, title FROM pmtext ORDER BY LENGTH(title) DESC LIMIT 1;

 

[Dkf]

I performed the same migration operation from Vbulletin to XenForo twice, using the same database. The first time was with XF 2.2.10, and the second time with XF 2.2.13.

The second time, I had to manually delete around 100,000 private messages because their title exceeded the allowed limits.

This is strange.

 

[Paul B]

The VB importer has been used hundreds, if not thousands of times and no-one else has reported this issue.

Was your VB installation customised to allow longer titles?

The first time was with XF 2.2.10, and the second time with XF 2.2.13.

Are you saying the import worked with 2.2.10 but failed with 2.2.13 with the same VB database?

 

[Dkf]

The VB importer has been used hundreds, if not thousands of times and no-one else has reported this issue.

Was your VB installation customised to allow longer titles?

I checked and indeed the size of the header exceeded 150 characters.
This may have happened due to the installed "Reply in a private message" plugin, where the header is taken from the thread.

Are you saying the import worked with 2.2.10 but failed with 2.2.13 with the same VB database?

Yes

 

[MySiteGuy]

update pmtext set title = left(title, 150);

 

[Dkf]

update pmtext set title = left(title, 150);

And xf_node 150 ?

XF\Db\Exception: MySQL query error [1406]: Data too long for column 'title' at row 1
src/XF/Db/AbstractStatement.php:230


INSERT  INTO `xf_node` (`node_id`, `title`, `node_name`, `description`, `node_type_id`, `parent_node_id`, `display_order`, `style_id`, `effective_style_id`, `display_in_list`, `breadcrumb_data`, `navigation_id`, `effective_navigation_id`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
------------

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL query err...', 1406, '22001')
#1 src/XF/Db/Mysqli/Statement.php(79): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1406, '22001')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(220): XF\Db\AbstractAdapter->query('INSERT  INTO `x...', Array)
#4 src/XF/Import/Data/EntityEmulator.php(329): XF\Db\AbstractAdapter->insert('xf_node', Array)
#5 src/XF/Import/Data/AbstractEmulatedData.php(71): XF\Import\Data\EntityEmulator->insert(307, Object(XF\Db\Mysqli\Adapter))
#6 src/XF/Import/Data/AbstractData.php(129): XF\Import\Data\AbstractEmulatedData->write(307)
#7 src/addons/XFI/Import/Importer/vBulletin.php(2195): XF\Import\Data\AbstractData->save(307)
#8 src/addons/XFI/Import/Importer/vBulletin.php(2205): XFI\Import\Importer\vBulletin->importNodeTree(Array, Array, 148, '148')
#9 src/addons/XFI/Import/Importer/vBulletin.php(2205): XFI\Import\Importer\vBulletin->importNodeTree(Array, Array, 69, '69')
#10 src/addons/XFI/Import/Importer/vBulletin.php(2118): XFI\Import\Importer\vBulletin->importNodeTree(Array, Array, -1)
#11 src/XF/Import/Runner.php(244): XFI\Import\Importer\vBulletin->stepForums(Object(XF\Import\StepState), Array, 8)
#12 src/XF/Import/Runner.php(61): XF\Import\Runner->runStep('forums', Object(XF\Import\StepState), 8)
#13 src/XF/Admin/Controller/Import.php(234): XF\Import\Runner->run()
#14 src/XF/Mvc/Dispatcher.php(352): XF\Admin\Controller\Import->actionRun(Object(XF\Mvc\ParameterBag))
#15 src/XF/Mvc/Dispatcher.php(259): XF\Mvc\Dispatcher->dispatchClass('XF:Import', 'Run', Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\Import), NULL)
#16 src/XF/Mvc/Dispatcher.php(115): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\Import), NULL)
#17 src/XF/Mvc/Dispatcher.php(57): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#18 src/XF/App.php(2487): XF\Mvc\Dispatcher->run()
#19 src/XF.php(524): XF\App->run()
#20 admin.php(13): XF::runApp('XF\\Admin\\App')

 

[Paul B]

Which version of the importer add-on did you use?

 

[Dkf]

Which version of the importer add-on did you use?

1.5.2

And I have new problem

XF\Db\DuplicateKeyException: MySQL query error [1062]: Duplicate entry '2764182' for key 'PRIMARY'
src/XF/Db/AbstractStatement.php:230

INSERT  INTO `xf_thread` (`thread_id`, `node_id`, `title`, `reply_count`, `view_count`, `user_id`, `username`, `post_date`, `sticky`, `discussion_state`, `discussion_open`, `discussion_type`, `type_data`, `first_post_id`, `last_post_date`, `last_post_id`, `last_post_user_id`, `last_post_username`, `first_post_reaction_score`, `first_post_reactions`, `prefix_id`, `custom_fields`, `tags`, `vote_score`, `vote_count`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
------------

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL query err...', 1062, '23000')
#1 src/XF/Db/Mysqli/Statement.php(79): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1062, '23000')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(220): XF\Db\AbstractAdapter->query('INSERT  INTO `x...', Array)
#4 src/XF/Import/Data/EntityEmulator.php(329): XF\Db\AbstractAdapter->insert('xf_thread', Array)
#5 src/XF/Import/Data/AbstractEmulatedData.php(71): XF\Import\Data\EntityEmulator->insert(2764182, Object(XF\Db\Mysqli\Adapter))
#6 src/XF/Import/Data/AbstractData.php(129): XF\Import\Data\AbstractEmulatedData->write(2764182)
#7 src/addons/XFI/Import/Importer/vBulletin.php(3057): XF\Import\Data\AbstractData->save(2764182)
#8 src/XF/Import/Runner.php(244): XFI\Import\Importer\vBulletin->stepThreads(Object(XF\Import\StepState), Array, 8)
#9 src/XF/Import/Runner.php(61): XF\Import\Runner->runStep('threads', Object(XF\Import\StepState), 8)
#10 src/XF/Admin/Controller/Import.php(234): XF\Import\Runner->run()
#11 src/XF/Mvc/Dispatcher.php(352): XF\Admin\Controller\Import->actionRun(Object(XF\Mvc\ParameterBag))
#12 src/XF/Mvc/Dispatcher.php(259): XF\Mvc\Dispatcher->dispatchClass('XF:Import', 'Run', Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\Import), NULL)
#13 src/XF/Mvc/Dispatcher.php(115): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Admin\Controller\Import), NULL)
#14 src/XF/Mvc/Dispatcher.php(57): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#15 src/XF/App.php(2487): XF\Mvc\Dispatcher->run()
#16 src/XF.php(524): XF\App->run()
#17 admin.php(13): XF::runApp('XF\\Admin\\App')
#18 {main}

 

[Dkf]

Can someone explain to me what's happening?!

I can't import the Vbulletin database. I've already tried about 10 times. And every time, in completely different places, I encounter an error like this:
XF\Db\DuplicateKeyException: MySQL query error [1062]: Duplicate entry '2764182' for key 'PRIMARY'

If I stop the import and start over, there are no such errors the second time! They occur in different places. Sometimes dozens in a row. Sometimes just one error.
The database passes the check - no errors. This is the incorrect behavior of the importer.

I have to manually delete records from the table to continue the import.

mysql> DELETE FROM xf_post WHERE xf_post.post_id = 70852432;
Query OK, 1 row affected (0,01 sec)

mysql> DELETE FROM xf_post WHERE xf_post.post_id = 70852480;
Query OK, 1 row affected (0,00 sec)

mysql> DELETE FROM xf_post WHERE xf_post.post_id = 70852498;
Query OK, 1 row affected (0,00 sec)

mysql> DELETE FROM xf_post WHERE xf_post.post_id = 70852552;
Query OK, 1 row affected (0,00 sec)

 

[FTL]

Perhaps try to think hard about the ways that the data could have been changed by vB add-ons in some way that could cause this problem. Its random nature is strange though.

What server are you running and PHP version?

 

[Dkf]

Perhaps try to think hard about the ways that the data could have been changed by vB add-ons in some way that could cause this problem. Its random nature is strange though.

What server are you running and PHP version?

Debian GNU/Linux 11 (bullseye)
MySQL version: 5.7.42-log
PHP Version 7.4.33

 

[Dkf]

mysql> DELETE FROM xf_post WHERE xf_post.post_id = 70852432;
Query OK, 1 row affected (0,01 sec)

mysql> DELETE FROM xf_post WHERE xf_post.post_id = 70852480;
Query OK, 1 row affected (0,00 sec)

mysql> DELETE FROM xf_post WHERE xf_post.post_id = 70852498;
Query OK, 1 row affected (0,00 sec)

mysql> DELETE FROM xf_post WHERE xf_post.post_id = 70852552;
Query OK, 1 row affected (0,00 sec)

I forgot to mention an important point...
You can see that the IDs are almost sequential.
However, errors with these IDs occurred with a one hour difference.

 

[FTL]

Debian GNU/Linux 11 (bullseye)
MySQL version: 5.7.42-log
PHP Version 7.4.33

Is it possible to upgrade PHP to the latest 8.0.x series, which is the latest officially compatible with XF? Note that later 8.2.x versions of XF are still not officially supported, so I can't recommend them here, although people say XF runs just fine with them.

Also, you're running a pretty old version of MySQL that isn't supported anymore afaik which is quite likely to throw up random errors like these.

Between those two, I'm not surprised that the import is failing.

I don't know who your host is, but if you can't upgrade those two critical components, then I suggest the following strategy:

1 You can try and find a host that does support the latest versions, although from my experience, you're gonna be on a hiding to nothing as they just seem to lurve offering out of date versions with no options for the latest, all in the name of "compatibility". Yeah.

2 Roll your own server on AWS with the latest versions of PHP 8.0.x and MySQL and then try the import again. It's quite possible that those errors will disappear.

I won't lie, it'll take a fair bit of time and effort to learn AWS and create the two virtual machines required. That will be a Linux / Windows web server and separate instance for MySQL, called RDS*. It's a black box function that creates a database for you using a Linux server that AWS creates. This is how I built my hosting platform and tuned it precisely for my needs, not what some host thinks I should have that just benefits them.

3 You can go XF Cloud and let the XF staff manage the import for you. They're skilled and will be able to do it, too. Note that you'll then have access to XF only, as admin, but no access to the server. All you then do is pay a monthly fee and that's it, your site stays up and add-ons work with it, too. It's also very reliable.

Look @Chris D I've bigged you lot up, so I want my small chocolate reward now.

* You can create the MySQL database on the web server, but I don't recommend it. Best to do it properly with its own server, see link below.

Fully Managed Relational Database - Amazon RDS - Amazon Web Services

Amazon Relational Database Service (RDS) is a fully managed, open-source cloud database service that allows you to easily operate and scale your relational database of choice, including Amazon Aurora, PostgreSQL, SQL Server, and MySQL.

aws.amazon.com

 

[MySiteGuy]

@Dkf

This problem is not due to the importer code, the MySQL version, or the PHP version. The importer runs fine with MySQL 5.5 all the way up through MySQL 8 and MariaDB 11, and with PHP 7.x. I've never seen these as issues over hundreds of migrations.

The problem you're seeing is from an importer session/thread getting into a "race" condition while the import session data is being updated. There are a few ways this can happen with big forums, but I won't bore you with the technical details

It is almost always a situation with (a) a large forum migration, and (b) too many import threads for the environment. I have seen it happen with single thread imports, though that is much rarer.

There are two ways to handle this on the same hosting setup:

1. Start the import fresh again. Run the command line importer but run only 1-2 threads to avoid another race condition. The fewer threads you run, the less likely the chance it will happen.

2. In some cases, not every ID after the first duplicate ID is another duplicate ID. So I don't recommend just deleting everything after that id, because a multi-thread import can have some good blocks you might delete by mistake. There are ways to determine what needs to be deleted and what needs to be skipped, but its more than I can put into a post. When those are properly deleted, an existing import can resume without data loss. If you don't want to do option 1, send me a message and we can discuss this service to get your import resumed.

 

[FTL]

@MySiteGuy that race condition you mention sounds intriguing. I know what these are in general terms, but not how that applies to XF and would be grateful if you could clarify how it happens in a quick summary for us.

This problem is not due to the importer code, the MySQL version, or the PHP version. The importer runs fine with MySQL 5.5 all the way up through MySQL 8 and MariaDB 11, and with PHP 7.x. I've never seen these as issues over hundreds of migrations.

You have lots of experience compared to my zero experience of forum migrations, so I guess you're right. Still, if all else fails, it might be worth giving it a go, I reckon. I think especially if the data doesn't fit the proper parameters due to vB add-ons, then later versions of the software might cope better with it, perhaps.

 

[MySiteGuy]

Race conditions are a common problem almost everyone from operating system coders to application programmers will eventually deal with when they are dealing with parallel operations.

There are various ways to signal "this resource is busy servicing work already or waiting on another resource". But despite these measures, collisions can happen because there are things about the environment outside Xenforo's control,

At a high level: If two or more threads at the same time go to update the status of "where we left off in my import", there's a very small possibility an update will conflict with another about to happen. So posts (or other records) can be imported, but their status of already being imported is not in the database. Along comes another thread, it tries to import those again, and a duplicate entry error happens. The more threads running and the busier the system is, the more likely it's going to happen.

2. There's also a case where post records are inserted, and before the thread can mark the import status, the thread is killed, crashes, or the database disconnects (which can also happen for multiple reasons).

In both cases, theoretically the duplicate problem should not happen due to the way data is committed.... but it does happen, as we've seen in this case.

The database being on a separate server can increase the chances it happens due to less bandwidth and higher latency to the database. Also, an issue with most hosting and cloud providers, including AWS, is while the DB works great for normal server loads, they either use stock DB settings or settings designed for "read mostly" loads. Imports have a high write load. Tuning the database settings can help by speeding up database insert and update rates.

 

[FTL]

Great explanation, thanks!