Page 1 of 1

MySQL databases not creating views

Posted: 12 Jul 2018, 12:59
by Mrmc_kiwi
When appletv or iOS mrmc creates the databases, it doesn’t seem to create the views.

I am using a qnap running MariaDB

Is there a sql script to drop/create the views?

Thanks.

Re: MySQL databases not creating views

Posted: 12 Jul 2018, 13:25
by koying
Debuglog would be nice to know why they are not created in the first place.

Probably some mariadb tweaks to be applied.

Re: MySQL databases not creating views

Posted: 12 Jul 2018, 20:40
by Mrmc_kiwi
Will recreate the dbs with logging enabled, and can provide the sql logs as well if helpful

Will be away for the weekend but will look at it afterwards.

Cheers

Re: MySQL databases not creating views

Posted: 16 Jul 2018, 06:49
by Mrmc_kiwi
LOGS:

Code: Select all

<i>
</i>18:44:40 T:6174748672  NOTICE: Running database version TV29
18:44:40 T:6174748672  NOTICE: Running database version Epg11
18:45:48 T:6170734592  NOTICE: Running database version Addons20
18:45:48 T:6170734592  NOTICE: Running database version ViewModes6
18:45:48 T:6170734592  NOTICE: Running database version Textures13
18:45:48 T:6170734592  NOTICE: Running database version mrmc_MyMusic60
18:45:48 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG107 [1049](Unknown database 'mrmc_MyVideosBUG107')
18:45:48 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG106 [1049](Unknown database 'mrmc_MyVideosBUG106')
18:45:48 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG105 [1049](Unknown database 'mrmc_MyVideosBUG105')
18:45:48 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG104 [1049](Unknown database 'mrmc_MyVideosBUG104')
18:45:48 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG103 [1049](Unknown database 'mrmc_MyVideosBUG103')
18:45:48 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG102 [1049](Unknown database 'mrmc_MyVideosBUG102')
18:45:48 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG101 [1049](Unknown database 'mrmc_MyVideosBUG101')
18:45:48 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG100 [1049](Unknown database 'mrmc_MyVideosBUG100')
18:45:48 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG99 [1049](Unknown database 'mrmc_MyVideosBUG99')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG98 [1049](Unknown database 'mrmc_MyVideosBUG98')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG97 [1049](Unknown database 'mrmc_MyVideosBUG97')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG96 [1049](Unknown database 'mrmc_MyVideosBUG96')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG95 [1049](Unknown database 'mrmc_MyVideosBUG95')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG94 [1049](Unknown database 'mrmc_MyVideosBUG94')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG93 [1049](Unknown database 'mrmc_MyVideosBUG93')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG92 [1049](Unknown database 'mrmc_MyVideosBUG92')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG91 [1049](Unknown database 'mrmc_MyVideosBUG91')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG90 [1049](Unknown database 'mrmc_MyVideosBUG90')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG89 [1049](Unknown database 'mrmc_MyVideosBUG89')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG88 [1049](Unknown database 'mrmc_MyVideosBUG88')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG87 [1049](Unknown database 'mrmc_MyVideosBUG87')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG86 [1049](Unknown database 'mrmc_MyVideosBUG86')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG85 [1049](Unknown database 'mrmc_MyVideosBUG85')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG84 [1049](Unknown database 'mrmc_MyVideosBUG84')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG83 [1049](Unknown database 'mrmc_MyVideosBUG83')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG82 [1049](Unknown database 'mrmc_MyVideosBUG82')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG81 [1049](Unknown database 'mrmc_MyVideosBUG81')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG80 [1049](Unknown database 'mrmc_MyVideosBUG80')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG79 [1049](Unknown database 'mrmc_MyVideosBUG79')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG78 [1049](Unknown database 'mrmc_MyVideosBUG78')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG77 [1049](Unknown database 'mrmc_MyVideosBUG77')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG76 [1049](Unknown database 'mrmc_MyVideosBUG76')
18:45:49 T:6170734592   ERROR: Unable to open database: mrmc_MyVideosBUG75 [1049](Unknown database 'mrmc_MyVideosBUG75')
18:45:56 T:6170734592   ERROR: SQL: [mrmc_MyVideosBUG107] Undefined MySQL error: Code (1419)
                                            Query: CREATE TRIGGER delete_movie AFTER DELETE ON movie FOR EACH ROW BEGIN DELETE FROM genre_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM actor_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM director_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM studio_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM country_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM writer_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM movielinktvshow WHERE idMovie=old.idMovie; DELETE FROM art WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM tag_link WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM rating WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM uniqueid WHERE media_id=old.idMovie AND media_type='movie'; END
18:45:56 T:6170734592   ERROR: CreateDatabase unable to create database:0
18:45:56 T:6170734592  NOTICE: Running database version TV29
18:45:56 T:6170734592  NOTICE: Running database version Epg11<e>

Last SQL command (from DB)
CREATE INDEX ix_country_link_3 ON country_link (media_type(20))

Re: MySQL databases not creating views

Posted: 16 Jul 2018, 07:27
by Mrmc_kiwi
I have added GRANT SUPER to the user and this seems to have resolved the issue.

https://bugs.mysql.com/bug.php?id=39489

Re: MySQL databases not creating views  [SOLVED]

Posted: 16 Jul 2018, 09:35
by koying
See the alternative ways by tweaking binary logging: http://wiki.ispirer.com/sqlways/trouble ... ry-logging

Not sure if you actually need to do replication on your db.