datacharmer/test_db

test_employees uses MyISAM

thildebrant opened this issue · 9 comments

thildebrant commented

the test employees scripts both fail when I use InnoDB, is this configurable?

datacharmer commented

Please give more info:

  • what version of MySQL you are using?
  • what actions did you perform to run with InnoDB?

In version 5.6 and later, InnoDB is the default engine, so running mysql < employees.sql would use InnoDB without additional action.
For older versions, you need to run explicitly

set global default_storage_engine="innodb";
datacharmer commented

UPDATE : I misread the question. The MyISAM engine is only used explicitly in the two test files. However, its engine is immediately changed to memory. Unless you are using MySQL earlier than 5.0.30, the test should work as expected.

thildebrant commented

Hi,
let me do this properly:
Server version: 8.0.21-u1-cloud MySQL Enterprise - Cloud
mysql -h 10.0.1.3 -u admin -p -t < test_employees_sha.sql
Enter password:
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
ERROR 3161 (HY000) at line 38: Storage engine MEMORY is disabled (Table creation is disallowed).

mysql -h 10.0.1.3 -u admin -p -t < test_employees_md5.sql
Enter password:
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
ERROR 3161 (HY000) at line 30: Storage engine MyISAM is disabled (Table creation is disallowed).

thildebrant commented

I created the employees DB using
mysql -h 10.0.1.3 -u admin -p < employees.sql
Enter password:
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
data_load_time_diff
00:01:23

thildebrant commented

(of note, since I am using OCI MySQLaaS the OCI user created doesn't have RELOAD privileges, so employees.sql fails at line 110. Once I commented this out it ran just fine)

datacharmer commented

Thanks for the details.
I have changed the test files to not use specific engines, and added a test to make sure it works with all MySQL versions from 5.0 to 8.0.

Regarding the RELOAD privilege, I left line 110 in place because I know of at least one workflow where it is desirable to have the binary log of the schema definition separated from the data loading.

thildebrant commented

I am putting this in the same issue (I can't reopen this one):
mysql -h 10.0.1.3 -u admin -p -t < test_employees_sha.sql
Enter password:
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+------------------------------------------+
| departments | 9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_emp | 331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| dept_manager | 24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| employees | 300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| salaries | 2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
| titles | 443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
+--------------+------------------+------------------------------------------+
ERROR 3161 (HY000) at line 56: Storage engine BLACKHOLE is disabled (Table creation is disallowed).

datacharmer commented

Removed also blackhole engine. It seems that the Cloud edition is aiming to breaking more backward compatibility ...

thildebrant commented

Thanks, employees.sql, test_employees_md5.sql, and test_employees_sha.sql all work on my OCI based https://docs.cloud.oracle.com/en-us/iaas/mysql-database/
Server version: 8.0.21-u1-cloud MySQL Enterprise - Cloud