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
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
283 views

MySQL configuration to connect to multiple local applications [closed]

I've installed FreeRADIUS, MySQL and FreeRADIUS management (a web-based app) on the same machine, with both FreeRADIUS and FreeRADIUS management using same user to connect MySQL. My problem is when I ...
1
vote
1answer
598 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
3answers
3k views

mysql database restore takes long time

I am in the process of reinstalling a Ubuntu server to rhel6 server. I have a pretty huge database. When I checked the mysql data directory size, it was around 354 GB. However, I dumped the entire ...
1
vote
0answers
180 views

MySQL replication of single DB to same server

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 ...
1
vote
0answers
161 views

Significant difference between duration for querys in SHOW PROFILE and SHOW PROFILES?

I have use mysql profiler to optimize an opencart query which takes more than 10 seconds to executes. mysql> SHOW PROFILES; ...
1
vote
1answer
210 views

Slow queries InnoDB occasionaly

I have the next table: CREATE TABLE `sessions` ( `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL, `session_expires` int(11) NOT NULL DEFAULT '0', `session_data` varchar(10000) COLLATE ...
1
vote
2answers
2k views

Two MySQL Server accessing same database over NFS

I want to run two separate MySQL instances on two separate machines, but using same data directory over nfs, is it possible with InnoDB Storage Engine? Application operation will assure the following: ...
1
vote
1answer
32 views

Is there any value in adding an already indexed column to a composite index?

The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE ...
1
vote
1answer
632 views

InnoDB primary key efficiency

I've read a number of articles regarding efficiency of primary keys depending on the storage engine, and I am left confused. Given a simple many-to-many table with two fields, storeId and zoneId, ...
1
vote
2answers
215 views

Securing Your MySQL Server within your team?

Recently I have been having an issue with our internal team members changing the password of the root user. I noticed the following things: OLD_PASSWORD = 1 (So all passwords on our development ...
1
vote
2answers
260 views

MySQL Slave not updated

This might be a duplicate of questions on the same problem asked before. But all the suggestions to the previous questions do not seem to work in my case. I have set up Replication using SSH Tunnel. ...
1
vote
1answer
153 views

Determining replication traffic

is there any way to determine from server statistics* running MySQL, how much traffic(bits per second) WOULD replication consume between 2 servers, without setting-up the actual replication between ...
1
vote
1answer
441 views

MySQL - Best long read query solution within a replication based system

I'm working on a project where work in a queue requires a database read against heterogeneous table schemas (InnoDB/MyISAM). The queries constructed during the "read" process can result in 10-15 ...
1
vote
1answer
689 views

When tables are locked does MySQL queue queries?

I have a small production website, but it's live 24/7. When running the Optimize, Analyze or Repair commands, MySQL locks the table it is working on. QUESTIONS How would this effect users trying ...
1
vote
1answer
4k views

MySQL: NOT EXISTS vs LEFT OUTER JOIN…IS NULL

Is NOT EXISTS or LEFT OUTER JOIN...IS NULL more efficient for finding records in one table that aren't in another? Specifically, I'm trying to figure out which of the two queries below is better: ...
1
vote
1answer
196 views

creating table result in errno 150

I'm trying to create the following schema, but I'm getting errors when I'm trying to create project and staffphone tables. Not sure what is it complaining about. mysql> CREATE TABLE staff( ...
1
vote
1answer
142 views

How does the transactions interact if you set a different isolation level per connection?

I read that I can have different isolation levels per connection and in the server. The default isolation level is REPEATABLE-READ. So if I have a transaction that issues a SERIALIZED to the client ...
1
vote
1answer
544 views

Native replication from Percona XtraDB Cluster to a standard stand-alone slave

I'm replicating from one node of a three-member Percona XtraDB cluster to a separate slave, using regular MySQL replication. The node is set to write binlog in ROW mode, with log_slave_updates=true ...
1
vote
3answers
844 views

What happens when we take a timediff between timestamp and datetime

Say I have following table in mysql (5.5+): CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL, `x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ...
1
vote
0answers
366 views

Missing rows in MyISAM tables with 10-20 billion records

I've got 2 tables with 10-20 billion records each. Currently these are in MyISAM (MySQL). They only require to be built and indexed once, after this only SELECT is used. They are currently ...
1
vote
0answers
3k 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
180 views

Is column order reliable in UPDATE?

Can we reply on the order of columns in an UPDATE? SET @x = 0 UPDATE table1 SET col1=IF(col1 IS NULL, NULL, @x:=@x+col1), col2=IF(col2 IS NULL, NULL, @x:=@x+col2), col3=IF(col3 IS NULL, NULL, @x:=0) ...
1
vote
1answer
91 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
4answers
131 views

Amount of data in a database table

I am trying to run an alter query on a table, and its giving me a Lost connection to MySQL server during query MySQL server has gone away. The query that I am running is alter table table_name ...
1
vote
0answers
185 views

problem installing mytop , need a up to date tools for monitoring mysql

so i'm trying to installl mytop to monitor the database i'm following this instruction : http://www.sohailriaz.com/how-to-install-mytop-a-top-clone-for-mysql/ when i try to install it with yum i get ...
1
vote
1answer
407 views

MySQL auto_increment delivers different primary key in MyISAM and InnoDB

I have a table with auto incremental primary key. If I want to change a row and add new rows I got different primary keys depending on engine MyISAM and InnoDB. The original table is: pk.. ...
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
2answers
634 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
4answers
244 views

Better Indexing for MySQL?

Is it better to index each column individually with it's own key or to index multiple columns under the primary key? I've seen it done both ways in many different applications and I don't see a clear ...
1
vote
0answers
217 views

mysql.sock missing

I started my MYSQL database this morning but failed. And I tried all means I can think of: chown -R mysql.mysql /var/lib/mysql chmod g+w /var/run/mysqld chgrp mysql /var/run/mysqld chmod -R 755 ...
1
vote
2answers
2k views

INT or TIMESTAMP for the change time column in a logging table?

We're having an argument over what to use for storing change date in a new log table for our site. One side says, use INT for the timestamp, storing the UNIX timestamp. We can convert the datetime ...
1
vote
1answer
2k views

MySQL dynamically optimize innodb tables without “file per table” setting

We are getting a “too many connections” error once a week the same time a mysql procedure runs. The procedure runs “optimize table” on hundreds of tables and takes nearly ten hours to finish, taking ...
1
vote
1answer
12k views

Delete from table where multiple fields match select subquery from other table

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: ...
1
vote
3answers
1k views

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
1
vote
2answers
429 views

Where condition execution sequence in MySQL

I am using MySQL 5.0.88. I have a query like: select DISTINCT party.­id FROM party­ LEFT join party_­identifier AS pi ON pi.­party_­id = party.­id­ WHERE ­(pi.­source_­note=? AND ...
1
vote
1answer
496 views

Select records that do not have associations outside a certain list

I have a mySQL table of nodes and each node can be associated with many categories. I have a list of category ids that are acceptable (498, 499). I want to select only nodes who do not have ...
1
vote
1answer
445 views

MySQL localhost vs Amazon RDS instance

I am seeing unexpected behavior with some MySQL performance. When I run a simple query SELECT 1; on my local host (MySQL 5.6.x) using workbench, it executes in 0.000s, but the same query run on ...
1
vote
1answer
3k views

Varchar index - will hashing value make it faster?

I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
1
vote
1answer
172 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: CAST + SUBSTR vs. FLOOR + MOD

I have a field in a mysql database where dates are stored as BIGINT like YYYYMMDDHHMMSS (PHP: date('YmdHis')). I'm building a query, where I check the amount of rows per hour and have tried two ...
1
vote
2answers
4k views

Recursive query in mysql

I am using GetAncestry function found at this post, The problem is when I try to use it with select query Mysql hang, I am not sure why it is happens my Mysql version is "5.5.16" any help appriciable ...
1
vote
1answer
80 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
1answer
1k views

Mysqldump from the Amazon RDS

I have a Mysql database deployed on Amazon web services RDS. I am running a crontab which runs mysqldump command. The problem is it freezes the rds instance and my website will be down for more then ...
1
vote
1answer
300 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
2answers
231 views

Breaking Semisynchronous Replication in MySQL 5.5

I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7. My application is running and updating the database of the master server and same is being updated in the ...
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
2answers
596 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
412 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
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 ...