[john_es]

What does the "TRIGGER FIX FOR MULT USERS" part actually do?

[BigMong]

@john_es

Yes you could use Main and X numbers, It's just how I like it with names and if I need to pull data from the DB I don't need to remember who is who
If I get to it I might do up a little script to sub in the names.

"This has only been Tested on a New Setup"
This means that using Kodi to upgrade the DB will prob fail as there are new tables and columns, and files is a view now not a table as its been renamed to globalfiles

PHP Code:

/* TRIGGER FIX FOR MULT USERS */
  DROP TRIGGER IF EXISTS `Kodi_Test_Matt_Video_99`.`delete_file`;
  DELIMITER $$
  /* REPLACE ---> KODI <--- WITH YOUR MYSQL LOGIN UID FROM THE ADVANCEDSETTINGS.XML */
  CREATE DEFINER=`KODI`@`%` TRIGGER `Kodi_Test_Matt_Video_99`.`delete_file` AFTER DELETE
  ON `Kodi_Test_Matt_Video_99`.`globalfiles`
  FOR EACH ROW
  BEGIN
    DELETE FROM `Kodi_Test_Matt_Video_99`.bookmark WHERE idfile = old.idfile;
    DELETE FROM `Kodi_Test_Mace_Video_99`.bookmark WHERE idfile = old.idfile;
    DELETE FROM `Kodi_Test_Kiyana_Video_99`.bookmark WHERE idfile = old.idfile;
    DELETE FROM `Kodi_Test_Jaide_Video_99`.bookmark WHERE idfile = old.idfile;
    DELETE FROM settings WHERE idfile = old.idfile;
    DELETE FROM stacktimes WHERE idfile = old.idfile;
    DELETE FROM streamdetails WHERE idfile = old.idfile;
  END$$
  DELIMITER ; 


This is a clean-up Trigger the default Kodi install has it, This will remove any bookmarks, settings, stacktimes, and streamdetails info when you remove a file from the globalfiles.

• bookmarks [Where you leave an unfinished video]
  
• settings [Subtitles On, Zoom, Audio Stream data etc (If you turn Subtitles off and another user goes to watch it its off for them too)]
  
• stacktimes [Stack times for Stacked files (The ones that are like CD1, CD2, etc)]
  
• streamdetails [Video info Codec, Width, Hight, Video Duration, etc]
  

If you are like me and keep everything then this isn't really needed but I know some people delete stuff and this will keep the DB tidy.

Hope that answers your questions

[npbhdk]

I attempted to use the 99 instructions, didnt get any errors when using the sql. But kodi seems to fail at inserting data into the views.
i get errors like this when i attempt to scan files

18:19:34 T:944 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into files (idFile, idPath, strFileName) values(NULL, 8, lots-tfoftr.mkv')
18:19:34 T:944 ERROR: CVideoDatabase::AddFile unable to addfile ()

and

18:19:17 T:5900 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/')
18:19:17 T:5900 ERROR: CVideoDatabase::AddPath unable to addpath (insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/'))

when attempting to change content type of a source this works when i use the master database in the advancedsettingsfile scanning for content does not

[RonM]

Big thanks to BigMong.

I have been stuck on Kodi 14 multi-user setup, postponing the upgrade because of all the work to figure out the new database schema and views. You made my life a lot easier, thanks!

Next step is to upgrade to Kodi 16.1, but since all my devices run on OpenELEC, I'll wait another month or so as it is still in beta (OpenELEC 7.0 beta 3).

BTW, I too use trakt.tv and can recommend it to anyone. The free version is sufficient for most any user, but I decided to become a VIP ($2.50/month).

[BigMong]

@npbhdk

I attempted to use the 99 instructions, didnt get any errors when using the sql. But kodi seems to fail at inserting data into the views.
i get errors like this when i attempt to scan files

18:19:34 T:944 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into files (idFile, idPath, strFileName) values(NULL, 8, lots-tfoftr.mkv')
18:19:34 T:944 ERROR: CVideoDatabase::AddFile unable to addfile ()

and

18:19:17 T:5900 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/')
18:19:17 T:5900 ERROR: CVideoDatabase::AddPath unable to addpath (insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/'))

when attempting to change content type of a source this works when i use the master database in the advancedsettingsfile scanning for content does not

If I understand right, you are using all systems Master and Slaves to scan for media? (I have found its best to use only the one system Master or Slave to import, as when you do a scan on a different user it will redo the whole TV Shows as the Hash changes (Haven't looked too far into why that is))

By the looks of the errors its trying to INSERT into a non insertable view, I would check that you can view data in that view "files" that is linked to "globalfiles" does the Master and Slave connect to the DB with the same Login? Does the login being used have All Privileges to the view?

@RonM

Thank You, Glad you are loving it, OpenELEC on some RPI2 or 3's work awesome around the house, and now a few streets over, over a PTP WiFi network

[npbhdk]

@npbhdk

I attempted to use the 99 instructions, didnt get any errors when using the sql. But kodi seems to fail at inserting data into the views.
i get errors like this when i attempt to scan files

18:19:34 T:944 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into files (idFile, idPath, strFileName) values(NULL, 8, lots-tfoftr.mkv')
18:19:34 T:944 ERROR: CVideoDatabase::AddFile unable to addfile ()

and

18:19:17 T:5900 ERROR: SQL: [Niels_Video_99] Undefined MySQL error: Code (1471) Query: insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/')
18:19:17 T:5900 ERROR: CVideoDatabase::AddPath unable to addpath (insert into path (idPath, strPath) values (NULL, 'smb://N-HTPC-PC/tv2/'))

when attempting to change content type of a source this works when i use the master database in the advancedsettingsfile scanning for content does not

If I understand right, you are using all systems Master and Slaves to scan for media? (I have found its best to use only the one system Master or Slave to import, as when you do a scan on a different user it will redo the whole TV Shows as the Hash changes (Haven't looked too far into why that is))

By the looks of the errors its trying to INSERT into a non insertable view, I would check that you can view data in that view "files" that is linked to "globalfiles" does the Master and Slave connect to the DB with the same Login? Does the login being used have All Privileges to the view?

@RonM

Thank You, Glad you are loving it, OpenELEC on some RPI2 or 3's work awesome around the house, and now a few streets over, over a PTP WiFi network

Ive tried both scanning on the master and the slave, neither works and yes they use same database login, ive tried logging into the database with a gui client and i can insert data into the view with that, and i can see it aswell. Not really sure why it fails with kodi The mysql user has these priviliges (GRANT ALL PRIVILEGES ON *.* TO 'xbmc'@'%' WITH GRANT OPTION) so if got it right(havent used mysql much) it should be able to do everything like root can tried to use root login didnt work either same error.

[D0bby]

Edit: "question was dump^^"

[D0bby]

Got it. Thanks anyways for the awesome script

[Sushi7]

@BigMong

I use the default Kodi Watch Status.
pressing W or Context menu Mark as Watched.

Once I have my "Matt" DB created I open Kodi and test the Watch Status work.

After I implement the code:

Code:

RENAME TABLE `Sushi99`.`files` TO `Sushi99`.`globalfiles`;
  ALTER TABLE `Sushi99`.`globalfiles` CHANGE playCount playCountSushi INT(11);
  ALTER TABLE `Sushi99`.`globalfiles` CHANGE lastPlayed lastPlayedSushi TEXT;
  ALTER TABLE `Sushi99`.`globalfiles` ADD playCountChloe INT(11) AFTER lastPlayedSushi;
  ALTER TABLE `Sushi99`.`globalfiles` ADD lastPlayedChloe TEXT AFTER playCountChloe;
  ALTER TABLE `Sushi99`.`globalfiles` ADD playCountFelicia INT(11) AFTER lastPlayedChloe;
  ALTER TABLE `Sushi99`.`globalfiles` ADD lastPlayedFelicia TEXT AFTER playCountFelicia;

  CREATE VIEW `Sushi99`.`files` AS SELECT
   `Sushi99`.`globalfiles`.`idFile` AS `idFile`,
   `Sushi99`.`globalfiles`.`idPath` AS `idPath`,
   `Sushi99`.`globalfiles`.`strFilename` AS `strFilename`,
   `Sushi99`.`globalfiles`.`playCountSushi` AS `playCount`,
   `Sushi99`.`globalfiles`.`lastPlayedSushi` AS `lastPlayed`,
   `Sushi99`.`globalfiles`.`dateAdded` AS `dateAdded`
  FROM `Sushi99`.`globalfiles`;

The watched status does not function anymore.

From the Log:

Code:

16:48:27 T:9872   DEBUG: CInputManager::OnKey: w (0xf057) pressed, action is ToggleWatched
16:48:27 T:2948   DEBUG: Mysql Start transaction
16:48:27 T:2948   DEBUG: Mysql execute: update files set playCount=NULL,lastPlayed=NULL where idFile=2
16:48:27 T:2948   ERROR: SQL: [sushi99] Undefined MySQL error: Code (1288)
                                            Query: update files set playCount=NULL,lastPlayed=NULL where idFile=2
16:48:27 T:2948   ERROR: CVideoDatabase::SetPlayCount failed
16:48:27 T:2948   DEBUG: Mysql commit transaction

I have narrowed it down to this code, and this is the first and only SQL Queries I have run.
I believe Kodi 16.1 Jarvis has made some changes that after we ALTER the Tables/Views this breaks.

This was working on 15 Isengrad

[BigMong]

@Sushi7

Is this only when you are marking as un-watched?
does it work marking as watch or watching the video?

I will do some testing

also what version of MySQL are you running?

Mine is running on an older one "Server version: 5.5.40-0ubuntu1 - (Ubuntu)"

[Sushi7]

MySQL 5.7 Windows
Kodi 16.1 Jarvis
Windows 10 Pro

Trying to mark or unmark Watched does not work but watching the Movie all the way through automatically marks as watched

MySQL states error 1288:
Error: 1288 SQLSTATE: HY000 (ER_NON_UPDATABLE_TABLE)
Message: The target table %s of the %s is not updatable

https://dev.mysql.com/doc/refman/5.5/en/...erver.html

I have also checked the IS_UPDATABLE column in the INFORMATION_SCHEMA.VIEWS table and it is set properly by default.
http://dev.mysql.com/doc/refman/5.7/en/v...ility.html

If I run the query manually directly in SQL on the View Files it works

Code:

update files set playCount=NULL,lastPlayed=NULL where idFile=2

Just not from within Kodi

[BigMong]

@Sushi7

I spotted that error and I have tested the manually running the SQL
it's strange that it works when your watch an episode but that is calling a different SQL string

After a few mins looking at the source on GitHub https://github.com/xbmc/xbmc

In the /xbmc/video/VideoDatabase.cpp file, line 5155 I believe is the SQL that is Prepared

Code:

if (!date.IsValid())
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed=NULL where idFile=%i", id);
      else
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed='%s' where idFile=%i", date.GetAsDBDateTime().c_str(), id);
    }

I will setup my 16.1 Kodi again and do some testing over the weekend

[gaxander]

@Sushi7

I spotted that error and I have tested the manually running the SQL
it's strange that it works when your watch an episode but that is calling a different SQL string

After a few mins looking at the source on GitHub https://github.com/xbmc/xbmc

In the /xbmc/video/VideoDatabase.cpp file, line 5155 I believe is the SQL that is Prepared

Code:

if (!date.IsValid())
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed=NULL where idFile=%i", id);
      else
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed='%s' where idFile=%i", date.GetAsDBDateTime().c_str(), id);
    }

I will setup my 16.1 Kodi again and do some testing over the weekend

Have you made any progress??

[ascona]

@Sushi7

I spotted that error and I have tested the manually running the SQL
it's strange that it works when your watch an episode but that is calling a different SQL string

After a few mins looking at the source on GitHub https://github.com/xbmc/xbmc

In the /xbmc/video/VideoDatabase.cpp file, line 5155 I believe is the SQL that is Prepared

Code:

if (!date.IsValid())
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed=NULL where idFile=%i", id);
      else
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed='%s' where idFile=%i", date.GetAsDBDateTime().c_str(), id);
    }

I will setup my 16.1 Kodi again and do some testing over the weekend

Have you made any progress??

Yep I'm getting the same issue and still can't figure it out after spending most of the night on it!

[Sushi7]

I have decided to use Isengrad 15 to be able to use the Watch status correctly.