XF 2.2 MySQL statement prepare error [1146]

[AndryOsminin]

Hello, I'm confused. I updated XenForo to a new version, then updated the template, but the template installed with visual issues and I decided to restore the forum.

On the hosting, I first restored the files, then the database. I restored both to the same date. After the restoration, topics and categories are no longer displayed on the forum, and there is an error hanging in the admin panel:

XF\Db\Exception: MySQL statement prepare error [1146]: Table 'andryon5_gtacom.xf_session_activity' doesn't exist 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 40
XF\Db\Mysqli\Statement->prepare() in src/XF/Db/Mysqli/Statement.php at line 56
XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 96
XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1428
XF\Mvc\Entity\Finder->fetch() in src/XF/Repository/SessionActivity.php at line 57
XF\Repository\SessionActivity->getOnlineStaffList() in src/XF/Admin/Controller/Index.php at line 110
XF\Admin\Controller\Index->actionIndex() in src/XF/Mvc/Dispatcher.php at line 352
XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 258
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

I tried various dates for recovery, tried clearing and recreating session_activity, but nothing helps. Please help!

I restored the website (files) several times today when I tried to update the engine. Everything was restored perfectly. As soon as I updated the engine to the new version, I decided to restore the original data - the database and files, and after that, an error appeared. So the error started after restoring the database. The first time I restored the database, the error occurred.

Hoster's response

"Unfortunately, there was a problem with creating backups of databases with tables using the MyISAM engine on servers with MySQL version 8.

As a solution, it is necessary to restore the database from a backup before transferring it to MySQL 8 and to change the engine from MyISAM to InnoDB. Please indicate in your reply if you agree to have us carry out these actions."

 

[Chris D]

There might be issues with other tables too but this is the query to restore the session_activity table:

CREATE TABLE `xf_session_activity` (
  `user_id` int unsigned NOT NULL,
  `unique_key` varbinary(16) NOT NULL,
  `ip` varbinary(16) NOT NULL DEFAULT '',
  `controller_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `controller_action` varchar(75) COLLATE utf8mb4_general_ci NOT NULL,
  `view_state` enum('valid','error') COLLATE utf8mb4_general_ci NOT NULL,
  `params` varbinary(100) NOT NULL,
  `view_date` int unsigned NOT NULL,
  `robot_key` varbinary(25) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`,`unique_key`),
  KEY `view_date` (`view_date`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

The data in there is temporary and not important so no data will have been lost.

 

[AndryOsminin]

There might be issues with other tables too but this is the query to restore the session_activity table:

CREATE TABLE `xf_session_activity` (
  `user_id` int unsigned NOT NULL,
  `unique_key` varbinary(16) NOT NULL,
  `ip` varbinary(16) NOT NULL DEFAULT '',
  `controller_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `controller_action` varchar(75) COLLATE utf8mb4_general_ci NOT NULL,
  `view_state` enum('valid','error') COLLATE utf8mb4_general_ci NOT NULL,
  `params` varbinary(100) NOT NULL,
  `view_date` int unsigned NOT NULL,
  `robot_key` varbinary(25) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`,`unique_key`),
  KEY `view_date` (`view_date`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

The data in there is temporary and not important so no data will have been lost.

An unexpected database error occurred. Please try again later. After adding SQL to the database..

 

[Chris D]

If you "View source" in your browser it will tell you what the database error is.

Your config.php file apparently has the wrong username/password for the database connection.

 

[AndryOsminin]

The site is working, it is connected to the database. However, after the query you suggested, an error popped up. This means the database is connected

 

[philmckrackon]

The site is working, it is connected to the database. However, after the query you suggested, an error popped up. This means the database is connected

The error message would help with you situation...