slave database mysql failed to start after reboot

[dhijrwn]

I after I install the slave server.

I check the database and tested it by setting slave server from the system settings.
But what weird is I cannot query on mysql.user because i'm checking the slave user and the main databaser user if they exist on the slave mysq.user

I was getting these errors:
select * from mysql.user;
The user specified as a definer ('mariadb.sys'@'localhost')

after reboot the mysql server was not start automatically, I ran systemctl start mysql but it failed, then i ran mysqld_safe --user=root i get these errors on /var/log/mysql/mysqld.log
to fix this i ran chown -R mysql.mysql /var/lib/mysql

140630894916480 [ERROR] mysqld: File '/var/lib/mysql/mysql-bin.~rec~' not found (Errcode: 13 "Permission denied")
8:06:34 140630894916480 [ERROR] MYSQL_BIN_LOG::open_purge_index_file failed to open register file.
8:06:34 140630894916480 [ERROR] MYSQL_BIN_LOG::open_index_file failed to sync the index file.
8:06:34 140630894916480 [ERROR] Aborting

also this
Fatal error: Can't open and lock privilege tables: 'mysql.user' is not TABLE

but when I ran /usr/sbin/mysqld --skip-grant-tables --general-log --user=root // the putty window just stuck with this command so I open a new connection to the slave server
or /usr/sbin/mysqld --defaults-file=/etc/my.cnf --user=mysql --skip-grant-tables
then mysql command suddenly works but the status of systemctl status mysql is still inactive.

Im getting these logs
2022-07-04 8:50:37 140145106424704 [Note] Reading of all Master_info entries succeeded
2022-07-04 8:50:37 140145106424704 [Note] Added new Master_info '' to hash table
2022-07-04 8:50:37 140145106424704 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.2.32-MariaDB-log' socket: '/run/mysql/mysql.sock' port: 3306 SUSE package
2022-07-04 8:50:37 140140507637504 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000801' at position 54783010, relay log '/var/lib/mysql/mysql-relay-bin.000010' position: 508
2022-07-04 8:50:37 140140507944704 [Note] Slave I/O thread: connected to master 'xxxuserxxx_slave@192.168.3.3:3306',replication started in log 'mysql-bin.000801' at position 5478301

after that I can query on the database now using the mysql command on the mysql table and also asterisk table but when I ran the systemctl status mysql it is still down which is weird to me because i can still access the database
aded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Mon 2022-07-04 08:44:23 EDT; 9min ago

I check the show slave status, Everything is good. There is no last_error
Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Mast>
+----------------------------------+-------------+---------------+-------------+---------------+------------------+---------------------+------------------------+---------------+----------->
| Waiting for master to send event | 192.168.3.3 | xxxuserxxx_slave | 3306 | 60 | mysql-bin.000801 | 77393831 | mysql-relay-bin.000012 | 22611094 | mysql-bin



Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Tab>
-----------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-------------------------->
00801 | Yes | Yes | | | | | | >
-----------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-------------------------



+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+------------>
e | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_>
--+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+------------>
| 0 | | 0 | 79672236 | 24890087 | None | | 0 | No | | >
--+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+------------




+-------------------------+-----------------------------+---------------+-----------+---------------------+--------------------------------------------------------------------->
Gtid_IO_Pos | Replicate_Do_Domain_Ids | Replicate_Ignore_Domain_Ids | Parallel_Mode | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State >
-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+--------------------------------------------------------------------->
| | | optimistic | 0 | NULL | Slave has read all relay log; waiting for the slave I/O thread to up>
-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+---------------------------------------------------------------------

After all of that, I set again the slave server on system settings and it is working but every time I reboot the slave server same errors, so ill just going to redo the process.

I used vicibox-install script database Y and then slave Y then supplied our custom configuration for the database config.

more logs on /var/log/mysql/mysqld.log

Status information:

Current dir: /srv/mysql/data/
Running threads: 11 Cached threads: 1 Stack size: 299008

Key caches:
default
Buffer_size: 4294967296
Block_size: 1024
Division_limit: 100
Age_threshold: 300
Partitions: 64
blocks used: 48
not flushed: 0
w_requests: 1105
writes: 28
r_requests: 18234
reads: 61


handler status:
read_key: 76884
read_next: 364
read_rnd 3097
read_first: 53
write: 55382
delete 53277
update: 6018

Table status:
Opened tables: 53
Open tables: 28
Open files: 52
Open streams: 0

Alarm status:
Active alarms: 0
Max used alarms: 0
Next alarm time: 0

Memory status:
Non-mmapped space allocated from system: 140206080
Number of free chunks: 34568
Number of fastbin blocks: 15
Number of mmapped regions: 154
Space in mmapped regions: 164401152
Maximum total allocated space: 0
Space available in freed fastbin blocks: 832
Total allocated space: 85014880
Total free space: 55191200
Top-most, releasable space: 20056080
Estimated memory (with thread stack): 308195328
Global memory allocated by server: 4485955496
Memory allocated by threads: 423672



Events status:
LLA = Last Locked At LUA = Last Unlocked At
WOC = Waiting On Condition DL = Data Locked
The Event Scheduler is disabled

2022-07-04 9:18:24 139966504515328 [ERROR] Fatal error: Can't open and lock privilege tables: 'mysql.user' is not TABLE

Status information:

Current dir: /srv/mysql/data/
Running threads: 11 Cached threads: 0 Stack size: 299008

Key caches:
default
Buffer_size: 4294967296
Block_size: 1024
Division_limit: 100
Age_threshold: 300
Partitions: 64
blocks used: 52
not flushed: 0
w_requests: 1105
writes: 28
r_requests: 18247
reads: 65


handler status:
read_key: 76891
read_next: 365
read_rnd 3098
read_first: 55
write: 55388
delete 53282
update: 6020

Table status:
Opened tables: 56
Open tables: 0
Open files: 13
Open streams: 0

Alarm status:
Active alarms: 0
Max used alarms: 0
Next alarm time: 0

Memory status:
Non-mmapped space allocated from system: 140324864
Number of free chunks: 34634
Number of fastbin blocks: 51
Number of mmapped regions: 154
Space in mmapped regions: 164401152
Maximum total allocated space: 0
Space available in freed fastbin blocks: 2912
Total allocated space: 82984624
Total free space: 57340240
Top-most, releasable space: 20056080
Estimated memory (with thread stack): 308015104
Global memory allocated by server: 4483931856
Memory allocated by threads: 423672



Events status:
LLA = Last Locked At LUA = Last Unlocked At
WOC = Waiting On Condition DL = Data Locked
The Event Scheduler is disabled

2022-07-04 9:18:24 139966504515328 [ERROR] Fatal error: Can't open and lock privilege tables: 'mysql.user' is not TABLE
2022-07-04 10:37:42 139966408218368 [ERROR] mysqld: Can't find record in 'vicidial_hopper'
2022-07-04 10:37:42 139966408218368 [Warning] Slave SQL: Could not execute Update_rows_v1 event on table asterisk.vicidial_hopper; Can't find record in 'vicidial_hopper', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000801, end_log_pos 244705230, Gtid 0-1-2063586003, Internal MariaDB error code: 1032
2022-07-04 10:37:46 139966408525568 [ERROR] mysqld: Can't find record in 'vicidial_hopper'
2022-07-04 10:37:46 139966408525568 [Warning] Slave SQL: Could not execute Update_rows_v1 event on table asterisk.vicidial_hopper; Can't find record in 'vicidial_hopper', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000801, end_log_pos 244906455, Gtid 0-1-2063586482, Internal MariaDB error code: 1032
2022-07-04 10:37:48 139966408218368 [ERROR] mysqld: Can't find record in 'vicidial_hopper'
2022-07-04 10:37:48 139966408218368 [Warning] Slave SQL: Could not execute Update_rows_v1 event on table asterisk.vicidial_hopper; Can't find record in 'vicidial_hopper', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000801, end_log_pos 245022840, Gtid 0-1-2063586772, Internal MariaDB error code: 1032

[williamconley]

What method did you use to build the "slave server"?

If you did not use the vicibox .iso installer, please post a link to the instruction set you used.

If you DID use the vicibox .iso installer, please post the script questions/answers you used during the install (hopefully you captured these before the final "yes" and placed it in a text file somewhere?)

[dhijrwn]

What method did you use to build the "slave server"?

If you did not use the vicibox .iso installer, please post a link to the instruction set you used.

If you DID use the vicibox .iso installer, please post the script questions/answers you used during the install (hopefully you captured these before the final "yes" and placed it in a text file somewhere?)

I used the vicibox-install script

--- ViciBox v.9.0 Install Summary ---

Expert : Yes
Legacy : No
Database : Yes
Slave ID : 2
Web : No
Telephony: No
Archive : No
Firewall : Disabled

--- Configuration Information ---
- Database -
SVN Rev : 3595
IP Addr : 192.168.3.3
Name : asterisk
User : xxxx_cron
Password : xxxx_cronpass
Cust User: xxxx_custom
Cust Pass: xxxx_custompass
Slave Usr: xxxx_slave
Slave Pw : xxxx_slavepass
Port : 3306

also I got this error so i just grant the user to have a show view command
mysqldump: Couldn't execute 'show create table `user`': SHOW VIEW command denied to user 'xxxx_slave'@ '192.168.3.17' for table 'user' (1142)
Could not get database dump from master: 512 at /usr/local/bin/vicibox-install line 292, <STDIN> line 18.

[dhijrwn]

What method did you use to build the "slave server"?

If you did not use the vicibox .iso installer, please post a link to the instruction set you used.

If you DID use the vicibox .iso installer, please post the script questions/answers you used during the install (hopefully you captured these before the final "yes" and placed it in a text file somewhere?)

I used the vicibox-install script

--- ViciBox v.9.0 Install Summary ---

Expert : Yes
Legacy : No
Database : Yes
Slave ID : 2
Web : No
Telephony: No
Archive : No
Firewall : Disabled

--- Configuration Information ---
- Database -
SVN Rev : 3595
IP Addr : 192.168.3.3
Name : asterisk
User : xxxx_cron
Password : xxxx_cronpass
Cust User: xxxx_custom
Cust Pass: xxxx_custompass
Slave Usr: xxxx_slave
Slave Pw : xxxx_slavepass
Port : 3306

also I got these errors
I just grant the user to have a show view command
mysqldump: Couldn't execute 'show create table `user`': SHOW VIEW command denied to user 'xxxx_slave'@ '192.168.3.17' for table 'user' (1142)
Could not get database dump from master: 512 at /usr/local/bin/vicibox-install line 292, <STDIN> line 18.

after vicibox-install was done
Beginning database load into slave, this may take a while...
The --upgrade-system-tables option was used, user tables won't be touched.
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.global_priv OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.transaction_registry OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views... Skipped
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1347 (HY000) at line 143: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 145: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 146: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 158: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 161: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 166: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 202: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 203: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 211: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 212: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 222: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 225: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 227: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 231: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 233: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 332: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 337: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 338: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 343: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 344: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 345: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 347: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 352: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 353: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 358: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 359: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 364: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 367: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 368: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 370: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 371: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 372: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 373: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 484: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 485: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 486: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 487: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 552: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 553: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 554: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 559: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1449 (HY000) at line 561: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 562: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 563: 'mysql.user' is not BASE TABLE
ERROR 1449 (HY000) at line 564: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 565: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 567: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 569: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 570: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 571: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 572: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 573: 'mysql.user' is not BASE TABLE
ERROR 1347 (HY000) at line 574: 'mysql.user' is not BASE TABLE
ERROR 1805 (HY000) at line 579: Column count of mysql.proc is wrong. Expected 20, found 21. The table is probably corrupted
ERROR 1805 (HY000) at line 581: Column count of mysql.proc is wrong. Expected 20, found 21. The table is probably corrupted
ERROR 1805 (HY000) at line 589: Column count of mysql.proc is wrong. Expected 20, found 21. The table is probably corrupted
ERROR 1805 (HY000) at line 591: Column count of mysql.proc is wrong. Expected 20, found 21. The table is probably corrupted
ERROR 1449 (HY000) at line 592: The user specified as a definer ('mariadb.sys'@'localhost') does not exist
ERROR 1347 (HY000) at line 616: 'mysql.user' is not BASE TABLE
FATAL ERROR: Upgrade failed
Slave loaded and started. Verify by connecting to MySQL and running 'slave show status'

[williamconley]

Is the new server using an identical version of mysql as the prior server?

Note that (in theory) you should be able to use a more recent version of mysql in the replication server, but under no circumstances can you install a replication slave with an older version of mysql.

IMHO: If you attempt the install with a non-identical version of mysql, that could be your problem. Even if it is newer.

We don't ordinarily use the vicibox replication install, we just install another Dialer/Web server and then alter its purpose after the buildout and perform the replication ourselves (along with our own custom replictation status monitor). So I can't really be of help with the vicidial-stock-installer version beyond that. The benefit of this method is that you then have a spare dialer/web server and (assuming you used an identical installer as the DB server) your mysql version should be identical, you just have to wake up mysql and manually replicate.

[dhijrwn]

Is the new server using an identical version of mysql as the prior server?

Note that (in theory) you should be able to use a more recent version of mysql in the replication server, but under no circumstances can you install a replication slave with an older version of mysql.

IMHO: If you attempt the install with a non-identical version of mysql, that could be your problem. Even if it is newer.

We don't ordinarily use the vicibox replication install, we just install another Dialer/Web server and then alter its purpose after the buildout and perform the replication ourselves (along with our own custom replictation status monitor). So I can't really be of help with the vicidial-stock-installer version beyond that. The benefit of this method is that you then have a spare dialer/web server and (assuming you used an identical installer as the DB server) your mysql version should be identical, you just have to wake up mysql and manually replicate.

The master database version: mysql Ver 15.1 Distrib 10.4.17-MariaDB, for Linux (x86_64) using EditLine wrapper
The slave database version: mysql Ver 15.1 Distrib 10.2.32-MariaDB, for Linux (x86_64) using EditLine wrapper

I guess i'll install a standalone webserver+dbserver or express using vicibox-install and then do a manual replication master and slave setup and make sure the mysql version are identical.

[williamconley]

The master database version: mysql Ver 15.1 Distrib 10.4.17-MariaDB, for Linux (x86_64) using EditLine wrapper
The slave database version: mysql Ver 15.1 Distrib 10.2.32-MariaDB, for Linux (x86_64) using EditLine wrapper

I guess i'll install a standalone webserver+dbserver or express using vicibox-install and then do a manual replication master and slave setup and make sure the mysql version are identical.

You could try to upgrade the mysql version in the slave, that could work ...

Or use the same installer for the slave that you did for the DB. Not sure why you'd be using an older one in the first place. But I've seen it happen for various reasons.

[dhijrwn]

The master database version: mysql Ver 15.1 Distrib 10.4.17-MariaDB, for Linux (x86_64) using EditLine wrapper
The slave database version: mysql Ver 15.1 Distrib 10.2.32-MariaDB, for Linux (x86_64) using EditLine wrapper

I guess i'll install a standalone webserver+dbserver or express using vicibox-install and then do a manual replication master and slave setup and make sure the mysql version are identical.

You could try to upgrade the mysql version in the slave, that could work ...

Or use the same installer for the slave that you did for the DB. Not sure why you'd be using an older one in the first place. But I've seen it happen for various reasons.

Hi william,

What I did is I re-installed again my slave database server and upgrade it from 15.1 to 15.3 and the slave mysql server version upgraded to mysql Ver 15.1 Distrib 10.5.8-MariaDB, for Linux (x86_64) using EditLine wrapper, then I ran vicibox-installed with web Y and database Y and slave Y and waited for the result.

And viola the result was success, I didn't encounter any error, after I rebooted the mysql is running properly.

I set the slave server on the system settings and I can see on the inspect html <!-- Using slave server 192.168.3.17 S --> on every report and I'm getting data.

Thank you very much!

[williamconley]

Excellent postback.

Good job posting the original details, too.

[rustynail]

Hi,

I am facing same issue while installing mysql slave server on vicibox-11.0.1 installer. I have checed mysql version is same on both master and slave servers..

10.6.15-MariaDB-log (Master)
10.6.15-MariaDB-log (Slave)

But still my install fails when doing mysqldump.. below is the error I am facing... Is this a native bug and any way to fix it so it should work out of the box?

--- ViciBox Install Summary ---

Expert : Yes
Legacy : No
Database : Yes
Slave ID : 2
Web : Yes
Redirect : Yes
Telephony: Yes
Archive : No
Firewall : Disabled

--- Configuration Information ---
- Database -
SVN Rev : 3764
IP Addr : XXX.XXX.XXX.XXX
Name : asterisk
User : cron
Password : 1234
Cust User: custom
Cust Pass: custom1234
Slave Usr: slave
Slave Pw : slave1234
Port : 3306


Please verify the above information before continuing!
Do you want to continue the installation? [y/N] : y


Beginning installation, expect lots of output...

Disabling firewall...
Local SVN revision matches DB revision: 3764
Doing general DataBase requirements...
Doing Slave-specific MySQL setup...

Beginning database dump from master, this may take a while...

Info: Using unique option prefix 'all-database' is error-prone and can break in the future. Please use the full name 'all-databases' instead.
mysqldump: Couldn't execute 'show create table `user`': SHOW VIEW command denied to user 'slave'@'YYY.YYY.YYY.YYY' for table `mysql`.`user` (1142)
Could not get database dump from master: 512 at /usr/local/bin/vicibox-install line 280, <STDIN> line 14.



It's first time I am installing vici-11 and facing this issue... Please guide so this issue can be fixed instead of manually doing something on the servers as this will be used in production environment.

Thanks.