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

2
votes
2answers
459 views

Bin logs with high i/o

I'm DBA (IBM Informix) and already use MySQL with Drupal , where have low workload. So, my knowledge of MySQL is minimal. Few days ago I start use MySQL with a basic configuration as back-end for a ...
8
votes
2answers
5k views

Deleting MySQL table with pending transactions

Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)? What happened: I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
1
vote
3answers
698 views

Which RDBMS has the most advanced “table-valued” functions?

Every Database Management System focuses on specific features and applications. I am using mysql, probably as it is popular and satisfies common needs. However, I came across a problem, which needs ...
0
votes
2answers
106 views

MySQL query slow even with indexes

Structure of my table mysql> show create table t_group_tag_relation\G *************************** 1. row *************************** Table: t_group_tag_relation Create Table: CREATE TABLE ...
1
vote
1answer
392 views

mysql freeze/crash on JOIN

here is query: SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image, nw.status, src.title as src_title, src.keyword as src_keyword, src.url as src_url, src.icon as ...
3
votes
1answer
108 views

Way to add referencing and referenced entities of a specific entity in E-R Diagram MYSQL

Is there any way to add entities that are referencing a specific entity in an E-R Diagram. I have a database containing 400 tables. I want to see a view containing a specific entity and its ...
2
votes
1answer
551 views

Database structure for affiliate system

I am creating affiliate tracking system and looking for best database structure to use on MySQL to place the lowest possible load on the server. There will be 1000 affiliates, and each will have ...
0
votes
4answers
8k views

Unable to access mysql database remotely

I ran this command- [root@dev-img mysql]# mysql -u root -h 192.168.1.181 -e "use mysql;" and this is the output I got- ERROR 1045 (28000): Access denied for user 'root'@'192.168.1.65' (using ...
1
vote
0answers
161 views

Why is this table structure so slow to CRUD?

I have a table that I am using for queuing emails. Unfortunately, adding to or reading from this table becomes horrendously slow after adding about 50 records. -- Table "mail_queue" DDL CREATE TABLE ...
3
votes
2answers
1k views

How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column: TABLE 1: word_scores word score this 3 is 4 a 5 test 1 another 0 sentence 8 TABLE 2: ...
5
votes
1answer
269 views

Which MySQL storage engine is the most similar to ARCHIVE when that is not available?

I have a table that contains a log of events that is written to often and read very infrequently by comparison. I would love to use the ARCHIVE engine, because it is basically designed for exactly ...
0
votes
2answers
214 views

How can I optimize this query and support multiple SKUs?

My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any ...
1
vote
2answers
3k views

Trouble with triggers and insert .. on duplicate key update

I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For ...
5
votes
1answer
3k views

MySQL on RDS, data transfer from one instance to another as a production job

I have a MySQL RDS instance ("production") from which I need to copy incremental data to another MySQL RDS instance ("reporting"). “Incremental data” being the new rows in certain tables. Currently ...
2
votes
2answers
1k views

mysql replication delay very serious

We use a ssd disk for master database and SATA disk for slave. On high write load (300 writes/s), slave delay is very serious. I have set: innodb_flush_log_at_trx_commit = 0 increased ...
5
votes
5answers
8k views

Field types for columns in a MySQL query

Is it possible to get the field type from a MySQL query, in the same way you can get it from a table with the SHOW COLUMNS command? edit: i should clarify that a bit. I am aware that I can get the ...
2
votes
2answers
6k views

How do I force a JOIN to use a specific index in MySQL?

I have a query that JOINs 2 tables; lineitem and part, select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem force index for join (l_pk), part where ( ...
2
votes
2answers
3k views

Comparing dates in a BEFORE INSERT trigger

I need help making a TRIGGER that compares two dates being entered into a table, but I'm not 100% on the syntax I should use. Something like: CREATE TRIGGER chk_dates BEFORE INSERT ON ...
8
votes
1answer
16k views

Should a MySQL replication slave be set to read only?

I've got replication running on Percona Server 5.5 by following this guide and wondered if I should add read-only=1 to my slave's my.cnf to make it read only? The guide sets up replication for the ...
3
votes
1answer
5k views

Foreign key of multiple tables

I have the following 3 tables: ╔═════════════════╗ ║ Companies ║ ╠═════════════════╣ ║ id ║ ║ name ║ ║ type ║ ║ etc... ║ ╚═════════════════╝ ...
1
vote
2answers
2k views

MySQL Enterprise Backup error: Log scan was only able to reach xxxx

I use MySQL Enterprise Backup to make incremental backups, but when I apply a restore (copy-back), I can't remake a backup. I get this error: mysqlbackup: ERROR: Log scan was only able to reach ...
2
votes
2answers
375 views

MySQL “Limit” clause grabbing more results than requested

I'm trying to add pagination to my points system (100 results per page) and came up with this : SELECT A.username, A.points FROM users A INNER JOIN ( SELECT points FROM users WHERE points ...
1
vote
2answers
2k views

Best way to organize compound indexes on partitioned table (date by range)?

I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date ...
0
votes
0answers
64 views

How to check on my partitions?

I am a newbie into partition. First I have a table with auto increment with its primary key and many other fields. I wanted to partition based on dataInsertDateTime column. It have me error so I had ...
7
votes
1answer
793 views

LOAD DATA INFILE blocks on concurrent use against MyISAM tables

I'm having trouble loading data into MyISAM table(s) concurrently using LOAD DATA INFILE. The loading operation works fine in isolation. Trying to do LOAD DATA INFILE operations concurrently, blocks ...
1
vote
1answer
750 views

How to run this MySQL statement?

See the MySQL example on http://dev.mysql.com/doc/refman/5.5/en/loop.html: I copy it and paste on MySQL command line but it stop at every semicolon. I wonder how to make these sentences work? I ...
2
votes
2answers
2k views

how to reduce mysql overhead?

My machine has 2GB RAM, I have some cron job run every minute insert into some data. My mysql always overhead. I think it is very frequently, if some cron job mysql insert run, 5-10 minutes, the db ...
3
votes
1answer
291 views

Where are partitions defined in mysql

I have a large table that I want to spread across multiple disks/LUNs. It will contain blobs and I want to have it separated out from the other data. How do I go from new disks (lets call them ...
1
vote
1answer
222 views

MySQL user defined rollback procedure

I'm attempting to write my own mini-rollback procedure. I have a table that tracks any updates or deletes to another table using a trigger. I am attempting to make it possible to restore one or more ...
8
votes
2answers
13k views

Dynamic change to innodb_flush_log_at_trx_commit

This is related to this question. It does help to get better performance for InnoDB tables. According to MySQL manual, innodb_flush_log_at_trx_commit is a global dynamic variable. Thus, I can change ...
0
votes
2answers
595 views

microsoft webmatrix deleted my wamp databases?

I've been using WAMP server for a while and I have several databases on the mysql server. today I installed microsoft webmatrix and when I opened one of my sites on wamp I got a mysql error saying ...
13
votes
2answers
28k views

MySQL: Tree-Hierarchical query

SUB-TREE WITHIN A TREE in MySQL In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
1
vote
0answers
284 views

Database instance keeps restarting

From the past few days, our MySQL database instance has crashed and had to be restarted a few times. My MySQL version is 5.1.57-log. The CPU and memory usage was way below 50% during the crash time. I ...
4
votes
3answers
5k views

InnoDB insertion faster

I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
1
vote
1answer
227 views

Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date?

I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will ...
3
votes
2answers
186 views

Tablestructure for fast inserts/deletes with foreign keys

Current Situation We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product. Example: id | competitors | some infos about competitor | ...
8
votes
1answer
9k views

MySQL INNODB corruption after server crash during concurrent truncate command

My server crashed today I think due to a concurrent truncate table command on one of our INNODB tables. The server could be restarted, but after it starts up, everytime I try to issue an SQL command, ...
5
votes
1answer
290 views

Walking a BTREE index as far as possible in MySQL

Suppose one has a column of words on which one builds a BTREE index: CREATE TABLE myTable ( words VARCHAR(25), INDEX USING BTREE (words) ); LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO ...
3
votes
1answer
6k views

Get week/month data

There is a table (MySQL). It's simplified structure: day DATE val INT UNSIGNED I need the average val for: every week containing a date from the table; every month containing a date from the ...
3
votes
1answer
4k views

MySQL Slave Relay Logging but not logging Binary Log

I have a master slave setup both mysql 5.5, the setup is fine except that the slave won't Binary log to the standard mysql-binlog only to the relay logs. I fear this may be due to my lack of ...
3
votes
2answers
797 views

How to concatenate a column after x rows?

I have a table with columns object varchar(255), frequency tinyint(3) unsigned, I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as .. ...
0
votes
1answer
61 views

Grabbing data from a query with specific criteria, then the data around it

The title may be slightly vague... however, this is the basis of what I want to do: I have people in a database ranked on a statistic, for example, 'Kills'. I want to order the kills by highest to ...
2
votes
2answers
1k views

Slow query with straight_join for small result

I have a performance-problem with a query in my application and I don’t understand the behavior of mysql. The query consists of different joins, especially the join to the mentioncache-table. If the ...
3
votes
3answers
769 views

High insert rate on MySQL

I have a project at the moment that is close to be finished. I have been asked (at the very end) to log a specific event. The event will occur very often (easily a few times each 5sec). Each event ...
3
votes
1answer
174 views

Backup and off-site restore setup

I've been reading a lot of threads about different backup options for MySQL and was hoping for some more insight so I can settle on one. Our current situation is ~100 GB of InnoDB and MySAM data in ...
2
votes
1answer
478 views

trigger in MySQL to disallow multiple row deletions at a time on a table

How do we create a trigger in MySql to disallow deletion of more than 2 rows from (say) employee table by a single query? delimiter \\ create trigger not_more_than_2_rows before delete on employee ...
3
votes
1answer
1k views

How to design tables for players in a tournament?

I need help with my database table design. I have group which contains a list of players. In every group each player plays games with other players. Game has also some result for example 1:0 and ...
-1
votes
1answer
225 views

How do I terminate a running SELECT query without restarting MySQL?

I have a MySQL select query that is not getting killed. Please help! And we have load avg building up on this server. | 64446 | root | localhost | dast-india | Killed | 3758 | Sorting result | ...
4
votes
2answers
3k views

Large files in wamp\bin\mysql\mysql5.5.24\data directory

I have discovered these files located in the wamp\bin\mysql\mysql5.5.24\data folder on my Windows 2008 server. They are consuming about 22 GB on a 40 GB drive. I am using a LMS called moodle, that ...
0
votes
1answer
73 views

Getting a “type” column from each table in query

Let's assume I have 3 tables: videos, books, albums and I would like a query that gives me the 3 latest entries in each table, along with a column "media_type" valued Video, Book, or Album, ...