search

jmac11 / googlecloudsql

Automatically exported from code.google.com/p/googlecloudsql
0 stars 0 forks source link

Updating pricing through console wipes mysql.user table... #90

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?
1. Create a new instance with the per use pricing plan.
2. Set the root password and allow connection from your IP.
3. Log in and add some users with GRANT ...
4. Add some data and run some queries. Connect your applications.
5. Decide you'd be better off with the package pricing plan and update it 
through the console.
6. Watch all hell break loose as the mysql.user table is apparently 
reinitialized and everything you have connected looses it's connection and 
can't reconnect.

What is the name of your database instance?
copper-aloe-235:main

What is the expected output? What do you see instead?
I expect to see connections be maintained or at the very least drop briefly 
then reconnect after the configuration is updated. What I see instead is a 
complete reset of the user table.

Please provide any additional information below.
This is either a very severe bug or if this is expected behavior the console is 
missing a very big "ARE YOU SURE YOUR WANT TO DELETE ALL USERS?" warning before 
allowing you to update the pricing model.

I am an experienced MySQL admin and a new Cloud SQL user. This is NOT a 
confidence builder...

Original issue reported on code.google.com by cavanmor...@softcoil.com on 30 May 2014 at 7:16

GoogleCodeExporter commented 9 years ago 
This is apparently not limited to occuring when the instance priceing scheme is 
updated. I just had it happen again with no interation from me to initiate it. 
Perhaps the automated backup kicked it off?

All users were removed from the mysql.user table at about 2014-06-02 03:06:00 
UTC. This is a serious issue! I discovered it several hours later and again had 
to re-add the root password through the console and then add my application 
users with grant commands.  This is what the user table looked like when I got 
in:
MySQL [(none)]> select User from mysql.user;
+------+
| User |
+------+
| root |
| root |
| root |
|      |
| root |
+------+

You will see that there are no non-root users. Here is what it looked like 
before the table got wiped:
MySQL [(none)]> select User from mysql.user;
+--------------+
| User         |
+--------------+
| apnData      |
| root         |
| smsterServer |
| smsterSite   |
| root         |
| root         |
|              |
| root         |
+--------------+

Original comment by cavanmor...@softcoil.com on 2 Jun 2014 at 6:14

GoogleCodeExporter commented 9 years ago 
It may be relevant that I am using the utf8mb4 character_set_server flag.

Original comment by cavanmor...@softcoil.com on 2 Jun 2014 at 6:17

GoogleCodeExporter commented 9 years ago 
Can you please make sure you run a FLUSH TABLES after you make changes to the 
mysql.user table? What you are observing was probably caused by some data that 
the mysqld did not flush before the process was killed.

Original comment by razvanm on 2 Jun 2014 at 2:34

GoogleCodeExporter commented 9 years ago 
I will run FLUSH PRIVILEGES and FLUSH TABLES immediately just to be sure. 
However, the issue is also affecting the root password set via the google web 
console and one difference between using GRANT and using an INSERT into the 
mysql.user table is that with a GRANT you are not supposed to have to run the 
FLUSH PRIVILEGES command.

With those two points in mind I do not believe this is the solution, or if it 
is a workaround there is still a bug in Cloud Sql that is causing the issue in 
the first place.

Original comment by cavanmor...@softcoil.com on 2 Jun 2014 at 3:38

GoogleCodeExporter commented 9 years ago 
This may be relevant. There was a big read spike on the DB at exactly the time 
the users were lost (3AM UTC), but you can see that the console says no backups 
have been run.

Original comment by cavanmor...@softcoil.com on 2 Jun 2014 at 4:29

Attachments:

GoogleCodeExporter commented 9 years ago 
I just experienced the same issue 2 hours ago (mysql.user basically gone, had 
to reset in console, etc.). Also had a spike in read ops around that time. We 
didn't switch pricing plans nor do we use utf8mb4.

Last change we did to users was a week ago. It seems _very_ strange that MySQL 
wouldn't manage to flush in that time, and I've never seen this happen 
elsewhere, even though I've used MySQL on more or less every project I worked 
on in last 12+ years..

It would be nice if you upgraded the priority a little, at least until you 
figure out what is going on.

Luckily we decided to test with just one of our clients. Our real production DB 
has over 3000 rows in there..

Original comment by msl...@gmail.com on 19 Jun 2014 at 7:27

GoogleCodeExporter commented 9 years ago

Original comment by jmccull...@google.com on 28 Apr 2015 at 7:05

GoogleCodeExporter commented 9 years ago 
I have forwarded this request to the engineering team. We will update this 
issue with any progress updates and a resolution.

Original comment by jmccull...@google.com on 9 May 2015 at 2:24

GoogleCodeExporter commented 9 years ago 
The issue with the mysql.user table being reset after changing the pricing plan 
should be resolved as of June of 2014.

Original comment by mo...@google.com on 9 May 2015 at 3:30