SUPER/Administrative privileges: no users of my database have them [Error Code 1227: Access denied]

1
votes

My working group has a (very) small database that lives on the Google Cloud SQL platform. We are a group of ~8 ecologists new to SQL database management, and new to the Google Cloud platform. We are working with MySQL Workbench.

We have been creating views in SQL to summarize & aggregate our raw tables. Sometimes (often) MySQL yells at us and says:

Error Code: 1055. Expression #7 of SELECT list is not in GROUP BY clause...

We can futz with our views, adding all the group by variables in til the cows come home, but really it would be nice to just re-set that global "group by" variable, ala

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

But at this point, MySQL gets even grouchier and says

Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation

So then I thought, no problem! I created this database, can't I just add myself as an administrator?

GRANT SUPER ON ourdb.* TO myusername@'localhost' IDENTIFIED BY 'password';

Oh, no, not so simple it turns out! ACCESS DENIED!

I tried logging in as root as well. ACCESS DENIED FOR ROOT!

AH! I think I have done something very bad, because NO ONE on our database has DBA privileges. I see this when I navigate in MySQL workbench to "Users and Privileges."

What have I done, and can I un-do it??

A million thanks to the SQL-ers of the world.

1
mysqlmysql-workbenchgoogle-cloud-sql

1 Answers

3
votes

No worries my friend!

You probably (as far as I can tell) have done nothing bad!

According to Google’s Cloud SQL documentation, the root user has all privileges but SUPER and FILE. It’s a characteristic of the Google Cloud SQL.

But rest assured! You have three other ways of easily changing that global "group by" variable that has been slowing your progress. There’s a neat explanation on this Configuring database flags how-to guide.

I hope this helps you!