# User Source Automatic not creating table in MySQL DB **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [December 8, 2020, 12:51pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290 "2020-12-08T12:51:03Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![seanbuckley2698](https://avatars.discourse-cdn.com/v4/letter/s/d6d6ee/32.png) [@seanbuckley2698](https://forum.inductiveautomation.com/u/seanbuckley2698)\ **Post date:** [December 8, 2020, 12:51pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/1 "2020-12-08T12:51:03Z")
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. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [December 8, 2020, 1:13pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/2 "2020-12-08T13:13:28Z")
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. ---
**Author:** ![seanbuckley2698](https://avatars.discourse-cdn.com/v4/letter/s/d6d6ee/32.png) [@seanbuckley2698](https://forum.inductiveautomation.com/u/seanbuckley2698)\ **Post date:** [December 8, 2020, 1:28pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/3 "2020-12-08T13:28:49Z")
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” ---
**Author:** ![jspecht](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jspecht/32/6465_2.png) [@jspecht](https://forum.inductiveautomation.com/u/jspecht)\ **Post date:** [December 8, 2020, 4:18pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/4 "2020-12-08T16:18:30Z")
Do you have any exceptions in the Gateway logs? Specifically logger `UserSource.DB_AutomaticMode` ---
**Author:** ![seanbuckley2698](https://avatars.discourse-cdn.com/v4/letter/s/d6d6ee/32.png) [@seanbuckley2698](https://forum.inductiveautomation.com/u/seanbuckley2698)\ **Post date:** [December 8, 2020, 9:09pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/5 "2020-12-08T21:09:30Z")
> [@jspecht](#): > > UserSource.DB\_AutomaticMode I have this error "DB\_AutomaticMode 08Dec2020 13:22:57 [profileName=Users] Unable to create tables for automatic database user source." ---
**Author:** ![jspecht](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jspecht/32/6465_2.png) [@jspecht](https://forum.inductiveautomation.com/u/jspecht)\ **Post date:** [December 8, 2020, 9:19pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/6 "2020-12-08T21:19:54Z")
Can you post the full stack trace for the exception? ---
**Author:** ![seanbuckley2698](https://avatars.discourse-cdn.com/v4/letter/s/d6d6ee/32.png) [@seanbuckley2698](https://forum.inductiveautomation.com/u/seanbuckley2698)\ **Post date:** [December 8, 2020, 9:36pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/7 "2020-12-08T21:36:09Z")
Would this be it? ```auto 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) ``` ---
**Author:** ![jspecht](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jspecht/32/6465_2.png) [@jspecht](https://forum.inductiveautomation.com/u/jspecht)\ **Post date:** [December 8, 2020, 9:37pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/8 "2020-12-08T21:37:46Z")
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? ---
**Author:** ![seanbuckley2698](https://avatars.discourse-cdn.com/v4/letter/s/d6d6ee/32.png) [@seanbuckley2698](https://forum.inductiveautomation.com/u/seanbuckley2698)\ **Post date:** [December 8, 2020, 10:00pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/9 "2020-12-08T22:00:36Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 8, 2020, 10:06pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/10 "2020-12-08T22:06:44Z")
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. ---
**Author:** ![seanbuckley2698](https://avatars.discourse-cdn.com/v4/letter/s/d6d6ee/32.png) [@seanbuckley2698](https://forum.inductiveautomation.com/u/seanbuckley2698)\ **Post date:** [December 8, 2020, 10:29pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/11 "2020-12-08T22:29:04Z")
The translator I am using is the one recommended in the [JDBC Drivers and Translators](https://docs.inductiveautomation.com/display/DOC80/JDBC+Drivers+and+Translators) documentation. ---
**Author:** ![jspecht](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jspecht/32/6465_2.png) [@jspecht](https://forum.inductiveautomation.com/u/jspecht)\ **Post date:** [December 8, 2020, 11:55pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/12 "2020-12-08T23:55:26Z")
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. ---
**Author:** ![jspecht](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jspecht/32/6465_2.png) [@jspecht](https://forum.inductiveautomation.com/u/jspecht)\ **Post date:** [December 9, 2020, 12:01am UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/13 "2020-12-09T00:01:47Z")
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. ---
**Author:** ![seanbuckley2698](https://avatars.discourse-cdn.com/v4/letter/s/d6d6ee/32.png) [@seanbuckley2698](https://forum.inductiveautomation.com/u/seanbuckley2698)\ **Post date:** [December 9, 2020, 11:39am UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/14 "2020-12-09T11:39:43Z")
> [@jspecht](#): > > ` Thank you very much, I changed the " in my translator to ` and it solved the issue. Would have never found that error myself. ---
**Author:** ![jspecht](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jspecht/32/6465_2.png) [@jspecht](https://forum.inductiveautomation.com/u/jspecht)\ **Post date:** [December 9, 2020, 4:44pm UTC](https://forum.inductiveautomation.com/t/user-source-automatic-not-creating-table-in-mysql-db/41290/15 "2020-12-09T16:44:01Z")
And I’ll pass that thanks to Phil - I didn’t notice the double quotes in your stack trace until he mentioned it.