Solved mysql reading problem
#16
Thanks for the tip MilhouseVH,

I have used a much smaller folder on later tests.

Unfortunatly my linux version of XBMC doesn't boot anymore so Now I'm trying to install XMBCBuntu on an old laptop. That also seems to take it's time...Sleepy

Here is a debug log from last night again made by the Raspberry Pi

What I noticed is when I select movies to be shown MySQL says it get's a lot of queries done but no movielist is shown.
Reply
#17
(2014-02-19, 13:27)Edwinus Wrote: Here is a debug log from last night again made by the Raspberry Pi

Code:
09:44:50 T:2871174240   ERROR: SQL: Insertion failed because database is full
                                            Query: update repo set lastcheck='2014-02-19 09:44:50' where addonID='repository.openelec.tv'
09:44:50 T:2871174240   ERROR: SetRepoTimestamp failed on repo 'repository.openelec.tv'
...
09:44:51 T:2871174240   ERROR: SQL: Insertion failed because database is full
                                            Query: UPDATE texture SET lasthashcheck='2014-02-17 09:44:51' WHERE url='http://mirrors.xbmc.org/addons/frodo/script.artistslideshow/icon.png'
09:44:51 T:2871174240   ERROR: ExecuteQuery - failed to execute query 'UPDATE texture SET lasthashcheck='2014-02-17 09:44:51' WHERE url='http://mirrors.xbmc.org/addons/frodo/script.artistslideshow/icon.png''
09:44:51 T:2871174240   ERROR: SQL: Insertion failed because database is full
                                            Query: UPDATE texture SET lasthashcheck='2014-02-17 09:44:51' WHERE url='http://mirrors.xbmc.org/addons/frodo/script.speedfaninfo/icon.png'
09:44:51 T:2871174240   ERROR: ExecuteQuery - failed to execute query 'UPDATE texture SET lasthashcheck='2014-02-17 09:44:51' WHERE url='http://mirrors.xbmc.org/addons/frodo/script.speedfaninfo/icon.png''

Is the partition with your .xbmc/userdata folder now full? This will mean that none of the local SQLite databases can be written to, or for any thumbnails to be cached.

Also:
Code:
11:12:56 T:2862064736   ERROR: SQL: The table does not exist
                                            Query: select * from movieview  ORDER BY dateAdded desc, idMovie desc LIMIT 10
11:12:56 T:2862064736   ERROR: GetMoviesByWhere failed
11:12:56 T:2862064736   ERROR: SQL: The table does not exist
                                            Query: select * from episodeview  ORDER BY dateAdded desc, idEpisode desc LIMIT 10
11:12:56 T:2862064736   ERROR: GetEpisodesByWhere failed
11:12:56 T:2862064736   ERROR: SQL: The table does not exist
                                            Query: select * from musicvideoview  ORDER BY dateAdded desc, idMVideo desc LIMIT 10
11:12:56 T:2862064736   ERROR: GetMusicVideosByWhere failed
11:12:56 T:2862064736   ERROR: SQL: The table does not exist
                                            Query: SELECT count(1) FROM songview LIMIT 1
which suggests your MySQL databases haven't been created correctly. Drop your MySQL databases and restart XBMC so that it can create them properly (then upload the debug log when it creates the databases). If XBMC fails to create the databases correctly you've probably not set the permissions on the user accounts correctly.
Texture Cache Maintenance Utility: Preload your texture cache for optimal UI performance. Remotely manage media libraries. Purge unused artwork to free up space. Find missing media. Configurable QA check to highlight metadata issues. Aid in diagnosis of library and cache related problems.
Reply
#18
Ok, The disk full I think was from the RPi. Since MySQL is used I dont think it should matter but I have done a clean installation on it again and added the advancedsettings.xml

Quote:<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>192.168.1.16</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</videodatabase>

<musicdatabase>
<type>mysql</type>
<host>192.168.1.16</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</musicdatabase>

<videolibrary>
<importwatchedstate>true</importwatchedstate>
</videolibrary>
</advancedsettings>

Removed the old databases from MySQL and rebooted the RPi

This is a large debug log so I hope it tell's you more than the ones before.
Reply
#19
You can make the log a lot easierto follow by grepping out all the CECLogMessage spam/crap.

As for the log, this doesn't look good:
Code:
16:26:47 T:3046793216    INFO: create albuminfo trigger
16:26:47 T:3046793216   DEBUG: Mysql execute: CREATE TRIGGER tgrAlbumInfo AFTER delete ON albuminfo FOR EACH ROW BEGIN delete from albuminfosong where albuminfosong.idAlbumInfo=old.idAlbumInfo; END
16:26:47 T:3046793216   ERROR: SQL: Undefined MySQL error: Code (1419)
                                            Query: CREATE TRIGGER tgrAlbumInfo AFTER delete ON albuminfo FOR EACH ROW BEGIN delete from albuminfosong where albuminfosong.idAlbumInfo=old.idAlbumInfo; END
16:26:47 T:3046793216   ERROR: CreateTables unable to create tables:0
16:26:47 T:3046793216   DEBUG: Mysql rollback transaction
...
16:26:50 T:3046793216    INFO: create deletion triggers
16:26:50 T:3046793216   DEBUG: Mysql execute: CREATE TRIGGER delete_movie AFTER DELETE ON movie FOR EACH ROW BEGIN DELETE FROM art WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM taglinks WHERE idMedia=old.idMovie AND media_type='movie'; END
16:26:50 T:3046793216   ERROR: SQL: Undefined MySQL error: Code (1419)
                                            Query: CREATE TRIGGER delete_movie AFTER DELETE ON movie FOR EACH ROW BEGIN DELETE FROM art WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM taglinks WHERE idMedia=old.idMovie AND media_type='movie'; END
16:26:50 T:3046793216   ERROR: CreateTables unable to create tables:0
16:26:50 T:3046793216   DEBUG: Mysql rollback transaction

Error 1419 is:
Quote:Error: 1419 SQLSTATE: HY000 (ER_BINLOG_CREATE_ROUTINE_NEED_SUPER)

Message: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

I'm not entirely sure why you're getting this error, but check that you have set (granted) the permissions correctly on the xbmc user in the database.

You also don't have any views, but it's not apparent why they have not been created:
Code:
16:30:53 T:2841637984   ERROR: SQL: The table does not exist
                                            Query: select * from movieview  ORDER BY dateAdded desc, idMovie desc LIMIT 10
16:30:53 T:2841637984   ERROR: GetMoviesByWhere failed
16:30:53 T:2841637984   ERROR: SQL: The table does not exist
                                            Query: select * from episodeview  ORDER BY dateAdded desc, idEpisode desc LIMIT 10
16:30:53 T:2841637984   ERROR: GetEpisodesByWhere failed
16:30:53 T:2841637984   ERROR: SQL: The table does not exist
                                            Query: select * from musicvideoview  ORDER BY dateAdded desc, idMVideo desc LIMIT 10
16:30:53 T:2841637984   ERROR: GetMusicVideosByWhere failed

My advice would be to:

1) Check your user permissions again
2) Consider using a more recent version of MySQL, eg. v5.5 (but not v5.6 as you are not using a recent Gotham nightly). Is there any specific reason you are using this really quite old v5.1.36?
Texture Cache Maintenance Utility: Preload your texture cache for optimal UI performance. Remotely manage media libraries. Purge unused artwork to free up space. Find missing media. Configurable QA check to highlight metadata issues. Aid in diagnosis of library and cache related problems.
Reply
#20
The reason for this older version of mysql is because it's installed by default on my QNAP NAS and they won't upgrade it. I'd like it to run on the QNAP because I want to keep it all as low-power-consumptioning as possible. Thats also the reason why I choose the RPi's.

The rights on the xbmc user in mysql are:
Image

As mentioned before, I figured the right column is not for the xmbc user.
Reply
#21
Error 1419 mentions a need for SUPER privilege, try enabling that then recreate the databases. I don't really know anything about QNAP (I run MySQL v5.5 on FreeNAS) so can't suggest much else - I've got no GUI and just followed one of the guides ages ago...
Texture Cache Maintenance Utility: Preload your texture cache for optimal UI performance. Remotely manage media libraries. Purge unused artwork to free up space. Find missing media. Configurable QA check to highlight metadata issues. Aid in diagnosis of library and cache related problems.
Reply
#22
I have changed the IMG in my previous post.

I was under the assumption that the setting at 2 had to be the grand option. Today with a fresh look I saw also a grant option at 1 and that was the one I had to select. At this moment it's adding the movies to the MySQL server. Big Grin

Thank you all for your tips. I was looking for how to add reputation but thats not that easy as well :p
Reply
#23
Probably shouldn't resurrect an old post like this one, but it's the only one Google turns up.

I finally upgraded to 13.2, and got the dreaded:

Code:
ERROR: SQL: Undefined MySQL error: Code (1419)

There's a really easy way to fix this without granting SUPER privileges. I read into the SUPER privilege and it allows for things like killing mysql threads to accounts other than your own, as well as modifying global settings in mysql. This really shouldn't be enabled on a user account, especially since I also use mysql for mythtv and I don't want them to be able to trash each other's databases.

I'm using mariadb, I tried going to mysql and it made no difference (despite what the bugtracker says.)

That error is telling you binary logging is enabled on the server. It is generally used for replication, so on most XBMC installs you don't need it.

Anyway, a quicker way is to check the status. In the mysql client check this parameter (you'll need to be root most likely):
Code:
mysql> show global variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

As you can see it's set to off.

BEFORE running xbmc, go to the mysql backend and set it with the client:

Code:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

Then check it:
Code:
mysql> show global variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.00 sec)

Make sure the incomplete tables are dropped, then run XBMC and let it do its upgrade. When it's done, set it back the way it was:

Code:
mysql> SET GLOBAL log_bin_trust_function_creators = 0;
Query OK, 0 rows affected (0.00 sec)

Hopefully this will help someone else.
Reply
#24
You are a genius!

This is still an issue when dealing with Jarvis and MariaDB 10.0.14 at least.

I've set "SET GLOBAL log_bin_trust_function_creators = 1;", dropped the partially created databases, re-ran Kodi Jarvis and everything went through without a hitch.

Then I ran "SET GLOBAL log_bin_trust_function_creators = 0;" again.

Kudos!
Reply
#25
Sorry for bumping this new thread. The log_bin_trust_function_creators switch solved my problem that I'm having with using Kodi with my QNAP. My question is, do I have to set it back to 0 after enabling it? From what I understand, toggling it on enables executing of some potentially dangerous function, which is required for modifying the database. But if I'm going to be adding and deleting to my library, doesn't this mean every time there's a change I need to toggle it on back again?
Reply
#26
You only need to enable it for schema changes (i.e. upgrading Kodi to a new major version.)

Adding and removing content to the database is not affected.
Reply
#27
(2015-03-18, 03:57)danomac Wrote: Probably shouldn't resurrect an old post like this one, but it's the only one Google turns up.

I finally upgraded to 13.2, and got the dreaded:
Code:
ERROR: SQL: Undefined MySQL error: Code (1419)

There's a really easy way to fix this without granting SUPER privileges. I read into the SUPER privilege and it allows for things like killing mysql threads to accounts other than your own, as well as modifying global settings in mysql. This really shouldn't be enabled on a user account, especially since I also use mysql for mythtv and I don't want them to be able to trash each other's databases.

I'm using mariadb, I tried going to mysql and it made no difference (despite what the bugtracker says.)

That error is telling you binary logging is enabled on the server. It is generally used for replication, so on most XBMC installs you don't need it.

Anyway, a quicker way is to check the status. In the mysql client check this parameter (you'll need to be root most likely):
Code:
mysql> show global variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)

As you can see it's set to off.

BEFORE running xbmc, go to the mysql backend and set it with the client:
Code:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

Then check it:
Code:
mysql> show global variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)

Make sure the incomplete tables are dropped, then run XBMC and let it do its upgrade. When it's done, set it back the way it was:
Code:
mysql> SET GLOBAL log_bin_trust_function_creators = 0;
Query OK, 0 rows affected (0.00 sec)

Hopefully this will help someone else. 
THANK YOU SO MUCH. Putzed with this for about 6 hours before finally stumbling across this and getting SPMC and Kodi to work FINALLY.
Reply
#28
Thank you, this post fixed my problem. My symptoms were similar: Kodi appeared to scrap the data correctly, but when I tried to enter a lsting of seasons of any of my TVshow, kodi would try to query the database and error out. However, I could play the show by selecting the episode from the main TV Episodes screen in the skin or select it from the Videos menu. However, selecting the TV Episodes heading would show the list of TV series, but would not allow me to dig any deeper. Searching the errors I saw in the log led me to the post. I discovered my db user did not have grant permissions. After enabling that and rebuilding the database, it works fine. I had recentlly recreated the db user and missed that permission this time. I dont think it is relevant, but My Buffalo LinkStation Duo NAS has mySQL 5.0.70 and also will not let me upgrade.
Reply
#29
I was too hasty last night and I have not had any improvements to my situation. A couple weeks ago, I setup an instance at my parent's house that shares my media library. It probably isnt necessary, but I deleted the existing db user and created new separate users for each machine, (but then didn't use it on the new machine because watched statuses don't behave as I had hoped). Now that I am home any using my own PC, I notice the database gives the weird behavior described above. I have tried creating more accounts, and deleting and recreating existing accounts. What should I try next? Log fie
Reply
#30
(2020-01-17, 04:10)killbilly Wrote: I deleted the existing db user and created new separate users for each machine

This is just plain wrong!  Every machine must be set (via advancedsettings.xml) to use the same user and password to access the database.  Otherwise, it's not shared, is it ?
Learning Linux the hard way !!
Reply

Logout Mark Read Team Forum Stats Members Help
mysql reading problem0