All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

1
vote
0answers
84 views

Innobackupex restore steps from 1 incremental and multiple incremental steps

My current backup plan is: Full -> Incremental1 based on full -> incremental2 based on incremental1 -> incremental3 based on incremental2... Please validate if this steps is correct during emergency ...
1
vote
0answers
848 views

Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2?

Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2 ? updated to answer the 3 questions: mysql> show variables like 'version%'; +--------------...
1
vote
2answers
1k views

How to break table into two without losing performance?

According to http://stackoverflow.com/a/174047/14731, splitting away infrequently-needed columns frees up the cache allowing faster retrieval of the commonly-used columns. I've got a table whose ...
1
vote
1answer
2k views

Many transactions are getting stuck in the “cleaning up” state - what could cause that?

I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list. ...
1
vote
1answer
797 views

MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)

We use INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it). We are loading changes to this table in a nightly batch job using LOAD ...
1
vote
1answer
168 views

Database design for dentists

I have a homework, where I should create a database for a dental clinic using MySQL Workbench. This database will be used later for a web app for dentists. Here is a picture of the database design: ...
1
vote
2answers
259 views

Mysql might have too many indexes

I am using the php framework Laravel for my application, together with mysql 5.6, in order to have full-text search with innoBD. But I am not sure if I have too many indexes. My application allows ...
1
vote
0answers
198 views

Best practice for storing image order number in a database

I am designing a social networking website(mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:- PicId (BIGINT, AUTO INCREMENT, PK) ...
1
vote
1answer
675 views

Fetch data from same table using two group by clauses in mysql

I have one table name as consultant with 4 fields(id, name,user_type, Created date). I need to write an mysql query for return the count of user_type in each month. In my table, user_type is ...
1
vote
2answers
710 views

How to create root user after running mysql_install_db?

I'm installing MySQL on ubuntu and I changed the data directory. I created new data files using mysql_install_db command. So now I need to access my database using root but it won't let me in. I ...
1
vote
1answer
53 views

Constrain/limit certain records in sql table definitions

Let's say I have posts on which users can leave a comment. And I want to limit the amount of comments to a certain post to 10, is it possible to add such constraint/restriction to the table? My guess ...
1
vote
1answer
117 views

Windows crashes on doing sql dump [closed]

Windows simply crashes and opens the blue screen for dumping physical memory on trying to import sql dump. Blue screen error: IRQL_NOT_LESS_OR_EQUAL So far I have tried 4 methods: Through workbench ...
1
vote
1answer
565 views

Replication Broken, List Out of Sync Tables

In a master-slave setup on MySQL 5.5 on RHEL 6.1, when db replication breaks (for whatever reason), what is a good way to: check (and alert) replication is broken? get a list of tables that are out ...
1
vote
0answers
4k views

mysql 5.6 GTID replication 'Got fatal error 1236 from master when reading data from binary log'

I Checked SHOW SLAVE STATUS error is "'Got fatal error 1236 from master when reading data from binary log: ''The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master ...
1
vote
2answers
11k views

Why is this MySQL proc using cursors failing to retrieve results?

I am importing data from a third-party vendor. I have already imported their tables into my existing database. Now, I just need to iterate through each of their person records, munge it a little bit, ...
1
vote
1answer
422 views

Mysql Incremental Backup and point in time recovery

I'm writing a shell script using the concept of binary logs for point in time recovery. Now, the scenario is such that a full-backup will happen every alternate day and Incremental backup should be ...
1
vote
2answers
113 views

How to evolve MySQL schema while maintaining integrity

As I understand it, when you run a MySQL DDL statement, it will often lock the table, and then unlock it. It will even unlock it even if you had manually locked it. Also, schema modifications cannot ...
1
vote
1answer
415 views

Setting up DRBD on an active MySQL server

When it comes to setting up DRBD and MySQL, is the following possible? Set up DRBD on an active MySQL server Set up DRBD with no downtime allowed
1
vote
1answer
93 views

Is an explicit KEY redundant if it is covered by a UNIQUE KEY?

I am auditing an application which I've been developing over the course of several weeks. I've noticed this in CREATE TABLE: UNIQUE KEY `baz_3` (`baz`,`foo`,`bar`), KEY `baz` (`baz`), Is the ...
1
vote
1answer
63 views

Update records from one table to another in different databases where condition is accomplish

I have two tables stored in two differents DBs and I need to perform some sync between both tables based on lastmodifieddate and lastSyncAt. Let's call them as db1 and db2. This is the DDL schema for ...
1
vote
1answer
504 views

MySQL backing up MyISAM tables by copying the physical files - and automating the process

I have 2 databases with MyISAM tables running on a Windows 2008 Server. These databases are about 20GB in size with a few tables with millions of rows. My question regards backing them up on a weekly ...
1
vote
2answers
695 views

Should master and slave have same mysql version?

I have a master database running mysql 5.5.11, and I am planning to setup a new slave. I am planning to use the 5.5.30 as it is the latest version. For setting up new slave, I am copying files from ...
1
vote
1answer
4k views

filling a table by triggering multiple tables on insert

I have a table m contains the following fields: id int(11) not null auto_increment, //PK name varchar(100) null, something_else varchar(100) default 'not important' I need to fill up table m by ...
1
vote
1answer
207 views

Adding time to an event that runs on a weekly basis

I have this event that runs every Thursday CREATE EVENT le_drop_database ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_DATE + INTERVAL 3 - WEEKDAY(CURRENT_DATE) DAY DO BEGIN drop database carbon; ...
1
vote
2answers
96 views

Change the Existing Topology of a MySQL Replication Setup

I want to be able to edit the topology of an existing replication, is that possible? For example if I have a star topology, I want to edit it to make it a ring one. How can I do that? I'm thinking ...
1
vote
1answer
250 views

Master port is disabled from Public IP, MySQL log is not getting refreshed

My MySQL Replication setup is over internet. Master is on Public IP. If I stop mysql service in master, immediately an error message is thrown in slave. And when I start the service, slave is ...
1
vote
1answer
174 views

Is there a way to recover this database (MySQL)

I did a "cold" (db offline, all filesystem files) backup of a MySQL db. I wrote the backup to an ISO image. Unfortunately Joliet filenames are not default. I didn't realize that until attempting to ...
1
vote
2answers
1k views

Mysql. Block/disconnect slow queries

I am experiencing issue. Someone 'attacked' my server: simply by searching the same phrase with multiple requests. As it is text search request and database indices are not used, the engine searches ...
1
vote
1answer
71 views

Multi-Column PK or Auto-Increment PK?

In another question I mentioned that I have a table where, in order to ensure uniqueness, I need to use multiple columns as the PK. However, lookups are usually only done using the first of those ...
1
vote
2answers
919 views

Very high load avg for MySQL database [closed]

I have a backend server which response to my clients app, it has more than 20k~25k hit per second and this make my server load average very high, I believe that my MySQL config is not appropriate. In ...
1
vote
0answers
359 views

mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?

I was reading this document: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html It says when upgrading from mysql 5.1 to do a mysqldump and then a reload. Is there anyway ...
1
vote
2answers
835 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
1
vote
2answers
15k views

Connect Error (2054) - mysql_old_password issue still not solved

i use php 5.4.3 and i want to connect to a MySQL DB remotely (php script resides on a different server than the db) with normal oop way: $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME); ...
1
vote
1answer
310 views

Linux 32b MySQL periodically crashing with signal 11 and OOM errors [closed]

I have a MySQL DB with mostly InnoDB tables that I'm fairly certain isn't tuned properly based on what's happening, but I don't have enough know how to pinpoint what to change. The server is a 8G 32b ...
1
vote
1answer
86 views

After adding a slave to Master - Master configuration, slave syncs from only one master

Futher to my previous question, Here is what we have +<------+ | ^ V | S1<---M1 M2 | ^ V | +-------+ what happens is Upon ...
1
vote
3answers
5k views

Automatic MySQL Replication Fail-over Strategy

I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the ...
1
vote
1answer
126 views

Unknown sticky Problem after MySQL upgrade

few weeks ago we upgraded mysql server from 5.5 to 5.6 on 1st server few days ago (5May) I reboot mysql server , after this customer reported that some database data table not load to their site . (...
1
vote
1answer
345 views

Why are these queries slow in some environments and not others? Can we fix their performance?

The following stored procedures in a MySQL 5.x environment: DELIMITER // CREATE PROCEDURE sp_belongs_to (IN schemaName VARCHAR(100), IN tableName VARCHAR(100)) BEGIN SELECT TABLE_NAME as ...
1
vote
2answers
101 views

Is this db/table structure sensible?

I'm not very experienced in database design so bear with me. I've taken over a ruby/rails app. with a connected MySQL/innodb database. As I'm updating the server to the latest ruby/rails version I ...
1
vote
1answer
1k views

Delete by primary key takes too long

What would make a query that deletes a row using the primary key of the table .. take too long ? This is a table with about 1.4M rows that gets .. 70%/30% read/write . I don't know where to look. ...
1
vote
1answer
3k views

MySQL - ERROR 1045 (28000): Access denied for user : permission issue

ex :on first server : mysql> show grants for 'test'@'localhost'; +---------------------------------------------------+ | Grants for test@localhost | +------...
1
vote
1answer
67 views

Are InnoDB files binary compatible between Windows and Linux?

I have seen many posts about recovering InnoDB files. The versions I have are: Windows: 5.6.17 Linux 5.0.96 (I cannot upgrade this at this time) If I create a new InnoDB file on the Windows ...
1
vote
2answers
4k views

Cache all mysql table

Maybe it sound's like i'm an idiot... But i'm developing app with huge db on RoR. Also i see that if i do some query and after that do it one more it is doing much faster, becouse it has been cached......
1
vote
1answer
2k views

Why does MATCH() give Score 0, if there are less rows in the database?

I followed this example from the Mysql Documentation: CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (...
1
vote
2answers
300 views

Master/Master/Slave Replication

Just trying to get some information on the best route to setup replication for this DB I've got. I'm using MySQL 5.1 I found this and it seems like the idea we have in mind. We're looking to have ...
1
vote
2answers
452 views

Mysql fulltext index search returning weird result

I have ingredient table. I want all those recipes which have certain ingredients. Below is my table structure. Table(ingredient) - Applied fulltext index on ingredient column. ------------------------...
1
vote
2answers
167 views

MySQL Records Turnover: How to do it?

I have a table of villas ordered by the UID as follow : UID, name, random_order: 4, villaA, 12 8, villaB, 5 9, villaC, 9 11, villaD, 2 I want to show my villas according to the random_order ...
1
vote
1answer
636 views

Programatically testing slave status - What fields in SHOW SLAVE STATUS indicated slave status is OK / in ERROR?

We use pingdom and a few internal heartbeat mechanisms on our product. We would like to add a 'are our slaves running and in good shape' check. What fields in SHOW SLAVE STATUS indicate that the ...
1
vote
1answer
861 views

Mysql Commands out of sync; you can't run this command now

I have a script which is heavily doing select and insert nearly every seconds without fail and the total queries varies from a second to another. Below is the results of running the queries and ...
1
vote
2answers
863 views

Mysql master-master permission replication issue

I came to know it's safe to replicate mysql database to have the users and permissions replicated, from a post in ServerFault is-it-ok-to-replicate-mysql-db. I have setup master-master replication ...