I have a gateway backup that used MSSQL. If I were to use this backup as a starting point for a new project, but it is using MySQL, what is the best way to accomplish this? The less need to go and massage things that get broke the better. The connection name will be the same, the only difference is the database.
Bump
Normally you can change many properties on the database reconfiguration page, but I do not think the driver of the database connection is one of them. You will need to make a new database connection to the MySQL database and set that as the default in your project properties.
If you refer to the database by name in your project scripts, delete the old MSSQL database connection, and then rename the MySQL connection to be exactly the same.
In terms of DB access auto-generated by Ignition (tag historian, SQL Bridge, etc), nothing should need to change on your end - we’ll automatically translate queries into DB appropriate syntax (as long as the DB name is exactly the same). However, any manually authored queries (named queries, query bindings, etc) may need adjustment - it depends on your queries.
If you’ve used MSSQL’s default square bracket delimiters in your queries, you’ll have to fix every one. The SQL standard specifies the double-quote character for identifier delimiters. MySQL is also non-compliant by default–it uses the back-quote character to delimit identifiers. Both databases can be configured to accept the SQL standard, usually called “ANSI Mode” or similar.
One of the reasons I prefer PostgreSQL–it uses the standard.
I had to delete the audit log and alarm journal to delete the connection. Then I was able to create the new connection and recreate audit log and journal. I had already made provisions to account for differences in the popular databases.
I was expecting worse, this was relatively painless.