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.

Sign up for GitHub

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

Jump to bottom

mysql_servers.hostgroup_id must be >= 0 #1244

Closed
ratabayev opened this issue Nov 16, 2017 · 14 comments
Closed

mysql_servers.hostgroup_id must be >= 0 #1244

ratabayev opened this issue Nov 16, 2017 · 14 comments

Comments

@ratabayev
Copy link

proxysql-1.4.3-1.1.el7.x86_64
CentOS Linux release 7.4.1708 (Core)

After recovering the broken node, I get the following error

/var/lib/proxysql/proxysql_node_monitor.log

Thu Nov 16 14:37:44 +06 2017 Cluster node (0:10.200.5.132:3306) current status 'OFFLINE_SOFT' in ProxySQL database! Thu Nov 16 14:37:44 +06 2017 Cluster node (0:10.200.5.134:3306) current status 'ONLINE' in ProxySQL database! Thu Nov 16 14:37:44 +06 2017 Cluster node (-1:10.200.5.133:3306) current status 'ONLINE' in ProxySQL database! Thu Nov 16 14:37:44 +06 2017 ###### Loading mysql_servers config into runtime ######

/var/lib/proxysql/proxysql_galera_checker.log
Thu Nov 16 14:37:45 +06 2017 ###### proxysql_galera_checker.sh SUMMARY ######
Thu Nov 16 14:37:45 +06 2017 Hostgroup writers 0
Thu Nov 16 14:37:45 +06 2017 Hostgroup readers -1
Thu Nov 16 14:37:45 +06 2017 Number of writers 1
Thu Nov 16 14:37:45 +06 2017 Writers are readers 1
Thu Nov 16 14:37:45 +06 2017 log file /var/lib/proxysql/proxysql_galera_checker.log

/var/lib/proxysql/proxysql.log
2017-11-16 14:37:45 main.cpp:814:ProxySQL_daemonize_phase3(): [ERROR] ProxySQL crashed. Restarting! 2017-11-16 14:37:45 [INFO] Angel process started ProxySQL process 14677 Standard ProxySQL Cluster rev. 0.1.0702 -- ProxySQL_Cluster.cpp -- Wed Oct 4 11:24:19 2017 Standard ProxySQL Admin rev. 0.2.0902 -- ProxySQL_Admin.cpp -- Wed Oct 4 11:24:19 2017 Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Wed Oct 4 11:24:19 2017 Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Wed Oct 4 11:24:19 2017 2017-11-16 14:37:45 [INFO] Dumping current MySQL Servers structures for hostgroup ALL 2017-11-16 14:37:45 [INFO] New mysql_replication_hostgroups table 2017-11-16 14:37:45 [INFO] New mysql_group_replication_hostgroups table 2017-11-16 14:37:45 [INFO] Dumping current MySQL Servers structures for hostgroup ALL HID: 0 , address: 10.200.5.132 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: NODE 1 HID: 0 , address: 10.200.5.133 , port: 3306 , weight: 100 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: NODE 2 HID: 0 , address: 10.200.5.134 , port: 3306 , weight: 10000 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: NODE 3 Standard Query Processor rev. 0.2.0902 -- Query_Processor.cpp -- Wed Oct 4 11:24:19 2017 In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Wed Oct 4 11:24:19 2017 Standard MySQL Monitor (StdMyMon) rev. 1.2.0723 -- MySQL_Monitor.cpp -- Wed Oct 4 11:24:19 2017 2017-11-16 14:37:46 network.cpp:53:listen_on_port(): [ERROR] bind(): Address already in use

In process list:

14676 ? S 0:00 mysql --defaults-file=/dev/stdin --protocol=tcp -Nse SELECT variable_value FROM global_variables WHERE variable_name IN ('mysql-monitor_username','mysql-monitor_password') ORDER BY variable_name DESC
14704 ? S 0:00 mysql --defaults-file=/dev/stdin --protocol=tcp -Bse SELECT variable_value FROM global_variables WHERE variable_name='mysql-monitor_username'
more than 100 similar rows
15399 ? S 0:00 mysql --defaults-file=/dev/stdin --protocol=tcp -Bse SELECT variable_value FROM global_variables WHERE variable_name='mysql-monitor_username'
@renecannao
Copy link
Contributor

I am not sure it really crashed.
I think the child process failed to start because it couldn't bind on address:port , and the parent process assumes it crashed, trying to restart it.

Probably you have multiple proxysql running.

@ratabayev
Copy link
Author

Really, there are two processes in the process list.
But why? How to solve this problem?
I use
sudo service proxysql start/stop

@renecannao
Copy link
Contributor

Unfortunately I cannot know why your system has multiple proxysql running.
Maybe one instance was executed manual or via systemd, and the other via service. I cannot know this.
I recommended to check and review system logs.

@ratabayev
Copy link
Author

I made a mistake. It was a child process.
As a result, there is one parent process and one child process in the process list.
Do you have any ideas where I can find the problem?

@ratabayev
Copy link
Author

Another error log.

proxysql: MySQL_HostGroups_Manager.cpp:960: void MySQL_HostGroups_Manager::generate_mysql_servers_table(int*): Assertion `rc==6' failed.
Error: signal 6:
/usr/bin/proxysql(_Z13crash_handleri+0x1a)[0x444caa]
/lib64/libc.so.6(+0x35270)[0x7fea9f69a270]
/lib64/libc.so.6(gsignal+0x37)[0x7fea9f69a1f7]
/lib64/libc.so.6(abort+0x148)[0x7fea9f69b8e8]
/lib64/libc.so.6(+0x2e266)[0x7fea9f693266]
/lib64/libc.so.6(+0x2e312)[0x7fea9f693312]
/usr/bin/proxysql(_ZN24MySQL_HostGroups_Manager28generate_mysql_servers_tableEPi+0x924)[0x45a144]
/usr/bin/proxysql(_ZN24MySQL_HostGroups_Manager6commitEv+0x58f)[0x45ea0f]
/usr/bin/proxysql(_ZN14ProxySQL_Admin29load_mysql_servers_to_runtimeEv+0x5a5)[0x497535]
/usr/bin/proxysql(_Z34admin_handler_command_load_or_savePcjP13MySQL_SessionP14ProxySQL_AdminPS_Pj+0x1c60)[0x4a74b0]
/usr/bin/proxysql(_Z21admin_session_handlerP13MySQL_SessionPvP10_PtrSize_t+0xbd9)[0x4a8e79]
/usr/bin/proxysql(_ZN13MySQL_Session7handlerEv+0x191a)[0x47d02a]
/usr/bin/proxysql(_Z11child_mysqlPv+0x251)[0x48c4d1]
/lib64/libpthread.so.0(+0x7e25)[0x7feaa0f44e25]
/lib64/libc.so.6(clone+0x6d)[0x7fea9f75d34d]
2017-11-16 15:13:41 main.cpp:814:ProxySQL_daemonize_phase3(): [ERROR] ProxySQL crashed. Restarting!
2017-11-16 15:13:41 [INFO] Angel process started ProxySQL process 2553
Standard ProxySQL Cluster rev. 0.1.0702 -- ProxySQL_Cluster.cpp -- Wed Oct 4 11:24:19 2017
Standard ProxySQL Admin rev. 0.2.0902 -- ProxySQL_Admin.cpp -- Wed Oct 4 11:24:19 2017
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Wed Oct 4 11:24:19 2017
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Wed Oct 4 11:24:19 2017
2017-11-16 15:13:41 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
2017-11-16 15:13:41 [INFO] New mysql_replication_hostgroups table
2017-11-16 15:13:41 [INFO] New mysql_group_replication_hostgroups table
2017-11-16 15:13:41 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 0 , address: 10.200.5.132 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: NODE 1
HID: 0 , address: 10.200.5.133 , port: 3306 , weight: 100 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: NODE 2
HID: 0 , address: 10.200.5.134 , port: 3306 , weight: 10000 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: NODE 3
Standard Query Processor rev. 0.2.0902 -- Query_Processor.cpp -- Wed Oct 4 11:24:19 2017
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Wed Oct 4 11:24:19 2017
Standard MySQL Monitor (StdMyMon) rev. 1.2.0723 -- MySQL_Monitor.cpp -- Wed Oct 4 11:24:19 2017
2017-11-16 15:13:42 network.cpp:53:listen_on_port(): [ERROR] bind(): Address already in use

@renecannao
Copy link
Contributor

Because it reports the port is already in use, I would use netstat to check that.
Error log after the "crash"(?) is not useful: eventually, before the event can be useful.
The numerous mysql commands may also indicate some misconfiguration. For example, hostgroup -1 for readers is very suspicion

@renecannao
Copy link
Contributor

I just saw your new comment.
I would try to change hostgroup -1 in hostgroup 1

@ratabayev
Copy link
Author

ratabayev commented Nov 16, 2017 •
edited
Loading

Thanks. It helped to solve problem with crashes.

As I understand, "-1" is used for configuration without division into readers and writers in percona documentation.

So, I use "-1" intentionally. Now, I don't know how to configure ProxySQL to use
only one node for write/read. Is it possible to enforce ProxySQL to use only one node for read/write even if after crashing and recovering main node there are some connections to reserve node?

@renecannao renecannao reopened this Nov 16, 2017
@renecannao
Copy link
Contributor

Reopening this issue, because the crash needs to be avoided even in cases of misconfiguration.
Percona's documentation there seems quite incorrect, -1 shouldn't be used.

@ratabayev
Copy link
Author

Is it possible to enforce ProxySQL to use only one node for read/write even if after crashing and recovering main node there are some connections to reserve node?

@renecannao
Copy link
Contributor

@daido : the galera checker is a simple script, you can customize it for your specific needs.
Thanks

@renecannao
Copy link
Contributor

Backtrace from 1.4.4:

0x00007ffff64c4428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
54      ../sysdeps/unix/sysv/linux/raise.c: No such file or directory.
(gdb) bt
#0  0x00007ffff64c4428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
#1  0x00007ffff64c602a in __GI_abort () at abort.c:89
#2  0x00007ffff64bcbd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x721007 "rc==SQLITE_LOCKED", 
    file=file@entry=0x7202f4 "MySQL_HostGroups_Manager.cpp", line=line@entry=972, 
    function=function@entry=0x725560 <MySQL_HostGroups_Manager::generate_mysql_servers_table(int*)::__PRETTY_FUNCTION__> "void MySQL_HostGroups_Manager::generate_mysql_servers_table(int*)") at assert.c:92
#3  0x00007ffff64bcc82 in __GI___assert_fail (assertion=0x721007 "rc==SQLITE_LOCKED", file=0x7202f4 "MySQL_HostGroups_Manager.cpp", line=972, 
    function=0x725560 <MySQL_HostGroups_Manager::generate_mysql_servers_table(int*)::__PRETTY_FUNCTION__> "void MySQL_HostGroups_Manager::generate_mysql_servers_table(int*)") at assert.c:101
#4  0x00000000004f4402 in MySQL_HostGroups_Manager::generate_mysql_servers_table (this=0x7ffff6047000, _onlyhg=0x0) at MySQL_HostGroups_Manager.cpp:972
#5  0x00000000004f2fd4 in MySQL_HostGroups_Manager::commit (this=0x7ffff6047000) at MySQL_HostGroups_Manager.cpp:770
#6  0x000000000055c6f2 in ProxySQL_Admin::load_mysql_servers_to_runtime (this=0x7ffff612f000) at ProxySQL_Admin.cpp:6765
#7  0x0000000000543230 in admin_handler_command_load_or_save (query_no_space=0x7fffeda170e0 "load mysql servers to runtime", query_no_space_length=29, 
    sess=0x7fffeda1b000, pa=0x7ffff612f000, q=0x7fffee5fe1b0, ql=0x7fffee5fe160) at ProxySQL_Admin.cpp:1466
#8  0x00000000005474ea in admin_session_handler (sess=0x7fffeda1b000, _pa=0x7ffff612f000, pkt=0x7fffee5fe9e0) at ProxySQL_Admin.cpp:2305
#9  0x000000000051bc10 in MySQL_Session::handler (this=0x7fffeda1b000) at MySQL_Session.cpp:2075
#10 0x000000000054a82f in child_mysql (arg=0x7ffff0c0f158) at ProxySQL_Admin.cpp:2952
#11 0x00007ffff7bc16ba in start_thread (arg=0x7fffee5ff700) at pthread_create.c:333
#12 0x00007ffff65963dd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Crash happens in:

#4  0x00000000004f4402 in MySQL_HostGroups_Manager::generate_mysql_servers_table (this=0x7ffff6047000, _onlyhg=0x0) at MySQL_HostGroups_Manager.cpp:972
972                     SAFE_SQLITE3_STEP(statement1);
(gdb) p rc
$2 = 19

19 = SQLITE_CONSTRAINT

hostgroup_id seems to go into integer overflow:

(gdb) p *mysrvc->myhgc 
$7 = {hid = 4294967295, current_time_now = 0, new_connections_now = 0, mysrvs = 0x7fffeda25380}

@rameshvs02
Copy link

@daido I have created percona bug report to resolve doc issue: https://jira.percona.com/browse/PSQLADM-4.

If you are installed proxysql from percona repository please use proxysql-admin tool to setup PXC nodes in proxysql.
proxysql-admin usage info : https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/proxysql.html#automatic-configuration

@renecannao renecannao changed the title [ERROR] ProxySQL crashed. Restarting! (Address already in use) mysql_servers.hostgroup_id must be >= 0 Nov 16, 2017
@pondix pondix mentioned this issue Dec 1, 2017
Merged
@renecannao
Copy link
Contributor

Fixed in #1270

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@ratabayev @renecannao @rameshvs02