2

I recently upgraded the MySQL version of my rails application from 5.6 and 5.7 and I am facing this issue while seeding my database.

When I run the seeding, it fails/aborts with the following message.

MysqlWarning: Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.

I have NO_AUTO_CREATE_USER mode enabled in my database but it is still failing.

enter image description here

Can someone please tell me what can I do to fix this issue? Should I simply remove this mode from my database or is there another way to fix this?

Note: I removed the ONLY_FULL_GROUP_BY mode from the database earlier.

Dev V
  • 85
  • 1
  • 10

1 Answers1

4

You shouldn't remove NO_AUTO_CREATE_USER. This is a good feature, and in MySQL 8.0 it's enforced behavior.

The reason it is important is for security. MySQL historically allowed you to create a new user implicitly, by granting that user privileges. The problem was that people did the grant without assigning a password:

GRANT ALL PRIVILEGES to 'newuser'@'%';

When a GRANT automatically creates a user, this would silently create the new user with no password. That's a big security no-no.

By requiring new users to be created with CREATE USER before they can be granted privileges, it ensures that users will be created deliberately with a password (or other authentication like a TLS certificate).

To fix this, you should change your Rails seed code to execute CREATE USER before granting privileges. Or else make it a manual step to create the MySQL user before running your Rails seed code.

P.S.: You shouldn't disable ONLY_FULL_GROUP_BY either. It's important to prevent invalid queries. See my explanation here: Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828