SalamBoy SalamBoy - 3 years ago 149
SQL Question

MySQL Database in XAMPP is stopped

I'm running Xampp on Linux(Ubuntu 14.04) and while everything was working fine until an hour back right after I shut it down it suddenly starts throwing this error.
the erreur :


MySQL said: Documentation
Cannot connect: invalid settings.
Connection for controluser as defined in your configuration failed.
phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in your configuration and make sure that they correspond to the information given by the administrator of the MySQL server.


The picture of the error message:
Error Message

my config.inc.php :

<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
* phpMyAdmin sample configuration, you can use it as base for
* manual configuration. For easier setup you can use setup/
*
* All directives are explained in documentation in the doc/ folder
* or at <http://docs.phpmyadmin.net/>.
*
* @package PhpMyAdmin
*/

/*
* This is needed for cookie based authentication to encrypt password in
* cookie
*/
$cfg['blowfish_secret'] = 'xampp'; /* YOU SHOULD CHANGE THIS FOR A MORE SECURE COOKIE AUTH! */

/*
* Servers configuration
*/
$i = 0;

/*
* First server
*/
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'brahim';
$cfg['Servers'][$i]['password'] = '';
/* Server parameters */
//$cfg['Servers'][$i]['host'] = 'localhost';
//$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = true;
$cfg['Servers'][$i]['AllowNoPassword'] = true;

/*
* phpMyAdmin configuration storage settings.
*/

/* User used to manipulate with storage */
// $cfg['Servers'][$i]['controlhost'] = '';
// $cfg['Servers'][$i]['controlport'] = '';
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = '';

/* Storage database and tables */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
// $cfg['Servers'][$i]['favorite'] = 'pma__favorite';
// $cfg['Servers'][$i]['users'] = 'pma__users';
// $cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
// $cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
// $cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
// $cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
/* Contrib / Swekey authentication */
// $cfg['Servers'][$i]['auth_swekey_config'] = '/etc/swekey-pma.conf';

/*
* End of servers configuration
*/

/*
* Directories for saving/loading files from server
*/
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

/**
* Whether to display icons or text or both icons and text in table row
* action segment. Value can be either of 'icons', 'text' or 'both'.
*/
//$cfg['RowActionType'] = 'both';

/**
* Defines whether a user should be displayed a "show all (records)"
* button in browse mode or not.
* default = false
*/
//$cfg['ShowAll'] = true;

/**
* Number of rows displayed when browsing a result set. If the result
* set contains more rows, "Previous" and "Next".
* default = 30
*/
//$cfg['MaxRows'] = 50;

/**
* disallow editing of binary fields
* valid values are:
* false allow editing
* 'blob' allow editing except for BLOB fields
* 'noblob' disallow editing except for BLOB fields
* 'all' disallow editing
* default = blob
*/
//$cfg['ProtectBinary'] = 'false';

/**
* Default language to use, if not browser-defined or user-defined
* (you find all languages in the locale folder)
* uncomment the desired line:
* default = 'en'
*/
//$cfg['DefaultLang'] = 'en';
//$cfg['DefaultLang'] = 'de';

/**
* How many columns should be used for table display of a database?
* (a value larger than 1 results in some information being hidden)
* default = 1
*/
//$cfg['PropertiesNumColumns'] = 2;

/**
* Set to true if you want DB-based query history.If false, this utilizes
* JS-routines to display query history (lost by window close)
*
* This requires configuration storage enabled, see above.
* default = false
*/
//$cfg['QueryHistoryDB'] = true;

/**
* When using DB-based query history, how many entries should be kept?
*
* default = 25
*/
//$cfg['QueryHistoryMax'] = 100;

/**
* Should error reporting be enabled for JavaScript errors
*
* default = 'ask'
*/
//$cfg['SendErrorReports'] = 'ask';

/*
* You can find more configuration options in the documentation
* in the doc/ folder or at <http://docs.phpmyadmin.net/>.
*/


When changed
cfg['Servers'][$i]['auth_type']
to
'cookie'
, He got another problem :


Cannot log in to the MySQL server
Connection for controluser as defined in your configuration failed.


the log error :

2016-01-23 06:37:42 8648 mysqld_safe mysqld from pid file /opt/lampp/var/mysql/DrBrm.pid ended
2016-01-23 20:31:06 2900 mysqld_safe Starting mysqld daemon with databases from /opt/lampp/var/mysql
2016-01-23 20:31:07 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2016-01-23 20:31:07 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-01-23 20:31:07 0 [Note] /opt/lampp/sbin/mysqld (mysqld 5.6.26) starting as process 3247 ...
2016-01-23 20:31:07 3247 [Note] Plugin 'FEDERATED' is disabled.
2016-01-23 20:31:07 3247 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-01-23 20:31:07 3247 [Note] InnoDB: The InnoDB memory heap is disabled
2016-01-23 20:31:07 3247 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-01-23 20:31:07 3247 [Note] InnoDB: Memory barrier is not used
2016-01-23 20:31:07 3247 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-01-23 20:31:07 3247 [Note] InnoDB: Using CPU crc32 instructions
2016-01-23 20:31:07 3247 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2016-01-23 20:31:07 3247 [Note] InnoDB: Completed initialization of buffer pool
2016-01-23 20:31:07 3247 [Note] InnoDB: Highest supported file format is Barracuda.
2016-01-23 20:31:07 3247 [Note] InnoDB: The log sequence numbers 1624742 and 1624742 in ibdata files do not match the log sequence number 1682069 in the ib_logfiles!
2016-01-23 20:31:07 3247 [Note] InnoDB: Database was not shutdown normally!
2016-01-23 20:31:07 3247 [Note] InnoDB: Starting crash recovery.
2016-01-23 20:31:07 3247 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-01-23 20:31:07 3247 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace myshop/client_compte uses space ID: 10 at filepath: ./myshop/client_compte.ibd. Cannot open tablespace myshop/mobile12 which uses space ID: 10 at filepath: ./myshop/mobile12.ibd
2016-01-23 20:31:07 7f8b98c6e740 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./myshop/mobile12.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
2016-01-23 20:31:07 2900 mysqld_safe mysqld from pid file /opt/lampp/var/mysql/DrBrm.pid ended

Answer Source

Unfortunately it doesn't look like good news.

According to the error log you've posted, it appears as if the mobile12 table in the myshop database has been corrupted, which is why MySQL won't start. There are three suggested solutions:

1) If there is a permission problem in the file and mysqld cannot open the file, you should modify the permissions.

Check the permissions on the file ./myshop/mobile12.ibd in your MySQL data directory (which might be /opt/lampp/var/mysql). Compare what you find to other files in other subdirectories in the data directory.

2) If the table is not needed, or you can restore it from a backup, then you can remove the .ibd file, and InnoDB will do a normal crash recovery and ignore that table.

Remove the ./myshop/mobile12.ibd file. Note that removing it will destroy it from your database, so you'll need to restore from backup. This is most likely the optimal solution, assuming you have a recent backup.

3) If the file system or the disk is broken, and you cannot remove the .ibd file, you can set innodb_force_recovery > 0 in my.cnf and force InnoDB to continue crash recovery here.

I'm not really clear what the comment is suggesting here, if it means setting innodb_force_recovery will try to rescue data from the corrupted file or if it will work around that file and try to remove it automatically.

Once you remove the corrupted .idb file, the daemon should start properly again. Sorry for the bad news but this should get you moving forward again.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download