Tag Archives: Unknown system variable ‘storage_engine’

Unknown system variable ‘storage_engine’

Download test library employees from MySQL official website_ Db-full-1.0.6.tar.bz2, two errors occurred during import

Error 1: error 1193 (HY000) at line 38: unknown system variable ‘storage_ engine’

[root@maomao employees_db]# mysql -t < employees.sql 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@maomao employees_db]# mysql -uroot -p123456 -t < employees.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
ERROR 1193 (HY000) at line 38: Unknown system variable 'storage_engine'

View your own database storage engine

(root@localhost:mysql.sock)[(none)]>show variables like '%engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

Open the script editor and mark the “storage” in red in the following code snippet_ “Engine” to “default”_ storage_ engineā€

DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;

SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';

DROP TABLE IF EXISTS dept_emp,
                     dept_manager,
                     titles,
                     salaries,
                     employees,
                     departments;

   set storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;

select CONCAT('storage engine: ', @@storage_engine) as INFO;

Import again, success

[root@maomao employees_db]# mysql -uroot -p123456 -t < employees.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+

Error 2: after importing successfully, enter the database to view and find that all table data is empty

(root@localhost:mysql.sock)[employees]>show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.00 sec)

(root@localhost:mysql.sock)[employees]>select count(*) from departments;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

(root@localhost:mysql.sock)[employees]>select count(*) from titles;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Solution: log in to the database employees in the directory where the file is located, manually execute the script employees.sql, and finally mark the statement in red

[root@maomao employees_db]# ls
Changelog                   employees.sql           load_employees.dump  README
employees_partitioned2.sql  load_departments.dump   load_salaries.dump   test_employees_md5.sql
employees_partitioned3.sql  load_dept_emp.dump      load_titles.dump     test_employees_sha.sql
employees_partitioned.sql   load_dept_manager.dump  objects.sql

cat employees.sql
CREATE TABLE salaries (
  emp_no   INT       NOT NULL,
  salary   INT       NOT NULL,
  from_date  DATE      NOT NULL,
  to_date   DATE      NOT NULL,
  KEY     (emp_no),
  FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
  PRIMARY KEY (emp_no, from_date)
);

SELECT 'LOADING departments' as 'INFO';
source load_departments.dump ;
SELECT 'LOADING employees' as 'INFO';
source load_employees.dump ;
SELECT 'LOADING dept_emp' as 'INFO';
source load_dept_emp.dump ;
SELECT 'LOADING dept_manager' as 'INFO';
source load_dept_manager.dump ;
SELECT 'LOADING titles' as 'INFO';
source load_titles.dump ;
SELECT 'LOADING salaries' as 'INFO';
source load_salaries.dump ;

The specific operation is as follows

[root@maomao employees_db]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 425
Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost:mysql.sock)[(none)]>use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(root@localhost:mysql.sock)[employees]>SELECT 'LOADING departments' as 'INFO';
(root@localhost:mysql.sock)[employees]>source load_departments.dump ;
(root@localhost:mysql.sock)[employees]>SELECT 'LOADING employees' as 'INFO';
......

After the execution, it is found that the data has been imported successfully

(root@localhost:mysql.sock)[employees]>select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.06 sec)