Database access broken after Ubuntu upgrade

For discussion of topics specific to MythTV on linux
Post Reply
Murtagh
Newcomer
Posts: 6
Joined: Sat Jan 26, 2019 4:05 am
United States of America

Database access broken after Ubuntu upgrade

Post by Murtagh » Tue Oct 06, 2020 5:15 pm

Summary: I performed the upgrade from Ubuntu to 20.04 to 20.04.1 on a working Myth box, and I am now having an issue with the database. When the system starts, I have normal operations for 15 minutes. For that 15 minutes, the client is good, UPNP access is good, and MythWeb is good. However, after that 15 minutes, access to the database collapses. The client will not start, UPNP access fails, and MythWeb runs, but does not have any database data present.

Doing my troubleshooting, I noticed the 15 minute timeout where everything works is when the MySQL service is attempting to start. The service never starts, but is listed as attempting to start for that 15 minutes. If when the system is broken, I issue a "sudo systemctl start mysql", access restarts for 15 minutes.

Side note: I note that MariaDB is installed. I do not recall purposely making the MariaDB/MySQL substitution, or know if it matters. Ubuntu's dmsg seems to treat them as interchangeable. I will just say MySQL in this ticket for simplicity.

Based on the fact the system partially works, I do not think the issue is the common database password issue. In fact, my bet is on mysql being broken over anything truly Myth based. But I am hoping the expertise is here to help me figure out the easy way through this.

Reproducing bug:
Setup: Working MythTV 31/Fixes Front/Backend on Ubuntu 20.04. System is fully functional.

One time operation
1) From Linux Command Line: do-release-upgrade
- This upgrades Ubnuntu OS to 20.04.1

Bug:
1) Power on Ubuntu system.
2) Wait for system to reach desktop.
3) Test MythTV Client. It boots normally.
4) Test UPNP access. Access is good, and will play an hour long recording.
5) Test MythWeb. Access is normal with correct previous recordings etc.
6) Check MySQL Service status:

Code: Select all

$ sudo systemctl status mysql
● mariadb.service - MariaDB 10.3.22 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor prese>
     Active: activating (start) since Mon 2020-10-05 22:06:56 PDT; 24s ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 3282 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var>
    Process: 3292 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_ST>
    Process: 3303 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && >
   Main PID: 3359 (mysqld)
      Tasks: 32 (limit: 19025)
     Memory: 70.5M
     CGroup: /system.slice/mariadb.service
             └─3359 /usr/sbin/mysqld

Oct 05 22:06:56 Bahumut systemd[1]: Starting MariaDB 10.3.22 database server...
Oct 05 22:06:56 Bahumut mysqld[3359]: 2020-10-05 22:06:56 0 [Note] /usr/sbin/my>

User@machine$ 
7) Wait 15 minutes
8) Check MySQL Service Status

Code: Select all

$ sudo systemctl status mysql
● mariadb.service - MariaDB 10.3.22 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor prese>
     Active: failed (Result: timeout) since Tue 2020-10-06 10:00:36 PDT; 42s ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 725 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/>
    Process: 747 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_STA>
    Process: 749 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && V>
    Process: 853 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_>
   Main PID: 853 (code=exited, status=0/SUCCESS)

Oct 06 09:45:30 Bahumut systemd[1]: Starting MariaDB 10.3.22 database server...
Oct 06 09:45:39 Bahumut mysqld[853]: 2020-10-06  9:45:39 0 [Note] /usr/sbin/mys>
Oct 06 10:00:34 Bahumut systemd[1]: mariadb.service: start operation timed out.>
Oct 06 10:00:36 Bahumut systemd[1]: mariadb.service: Failed with result 'timeou>
Oct 06 10:00:36 Bahumut systemd[1]: Failed to start MariaDB 10.3.22 database se>
lines 1-16/16 (END)
9) Test MythTV Client. It fails to launch, saying database cannot be reached
10) Test UPNP Access. Access Fails.
11) Test MythWeb. Mythweb framework is functional, but no database data.
12) Logs:

Code: Select all

$ cat /var/log/mysql/error.log
2020-10-06  9:45:41 0 [Note] InnoDB: Using Linux native AIO
2020-10-06  9:45:41 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-10-06  9:45:41 0 [Note] InnoDB: Uses event mutexes
2020-10-06  9:45:41 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-10-06  9:45:41 0 [Note] InnoDB: Number of pools: 1
2020-10-06  9:45:41 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-10-06  9:45:41 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-10-06  9:45:41 0 [Note] InnoDB: Completed initialization of buffer pool
2020-10-06  9:45:41 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-10-06  9:45:42 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-10-06  9:45:42 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-10-06  9:45:42 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-10-06  9:45:42 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-10-06  9:45:42 0 [Note] InnoDB: 10.3.22 started; log sequence number 179997650; transaction id 29709
2020-10-06  9:45:42 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2020-10-06  9:45:42 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-10-06  9:45:43 0 [ERROR] mysqld: Can't open shared library '/usr/lib/x86_64-linux-gnu/mariadb19/plugin/auth_socket.so' (errno: 2, cannot open shared object file: No such file or directory)
2020-10-06  9:45:43 0 [Note] Server socket created on IP: '127.0.0.1'.
2020-10-06  9:45:44 0 [Note] InnoDB: Buffer pool(s) load completed at 201006  9:45:44
2020-10-06  9:45:44 0 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_B
2020-10-06  9:45:44 0 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2020-10-06  9:45:44 0 [Note] Reading of all Master_info entries succeeded
2020-10-06  9:45:44 0 [Note] Added new Master_info '' to hash table
2020-10-06  9:45:44 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.22-MariaDB-1ubuntu1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 20.04
2020-10-06  9:51:06 11 [ERROR] mysqld: Table './mythconverg/pidcache' is marked as crashed and should be repaired
2020-10-06  9:51:06 11 [Warning] Checking table:   './mythconverg/pidcache'
2020-10-06 10:00:34 0 [Note] /usr/sbin/mysqld (initiated by: unknown): Normal shutdown
2020-10-06 10:00:34 0 [Note] InnoDB: FTS optimize thread exiting.
2020-10-06 10:00:34 0 [Note] InnoDB: Starting shutdown...
2020-10-06 10:00:34 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2020-10-06 10:00:34 0 [Note] InnoDB: Buffer pool(s) dump completed at 201006 10:00:34
2020-10-06 10:00:36 0 [Note] InnoDB: Shutdown completed; log sequence number 179997688; transaction id 29726
2020-10-06 10:00:36 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-10-06 10:00:36 0 [Note] /usr/sbin/mysqld: Shutdown complete

brent@Bahumut:~$ 

Code: Select all

$ tail /var/log/mythtv/mythbackend.log
Oct  6 10:07:09 Bahumut mythbackend: mythbackend[1784]: I JobQueue mythdbcon.cpp:631 (exec) MySQL server disconnected
Oct  6 10:07:09 Bahumut mythbackend: mythbackend[1784]: E JobQueue mythdb.cpp:181 (DBError) DB Error (Error in JobQueue::GetJobs(), Unable to query list of Jobs in Queue.):#012Query was:#012#012No error type from QSqlError?  Strange...
Oct  6 10:07:10 Bahumut mythbackend: mythbackend[1784]: E HttpServer9 mythdbcon.cpp:844 (prepare) Error preparing query: SELECT chanid, starttime FROM recorded WHERE recordedid = :RECORDEDID
Oct  6 10:07:10 Bahumut mythbackend: mythbackend[1784]: E HttpServer9 mythdbcon.cpp:846 (prepare) Driver error was [2/2006]:#012QMYSQL3: Unable to prepare statement#012Database error was:#012MySQL server has gone away
Oct  6 10:07:10 Bahumut mythbackend: mythbackend[1784]: I HttpServer9 mythdbcon.cpp:631 (exec) MySQL server disconnected
Oct  6 10:07:10 Bahumut mythbackend: mythbackend[1784]: C HttpServer9 programinfo.cpp:259 (ProgramInfo) ProgramInfo(): Failed to find recorded entry for 1771.
Oct  6 10:07:14 Bahumut mythbackend: mythbackend[1784]: E Expire mythdbcon.cpp:235 (OpenDatabase) [DBManager10] Unable to connect to database!
Oct  6 10:07:14 Bahumut mythbackend: mythbackend[1784]: E Expire mythdbcon.cpp:236 (OpenDatabase) Driver error was [1/2002]:#012QMYSQL: Unable to connect#012Database error was:#012Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
Oct  6 10:07:14 Bahumut mythbackend: mythbackend[1784]: I Expire mythdbcon.cpp:820 (prepare) MySQL server disconnected
Oct  6 10:07:14 Bahumut mythbackend: mythbackend[1784]: I Expire mythdbcon.cpp:631 (exec) MySQL server disconnected
brent@Bahumut:~$ 

Code: Select all

$ mythutil --version
Please attach all output as a file in bug reports.
MythTV Version : v31.0+fixes.202009212108.8ca7747648~ubuntu18.04.1
MythTV Branch : fixes/31
Network Protocol : 91
Library API : 31.20200101-1
QT Version : 5.9.5
Options compiled in:
 linux profile use_hidesyms using_alsa using_oss using_pulse using_pulseoutput using_backend using_bindings_perl using_bindings_python using_bindings_php using_dvb using_firewire using_frontend using_hdhomerun using_vbox using_ceton using_hdpvr using_ivtv using_joystick_menu using_libcec using_libcrypto using_gnutls using_libdns_sd using_libfftw3 using_libxml2 using_lirc using_mheg using_opengl using_egl using_qtwebkit using_qtscript using_qtdbus using_taglib using_v4l2 using_v4l2prime using_x11 using_libbluray_external using_xrandr using_profiletype using_systemd_notify using_systemd_journal using_drm using_bindings_perl using_bindings_python using_bindings_php using_freetype2 using_mythtranscode using_opengl using_egl using_drm using_vaapi using_nvdec using_vdpau using_ffmpeg_threads using_mheg using_libass using_libxml2 using_libmp3lame
brent@Bahumut:~$ 

yawlhoo
Junior
Posts: 95
Joined: Wed Apr 15, 2015 4:18 pm
United States of America

Re: Database access broken after Ubuntu upgrade

Post by yawlhoo » Tue Oct 06, 2020 9:42 pm

I don't know how you wound up with mariadb; my system, a 20.04 Ubuntu backend that is working well, is running mysql, maybe this is the source of your problem?

Here are a few sample outputs for reference:
=====
$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Sat 2020-09-26 08:18:47 PDT; 1 weeks 3 days ago
Main PID: 1128 (mysqld)
Status: "Server is operational"
Tasks: 51 (limit: 8988)
Memory: 379.0M
CGroup: /system.slice/mysql.service
└─1128 /usr/sbin/mysqld

Sep 26 08:18:40 nuc2 systemd[1]: Starting MySQL Community Server...
Sep 26 08:18:47 nuc2 systemd[1]: Started MySQL Community Server.
----
$ mythutil --version
Please attach all output as a file in bug reports.
MythTV Version : v31.0+fixes.202009212108.8ca7747648~ubuntu20.04.1
MythTV Branch : fixes/31
Network Protocol : 91
Library API : 31.20200101-1
QT Version : 5.12.8
Options compiled in:
linux profile use_hidesyms using_alsa using_oss using_pulse using_pulseoutput using_backend using_bindings_perl using_bindings_python using_bindings_php using_dvb using_firewire using_frontend using_hdhomerun using_vbox using_ceton using_hdpvr using_ivtv using_joystick_menu using_libcec using_libcrypto using_gnutls using_libdns_sd using_libfftw3 using_libxml2 using_lirc using_mheg using_opengl using_egl using_qtwebkit using_qtscript using_qtdbus using_taglib using_v4l2 using_x11 using_libbluray_external using_xrandr using_profiletype using_systemd_notify using_systemd_journal using_bindings_perl using_bindings_python using_bindings_php using_freetype2 using_mythtranscode using_opengl using_egl using_vaapi using_nvdec using_vdpau using_ffmpeg_threads using_mheg using_libass using_libxml2 using_libmp3lame
====
Hope this helps.

jksj
Senior
Posts: 124
Joined: Thu Feb 13, 2014 7:53 pm
Great Britain

Re: Database access broken after Ubuntu upgrade

Post by jksj » Wed Oct 07, 2020 7:51 am

The following in the MariaDb log looks ominous. Have you tried repairing the database
Version: '10.3.22-MariaDB-1ubuntu1' socket: '/run/mysqld/mysqld.sock' port: 3306 Ubuntu 20.04
2020-10-06 9:51:06 11 [ERROR] mysqld: Table './mythconverg/pidcache' is marked as crashed and should be repaired
2020-10-06 9:51:06 11 [Warning] Checking table: './mythconverg/pidcache'
2020-10-06 10:00:34 0 [Note] /usr/sbin/mysqld (initiated by: unknown): Normal shutdown

Murtagh
Newcomer
Posts: 6
Joined: Sat Jan 26, 2019 4:05 am
United States of America

Re: Database access broken after Ubuntu upgrade

Post by Murtagh » Wed Oct 07, 2020 6:28 pm

@jksj - What is the best way to repair the database? Inside the database or inside Myth?

User avatar
bill6502
Developer
Posts: 1895
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: Database access broken after Ubuntu upgrade

Post by bill6502 » Wed Oct 07, 2020 8:52 pm

You can do it through the Services API

Code: Select all

curl --data Repair=True localhost:6544/Myth/CheckDatabase
and from any other host replacing localhost with the hostname or IP of the backend.

Results are in the backend log.

Post Reply