Schema dump, PROCESS privilege

[ethanclevenger91]

• Laravel Version: 8.62.0
• PHP Version: 8.0.11
• Database Driver & Version: MySQL 8.0.22

Description:

php artisan schema:dump --prune throws error:

mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
Database schema dumped successfully.
Migrations pruned successfully.

Appears to have run successfully, but it wouldn't be the first time I got a partial backup due to a MySQL error during an operation.

The two possible solutions seem to be:

1. Run mysqldump with the --no-tablespaces option. Simpler option, especially if tablespaces aren't vital to this operation.
2. Add the proper privileges to the MySQL user. This makes it the end user's problem, which is fine, though I'm using Laravel Sail and would probably lobby for those permissions to be granted to the MySQL user it creates by default in the future.

Steps To Reproduce:

1. Set up a Sail environment
2. Create a migration
3. Run php artisan schema:dump --prune to turn your migration into a schema file.

[driesvints]

I think we should include tablespaces. I'll check to see if we can add that privilege to Sail.

[driesvints]

Btw I see you're running php artisan. It doesn't by any chance work if you run sail artisan schema:dump --prune?

[driesvints]

Also see https://laravel.com/docs/8.x/sail#executing-sail-commands

[ethanclevenger91]

@driesvints I did actually use sail artisan, I just figured this issue impacted more than Sail users so gave a more generic description.

[taylorotwell]

@ethanclevenger91 are you able to make a PR to fix this problem?

[ethanclevenger91]

@taylorotwell probably, with a firm pick on the direction you want to go (ignore tablespaces or add permissions)

[driesvints]

Probably add permissions as I don't think that skipping part of the schema is a good choice? It would effectively make the database different from the original.

[ethanclevenger91]

Knowing very little about tablespaces, I'm not convinced they're important in this context, but I'm not an expert in that space. Is it even possible to manipulate tablespaces via Laravel migrations?

[driesvints]

@ethanclevenger91 yeah I think you may be right after all. Maybe we should consider ignoring tablespaces. If you can, feel free to send that to the framework.

[ethanclevenger91]

I'll have a go.

The other thing that occurred to me - by doing this, MySQL is going to break a lot of people's backup systems, right? The framework can't make assumptions about what users have what permissions, so simply running a command most likely to succeed can mitigate some of that.

[ethanclevenger91]

Oh, and maybe move this issue back to the framework, for posterity?

[driesvints]

Keeping this open here as I'm also running into this now.