MySQL Bugs: #83706: Data Dictionary initialization fails when using binary charsets

Bug #83706	Data Dictionary initialization fails when using binary charsets
Submitted:	6 Nov 2016 12:52	Modified:	17 Nov 2016 14:27
Reporter:	Jaime Crespo (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S3 (Non-critical)
Version:	8.0.0-dmr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Aborting, BINARY, character_set, collation, data dictionary, failed, initialization

Description:
MySQL data dictionary fails to initialize and start when using binary collations:

character_set_server           = binary
character_set_filesystem       = binary
collation_server               = binary

with 

2016-11-06T12:29:29.267405Z 0 [ERROR] Data Dictionary initialization failed.
2016-11-06T12:29:29.267414Z 0 [ERROR] Aborting

How to repeat:
8.0.0-dmr Compiled from source code, with no special config except linking with openssl.

Relevant config:

```
[client]
port   = 3306
socket = /tmp/mysql.sock

[mysqld]

# administrative options
user       = mysql
socket     = /tmp/mysql.sock
port       = 3306
# MariaDB only
#extra-port = 3307
datadir    = /srv/sqldata
basedir    = /opt/mysql
tmpdir     = /srv/tmp
server_id  = 1

# charsets and collations
character_set_server           = binary
character_set_filesystem       = binary
collation_server               = binary
```

Both when doing:

```
rm -Rf /srv/sqldata/* && rm -Rf /srv/tmp/* && ./bin/mysqld --initialize-insecure --basedir=/opt/mysql --datadir=/srv/sqldata
```

I get:

```
2016-11-06T12:29:19.828673Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.
 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
mysqld: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'binary' at line 1
2016-11-06T12:29:29.267405Z 0 [ERROR] Data Dictionary initialization failed.
2016-11-06T12:29:29.267414Z 0 [ERROR] Aborting
```

And if I comment all 3 character_set/collation options (and it defaults to latin1), run initialization, but later it starts with those enabled:

```
root@sangai:/opt/mysql# /etc/init.d/mysql start
Starting MySQL
[FAIL....] The server quit without updating PID file (/srv/sqldata/sangai.pid). ... failed!

root@sangai:/opt/mysql# cat /srv/sqldata/sangai.err 
2016-11-06T12:24:57.047642Z mysqld_safe Starting mysqld daemon with databases from /srv/sqldata
2016-11-06T12:24:57.198406Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.
mysqld: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'binary' at line 1
2016-11-06T12:24:57.891864Z 0 [ERROR] Data Dictionary initialization failed.
2016-11-06T12:24:57.891871Z 0 [ERROR] Aborting
```

Suggested fix:
I assume there is some initialization and start routines that assume utf8- do not assume and enforce it wherever it is needed. I assume binary collation is not deprecated on 8.0 (all my databases depend on it!).

Hello Jaime Crespo,

Thank you for the report.
Verified as decsribed.

Thanks,
Umesh

-- 8.0

rm -rf 83437
bin/mysqld  --initialize-insecure --basedir=/export/umesh/server/binaries/Trunk/mysql-advanced-8.0 --datadir=/export/umesh/server/binaries/Trunk/mysql-advanced-8.0/83437 -v
bin/mysqld --basedir=/export/umesh/server/binaries/Trunk/mysql-advanced-8.0 --datadir=/export/umesh/server/binaries/Trunk/mysql-advanced-8.0/83437 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3306 --log-error=/export/umesh/server/binaries/Trunk/mysql-advanced-8.0/83437/log.err --character_set_server=binary --character_set_filesystem=binary --collation_server=binary 2>&1 &

2016-11-07T06:52:24.400020Z 0 [Note] InnoDB: Loading buffer pool(s) from /export/umesh/server/binaries/Trunk/mysql-advanced-8.0/83437/ib_buffer_pool
mysqld: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'binary' at line 1
2016-11-07T06:52:24.400446Z 0 [ERROR] Data Dictionary initialization failed.
2016-11-07T06:52:24.400460Z 0 [ERROR] Aborting

-- 5.7.16 - no issues

rm -rf 83437
bin/mysqld --initialize-insecure --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.7.16 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.7.16/83437 -v
bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.7.16 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.7.16/83437 --core-file --socket=/tmp/mysql_ushastry.sock --port=3306 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.7.16/83437/log.err --character_set_server=binary --character_set_filesystem=binary --collation_server=binary 2>&1 &

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.16: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show variables like '%coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | binary          |
| collation_server     | binary          |
+----------------------+-----------------+
3 rows in set (0.01 sec)

mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------------------+
| Variable_name            | Value                                                               |
+--------------------------+---------------------------------------------------------------------+
| character_set_client     | utf8                                                                |
| character_set_connection | utf8                                                                |
| character_set_database   | binary                                                              |
| character_set_filesystem | binary                                                              |
| character_set_results    | utf8                                                                |
| character_set_server     | binary                                                              |
| character_set_system     | utf8                                                                |
| character_sets_dir       | /export/umesh/server/binaries/GABuilds/mysql-5.7.16/share/charsets/ |
+--------------------------+---------------------------------------------------------------------+
8 rows in set (0.01 sec)

mysql> \! cat 83437/log.err
2016-11-07T06:55:23.511196Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-11-07T06:55:23.511349Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2016-11-07T06:55:23.511400Z 0 [Note] bin/mysqld (mysqld 5.7.16) starting as process 24612 ...
2016-11-07T06:55:23.517577Z 0 [Note] InnoDB: PUNCH HOLE support not available
2016-11-07T06:55:23.517609Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-11-07T06:55:23.517616Z 0 [Note] InnoDB: Uses event mutexes
2016-11-07T06:55:23.517621Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2016-11-07T06:55:23.517625Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-11-07T06:55:23.517630Z 0 [Note] InnoDB: Using Linux native AIO
2016-11-07T06:55:23.517944Z 0 [Note] InnoDB: Number of pools: 1
2016-11-07T06:55:23.518078Z 0 [Note] InnoDB: Using CPU crc32 instructions
2016-11-07T06:55:23.520304Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2016-11-07T06:55:23.529350Z 0 [Note] InnoDB: Completed initialization of buffer pool
2016-11-07T06:55:23.532095Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2016-11-07T06:55:23.544489Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016-11-07T06:55:23.553398Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2016-11-07T06:55:23.553478Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2016-11-07T06:55:23.571970Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2016-11-07T06:55:23.572997Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2016-11-07T06:55:23.573011Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2016-11-07T06:55:23.573353Z 0 [Note] InnoDB: Waiting for purge to start
2016-11-07T06:55:23.623493Z 0 [Note] InnoDB: 5.7.16 started; log sequence number 2532034
2016-11-07T06:55:23.623763Z 0 [Note] InnoDB: Loading buffer pool(s) from /export/umesh/server/binaries/GABuilds/mysql-5.7.16/83437/ib_buffer_pool
2016-11-07T06:55:23.623876Z 0 [Note] Plugin 'FEDERATED' is disabled.
2016-11-07T06:55:23.625470Z 0 [Note] InnoDB: Buffer pool(s) load completed at 161107  7:55:23
2016-11-07T06:55:23.630308Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2016-11-07T06:55:23.630330Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2016-11-07T06:55:23.630383Z 0 [Note] IPv6 is available.
2016-11-07T06:55:23.630400Z 0 [Note]   - '::' resolves to '::';
2016-11-07T06:55:23.630414Z 0 [Note] Server socket created on IP: '::'.
2016-11-07T06:55:23.640399Z 0 [Note] Event Scheduler: Loaded 0 events
2016-11-07T06:55:23.640538Z 0 [Note] bin/mysqld: ready for connections.
Version: '5.7.16'  socket: '/tmp/mysql_ushastry.sock'  port: 3306  MySQL Community Server (GPL)
mysql>

Posted by developer:
 
Thank you for testing MySQL 8.0.0-dmr! There are no plans to deprecate "binary" charset or collation and your example should indeed have worked. The collation name "binary" is, however, special in the sense that it needs to be quoted in situations where other collation names don't need to be. One such case is when using it as the default collation for a schema (database). When bootstrapping the new DD we use such a statement to create the DD schema, and the collation name comes from the --collation_server option. Unfortunately, the name is not quoted in the generated query string, and this is what is causing the bug.

Posted by developer:
 
Fixed as of the upcoming 8.0.1 release, and here's the changelog entry:

The data dictionary failed to initialize and start when using a binary
collation. The query string generated to create the data dictionary schema
did not add quotes to the binary collation name. 

Thank you for the bug report.