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
1answer
1k views

Solutions to 4-byte utf-8 characters in mysql 5.1

I'm running into a problem that seems to be an issue w/ mysql 5.1s limitations on UTF8 characters. From http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-utf8.html Currently, MySQL support for ...
7
votes
5answers
2k 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, ...
4
votes
2answers
4k 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 ...
0
votes
1answer
597 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: ...
3
votes
1answer
4k 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, ...
4
votes
2answers
2k views

What's wrong with this MySQL query?

I am getting a syntax error for my MySQL (5.5.24) query. The error is: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right ...
1
vote
2answers
275 views

Mysql slows down temporarily after large file copying

We have a MySQL 5.0 server on a Centos box. 32GB memory. We have only one harddrive, 374GB capacity, 50GB available. All our tables are MyISAM. When we copy a large file (2GB-3GB) within the hard ...
2
votes
2answers
220 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 ...
0
votes
2answers
1k 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 ...
3
votes
1answer
125 views

Screwed up replication by sharing server ids

So I had my screwup for the week today. I was adding another pair of slaves and set the same server ID on the new slave as an old slave. The layout kind of looks like Master | | \/ | oldS | ...
11
votes
1answer
5k 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 ...
9
votes
2answers
134k 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, each of which opens a MySQL connection. Now when I access PHPlist it often shows this error: Fatal ...
4
votes
3answers
2k views

Should we need to use Barriers on a production database (MySQL/InnoDB)?

Even though we are using a journaling filesystem (EXT3) with barrier enabled, is this still safer and recommended? e.g mount -o barrier=1 /dev/sda /mntpnt Reference: http://lwn.net/Articles/...
0
votes
7answers
17k views

How to recover deleted/updated record in sql server or mysql?

I want to know that is there any way that I can can recover the deleted or updated record in the sqlserver and mysql as well?
4
votes
1answer
7k views

Move ibdata1, set innodb_data_file_path

I am in a bit of an awkward situation right now. I have a database with a few large InnoDB tables, one of which is about 145 GiB. I have been running a lot of maintenance on this table in particular, ...
2
votes
1answer
874 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: ...
2
votes
1answer
1k views

Foreign key with multiple table options

I'm not sure if the title is very good, feel free to suggest a better one. People can "comment" on different objects of my site,similarly to StackExchange (for example they can comment on a Question, ...
1
vote
1answer
5k views

many 'unauthenticated user', 'connecting host', in show full processlist (Mysql 5.1.5)

I have a mysql slave on AWS/EC2 that receives peak CPU randomally. I run show full processlits and I get many (few 100s) of processes like this: '1141944', 'unauthenticated user', 'connecting host', ...
-1
votes
1answer
560 views

Very slow MySQL query. Index problem?

I have a query like this: SELECT BE.NAME as NAME, BE.PREVIEW_TEXT as PREVIEW_TEXT, BE.ID as ID, FPV1.VALUE as PROPERTY_COUNTRY_VALUE, FPV1.ID as PROPERTY_COUNTRY_VALUE_ID, ...
8
votes
1answer
5k 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 ...
4
votes
3answers
4k 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 ...
3
votes
1answer
78 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 ...
1
vote
1answer
472 views

Converting a large SQL Server database to MySQL. Database corruption? Can't access data after transferring [closed]

I have gigabytes of tabular data stored in a SQL Server database. I've been trying to migrate it to MySQL using the Migration Toolkit from Oracle. I didn't have any mapping errors and fixed all the ...
8
votes
1answer
20k 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
278 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 ...
224
votes
15answers
96k views

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies ...
5
votes
3answers
7k 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 ...
1
vote
2answers
4k views

Error reading MySQL 5.5 binary log using mysqlbinlog

I'm having some trouble with a replication setup I'm using. It has been working fine for months, and then over the weekend, the slave stopped reading updates from the masters binlog with an error Got ...
4
votes
2answers
6k views

SELECT TOP in MySQL

How can I do this in MySQL? SELECT TOP 50 PERCENT * FROM table What's the simplest way to do it without running to separate queries (if possible)?
1
vote
1answer
45 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) ...
9
votes
2answers
9k views

Running SQL Query on all databases

I have one hosting account using cPanel and phpmyadmin. I have 50 databases under this account, all WordPress. I need this query modified so that it runs through all databases to update the password....
6
votes
1answer
25k 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 ...
3
votes
2answers
7k views

MySQL: Delete all but last N records

Consider the following table: mysql> DESCRIBE pixels; +---------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default ...
17
votes
3answers
44k views

MySQL Set UTC time as default timestamp

How do I set a timestamp column whose default value is the current UTC time? MySQL uses UTC_TIMESTAMP() function for UTC timestamp: mysql> SELECT UTC_TIMESTAMP(); +---------------------+ | ...
1
vote
1answer
432 views

query_cache tuning for a database server to keep db from slowing

I have a db server that that is a cloud slice of 512mb ram and 20gb disk. I wanted to make this run as fast as i can since it's the db for a magento insall on the app server. I turned on the ...
7
votes
1answer
18k 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 TIME+...
0
votes
1answer
62 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 ...
0
votes
1answer
639 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, ...
3
votes
1answer
1k 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 "...
-10
votes
1answer
341 views

What are the edges of typical (or specific) paid databases like oracle of mysql compared to the free oness? [closed]

Note: I am building a startup. I am currently using mysql. I am pretty much stuck. Others recommend other free databases like mongodb. I am seriously considering paid one IF and only IF it gets better ...
1
vote
1answer
6k views

Connecting remote MySQL database to local MySQL database?

I want to write PHP code to be embedded in a Drupal7 module. I want to call a procedure which can copy newly generated data in the local MySQL database to a remote MySQL database. When data is ...
0
votes
2answers
2k 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. # ...
5
votes
3answers
6k views

What's the formula for calculating Key Efficiency, Key Buffer Used, and Query Cache Hitrate in MySql?

Now, I have retrieved some dates from table of 'GLOBAL_STATUS' and 'GLOBAL_VARIABLES' by MySQL DB named information_schema . I can retrieve key_buffer_size key_cache_block_size query_cache_limit ...
0
votes
1answer
112 views

Multitable partition guide

We have a main table called tblLink in it will have a date field.The tblLink will have its primary key as foreign in few other tables too and one of the table will tblMainData which over time will be ...
0
votes
1answer
79 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 ...
4
votes
4answers
10k views

Benefits of BTREE in MySQL

What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage? Does BTREE provide easier iteration in increasing order ? What kind of queries would ...
0
votes
2answers
1k views

Some queries slower in MySQL 5.5 than 5.0?

We have an exceptionally inefficient process (1.3 million selects, 232k updates) that I'm now recoding to be 1 select and 50 updates. In the meantime, though, today's process ran. We recently updated ...
2
votes
1answer
4k 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 ...
5
votes
2answers
2k 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
1answer
954 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. +------------+...