MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
2answers
746 views

Dates changed to 0000-00-00

Previously, on my table (~81000 rows), I have had date and time fields set as VARCHAR instead of date/time. All those dates were of the type MM/DD/YY. I changed the type from VARCHAR to DATE and TIME ...
1
vote
1answer
133 views

slave issue with load data

When I use "load data infile" on master, the data is properly transferred to slave. But when I use "load data local infile" from remote server to master, the data does not pass on to slave. Why does ...
2
votes
1answer
791 views

MySQL Master/Slave Master/Master setup?

I currently have 1 mysql server running and want to install 2 seperate mysql VPS'es. Maybe one master for inserts/updates and one slave for selects. What is the best setup for this if I have the ...
2
votes
3answers
3k views

Monitoring replication on mysql

I have managed to setup master and slave replication. It is working fine. What are the possibilities that it might go down? Is there any alerting tool to monitor that? Another thing: Can I run a ...
4
votes
1answer
9k views

What can cause TRUNCATE TABLE to take a really long time?

I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves). I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands ...
3
votes
2answers
3k views

pt-table-checksum help required

I am trying to figure out pt-table-checksum, as I am using it for the first time. It look likes the documentation is complex and not easy to understand stuff. I have a complex replication topology ...
4
votes
3answers
769 views

MySQL console: Ctrl+C driving me nuts

There is something in the MySQL console that drives me nuts. When I hit ctrl+c to cancel the current command being typed, the terminal exits. In every terminal I know (*nix terminals, Python, ...
0
votes
1answer
555 views

percona mysql crashing at innodb initialization

i have a slave percona db setup that also maintains few of it's own tables it started crashing with this in error.log: InnoDB: In a MySQL replication slave the last master binlog file InnoDB: ...
2
votes
0answers
3k views

mysql taking too long to send data

I have a simple table with million of records (14,000,000) and for a simple query it is spending too much time "sending data". The table CREATE TABLE IF NOT EXISTS details ( id int(11) NOT NULL, ...
11
votes
1answer
3k views

MySQL table with 100,000 records queried often

I have a single database of about 100 tables to store various kinds of information. The most important table is our order table which is used to store customers orders and is over 100000 records as ...
5
votes
2answers
935 views

Implementing a Circular Buffer (Sliding window) in MySQL

I intend to store some Java objects in a MySQL database, accompanied by a timestamp. These objects should be kept in a Sliding Window fashion (also known as Circular Buffer), meaning that only the ...
2
votes
2answers
206 views

MySQL : UNION script help

I have two tables which have roughly the same data but we could not mix them together due to the nature of the data input and presentation and to keep the table size small. We run the followign ...
2
votes
1answer
1k views

MySQL : “FLUSH TABLES WITH READ LOCK” started automatically

I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being ...
4
votes
3answers
2k views

InnoDB vs MyISAM with many indexes

I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway: I have a ...
6
votes
2answers
79k views

How to resolve too many connections and fatal error in mysql running on vps

I am running an application PHPlist on my linode server, simultaneously running 12 php scripts which open mysql connection, now when i access phplist it shows lot of times an error shows: Fatal ...
2
votes
1answer
669 views

Help with a complicated MySQL Query inserting data using select from 2 tables

I am stuck with a case and unable to get the desired result from my query.. I have to insert into a table some records based on multiple records from 2 different tables.. Tables: Target: ...
4
votes
1answer
1k views

Which mysql storage engine to choose?

I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB). The theories say: Both work with BTree indexes, but MyISAM can work with FULLTEXT index ...
2
votes
5answers
528 views

Help optimizing query in the MySQL slow-query log

Our database is set up so that we have a credentials table that hold multiple different types of credentials (logins and the like). There's also a credential_pairs table that associates some of these ...
6
votes
1answer
3k views

Capabilities of InnoDB INSERT Performance

Hi I am running the most recent version of Percona Server. Server version: 5.5.24-55 Percona Server (GPL), Release 26.0 I have a 10 cpu box of these characteristics. processor : 0 vendor_id ...
3
votes
1answer
710 views

MySQLDump wrong dump

i'm trying to generate a dump from my database but when i try to load the dump file it gives me an error. What should i do ? My MySQL ( loader) version is mysql "Ver 14.14 Distrib 5.1.57, for ...
4
votes
3answers
5k views

Table cache hit rate is 0% - is that a huge concern?

Hi all of a sudden my server has slowed to a crawl, queries that were 0.10 of a second consistently now taking 20 seconds. somewhat randomly, sometimes it snaps right back. so I have a really sudden ...
21
votes
5answers
32k views

How do you tune MySQL for a heavy InnoDB workload?

Assuming a production OLTP system with predominantly InnoDB tables What are the common symptoms of a mistuned/misconfigured system? What configuration parameters do you most commonly change from ...
3
votes
1answer
71 views

Administration of MySQL Users

What is the best practice for keeping and maintaining MySQL users? Say for example I have 5 programmers and a PHP application connecting to a database. Should I create 6 different users 1 for each ...
7
votes
1answer
11k views

Trigger to UPDATE after UPDATE?

I want to make a trigger to record the time of any update as CREATE TRIGGER col_update AFTER UPDATE ON col FOR EACH ROW BEGIN UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id END The ...
3
votes
1answer
228 views

Serialized objects vs multiple tables

What would be the best way to store several entries in a database, each containing a list of other entries? An example of this would be a table of receipts, where each receipt has some information ...
1
vote
1answer
44 views

What is this type of query called, and how can I formulate it?

I have following tables: events(event_id, name, description) groups(group_id, event_id, title, description) event_group(event_id, group_id) users(user_id, name, email) ...
2
votes
1answer
12k views

MySQL user permission on stored procedure

I've created a simple stored procedure: mysql> CREATE FUNCTION hello (s CHAR(20)) -> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT('Hello, ',s,'!'); Query OK, 0 rows affected, 1 ...
1
vote
2answers
5k views

MySQL: Delete all but last N records

Consider the following table: mysql> DESCRIBE pixels; +---------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default ...
6
votes
1answer
11k views

High CPU usage from MySQL with no queries at all running

I noticed that MySQL is using way more CPU than usual on my workstation (usual is 1% or less, because this is my workstation, not a server). PID USER PR NI VIRT RES SHR S %CPU %MEM ...
0
votes
1answer
573 views

Why do aggregate functions alter the way my rows are fetched?

Running this query SELECT s0.id, s0.symbol, YEARWEEK(p0.datetime) AS label, p0.id AS point_id, p0.datetime, KG_FIRST(p0.open, p0.datetime) AS open, MAX(p0.high) AS high, ...
1
vote
1answer
557 views

MySQL: Try to Port a Trigger from SQL Server

Here, there's a sample of a trigger with "INSTEAD OF UPDATE" clause: CREATE TRIGGER [dbo].[Test_PTA_Table_Update_trg] -- ALTER TRIGGER [dbo].[Test_PTA_Table_Update_trg] ON [dbo].[Test_PTA_Table] ...
0
votes
1answer
59 views

Is index like key(timetime) usable when we query: SELECT * … ORDER BY timetime DESC

When we index a timestamp field like timetime. Can we use it for ORDER BY timetime DESC or is it just usable for ORDER BY timetime ASC OR ORDER BY timetime? Does mySQL use this index when doing vise ...
13
votes
2answers
1k views

What can we do in MySQL 5.0 Replication to address bandwidth concerns?

I am developing an application to run on the client PC (Win) which is configured with a MySQL server 5.1 instance that will act as read-only slave to the remote master. The remote master has dozens of ...
2
votes
2answers
303 views

MySQL Partitioning

I have a MyISAM table that holds a few million Messages. I am thinking about converting it to an partition InnoDB table. Just wondering about the best partition strategy. I would like to do this ...
3
votes
1answer
752 views

MySQL Handler_read_rnd_next == tablescans without indexes?

I am in the process of tuning our Percona 5.5 server, and have been using percona's tools to plot our database's performance. In the MySQL Handlers Plot, i have been noticing alot of ...
3
votes
2answers
102 views

MySQL using a index referencing a different column

I have a large table which uses a versioning system in the table. When a record is updated a new record is inserted with the old data and the original record is updated ( so to maintain the same ...
10
votes
1answer
495 views

Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
6
votes
2answers
378 views

Less restrictive query return less result due to simple removing one additional constraint

Look at this query SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance FROM ...
0
votes
1answer
1k views

Lock wait timeout exceeded on insert despite low lock_time in the slow query log

Our Java/Spring application reports occasional lock wait timeouts on single record INSERTs to a MySQL 5.5 InnoDB table. Here are a few slow query log records for some of the failed INSERTs. # ...
3
votes
2answers
790 views

MySQL insists on trying to use InnoDB even when told not to, please help

We're trying out MySQL 5.5, and have installed it on one of our slave servers as a test run. Our main server is 5.0, and we have zero optimization here for InnoDB, so I want everything to just be ...
4
votes
1answer
2k views

MySQL master binlog corruption

We recently made some changes to our infrastructure and now I can't keep MySQL replication running. The slave complains of a corrupt binlog and resetting it doesn't help. I keep seeing entries like ...
1
vote
2answers
843 views

String comparison with integer is not working sometimes in MySQL 5.5

I have upgraded MySQL to version 5.5. Before upgrading when we used string comparison with integer, it was working. Code is "value = 1", here 'value' is declared as varchar(45) and its value can be ...
0
votes
1answer
74 views

MySQL slave seems confused as to just how far out of date it is

I have a slave that claims, in SHOW SLAVE STATUS, to be zero seconds behind the master. However, it also says it's on Master_Log_File binlog.009636, which is 46.9 gig behind where the master is. I ...
0
votes
1answer
2k views

How can I tell if an index is being used to sort in MySQL?

I have a query with an ORDER BY clause which uses a column which is the last column on an index which is being used in the WHERE clause, essentially of the form: SELECT cols FROM tables WHERE ...
2
votes
1answer
494 views

Data type for protobuf objects in mysql

I would like to store some protobuf objects in a MySQL database. The objects are created in a Java application. The object is accompanied with a textual type identifier and a timestamp. ...
2
votes
1answer
2k views

Should I use WHERE NOT EXISTS or LEFT JOIN for getting the ids of relationship table?

I have users table: CREATE TABLE `users` ( `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `flname` varchar(60), ...
1
vote
2answers
154 views

Copying a database for testing in a safe way

For testing purposes I have to create a copy of a production MySQL database that cannot be touched. These are the steps I would follow: Create a reduced size database backup: mysqldump prodDB ...
1
vote
1answer
3k views

How do you load a data file into a specific field?

How can I load data in a text file into a specific field. It's a 30 line log file from some copy operations; nothing fancy. I just want to store it as large text or a blob. The examples I am able to ...
2
votes
2answers
2k views

Best practices to secure mysql

I don't have that much knowledge about mysql and i would like to secure a MySQL server. We have a few VPS running around the world (6 currently) and they all need to store information in our mySQL ...
1
vote
1answer
2k views

MySQL - SELECT INTO OUTFILE, with a UNION

Is it possible to issue multiple select queries whose resultsets are combined via union, into the same outfile? What I am trying to do, but not working: (SELECT email FROM table WHERE createdate ...