Phpmyadmin: Generated incorrect SQL command with lower_case_table_names=2 (OS X)

[smallufo]

After upgrading to MySQL 5.7.10 and phpmyadmin 4.5.3.1 , mysqladmin seems generate incorrect SQL command when I want to delete an row.

There is no where id=1 in the generated sql command.

This is my server environments :

[lem9]

Please show us an export of your table's structure and some sample data.

[smallufo]

OK . This is a very simple test database.
Just a table , with 3 columns . no any FKs.
testdb.sql.zip

The result is the same

[smallufo]

Edit & Copy also don't work .

[lem9]

Cannot reproduce with your sample table, phpMyAdmin 4.5.3.1, MySQL 5.7.8, PHP 5.6.7, mysqlnd 5.0.11, Chrome 46.

[smallufo]

My environment not mentioned : OS X 10.11.2 (El capitan) . Safari 9.0.2 & Firefox 43.0.3 both have this bug.

[smallufo]

After downgrading to 4.4.15.2 , it works fine.

[ibennetch]

I'm able to reproduce this on my Mac server. It appears to be related to case sensitivity; if I name the table with an all-lowercase name I do not have trouble. Also, I'm unable to, for instance, rename the Log table from the Operations tab.

I notice an oddity in your last screenshot where the SQL statement reads DELETE FROMtestdb.Log where log.id...-- the SQL query contains bothLogandlog`.

[alokrajiv]

I'm able to reproduce the error too with version 4.6.0-dev and Innodb on OS X.
I agree the rename from Log to anything else solves the issue.
However, Operations tab - rename was working in my setup.

I also didn't face the issue in Version: 4.4.9 and Innodb on OS X and everything worked as expected, but oddly yes the delete SQL Query created was DELETE FROM testdb_dev.Log WHERE log.id = 1 with mixing Log and log. Could be some identifier-case-insentivity problem doc.

[ibennetch]

On OSX, the default value for lower_case_table_names is 2, on Windows it's 1. My Debian Linux system is currently set to 0, though I'm uncertain whether that's a Debian or MySQL default.

According to the MySQL manual,

If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.

When I edit my.cnf to force lower_case_table_names to 1 as suggested by the MySQL documentation, new tables do not have any problems.

Is it possible that we're no longer properly handling case-sensitive MySQL installations?

[ibennetch]

Thanks for the follow-up @alokrajiv
Let's see what the others think of our discoveries.

[lem9]

@ibennetch Can you try a git bisect to find out where this broke?

[davideradice]

Same issue as in:

11669

11700

[MPLew-is]

Just wanted to say that I'm experiencing this issue on 4.5.4.1. It's primarily affecting transferring data from my production environment (CentOS) to a local environment (OS X), and only with tables that have capital letters. I've temporarily solved it (and thus restored the ability to use the edit, copy, and delete functions) by renaming the databases to something different and then back again. This converts all of the table names to lowercase and solves the problem, but I'd like the two environments to mirror cases if at all possible.

It seems like this isn't necessarily an environment issue, as the tables import perfectly fine and display in the correct case, it's just the helper copy/edit/delete functions that quick working.

[ibennetch]

@MPLew-is can you also tell us the value of lower_case_table_names on each system?

[MPLew-is]

In the production environment (CentOS), lower_case_table_names is 0, while it is 2 in the local environment (OS X).

[ibennetch]

According to git bisect, 739b142580d7fa6bf8e5ca70f45baced6e22aeeb is the commit causing the problem.

However, I wonder if we should detect when lower_case_table_names is running in an not-suggested configuration (for instance, http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_lower_case_table_names states that it should always be 1 when using InnoDB tables). It's questionable whether this is phpMyAdmin's responsibility to check and enforce.

[davideradice]

@ibennetch About the question regarding 'responsibility', in my issue #11669 I've shown that MysqlWorkbench and Valentina Studio allow me to edit 'database_1' rows, Phpmyadmin not.

[ibennetch]

Well, there are two matters at hand; the issue appears to be a defect/regression and I agree that it should be fixed, however in addition to that phpMyAdmin issues warnings on the main page for some situations where the installation is running in less than ideal conditions. For instance, user root without a password or if the client library version doesn't match. It's my musing that perhaps this is a similar situation where phpMyAdmin should alert the user.

But about the actual issue, I completely agree that the regression should be fixed.

[davideradice]

@ibennetch I do agree. Probably fixing regression and make an alert for creating CamelCase table names in a not case-sensitive environment is a good start point.

[MPLew-is]

@ibennetch There is another related regression here that happened prior to the commit you referenced. When importing a database with uppercase letters from a case-sensitive file system (lower_case_table_names = 0) to a case-insensitive system (lower_case_table_names = 2), stored procedures and functions are no longer represented as part of the database in the interface, but still exist in the system and work perfectly.

If I had to guess, the issue is related to phpmyadmin's processing of the show function/procedure status command. On a case-insensitive system, the result of this command has a database name that is lowercase, even if the database itself has uppercase letters. It looks like phpmyadmin is performing a case-sensitive comparison on those names to determine if the procedure/function is part of a particular database.

The procedures appear as expected in 4.4.9, but not in 4.4.15.4, so the regression must have happened between those two releases. I have not tested any other versions.

[MPLew-is]

Here are screenshots elaborating on my previous comment. The two cases shown are from the same MySQL server in the exact same state, only switching between two versions of phpmyadmin using the exact same config.inc.php file:

4.4.9 (correct behavior)

Result of show function status:

GUI list of routines:

4.4.15.4 (regressed behavior)

Result of show function status:

GUI list of routines:

Let me know if there's any other information you need.

[ibennetch]

@MPLew-is thank you for your additional report and screenshots. We'll look in to it and keep you posted here.

[MPLew-is]

In trying to resolve another case-sensitivity issue, I've narrowed down the version in which the change happened. The function/routine issue first appears in 4.4.15, and the last version with the desired behavior is 4.4.14.1. Hopefully that helps you narrow down the change that caused this.

[MPLew-is]

I've further tracked this issue to this change in commit dbf9ff2

This, along with a few other changes, resolves the other issue I was having (the relation view was not displaying existing foreign keys, nor an interface to add new ones), but creates the issue with routines being missing from the interface. It seems that, in solving #10961 and the #11461, the function getCollateForIS() in libraries/Util.class.php was created, and is applied every time that information_schema is accessed to change it to a binary collation. For case-insensitive systems, this apparently should not be the case, as removing the calls to that function from libraries/rte/rte_routines.lib.php and libraries/navigation/Nodes/Node_Database.class.php fixed both issues.

I'd be happy to make a pull request with the changes to fix this (after making sure the other linked issues are not un-fixed on case-sensitive systems by doing this, of course), but, as I'm a few versions behind, I'll wait until the main problem this issue references is resolved so I can make the changes against an updated version.

[kafoso]

Edit:
The problem lingers. See this reply: https://github.com/phpmyadmin/phpmyadmin/issues/11816#issuecomment-211868859

---

Original message:

I encountered this issue when I downloaded and installed the newest version of XAMPP (currently 7.0.4 / PHP 7.0.4) from https://www.apachefriends.org/download.html for Windows.

I added lower_case_table_names = 2 in my.ini, but it did not resolve the issue. After trying various hacks I found by Googling this issue, I decided to completely replace phpMyAdmin with the newest available version 4.6.0.

It now works beautifully.

Simply remove /path/to/xampp/phpMyAdmin and replace it with the contents of the ZIP-file downloaded here: https://github.com/phpmyadmin/phpmyadmin/archive/RELEASE_4_6_0.zip

You need to rename the folder. Notice that capital "M" and "A" in phpMyAdmin. I don't know if they're significant, but by now I'm pretty tired of letter-case issues.

When you access phpMyAdmin in browser (e.g. on http://localhost/phpmyadmin), and you see the login screen, use the default credentials. If that doesn't work, you may set your password in terminal using this command:

mysql -h your_host -u root

SET PASSWORD FOR root@localhost = PASSWORD('yourpassword');

Credits: http://askubuntu.com/questions/118772/how-to-change-root-password-for-mysql-and-phpmyadmin

[xini]

same issue as @kafoso, but 4.6.0 didn't resolve the issue. reverting to version 4.4.14.1 (as stated by @MPLew-is) did.

What I noticed is that the generated statement in 4.4.14.1 doesn't adhere to the case:

But it does at least accept the incorrect statement.

[kafoso]

@xini You are right. For some reason phpmydmin force-converted all table names to lowercase on import and so it worked - at first. However, they were still displayed in the their camel-case names for the remainder of the session. A caching issue, perhaps?

This is a massive bug and it worries me it hasn't been fixed after 4-5 months. I'm just using this for a hobby project, but I can imagine many companies are affected by this.

When strictly adhering to PSR-4, case sensitivity is an absolute must-have, since database tables should correspond 1:1 with PHP class entities (when using a DBAL like Doctrine 2). For readability and grep-ability reasons. This bug makes this very thing impossible.

Reverting to 4.4.14.1, then. Thanks.

[jonom]

Any progress on this issue? This has become a big problem for me all of a sudden so would be very grateful for a fix. As @kafoso touched on, I'm affected by it because the PHP-based CMS I use generates table names with StudlyCaps.

[m-derevyanko]

I have the same problem as @jonom . 1 to 1. CamelCase generated tables very important to me. FIx it please?

[lanterneRouge]

I have an equivalent problem, more like #11669 but that was closed with a note saying "Let's handle this in #11816."

Clicking the Edit link on a row results in an error message caused by an SQL query with a missing condition after "WHERE":
SELECT * FROMdbname.abcXYZ1_tableWHERE ;

MySQL 5.7.12 | phpMyAdmin 4.6.1 | OS X 10.11.5 | Firefox 46.0.1

[FedeX699]

Same problem in Windows 10, PHP 7.0.0, mysql 5.7.9, lower_case_table_names=2 (because I'm on Windows and I need table names like "SYS_users", etc.

[irs-thba1]

I have the same issue and just created a GitHub account in order to tell you. I use phpMyAdmin 4.5.2 as part of a WAMP named Uniform Server Zero XI on Windows 7. My lower_case_tablename setting is 2 because I want to have CamelCase table names. I want it because I use Entity Framework to generate C# classes out of my MySql tables and lowercase class names in C# is a no go for me! I tell you this to maybe raise more awareness about the usecases which could happen. If, like mentioned earlier in the thread, the solution just would be to display a warning about the "wrong lower_case_tablename setting" for my system I would be very angry. I have not yet tried to upgrade to 4.6.0.

[MPLew-is]

@ibennetch or any other member of the PHPMyAdmin team: can we get a status update on this issue, even if it's just to change its status to "won't fix"? This is a major problem for people using PHPMyAdmin in a Windows or Mac development environment, and it needs more attention than it's been getting. Personally, I've had to cobble together my own Franken-version somewhere between 4.4.14.1 and 4.4.15 in order to get a product anywhere close to working, and I'm running into more and more annoying bugs that have been fixed in later versions, versions which I'm virtually unable to use due to this issue.

[ibennetch]

The update basically is that there's no news at this time. I think it's very important to fix, but I haven't had a lot of time to track down the cause and some of the other developers aren't able to reproduce it in their servers. I agree that this should be fixed, and it's still in the roadmap to fix eventually.

[ibennetch]

For the other developers, I was able to bisect it back to commit 739b142580d7fa6bf8e5ca70f45baced6e22aeeb as the first bad commit, which does some work with generating the WHERE clause, so we should start looking there first for the cause.

[mjac]

I am also experiencing an empty WHERE clause on my MacBook Air with the latest homebrew version:

System Configuration

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.7.12 - Homebrew
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)
Web server

Apache/2.4.18 (Unix) PHP/7.0.7
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 241ae00989d1995ffcbbf63d579943635faf9972 $
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
PHP version: 7.0.7

I expect this is affecting a lot of Mac users. Let me know if I can provide any more details.

[torleif]

@MPLew-is Thanks for that, I've reverted back to an older version of phpmyadmin to get around this issue.

Like other users in this thread, I have a need for case sensitive table names.

[MPLew-is]

@torleif, not a problem.

Also, to anyone having this issue, I'd be happy to post my franken-version (some weird mash-up between 4.4.14.1 and 4.4.15). It keeps the ability to use case-sensitive tables, and fixes some issues I'd been having (with functions and procedures, and some other random things I can't think of at the moment) by porting in some of the changes from 4.4.15. It certainly has its quirks, and I don't guarantee anything in regards to functionality, but I'd be happy to set up a repository for it if anyone wants it.

[Eazyjd]

This problem also is present on the current version 4.6.4 of phpmyadmin when used on a windows system with MariaDB Version 10.1.16.

[miguelhughes]

My grain of sand:
My iOS is Yosemite, 10.10.5.
Reverting back to 4.4.14.1 fixed the issue for me as well.
On the latest version (4.6.4) I could not edit rows on tables that had upper case letters in them, but I could on all lowercase names.
The problem remained whether I used lower_case_table_names=2 or 1, both InnoDB and MyISAM tables.

[irs-thba1]

It's been nearly a year, since this issue was opened. It seems that this issue does not draw enough interest to be fixed. Maybe because using case sensitive TableNames is viewed to be irrelevant amoung the majority of the developers / users. I find this very sad, because phpMyAdmin is an important part of my software system. I am looking for alternatives (web & non web based) right now.

[motoom]

I just got bitten by this bug, on OSX. My suggestion is if this won't be fixed, at least give a warning during setup of phpMyAdmin.

[xini]

Comparing v4.4.14.1 to v4.4.15 I found 0bc6f30 and ef5177f that change the lower_case_table_names config for testing. One of the commit messages is "fix failing tests"... Maybe, when reverting these changes, the underlying issue will be revealed in testing?

[wdfee]

Even better would be a warning when creating a new database and new table. Best would be, if it just would be impossible to create uppercase table names (auto correcting e.g.).
But for me a fix is very important. I already have mixed case table names and cannot just rename them, because the table names are referenced in many table contents of big databases.

[xini]

<rant>@wdfee not being able to create uppercase table names is definitely not the solution. I am working with a software that uses an ORM that automatically creates the tables etc according to class names. So if you have a class called 'MyGreatObject' the table needs to be the same. etc. MySQL allows uppercase tables, so why should phpmyadmin not? Fixing phpmyadmin instead of introducing workarounds is the way to go.</rant>

[irs-thba1]

I have the same scenario as @xini and therefore can say that not using case sensitive table names is not an option for me.

MySQL allows uppercase tables, so why should phpmyadmin not?

And to add to this quote: PhpMyAdmin also supported case sensitive table names in the past correctly, at least until 4.4.15.

[nijel]

Okay, finally got to reproduce this on Linux as well:

• set lower-case-table-names = 2 in mysql server configuraiton
• move your databases to case insensitive filesystem (vfat)
• create database with one upper case

[nijel]

Fixed by 715bdf3a4993f1ba85a539c4fbea4819866295d8

[jonom]

Thanks @nijel! Looking forward to 4.7

[ibennetch]

This fix will be part of 4.6.6 when it's released (and 4.7.0, of course); in the mean time if you want to try out the fix you should be able to patch against 4.6.x with https://github.com/phpmyadmin/phpmyadmin/commit/715bdf3.patch

[irs-thba1]

What a wonderful christmas present for the "case sensitiv table name community"! Thank you @nijel .

[MPLew-is]

@nijel thank you! So far, everything looks great and things are working as expected.