After changing the password, MySQL does not work.

Problems with the Windows version of XAMPP, questions, comments, and anything related.

After changing the password, MySQL does not work.

Postby luffy1025 » 10. July 2019 11:47

Operating System: Windows 10 64bit
Xampp Version: 7.3.7

Summary:
1. Installing xampp ->
2. Changing passwords for accounts in phpmyadmin ->
3. Enter password in 'config.inc.php' below

$ cfg ['Servers'] [$ i] ['user'] = 'root';
$ cfg ['Servers'] [$ i] ['password'] = '';

$ cfg ['Servers'] [$ i] ['controluser'] = 'pma';
$ cfg ['Servers'] [$ i] ['controlpass'] = '';

4. And restart mysql, xampp mysql will not run.

Video: https://youtu.be/17JfayWdKTo


Error:
[mysql] Status change detected: running
[mysql] Status change detected: stopped
[mysql] Error: MySQL shutdown unexpectedly.
[mysql] This may be due to a blocked port, missing dependencies,
[mysql] improper privileges, a crash, or a shutdown by another method.
[mysql] Press the Logs button to view error logs and check
[mysql] the Windows Event Viewer for more clues
[mysql] If you need more help, copy and post this
[mysql] entire log window on the forums

Log Error:
[Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
[Note] InnoDB: Uses event mutexes
[Note] InnoDB: Compressed tables use zlib 1.2.11
[Note] InnoDB: Number of pools: 1
[Note] InnoDB: Using SSE2 crc32 instructions
[Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: Starting crash recovery from checkpoint LSN=1796554
[Note] InnoDB: 128 out of 128 rollback segments are active.
[Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
[Note] InnoDB: Creating shared tablespace for temporary tables
[Note] InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
[Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is now 12 MB.
[Note] InnoDB: Waiting for purge to start
[Note] InnoDB: 10.3.16 started; log sequence number 1796563; transaction id 201
[Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
[Note] InnoDB: Buffer pool(s) load completed at 190710 19:37:34
[Note] Plugin 'FEEDBACK' is disabled.
[Note] Server socket created on IP: '::'.
luffy1025
 
Posts: 8
Joined: 10. July 2019 11:16
XAMPP version: 7.3.7
Operating System: windows

Re: After changing the password, MySQL does not work.

Postby luffy1025 » 10. July 2019 18:23

If any more detail need tell me i will post here. thank you for reading my article.
luffy1025
 
Posts: 8
Joined: 10. July 2019 11:16
XAMPP version: 7.3.7
Operating System: windows

Re: After changing the password, MySQL does not work.

Postby Altrea » 10. July 2019 18:26

I can reproduce the issue.
It seams that the current version of phpMyAdmin (4.9.0.1) can corrupt the database tables o Maria 10.3.X
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: After changing the password, MySQL does not work.

Postby luffy1025 » 11. July 2019 07:28

Altrea wrote:I can reproduce the issue.
It seams that the current version of phpMyAdmin (4.9.0.1) can corrupt the database tables o Maria 10.3.X

Can you fix this in the next update?
luffy1025
 
Posts: 8
Joined: 10. July 2019 11:16
XAMPP version: 7.3.7
Operating System: windows

Re: After changing the password, MySQL does not work.

Postby Altrea » 11. July 2019 07:40

I have reported this issue to the XAMPP developers.
You can use an older version of XAMPP like XAMPP 7.3.5 which does not seem to be affected by this bug.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: After changing the password, MySQL does not work.

Postby luffy1025 » 11. July 2019 09:40

Altrea wrote:I have reported this issue to the XAMPP developers.
You can use an older version of XAMPP like XAMPP 7.3.5 which does not seem to be affected by this bug.

thanks! :)
luffy1025
 
Posts: 8
Joined: 10. July 2019 11:16
XAMPP version: 7.3.7
Operating System: windows

Re: After changing the password, MySQL does not work.

Postby gonzalo » 12. July 2019 10:33

Hi,

Thanks for reporting this issue. We will take a look into it and post here any update.

Regards,
Gonzalo
gonzalo
 
Posts: 42
Joined: 12. March 2019 08:52
XAMPP version: any
Operating System: any

Re: After changing the password, MySQL does not work.

Postby luffy1025 » 14. July 2019 08:19

gonzalo wrote:Hi,

Thanks for reporting this issue. We will take a look into it and post here any update.

Regards,
Gonzalo

thanks!
luffy1025
 
Posts: 8
Joined: 10. July 2019 11:16
XAMPP version: 7.3.7
Operating System: windows

Re: After changing the password, MySQL does not work.

Postby jotamartos » 16. July 2019 16:06

Hi,

I just installed XAMPP 7.3.7 in my Windows machine and worked on reproducing the issue you reported. I changed the password for all the users as you did in the video and modified the phpMyAdmin's configuration after that. We checked that we had access to the database without problems and restarted the database then.

After that, I restarted the database and we didn't receive any warning or error message during that process. phpMyAdmin returned a warning message saying that the 'user' table was corrupted but we simply recovered the table using the phpMyAdmin option and everything worked properly after that.

Could you please ensure that no other process is using the database's port when you try to start the XAMPP's MariaDB? If not, can you try installing the latest version again and changing the password in the mysql.user table directly? You will only need to edit the user's information and change the password using the PASSWORD function.

Thanks
jotamartos
 
Posts: 2
Joined: 04. May 2017 17:51
XAMPP version: 7.0.12
Operating System: OS X

Re: After changing the password, MySQL does not work.

Postby Altrea » 16. July 2019 19:32

XAMPP version 7.3.7

- setted root@localhost password directly in the mysql.user table using the password function
- setting the password in phpmyadmins config.inc.php file
- restarting mariadb and trying to access the phpmyadmin results in
Code: Select all
mysqli_real_connect(): (HY000/1130): Host 'localhost' is not allowed to connect to this MariaDB server

- starting mysqld in the console with --skip-grant-tables
- repairing mysql.user table results in
Code: Select all
mysql.user
repair
info
Wrong bytesec:  50- 48- 49 at 0; Skipped
mysql.user
repair
info
Found block that points outside data file at 164
mysql.user
repair
info
Found block that points outside data file at 168
mysql.user
repair
info
Found block that points outside data file at 172
mysql.user
repair
info
Found block that points outside data file at 176
mysql.user
repair
info
Found block that points outside data file at 180
mysql.user
repair
warning
Number of rows changed from 5 to 2
mysql.user
repair
status
OK

- After that repair several entries in the mysql.user table are lost.
Remaining are only the following
Code: Select all
root@::1
pma@localhost


repairing the mysql.user table results in every test case in lost user entries.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: After changing the password, MySQL does not work.

Postby gonzalo » 19. July 2019 15:30

Hi,

TL;DR

We think this issue is related to how are we stopping the MySQL process using the control panel. It seems that the stop function from time to time kills the process leaving the database in an inconsistent state but others it works fine. We also haven’t been able to reproduce it with previous MariaDB 10.1.x, so we think it may be related to the (kill process + MariaDB 10.3) combination.

We didn’t have issues manually stopping the MySQL process using the command below

Code: Select all
“C:\xampp73\mysql\bin\mysqladmin.exe” -u root -pmyPassword shutdown


Or when MySQL is installed as a Windows service using the XAMPP control panel.

We will investigate a better way of stopping the MySQL process using the control panel to avoid this issue.

Detailed information

We have been investigating this issue during the last days and we think the issue is related to how the MySQL (MariaDB) process is stopped using the control panel. We reproduced @luffy1025 issue on Windows 10 by setting the password using the phpMyAdmin interface and the stopping and starting the process using the control panel as shown in the video linked on his first post.

We also “partially” reproduced @Altrea steps on several versions of Windows server on AWS (we tried 2012, 2016 and 2019), and although we end up with the MariaDB `mysql.user` table in a corrupted state, we didn’t had problems to connect with phpMyAdmin after modifying the password. We see the next error message in the console after stopping the MySQL process with the `taskkill.exe` command and starting it from the command line:

Code: Select all
“C:\xampp73\mysql\bin\mysqld.exe” -.-defaults-file=“C:\xampp73\mysql\bin\my.ini” -.-verbose -.-console
(…)
2019-07-19 14:01:37 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-07-19 14:01:37 0 [Note] Server socket created on IP: '::'.
2019-07-19 14:01:37 0 [Note] InnoDB: Buffer pool(s) load completed at 190719 14:01:37
2019-07-19 14:01:37 0 [ERROR] mysqld.exe: Table '.\mysql\user' is marked as crashed and should be repaired
2019-07-19 14:01:37 0 [Warning] Checking table:   '.\mysql\user'
2019-07-19 14:01:37 0 [ERROR] mysql.user: 1 client is using or hasn't closed the table properly
2019-07-19 14:01:37 0 [Note] Reading of all Master_info entries succeeded
2019-07-19 14:01:37 0 [Note] Added new Master_info '' to hash table
2019-07-19 14:01:37 0 [Note] bin\mysqld.exe: ready for connections.
Version: '10.3.16-MariaDB'  socket: ''  port: 3306  mariadb.org binary distribution


If after this “broken” start we stop the process from the command line using the shutdown command below (note the new password is required to run it), and we start MySQL again using the same command above, the error messages disappear and we can use phpMyAdmin without further issues.

Code: Select all
“C:\xampp73\mysql\bin\mysqladmin.exe” -u root -pmyPassword shutdown


We can also reproduce this behavior if we set the password using the MySQL command-line client with the SQL query shown below. Because of that, we discarded phpMyAdmin as the issue here

Code: Select all
SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(‘myPassword’);


We didn’t reproduce the issue after stopping the MySQL process with the shutdown command above or if MySQL is installed as a service on the system.

Can you try using any of those options?

Hope it helps
gonzalo
 
Posts: 42
Joined: 12. March 2019 08:52
XAMPP version: any
Operating System: any

Re: After changing the password, MySQL does not work.

Postby gonzalo » 19. July 2019 17:45

Hi,

TL;DR

We think the issue is related to how are we stopping the MySQL process using the control panel. It seems that the stop function from time to time kills the process leaving the database in an inconsistent state but others it works fine. We also haven’t been able to reproduce it with previous MariaDB 10.1.x, so we think it may be related to the (kill process + MariaDB 10.3) combination.

We didn’t have issues manually stopping the MySQL process using the command below

Code: Select all
“C:\xampp73\mysql\bin\mysqladmin.exe” -u root -pmyPassword shutdown


Or when MySQL is installed as a Windows service using the XAMPP control panel.


Detailed information

We have been investigating this issue during the last days and we think the issue is related to how the MySQL (MariaDB) process is stopped using the control panel. We reproduced @luffy1025 issue on Windows 10 by setting the password using the phpMyAdmin interface and the stopping and starting the process using the control panel as shown in the video linked on his first post.

We also “partially” reproduced @Altrea steps on several versions of Windows server on AWS (we tried 2012, 2016 and 2019), and although we end up with the MariaDB `mysql.user` table in a corrupted state, we didn’t had problems to connect with phpMyAdmin after modifying the password. We see the next error message in the console after stopping the MySQL process with the `taskkill.exe` command and starting it from the command line:

Code: Select all
“C:\xampp73\mysql\bin\mysqld.exe” --defaults-file=“C:\xampp73\mysql\bin\my.ini” --verbose --console
(…)
2019-07-19 14:01:37 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-07-19 14:01:37 0 [Note] Server socket created on IP: '::'.
2019-07-19 14:01:37 0 [Note] InnoDB: Buffer pool(s) load completed at 190719 14:01:37
2019-07-19 14:01:37 0 [ERROR] mysqld.exe: Table '.\mysql\user' is marked as crashed and should be repaired
2019-07-19 14:01:37 0 [Warning] Checking table:   '.\mysql\user'
2019-07-19 14:01:37 0 [ERROR] mysql.user: 1 client is using or hasn't closed the table properly
2019-07-19 14:01:37 0 [Note] Reading of all Master_info entries succeeded
2019-07-19 14:01:37 0 [Note] Added new Master_info '' to hash table
2019-07-19 14:01:37 0 [Note] bin\mysqld.exe: ready for connections.
Version: '10.3.16-MariaDB'  socket: ''  port: 3306  mariadb.org binary distribution


If, after this “broken” start we stop the process from the command line using the shutdown command below (note the new password is required to run it), and we start MySQL again using the same command above, the error messages disappear and we can use phpMyAdmin without further issues.

Code: Select all
“C:\xampp73\mysql\bin\mysqladmin.exe” -u root -pmyPassword shutdown


We can also reproduce this behavior if we set the password using the MySQL command-line client with the SQL query shown below. Because of that, we discarded phpMyAdmin as the issue here

Code: Select all
SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(‘myPassword’);


We didn’t reproduce the issue after stopping the MySQL process with the shutdown command above or if MySQL is installed as a service on the system.

Can you try using any of those options?

Hope it helps
gonzalo
 
Posts: 42
Joined: 12. March 2019 08:52
XAMPP version: any
Operating System: any

Re: After changing the password, MySQL does not work.

Postby Altrea » 19. July 2019 18:11

I will test this out.

Please keep in mind that authentication beginning with MariaDB 10.4 change many things, especially relating to root password requirement.
https://mariadb.com/kb/en/library/authe ... riadb-104/

Maybe it is easier to implement MariaDB 10.4 and build a new shutdown process around this version in the control panel? Maybe connecting the user via named pipe instead of password?
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: After changing the password, MySQL does not work.

Postby gonzalo » 23. July 2019 08:10

Hi @Altrea,

Thanks for the info about MariaDB 10.4 and the named pipe protocol. We will evaluate it.
gonzalo
 
Posts: 42
Joined: 12. March 2019 08:52
XAMPP version: any
Operating System: any

Re: After changing the password, MySQL does not work.

Postby badLoser » 26. July 2019 09:05

After password change:

[ERROR] mysqld: Table '.\mysql\user' is marked as crashed and should be repaired
[ERROR] mysqld: Index for table '.\mysql\user' is corrupt; try to repair it
[ERROR] Couldn't repair table: mysql.user
[ERROR] Fatal error: Can't open and lock privilege tables: Index for table 'user' is corrupt; try to repair it

Just to confirm that this recurs despite upgrading to Windows XAMPP 7.3.7 (complete re-install).

My circumvention was to restore the mysql.user table from the distribution backup table
badLoser
 
Posts: 1
Joined: 26. July 2019 08:48
XAMPP version: 7.3.7
Operating System: Windows

Next

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 105 guests