SQLException: Unkown Column 'created' in 'where clause' #883
Comments
|
Never mind, I droppped all |
|
I actually noticed this problem myself just yesterday, although it has been introduced already two months ago in v1.2.1. The thing is, when I added the NC22 support in v1.2.1, the DB migration broke for such cases where the new version of Music is installed over previous version older than v1.0.0. And that older version doesn't actually need to be active at the moment of installation; it's enough for the problem to arise if any older versions have ever been installed on the same cloud instance. The problem affects Nextcloud but not ownCloud. I'll try to fix this for the next release of the Music app, although there probably is all the time less and less such setups where Music older than v1.0.0 is present. In the mean time, the easiest workaround is just the one described above: drop the |
|
Alright, thanks for letting me know - this might be helpful for some users! For me I was pretty sure I had no playlists et cetera created back when I was testing the app, so no loss of data by dropping the tables. (...and by the way, it works like a charm now - thank you so much for this very nice app!) |
When defining the DB schema for Nextcloud was moved to use the migrations mechanism in v1.2.1 (commit 6982d91), a problem was introduced: The migration worked only either on a clean database (no earlier versions of Music installed) or on v1.0.0 or newer. Installing over any v0.x.y got broken. This was because the auto-generated migration script Version010200Date20210513171803 newer altered the DB schema of existing tables; it just created tables which were missing. The not so well-working migration script has now been replaced with the hand-written Version010000Date20210903000000. This script checks separately that each needed column exists in the database and creates the missing ones. It also removes couple of obsolete columns (removed over the years) if those are still present in the database. This new script should be able to migrate the database from any version starting from v0.4.0 to the v1.0.0 level. Most of these versions have not been tested, though. refs owncloud#889 refs owncloud#883
|
The application update over any older Music (database) version should now work with Music v1.3.2. |
I am attempting to install the Music app 1.3.1 on a Nextcloud 21 and - during the installation - am faced with the exception shown below. I am not 100% sure I have never had the Music app installed, but if so that must have been years ago. I am adding this because the error looks a bit like there were some tables already present in my database? If this is the case I'd love to learn which tables to drop in order to be able to install this app (again?)
{ "reqId": "xaiYiPEkF5me4ovjt1NL", "level": 3, "time": "2021-08-29T16:42:24+02:00", "remoteAddr": "2003:fc:8f1a:7900:e272:b4a1:a08b:9353", "user": "smn", "app": "settings", "method": "POST", "url": "/settings/apps/enable", "message": { "Exception": "OC\\DB\\Exceptions\\DbalException", "Message": "An exception occurred while executing a query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created' in 'where clause'", "Code": 1054, "Trace": [{ "file": "/var/www/nextcloud/lib/private/DB/ConnectionAdapter.php", "line": 77, "function": "wrap", "class": "OC\\DB\\Exceptions\\DbalException", "type": "::", "args": [{ "__class__": "Doctrine\\DBAL\\Exception\\InvalidFieldNameException" }] }, { "file": "/var/www/nextcloud/apps/music/lib/Migration/TimestampInit.php", "line": 60, "function": "executeUpdate", "class": "OC\\DB\\ConnectionAdapter", "type": "->", "args": ["UPDATE `*PREFIX*music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"]] }, { "file": "/var/www/nextcloud/apps/music/lib/Migration/TimestampInit.php", "line": 51, "function": "setCreated", "class": "OCA\\Music\\Migration\\TimestampInit", "type": "->", "args": ["*PREFIX*music_albums", "2021-08-29 14:42:24"] }, { "file": "/var/www/nextcloud/lib/private/Repair.php", "line": 118, "function": "run", "class": "OCA\\Music\\Migration\\TimestampInit", "type": "->", "args": [{ "__class__": "OC\\Repair" }] }, { "file": "/var/www/nextcloud/lib/private/legacy/OC_App.php", "line": 1062, "function": "run", "class": "OC\\Repair", "type": "->", "args": [] }, { "file": "/var/www/nextcloud/lib/private/Installer.php", "line": 166, "function": "executeRepairSteps", "class": "OC_App", "type": "::", "args": ["music", ["OCA\\Music\\Migration\\BogusTrackRemoval", "OCA\\Music\\Migration\\DiskNumberMigration", "OCA\\Music\\Migration\\TimestampInit"]] }, { "file": "/var/www/nextcloud/apps/settings/lib/Controller/AppSettingsController.php", "line": 448, "function": "installApp", "class": "OC\\Installer", "type": "->", "args": ["music"] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php", "line": 218, "function": "enableApps", "class": "OCA\\Settings\\Controller\\AppSettingsController", "type": "->", "args": [ ["music"], [] ] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php", "line": 127, "function": "executeController", "class": "OC\\AppFramework\\Http\\Dispatcher", "type": "->", "args": [{ "__class__": "OCA\\Settings\\Controller\\AppSettingsController" }, "enableApps"] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/App.php", "line": 157, "function": "dispatch", "class": "OC\\AppFramework\\Http\\Dispatcher", "type": "->", "args": [{ "__class__": "OCA\\Settings\\Controller\\AppSettingsController" }, "enableApps"] }, { "file": "/var/www/nextcloud/lib/private/Route/Router.php", "line": 302, "function": "main", "class": "OC\\AppFramework\\App", "type": "::", "args": ["OCA\\Settings\\Controller\\AppSettingsController", "enableApps", { "__class__": "OC\\AppFramework\\DependencyInjection\\DIContainer" }, { "_route": "settings.AppSettings.enableApps" }] }, { "file": "/var/www/nextcloud/lib/base.php", "line": 993, "function": "match", "class": "OC\\Route\\Router", "type": "->", "args": ["/settings/apps/enable"] }, { "file": "/var/www/nextcloud/index.php", "line": 37, "function": "handleRequest", "class": "OC", "type": "::", "args": [] }], "File": "/var/www/nextcloud/lib/private/DB/Exceptions/DbalException.php", "Line": 70, "Previous": { "Exception": "Doctrine\\DBAL\\Exception\\InvalidFieldNameException", "Message": "An exception occurred while executing a query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created' in 'where clause'", "Code": 1054, "Trace": [{ "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php", "line": 1728, "function": "convert", "class": "Doctrine\\DBAL\\Driver\\API\\MySQL\\ExceptionConverter", "type": "->", "args": [{ "__class__": "Doctrine\\DBAL\\Driver\\PDO\\Exception" }, { "__class__": "Doctrine\\DBAL\\Query" }] }, { "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php", "line": 1667, "function": "handleDriverException", "class": "Doctrine\\DBAL\\Connection", "type": "->", "args": [{ "__class__": "Doctrine\\DBAL\\Driver\\PDO\\Exception" }, { "__class__": "Doctrine\\DBAL\\Query" }] }, { "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php", "line": 1146, "function": "convertExceptionDuringQuery", "class": "Doctrine\\DBAL\\Connection", "type": "->", "args": [{ "__class__": "Doctrine\\DBAL\\Driver\\PDO\\Exception" }, "UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/lib/private/DB/Connection.php", "line": 257, "function": "executeStatement", "class": "Doctrine\\DBAL\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php", "line": 1747, "function": "executeStatement", "class": "OC\\DB\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/lib/private/DB/Connection.php", "line": 236, "function": "executeUpdate", "class": "Doctrine\\DBAL\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/lib/private/DB/ConnectionAdapter.php", "line": 75, "function": "executeUpdate", "class": "OC\\DB\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/apps/music/lib/Migration/TimestampInit.php", "line": 60, "function": "executeUpdate", "class": "OC\\DB\\ConnectionAdapter", "type": "->", "args": ["UPDATE `*PREFIX*music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"]] }, { "file": "/var/www/nextcloud/apps/music/lib/Migration/TimestampInit.php", "line": 51, "function": "setCreated", "class": "OCA\\Music\\Migration\\TimestampInit", "type": "->", "args": ["*PREFIX*music_albums", "2021-08-29 14:42:24"] }, { "file": "/var/www/nextcloud/lib/private/Repair.php", "line": 118, "function": "run", "class": "OCA\\Music\\Migration\\TimestampInit", "type": "->", "args": [{ "__class__": "OC\\Repair" }] }, { "file": "/var/www/nextcloud/lib/private/legacy/OC_App.php", "line": 1062, "function": "run", "class": "OC\\Repair", "type": "->", "args": [] }, { "file": "/var/www/nextcloud/lib/private/Installer.php", "line": 166, "function": "executeRepairSteps", "class": "OC_App", "type": "::", "args": ["music", ["OCA\\Music\\Migration\\BogusTrackRemoval", "OCA\\Music\\Migration\\DiskNumberMigration", "OCA\\Music\\Migration\\TimestampInit"]] }, { "file": "/var/www/nextcloud/apps/settings/lib/Controller/AppSettingsController.php", "line": 448, "function": "installApp", "class": "OC\\Installer", "type": "->", "args": ["music"] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php", "line": 218, "function": "enableApps", "class": "OCA\\Settings\\Controller\\AppSettingsController", "type": "->", "args": [ ["music"], [] ] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php", "line": 127, "function": "executeController", "class": "OC\\AppFramework\\Http\\Dispatcher", "type": "->", "args": [{ "__class__": "OCA\\Settings\\Controller\\AppSettingsController" }, "enableApps"] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/App.php", "line": 157, "function": "dispatch", "class": "OC\\AppFramework\\Http\\Dispatcher", "type": "->", "args": [{ "__class__": "OCA\\Settings\\Controller\\AppSettingsController" }, "enableApps"] }, { "file": "/var/www/nextcloud/lib/private/Route/Router.php", "line": 302, "function": "main", "class": "OC\\AppFramework\\App", "type": "::", "args": ["OCA\\Settings\\Controller\\AppSettingsController", "enableApps", { "__class__": "OC\\AppFramework\\DependencyInjection\\DIContainer" }, { "_route": "settings.AppSettings.enableApps" }] }, { "file": "/var/www/nextcloud/lib/base.php", "line": 993, "function": "match", "class": "OC\\Route\\Router", "type": "->", "args": ["/settings/apps/enable"] }, { "file": "/var/www/nextcloud/index.php", "line": 37, "function": "handleRequest", "class": "OC", "type": "::", "args": [] }], "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php", "Line": 65, "Previous": { "Exception": "Doctrine\\DBAL\\Driver\\PDO\\Exception", "Message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created' in 'where clause'", "Code": 1054, "Trace": [{ "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php", "line": 84, "function": "new", "class": "Doctrine\\DBAL\\Driver\\PDO\\Exception", "type": "::", "args": [{ "errorInfo": ["42S22", 1054, "Unknown column 'created' in 'where clause'"], "__class__": "PDOException" }] }, { "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php", "line": 1138, "function": "execute", "class": "Doctrine\\DBAL\\Driver\\PDO\\Statement", "type": "->", "args": [ ["2021-08-29 14:42:24"] ] }, { "file": "/var/www/nextcloud/lib/private/DB/Connection.php", "line": 257, "function": "executeStatement", "class": "Doctrine\\DBAL\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php", "line": 1747, "function": "executeStatement", "class": "OC\\DB\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/lib/private/DB/Connection.php", "line": 236, "function": "executeUpdate", "class": "Doctrine\\DBAL\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/lib/private/DB/ConnectionAdapter.php", "line": 75, "function": "executeUpdate", "class": "OC\\DB\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/apps/music/lib/Migration/TimestampInit.php", "line": 60, "function": "executeUpdate", "class": "OC\\DB\\ConnectionAdapter", "type": "->", "args": ["UPDATE `*PREFIX*music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"]] }, { "file": "/var/www/nextcloud/apps/music/lib/Migration/TimestampInit.php", "line": 51, "function": "setCreated", "class": "OCA\\Music\\Migration\\TimestampInit", "type": "->", "args": ["*PREFIX*music_albums", "2021-08-29 14:42:24"] }, { "file": "/var/www/nextcloud/lib/private/Repair.php", "line": 118, "function": "run", "class": "OCA\\Music\\Migration\\TimestampInit", "type": "->", "args": [{ "__class__": "OC\\Repair" }] }, { "file": "/var/www/nextcloud/lib/private/legacy/OC_App.php", "line": 1062, "function": "run", "class": "OC\\Repair", "type": "->", "args": [] }, { "file": "/var/www/nextcloud/lib/private/Installer.php", "line": 166, "function": "executeRepairSteps", "class": "OC_App", "type": "::", "args": ["music", ["OCA\\Music\\Migration\\BogusTrackRemoval", "OCA\\Music\\Migration\\DiskNumberMigration", "OCA\\Music\\Migration\\TimestampInit"]] }, { "file": "/var/www/nextcloud/apps/settings/lib/Controller/AppSettingsController.php", "line": 448, "function": "installApp", "class": "OC\\Installer", "type": "->", "args": ["music"] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php", "line": 218, "function": "enableApps", "class": "OCA\\Settings\\Controller\\AppSettingsController", "type": "->", "args": [ ["music"], [] ] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php", "line": 127, "function": "executeController", "class": "OC\\AppFramework\\Http\\Dispatcher", "type": "->", "args": [{ "__class__": "OCA\\Settings\\Controller\\AppSettingsController" }, "enableApps"] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/App.php", "line": 157, "function": "dispatch", "class": "OC\\AppFramework\\Http\\Dispatcher", "type": "->", "args": [{ "__class__": "OCA\\Settings\\Controller\\AppSettingsController" }, "enableApps"] }, { "file": "/var/www/nextcloud/lib/private/Route/Router.php", "line": 302, "function": "main", "class": "OC\\AppFramework\\App", "type": "::", "args": ["OCA\\Settings\\Controller\\AppSettingsController", "enableApps", { "__class__": "OC\\AppFramework\\DependencyInjection\\DIContainer" }, { "_route": "settings.AppSettings.enableApps" }] }, { "file": "/var/www/nextcloud/lib/base.php", "line": 993, "function": "match", "class": "OC\\Route\\Router", "type": "->", "args": ["/settings/apps/enable"] }, { "file": "/var/www/nextcloud/index.php", "line": 37, "function": "handleRequest", "class": "OC", "type": "::", "args": [] }], "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php", "Line": 26, "Previous": { "Exception": "PDOException", "Message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'created' in 'where clause'", "Code": "42S22", "Trace": [{ "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php", "line": 82, "function": "execute", "class": "PDOStatement", "type": "->", "args": [ ["2021-08-29 14:42:24"] ] }, { "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php", "line": 1138, "function": "execute", "class": "Doctrine\\DBAL\\Driver\\PDO\\Statement", "type": "->", "args": [ ["2021-08-29 14:42:24"] ] }, { "file": "/var/www/nextcloud/lib/private/DB/Connection.php", "line": 257, "function": "executeStatement", "class": "Doctrine\\DBAL\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php", "line": 1747, "function": "executeStatement", "class": "OC\\DB\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/lib/private/DB/Connection.php", "line": 236, "function": "executeUpdate", "class": "Doctrine\\DBAL\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/lib/private/DB/ConnectionAdapter.php", "line": 75, "function": "executeUpdate", "class": "OC\\DB\\Connection", "type": "->", "args": ["UPDATE `oc_music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"], [] ] }, { "file": "/var/www/nextcloud/apps/music/lib/Migration/TimestampInit.php", "line": 60, "function": "executeUpdate", "class": "OC\\DB\\ConnectionAdapter", "type": "->", "args": ["UPDATE `*PREFIX*music_albums` SET `created` = ? WHERE `created` IS NULL", ["2021-08-29 14:42:24"]] }, { "file": "/var/www/nextcloud/apps/music/lib/Migration/TimestampInit.php", "line": 51, "function": "setCreated", "class": "OCA\\Music\\Migration\\TimestampInit", "type": "->", "args": ["*PREFIX*music_albums", "2021-08-29 14:42:24"] }, { "file": "/var/www/nextcloud/lib/private/Repair.php", "line": 118, "function": "run", "class": "OCA\\Music\\Migration\\TimestampInit", "type": "->", "args": [{ "__class__": "OC\\Repair" }] }, { "file": "/var/www/nextcloud/lib/private/legacy/OC_App.php", "line": 1062, "function": "run", "class": "OC\\Repair", "type": "->", "args": [] }, { "file": "/var/www/nextcloud/lib/private/Installer.php", "line": 166, "function": "executeRepairSteps", "class": "OC_App", "type": "::", "args": ["music", ["OCA\\Music\\Migration\\BogusTrackRemoval", "OCA\\Music\\Migration\\DiskNumberMigration", "OCA\\Music\\Migration\\TimestampInit"]] }, { "file": "/var/www/nextcloud/apps/settings/lib/Controller/AppSettingsController.php", "line": 448, "function": "installApp", "class": "OC\\Installer", "type": "->", "args": ["music"] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php", "line": 218, "function": "enableApps", "class": "OCA\\Settings\\Controller\\AppSettingsController", "type": "->", "args": [ ["music"], [] ] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php", "line": 127, "function": "executeController", "class": "OC\\AppFramework\\Http\\Dispatcher", "type": "->", "args": [{ "__class__": "OCA\\Settings\\Controller\\AppSettingsController" }, "enableApps"] }, { "file": "/var/www/nextcloud/lib/private/AppFramework/App.php", "line": 157, "function": "dispatch", "class": "OC\\AppFramework\\Http\\Dispatcher", "type": "->", "args": [{ "__class__": "OCA\\Settings\\Controller\\AppSettingsController" }, "enableApps"] }, { "file": "/var/www/nextcloud/lib/private/Route/Router.php", "line": 302, "function": "main", "class": "OC\\AppFramework\\App", "type": "::", "args": ["OCA\\Settings\\Controller\\AppSettingsController", "enableApps", { "__class__": "OC\\AppFramework\\DependencyInjection\\DIContainer" }, { "_route": "settings.AppSettings.enableApps" }] }, { "file": "/var/www/nextcloud/lib/base.php", "line": 993, "function": "match", "class": "OC\\Route\\Router", "type": "->", "args": ["/settings/apps/enable"] }, { "file": "/var/www/nextcloud/index.php", "line": 37, "function": "handleRequest", "class": "OC", "type": "::", "args": [] }], "File": "/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php", "Line": 82 } } }, "CustomMessage": "--" }, "userAgent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:91.0) Gecko/20100101 Firefox/91.0", "version": "21.0.4.1", "id": "612b9cea6414c" }The text was updated successfully, but these errors were encountered: