[Bug 22128] New: koha-remove fails mysql ERROR 1133 (42000) at line 2: Can't find any matching row in the user table
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 Bug ID: 22128 Summary: koha-remove fails mysql ERROR 1133 (42000) at line 2: Can't find any matching row in the user table Change sponsored?: --- Product: Koha Version: 18.05 Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Command-line Utilities Assignee: koha-bugs@lists.koha-community.org Reporter: Heinrich.Hartl@email.de QA Contact: testopia@bugs.koha-community.org CC: robin@catalyst.net.nz Target Milestone: --- The script fails in in a GRANT command - the user does not exist This probably is due to changed behaviour of mysql 5.7ff From https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-overview: Note If an account named in a GRANT statement does not already exist, GRANT may create it under the conditions described later in the discussion of the NO_AUTO_CREATE_USER SQL mode. It is also possible to use GRANT to specify nonprivilege account characteristics such as whether it uses secure connections and limits on access to server resources. However, use of GRANT to create accounts or define nonprivilege characteristics is deprecated as of MySQL 5.7.6. Instead, perform these tasks using CREATE USER or ALTER USER. In my case mysql hosted a koha 16.11 database upgraded to 18.5 and the only existing user was koha_library@%. There was no user koha_library@localhost. mysql was running in the version (> 5.7)supplied with ubuntu 16.4 (Xenial). My solution to the problem was commenting out two lines in the script. diff /usr/sbin/koha-remove /usr/sbin/koha-remove_ori 71c71 < # GRANT USAGE ON \`koha_$name\`.* TO \`koha_$name\`@\`$mysql_hostname\`; ---
GRANT USAGE ON \`koha_$name\`.* TO \`koha_$name\`@\`$mysql_hostname\`; 73c73 < # DROP USER \`koha_$name\`@\`$mysql_hostname\`;
DROP USER \`koha_$name\`@\`$mysql_hostname\`; hhl@koha-VM-dev:~ >
For a general solution the koha-remove script should be made more resilient and should not use deprecated mysql features. Since I am only very occasionally dealing with mysql and moreover I have no knowledge which user(s) have been used for koha instances in the past it is beyond my skills to define the proper revision of the script. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 Rudolf Byker <rudolfbyker@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |rudolfbyker@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 Rudolf Byker <rudolfbyker@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff Assignee|koha-bugs@lists.koha-commun |rudolfbyker@gmail.com |ity.org | --- Comment #1 from Rudolf Byker <rudolfbyker@gmail.com> --- Created attachment 89528 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=89528&action=edit Use "DROP USER IF EXISTS" instead of "GRANT USAGE" before "DROP USER". MySQL 5.7 is old, and the latest versions fail on "GRANT USAGE" if the user does not exist. "DROP USER IF EXISTS" has been with us since 5.7, so all users should have it by now. This patch fixes the issue. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 Liz Rea <wizzyrea@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #89528|0 |1 is obsolete| | --- Comment #2 from Liz Rea <wizzyrea@gmail.com> --- Created attachment 90081 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=90081&action=edit Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE MySQL 5.7 is old, and the latest versions fail on "GRANT USAGE" if the user does not exist. "DROP USER IF EXISTS" has been with us since 5.7, so all users should have it by now. This patch fixes the issue. To test: Try to koha_remove a site, and if it works with no errors, all good! Note: I reformatted this patch to community guidelines, and did not test it. I took the intent of the patch, and made it so that it would work. The work is Rudolf's and I have attributed it as such. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 lulihua@ibwya.net changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lulihua@ibwya.net -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 --- Comment #3 from lulihua@ibwya.net --- Koha18.11.05.000 on Debian 9.9 and MariaDB10.1.38 has the same issue -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 --- Comment #4 from lulihua@ibwya.net --- $ sudo koha-remove klib Removing Koha instance klib ERROR 1133 (28000) at line 1: Can't find any matching row in the user table -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mtompset@hotmail.com See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=23022 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 --- Comment #5 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 90491 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=90491&action=edit Bug 22128: Removed outdated comment -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 M. Tompsett <mtompset@hotmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #90081|0 |1 is obsolete| | Attachment #90491|0 |1 is obsolete| | --- Comment #6 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 90492 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=90492&action=edit Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE MySQL 5.7 is old, and the latest versions fail on "GRANT USAGE" if the user does not exist. "DROP USER IF EXISTS" has been with us since 5.7, so all users should have it by now. This patch fixes the issue. To test: Try to koha_remove a site, and if it works with no errors, all good! Signed-off-by: Mark Tompsett <mtompset@hotmail.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 --- Comment #7 from M. Tompsett <mtompset@hotmail.com> --- Created attachment 90493 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=90493&action=edit Bug 22128: Removed outdated comment Signed-off-by: Mark Tompsett <mtompset@hotmail.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #90492|0 |1 is obsolete| | Attachment #90493|0 |1 is obsolete| | --- Comment #8 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 91063 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=91063&action=edit Bug 22128: Use DROP USER IF EXISTS instead of GRANT USAGE before DROP USAGE MySQL 5.7 is old, and the latest versions fail on "GRANT USAGE" if the user does not exist. "DROP USER IF EXISTS" has been with us since 5.7, so all users should have it by now. This patch fixes the issue. To test: Try to koha_remove a site, and if it works with no errors, all good! Signed-off-by: Mark Tompsett <mtompset@hotmail.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 --- Comment #9 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 91064 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=91064&action=edit Bug 22128: Removed outdated comment Signed-off-by: Mark Tompsett <mtompset@hotmail.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to master Version(s)| |19.11.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 --- Comment #10 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Nice work! Pushed to master for 19.11.00 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@ptfs-europe | |.com --- Comment #11 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Welcome aboard Rudolf, congratulations on your first submission to be pushed to Koha and thank you. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 Fridolin SOMERS <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m Version(s)|19.11.00 |19.11.00,19.05.03 released in| | Status|Pushed to master |Pushed to stable --- Comment #12 from Fridolin SOMERS <fridolin.somers@biblibre.com> --- Pushed to 19.05.x for 19.05.03 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com --- Comment #13 from Lucas Gass <lucas@bywatersolutions.com> --- backported to 18.11.x for 18.11.09 Thanks Rudolf! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |23945 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23945 [Bug 23945] koha-remove does not support older mysql and mariadb -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 --- Comment #14 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- This was a bad push on my part. We still support Debian Jessie (which comes with Mysql5.5 by default) and this breaks our support for Jessie as such... Stretch is also pinned to MySQL5.5 which makes this worse but you can get MariaDB 10.1.4 which resolves it in Stretch (MariaDB supports IF EXISTS from 10.1.3) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 --- Comment #15 from Rudolf Byker <rudolfbyker@gmail.com> --- Not just your fault. I proposed the fix without checking which distributions Koha is supposed to support. We have a conflict between trying to support the old DBs vs the new DBs, because as I mentioned above, the latest versions of MySQL and MariaDB fail on "GRANT USAGE" if the user does not exist, which is problematic. Maybe we should check the MySQL version, and then run the appropriate code in response to that. It's a simple IF statement. One day, when oldstable has moved on to the next release, the old code and the IF statement can simply be removed. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22128 --- Comment #16 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- In better news.. Jessie drops out of support just after the release of 20.05.. so we won't have to support this for long.. Having said that.. we don't limit people to MariaDB.. so in Stretch they'll still have issues if they want to stick to mysql-server and not myself-server-transitional (which installs mariadb I believe). One for broader discussion there. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org