[_Andy_]

Hello

I downloaded the latest nightly build 6/1/2012 and did a fresh install, no update.
After scraping some movies i noticed there are no movies in the library.

The same with tv shows. I watched in the debug log and found xbmc is unable to find the views in the mysql database.
So all views are missing.

ERROR: SQL: The table does not exist
Query: select * from movieview ORDER BY dateAdded desc, idMovie desc LIMIT 10

Is this a known issue? How can i get the views?
I like to solve because i want to test the new AE.

[_Andy_]

I could solve this issue. I downloaded the source and looked into the mysql code. I found the section where the views are created.
I copied the code and add the views manualy.

Now it's working but i have no clue why the views are not created automaticly.

[jmarshall]

Get it reproducible. To my knowledge there is no issue. There will be a failure in the log (on first creation only) most likely.

[lumendes]

I got the exact same problem, with both 06/06 nightly and Eden versions.
First time I'm trying to set up a centralized mysql database for Xbmc, but followed all steps at official page.

Spotted the following from logs...

Code:

Query: SELECT count(1) FROM tvshowview LIMIT 1
23:24:25 T:5872   ERROR: CDatabase::GetSingleValue - failed on query 'SELECT count(1) FROM tvshowview LIMIT 1'
23:24:25 T:5872   ERROR: SQL: The table does not exist

Databases are created, but all views are missing:

Code:

mysql> show databases;
+------------+
| Database   |
+------------+
| MyMusic20  |
| MyVideos64 |
| mysql      |
| test       |
+------------+
4 rows in set (0.00 sec)

Some details of my setup;

- Client: Windows 7 64bit
- Server: Lacie (NAS) linux, mysql 4.XXX

- Logs & use case:
1) no databases
2) open xbmc
3) add media source (local hardrive)
4) use scrapper
5) attempt to open library or tv shows/movie views

logs

[jmarshall]

Thanks lumendes,

Interestingly, many are using mysql fine via upgrade, which adds the same triggers, yet here it is complaining about the trigger syntax.

It could be as simple as the "old" needing to be "OLD". Mind testing the statements in mysql:

Code:

CREATE TRIGGER delete_movie AFTER DELETE ON movie FOR EACH ROW BEGIN DELETE FROM art WHERE media_id=old.idMovie AND media_type='movie'; END

[lumendes]

Thanks jmarshall,

I'll try it when at home, don't have access to the equipment at the moment.

One thing however, is that the server in cause is using MySQL 4.1 and not MySQL 5 (latest).
There were at least some changes in query syntax between those 2 mysql major versions.
Just don't know if it's supposed to be fully backwards compatible with mysql 4 query syntax.

[jmarshall]

A quick google suggests that MySQL 4.1 didn't have CREATE TRIGGER at all - it was introduced in MySQL 5.0.

This is odd, however, as the music db has always had triggers associated with it - are you sharing that?

I don't see any point supporting an old database to be honest.

[lumendes]

Ok, so most likely is just due to being using and old database in the server.
Don't actually have a music collection, I can try later adding some and check the outcome.

I guess I'll have to update mysql in my small hacked Lacie NAS2
Only used mysql 4 because had few space left in root partition.

Thanks

[lumendes]

Ok, managed to update my NAS to mysql5 (add to symlink /opt to the media partition and run mysql as root)
A bit hacky but well, that's another story.

Anyhow with updated mysql version, the problem is not reproducible any more.
Everything runs smoothly

Thanks

[dunt]

I was having the exact same issue for some time now.
Using MySQL 5.

In addition to this, things were running at a max of 5 fps through main menu, and video related menu's.. but perfectly fine in settings window.

My original database names were as follows:

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| myvideos60 |
| performance_schema |
| xbmc_music1818 |
+--------------------+

The nightly build version at some point added an additional 'myvideos64'
Dropped this database, and let xbmc recreate it.... and after slight delay (while it copied the db i guess), everything is now perfect.
Library shows correctly, and fps is back up where is should be.

Just thought I'd share =)

Log file from when things were going haywire can be provided if wanted for review or something.