MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
2answers
327 views

Why ALL PRIVILEGES ON `db`.* gives access to all databases?

I've installed Ubuntu 12.10(quantal) and mysql-server-5.5. When I execute: drop user ''@'localhost'; create database `redmine`; grant all on `redmine`.* to 'redmine'@'localhost'; User ...
3
votes
1answer
325 views

Random write freezes

I have a MySQL 5.5.23 DB that performs about 3k qps, 5% of which are writes. Lately, I've been having a big problem with random write freezes. Everything is going great, and then all of a sudden any ...
3
votes
1answer
3k views

mysql: deleted performance_schema, is it a problem?

Been following this tutorial http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 in the process I deleted performance_schema along ...
3
votes
1answer
513 views

why is mysql still writing data two hours after all transactions have stopped

I have a mysql server used mostly as data warehouse. It loads about 4-5GB of data per day (actual database size, not the size of the source data). Lately, I noticed something peculiar. After I ...
3
votes
2answers
4k views

what is bigger than a longblob?

the longblob is the biggest data type of Mysql. If I want to save something bigger than 4gb (longblob) how can I do?. I'm talking about Mysql v-5.+
3
votes
1answer
241 views

What's the latest on MySQL Proxy?

Does somebody here know the latest state of the development of MySQL Proxy? Most materials I found on web are outdated.
3
votes
3answers
1k views

Speed up search across multiple columns

We have a table: user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.) Now, we have a user search that search across three columns first_name,last_name and work_place. ...
3
votes
1answer
411 views

How do I prevent updates on an entire table (but allow inserts) in Mysql 5.5?

I want to make the rows in a Mysql table immutable, once they are inserted I don't want it to be possible to change them. How can I do this? This question handles making individual rows immutable, ...
3
votes
2answers
2k views

MySQL connection count much larger than query count

I'm maintaining a relatively simple LAMP server. It does how ever have a some what high load. What I'm noticing is that in a one hour time span there are roughly 50k connections made to the mysql ...
3
votes
4answers
6k views

Synchronizing data between MySQL and Oracle databases?

I want to sync data between Oracle & MySQL databases. What is the best possible way to achieve this? Are there any tools/software available? This is not a one-time but an ongoing operation. The ...
3
votes
2answers
2k views

MySQL replication to slave having VPN connection to Master

I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network ...
3
votes
1answer
6k views

MySql - ERROR 1071 Specified key was too long; max key length is 1000 bytes while restoring dump

while trying to restore a MySql Dump, i get the following error: ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 1000 bytes any ideas?
3
votes
1answer
1k views

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I tried restarting the Mysql server but its not working for me. I am sure the server is running and the permissions on the folder /var/lib/mysql/ are correct (777). When i tried to open the file ...
3
votes
1answer
1k views

Master and Slave having same id's

I am working as a remote DBA for some of the production servers. There was replication from Master to Slave. There is something changed in the scenario they have made one of the Slaves as master ...
3
votes
3answers
3k views

indexes, foreign keys and optimization

Not a DBA so pardon my ignorance... I have a table with 20 fields of which 8 are foreign keys. Mysql has by default added an index for each foreign key. Table has about 100k rows. When retrieving ...
3
votes
3answers
736 views

Converting CHAR to BIT

I have a column with char(16) data type and it was mistakenly set to char(16) actually it should be bit(1) because it contains only three distinct values as follows: 16 characters long string 10 ...
3
votes
2answers
578 views

Testing Query Speed

When I'm running a query for the first time, it takes x miliseconds to be executed. When I run it the second time it takes a lot less time. I assume that it is some caching involved in this mechanism, ...
3
votes
5answers
3k views

Why is MySQL caching data on our dynamic PHP site?

We are having an annoyance with our PHP/MySQL site in that it seems to cache data from the database and our PHP pages are showing out of date records. In fact, there are pages that link to URLs that ...
2
votes
2answers
38 views

Joining two Mysql VIEWs takes a very long time

I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of ...
2
votes
1answer
237 views

Difference data between adjacent rows

I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column. Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not ...
2
votes
3answers
529 views

Can I use PHP function strtotime() in SQL queries?

I have this table, and I need to use a PHP function in the query. id time lastTime 1 08:02 2 08:04 3 08:30 I am using this query with the strtotime(), and it's not working: ...
2
votes
1answer
65 views

Wordpress blog where pages are almost-always served from cache: SSD or more RAM?

I am working on the design of a Wordpress blog which when finished will almost-always serve pages from disk cache and this is because all posts will be published in batch and then cached, with at ...
2
votes
1answer
850 views

pt-heartbeat showing “Host is blocked because of many connection errors”…but connections are well under limit

I'm running MySQL in a master-slave configuration. pt-heartbeat is showing "Host is blocked because of many connection errors"...but connections are well under limit (limit is 600 connections, MySQL ...
2
votes
1answer
101 views

Does READ UNCOMMITTED have the same effect as AUTOCOMMIT=1?

AFAIK running transactions as READ UNCOMMITTED means that there is no lock, so every change caused by a transaction is immediately visible to other transactions. Is it correct to assume that this is ...
2
votes
1answer
133 views

InnoDB: “Only one log file found” after binary import

My first one on DBA... After successful upgrading a master MySQL server 5.5 to 5.6.17 (CentOS 6 VPS serer), I done and moved my binary backups to a slave server (same version 5.6.17 but different ...
2
votes
2answers
341 views

MySQL 5.6: Slave_IO thread stops working

Standard replication breaks for no apparent reason. mysql> SELECT @@version, @@version_comment; +---------------+----------------------------------------------------------------------------+ | ...
2
votes
1answer
152 views

Optimize Percona MySQL InnoDB 3.2TB database

(Before you will call me an idiot or start laughing, please remember it's a real deal on my side and I cannot cancel this assignment as it is a part of a bigger project) I have a database server that ...
2
votes
2answers
320 views

How to monitor InnoDB Disk I/O Required for MySQL?

I am doing an estimate on how much i/o[disk read write] on Mysql 5.6 [innodb storage engine], for getting idea how to calculate total number of disk read write i have searched a lot on SO on DA and ...
2
votes
1answer
840 views

MySQL Index creation failing on table is full

UPDATE: tl;dr: The problem was MySQL uses the TMPDIR when creating indexes. And my TMPDIR was the one running out of disk space. Original Q: I'm trying to add an index to an InnoDB table, and ...
2
votes
2answers
221 views

Design of an application log database

We are generating logs like following example(this is a table no actual pipes): 2014-06-10 09:00:03.457 | Channel1 | Operation3 | Function15 | 15ms 2014-06-10 09:00:08.245 | Channel2 | Operation5 | ...
2
votes
2answers
157 views

Why is this MySQL SELECT performing so slowly despite indexing?

I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation. So my table A looks like this: CREATE TABLE `A` ( `id_b` int(10) DEFAULT NULL, `id_c` int(12) ...
2
votes
1answer
89 views

Mysql Master-Master with additional slaves

We have two datacenters with a copy of our web app (online training platform) at each. The primary datacenter receives all of the traffic at any given time and the second datacenter serves as a ...
2
votes
1answer
117 views

Migrate from innodb_file_per_table to off in MySQL

We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large ...
2
votes
1answer
358 views

MyISAM with Innodb in the same Wordpress database reduce performace?

My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine. I use MySQL ...
2
votes
3answers
5k views

Purge old records from mysql ibdata1 file

Is it possible to purge old records from an ibdata1 file without taking down the database attached to it? Records that have been deleted from the database are still present when I scan ibdata1 for ...
2
votes
2answers
118 views

Convert to Self JOIN

I have a correlated sub query, which is taking 16 Minutes to execute. How can I convert it to join or optimize it. SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM Stock AS SSF WHERE ...
2
votes
1answer
77 views

Is there any way to use different InnoDB settings for different databases on the same server?

From what i know, this is not possible but i'm surprised its not highly requested feature. I'd love to be able to have one db using InnoDB in ACID compliant mode and another db on the same server used ...
2
votes
1answer
243 views

Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)

Innodb works in Mysql 5.1 whether or not the innodb plugin, ha_innodb_plugin.so is loaded at startup or not. Configuration dependent of course. What are the advantages and disadvantages of either ...
2
votes
2answers
766 views

Select the latest changed row in a table

I have a Mysql table test with the fields id, changedate, name. some names have multiple changedates. I can select all rows with SELECT id, changedate, name FROM `test` ORDER BY changedate; How ...
2
votes
0answers
866 views

Query getting periodically stuck in 'copying to tmp table' state, never completes

I am running Wordpress on a dedicated server with a MySQL backend. I have a query that usually takes <1 second to execute, but periodically, this query will get stuck in a 'copying to tmp table' ...
2
votes
1answer
62 views

How to go about modeling the main object of relationship?

I'm using MySQL and MyISAM tables. I have a bunch of objects that I want to group together 1 object belongs to 1 and only 1 group 1 group can have many objects 1 group has 1 required main object ...
2
votes
1answer
435 views

MySQL replication between datacenters

I am trying to figure out the best MySQL replication topology, but I am not sure the best way to lay it out. datacenter A | datacenter B ____ | ____ | | | | | | AA | ...
2
votes
1answer
427 views

Does the size of the primary key contribute to table size?

I have a table (InnoDB) with data length around 36G and index length 23G. It had a composite primary key across three columns. As an initial attempt to reduce the size of this table, I dropped the ...
2
votes
1answer
134 views

MySQL: Securing Access using Stored Procedures

I'm new to using MySQL. I'd like to use stored procedures to help secure my database when accessing it from a web service (i.e., grant only proc execute to the web service account). What permissions ...
2
votes
0answers
19 views

MySQL is too slow [duplicate]

I don't know why mysql is too slow for querying about 39000 records which include inner join with other two tables. I have country, region and city tables as follows: tbl_country CREATE TABLE ...
2
votes
0answers
85 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
2
votes
1answer
768 views

Why is MySQL order by performance poor within a single partition of a partitioned table?

I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a ...
2
votes
1answer
1k views

Best practice: Unions or a derived table?

I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so: CREATE TABLE `pending` ( ... `invoice` int(11) DEFAULT NULL, `lid` int(11) DEFAULT ...
2
votes
1answer
5k views

MySQL: Error reading communication packets

I get this warning in mysql, [Warning] Aborted connection 21 to db: 'MyDB' user: 'MyUser' host: 'localhost' (Got an error reading communication packets) I have been through few topics in google ...
2
votes
4answers
625 views

search for something in the first 1000 records in the table

If I do the following MySQL query select * from table where name Like '%john%' limit 5; that mean the query will search all the mysql database table and return only the top 5 results that match the ...