Things to consider when using MySQL

 

 

In order to use the UltraPort SQL Schema Engine successfully you may want to watch for a few issues. This is especially true when using a MySQL instance on a Linux or Unix server. If you experience problems when Creating your SQL Schemas, Validating your Schemas, Repairing your Schemas, Testing Your Schema, or running an inbound or outbound processor (see Running the Schema Engine) then consider the following issues.

 

1. Make sure that your MySQL server is using Legacy Authentication. We do not support the newer SHA authentication which started with MySQL 8.

IMPORTANT: If you need to use the SHA authentication or are experiencing compatibility issues try using the ODBC MySQL option in your MySQL Schema Profile.

 

2. Make sure that for MySQL your table names and queries are NOT case sensitive.

 

In Linux (and any OS which has a case-sensitive file system) your MySQL tables are likely case-sensitive by default. In MS Windows and Mac OS they are not. You can do this by changing the MySQL system variable lower_case_table_names to 1 (for Linux or any OS which has a case-sensitive file system) or to 2 for MS Windows or MAC OS. A value of 0 indicates that your table names ARE case-sensitive.

 

You can see the current value of this setting by issuing this command in your MySQL client:

 

SHOW VARIABLES LIKE 'lower_case_table_names%';

 

You can also test by issuing some simple SQL statements to see if an error is created. For instance if you have a MySQL database and there is a table named Customers. You can issue statements like:

 

select * from Customers;

select * from CUSTOMERS;

select * from cuSTOmers;

 

If your table names are case-sensitive only the first sql statement will run without errors.

 

To change the value:

 

•Stop MySQL service

•Edit your MySQL configuration file (my.cnf) (for windows it is your my.ini)

•Under the [mysqld] section set the value by adding (or changing the line if it already exists like so: lower_case_table_names = 1 (for Linux, etc) or lower_case_table_names = 2 (for Windows or Mac).

•Restart the MySQL service

•Restart your UltraPort SQL Schema Engine and see if the problem persists.

•IMPORTANT: You need to make sure that your MySQL database(s) have NO TABLES existing already when you make this change. If you have already created your SQL Schema you need to remove it in MySQL first. If your database(s) have data in them already and you need to preserve it then refer to the MySQL documentation for instructions on exporting your data tables, removing them, changing the lower_case_table_names setting as described above, and then reimporting your data tables.

 

 

 

 

3. MySQL "page size" issues. Error Code 1118 Row Size Too Large (>8126)

 

NEW in version 6.0. You can direct the Schema Engine to create your Schema Tables using the MyISAM storage engine. This will usually easily solve this error code.

 

This is a legendarily hairy problem in MySQL and InnoDB in particular.

 

Microsoft SQL Server 2000 and earlier had an issue with the default "page size" allocated to tables, it was 8KB, meaning that the total size of all columns in a table could not exceed 8KB. For instance you could not create a table with 9 fields of type Varchar(1024) etc. Microsoft addressed this issue (weakly in our opinion) starting with MS SQL 2005. MySQL from it's inception also had a similar issue which they also addressed but differently.

 

If you encounter issues where the program will not create all or some of your Schema Tables you can try the following:

 

•Check page size. The UltraPort SQL Schema tables can be quite large (IE many columns) especially with later versions of HL7 (2.5.1 and 2.6, etc). If your page size is 8KB you might need to double it. We've never had the need reported to go above 16KB but if problems persist try going to 32KB to see if the situation resolves itself. You can see your current page size by issuing the following command in your MySQL client: (SHOW VARIABLES LIKE '%innodb_page_size%';). Refer to your MySQL documentation for best practices for changing this value.

•Try turning "strict mode" ON or OFF. See this setting by issuing the following command in your MySQL client: (SHOW VARIABLES LIKE 'innodb_strict_mode%';). Refer to your MySQL documentation for best practices for changing this value.

•Try making the collation Latin1.

•If all else fails change your default-storage-engine and default_tmp_storage_engine from InnoDB to MyISAM.

 

 

4. Make sure you have created your MySQL triggers.

 

The UltraPort SQL Schema engine requires that there be some triggers in place for some of your tables. You can make sure that these have all been created properly. See the note in Creating Schema Tables for more information.

 

5. Verify your MySQL version.

 

The UltraPort SQL Schema Engine MySQL interfaces were vetted and certified using MySQL versions 5.6 and higher. If you are using an earlier version then you likely cannot use the MySQL Native connection when creating your UltraPort SQL Schema Profiles and instead must use the MySQL ODBC connection (see MySQL Schemas). The MySQL ODBC interface was created by HermeTech for the sole purpose of allowing interfaces with older versions of MySQL.

 

IMPORTANT FOR ODBC CONNECTIONS: You should insure that you have the correct MySQL ODBC driver for your MS Windows operating system. If you are running a 64-Bit MS Windows system then you should have a 64-Bit MySQL ODBC driver.