MySQL table cannot have the name "user"

[eg15]

Describe the bug

Anonimatron fails to process the MySQL table with the name user with the Java runtime exception "Unknown column 'Host' in 'field list'". To process my database, I have to rename the table first.

To Reproduce

Steps to reproduce the behavior:

1. Create a database with the following contents:

create database mydb;

create user 'myuser'@'%' identified by 'mypassword';
grant all on *.* to 'myuser'@'%';
flush privileges;

create table mydb.user (
         id int not null auto_increment primary key,
         firstname varchar(20),
         lastname varchar(20),
         creditcardnr varchar(20)
       );
  
insert into mydb.user
       (firstname,lastname, creditcardnr)
values ('Homer', 'Simpson','1234'),
       ('Marge', 'Simpson','5678'),
       ('Ned', 'Flanders','3456'),
       ('Charles', 'Burns','3456');

2. Configure Anonimatron as follows:

<?xml version="1.0" encoding="UTF-8"?>
<configuration jdbcurl="jdbc:mysql://localhost:3306/mydb"
               userid="myuser" password="mypassword">

  <table name="user">
    <column name="firstname" type="ELVEN_NAME" />
  </table>

</configuration>

3. Start with the following command: ./anonimatron.sh -config config.xml -synonyms synonyms.xml

4. See error in log:

Anonymization process started

Jdbc url      : jdbc:mysql://localhost:3306/mydb
Database user : myuser
To do         : 1 tables.

Pre-scanning table 'user', total progress  [0%, ETA 16:23:30]Exception in thread "main" java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: Unknown column 'Host' in 'field list'
	at com.rolfje.anonimatron.jdbc.JdbcAnonymizerService.processTableColumns(JdbcAnonymizerService.java:221)
	at com.rolfje.anonimatron.jdbc.JdbcAnonymizerService.preScanTable(JdbcAnonymizerService.java:110)
	at com.rolfje.anonimatron.jdbc.JdbcAnonymizerService.anonymize(JdbcAnonymizerService.java:73)
	at com.rolfje.anonimatron.Anonimatron.anonymize(Anonimatron.java:94)
	at com.rolfje.anonimatron.Anonimatron.main(Anonimatron.java:48)
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'Host' in 'field list'
	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.executeInternal(StatementImpl.java:764)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at com.rolfje.anonimatron.jdbc.JdbcAnonymizerService.processTableColumns(JdbcAnonymizerService.java:153)
	... 4 more

Expected behavior

The anonymization completes successfully as soon as the table user has been renamed to user1:

mysql> alter table user rename to user1;
Query OK, 0 rows affected (0.01 sec)

Run Anonimatron:

❯ ./anonimatron.sh -config config.xml -synonyms synonyms.xml

Anonymization process started

Jdbc url      : jdbc:mysql://localhost:3308/mydb
Database user : myuser
To do         : 1 tables.

Anonymizing table 'user1', total progress  [100%, ETA 16:31:59]
Anonymization process completed.

Desktop (please complete the following information):

• OS: macOS 10.15.6 with MySQL 5.6.44 in a Docker container
• Java version:

❯ java --version
java 14.0.2 2020-07-14
Java(TM) SE Runtime Environment (build 14.0.2+12-46)
Java HotSpot(TM) 64-Bit Server VM (build 14.0.2+12-46, mixed mode, sharing)

[realrolfje]

Thank you for this very concise writeup, it made searching for the problem much easier. It looks like a bug in Anonimatron caused by not actively selecting a catalog.

When requesting table metadata without limiting the search to the current catalog, MySQL connector gives built-in tables a higher priority. It seems to be solved by passing the connection catalog into the getPrimaryKeys() method: https://github.com/realrolfje/anonimatron/compare/bugfix/mysql-table-user-%23105

All current tests pass after this fix. For now it looks like this fix does not break anything, although I'm not sure if this introduces a problem for other database types.