change_username.sh

[Invader Zim]

So we discovered we had some users with the same username across our servers. Awhile ago we checked the box to check for usernames in the multiserver setup but there already were a number of users that have the same name.

Using change_username.sh in the scripts dir to make changes. It's nice to see the databases are also renamed. However, when the times comes to back them up mysqldump would throw an error:

Got error: 1146: Table 'username_database.tablename' doesn't exist when using LOCK TABLES

which was quite weird because doing

connect username_database;
show tables;

did list 'tablename'. So drop and recreate the database and then manually read in the dump file from the previous backup. Problem solved. However, we have another one which puzzles me on how to fix it. We changed another user, say user0 to user1. Now mysqldump throws:

Got error: 1449: The user specified as a definer ('user0'@'localhost') does not exist when using LOCK TABLES

There is no mention of user0 anywhere in the databases, just user1. Any thoughts, ideas, solutions?

 

[DirectAdmin Support]

Hello,

If you can still produce the error (do it once more to be sure)... after you get the user0 error, try running this SQL query as da_admin:<p class=code>FLUSH PRIVILEGES;</p>which will reset the tables in memory with the new values.

Let us know if that works, and we can update the script to call it.

John

 

[Invader Zim]

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.14 sec)

Update:

Got error: 1449: The user specified as a definer ('user0'@'localhost') does not exist when using LOCK TABLES

So, unfortunately that didn't help.

A bit of more looking revealed that when checking the tables in the database mysql pops up with the following error:

The user specified as a definer ('user0'@'localhost') does not exist

Still can't tell where he gets user0 from, as it is nowhere listed in the database. It's a joomla temporary table so I just recreated it (again):

DROP TABLE IF EXISTS `jos_jf_languages`;
/*!50001 DROP VIEW IF EXISTS `jos_jf_languages`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `jos_jf_languages` (
  `lang_id` tinyint NOT NULL,
  `lang_code` tinyint NOT NULL,
  `title` tinyint NOT NULL,
  `title_native` tinyint NOT NULL,
  `sef` tinyint NOT NULL,
  `description` tinyint NOT NULL,
  `published` tinyint NOT NULL,
  `image` tinyint NOT NULL,
  `image_ext` tinyint NOT NULL,
  `fallback_code` tinyint NOT NULL,
  `params` tinyint NOT NULL,
  `ordering` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

Error has gone now.