Troubleshooting
Fix historian table creation failing on MySQL over ODBC
Short answer
Capture the MySQL error from the historian's table-creation attempt. Use the historian's runtime connection settings to check the database, account, grants and supported schema. After provisioning for the exact historian release, verify a known test value and its recorded timestamp.
On this page
Start with the native MySQL error from the historian’s table-creation attempt. The ODBC connection test can succeed while the runtime uses another DSN, the session lacks a default database, or MySQL refuses the actual SQL statement. Confirm support for the exact historian release, then isolate the failure before changing privileges or touching existing tables.
Reproduce the historian connection #
- Record the historian version, service identity, ODBC driver version and exact data source name. Use the historian’s own connection test if it provides one.
- On 64-bit Windows, check whether the historian process uses a 32-bit or 64-bit ODBC driver. The administrators in
SysWOW64andSystem32manage 32-bit and 64-bit System DSNs, respectively. Both administrators can display User DSNs. A User DSN configured in your desktop session belongs to that user and may be unavailable to the service account. - For MySQL Connector/ODBC, verify the DSN’s Database field. If the application supplies its own connection parameters, verify the effective database from the runtime connection. In the historian’s diagnostic console, or a test session that matches the runtime connection, run
SELECT DATABASE(), CURRENT_USER()andSHOW GRANTS. Record whether the database is correct and which MySQL account the server recognizes. Keep passwords and complete connection strings out of the fault report.
If the historian fails before reaching table setup, resolve the connection layer first. The connection refusal checklist explains how to distinguish a network refusal from an application error.
Match the native error to the next check #
Use the native MySQL code alongside the SQLSTATE and the operation that failed. If the historian exposes only a generic message, capture the native diagnostics on a staging connection or collect the relevant redacted entries from the application log.
| Error or observation | Check next |
|---|---|
MySQL 1046 (3D000) during unqualified CREATE TABLE |
The session has no default database. Set the Connector/ODBC Database field or the vendor-documented connection parameter and recheck DATABASE(). |
MySQL 1142 (42000) naming the denied CREATE command |
Check the account and grants on the exact target. Use the vendor’s documented provisioning method and grant only the actions it needs. |
| MySQL 1050 (42S01) | Inspect the existing table with SHOW CREATE TABLE and compare it to the supported schema. A previous attempt may have succeeded on part of the schema. Preserve existing data while you decide whether to resume or provision separately. |
| MySQL 1064 (42000) on the creation statement | Compare the actual DDL with the MySQL server version and the historian’s support matrix. A SQL Server-specific setup script needs a vendor-supported MySQL equivalent. |
| The ODBC administrator test passes and the historian cannot open the same DSN | Recheck runtime bitness, driver and service-account visibility. Then retest from the historian. |
Provision a supported schema with scoped privileges #
Use the historian’s setup utility or the MySQL-specific schema documented for that release. Where the vendor supports manual setup, provision in staging first and confirm the required column types, timestamp representation, keys and indexes. Compare version requirements before reusing a table left by an earlier attempt. If the vendor only documents a SQL Server target, get the supported backend requirements before modifying MySQL.
Use a dedicated historian account on the target database. Keep the provisioning account separate where the vendor allows it. A system that appends rows can have different grants from one that updates records, maintains partitions or purges old data. Confirm the grants against the chosen logging and retention settings. If a password was exposed in a screenshot or trace, rotate it and update the service using its supported credential storage.
Confirm a tagged write in the intended table #
Send a known test value at a recorded time. Query the target tag and timestamp window using the vendor’s documented schema. Check the value, units, timestamp and quality where applicable. Wait through the configured flush interval or trigger a flush through the vendor’s supported procedure, then check the runtime log for write errors. If the tag still has no new sample, check the collection enablement, trigger thresholds, buffering and effective database.
As a calculated check, a fixed five-second schedule makes 720 logging attempts in an hour. Expect 720 new rows only when each attempt inserts one row, the test window aligns with the schedule and the writer flushes within that window. A held value under deadband can produce fewer rows. Compare the exact test point and recorded value before treating a whole-table count as evidence.
Questions asked
Why can an ODBC test succeed when table creation fails?
A connection test checks the path and credentials used by that test. The historian submits a separate SQL statement for table creation. Check the native MySQL error from that attempt, then confirm what the same account can do on the target schema.
Can I reuse the vendor's SQL Server setup script on MySQL?
Check whether the exact historian release supports MySQL and whether the vendor provides a MySQL schema or setup tool. A working SQL Server configuration shows that the SQL Server path works. Any conversion needs a vendor-supported schema and a staging write test.
How do I tell which database the ODBC connection selected?
Use the historian's diagnostic console if available, or reproduce the connection with the same driver, DSN and connection parameters. Run SELECT DATABASE(), CURRENT_USER(). The first value is the selected database or NULL. The second identifies the MySQL account used to check privileges.
What privileges does the runtime need?
Use a dedicated account and the historian vendor's documented grants for the exact version and logging mode. A separate deployment account can provision the tables if the application supports pre-provisioning. Some systems also create partitions or rotate tables during operation. Verify the required actions before removing setup grants.
How can I prove data is arriving?
Log one identifiable test point or known value change. Query the intended tag and a short timestamp window in the documented data table. Verify the value, timestamp and quality field where available. A steady table count can also result from deadband, batching, updates or retention.
Corrections
- : Clarified how to identify MySQL ODBC errors, verify the vendor-supported schema and confirm a tagged write.