search

henryjfry / fryhenryj

Kodi Addons
17 stars 3 forks source link

Error Contents: 1060 (42S21): Duplicate column name 'lastplayed' #3

Closed AakashC2020 closed 3 years ago

AakashC2020 commented 3 years ago

Hi, I'm getting the below error while running the MySQL version of this add-on. Please advise.

2020-10-27 19:38:10.096 T:16832 ERROR: Traceback (most recent call last): 2020-10-27 19:38:10.096 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.seren\resources\lib\modules\trakt_sync\activities.py", line 57, in sync_activities 2020-10-27 19:38:10.096 T:16832 ERROR: success = self._remove_old_meta_items('shows') 2020-10-27 19:38:10.096 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.seren\resources\lib\modules\trakt_sync\activities.py", line 606, in _remove_old_meta_items 2020-10-27 19:38:10.097 T:16832 ERROR: for i in range(2, int(trakt_api.response_headers['X-Pagination-Page-Count']) + 1): 2020-10-27 19:38:10.097 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.requests\lib\requests\structures.py", line 52, in getitem 2020-10-27 19:38:10.097 T:16832 ERROR: return self._store[key.lower()][1] 2020-10-27 19:38:10.097 T:16832 ERROR: KeyError: 'x-pagination-page-count' 2020-10-27 19:38:10.199 T:16832 ERROR: Traceback (most recent call last): 2020-10-27 19:38:10.199 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.seren\resources\lib\modules\trakt_sync\activities.py", line 75, in sync_activities 2020-10-27 19:38:10.199 T:16832 ERROR: success = self._remove_old_meta_items('movies') 2020-10-27 19:38:10.199 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\plugin.video.seren\resources\lib\modules\trakt_sync\activities.py", line 606, in _remove_old_meta_items 2020-10-27 19:38:10.199 T:16832 ERROR: for i in range(2, int(trakt_api.response_headers['X-Pagination-Page-Count']) + 1): 2020-10-27 19:38:10.199 T:16832 ERROR: File "C:\Users\SHIBAJI\AppData\Roaming\Kodi\addons\script.module.requests\lib\requests\structures.py", line 52, in getitem 2020-10-27 19:38:10.200 T:16832 ERROR: return self._store[key.lower()][1] 2020-10-27 19:38:10.200 T:16832 ERROR: KeyError: 'x-pagination-page-count' 2020-10-27 19:38:43.169 T:16276 WARNING: CGUIWindowManager - CGUIWindowManager::HandleAction - ignoring action 107, because topmost modal dialog closing animation is running 2020-10-27 19:38:46.454 T:16276 WARNING: Previous line repeats 2 times. 2020-10-27 19:38:46.454 T:16276 ERROR: Control 55 in window 10025 has been asked to focus, but it can't 2020-10-27 19:38:47.044 T:3388 ERROR: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--

henryjfry commented 3 years ago

Ok well I dont actually use a MYSQL database so it not something I can test but I suspect that its complaining about these two columns in the query "files.lastplayed, tvshowcounts.lastplayed".

When I was developing this I had issues with the MySQL query needing to have aliases set for parts of the query which worked without problems under sqlite. Therefore I think possibly the columns just need to have two different names set (ie "files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed") to prevent this error.

So can you test the following query in a python terminal or script:

import sqlite3 con = sqlite3.connect('/home/osmc/.kodi/userdata/Database/MyVideos116.db') cur = con.cursor() sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall() print sql_result

I tested it at my end and it still works, but the original worked for me too.

AakashC2020 commented 3 years ago

Hi, I'm a novice in Python scripting. I tried to run the above query, but I'm getting this error: Sqlite3, OperationalError: unable to open database file

Please advise.

henryjfry commented 3 years ago

Yeah you would need to point it to the correct database file. Modify this line:

con = sqlite3.connect('/home/osmc/.kodi/userdata/Database/MyVideos116.db')

To point to wherever your MySQL database is stored and then try it again.

AakashC2020 commented 3 years ago

The MySQL database in my Windows 10 PC is created like this: C:\ProgramData\MySQL\MySQL Server 5.6\data\myvideos116

Instead of a .db file it's an entire folder containing multiple files. How do I run the above query in this case? Please advise.

henryjfry commented 3 years ago

Right looking at the addon code the MySQL part connects like this:

import mysql.connector sql_username = addon.getSetting('username') sql_password = addon.getSetting('password') sql_host = addon.getSetting('host') sql_port = addon.getSetting('port') sql_db_name = addon.getSetting('db_name') con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name)

So you would need to replace these lines:

import sqlite3 con = sqlite3.connect('/home/osmc/.kodi/userdata/Database/MyVideos116.db')

With the correct connection details for your mysql database as above, with the username, password, host etc. You could look in the settings for the addon if you arent sure what details you have used as it appears as if it is connecting to your mysql database.

AakashC2020 commented 3 years ago

Hi I tried running this query: import sqlite3 con = sqlite3.connect('C:\ProgramData\MySQL\MySQL Server 5.6\data\myvideos116\')

However I received the below error: SyntaxError: EOL while scanning string literal

Please advise.

henryjfry commented 3 years ago

For MySQL "con = sqlite3.connect('C:\ProgramData\MySQL\MySQL Server 5.6\data\myvideos116')" isnt going to work.

You need to setup the connection like:

import mysql.connector sql_username = 'Username' sql_password = 'Password' sql_host = '192.168.0.99' sql_port = '1234' sql_db_name = 'database_name' con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name)

And whatever the real values of username,password,etc are

AakashC2020 commented 3 years ago

Hi, I ran the below query:

import mysql.connector sql_username = 'kodi' sql_password = '****' sql_host = '192.168.0.116' sql_port = '3306' sql_db_name = 'myvideos116' con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name) cur = con.cursor() sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall()

I received the below error:

Traceback (most recent call last): File "", line 1, in File "C:\Users\SHIBAJI\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\cursor_cext.py", line 234, in execute self._cnx.handle_unread_result() File "C:\Users\SHIBAJI\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 746, in handle_unread_result raise errors.InternalError("Unread result found") mysql.connector.errors.InternalError: Unread result found

Please advise.

AakashC2020 commented 3 years ago

Also I ran the below query and the results were as follows:

import mysql.connector sql_username = 'kodi' sql_password = '****' sql_host = '192.168.0.116' sql_port = '3306' sql_db_name = 'myvideos116' con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name) cur = con.cursor() sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall() Traceback (most recent call last): File "", line 1, in AttributeError: 'NoneType' object has no attribute 'fetchall' print sql_result File "", line 1 print sql_result ^ SyntaxError: Missing parentheses in call to 'print'. Did you mean print(sql_result)?

Please advise.

henryjfry commented 3 years ago

Ok possibly you need to close the connection, i think that error may be cause as it hasnt been getting closed properly.

Try running this on its own.

cur.close()

Then rerun the query:

import mysql.connector sql_username = 'kodi' sql_password = '****' sql_host = '192.168.0.116' sql_port = '3306' sql_db_name = 'myvideos116' con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name) cur = con.cursor() sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall() cur.close()

And let me know what happens?

AakashC2020 commented 3 years ago

Hi, see below please:

cur.close() Traceback (most recent call last): File "", line 1, in File "C:\Users\SHIBAJI\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\cursor_cext.py", line 402, in close self._cnx.handle_unread_result() File "C:\Users\SHIBAJI\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 746, in handle_unread_result raise errors.InternalError("Unread result found") mysql.connector.errors.InternalError: Unread result found

henryjfry commented 3 years ago

I think both those issue are due to an open cursor pointing at a non existent database. So you need to close it and recreate the cursor where the database query will be sent.

Thats how i read the "AttributeError: 'NoneType' object has no attribute 'fetchall'" error (attempting to fetch results on a query which didnt work).

The "mysql.connector.errors.InternalError: Unread result found" error also seems to have a connection to the results returned by the query. If it wasnt closed properly first im assuming that could cause this issue. But i dont know much about mysql and was only able to get this part of the addon working with much trial and error with a user with a mysql database over discord. So it might take a bit of back and forth to figure out why its not working for you.

AakashC2020 commented 3 years ago

Hi, Fine. I'll again check back on this later and let you know what happens. Thanks a lot for your help! :)

henryjfry commented 3 years ago

Some googling and it suggests that the cursor needs setup differently? Also rebooting the machine you are running this on and the machine the database is on may help? If it has anything to do with open connections that would be a way to ensure they close and everything is starting from fresh?

So the cursor option which may have some bearing:

cursor(buffered=True)

So your query would be:

import mysql.connector sql_username = 'kodi' sql_password = '****' sql_host = '192.168.0.116' sql_port = '3306' sql_db_name = 'myvideos116' con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name) cur = con.cursor(buffered=True) sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall() cur.close()

AakashC2020 commented 3 years ago

Hi, please see below:

import mysql.connector sql_username = 'kodi' sql_password = '****' sql_host = '192.168.0.116' sql_port = '3306' sql_db_name = 'myvideos116' con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name) cur = con.cursor(buffered=True) sql_result = cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, case when tvlastplayed > c05 then tvlastplayed else c05 end as tvlastplayed from (SELECT idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null GROUP BY tvshow.c00) as a)) as b where (idshow in (select idshow from (SELECT files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, files.lastplayed as fileslastplayed, tvshowcounts.lastplayed as tvlastplayed from episode, files, tvshow, tvshowcounts where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and (episode.idshow = tvshowcounts.idshow) and files.idfile in (select idfile from (SELECT max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 FROM episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 GROUP BY tvshow.c00) as c)) as d)) and c05 < current_date order by tvlastplayed desc;").fetchall() Traceback (most recent call last): File "", line 1, in AttributeError: 'NoneType' object has no attribute 'fetchall' cur.close() True

henryjfry commented 3 years ago

Hmmm, that error isnt really helping me too much "AttributeError: 'NoneType' object has no attribute 'fetchall'".

I've done a bit of a google and i've looked at the version of the sql query from my github and I dont think its the same as the version you posted. So try the following, which is from here v0.0.12 in my repo:

https://github.com/henryjfry/repository.nextup/blob/master/zips/service.next_playlist/service.next_playlist-0.0.12.zip

I see you have this version installed "plugin.video.nextup-mysql", I think thats your problem, I'm pretty sure I stopped updating that version and incorporated the features into the main addon. It should be in my repo. Anyway try the following, i think the previous setup with the ".fetchall()" attached to the query was tripping it up, but the following sql query is the current iteration and its not setup like that and I believe it has been tested and works on mysql.

import mysql.connector sql_username = 'kodi' sql_password = '****' sql_host = '192.168.0.116' sql_port = '3306' sql_db_name = 'myvideos116' con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name) cur = con.cursor() cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, episode1 from (select idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, lastplayed from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and files.idfile in (select idfile from (select min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null group by tvshow.c00) as a)) as b where (idshow in (select idshow from (select files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, lastplayed from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and files.idfile in (select idfile from (select max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 group by tvshow.c00) as c))as d)) and c05 < current_date order by c05 desc;") sql_result = cur.fetchall() print sql_result cur.close()

henryjfry commented 3 years ago

My repo is here:

https://henryjfry.github.io/repository.nextup/repo/

AakashC2020 commented 3 years ago

Finally, this worked:

import mysql.connector sql_username = 'kodi' sql_password = '****' sql_host = '192.168.0.116' sql_port = '3306' sql_db_name = 'myvideos116' con = mysql.connector.connect(host=sql_host, user=sql_username, passwd=sql_password, port=sql_port, db=sql_db_name) cur = con.cursor() cur.execute("select idepisode, c18, c13, tvshow1, genre, idshow, idseason, episode1 from (select idseason, files.idfile, episode.c00 as episode1, episode.c18, episode.c12, episode.c13, episode.c05, tvshow.c08 as genre, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, lastplayed from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and files.idfile in (select idfile from (select min(files.idfile) as idfile, min(episode.c05) as firstaired, tvshow.c00 from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount is null group by tvshow.c00) as a)) as b where (idshow in (select idshow from (select files.idfile, episode.c00 as episode1, episode.c05, idepisode, tvshow.c00 as tvshow1, episode.idShow, playcount, lastplayed from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and files.idfile in (select idfile from (select max(files.idfile) as idfile, max(episode.c05) as firstaired, tvshow.c00 from episode, files, tvshow where (episode.idfile = files.idfile) and (episode.idshow = tvshow.idshow) and playcount > 0 group by tvshow.c00) as c))as d)) and c05 < current_date order by c05 desc;") sql_result = cur.fetchall() print(sql_result) [(391, 'smb://192.168.0.116/TV Shows/TV Shows (From DVD)/English/RoboCop - Prime Directives (2001)/Season 01/S01E02 - Meltdown.avi', '2', 'Robocop: Prime Directives', 'Action & Adventure / Sci-Fi & Fantasy', 17, 54, 'Meltdown'), (396, 'smb://192.168.0.116/TV Shows/TV Shows (From DVD)/English/RoboCop - The Series (1994)/Season 01/S01E03 - Trouble in Delta City.avi', '3', 'RoboCop: The Series', 'Sci-Fi & Fantasy / Action & Adventure / Comedy / Crime', 18, 58, 'Trouble in Delta City')] cur.close() True

AakashC2020 commented 3 years ago

However, even though the SQL query worked, but the addon is not working. I entered my MySQL database configuration in the addon but every time I open it the settings box is popping up and after that nothing is happening. Please advise.

henryjfry commented 3 years ago

This add-on is different in that it runs as a service and creates a smart playlist with the entries from the SQL result.

So you can then point a widget at that or open it from playlists. If you really need the addon page I think I could get that working but it's basically the same list and I personally think the service add-on is better.

It's created in the playlists folder in userdata which should show up in Kodi and also smart shortcuts in skins.

AakashC2020 commented 3 years ago

I'm getting this error in Kodi after running the addon:

2020-10-28 03:33:51.883 T:2816 NOTICE: load skin from: C:\Program Files\Kodi\addons\skin.estuary (version: 2.0.27) 2020-10-28 03:33:53.355 T:2816 NOTICE: VideoPlayer::OpenFile: update_playlist_path 2020-10-28 03:33:53.357 T:4580 NOTICE: Creating InputStream 2020-10-28 03:33:53.357 T:4580 ERROR: CVideoPlayer::OpenInputStream - error opening [update_playlist_path] 2020-10-28 03:33:53.357 T:4580 NOTICE: CVideoPlayer::OnExit() 2020-10-28 03:33:53.562 T:2816 NOTICE: CVideoPlayer::CloseFile() 2020-10-28 03:33:53.562 T:2816 NOTICE: VideoPlayer: waiting for threads to exit 2020-10-28 03:33:53.562 T:2816 NOTICE: VideoPlayer: finished waiting 2020-10-28 03:33:53.568 T:8828 ERROR: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--

henryjfry commented 3 years ago

Also the service add-on does some additional stuff, like marking episodes watched and a couple of other things.

I'd have to review the code to see what I actually added. My own version has a number of additions which work for me but have never really been tested enough that I'd be confident releasing it.

henryjfry commented 3 years ago

I think that means that you need to install the Kodi add-on Mysql connector/python

https://kodi.wiki/view/Add-on:MySQL_Connector/Python

henryjfry commented 3 years ago

I should probably have added that as a dependancy but I obviously forgot.

AakashC2020 commented 3 years ago

Hi,

It seems we can't install a dependency addon separately. It needs to be embedded to your addon. Please check the below and advise.

Kindly see this link: https://forum.kodi.tv/showthread.php?tid=298428

"it's not possible to manually install script modules through the repo though, you need to depend on it in the addon.xml file of your addon. it will then be auto-installed when someone installs your addon."

The connector IS available in the official repo. You have to include it as a dependency of your add-on. When your add-on is installed, it will get pulled in and installed automatically.

This is part of the 'addon.xml' file for Video Database Cleaner, which installs it as a dependency.

<addon id="script.database.cleaner" name="Video Database Cleaner" version="0.5.7-1" provider-name="black_eagle, BatterPudding.">

<extension point="xbmc.python.script" library="default.py" />

all
henryjfry commented 3 years ago

Ok well im pretty sure i installed it manually at some point when i was building the addon so can you try installing it from here:

script.module.mysql.connector-master.zip

Which is just the following cloned as a zip from github:

https://github.com/add-ons/script.module.mysql.connector

henryjfry commented 3 years ago

Actually this is the version i have if that doesnt work:

https://github.com/twinther/script.module.myconnpy

script.module.myconnpy-master.zip

AakashC2020 commented 3 years ago

After installing these, the new error is as below:

2020-10-28 04:23:26.280 T:2996 ERROR: Control 55 in window 10025 has been asked to focus, but it can't 2020-10-28 04:23:30.893 T:12180 ERROR: XFILE::CDirectory::GetDirectory - Error getting plugin://service.next_playlist/ 2020-10-28 04:23:30.967 T:2996 ERROR: CGUIMediaWindow::GetDirectory(plugin://service.next_playlist/) failed

2020-10-28 04:23:14.953 T:2996 WARNING: CGUIMediaWindow::OnMessage - updating in progress 2020-10-28 04:23:19.833 T:2996 ERROR: Control 55 in window 10025 has been asked to focus, but it can't 2020-10-28 04:23:23.101 T:12772 ERROR: XFILE::CDirectory::GetDirectory - Error getting plugin://service.next_playlist/ 2020-10-28 04:23:23.188 T:2996 ERROR: CGUIMediaWindow::GetDirectory(plugin://service.next_playlist/) failed 2020-10-28 04:23:26.280 T:2996 ERROR: Control 55 in window 10025 has been asked to focus, but it can't 2020-10-28 04:23:30.893 T:12180 ERROR: XFILE::CDirectory::GetDirectory - Error getting plugin://service.next_playlist/ 2020-10-28 04:23:30.967 T:2996 ERROR: CGUIMediaWindow::GetDirectory(plugin://service.next_playlist/) failed 2020-10-28 04:26:06.508 T:2996 ERROR: Control 55 in window 10025 has been asked to focus, but it can't 2020-10-28 04:26:11.036 T:2996 NOTICE: VideoPlayer::OpenFile: update_playlist_path 2020-10-28 04:26:11.037 T:8988 NOTICE: Creating InputStream 2020-10-28 04:26:11.037 T:8988 ERROR: CVideoPlayer::OpenInputStream - error opening [update_playlist_path] 2020-10-28 04:26:11.037 T:8988 NOTICE: CVideoPlayer::OnExit() 2020-10-28 04:26:11.071 T:2996 NOTICE: CVideoPlayer::CloseFile() 2020-10-28 04:26:11.072 T:2996 NOTICE: VideoPlayer: waiting for threads to exit 2020-10-28 04:26:11.072 T:2996 NOTICE: VideoPlayer: finished waiting 2020-10-28 04:26:11.167 T:4204 ERROR: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--

Please advise.

AakashC2020 commented 3 years ago

I guess it is not working separately. I think it needs to be added as a dependency.

henryjfry commented 3 years ago

Did you try manually installing it from the zip:

https://github.com/twinther/script.module.myconnpy

https://github.com/henryjfry/fryhenryj/files/5448479/script.module.myconnpy-master.zip

AakashC2020 commented 3 years ago

Yes, I manually installed it from the Zip. Still the same issue.

henryjfry commented 3 years ago

If not you could install that add-on mentioned in the message as a work around. Otherwise I can do you a version with the dependancy but that would have to wait until tomorrow.

Have you checked it exists under the dependencies in settings/system/add-ons? And that it's enabled?

AakashC2020 commented 3 years ago

Fine. I'll wait for a version with the dependency. Please let me know when it's updated. Thanks for all your help!

henryjfry commented 3 years ago

It'll just be a zip version with a quick change to add-on.xml

I don't have git currently setup and I can never remember how to properly commit and push stuff to my repo Hence me not having updated it in months

AakashC2020 commented 3 years ago

Fine. I'll wait for the updated zip file. Thanks!

henryjfry commented 3 years ago

If you want you can download the zip and edit add-on.xml yourself with this in the requires section:

<import addon="script.module.myconnpy" version="1.1.7"/>

https://github.com/henryjfry/repository.nextup/blob/master/zips/service.next_playlist/service.next_playlist-0.0.12.zip

And reinstall it from the modified zip

AakashC2020 commented 3 years ago

I can try but it'll be really helpful if you could do it from your end as I'm a novice in Python scripting. Thanks!

henryjfry commented 3 years ago

Ok service.next_playlist-0.0.12a.zip has the following dependancy:

<import addon="script.module.mysql.connector" version="8.0.17"/>

service.next_playlist-0.0.12a.zip

And service.next_playlist-0.0.12b.zip:

<import addon="script.module.myconnpy" version="1.1.7"/>

service.next_playlist-0.0.12b.zip

As im not sure which version is in the official repo.

If that fails you can manually install an addon by extracting the addon from the zip file, and copying and pasting it into "/home/.kodi/addons" while kodi is not running, when you start kodi you can then go into the manage dependencies page in settings/system/addons and enable the addon, which will initially show up as disabled.

If that still doesnt work then my suggestion would be dont use a mysql database and im probably unable to help you any further without hands on access to your machine.

Buts its not actually a problem with the addon at this stage, but with a dependency which i was able to install without problems manually. If you aren't able to do that then there isn't too much help I can provide from my end.

AakashC2020 commented 3 years ago

Hi, No problem. I can understand. Actually I need to use MySQL database as there are 3 instances of Kodi running in my home having access to the shared database. Nevertheless thanks a lot for your help. I learnt a lot of new things from you. Keep up the good work! :)

henryjfry commented 3 years ago

So did either of those version 12's install the dependancy then?

Failing that you could try the addon "Video Database Cleaner" as per your message before from the kodi forum. Its in the Black Eagle repo (not sure where the kodi explorer repo path is but the zip file is below)

https://github.com/the-black-eagle/repository.blackeagle/blob/master/repository.blackeagle.zip

With the dependancy issue directly installing the addon from the zip might not work properly without the repo installed first but the direct link to the Video Database cleaner is here:

https://github.com/the-black-eagle/repository.blackeagle/blob/master/zips/script.database.cleaner/script.database.cleaner-0.5.9.zip

henryjfry commented 3 years ago

Also possibly if the addon isnt going to work installing the python "mysql.connector" module on the kodi machine on which it needs to run might work? However I think kodi has its own version of python, which is why addons like myconnpy are required to provide the required modules when the python scripts are launched as addons rather than being launched from the command line with a fully working python environment.

AakashC2020 commented 3 years ago

Hi, I tried installing both the versions of MySQL connector with the addon v12 from your repo. However, none of them worked. At present it's not possible for me to clean my database as it's quite large in size. I shall look into this issue later and let you know if I find any solution. Thanks!

henryjfry commented 3 years ago

You wouldn't need to actually clean your database, just install the addon to get the dependancy installed. Im pretty sure if you install the "Video Database Cleaner" addon you would still need to add the correct login to your mysql database before any of the cleaning actions would work, so just installing that addon shouldn't cause any problems or perform any database cleaning.

However i'm just trying to figure out how it is that this mysql connector addon wont install. Can you take a screenshot of the kodi system settings/addons/manage dependencies at the "M" section in the list?

Like the attached? IMG_20201028_115218

I want to see if its installed but disabled.

The two versions of the addon are called "MySQL Connector/Python" and "mysql-connector"

With the version I have installed which you can see on the screen: https://github.com/henryjfry/fryhenryj/files/5448479/script.module.myconnpy-master.zip

henryjfry commented 3 years ago

I checked "plugin.video.nextup-mysql.0.0.18.zip" and i did add the line: <import addon="script.module.myconnpy" version="1.1.7"/>

To that addon, so maybe reinstalling that addon may help? (if you uninstalled it)

plugin.video.nextup-mysql.0.0.18.zip

AakashC2020 commented 3 years ago

Hi, I'll try that but I think you got me all wrong. The MySQL connector for python has installed and it's showing up there under my addon dependencies. However, still I'm getting the error from the next episode addon even after installing that. Thanks!

henryjfry commented 3 years ago

Yeah im not sure what the problem is then. If its installed and enabled then it should just work with "import msql.connector" in the addon.

Can you please provide the screen shot? I want to see that its definetly enabled, its all too easy to think its installed but the addon is not actually enabled.

Also I want to see which version of the addon is installed. And are you on Kodi 18 or Kodi 19? Kodi 19 has a different version of Python and this addon isnt developed for that version.

henryjfry commented 3 years ago

Your initial errors were about the actual SQL query, which would have been using "import msql.connector" under "plugin.video.nextup-mysql", so im pretty certain it was working at one point.

If you uninstalled that addon it may have disabled the orphaned dependancy, thats all I can think that is happening.

henryjfry commented 3 years ago

Also it might be worth completely removing "plugin.video.nextup-mysql" and "service.next_playlist" and/or "script.module.mysql.connector" and "script.module.myconnpy"

And then reinstall just "service.next_playlist-0.0.12a.zip" or "service.next_playlist-0.0.12b.zip" with the dependancy added (use 12b which links to the exact same dependancy as "plugin.video.nextup-mysql"). With a reboot of kodi in between and after to make sure everything is running once it starts up again.

AakashC2020 commented 3 years ago

Yeah im not sure what the problem is then. If its installed and enabled then it should just work with "import msql.connector" in the addon.

Can you please provide the screen shot? I want to see that its definetly enabled, its all too easy to think its installed but the addon is not actually enabled.

Also I want to see which version of the addon is installed. And are you on Kodi 18 or Kodi 19? Kodi 19 has a different version of Python and this addon isnt developed for that version.

Hi, I'm using Kodi v18.9. The MySQL connector is installed and enabled. It's installed as a dependency for the TMDB scrapped so can't be installed or disabled. Thanks!

henryjfry commented 3 years ago

Ive just looked and "script.module.myconnpy" is a dependancy of "script.module.metahandler", so it should probably be installed by defualt.

Why its not working then, I'm not sure. What version number is it?