Hi, I created an automatic user source to create a table in my MySQL database but its not creating the table, I am not sure what the issue is. Is there any common mistakes I could have made? Any help is welcome, Thank you.
What’s an automatic user source? Do you mean you wanted a database user source? For database user source it is still on you to make the database table and then provide Ignition the queries for finding and validating users.
If you are using an internal source, then that’s kept inside Ignitions internal database iirc, but definitely not inside your database.
When creating the User Source, there is an option to make it automatic and it says “In Automatic mode, tables will be created for you and all interaction with the database is handled automatically”
Do you have any exceptions in the Gateway logs? Specifically logger UserSource.DB_AutomaticMode
I have this error "DB_AutomaticMode 08Dec2020 13:22:57 [profileName=Users] Unable to create tables for automatic database user source."
Can you post the full stack trace for the exception?
Would this be it?
java.lang.Exception: Error running query: CREATE TABLE auto_users ("id" int NOT NULL AUTO_INCREMENT,"username" varchar(255) NOT NULL,"passwd" varchar(255) NOT NULL,"fname" varchar(255),"lname" varchar(255),"notes" varchar(255),"schedule" varchar(255),"language" varchar(255),"badge" varchar(255),PRIMARY KEY ("id"))
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:140)
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.createTable(DBTableSchema.java:109)
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.verifyAndUpdate(DBTableSchema.java:91)
at com.inductiveautomation.ignition.gateway.authentication.impl.DatabaseAutomaticUserSource.createTables(DatabaseAutomaticUserSource.java:172)
at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$ThrowableCatchingRunnable.run(BasicExecutionEngine.java:518)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"id" int NOT NULL AUTO_INCREMENT,"username" varchar(255) NOT NULL,"passwd" varch' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335)
at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108)
at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:322)
at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:322)
at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeUpdate(DelegatingStatement.java:80)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeUpdate(SRConnectionWrapper.java:913)
at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:138)
Yep, looks like it. Which software / version are you using for your database? And which version of Ignition are you using? Also, is your database connection running fine or is it faulted (you can look at the status page for database connection status). Wondering if you use this DB connection for other areas of Ignition and whether or not it is working fine in general?
I am using the latest version of MySQL and ignition maker edition. There are no errors in my database connection all looks good there and it works fine with other aspects of the project.
There are double quotes on the identifiers. Did IA change the default connection parameters to include ANSI mode? Otherwise check if you have the correct translator attached to this driver.
2 Likes
The translator I am using is the one recommended in the JDBC Drivers and Translators documentation.
I tested this out with a fresh install of Ignition Maker 8.1.0-final and MySQL 8.0.22 using the mysql-connector-java-8.0.22 JDBC driver. I had no trouble adding a database connection to this DB as well as a DB User Source Profile with automatic mode set. I left the connection properties on the driver as well as the Translator settings as default the way they were installed (and it doesn’t look like these defaults have changed recently). Are you sure your JDBC driver is pointing to the correct translator and the column quote character on that translator is ` and not " ? When I changed my translator’s column quote character to " (double quote) I saw the same exact exception in my logs that you are seeing in yours.
1 Like
Something I just experienced: if you were fiddling around with the translator / driver settings, and you have them in a place where they should be, you might need to essentially “restart” the user source profile (I did this by simply editing the DB user source profile settings and saving without actually changing any of the props). Without this gentle “kick” it looked like the user source was stuck in the broken state from when the translator was using the wrong column quote character.
2 Likes
Thank you very much, I changed the " in my translator to ` and it solved the issue. Would have never found that error myself.
1 Like
And I’ll pass that thanks to Phil - I didn’t notice the double quotes in your stack trace until he mentioned it.
1 Like