search

datacharmer / test_db

A sample MySQL database with an integrated test suite, used to test your applications and database servers
4.06k stars 2.59k forks source link

test_employees uses MyISAM #24

Closed thildebrant closed 4 years ago

thildebrant commented 4 years ago

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

datacharmer commented 4 years ago

Please give more info:

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 4 years ago

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 4 years ago

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 4 years ago

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 4 years ago

(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 4 years ago

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 4 years ago

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 4 years ago

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

thildebrant commented 4 years ago

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