Trong bài viết này tôi sẽ hướng dẫn các bạn sửa lỗi Xenforo thông báo Mysqli prepare error: Table '.xf_error_log' doesn't exist bằng lệnh Mysql query đơn giản nhất trên phpmyadmin.

Nguyên nhân lỗi này là do table xf_error_log trong xenforo bị gì đó mà không tồn tại, có thể bị xoá hoặc quá trình chuyển dữ liệu bị mất ... Dưới đây là cách khắc phục đơn giản nhất bạn nhé.

Nếu bạn sử dụng hosting CP11

Thì truy cập vào hosting của mình tìm đến mục phpMyadmin

Bước 1: Truy cập vào Mysql

Bước 2: Chọn database cần sửa lỗi

Bước 3: Thực thi câu truy vấn tạo table.

CREATE TABLE xf_error_log (  
        error_id INT UNSIGNED NOT NULL AUTO_INCREMENT,  
        exception_date INT UNSIGNED NOT NULL,  
        user_id INT UNSIGNED DEFAULT NULL,  
        ip_address VARBINARY(16) NOT NULL DEFAULT '',  
        exception_type VARCHAR(75) NOT NULL,  
        message TEXT NOT NULL,  
        filename VARCHAR(255) NOT NULL,  
        line INT UNSIGNED NOT NULL,  
        trace_string MEDIUMTEXT NOT NULL,  
        request_state MEDIUMBLOB NOT NULL,  
        PRIMARY KEY (error_id),  
        KEY exception_date (exception_date)  
    ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci

Copy câu truy vấn bên trên và làm theo hướng dẫn ở bước 3 bạn nhé!

Nếu bạn sử dụng các hệ thống host khác

Bạn có thể sử dụng các công cụ truy cập vào Mysql để thực thi lệnh khở tạo table theo đoạn Query bên trên.

Chúc thành công!

Thông tin tác giả

LÊ VĂN PHÚ

LPTech được thành lập với mong muốn đem những công nghệ mới nhất cho các doanh nghiệp trong nước. Công ty chúng tôi chuyên Thiết kế website, Dịch vụ SEO và Chỉnh sửa website. Các vấn đề đến hệ thống mạng, lập trình ứng dụng. Uy tín là danh dự của LPTech. Chúng tôi không chấp nhận một sản phẩm kém chất lượng đến tay khách hàng.

I have two VPS's running right now. One, which I will refer to as the "old" server, runs on Debian 7 and gives this output for mysql --version:

mysql Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.2

The other, which I will refer to as the "new" server, is running on a brand new installation of Debian 8, and gives this output:

mysql Ver 15.1 Distrib 10.0.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

I am trying to move my databases & tables from the old server to the new server. I thought that I could do this by simply copying the /var/lib/mysql/ directory from the old server and overwriting the same directory on the new server. However after doing this, I now receive this error in phpMyAdmin on the new server when I click on any of the tables or databases:

`

1932 - Table 'phpmyadmin.pma__tracking' doesn't exist in engine`

However I can clearly see that it exists:

Is there a solution to this? I'm not sure what I did wrong. Thanks!

asked Aug 20, 2015 at 2:19

Mitch TalmadgeMitch Talmadge

1601 gold badge1 silver badge8 bronze badges

2

You can copy files from one server to the next to move your database. To do this, you must make sure that both instances of your DB are stopped. I also recommend using rsync to copy your database files. In addition to making sure that both database servers are stopped before moving the files, you will most likely need to change the ownership of your files. sudo chown --recursive mysql:mysql /var/lib/mysql before starting your new instance.

There are other options available to move your database. If your database isn't terribly large, mysqldump is probably the most simple. I have attached the documentation for mysqldump below as provided by MariaDB, as you are using MariaDB.

https://mariadb.com/kb/en/mariadb/mysqldump/

Another option to consider, a bit more complex but a little more flexible for size, would be Percona's xtrabackup.

https://www.percona.com/software/mysql-database/percona-xtrabackup

answered Aug 20, 2015 at 3:01

7

If you are just moving your DB from one folder to another folder, but your InnoDB tables no longer work, because they can't be found and the error message "Table does not exist in the engine" is displayed, then these pair of SQL statements can be as quick as helpful:

ALTER TABLE __yourtable_name__ DISCARD TABLESPACE ;
ALTER TABLE __yourtable_name__ IMPORT TABLESPACE ;

In pratice, the

LATEST FOREIGN KEY ERROR

2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

0 file is detached from

LATEST FOREIGN KEY ERROR

2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

1 file and then bound again, so that table contents turn into readable.

It worked in my case. Hope yours too.

John K. N.

17.6k12 gold badges51 silver badges110 bronze badges

answered Sep 14, 2018 at 7:24

I solved the problem by following this answer: https://stackoverflow.com/a/11506495/2364405

Except with these changes:

• On Step 5, I did not find `

  ---

  LATEST FOREIGN KEY ERROR

  ---

  2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

  2, but I found

  ---

  LATEST FOREIGN KEY ERROR

  ---

  2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

  ` 3 instead. I used WinSCP and downloaded this file, then imported it into phpMyAdmin (This takes care of steps 6 and 7).
• On Step 8, I only changed the username/password. My tables have TWO underscores ( `

  ---

  LATEST FOREIGN KEY ERROR

  ---

  2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

  ` 4 for example), so I did not change them to one underscore.

Somehow this allowed me to use phpMyAdmin again with no errors. :)

answered Aug 20, 2015 at 5:27

Mitch TalmadgeMitch Talmadge

1601 gold badge1 silver badge8 bronze badges

I was getting this same error after moving a mysqldump backup to a new machine, and doing some updates on the database I've deleted a unique index which had a key for an foreign constraint.

I did realize It was a Foreign Key Error using: show engine innodb status.

LATEST FOREIGN KEY ERROR

2019-07-23 10:43:05 0x7f68a4255700 Error in foreign key constraint of table lhprovedorinfo/NOTAFISCAL:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:

After that, I'v disabled the Foreign Key Checks with: set FOREIGN_KEY_CHECKS=0; Then dropped the foreign key and recreated it, in order to mysql create the necessary indexes. And the my table went back to life.

Hope it helps someone.

Erik Darling

39.8k14 gold badges129 silver badges447 bronze badges

answered Jul 23, 2019 at 14:47

I did the same - but moved from docker hosted on Windows 10 to a docker on Debian 10.9 - both having the /var/lib/mysql docker-mapped to a host directory.

If I move from docker on Windows to docker on another Windows machine there is no error.

I therefore suspected the different ways of handling case sensitivity on Windows and Linux. Inspecting the files in /var/lib/mysql I noticed that my 'File' table had a File.frm and a file.idb file. I tried to rename file.ibd to File.ibd to no avail but renaming both to lowercase makes the table readable again !!

In Windows docker defaults are MariaDB [(none)]> show variables where Variable_name like 'lower%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+

but in Linux docker the defaults are

MariaDB [(none)]> show variables where Variable_name like 'lower%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+

which kind of explains :-)

If you had different settings for lower case on your systems it could explain.

answered Jun 9, 2021 at 13:19

ClausClaus

11 bronze badge

In my case the following worked:

• I removed stray table.frm,table.idb files from /var/lib/mysql/database
• drop table
• create table (structure taken from a healthy table - show create table table)
• if necessary re-import table from backup

answered Oct 21, 2022 at 13:45

Had the same problem. I had installed a new OS on my machine. So I copied my Xampp folder then after installing the OS and copying back the files I got the "can;t find table in engine problem" Deleting all the contents in the mysql folder then copying everything in the mysql folder from the old xampp worked like charm.