I just upgraded from Debian Squeeze to Wheezy and mysql stopped working. Blindly following  without any understanding of what I was doing, I removed ibdata1 in an attempt to solve the problem. Unfortunately, it was only after doing so that I realised this was a terrible idea, especially as I was missing this option in my.cnf: innodb_file_per_table = 1.  I found the cause of my problem and now my DB server is working again. However, now, I am left with a mess. My problem is that some (but not all) of my tables, which appear when I do show tables;, are giving me this error:  ERROR 1146 (42S02): Table 'db.tablename' doesn't exist   I have since enabled the setting innodb_file_per_table = 1 (perhaps this was a bad idea?) and before I came to realise that I probably didn't have a chance, I was trying out the innodb_force_recovery flags.  I'm fairly confident that these tables are gone for good so I'm trying to restore my database from earlier dumps. The problem is that the import breaks on these tables that "do but don't" exist. I have tried dropping the database before doing the import but there are clearly some other files lying around. Needless to say I'm reluctant to do any more speculative deletion of files...  Any advice on how to clean up this mess?
123989
My WAMP directory accidentally get deleted by another user. Only data folder in MySQLis available. And, in that only database-folders (folders in "\bin\mysql\mysql5.6.12\data\" with name of databases) are available. All files including "ibdata1" in root of "\bin\mysql\mysql5.6.12\data\" are also deleted.  The database folders contains files with below extensions only.  *.frm, *.ibd  and "db.opt" file.  How the databases can be recovered?  I already have tried to recover bdata1. But, unable to get it back. And, some database contains MYISAM also.
36049
We have several MySQL backup files of a database in *.SQL format and we want to restore the database by using these files. However, as the sql files contains "DROP TABLE" definitions, running one script cause to remove the data created by the previous script. In addition to these sql files, we have also some other backup folders containing *.FRM and *.MYD files. In all of two cases I think these backups are Differential or Incremental and we have to merge/combine all of them. If so,  how can we combine them without losing data? As I have no experience on MySQL, could you explain a way by using MySQL Workbench if it is possible? Thanks in advance.  Some lines from one of the sql file:  DROP TABLE IF EXISTS `BLACKLIST`;     CREATE TABLE `BLACKLIST` (       `ID` int(11) NOT NULL auto_increment,       `NAME` varchar(30) NOT NULL,       `SURNAME` varchar(30) NOT NULL,       PRIMARY KEY  (`ID`),       UNIQUE KEY `BLACKLISTINDEX` (`NAME`,`SURNAME`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
46196
I have a lot of field_deleted_* tables in the database and neither drush cr or running cron is removing them. Is it okay to just remove them from the database? How can I tell Drupal to remove them otherwise?    Edit: Per 4k4 troubleshooting tip:  ➜  default git:(HUHLR-172) ✗ drush ev "var_dump(\Drupal::state()-&gt;get('field.storage.deleted'))" array(0) { }   This means:     If this is empty, then you can remove the tables in the database as   well, because Drupal has no chance to guess the hash keys of the table   names without this information.
115198
This problem is really strange, I have tried many ways to solve but nothing worked. My last output was:      Reading package lists... Done     Building dependency tree            Reading state information... Done 0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded. 2 not fully installed or removed. After this operation, 0 B of additional disk space will be used. perl: warning: Setting locale failed. perl: warning: Please check that your locale settings: LANGUAGE = (unset), LC_ALL = (unset), LANG = "pl_PL.UTF-8" are supported and installed on your system. perl: warning: Falling back to the standard locale ("C"). locale: Cannot set LC_CTYPE to default locale: No such file or directory locale: Cannot set LC_MESSAGES to default locale: No such file or directory locale: Cannot set LC_ALL to default locale: No such file or directory Setting up mysql-server-5.5 (5.5.38-0ubuntu0.14.04.1) ... locale: Cannot set LC_CTYPE to default locale: No such file or directory locale: Cannot set LC_MESSAGES to default locale: No such file or directory locale: Cannot set LC_ALL to default locale: No such file or directory /var/lib/dpkg/info/mysql-server-5.5.postinst: line 146: logger: command not found ATTENTION: An error has occured. More info is in the syslog! /var/lib/dpkg/info/mysql-server-5.5.postinst: line 236: logger: command not found dpkg: error processing package mysql-server-5.5 (--configure): subprocess installed post-installation script returned error exit status 127 dpkg: dependency problems prevent configuration of mysql-server: mysql-server depends on mysql-server-5.5; however: Package mysql-server-5.5 is not configured yet.  dpkg: error processing package mysql-server (--configure): dependency problems - leaving unconfigured Errors were encountered while processing: mysql-server-5.5 mysql-server E: Sub-process /usr/bin/dpkg returned an error code (1)   I really don't know how to fix that, guys please help me :)
10305
I tried creating a field, but my server crapped out halfway with an Internal Server Error due to some spambot attack...  Now, when I try to add the field again, I am getting:     The machine-readable name is already in use. It must be unique.   I guess some tables were populated in the db. How can I remove these easily?
18221
How can I restore a mysql database that was dropped using a "drop database" command? I have access to binary logs which should make this type of rollback possible.
94572
I added the official MySQL repository just as instructed , it works just fine in Ubuntu 18.04 , and the mysql-server version 8 is available, but for some reason in 18.10 it doesn't work, I can't seem to find MySQL version 8 packages, please help.
49283
We are using MySQL version 5.0 and most of the tables are InnoDB. We run replication to a slave server. We are thinking of backing up the MySQL log files on a daily basis. Questions  Is there any other way of doing an incremental backup without using the log files? What are the best practices when doing incremental backups?
91873
We had several power outages and server rebooted couple times which seemed to cause issues with one of the databases.  We tried detaching at attaching database again but it looks like database is corrupted and we’re getting the  Msg 5172, Level 16, State 15, Line 1  Claiming that database header is invalid. Is there anything we can to do repair database or extract data from MDF file?  We do have a backup but it’s like 2 weeks old and doesn’t contain all data.
96803
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by default).  I understand that InnoDB will map the location of data in the file by individual index files for tables, but I do not understand why it mixes all data in one file. And more importantly, why mix the data of all databases on the server?  An interesting feature of MyISAM is that one can copy/paste a database folder to another machine and then use the database (without a dump).
119372
I am very, very new to MYSQL.I tried to create a table named "option". My SQL Query is :  create table option(   id int not null primary key auto_increment,  choice varchar(30)  )  While executing this query it shows the following error  Error Code : 1064 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 'option( id int not null primary key auto_increment, choice varchar(30) )' at line 1 (0 ms taken)  If I try with the table name as "choice" it is working.  can we have the table name as "option" in mysql?   thanks
112412
WAMP installed fine, no problems, BUT...  When going to phpMyAdmin, I get the error from phpMyAdmin as follows:  Cannot load mysqli extension. Please check your PHP configuration   Also, phpMyAdmin documentation explains this error message as follows:     To connect to a MySQL server, PHP needs a set of MySQL functions   called "MySQL extension". This extension may be part of the PHP   distribution (compiled-in), otherwise it needs to be loaded   dynamically. Its name is probably mysql.so or php_mysql.dll.   phpMyAdmin tried to load the extension but failed. Usually, the   problem is solved by installing a software package called "PHP-MySQL"   or something similar.   Finally, the apache_error.log file has the following PHP warnings (see the mySQL warning):  PHP Warning: Zend Optimizer does not support this version of PHP - please upgrade to the latest version of Zend Optimizer in Unknown on line 0 PHP Warning: Zend Platform does not support this version of PHP - please upgrade to the latest version of Zend Platform in Unknown on line 0 PHP Warning: Zend Debug Server does not support this version of PHP - please upgrade to the latest version of Zend Debug Server in Unknown on line 0 PHP Warning: gd wrapper does not support this version of PHP - please upgrade to the latest version of gd wrapper in Unknown on line 0 PHP Warning: java wrapper does not support this version of PHP - please upgrade to the latest version of java wrapper in Unknown on line 0 PHP Warning: mysql wrapper does not support this version of PHP - please upgrade to the latest version of mysql wrapper in Unknown on line 0   So, for some reason PHP is not recognizing the mysql extension.  Anyone know why? Any solution or workaround?
25421
I recently read that because of how InnoDB recalculates the AUTO_INCREMENT value when the server restarts, any records on the high end of the ID list may have their IDs reused.  Normally, this isn't a problem, because when a user is deleted everything associated with the ID is deleted from other tables too.  But I'm deliberately leaving their forum posts orphaned, labelled as "Posted by =User #123=", so that past conversations are retained. Clearly, should an ID be reused, this will be a problem.  I've never had this issue before because there were always enough new users to make it unlikely for an ID to be reused in this way. However on my new project signups are rare and inactive user deletions frequent (especially since the "Open Alpha" accounts only last for three days as a preview), and such ID reuse has happened three for three now.  I have "fixed" the issue by saving the correct value for AUTO_INCREMENT elsewhere and using that instead of relying on the internal value. Is there an actual way to have InnoDB remember the actual last value?
77913
I'm going to tell you exactly what I need in order to clarify the cryptic question in the title. I'm currently making scheduled MySQL backups of all my databases with something like:  mysqldump ... | gzip -c &gt; mysql-backup.gz   This is ok, but I'm willing to make a separate file for each single database, since that will make it easier to take a look at dumped data or restore a single database:  for db in $dbs; do mysqldump ... $db | gzip -c &gt; mysql-backup-$db.gz; done   I'd like to store all of the dumps for each single backup in a single .tar file, i.e. mysql-backup.tar.gz with all the dumped databases inside. I know I can simply leave .sql files uncompressed and then tar -cz *.sql, but 1) I'm searching for a way that doesn't need to temporarly store big files. In my current script, in fact, mysqldump is piped into gzip, so no big file is created.   2) Is there a similar way in which I can create .tar.gz from stdin?   3) Is tar -c *.sql.gz eqivalent to tar -cz *.sql?
90750
This is the error I am suddenly getting.  Additional uncaught exception thrown while handling exception.  Original PDOException: SQLSTATE[70100]: Unknown error: 1317 Query execution was interrupted...my query  Additional PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away...my query   After doing some research it sounds like a custom module, which I have made, is attempting a query which is too big. A solution that seems to work for a lot of people is this:   Open my.ini Change "max_allowed_packet" from "1m" to "16m" (or larger) Save my.ini and restart MySQL   However, I am on a shared server, so I cannot edit my.ini, and although my provider has confirmed to me that the value is set to 1M, they won't increase it.  I also tried adding this: ini_set('mysqli.reconnect', 'on'); to settings.php. It didn't solve the problem.  Does anyone know of a another solution, or work around?  Thanks.
54860
I was given a MySQL database file that I need to restore as a database on my Windows Server 2008 machine.  I tried using MySQL Administrator, but I got the following error:     The selected file was generated by   mysqldump and cannot be restored by   this application.   How do I get this working?
3892
I am trying to move the data directory of my MySQL database to a second disk array that I have as a mount point /array2/.  The problem I am having is I have tried everything and after I modify the location of datadir in my.cnf mysql will not start up again.  All I get is:  start: Job failed to start