@rgwch/normalize_mysqldb

About

This utility normalizes a MariaDB or Mysql database. This means it will convert all table names and all column names to lowercase.

The rationale is: Mysql (and MariaDB) treat table names and field names case insensitive while some tools treat them case sensitive. In older databases, sometimes we are confronted with fields where some clients use "Somefieldname", some prefer "SomeFieldName", others "somefieldname" or even "SOMEFIELDNAME" for the same field. This leads to confusion when we apply ORM tools in case sensitive languages.

To reduce such confusion, we radically convert all to lowercase. This conforms to mysql recommendations.

Install

npm install -g normalize_mysqldb

Precautions

On case sensitive file systems, MyIsam table names are case sensitive by default. To avoid this, you should apply the following setting in my.cfg :

lower_case_table_names=1

after running normalize_mysqldb and restart the mysql server. (Do not apply the setting before normalizing, since mixed case table names won't be accessible any more then.)

Usage

normalize_mysqldb -u username -p password -d database -h hostname

WARNING The tool will immediately convert the database. So, if you're not sure: Use it only on a database copy.

Troubleshoot

On some newer Mysql-Servers (>=8) you might encounter an error message simililar to the following:

Client does not support authentication protocol requested by server, consider upgrading mysql client

Probably this error will go away, as npm-mysql2 upgrades to the newer protocol. Until then you can do the following:

From the mysql client commandline enter:

ALTER USER my_username@'localhost' IDENTIFIED WITH mysql_native_password BY 'my_password';
flush privileges;

After that, normalize_mysqldb should work for the user my_username with the password my_password.

License

MIT