realrolfje/anonimatron

MySQL table cannot have the name "user"

Closed this issue · 1 comments

eg15 commented

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');
  1. 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>

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

  2. 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 commented

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.

  • 👍1