MySQL Bugs: #76780: Can't change lower_case_table

Bug #76780	Can't change lower_case_table_names
Submitted:	22 Apr 2015 1:51	Modified:	12 May 2015 16:14
Reporter:	Walter Gabrielsen III	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	6.3.1	OS:	Windows (Microsoft Windows 8.1)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
I can't change the lower_case_table_names system variable to "2" so that I can use uppercase letters in my table names.

How to repeat:
1. Open MySQL Workbench
2. connect to the database.
3. On the sidebar, under the "Instance" section, click on "Options File"
4. Under the "General" tab locate the "lower_case_table_names" option (it should begin unchecked).
5. Check that option and in the text field put "2".
6. On the sidebar, under the "Instance" section, click on "Startup/shutdown"
7. Click on the "Stop Server" button.
8. The server is still running.
9. on the sidebar, under the "Management" section click on "Status and System Variables"
10. click on the "System Variables" tab and search for "lower_case_table_names"
11. The value is still "1" which is the default for Windows.
12. under any schema try to create a table with a name with an uppercase letter, and it changes the table name to lowercase.

wb log file

Attachment: wb.log (application/octet-stream, text), 22.40 KiB.

Hello Walter Gabrielsen,

Thank you for the bug report.
I tried to reproduce the issue at my end using Microsoft Windows 8.1 and MySQL Workbench 6.3.2 build version but not seeing any issue in parameters change.
After changing the read only parameter server should be restart and please check server started or not.

Thanks,
Chiranjeevi.

mysql> use testdb
Database changed
mysql> SHOW VARIABLES like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> create table TEST_TABLE(sno int(3));
Query OK, 0 rows affected (2.64 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test_table       |
+------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES like 'lower%';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> use testdb
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***

Database changed
mysql> SHOW VARIABLES like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> create table TEST_TABLE1(sno int(3));
Query OK, 0 rows affected (0.09 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| TEST_TABLE1      |
| test_table       |
+------------------+
2 rows in set (0.00 sec)

mysql>

WB Screenshot

Attachment: 76780.PNG (image/png, text), 92.77 KiB.

I'm using Workbench 6.3.1, emphasis on the point 1. If there's an update maybe I'll try to download it and retry again.

But in the meantime I just wanted to say that I'm not using the console. I don't know how to use it. I'm using Workbench's graphical interface to set it up.

I'm going to attach some screenshots.

1. Setting lower_case_table_names to 2.
2. What I see after I click the Stop Server button.
3. The variable is still 1.

1 setting

Attachment: 1 setting.png (image/png, text), 94.14 KiB.

2 stop server

Attachment: 2 stop.png (image/png, text), 85.76 KiB.

3 system variable

Attachment: 3 variable.png (image/png, text), 132.65 KiB.

I just upgraded to Workbench 6.3.2. I'm still having problems.

My lower_case_table_names setting is still stuck on 1 even though it's set up for 2. (And yes I clicked on apply when setting the option.)

Okay, I'm going to copy your exact commands into the command shell:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use testdb
Database changed
mysql> SHOW VARIABLES like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.02 sec)

mysql> create table TEST_TABLE(sno int(3));
Query OK, 0 rows affected (0.13 sec)

mysql> SHOW VARIABLES like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.02 sec)

mysql> use testdb
Database changed
mysql> SHOW VARIABLES like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> create table TEST_TABLE1(sno int(3));
Query OK, 0 rows affected (0.13 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test_table       |
| test_table1      |
+------------------+
2 rows in set (0.00 sec)

mysql>

Hello Walter Gabrielsen,

Thank you for your feedback.
As per your steps, In step 2 server was not restarted and if we are modifying any read only parameters, then server have to restart otherwise it wouldn't affect, make sure server restarted or not.

Thanks,
Chiranjeevi.

confirmation screenshots

Attachment: 76780.zip (multipart/x-zip, text), 672.75 KiB.

I'm unable to shut down the server from Workbench.

I click the stop server button, and it says it shuts down and restartes, but it actually stays running through the whole process.

I have restarted my computer to force it to start over and read the options file, but it remains set at 1.

I have uninstalled and reinstalled the latest version of MySQL and Workbench, but it doesn't fix the problem.