Fixed Mysql error on upgrading XF1 to XF2.

[MojoW]

So i was in the process of upgrading my live site but got a alot of errors, so i had to roll back.
Now i have a local test bed setup and i've fixed all the database problems that had to do with old addons.

I'm stuck on one more error and i think this one has to do with MYSQL 8.0.11 as i can not find the solution for this one.
This error happens straight after i press begin upgrade, without performing any steps.

Apache 2.4
PHP 7
MYSQL 8.0.11

XF\Db\InvalidQueryException: MySQL statement prepare 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 'admin INNER JOIN xf_user AS user ON (admin.user_id = user.user_id) WHE' at line 2 in src\XF\Db\AbstractStatement.php at line 212

    XF\Db\AbstractStatement->getException() in src\XF\Db\Mysqli\Statement.php at line 196
    XF\Db\Mysqli\Statement->getException() in src\XF\Db\Mysqli\Statement.php at line 39
    XF\Db\Mysqli\Statement->prepare() in src\XF\Db\Mysqli\Statement.php at line 54
    XF\Db\Mysqli\Statement->execute() in src\XF\Db\AbstractAdapter.php at line 79
    XF\Db\AbstractAdapter->query() in src\XF\Db\AbstractAdapter.php at line 106
    XF\Db\AbstractAdapter->fetchAllKeyed() in src\XF\Install\Upgrade\2000010-200a.php at line 1304
    XF\Install\Upgrade\Version2000010->step27() in src\XF\Install\Controller\Upgrade.php at line 169
    XF\Install\Controller\Upgrade->actionRun() in src\XF\Mvc\Dispatcher.php at line 249
    XF\Mvc\Dispatcher->dispatchClass() in src\XF\Mvc\Dispatcher.php at line 88
    XF\Mvc\Dispatcher->dispatchLoop() in src\XF\Mvc\Dispatcher.php at line 41
    XF\Mvc\Dispatcher->run() in src\XF\App.php at line 1931
    XF\App->run() in src\XF.php at line 328
    XF::runApp() in install\index.php at line 14

If anyone can help in the right direction it would be appreciated.

MojoW

 

[Chris D]

Ok, this is a bug.

MySQL 8.0 has added some new reserved words/keywords. One of them is admin and on line 1304 of the file 2000010-200a.php we run this query:

SELECT admin.user_id, admin.permission_cache, admin.is_super_admin
FROM xf_admin AS admin
INNER JOIN xf_user AS user ON
   (admin.user_id = user.user_id)
WHERE user.is_moderator = 1

As a quick fix to get you going, for now, this should either be changed to the following, or run the upgrade using a lower MySQL version:

SELECT ad.user_id, ad.permission_cache, ad.is_super_admin
FROM xf_admin AS ad
INNER JOIN xf_user AS user ON
   (ad.user_id = user.user_id)
WHERE user.is_moderator = 1

It looks like this is the only occurrence of an incompatibility with MySQL 8.0 but please do let us know if you come across anything else.

 

[MojoW]

Thanks that fixed that error and the installation proceeded.

Now i got a new error and it implies that i do not have the "xf_thread_field" table.

InvalidArgumentException: Table 'xf_thread_field' does not exist so cannot be altered in src\XF\Db\Schema\Alter.php at line 38

    XF\Db\Schema\Alter->__construct() in src\XF\Db\SchemaManager.php at line 188
    XF\Db\SchemaManager->newAlter() in src\XF\Db\SchemaManager.php at line 141
    XF\Db\SchemaManager->alterTable() in src\XF\Install\Upgrade\2000034-200b4.php at line 32
    XF\Install\Upgrade\Version2000034->step2() in src\XF\Install\Controller\Upgrade.php at line 169
    XF\Install\Controller\Upgrade->actionRun() in src\XF\Mvc\Dispatcher.php at line 249
    XF\Mvc\Dispatcher->dispatchClass() in src\XF\Mvc\Dispatcher.php at line 88
    XF\Mvc\Dispatcher->dispatchLoop() in src\XF\Mvc\Dispatcher.php at line 41
    XF\Mvc\Dispatcher->run() in src\XF\App.php at line 1931
    XF\App->run() in src\XF.php at line 328
    XF::runApp() in install\index.php at line 14

I've checked the database and i am missing that table.
Is there anyway for me to create that table?

Edit:
I found the solution posted somewhere else by you.
I'll just leave it here aswell if anyone else runs in to the same problem.

Run this query:

CREATE TABLE `xf_thread_field` (
  `field_id` varbinary(25) NOT NULL,
  `display_group` varbinary(25) NOT NULL DEFAULT 'before',
  `display_order` int(10) unsigned NOT NULL DEFAULT 1,
  `field_type` varbinary(25) NOT NULL DEFAULT 'textbox',
  `field_choices` blob NOT NULL,
  `match_type` varbinary(25) NOT NULL DEFAULT 'none',
  `match_params` blob NOT NULL,
  `max_length` int(10) unsigned NOT NULL DEFAULT 0,
  `required` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `user_editable` enum('yes','once','never') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'yes',
  `display_template` text COLLATE utf8mb4_general_ci NOT NULL,
  `moderator_editable` tinyint(3) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`field_id`),
  KEY `display_group_order` (`display_group`,`display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

After that i got this error:

XF\Db\Exception: MySQL statement prepare error [1146]: Table 'solidsnake_forum.xf_widget' doesn't exist in src\XF\Db\AbstractStatement.php at line 212

    XF\Db\AbstractStatement->getException() in src\XF\Db\Mysqli\Statement.php at line 196
    XF\Db\Mysqli\Statement->getException() in src\XF\Db\Mysqli\Statement.php at line 39
    XF\Db\Mysqli\Statement->prepare() in src\XF\Db\Mysqli\Statement.php at line 54
    XF\Db\Mysqli\Statement->execute() in src\XF\Db\AbstractAdapter.php at line 79
    XF\Db\AbstractAdapter->query() in src\XF\Mvc\Entity\Finder.php at line 1163
    XF\Mvc\Entity\Finder->fetch() in src\XF\Repository\Widget.php at line 102
    XF\Repository\Widget->getWidgetCache() in src\XF\Repository\Widget.php at line 149
    XF\Repository\Widget->rebuildWidgetCache() in src\XF\Job\CoreCacheRebuild.php at line 37
    XF\Job\CoreCacheRebuild->run() in src\XF\Job\Atomic.php at line 38
    XF\Job\Atomic->run() in src\XF\Job\Manager.php at line 241
    XF\Job\Manager->runJobInternal() in src\XF\Job\Manager.php at line 187
    XF\Job\Manager->runJobEntry() in src\XF\Job\Manager.php at line 138
    XF\Job\Manager->runUnique() in src\XF\Install\Controller\AbstractController.php at line 24
    XF\Install\Controller\AbstractController->manualJobRunner() in src\XF\Install\Controller\Upgrade.php at line 251
    XF\Install\Controller\Upgrade->actionRunJob() in src\XF\Mvc\Dispatcher.php at line 249
    XF\Mvc\Dispatcher->dispatchClass() in src\XF\Mvc\Dispatcher.php at line 88
    XF\Mvc\Dispatcher->dispatchLoop() in src\XF\Mvc\Dispatcher.php at line 41
    XF\Mvc\Dispatcher->run() in src\XF\App.php at line 1931
    XF\App->run() in src\XF.php at line 328
    XF::runApp() in install\index.php at line 14

And fixed it with this query posted by you.

CREATE TABLE `xf_widget` (
  `widget_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `widget_key` varbinary(50) NOT NULL,
  `definition_id` varbinary(25) NOT NULL,
  `positions` blob NOT NULL,
  `options` blob NOT NULL,
  PRIMARY KEY (`widget_id`),
  UNIQUE KEY `widget_key` (`widget_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Now my test bed is upgraded and i'm in the process of making a upgrade plan for the live server.

Thanks for the help @Chris D