I'm running KMyMoney using a MySQL database on another server on launching I get a reported error ... Cannot open file as requested. Error was: Error in function void MyMoneyStorageSql::createTable(const MyMoneyDbTable&, int) : creating table/index kmmAccountsPayeeIdentifier Driver = QMYSQL, Host = zanshin.fudokai.lan, User = alan, Database = KMyMoney Driver Error: Database Error No -1: Text: Error type 0 Executed: Query error No 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"order" smallint unsigned NOT NULL, identifierId varchar(32) NOT NULL, PRIMARY K' at line 1 QMYSQL: Unable to execute query Error type 2 Reproducible: Always
This bug is also found in KMyMoney 4.8 for Windows (the same error message). The SQL Server I am using is MySQL 5.7.11. Previously it worked perfectly well in KMyMoney 4.7.2.
*** Bug 366132 has been marked as a duplicate of this bug. ***
This bug is caused by mymoneydbdef.cpp, line 221. There I used “order” as column name. This is okay if the identifier is quoted. Unfortunately the way of quoting it is different between the databases (e.g. https://www.postgresql.org/docs/7.3/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS vs https://dev.mysql.com/doc/refman/5.7/en/identifiers.html). I did not know that :( It should be safe to rename the identifier as we do not really use it, yet. Alternatively we could adopt our query creator to this. However, I think this is too much work for a minor benefit. There is another case of this issue in line 162. If someone is working an this bug, please assign it to yourself.
Git commit c0f003134059354e2bc2b19ffa1b3ca7a98cbb19 by Christian Dávid. Committed on 31/07/2016 at 19:31. Pushed by christiand into branch 'master'. Renamed SQL column "order" to "userOrder" in some tables The name "order" is a reserved keyword. It can be used as identifier but only if it is escaped. Unfortunately the escape sequence is database dependent. This patch is untested as another bug prevents me from opening a database. Scince this fix is kind of urgent, I publish it anyway. The port to 4.8 should be straight forward. FIXED-IN: 5.0 M +3 -3 kmymoney/mymoney/storage/mymoneydbdef.cpp M +3 -3 kmymoney/mymoney/storage/mymoneystoragesql.cpp http://commits.kde.org/kmymoney/c0f003134059354e2bc2b19ffa1b3ca7a98cbb19
Git commit 20191b3d1b662cd8e0d5775799ff51a39cd68e47 by Thomas Baumgart. Committed on 14/08/2016 at 10:16. Pushed by tbaumgart into branch '4.8'. Renamed SQL column "order" to "userOrder" in some tables The name "order" is a reserved keyword. It can be used as identifier but only if it is escaped. Unfortunately the escape sequence is database dependent. Added upgrade feature to convert database on open. Accomplished compatability with DB layout version 10 as used in master. This has yet been tested against SQLite but not MySQL. FIXED-IN: 4.8.1 M +1 -1 kmymoney/mymoney/storage/mymoneydatabasemgrtest.cpp M +20 -6 kmymoney/mymoney/storage/mymoneydbdef.cpp M +13 -4 kmymoney/mymoney/storage/mymoneydbdef.h M +47 -7 kmymoney/mymoney/storage/mymoneystoragesql.cpp M +2 -0 kmymoney/mymoney/storage/mymoneystoragesql.h http://commits.kde.org/kmymoney/20191b3d1b662cd8e0d5775799ff51a39cd68e47