Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2014
    Posts
    224
    Points
    4,378
    Level
    27

    (Mass) Import fails with DB error

    Hi all,

    can anyone shed light on this error:
    Failure
    Homedir: /home/domain, FTP account was imported : account, Database was imported : wp123, DB Error: unknown error GRANT SUPER ON *.* TO `import`@`%` [nativecode=1133 ** Can't find any matching row in the user table], Reverting Backup Directory : domain.com
    this happens when I try to import an account from another server (same IW version). It happens with several accounts, so it must be connected to the server.

    Cheers,

    Michael

  2. #2
    Join Date
    Apr 2012
    Posts
    2,186
    Points
    24,547
    Level
    68
    Hi Michael

    My instinct is the database is out of alignment as it were.

    Sorry, I'm trying to think of the correct term for it, but I think the schema is not updated

    There is a MySQL command which needs to be run, but I'm not back until tommorow, so I'll post them if alright

    I could be entirely wrong though, so apologies in advance as I'm thinking if you mean the ftp passwords are wrong

    Many thanks and have a lovely night

    John

  3. #3
    Join Date
    Apr 2012
    Posts
    2,186
    Points
    24,547
    Level
    68
    Hi Michael


    Sorry, I could be entirely wrong, but running this command I do not believe would cause any harm, and could possible correct your issue.


    Also, if you look at logs, mysql, are there any additional errors shown


    If you do not know your Mysql password, you can reset it from nodeworx, system, mysql - towards the very bottom, reset mysql password


    I hope that helps a little


    Many thanks


    John


    mysql_upgrade -u root -p

  4. The Following User Says Thank You to d2d4j For This Useful Post:


  5. #4
    Join Date
    Jun 2014
    Posts
    224
    Points
    4,378
    Level
    27
    Hi John,

    there was indeed an error "[ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it" in the logfile, and mysql_upgrade seems to have fixed that.

    But unfortunately the import error stays the same :-(

    Some more information:

    Trying to import from server A to server B.
    Server a is running mySQL 5.5.54 and Centos 6.9
    Server B is running 10.1.23-MariaDB and Centos 7.3 (this is my testbed for new stuff. I wanna see, if these things are ready for production)

    Cheers,
    Michael

  6. #5
    Join Date
    Jun 2014
    Posts
    224
    Points
    4,378
    Level
    27
    I tried 2 other servers running Centos 6.9 and MySQL 5.5.54, let's call them server C and server D

    Server C didn't import with "Failure-Invalid Password" wtf? I always copy-paste passwords and it listed me all accounts from server A.
    Server D did import just fine.

    So I guess this might be relatd to MySQL and MariaDB?

  7. #6
    Join Date
    Apr 2012
    Posts
    2,186
    Points
    24,547
    Level
    68
    Hi Michael
    Stranger and stranger
    I had a quick google and read this from stackoverflow
    quote
    I encountered this error using MySQL in a different context (not within phpMyAdmin). GRANT and SET PASSWORD commands failed on a particular existing user, who was listed in the mysql.user table. In my case, it was fixed by running
    FLUSH PRIVILEGES;
    The documentation for this command says

    Reloads the privileges from the grant tables in the mysql database.
    The server caches information in memory as a result of GRANT and CREATE USER statements. This memory is not released by the corresponding REVOKE and DROP USER statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.
    Apparently the user table cache had reached an inconsistent state, causing this weird error message. More information is available here.

    Interestingly, was there any error shown when server C did not import (i'm thinking of hash thinking here from my.cnf for old hash v4 to v5)
    Many thanks
    John

  8. #7
    Join Date
    Jun 2014
    Posts
    224
    Points
    4,378
    Level
    27
    I can make it even stranger

    I set up corresponding testaccounts on each server, testaccount A, testaccount B, testaccount C and testaccount D. Then I made an all-out-crossover-import-mass-orgy

    Server A imported testacount C but not D
    Server B imports testaccount A, C and D
    Server C imports testaccount D but not A
    Server D imports testaccount A and C

    I know I am totally confusing you by now, haha. But there's two things I am seeing:

    1. There is an undefined problem where the import doesn't start at all with the error message "Failure-Invalid Password" - Need to find out, what causes this (EDIT: Solution can be found in post #11 of this thread )
    2. The original problem is NOT related to the server but to the accounts!

    The two accounts I wanted to import from Server A to B in my first post have been imported from server D to A a year ago or so. This might be one thing to consider. Or it is some database related issue as you are mentioning in your last post, John.

    Too add insult to injury I am now seeing imports I have done on server A in the Mass Account Transfer Logs on server C - I have never imported those accounts there?!?

    I guess I really have to open a ticket as I am either not seeing the wood for the trees or I have some other underlying issue I can't yet make out.
    Last edited by mdeinhardt; 05-24-2017 at 05:19 PM.

  9. The Following User Says Thank You to mdeinhardt For This Useful Post:


  10. #8
    Join Date
    Apr 2012
    Posts
    2,186
    Points
    24,547
    Level
    68
    Hi Michael

    Haha yes totally confused now sorry ooxx

    That's twice I've been confused today, your post and an odd ball question from a client over a china domain - arrrh I do not read or speak Chinese haha

    My gut feeling is the hash password is stopping it, which from fading memory is about a year or so ago, when IW changed from v4 to v5 hashing for MySQL (remember in my.cnf old password = 1 or 0)

    I would open a support ticket as it is connected with mass import failure on Iw to Iw

    I think also the wrong password failure is just that, wrong password but this maybe hashing giving the wrong password or password not been accepted due to wrong hash, if you see what I mean

    If you could update your post once Iw have resolved I'd appreciate it

    Many thanks

    John

  11. The Following User Says Thank You to d2d4j For This Useful Post:


  12. #9
    Join Date
    Jun 2014
    Posts
    224
    Points
    4,378
    Level
    27
    I have opened a ticket regarding the second issue "Import fails with "Failure-Invalid Password".

    So back to the original issue:

    My cross-server-mass-import tests have not been detailed enough, because I didn't create any databases in my testaccounts and so the imports went fine.

    I have now created another testaccount on server A incld. a wordpress installation and thus a database.
    And Server B fails to import that account with exactly the same error.
    Server D imports the same account just fine.

    So we're back to my assumption that this might be related to MySQL 5.5.54 vs. MariaDB 10.1.23....

  13. #10
    Join Date
    Jun 2014
    Posts
    224
    Points
    4,378
    Level
    27
    I just checked and all our servers have old-passwords=1 - except the new Server B with MariaDB, that doesn't have hat line at all in my.cnf (I set up Server B in December '16 and followed Brent Dacus' MariaDB 10 guide from here http://forums.interworx.com/threads/...riaDB-10-Guide )

    Not sure how this affects the password hashing and what to do now, but I added your hunch to the ticket and will see what IW will have to say about this.

    Is anybody else using MariaDB and attempted (mass) imports of older MySQL accounts already?

  14. #11
    Join Date
    Jun 2014
    Posts
    224
    Points
    4,378
    Level
    27
    Update on the "Failure-Invalid Password"-Issue:

    IW, this time Nathan, helped my quickly (those guys ARE awesome). It turns out the "Failure-Invalid Password" occurs because during mass transfer InterWorx expects the be asked for the root password of the source server. But in my case key based authentication was configured (not sure yet, when, how, why - I asked and will update here).
    This is a known issue, but for now disabling this authentication between the servers and requiring a root password resolves the issue. In /root/.ssh/authorized_keys remove the entry for the respective server. A password is then required to ssh as the root user to that server.

    Back to my DB-related import problem.... ;-)

    Cheers,
    Michael

    P.S. 约翰,你不会说中国话?

  15. The Following User Says Thank You to mdeinhardt For This Useful Post:


  16. #12
    Join Date
    Jun 2014
    Posts
    224
    Points
    4,378
    Level
    27
    Looking closer at the error message I think I found the real cause, but I have no clue what to do about it.

    MariaDB supports several different modes which allow you to tune it to suit your needs. The most important ways for doing this are using SQL_MODE, a string with different options separated by commas. (see https://mariadb.com/kb/en/mariadb/sql-mode/ )

    One of those is this:

    NO_AUTO_CREATE_USER - Don't automatically create users with GRANT. Produce a 1133 error: "Can't find any matching row in the user table". Default since MariaDB 10.1.7.

    Runnning SELECT @@SQL_MODE LIKE '%NO_AUTO_CREATE_USER%'; in SQL I see, that this is enabled.

    So now I know why this happens, but not how to fix it. Simply disabling it?

    Any DBAs here who can chime in?

  17. The Following User Says Thank You to mdeinhardt For This Useful Post:


  18. #13
    Join Date
    Apr 2012
    Posts
    2,186
    Points
    24,547
    Level
    68
    Hi Michael

    Kudos to IW and you

    I guess I was on wrong tack sorry

    Have you tried allowing auto create user grant, to make sure it imports as expected

    If it does, I guess you have 3 choices - leave as is, change grant user every time or disable no auto grant

    Iw may perhaps be able to code for disable no auto grant on the import script, but if this is only for mariadb v10, then if this was scripted, it may cause issues on those not using Maria db

    I think I need to think a little more about this, but I'm leaning towards disable no auto grant, as users with access to a db or access to Mariadb cli, could only use grant and each grant should be only for per db it's assigned to

    Please could I ask what your ps was, it looks Chinese on tapatalk haha

    Many thanks

    John

  19. #14
    Join Date
    Apr 2012
    Posts
    2,186
    Points
    24,547
    Level
    68
    Hi Michael

    I've been reading quickly (never good sorry), but an extract below from mariadb, shows the correct use for create instead of grant when mariadb is set as you have it set

    I would probably disable that option as grant and create look to do the same, but more reading is probably needed to be sure

    Many thanks

    John

    If the NO_AUTO_CREATE_USER SQL_MODE is set, users can only be created with a CREATE USER statement. In this case GRANT will produce an error when the specified user does not exist.

  20. #15
    Join Date
    Jun 2014
    Posts
    224
    Points
    4,378
    Level
    27
    I went ahead and disabled that option and now mass imports work just fine!

    This is what I did:

    Open phpMyAdmin from Nodeworx (open "localhost")
    Go to the SQL tab and enter
    SELECT REPLACE(@@SQL_MODE, ',', '\n');
    This will give you a list of all SQL_Mode options set, currently that's only NO_AUTO_CREATE_USER and NO_ENGINE_SUBSTITUTION. Since I don't want the former, but want to keep the latter I added this line to /etc/my.cnf
    [mysqld]
    #some stuff thats already there
    sql_mode = "NO_ENGINE_SUBSTITUTION"
    and restarted MySQL. Voila, it works.

    Hope that helps others. Thanks a million to John (你是男人!) and to IW-Nathan!

  21. The Following User Says Thank You to mdeinhardt For This Useful Post:


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •