Issues with uppercase table and database names

[stevenwdv]

Steps to reproduce

Set lower_case_table_names = 1 in my.ini

Option 1:

1. Create a database UPPERCASE
2. Create a new table test in the database
3. PHPMyAdmin tries to load database UPPERCASE instead of uppercase (lower_case_table_names = 1); you are 'redirected' to something like db_structure.php?server=1&db=UPPERCASE&table=test. This does not happen when using the navigation panel afterwards (which displays lowercase names)
4. The following error messages appear:

Warning in .\libraries\controllers\table\TableStructureController.php#1299
 Illegal string offset 'Data_length'

Backtrace

.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
boolean false,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

Warning in .\libraries\controllers\table\TableStructureController.php#1302
 Illegal string offset 'Index_length'

Backtrace

.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
boolean false,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

Warning in .\libraries\controllers\table\TableStructureController.php#1314
 Illegal string offset 'Data_length'

Backtrace

.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
boolean false,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

Warning in .\libraries\controllers\table\TableStructureController.php#1318
 Illegal string offset 'Index_length'

Backtrace

.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
boolean false,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

Warning in .\libraries\controllers\table\TableStructureController.php#1336
 Illegal string offset 'Data_length'

Backtrace

.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
boolean false,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

Warning in .\libraries\controllers\table\TableStructureController.php#1337
 Illegal string offset 'Index_length'

Backtrace

.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
boolean false,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

Warning in .\libraries\controllers\table\TableStructureController.php#1342
 Illegal string offset 'Data_length'

Backtrace

.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
boolean false,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

Warning in .\libraries\controllers\table\TableStructureController.php#1342
 Illegal string offset 'Index_length'

Backtrace

.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
boolean false,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

Warning in .\templates\table\structure\display_table_stats.phtml#4
 Illegal string offset 'TABLE_COMMENT'

Backtrace

.\libraries\Template.php#152: include(.\templates\table\structure\display_table_stats.phtml)
.\libraries\controllers\table\TableStructureController.php#1379: PMA\libraries\Template->render(array)
.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
boolean false,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

Warning in .\templates\table\structure\display_table_stats.phtml#6
 Illegal string offset 'TABLE_COMMENT'

Backtrace

.\libraries\Template.php#152: include(.\templates\table\structure\display_table_stats.phtml)
.\libraries\controllers\table\TableStructureController.php#1379: PMA\libraries\Template->render(array)
.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
boolean false,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

Option 2:

1. Create a database lowercase
2. Create a table TEST
3. PHPMyAdmin again tries to load TEST instead of test
4. The same error messages appear

Additional info

PHPMyAdmin also throws an error when loading the structure of a table in an uppercase database or the structure of an uppercase table in an uppercase/lowercase database which was created with lower_case_table_names = 2 (so the table/database has an actual uppercase name).

E.g. there is a database called UPPERCASE with a table test, then tbl_structure.php?db=UPPERCASE&table=test throws an error.

This only and always happens when loading up the table with lower_case_table_names = 1

However, INFORMATION_SCHEMA does not seem to be affected by this.

This also happens when trying to load table test while it's actually called TEST if lower_case_table_names = 1.

I couldn't test with lower_case_table_names = 0, because MariaDB does not support this on Windows.

Expected behavior

1. PHPMyAdmin should use a lowercase name when loading a table after creating, because lower_case_table_names = 1
2. No error messages should appear anyway, even when the URL contains an uppercase name (I use collation utf8mb4_general_ci). Especially not if the name actually contains uppercase characters.

Actual behavior

1. Names are not corrected
2. Error messages appear when loading uppercase tables/databases (table structure) with lower_case_table_names = 1 set

Summary of error messages:

1. Set lower_case_table_names = 2
2. Create an uppercase table TEST in database lowercase

lower_case_table_names-->	1	2
tbl_structure.php?db=lowercase&table=test	Error in Microsoft Edge (redirects to TEST), Works in Firefox	Error!
tbl_structure.php?db=lowercase&table=TEST	Error!	Works

Server configuration

Operating system: Windows 10 x64

Web server: Apache 2.4.25 x86

Database: MariaDB 10.2.5 RC x64

PHP version: 7.1.1 x86

phpMyAdmin version: 4.7.0

Client configuration

Browser: Microsoft Edge 14, Mozilla Firefox 52.0.2

Operating system: Windows 10 x64

[ypy-pasha]

after click on structure,(table and db is lower case)

Server: localhost via TCP/IP
Server type: MariaDB
Server version: 10.1.21-MariaDB - mariadb.org binary distribution
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)

Web server

Apache/2.4.25 (Win32) OpenSSL/1.0.2j PHP/7.1.1
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
PHP version: 7.1.1

Warning in .\libraries\controllers\table\TableStructureController.php#1299
Illegal string offset 'Data_length'

Backtrace

.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

Warning in .\templates\table\structure\display_table_stats.phtml#6
Illegal string offset 'TABLE_COMMENT'

Backtrace

.\libraries\Template.php#152: include(.\templates\table\structure\display_table_stats.phtml)
.\libraries\controllers\table\TableStructureController.php#1379: PMA\libraries\Template->render(array)
.\libraries\controllers\table\TableStructureController.php#1255: PMA\libraries\controllers\table\TableStructureController->getTableStats()
.\libraries\controllers\table\TableStructureController.php#353: PMA\libraries\controllers\table\TableStructureController->displayStructure(
array,
array,
array,
,
array,
array,
)
.\tbl_structure.php#47: PMA\libraries\controllers\table\TableStructureController->indexAction()

[alexszilagyi]

@stevenwdv & @Pashaaa : Have one of you managed to solve this issue?

I have:

MAMP (Version 4.2.1 (852))

phpMyAdmin

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.6.35 - MySQL Community Server (GPL)
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)

Web server

Apache/2.2.32 (Unix) mod_wsgi/3.5 Python/2.7.13 PHP/7.1.8 mod_ssl/2.2.32 OpenSSL/1.0.2j DAV/2 mod_fastcgi/2.4.6 mod_perl/2.0.9 Perl/v5.24.0
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
PHP version: 7.1.8

And getting the following error:

[stevenwdv]

@alexszilagyi Well it's still open so it's probably not resolved :)

[alexszilagyi]

@stevenwdv : worth double checking, trying to find a solution or a workaround for this. Thanks!

[stevenwdv]

@alexszilagyi Setting lower_case_table_names = 2 or using lowercase names should work

[alexszilagyi]

@stevenwdv : Thanks, did a search on MAMP folder for lower_case_table_names keyword but couldn't find any of it.

Where my.ini is located ?

[stevenwdv]

@alexszilagyi Normally in someting like mysql/bin

[xitclub]

Got the same error today, undefined table indexes. I am using Mamp on mac and my development environment seems to be broken

[reevesdigital]

Thanks! I got this to work by adding:
lower_case_table_names=2
to my auto.cnf file buried in the db folder.

[BoddyMercado]

Hola yo lo pude solucionar simplemente cambiando el cotejamiento de la llave primaria o otro campo INT

[Marq91]

[UPDATE]
lowercase works for my :) nice

07-16-2018
i have lowercase names in my table, same problem currently :/ pls need help

Servidor: 127.0.0.1 via TCP/IP
Tipo de servidor: MariaDB
Conexión del servidor: No se está utilizando SSL Documentación
Versión del servidor: 10.1.32-MariaDB - mariadb.org binary distribution
Versión del protocolo: 10
Usuario: root@localhost
Conjunto de caracteres del servidor: UTF-8 Unicode (utf8)

Apache/2.4.33 (Win32) OpenSSL/1.1.0h PHP/7.2.5
Versión del cliente de base de datos: libmysql - mysqlnd 5.0.12-dev

Versión de PHP: 7.2.5

phpmyadmin version: 4.8.0.1

[eeraanjum13]

Do these steps:

open your MySQL configuration file: [drive]\xampp\mysql\bin\my.ini
look up for: # The MySQL server [mysqld]
add this right below it: lower_case_table_names = 2
save the file and restart MySQL service
From: http://webdev.issimplified.com/2010/03/02/mysql-on-windows-force-table-names-to-lowercase/

^Took this from another forum regarding lowercase issue and it worked fine for me.

here's the link of the original post:

https://stackoverflow.com/questions/8550789/where-to-change-the-value-of-lower-case-table-names-2-on-windows-xampp

[williamdes]

https://github.com/williamdes/mariadb-mysql-kbs/blob/master/dist/merged-raw.md#lower_case_table_names

https://mariadb.com/kb/en/library/server-system-variables/#lower_case_table_names

lower_case_table_names
Description: If set to 0 (the default on Unix-based systems), table names and aliases and database names are compared in a case-sensitive manner. If set to 1 (the default on Windows), names are stored in lowercase and not compared in a case-sensitive manner. If set to 2 (the default on Mac OS X), names are stored as declared, but compared in lowercase.
Commandline: --lower-case-table-names[=#]
Scope: Global
Dynamic: No
Data Type: numeric
Default Value: 0 (Unix), 1 (Windows), 2 (Mac OS X)
Range: 0 to 2

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names

If set to 0, table names are stored as specified and comparisons are case-sensitive.
If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive.
If set to 2, table names are stored as given but compared in lowercase.
This option also applies to database names and table aliases. For additional details, see Section 9.2.2, “Identifier Case Sensitivity”.

On Windows the default value is
1. On macOS, the default value is
2. On Linux, a value of 2 is not supported; the server forces the value to 0 instead.

You should not set lower_case_table_names to 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or macOS).
It is an unsupported combination that could result in a hang condition when running an INSERT INTO ... SELECT ... FROM tbl_name operation with the wrong tbl_name letter case.
With MyISAM, accessing table names using different letter cases could cause index corruption.

[williamdes]

Fixed by ca3f027 🎉