(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

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

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

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 :frowning:

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

I tried 2 other servers running Centos 6.9 and MySQL 5.5.54, let’s call them server C and server D :smiley:

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

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

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

I can make it even stranger :smiley:

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 :wink:

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.

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

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…

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/8489-MariaDB-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?

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… :wink:

Cheers,
Michael

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

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?

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

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.

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, ‘,’, ’
');

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!

Hi Michael

Haha sorry, no speak the Chinese

Also, a lovely end to the week, we went to the coast yesterday, a days holiday, and mid afternoon, can you believe all servers went off line, I was on the beach, but the issue was the datacentre, a PoP issue (bgp) in London. Soon resolved but I’m glad this week is over

Many thanks

John

Sounds like a week to go down in the annals. And with a nice ending.
Oh, and the translation: You are the man! Just a Chinese thank you from me :smiley: