[SOLVED] Fresh install on RPi 4: QMYSQL3: Unable to prepare statement

[zetoune]

Hi there,

I have some issues with a brand new fresh install on a RPi 4 trying to connect to an existing MythTV backend.
When I can enter the MythTV Frontend UI, I cannot change any settings. (e.g. Trying to change the theme, keeps the default one all the time). But most of the time, I'm stuck at the first screen saying "Cannot connect to Master".
It seems to be an error with MySQL. Logs reports this error all the time.

Code: Select all

QMYSQL3: Unable to prepare statement
Database error was:
Unknown MySQL error

From the RPi 4, I can successfully connect to master MythTV DB with mysql-client (actually mariadb-client with Buster).
Nothing has been installed except MythTV Light. I used "Raspbian with desktop only" image.

Any ideas?

[solution]:
Buster uses mariadb and my primary backend used MySQL Server 5.7. I have replaced MySQL Server with MariaDB Server to solve this.
Also I needed to setup timezone
https://www.mythtv.org/wiki/Troubleshoo ... Zone_error

[jfabernathy]

I just tested something close but didn't get that error.

I have a working RP4 4GB with Raspbian Buster lite plus the XFCE4 desktop installed. I also have Mythtv light installed. However, I also have mythtv-backend installed. So normally my RP4 mythfrontend displays recordings from my localhost backend.

To see what it would do as a remote FE, I went into setup on the RP4 mythfrontend and changed the database setup to point to my legacy backend on a PC in the closet. I also changes the password to match the remote backend.

Mythfrontend connected just fine and played videos. Changed it back the the localhost backend and that still worked as well. I exited the mythfrontend each time I changed the database configuration.

One thought is my backend is configured to support remote FE's running mythfrontend. That is not the default.

Make sure your backend has a file /etc/mysql/mariadb.conf/mythtv.cnf. Mines is:

Code: Select all

[mysqld]
bind-address=::
max_connections=100
table_open_cache=128

The important part is bind-address=::
Make sure it is not commented out.

Jim A

[zetoune]

Hi Jim,

My MythTV backend is using MySQL Server 5.7, not MariaDB. (It's on Ubuntu 18.04).
An old backend I have since Mythtv 0.21 (now upgraded to 0.30 obviously)
The other frontend does connect to it without any issues.

I did change the settings according to your recommandations with no luck

I'm still facing this issue.
This is what I get when I start it from the command line:

Code: Select all

$> mythfrontend -O libCECEnabled=0 -O PowerOffTVOnExit=0
qt5ct: using qt5ct plugin
2019-12-14 11:48:24.252893 I  Setup Interrupt handler
2019-12-14 11:48:24.252984 I  Setup Terminated handler
2019-12-14 11:48:24.253003 I  Setup Segmentation fault handler
2019-12-14 11:48:24.253028 I  Setup Aborted handler
2019-12-14 11:48:24.253045 I  Setup Bus error handler
2019-12-14 11:48:24.253070 I  Setup Floating point exception handler
2019-12-14 11:48:24.253087 I  Setup Illegal instruction handler
2019-12-14 11:48:24.253115 I  Setup Real-time signal 0 handler
2019-12-14 11:48:24.253138 I  Setup User defined signal 1 handler
2019-12-14 11:48:24.253162 I  Setup User defined signal 2 handler
2019-12-14 11:48:24.253178 I  Setup Hangup handler
2019-12-14 11:48:24.253566 C  mythfrontend version: fixes/30 [v30.0-60-gb9c1b41a6c]
2019-12-14 11:48:24.253588 C  Qt version: compile: 5.11.3, runtime: 5.11.3
2019-12-14 11:48:24.253604 N  Enabled verbose msgs:  general
2019-12-14 11:48:24.253656 N  Setting Log Level to LOG_INFO
2019-12-14 11:48:24.264554 N  Using runtime prefix = /usr
2019-12-14 11:48:24.264568 N  Using configuration directory = /home/pi/.mythtv
2019-12-14 11:48:24.264572 I  Added logging to the console
2019-12-14 11:48:24.264783 I  Assumed character encoding: en_US.UTF-8
2019-12-14 11:48:24.264921 N  Setting 'PowerOffTVOnExit' being forced to '0'
2019-12-14 11:48:24.264994 N  Setting 'libCECEnabled' being forced to '0'
2019-12-14 11:48:24.277185 I  Loading fr translation for module mythfrontend
2019-12-14 11:48:24.278853 I  Using a profile name of: 'RaspberryPi Frontend' (Usually the same as this host's name.)
2019-12-14 11:48:24.279098 I  Start up testing connections. DB 192.168.5.20, BE , attempt 0, status dbAwake, Delay: 2000
2019-12-14 11:48:24.921303 E  Error preparing query: SELECT data FROM settings WHERE value = :VALUE AND hostname = :HOSTNAME
2019-12-14 11:48:24.921334 E  Driver error was [2/2000]:
QMYSQL3: Unable to prepare statement
Database error was:
Unknown MySQL error

2019-12-14 11:48:24.922195 E  Error preparing query: SELECT data FROM settings WHERE value = :KEY AND hostname = :HOSTNAME
2019-12-14 11:48:24.922219 E  Driver error was [2/2000]:
QMYSQL3: Unable to prepare statement
Database error was:
Unknown MySQL error

2019-12-14 11:48:24.922546 E  Error preparing query: SELECT data FROM settings WHERE value = :KEY AND hostname IS NULL
2019-12-14 11:48:24.922568 E  Driver error was [2/0]:
QMYSQL3: Unable to prepare statement
Database error was:


2019-12-14 11:48:25.925086 I  Start up testing connections. DB 192.168.5.20, BE , attempt 1, status beAwake, Delay: 2000
2019-12-14 11:48:30.929685 I  Start up testing connections. DB 192.168.5.20, BE , attempt 2, status beAwake, Delay: 2000
2019-12-14 11:48:30.960164 I  Starting process manager
2019-12-14 11:48:30.960225 I  Starting process signal handler
2019-12-14 11:48:30.972339 I  Starting IO manager (read)
2019-12-14 11:48:30.972393 I  Starting IO manager (write)
2019-12-14 11:48:31.012308 I  ScreenSaverX11Private: DPMS is active.
2019-12-14 11:48:31.064121 N  Desktop video mode: 2560x1440 59.951 Hz
2019-12-14 11:48:31.184961 E  DB Error (StorageGroup::StorageGroup()):
Query was:
SELECT DISTINCT dirname FROM storagegroup WHERE groupname = 'Themes' AND hostname = 'RaspberryPi Frontend'
Bindings were:
:GROUP="Themes", :HOSTNAME="RaspberryPi Frontend"
Driver error was [2/0]:
QMYSQL: Unable to execute query
Database error was:

2019-12-14 11:48:31.212575 E  LIRC: Failed to connect to Unix socket '/var/run/lirc/lircd'
			eno: No such file or directory (2)
2019-12-14 11:48:31.212776 I  No joystick configuration found, not enabling joystick control
2019-12-14 11:48:31.212851 I  CECAdapter: libCEC support is disabled.
2019-12-14 11:48:31.213038 I  UDPListener: Enabling
2019-12-14 11:48:31.229907 I  Binding to UDP 0.0.0.0:0
2019-12-14 11:48:31.231311 I  Binding to UDP [::]:0
2019-12-14 11:48:31.253671 I  Using Frameless Window
2019-12-14 11:48:31.253692 I  Using Full Screen Window
2019-12-14 11:48:31.266154 I  UI Screen Resolution: 2560 x 1440
2019-12-14 11:48:31.377220 I  Using the Qt painter
2019-12-14 11:48:31.400787 I  MythUIHelper: Pruning cache directory: /home/pi/.mythtv/cache/remotecache
2019-12-14 11:48:31.401746 I  MythUIHelper: Kept 0 files, deleted 0 files, stat error on 0 files
2019-12-14 11:48:31.402818 I  MythUIHelper: Pruning cache directory: /home/pi/.mythtv/cache/thumbnails
2019-12-14 11:48:31.409378 I  MythUIHelper: Kept 24 files, deleted 0 files, stat error on 0 files
qt5ct: D-Bus system tray: no
2019-12-14 11:48:32.937327 I  MythUIWebBrowser: Loading css from - file:///usr/share/mythtv/themes/default/htmls/mythbrowser.css
2019-12-14 11:48:32.942868 I  MythUIWebBrowser: enabling plugins
2019-12-14 11:48:33.024146 E  SaveSettingOnHost('WebBrowserZoomLevel') - database not open
2019-12-14 11:48:38.440604 I  Start up testing connections. DB 192.168.5.20, BE , attempt 3, status beAwake, Delay: 2000
2019-12-14 11:48:43.501039 I  Start up testing connections. DB 192.168.5.20, BE , attempt 4, status beAwake, Delay: 2000
2019-12-14 11:48:48.568770 I  Start up testing connections. DB 192.168.5.20, BE , attempt 5, status beAwake, Delay: 2000
2019-12-14 11:48:53.632980 I  Start up testing connections. DB 192.168.5.20, BE , attempt 6, status beAwake, Delay: 2000
2019-12-14 11:48:58.779532 I  Start up testing connections. DB 192.168.5.20, BE , attempt 7, status beAwake, Delay: 2000
QEventLoop::exec: instance 0x1fe1798 has already called exec()
QEventLoop::exec: instance 0x1fe1798 has already called exec()
2019-12-14 11:49:02.597261 I  Start up failure. host 192.168.5.20, status beAwakeFail
2019-12-14 11:49:02.597458 E  Failed to init MythContext, exiting.
2019-12-14 11:49:02.706242 I  RAOP Device: Cleaning up.
2019-12-14 11:49:02.707003 I  AirPlay: Cleaning up.
2019-12-14 11:49:02.707094 I  Waiting for threads to exit

As I said, I can log into MySQL server from the RPi 4 with mysql client.

Code: Select all

 $ mysql -u mythtv -h 192.168.5.20 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.7.28-0ubuntu0.18.04.4 (Ubuntu)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> use mythconverg;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [mythconverg]> 

[jfabernathy]

I think that Ubuntu 18.04 came with mariadb, but you still use mysql commands to access it. Your log shows your are running it.

Not sure if you upgraded mythtv version in place or not. I've not done that so not sure what can get introduced.

I always backup my mthconverg database and my recordings to an external drive and then rebuild the O/S and mythtv. Much easier that way. Once everything is working in test I just restore the database and recordings.

[zetoune]

Actually no
Ubuntu 18.04 comes with whatever you install (MySQL 5.7 or MariaDB 10) but Raspbian Buster comes with MariaDB.
When I install default-mysql-client on Buster, it install mariadb-client.
I would be really surprised that it's incompatible with MySQL Server 5.7, but I'm gonna try with a fresh installed backend with MariaDB Server.
Just in case.

[zetoune]

That was it, so thank you a lot
I removed MySQL Server from the server and installed MariaDB instead.
I can now connect to the backend with the Pi. Something strange. The other frontend (and other backend) still connect to primary too with mysql-5.7 client.
I still have issue though. I cannot play any files.

Code: Select all

RemoteFile::openSocket(file data socket): Failed to open socket, error was malformed_ann_query

[zetoune]

According to what I read it's because of different protocol.
So it's another issue.
Once again, thank you for the help.