Skip to content
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

vtgate: mysql_server_version returns inconsistent results #7498

Closed
bolt-leandro opened this issue Feb 16, 2021 · 7 comments
Closed

vtgate: mysql_server_version returns inconsistent results #7498

bolt-leandro opened this issue Feb 16, 2021 · 7 comments

Comments

@bolt-leandro
Copy link

bolt-leandro commented Feb 16, 2021

Overview of the Issue

Setting _mysql_server_version returns inconsistent and different values of reported MySQL version (default “5.7.9-Vitess”).
When trying this, some values/variables get changed while others do not. This confuses some clients which end up sending queries for 5.7 that are not supported in 8.0 (eg: query cache vars).

Reproduction Steps

Tested this on both v9.0 binaries (daa6085) and latest git compiled from source (10.0.0-SNAPSHOT a577cc4 branch 'master'). Each version gave different inconsistencies. Installed mysqld version is Percona 8.0.22-13 for Linux on x86_64.

To reproduce, use local example, and add option to vtgate start script:

vagrant@ubuntu-bionic:~/my-vitess-example$ grep mysql_server_version scripts/vtgate-up.sh
  -mysql_server_version '8.0.22' \ 

Then start local example and connect to vtgate, check versions reported:

    mysql --socket /tmp/mysql.sock
    mysql> show variables like '%version%';   
    mysql> \s

Observe discrepancy in values, eg:
Server version: 5.7.9-Vitess Percona Server (GPL), Release '13', Revision '6f7822f'

VS

| version | 8.0.22-13

Binary version

Test 1 - v9.0:

vagrant@ubuntu-bionic:~/my-vitess-example$ vtgate --version
Version: daa6085 (Git branch 'HEAD') built on Tue Jan 26 09:37:50 UTC 2021 by runner@fv-az195-520 using go1.15.7 linux/amd64

Test 2 - v10.0 git:

vagrant@ubuntu-bionic:~/my-vitess-example$ vtgate --version
Version: 10.0.0-SNAPSHOT (Git revision a577cc4da branch 'master') built on Sat Feb 13 13:09:08 UTC 2021 by vagrant@ubuntu-bionic using go1.15.6 linux/amd64

Operating system and Environment details

  • Operating system: Ubuntu 18.04.5 LTS
  • Kernel version (output of uname -sr): Linux 4.15.0-130-generic
  • Architecture (output of uname -m): x86_64
  • mysqld version: Percona 8.0.22-13 for Linux on x86_64

Log Fragments

Test 1 - v9.0: says 5.7.9 even though both mysql_server_version or mysqld are 8.0.x:

vagrant@ubuntu-bionic:~/my-vitess-example$ grep mysql_server_version scripts/vtgate-up.sh
  -mysql_server_version '8.0.22' \

vagrant@ubuntu-bionic:~/my-vitess-example$ vtgate --version
Version: daa6085 (Git branch 'HEAD') built on Tue Jan 26 09:37:50 UTC 2021 by runner@fv-az195-520 using go1.15.7 linux/amd64

vagrant@ubuntu-bionic:~/my-vitess-example$ mysql --socket /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.9-Vitess Percona Server (GPL), Release '13', Revision '6f7822f'  <=== THIS STILL HAS DEFAULT
....
mysql> show variables like '%version%';
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                          |
| immediate_server_version | 999999                                                 |
| innodb_version           | 8.0.22-13                                              |
| original_server_version  | 999999                                                 |
| protocol_version         | 10                                                     |
| slave_type_conversions   |                                                        |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                          |
| version                  | 8.0.22-13                                              |
| version_comment          | Percona Server (GPL), Release '13', Revision '6f7822f' |
| version_compile_machine  | x86_64                                                 |
| version_compile_os       | Linux                                                  |
| version_compile_zlib     | 1.2.11                                                 |
| version_suffix           |                                                        |
+--------------------------+--------------------------------------------------------+
13 rows in set (0.01 sec)
mysql> \s
--------------
mysql  Ver 8.0.22-13 for Linux on x86_64 (Percona Server (GPL), Release '13', Revision '6f7822f')
Connection id:		4
Current database:
Current user:		vt_app@localhost
SSL:			Not in use
Current pager:		grep -i version
Using outfile:		''
Using delimiter:	;
Server version:		5.7.9-Vitess Percona Server (GPL), Release '13', Revision '6f7822f'  <=== THIS STILL HAS DEFAULT
Protocol version:	10
Connection:		Localhost via UNIX socket
<SNIP>

Test 2 - v10.0 git :

Seems OK if vtgate's -mysql_server_version matches mysqld --version.

vagrant@ubuntu-bionic:~/my-vitess-example$ grep version scripts/vtgate-up.sh
  -mysql_server_version '8.0.22' \
  
vagrant@ubuntu-bionic:~/my-vitess-example$ vtgate --version
Version: 10.0.0-SNAPSHOT (Git revision a577cc4da branch 'master') built on Sat Feb 13 13:09:08 UTC 2021 by vagrant@ubuntu-bionic using go1.15.6 linux/amd64

vagrant@ubuntu-bionic:~/my-vitess-example$ mysqld --version
/usr/sbin/mysqld  Ver 8.0.22-13 for Linux on x86_64 (Percona Server (GPL), Release '13', Revision '6f7822f')

vagrant@ubuntu-bionic:~/my-vitess-example$ mysql --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 8.0.22 Version: 10.0.0-SNAPSHOT (Git revision a577cc4da branch 'master') built on Sat Feb 13 13:09:08 UTC 2021 by vagrant@ubuntu-bionic using go1.15.6 linux/amd64
Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, 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 "%version%";
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                          |
| immediate_server_version | 999999                                                 |
| innodb_version           | 8.0.22-13                                              |
| original_server_version  | 999999                                                 |
| protocol_version         | 10                                                     |
| slave_type_conversions   |                                                        |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                          |
| version                  | 8.0.22-13                                              |
| version_comment          | Percona Server (GPL), Release '13', Revision '6f7822f' |
| version_compile_machine  | x86_64                                                 |
| version_compile_os       | Linux                                                  |
| version_compile_zlib     | 1.2.11                                                 |
| version_suffix           |                                                        |
+--------------------------+--------------------------------------------------------+
13 rows in set (0.02 sec)

mysql> \s
--------------
mysql  Ver 8.0.22-13 for Linux on x86_64 (Percona Server (GPL), Release '13', Revision '6f7822f')
Connection id:		2
Current database:
Current user:		vt_app@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.22 Version: 10.0.0-SNAPSHOT (Git revision a577cc4da branch 'master') built on Sat Feb 13 13:09:08 UTC 2021 by vagrant@ubuntu-bionic using go1.15.6 linux/amd64
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock
Binary data as:		Hexadecimal
--------------

However, if vtgate's -mysql_server_version is different from mysqld --version, we get mixed results:

vagrant@ubuntu-bionic:~/my-vitess-example$ grep version scripts/vtgate-up.sh
  -mysql_server_version '8.0.11' \

vagrant@ubuntu-bionic:~/my-vitess-example$ vtgate --version
Version: 10.0.0-SNAPSHOT (Git revision a577cc4da branch 'master') built on Sat Feb 13 13:09:08 UTC 2021 by vagrant@ubuntu-bionic using go1.15.6 linux/amd64

vagrant@ubuntu-bionic:~/my-vitess-example$ mysqld --version
/usr/sbin/mysqld  Ver 8.0.22-13 for Linux on x86_64 (Percona Server (GPL), Release '13', Revision '6f7822f')

vagrant@ubuntu-bionic:~/my-vitess-example$ mysql --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 8.0.11 Version: 10.0.0-SNAPSHOT (Git revision a577cc4da branch 'master') built on Sat Feb 13 13:09:08 UTC 2021 by vagrant@ubuntu-bionic using go1.15.6 linux/amd64 
^^^^THIS IS FROM mysql_server_version
Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, 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 "%version%";
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                          |
| immediate_server_version | 999999                                                 |
| innodb_version           | 8.0.22-13                                              | <==  THIS IS FROM MYSQLD
| original_server_version  | 999999                                                 |
| protocol_version         | 10                                                     |
| slave_type_conversions   |                                                        |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                          |
| version                  | 8.0.22-13                                              |  <==  THIS IS FROM MYSQLD
| version_comment          | Percona Server (GPL), Release '13', Revision '6f7822f' |
| version_compile_machine  | x86_64                                                 |
| version_compile_os       | Linux                                                  |
| version_compile_zlib     | 1.2.11                                                 |
| version_suffix           |                                                        |
+--------------------------+--------------------------------------------------------+
13 rows in set (0.01 sec)

mysql> \s
--------------
mysql  Ver 8.0.22-13 for Linux on x86_64 (Percona Server (GPL), Release '13', Revision '6f7822f') <==  THIS IS FROM MYSQLD

Connection id:		2
Current database:
Current user:		vt_app@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.11 Version: 10.0.0-SNAPSHOT (Git revision a577cc4da branch 'master') built on Sat Feb 13 13:09:08 UTC 2021 by vagrant@ubuntu-bionic using go1.15.6 linux/amd64  <==  THIS IS FROM mysql_server_version
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock
Binary data as:		Hexadecimal
--------------
@askdba
Copy link
Contributor

askdba commented Feb 16, 2021

Related discussion in this PR #7337

@bolt-leandro
Copy link
Author

Noticed an error and corrected it. In last example above, corrected the "THIS IS FROM MYSQLD" and "THIS IS FROM mysql_server_version" comments.

@systay systay removed their assignment Apr 6, 2021
@harshit-gangal harshit-gangal self-assigned this Apr 9, 2021
@harshit-gangal
Copy link
Member

I do not see this issue on recent master

> mysql -P15306 -u rustapp --protocol=TCP

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-vitess-10.0.0-RC1 Version: 10.0.0-RC1 (Git revision 0e509bea9 branch 'show-vgtid') built on Fri Apr  9 08:53:32 IST 2021 by harshit@harshit-X1 using go1.15.6 linux/amd64

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> \s
--------------
mysql  Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Connection id:		1
Current database:	
Current user:		vt_app@localhost
SSL:			Not in use
Current pager:		less
Using outfile:		''
Using delimiter:	;
Server version:		5.7.9-vitess-10.0.0-RC1 Version: 10.0.0-RC1 (Git revision 0e509bea9 branch 'show-vgtid') built on Fri Apr  9 08:53:32 IST 2021 by harshit@harshit-X1 using go1.15.6 linux/amd64
Protocol version:	10
Connection:		localhost via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		15306
Binary data as:		Hexadecimal
--------------

mysql> show variables like '%version%';
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name            | Value                                                                                                                                                   |
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                                                                                                                           |
| immediate_server_version | 999999                                                                                                                                                  |
| innodb_version           | 8.0.23                                                                                                                                                  |
| original_server_version  | 999999                                                                                                                                                  |
| protocol_version         | 10                                                                                                                                                      |
| slave_type_conversions   |                                                                                                                                                         |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                                                                                                                           |
| version                  | 5.7.9-vitess-10.0.0-RC1                                                                                                                                 |
| version_comment          | Version: 10.0.0-RC1 (Git revision 0e509bea9 branch 'show-vgtid') built on Fri Apr  9 08:53:32 IST 2021 by harshit@harshit-X1 using go1.15.6 linux/amd64 |
| version_compile_machine  | x86_64                                                                                                                                                  |
| version_compile_os       | Linux                                                                                                                                                   |
| version_compile_zlib     | 1.2.11                                                                                                                                                  |
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)

Setting it to 8.0.23-VV

> mysql -P15306 -u rustapp --protocol=TCP

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.23-VV Version: 10.0.0-RC1 (Git revision 0e509bea9 branch 'show-vgtid') built on Fri Apr  9 08:53:32 IST 2021 by harshit@harshit-X1 using go1.15.6 linux/amd64

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> \s
--------------
mysql  Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Connection id:		1
Current database:	
Current user:		vt_app@localhost
SSL:			Not in use
Current pager:		less
Using outfile:		''
Using delimiter:	;
Server version:		8.0.23-VV Version: 10.0.0-RC1 (Git revision 0e509bea9 branch 'show-vgtid') built on Fri Apr  9 08:53:32 IST 2021 by harshit@harshit-X1 using go1.15.6 linux/amd64
Protocol version:	10
Connection:		localhost via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		15306
Binary data as:		Hexadecimal
--------------

mysql> show variables like '%version%';
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name            | Value                                                                                                                                                   |
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                                                                                                                           |
| immediate_server_version | 999999                                                                                                                                                  |
| innodb_version           | 8.0.23                                                                                                                                                  |
| original_server_version  | 999999                                                                                                                                                  |
| protocol_version         | 10                                                                                                                                                      |
| slave_type_conversions   |                                                                                                                                                         |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                                                                                                                           |
| version                  | 8.0.23-VV                                                                                                                                               |
| version_comment          | Version: 10.0.0-RC1 (Git revision 0e509bea9 branch 'show-vgtid') built on Fri Apr  9 08:53:32 IST 2021 by harshit@harshit-X1 using go1.15.6 linux/amd64 |
| version_compile_machine  | x86_64                                                                                                                                                  |
| version_compile_os       | Linux                                                                                                                                                   |
| version_compile_zlib     | 1.2.11                                                                                                                                                  |
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)

@harshit-gangal
Copy link
Member

@bolt-leandro can you also provide information regarding which client is being problematic so that we can try using that client directly and see.

Also, provide the issue you are seeing using that client because of this behaviour

@systay
Copy link
Collaborator

systay commented Apr 21, 2021

Seems we can't reproduce this error. Feel free to re-open if you are still having this issue

@systay systay closed this as completed Apr 21, 2021
@bolt-leandro
Copy link
Author

bolt-leandro commented Apr 29, 2021

Apologies, somehow I missed your updates. I did some tests with latest 10 stable version and seems problem is now solved.

I'll answer questions for completeness:

can you also provide information regarding which client is being problematic so that we can try using that client directly and see.

Client having the original problem was IntelliJ IDEA.

Also, provide the issue you are seeing using that client because of this behaviour

Problem was due to client thinking this was 5.7 and asking for query_cache info (query_cache doesn't exist in 8.0)

[08001] Could not create connection to database server. Attempted reconnect 3 times. Giving up. com.mysql.cj.exceptions.CJException: vtgate: http://vtgate-1:15000/: target: shard.80-.master, used tablet: zoneA-301 (vttablet-301): vttablet: rpc error: code = InvalidArgument desc = Unknown system variable 'query_cache_size' (errno 1193) (sqlstate HY000) (CallerID: ro_user): Sql: "/* mysql-connector-java-8.0.21 (Revision: 33f65445a1bcc544eb0120491926484da168f199) */select @@session.auto_increment_increment as auto_increment_increment, @@character_set_client as character_set_client, @@character_set_connection as character_set_connection, @@character_set_results as character_set_results, @@character_set_server as character_set_server, @@collation_server as collation_server, @@collation_connection as collation_connection, @@init_connect as init_connect, @@interactive_timeout as interactive_timeout, @@license as license, @@lower_case_table_names as lower_case_table_names, @@max_allowed_packet as max_allowed_packet, @@net_write_timeout as net_write_timeout, @@performance_schema as performance_schema, @@query_cache_size as query_cache_size, @@query_cache_type as query_cache_type, @@sql_mode as sql_mode, @@system_time_zone as system_time_zone, @@time_zone as time_zone, @@tx_isolation as transaction_isolation, @@wait_timeout as wait_timeout from dual", BindVars: {}.

@bolt-leandro
Copy link
Author

FWIW, latest test seems fine. Thanks for fixing this! 🙇

One last nitpick, latest stable version is 10.0.0 - Git revision 48dccf5.
However if you see vtgate --version output below, it still has the RC1 tag ;)

No need to reopen ticket.

vagrant@ubuntu-bionic:~/my-vitess-example$ dpkg -l | grep mysql
ii  percona-server-common            8.0.22-13-1.bionic                  amd64        Percona Server database common files (e.g. /etc/mysql/my.cnf)

vagrant@ubuntu-bionic:~/my-vitess-example$ vtgate --version
Version: 10.0.0-RC1 (Git revision 48dccf5 branch 'HEAD') built on Tue Apr 27 13:58:22 UTC 2021 by runner@fv-az201-958 using go1.15.11 linux/amd64

vagrant@ubuntu-bionic:~/my-vitess-example$ grep mysql_server_version scripts/vtgate-up.sh
  -mysql_server_version '8.0.22' \

vagrant@ubuntu-bionic:~/my-vitess-example$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.22 Version: 10.0.0-RC1 (Git revision 48dccf5 branch 'HEAD') built on Tue Apr 27 13:58:22 UTC 2021 by runner@fv-az201-958 using go1.15.11 linux/amd64

Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, 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> \s
--------------
mysql  Ver 8.0.22-13 for Linux on x86_64 (Percona Server (GPL), Release '13', Revision '6f7822f')

Connection id:		1
Current database:
Current user:		vt_app@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.22 Version: 10.0.0-RC1 (Git revision 48dccf5 branch 'HEAD') built on Tue Apr 27 13:58:22 UTC 2021 by runner@fv-az201-958 using go1.15.11 linux/amd64
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		15306
Binary data as:		Hexadecimal
--------------

mysql> show variables like '%version%';
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name            | Value                                                                                                                                             |
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                                                                                                                     |
| immediate_server_version | 999999                                                                                                                                            |
| innodb_version           | 8.0.22-13                                                                                                                                         |
| original_server_version  | 999999                                                                                                                                            |
| protocol_version         | 10                                                                                                                                                |
| slave_type_conversions   |                                                                                                                                                   |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3                                                                                                                     |
| version                  | 8.0.22                                                                                                                                            |
| version_comment          | Version: 10.0.0-RC1 (Git revision 48dccf5 branch 'HEAD') built on Tue Apr 27 13:58:22 UTC 2021 by runner@fv-az201-958 using go1.15.11 linux/amd64 |
| version_compile_machine  | x86_64                                                                                                                                            |
| version_compile_os       | Linux                                                                                                                                             |
| version_compile_zlib     | 1.2.11                                                                                                                                            |
| version_suffix           |                                                                                                                                                   |
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)

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

No branches or pull requests

4 participants