I think my question is impossible but I sure I do not know everything.   In my opinion, when mysqld restart, some transaction is not still completed will be rollback on startup. Mysqld use undo log to do it. But is there any way to keep long transactions on restarting mysqld ?  I am using MariaDB Galera with three nodes.
120831
I would like to restart mysql gracefully just like httpd where threads are served before restart. I would dislike queries breaking.
30677
We are a team of mathematicians (i.e., no DBA experience).  We have a large database in SQL Server 2012.  It has more than 2 TB of data (hundreds of tables, each with millions of rows and hundreds of columns wide).  Each month, we receive a bundle of additions and revisions to the data, requiring us to perform extensive updates to our database by deleting, replacing, or updating most or all of the tables.  Our work is mostly focused on crafting the SQL logic to calculate the results that we need.  We're not running a real-time call center.  We apply a few indexes as needed, and we're pretty happy with the performance.  The problem is the log file.  Naturally, the log file grows and grows with so much data manipulation.  Our log file currently stands at about 1 TB.  We have a good amount of disk space, but it is not infinite.  From what we read on the Internet, we understand that the log file is necessary for transaction integrity, rollbacks, and recovery.  But for our specific purposes, we don't care about any of that.  We will probably never perform a rollback, nor would we ever attempt a recovery.  Worse comes to worse, we would simply download the data files again and create a new database from scratch.  We really just want the log file to go away and never come back.  We set the database recovery mode to Simple, naively thinking that this meant "no recovery mode", but we were quickly cured of those illusions.  We also understand that there are many wrong things not to do (detaching, shrinking, etc.).  We just don't know the right thing to do.  Maybe someone is going to suggest that we set a limit to log file growth.  However, that leaves two issues: (1)  How do we get rid of the 1 TB which is already there? (2)  We previously tried that, and as we approached the specified limit we started getting Error 9002 (log file full) here, there, and everywhere.  So now we are afraid of applying a size limit.  How do we tell the database "No log file, please" without any hurt feelings?
76421
I scheduled and ran CHKDSK after restart, but it took a long time, so I left it to run all night. In the morning, it had finished and the computer had logged in, but I had no way of knowing if it found bad sectors or anything.  Is there a log file I can look at?  This is what CHKDSK looks like when running before login:
48042
One of our SQL Server 2014 database transaction logs is growing very fast and I cannot find the root cause for this.  Database in Simple recovery mode. During full backup the log file grows to its limits (800GB) and backup fails.  I checked the transactions during the backup and did not find anything strange.  All transactions are our regular loading and no maintenance jobs (index rebuilds etc.)  I am aware of that backup keep the log from reuse but this mean 800GB changes during the backup period and this does not make any sense.  I had compared this DB to similar DB size, loading and backup duration. The second DB Log is not growing more than 400GB.  DB size is 7TB. Backup time is ~20H. The only time log is growing is during backup. Log_reuse shows correct Backup_Restore. No long running transactions.  The problem is the "speed" of log growth - compared to similar sites with same load and same backup duration, the log is 4 time less.
5192
I am trying to delete several rows from a MySQL 5.0.45 database:  delete from bundle_inclusions;   The client works for a while and then returns the error:  Lock wait timeout exceeded; try restarting transaction   It's possible there is some uncommitted transaction out there that has a lock on this table, but I need this process to trump any such locks.  How do I break the lock in MySQL?
32133
I'm running in circles currently, trying to fix some mysql problem.  I couldn't use apt-get for a while now, because it always stuck at the line Setting up mysql-server-5.6 (5.6.27-0ubuntu0.15.04.1) ... and didn't let me do anything. So I tried to reinstall mysql, purging it. While I was able to remove mysql-server and mysql-client eventually, I'm still unable to purge mysql-common. Obviously, I managed to corrupt something, but how can I get my system back and running at this point?  ...:~# apt-get purge mysql-common Reading package lists... Done Building dependency tree        Reading state information... Done The following packages will be REMOVED:   mysql-common* 0 upgraded, 0 newly installed, 1 to remove and 5 not upgraded. After this operation, 0 B of additional disk space will be used. Do you want to continue? [Y/n] Y (Reading database ... 423600 files and directories currently installed.) Removing mysql-common (5.6.27-0ubuntu0.15.04.1) ... Purging configuration files for mysql-common (5.6.27-0ubuntu0.15.04.1) ... update-alternatives: error: /var/lib/dpkg/alternatives/my.cnf corrupt: invalid status dpkg: error processing package mysql-common (--purge):  subprocess installed post-removal script returned error exit status 2 Errors were encountered while processing:  mysql-common E: Sub-process /usr/bin/dpkg returned an error code (1)   -  ...# sudo dpkg -P --force-all mysql-common (Reading database ... 424733 files and directories currently installed.) Removing mysql-common (5.6.27-0ubuntu0.15.04.1) ... Purging configuration files for mysql-common (5.6.27-0ubuntu0.15.04.1) ... update-alternatives: error: no alternatives for my.cnf dpkg: error processing package mysql-common (--purge):  subprocess installed post-removal script returned error exit status 2 Errors were encountered while processing:  mysql-common   -  ...:~# lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description:    Ubuntu 15.04 Release:    15.04 Codename:   vivid   My goal is to just get it back and running again (mysql).
39169
I have a database with recovery mode set to FULL. It's a dev database and I have two full backups dated 19th and 27th i.e. latest.  One of the developer did some changes on 26th and I want to rollback those changes and want to restore database at a state of 25th.   restore database MyDb from disk = 'I:\MSSQL\DATA\MyDb_20160127.bak' with recovery, stopat = '2016-01-25 10:38:15.280', move 'MyDb' to  'I:\MSSQL\DATA\MyDb.mdf', move 'MyDb_log' to 'I:\MSSQL\DATA\MyDb.ldf'      The STOPAT clause specifies a point too early to allow this backup set   to be restored. Choose a different stop point or use RESTORE DATABASE   WITH RECOVERY to recover at the current point.   I am using SQL Server 2005
83506
If I simply wrap my query with:  BEGIN TRANSACTION    COMMIT TRANSACTION   If anything fails inside of that, will it automatically rollback?  From looking at other code, they seem to check for an error, if there is an error then they do a GOTO statement which then calls ROLLBACK TRANSACTION  But that seems like allot of work, to have to check for IF( @@ERROR &lt;> 0) after every insert/update.
17030
How can I backup a large database in mysql? And what is the best way to replicate the database?    what i'm looking for is backing up large database with large amount of data in a small time limit then restoring it within a small time of limit??
13446
I have a question about XTP_CHECKPOINT.  I'm using SQL Server 2014. I have a database that is in SIMPLE recovery model mode. It is also being replicated.  There are no open transactions. I've run DBCC OPENTRAN and it returns:     "No active open transactions."   But I keep getting this message whenever I try to create or drop a table or delete data: (I've replaced my actual database name with the word database_name)     "The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'"     Does anyone know why this might be happening, and, more importantly, how can I make it stop?  And yes, the database really is in SIMPLE recovery model mode. i.e. The transaction log should truncate automatically.  Incidentally, another database that I have in full recovery mode did the same thing, started returning the same error:     The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'   I tried to change the log growth settings to unlimited growth, but it wouldn't let me, returning the same error.  I can reproduce the problem without any XTP stuff at all, except for just the filegroup. Here's how:
43086
I'm not a SQL expert, and I'm reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log?
15020
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help  Setup  The servers are quite hefty with an OLAP/DataWarehouse (DW) type load:   Primary: 96GB RAM, 8 cores + single RAID 10 array Test: 32GB RAM with 4 cores The biggest DB is 540 GB, the total is around 1.1TB and mostly InnoDB tables Solaris 10 Intel-64 MySQL 5.5.x   Note: The biggest DB is the replicated one from the OLTP DR server and the DW is loaded from this. It isn't a full DW: just last 6 months to 6 weeks so it is smaller than the OLTP DB.  Observations on a test server   3 separate connections each has a concurrent (and different) ALTER TABLE...DROP KEY...ADD INDEX the 3 tables have a 2.5, 3.8 and 4.5 million rows CPU usage goes up to 25% (one core is maxed out) and no higher the 3 ALTERs take 12-25 minutes (a single on the smallest takes 4.5)   Questions   What setting or patch is required to allow more than one core to be used? That is, why doesn't MySQL use all cores available? (like other RDBMS) Is it a consequence of replication?   Other notes   I understand the difference between an RDBMS "thread" and an OS "thread" I'm not asking about any form of parallelism Some of the system variables for InnoDB and threads are sub-optimal(looking for a quick win) Short term, I'm unable to change the disk layout OS can be tweaked if needed A single ALTER TABLE on the smallest table takes 4.5 minutes (shocking IMO)   Edit 1   innodb_thread_concurrency is set to 8 on both. Yes, it's wrong but won't make MySQL use multiple cores innodb_buffer_pool_size is 80GB on primary, 10GB on a test (another instance is shut down). This is OK for now. innodb_file_per_table = ON   Edit 2   innodb_flush_log_at_trx_commit = 2 innodb_use_sys_malloc = ON innodb_flush_method should be O_DIRECT (but SHOW VARIABLES doesn't show this) innodb_doublewrite = OFF File system = ZFS (And my sysadmin found this: )   To test   innodb_flush_method isn't showing as O_DIRECT when it should be will follow RolandoMySQLDBA's settings   Let me know if I've missed anything important  Cheers  Update  Changed innodb_flush_method + 3 x thread settings in RolandoMySQLDBA's answer Result: > 1 core used for the tests = positive result
101940
I just installed appache, MySQL and PHP, suring my installation, I was prompted for a MySQL root user password, but when I start my MySQL, it pops the error, and I just can't seem to find a way around it, most solutions I've read doesn't solve it, moreover, I found out that the /var/run/mysqld path doesn't even exist, my php also needs the socket to connect to my Database.
104926
I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source).  I have dozens of databases but one of them is being hit quite hard with large selects which locks out other updates which in turn locks out other selects (at least I think this is what's happening!)  To alleviate this I would like to replicate one of the databases onto the same server so that the writes can write to the "master" and reads can be done on the "slave". I don't have the luxury of additional hardware.  I have found a lot of guides on the internet describing how to replicate to a different server, and one () describing how to replicate to the same server but it doesn't seem to be geared towards retrofitting this functionality to an existing database with 10s of GB of data in it already. It also talks about compiling MySQL from source.  Does anyone know how I can retrospectively convert my MySQL 5.5 config to replicate one of my databases on the same server?  My existing DB runs on port 3306 so I feel it should be as easy as firing up another instance of MySQL on port 3307 and making some changes to my.cnf but not sure what changes.  Thanks in anticipation.
90818
I have been searching for a solution but nothing seems to work.  I have a single Drupal 6 website up on this server.    Server:Ubuntu 12.04 Apache/2.2.22 (Ubuntu) 5.3.10-1ubuntu3.8 MySQL 5.5.32 Drupal 6  Here is a link to my Apache2 and MySQL error files and my config file for MySQL.  Please have a look.  Those files are huge, that is why I linked to them instead of pasted them in here.  If I need to I can paste in here.  Just let me know.  Thanks.  I have done a lot of searching.  I mostly try to Google the errors themselves but can't seem to find the right solution.  I think I have narrowed it down to a problem with innodb but that is it.  I check the tables in the error log and phpMyAdmin say they are ok and I repair them anyway and still get the crashing.  When my site crashes, I have to restart Apache and then restart MySQL and then the site comes back up.  It has been crashing 2 - 6 times a day.  Any help would be greatly appreciated.  Thanks.
107248
After upgrading to 15.04, I have had lots of fun getting to know systemd. I think I have everything working except I am unable to stop mysql.service; the systemctl command just hangs and mysql just keeps on running. Has anyone else experienced this or might know what's going on?
8033
I want to make my Python library working with MySQLdb be able to detect deadlocks and try again.  I believe I've coded a good solution, and now I want to test it.  Any ideas for the simplest queries I could run using MySQLdb to create a deadlock condition would be?  system info:   MySQL 5.0.19  Client 5.1.11  Windows XP Python 2.4 / MySQLdb 1.2.1 p2