MySQL: GRANT ALL ON misc.* TO 'fred'@'localhost' IDENTIFIED BY 'zap' is deprecated

[winkler-winsen]

https://github.com/csev/wa4e/blob/cf576876a77c264a88348022ec7c6891ad2bb0ed/assn/autosdb/index.php#L116 https://github.com/csev/wa4e/blob/cf576876a77c264a88348022ec7c6891ad2bb0ed/assn/autosdb/index.php#L117

should be:

CREATE USER 'fred'@'localhost' IDENTIFIED BY 'zap';
GRANT ALL ON misc.* TO 'fred'@'localhost';
CREATE USER 'fred'@'127.0.0.1' IDENTIFIED BY 'zap';
GRANT ALL ON misc.* TO 'fred'@'127.0.0.1';

[srcatto]

The current versions of MAMP & XAMPP are not MySQL 8.0. When this changes the updates will be made, it's already been tested.

[csev]

Stephen - If we make this change - won't it work for both MySQL 5 and 8?

[srcatto]

Hi Chuck,

It looks like it, there is also the issue to update ENGINE=InnoDB CHARACTER SET=utf8mb4; so the default isn't the utf8 alias to utf8mb3. It looks like at some point the alias will change anyway, but don't use it, be explicit instead. https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

Mostly I didn't want to submit these changes when the course was busy, and I still haven't done a 2nd or 3rd LAMP from scratch to verify. I haven't forgotten. There was a note on MAMP that the MAC 6.0 version might have been MySQL 8.0 but that didn't happen. The one big change was switching to PHP 8.0 in XAMPP and MAMP.

[srcatto]

Update, course 2 music db, copied working assignment to a new PC via phpMyAdmin Export (2 years old), into new system running MySQL 8.0.22 and received multiple warnings,

Warning: #1681 Integer display width is deprecated and will be removed in a future release.

That is for all the lines, like account_id INT(11); that were converted from account_id INTEGER by the previous export.

MySQL info, it's a difference after 8.0.17 https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html

Will need to go back and check course examples, it may just be an Export / Import issue and nothing needs changed.

Course 3 autos, 1)fixed phpMyAdmin syntax errors. Code worked correctly but students do post about the red circle X. 2)year is now a reserved word, use backticks for all table, column names.