New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Table Not Found - Wrong table name with locale characters #8565

Closed
ceyhunkerti opened this Issue Sep 20, 2018 · 3 comments

Comments

Projects
None yet
3 participants
@ceyhunkerti

ceyhunkerti commented Sep 20, 2018

  • Your browser and the version: (Chrome 69)
  • Your operating system: (Ubuntu 18.04)
  • Your databases: (Postgres)
  • Metabase version: (0.30.3)
  • Metabase hosting environment: (x86_64 x86_64 GNU/Linux)
  • Metabase internal database: (H2)

Getting following error

09-20 15:18:18 WARN server.HttpChannel :: /auth/login?redirect=%2F
org.h2.jdbc.JdbcSQLException: Table "SETTİNG" not found; SQL statement:
SELECT * FROM "SETTİNG" WHERE ("KEY" = ? AND "VALUE" > ?) LIMIT 1 [42102-197]

See actual table name is setting but metabase tries it with SETTİNG , İ is a turkish character
I think table names should be independent of the user locale settings.

So is there a workaround for this ?

P.S. I think this happened after changing to Turkish on settings

@1dot44mb

This comment has been minimized.

Show comment
Hide comment
@1dot44mb

1dot44mb Sep 29, 2018

Same issue, changing language to Turkish caused admin panel to crash with the error mentioned above.

1dot44mb commented Sep 29, 2018

Same issue, changing language to Turkish caused admin panel to crash with the error mentioned above.

@senior senior self-assigned this Oct 1, 2018

@senior

This comment has been minimized.

Show comment
Hide comment
@senior

senior Oct 1, 2018

Member

I am able to reproduce this issue. It appears to only affect users that have H2 as their Metabase application database as it appears to work fine with PostgreSQL.

Member

senior commented Oct 1, 2018

I am able to reproduce this issue. It appears to only affect users that have H2 as their Metabase application database as it appears to work fine with PostgreSQL.

@1dot44mb

This comment has been minimized.

Show comment
Hide comment
@1dot44mb

1dot44mb Oct 2, 2018

Default behaviour of uppercasing table and field names in H2 causes to i's to be İ's in queries. This results in error in tables like "SETTING".

The temporary solution is to download H2 from its own website, run bin/h2 to connect to H2 database of metabase. And update the SETTING table;
UPDATE SETTING SET VALUE="en" WHERE KEY="site_locale" AND VALUE="tr"
After you run the query, metabase will start working again. However you have to do the following:
1- Stop metabase before opening the H2 database for modification
2- The read/write permission for H2 database should be set in filesystem level before opening it in H2 editor (or another dbms)
3- The path to H2 database can be confusing, its name is metabase.db.mv but you should only include metabase.db in the database path input. Keep in mind that if the database you pointed does not exist, it automatically creates the database there, and opens it. So you should make sure that you point the right path. There is also a connection string configuration for not auto-creating the non-existent database, you can search and use it if necessary.

The permanent solution would be to create a bug report in H2, i think. I don't know if it can be solved inside metabase. Maybe it can be solved by using a connection string configuration to tell H2 not to uppercase field and table names. However i think i read somewhere that it is not fully tested and it may have side effects.

1dot44mb commented Oct 2, 2018

Default behaviour of uppercasing table and field names in H2 causes to i's to be İ's in queries. This results in error in tables like "SETTING".

The temporary solution is to download H2 from its own website, run bin/h2 to connect to H2 database of metabase. And update the SETTING table;
UPDATE SETTING SET VALUE="en" WHERE KEY="site_locale" AND VALUE="tr"
After you run the query, metabase will start working again. However you have to do the following:
1- Stop metabase before opening the H2 database for modification
2- The read/write permission for H2 database should be set in filesystem level before opening it in H2 editor (or another dbms)
3- The path to H2 database can be confusing, its name is metabase.db.mv but you should only include metabase.db in the database path input. Keep in mind that if the database you pointed does not exist, it automatically creates the database there, and opens it. So you should make sure that you point the right path. There is also a connection string configuration for not auto-creating the non-existent database, you can search and use it if necessary.

The permanent solution would be to create a bug report in H2, i think. I don't know if it can be solved inside metabase. Maybe it can be solved by using a connection string configuration to tell H2 not to uppercase field and table names. However i think i read somewhere that it is not fully tested and it may have side effects.

This was referenced Oct 2, 2018

senior added a commit that referenced this issue Oct 4, 2018

Specify English locale when formatting H2 SQL identifiers
Table names for H2 need to ensure that the English locale is specified
when uppercasing. Using the default locale when uppercasing can lead
to surprising results. Our `SETTING` table is a good example. If the
user has set the locale to Turkish, calling `.toUpperCase` on
"setting" will result in `SETTİNG`, which is not the same as `SETTING`
and will cause an error.

Fixes #8615, fixes #8565

@senior senior added this to the 0.31 milestone Oct 4, 2018

@senior senior closed this in #8632 Oct 4, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment