phpmyadmin error 1064 (42000) during installation

Question

I'm quite confused about an error while installing phpmyadmin on a Debian server with MySQL already installed and configured. I've already tried to purge the latest phpmyadmin failed installations, but I'm not being able to installing it.

I've also already searched for common questions as trying to fix phpmyadmin files as https://serverfault.com/questions/341116/setting-up-phpmyadmin-got-a-mysql-syntax-error but every timestamp call is correct without values ( timestamp(value) seems to be wrong).

If anyone could help me I'd be so grateful!

The error says:

An error occurred while installing the database: 

 │                                                                             
 │ mysql said: ERROR 1064 (42000) at line 1: You have an error in your SQL     
 │ syntax; check the manual that corresponds to your MySQL server version      
 │ for the right syntax to use near 'IDENTIFIED BY                             
 │ 'hereismypassword'' at line 1 . Your options are:               
 │  * abort - Causes the operation to fail; you will need to downgrade,        
 │    reinstall, reconfigure this package, or otherwise manually intervene     
 │    to continue using it. This will usually also impact your ability to      
 │    install other packages until the installation failure is resolved.       
 │  * retry - Prompts once more with all the configuration questions           
 │    (including ones you may have missed due to the debconf priority          
 │    setting) and makes another attempt at performing the operation.          
 │  * retry (skip questions) - Immediately attempts the operation again,       
 │                                                                             
 │                                  <Ok>                               

Answer 1

i really struggled with this issue while setting up a server on Digital Ocean. I needed a server to Host a Laravel application. Basically i had to setup my server by installing Nginx using this Tutorial, then i installed MYSQL Version 8.0.22 using this Tutorial (basically the latest available version - Which usually leads to this error), then i installed PHP7.4 using this Tutorial of which also required me to check the DigitalOcean article to configure the php processor for Nginx using the same Tutorial i used to install Nginx.

At this point i wanted to install phpMyAdmin, and it failed with the same error, i read every article i could find on Digital Ocean, Stackoverflow and other forums and no solution worked. I found out i could download phpMyAdmin version 5.0.4 or greater (Basically the latest version) from the official website since it would be more compatible with the MYSQL Version 8.0.22. I followed this Tutorial exactly to install phpMyAdmin manually instead of automatically and everything worked perfectly! I was able to open the phpMyAdmin on the browser and even able to login using root and password. I created another user as indicated on the tutorial and was also able the login using that new user.

Really hope this helps someone having the same issue. In conclusion use this article to install phpMyAdmin manually: https://www.digitalocean.com/community/questions/how-to-install-manually-phpmyadmin-on-ubuntu

Answer 2

if you are using or upgrading to MySQL 8, the default Authentication Plugin is caching_sha2_password

Edit /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following line under [mysqld]

default-authentication-plugin=mysql_native_password

Then restart mysql service and connect to your MySQL

service mysql restart 

mysql -u root -p

Then execute the following commands and replace 'password' with 'your root password'

ALTER USER user IDENTIFIED WITH caching_sha2_password BY 'password';

FLUSH PRIVILEGES;

Source dev.mysql.com

Answer 3

Ok, I solved my problem by doing the following steps:

1. Removing completely phpmyadmin and mysql:

   sudo remove phpmyadmin + sudo remove mysql

   sudo purge phpmyadmin + sudo purge mysql)

2. Upgrading the dist: sudo apt-get dist-upgrade

3. Reinstalling everything (phpmyadmin + mysql)

4. Later I had other kind of issues related about users and passwords in phpmyadmin, but I've solved them with that query:

mysql -u root mysql

UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root';

FLUSH PRIVILEGES;

exit;

Source: Can't set root MySQL password to null