•   
  • 1
  • 17
  • 18
  • 19
  • 20
  • 21(current)
[MYSQL] HOW-TO: 5 User XBMC
Hello again.  I moved some files around and tried to clean the database, but now I'm getting two references for a few videos.  This error in the logs.
2019-08-24 18:54:57.485 T:4088872976  NOTICE: CleanDatabase: Starting videodatabase cleanup ..
2019-08-24 18:55:14.167 T:4088872976   ERROR: SQL: [Kodi_U01_Video_116] The table does not exist
                                            Query: DELETE FROM files WHERE idFile IN (334,424,487,493,497,674,738,833,853,863,1033,1137,1297,1482,3,3,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,176,177,178,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,243,244,245,246,247,248,249,249,250,251,252,253,254,255,256,257,258,259,260,261,261,262,263,264)
2019-08-24 18:55:14.168 T:4088872976   ERROR: CleanDatabase failed
I did run the delete_file trigger SQL query when I set it up.  Is there something else I need to do?  Is this error to be expected because I'm only using 5 of the 9 databases?  I think I noticed a typo in the delete_file trigger.  You have Kodi_U03_Video_116 in there twice and skipped U04, so I changed that when I ran it.  Was that not correct?
Reply
(2019-08-25, 06:19)OtheA Wrote: Hello again.  I moved some files around and tried to clean the database, but now I'm getting two references for a few videos.  This error in the logs.
2019-08-24 18:54:57.485 T:4088872976  NOTICE: CleanDatabase: Starting videodatabase cleanup ..
2019-08-24 18:55:14.167 T:4088872976   ERROR: SQL: [Kodi_U01_Video_116] The table does not exist
                                            Query: DELETE FROM files WHERE idFile IN (334,424,487,493,497,674,738,833,853,863,1033,1137,1297,1482,3,3,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,176,177,178,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,243,244,245,246,247,248,249,249,250,251,252,253,254,255,256,257,258,259,260,261,261,262,263,264)
2019-08-24 18:55:14.168 T:4088872976   ERROR: CleanDatabase failed
I did run the delete_file trigger SQL query when I set it up.  Is there something else I need to do?  Is this error to be expected because I'm only using 5 of the 9 databases?  I think I noticed a typo in the delete_file trigger.  You have Kodi_U03_Video_116 in there twice and skipped U04, so I changed that when I ran it.  Was that not correct?
Wow I did have a typo haha Thank you I have updated it Smile
This just removes bookmarks of deleted files no biggie just keeping things tidy.

That's fine if your only using 5 of the 9 or increased it to 20 or whatever.
you will just need to remove lines from the tiggers and not run the user setup of the other. can leave the other cols within the "global_files" table or drop them it wont change anything.

within the database "Kodi_U01_Video_116" do you have a view called "files", this must be a view and not a table. You should also have a table "global_files" but if your missing "files" run the following
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

    CREATE ALGORITHM=MERGE DEFINER=`KODI_18`@`%` SQL SECURITY DEFINER VIEW `files`
    AS SELECT
      `global_files`.`idFile` AS `idFile`,
      `global_files`.`idPath` AS `idPath`,
      `global_files`.`strFilename` AS `strFilename`,
      `global_files`.`playCount_U01` AS `playCount`,
      `global_files`.`lastPlayed_U01` AS `lastPlayed`,
      `global_files`.`dateAdded` AS `dateAdded`
    FROM `global_files`;

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
Reply
(2019-08-25, 21:08)BigMong Wrote:
(2019-08-25, 06:19)OtheA Wrote: Hello again.  I moved some files around and tried to clean the database, but now I'm getting two references for a few videos.  This error in the logs.
2019-08-24 18:54:57.485 T:4088872976  NOTICE: CleanDatabase: Starting videodatabase cleanup ..
2019-08-24 18:55:14.167 T:4088872976   ERROR: SQL: [Kodi_U01_Video_116] The table does not exist
                                            Query: DELETE FROM files WHERE idFile IN (334,424,487,493,497,674,738,833,853,863,1033,1137,1297,1482,3,3,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,176,177,178,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,243,244,245,246,247,248,249,249,250,251,252,253,254,255,256,257,258,259,260,261,261,262,263,264)
2019-08-24 18:55:14.168 T:4088872976   ERROR: CleanDatabase failed
I did run the delete_file trigger SQL query when I set it up.  Is there something else I need to do?  Is this error to be expected because I'm only using 5 of the 9 databases?  I think I noticed a typo in the delete_file trigger.  You have Kodi_U03_Video_116 in there twice and skipped U04, so I changed that when I ran it.  Was that not correct?
Wow I did have a typo haha Thank you I have updated it Smile
This just removes bookmarks of deleted files no biggie just keeping things tidy.

That's fine if your only using 5 of the 9 or increased it to 20 or whatever.
you will just need to remove lines from the tiggers and not run the user setup of the other. can leave the other cols within the "global_files" table or drop them it wont change anything.

within the database "Kodi_U01_Video_116" do you have a view called "files", this must be a view and not a table. You should also have a table "global_files" but if your missing "files" run the following
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

    CREATE ALGORITHM=MERGE DEFINER=`KODI_18`@`%` SQL SECURITY DEFINER VIEW `files`
    AS SELECT
      `global_files`.`idFile` AS `idFile`,
      `global_files`.`idPath` AS `idPath`,
      `global_files`.`strFilename` AS `strFilename`,
      `global_files`.`playCount_U01` AS `playCount`,
      `global_files`.`lastPlayed_U01` AS `lastPlayed`,
      `global_files`.`dateAdded` AS `dateAdded`
    FROM `global_files`;

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
 

I have a view called "files" and a table called "global_files" in U01.  I did a search for one of my movies and it is indeed listed twice in the database.  I'll run the above query tonight and report back.  Thanks again!

Yup.  SQL responded with #1050 - Table 'files' already exists.
Reply
(2019-08-25, 22:03)OtheA Wrote:
(2019-08-25, 21:08)BigMong Wrote:
(2019-08-25, 06:19)OtheA Wrote: Hello again.  I moved some files around and tried to clean the database, but now I'm getting two references for a few videos.  This error in the logs.
2019-08-24 18:54:57.485 T:4088872976  NOTICE: CleanDatabase: Starting videodatabase cleanup ..
2019-08-24 18:55:14.167 T:4088872976   ERROR: SQL: [Kodi_U01_Video_116] The table does not exist
                                            Query: DELETE FROM files WHERE idFile IN (334,424,487,493,497,674,738,833,853,863,1033,1137,1297,1482,3,3,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,176,177,178,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,243,244,245,246,247,248,249,249,250,251,252,253,254,255,256,257,258,259,260,261,261,262,263,264)
2019-08-24 18:55:14.168 T:4088872976   ERROR: CleanDatabase failed
I did run the delete_file trigger SQL query when I set it up.  Is there something else I need to do?  Is this error to be expected because I'm only using 5 of the 9 databases?  I think I noticed a typo in the delete_file trigger.  You have Kodi_U03_Video_116 in there twice and skipped U04, so I changed that when I ran it.  Was that not correct?
Wow I did have a typo haha Thank you I have updated it Smile
This just removes bookmarks of deleted files no biggie just keeping things tidy.

That's fine if your only using 5 of the 9 or increased it to 20 or whatever.
you will just need to remove lines from the tiggers and not run the user setup of the other. can leave the other cols within the "global_files" table or drop them it wont change anything.

within the database "Kodi_U01_Video_116" do you have a view called "files", this must be a view and not a table. You should also have a table "global_files" but if your missing "files" run the following
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

    CREATE ALGORITHM=MERGE DEFINER=`KODI_18`@`%` SQL SECURITY DEFINER VIEW `files`
    AS SELECT
      `global_files`.`idFile` AS `idFile`,
      `global_files`.`idPath` AS `idPath`,
      `global_files`.`strFilename` AS `strFilename`,
      `global_files`.`playCount_U01` AS `playCount`,
      `global_files`.`lastPlayed_U01` AS `lastPlayed`,
      `global_files`.`dateAdded` AS `dateAdded`
    FROM `global_files`;

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
 

I have a view called "files" and a table called "global_files" in U01.  I did a search for one of my movies and it is indeed listed twice in the database.  I'll run the above query tonight and report back.  Thanks again!

Yup.  SQL responded with #1050 - Table 'files' already exists. 

Sorry for the delay,
Two other thoughts about this
check the permissions of the user "KODI_18"
GRANT ALL PRIVILEGES ON `Kodi\_U01\_Video\_116`.* TO 'KODI_18'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
then try a cleanup again.

and/or try and do the task manually for one of the id's if able log into the database as the KODI_18 user.
USE `Kodi_U01_Video_116`;
DELETE FROM files WHERE idFile IN (334);
just to confirm the table is working
Reply
(2019-08-27, 21:35)BigMong Wrote:
(2019-08-25, 22:03)OtheA Wrote:
(2019-08-25, 21:08)BigMong Wrote: Wow I did have a typo haha Thank you I have updated it Smile
This just removes bookmarks of deleted files no biggie just keeping things tidy.

That's fine if your only using 5 of the 9 or increased it to 20 or whatever.
you will just need to remove lines from the tiggers and not run the user setup of the other. can leave the other cols within the "global_files" table or drop them it wont change anything.

within the database "Kodi_U01_Video_116" do you have a view called "files", this must be a view and not a table. You should also have a table "global_files" but if your missing "files" run the following
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

    CREATE ALGORITHM=MERGE DEFINER=`KODI_18`@`%` SQL SECURITY DEFINER VIEW `files`
    AS SELECT
      `global_files`.`idFile` AS `idFile`,
      `global_files`.`idPath` AS `idPath`,
      `global_files`.`strFilename` AS `strFilename`,
      `global_files`.`playCount_U01` AS `playCount`,
      `global_files`.`lastPlayed_U01` AS `lastPlayed`,
      `global_files`.`dateAdded` AS `dateAdded`
    FROM `global_files`;

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;

I have a view called "files" and a table called "global_files" in U01.  I did a search for one of my movies and it is indeed listed twice in the database.  I'll run the above query tonight and report back.  Thanks again!

Yup.  SQL responded with #1050 - Table 'files' already exists.   

Sorry for the delay,
Two other thoughts about this
check the permissions of the user "KODI_18"
GRANT ALL PRIVILEGES ON `Kodi\_U01\_Video\_116`.* TO 'KODI_18'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
then try a cleanup again.

and/or try and do the task manually for one of the id's if able log into the database as the KODI_18 user.
USE `Kodi_U01_Video_116`;
DELETE FROM files WHERE idFile IN (334);
just to confirm the table is working  

Thanks BigMong.  No need to apologize.  Still not working for me.  I ran the query from SQL and got the following:

#1146 - Table 'Kodi_U06_Video_116.bookmark' doesn't exist

Which is correct, because I didn't create anything past U05, so I expect this would be a normal error.

Okay, that fixed it.  I didn't realize I needed to remove the users I didn't create from the cleanup.  The query was halting at the first user that didn't exist so it couldn't complete.  I went back and removed the users I didn't configure and cleaned the library and that got rid of the extras.  Thanks again BigMong.  I appreciate you again.
Reply
(2019-08-28, 05:36)OtheA Wrote:
(2019-08-27, 21:35)BigMong Wrote:
(2019-08-25, 22:03)OtheA Wrote: I have a view called "files" and a table called "global_files" in U01.  I did a search for one of my movies and it is indeed listed twice in the database.  I'll run the above query tonight and report back.  Thanks again!

Yup.  SQL responded with #1050 - Table 'files' already exists.   

Sorry for the delay,
Two other thoughts about this
check the permissions of the user "KODI_18"
GRANT ALL PRIVILEGES ON `Kodi\_U01\_Video\_116`.* TO 'KODI_18'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
then try a cleanup again.

and/or try and do the task manually for one of the id's if able log into the database as the KODI_18 user.
USE `Kodi_U01_Video_116`;
DELETE FROM files WHERE idFile IN (334);
just to confirm the table is working   

Thanks BigMong.  No need to apologize.  Still not working for me.  I ran the query from SQL and got the following:

#1146 - Table 'Kodi_U06_Video_116.bookmark' doesn't exist

Which is correct, because I didn't create anything past U05, so I expect this would be a normal error.

Okay, that fixed it.  I didn't realize I needed to remove the users I didn't create from the cleanup.  The query was halting at the first user that didn't exist so it couldn't complete.  I went back and removed the users I didn't configure and cleaned the library and that got rid of the extras.  Thanks again BigMong.  I appreciate you again. 

Awesome glad its working Smile
Reply
Hey BigMong, 

Thanks for updating the SQL for V116, I am moving from a solo user setup to hopefully allowing another user to have seperate watchlists maintained on the database as per your tutorial.

I have databases U01-U09 setup, the master db has 42 tables/views and the others 37, and I also did the delete trigger update. 

My question (and this may come across as stupid but I'm a kodi novice), when do I add profiles to my kodi setup (currently just the master user running off the U01 DB), and if I was to just add a new user, how will kodi know whether 'Marcus' or 'Susan' is U03 or U07 etc, and also what settings should I use, shared readonly, seperate, etc. 

Apologies for the beginner level questioning, I'm familiar with SQL, XML etc, just not Kodi
Reply
I just recently went through the same process.  You'll copy your advancedsettings.xml to the profile directory and change the DB reference there.  The Wiki has info on how to create Profiles here.

Example:
Create Marcus Profile in Kodi. Kodi creates Marcus directory under profiles.
Copy advancedsettings.xml from main profile to Marcus directory.
Edit advancedsettings.xml in Marcus' directory and change Kodi_U01_Video_116 to Kodi_U03_Video_116 per your example above.
Now when Marcus is logged in, it will access his U03 DB.

Hope this helps.
Reply
Perfectly explained thank you! Everything is working as expected Big Grin
Reply
  •   
  • 1
  • 17
  • 18
  • 19
  • 20
  • 21(current)
 
Thread Rating:
  • 4 Vote(s) - 5 Average



Logout Mark Read Team Forum Stats Members Help
[MYSQL] HOW-TO: 5 User XBMC54