MariaDB Server - Not enough rights to even import old backup

Server provisioning and management
#1

Hi there,

i created a mariadb server via cleavr.
i created the user during creation of the database. (weirdly i can not specify the host afterwords anymore when i create a new user.

but i want to import my old database and i get this error. i can not change anything and during the creation i did not get any root password with higher right.

Could you please help?

[HY000][1419] You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

#2

Are you importing via a db client such as TablePlus or some other method?

#3

I tried it via command line ‚ mysql‘. And via DataGrip from Jetbrains. Both methods failed.

#4

I see!

You can try running this command:

mysql -u USERNAME -p
set global log_bin_trust_function_creators=1;

More details about what is happening here can be found on the mysql docs if interested - MySQL :: MySQL 8.0 Reference Manual :: 25.7 Stored Program Binary Logging (about 2/3rds down the page)

#5

Hi @amiedema,

when i Try using this, i get the following error message:

MariaDB [(none)]> set global log_bin_trust_function_creators=1;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
#6

Hello @Houbsi,

One quick question: can you check the dump file to see if DEFINER is specified in the dump or not?

#7

/!50003 CREATE/ /!50017 DEFINER=database_name@%/ /*!50003 TRIGGER order_cash_rounding_insert BEFORE INSERT ON order

#8

Hello @Houbsi,

I found this in stackoverflow which might be helpful to you.

#9

Please do let us know whether that works for you or not.

#10

While attempting to migrate a DB from my old server to cleavr using the recommended TablePlus App. I stumbled upon a bug on the services / firewall page. The firewall says Port 3306 MySQL open by default after installing MariaDB:

Screenshot 2022-03-10 at 21.40.39
Screenshot 2022-03-10 at 21.40.392534×172 12 KB

But this port is not open! After getting frustrated by the connection errors, I checked it with netcat and got a timeout on port 3306:

$ nc -z -v {SERVER_IP} 3306

The other Ports (22, 80, 443) were accessible.

However, deleting the firewall rule and manually adding one for Port 3306 solved the problem. Annoying is that the problem occurred on all servers I have. Custom ones and same with Oracle Cloud ones.

#11

This looks like a bug. I’ll confirm and will add a ticket accordingly.

Thank you @Jeazyee for bringing it up!

1 Like