Fixed Mysql error on upgrading XF1 to XF2.

MojoW

MojoW

Active member
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

Code: 
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
 
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:
Code: 
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:
SQL: 
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.
 
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.

Code: 
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.
Chris D said:
Run this query:
SQL: 
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;
Click to expand...


After that i got this error:

Code: 
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.
Chris D said:
SQL: 
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;
Click to expand...


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
 
Last edited:

Similar threads

Daveo16
Fixed 2.3.0 Beta 4 Install Bug
Replies
9
Views
555
XF Bug Bot
XF Bug Bot
philmckrackon
Fixed 2.3.0 Beta 4 Install Stopped
2
Replies
21
Views
711
MattW
MattW
Alan_SP
Errors after transfer to a new server
Replies
1
Views
321
Alan_SP
Alan_SP
M
  • Question Question
XF 2.2 can you help with these server errors
Replies
14
Views
302
Sim
Sim
N
  • Question Question
XF 2.2 XF\Db\Exception: MySQL query error [1048]: Column 'ip_id' cannot be null in src/XF/Db/AbstractStatement.php at line 230
Replies
2
Views
195
nathanw
N
Top Bottom