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

Issue moving database between servers (cannot find .frm file even though it's there)

I used MAMP on my local machine to make a little site with a mysql database. I moved all the site files over the to server. Great. The name of my database is roster and it has one table called users. ...
2
votes
1answer
218 views

Problem with the function UNIX_TIMESTAMP

Query 1 SELECT SessionInfo.IVRSessionInfoID FROM SessionInfo WHERE SessionCallTime BETWEEN UNIX_TIMESTAMP('2013-08-01 00:00:00') AND UNIX_TIMESTAMP('2013-08-01 ...
3
votes
3answers
6k views

How to partition a table by timestamp, where data from every i'th day goes to partition i?

I'm looking into partitioning a table in my InnoDB database. I have a column corresponding to a UTC timestamp, and I want to partition around that. The basic idea that I want to capture is a ...
2
votes
0answers
1k views

Mysql slow writes

i have a strange problem with mysql. My selects goes fast. My insert/create/delete/drop/update go very slow. I have done some tests on 2 machines. My old machine and my new machine. Old is worst ...
9
votes
1answer
21k views

Performance difference between MySQL and PostgreSQL for the same schema/queries [closed]

I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS. I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests ...
3
votes
1answer
3k views

User has no password set

I see these when running MySQLTuner.pl: -------- Security Recommendations ------------------------------------------- [!!] User '@debian' has no password set. [!!] User '@localhost' has no password ...
5
votes
1answer
11k views

Query to compare two subsets of data from the same table?

My problem: I'm trying to compare to subsets of data within one table, and I've got two methods that partially work and one certainty that there's got to be a more correct way to do it. The idea ...
0
votes
1answer
38 views

I have to create INDEX but which? [closed]

I have to create INDEX but which with this query ? SELECT * FROM TABLE WHERE A = 1 AND B = '5427dbfac0a8280d74bc672c72636a4f' AND C = 'BIL' AND D = 'B' and E = '01' GROUP BY F,G,E,H,I ORDER ...
3
votes
1answer
183 views

What if I want to keep using MyISAM

In my current use of MyISAM the databases are extremely small and kept for a few weeks after which time they are deleted and completely rebuilt with a clean slate. The script uses MYISAM tables and ...
1
vote
1answer
527 views

MySQL query optimization with aggregate ORDER BY

I have an analytics table that stores metrics aggregated the day level. I need to ask questions such as "For a given date range and client, what are the most popular items." The schema is as follows: ...
2
votes
2answers
938 views

Viewing MySQL Account Resource Limits

Is there any way of viewing an account's remaining resources that are allocated to it? I setup an account that's allowed 7200 queries an hour. At any point, could I then run a query to find out how ...
0
votes
2answers
160 views

Backup certain tables in MySQL [duplicate]

Is it possible to take backup of certain table in mysql? Suppose I have 10 tables, and I want to take a backup of 8 tables through one command. How can I do this?
0
votes
0answers
147 views

Need advice on this EER Diagram

This db will work with a CakePhp app and need advice about design. Is it good idea to put orders on center or should I put clients on center? Clients has orders and an order record can only have one ...
3
votes
1answer
3k views

Why can't I change the isolation levels of my connection in MySQL?

I am trying to experiment a bit with the isolation level of MySQL in a test environment. I do the following: mysql> set @@session.tx_isolation='READ-UNCOMMITED'; ERROR 1231 (42000): Variable ...
0
votes
1answer
122 views

Is it possible to have extra tables in a Slave with MySQL Replication

As my title mention I have a Master and a Slave database. Master if for operations data and my slave mainly for reporting stuff. The issue is that I need to create extra tables on reporting that ...
0
votes
1answer
53 views

Best way to display number of overspent projects [closed]

I have the first view displayed as: CREATE VIEW Projects AS SELECT DepartmentName, COUNT(*)NumberOfProjects, OfficeNumber, Phone FROM DEPARTMENT AS D JOIN PROJECT ...
2
votes
1answer
3k views

Optimizing multi-table left joins

I have four tables which I am trying to join together. Table event +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
3
votes
2answers
998 views

Extract query fingerprints and tables from large log files using pt-query-digest

I tried to parse a large log file using pt-query-digest but it gives "Out of memory!" when I try to read the file. I have 3GB RAM, 40GB empty space on HDD, the file is 20GB. I did not found anything ...
4
votes
3answers
8k views

Issue after moving the ib_logfile1 and ib_logfile0 files

I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of ...
1
vote
2answers
55 views

Indexing a reference column

Say I have a reference table like languages with only 20 records. Is it worth indexing a field like language_code (e.g. en, es) that's frequently used in queries? I'm wondering if an efficient server ...
1
vote
1answer
427 views

How can I verify that MySQL's slave_compressed_protocol is working

I've set slave_compressed_protocol=1 on both my master and slave. Using SHOW VARIABLES, I can see that it's set. How can I check that it's actually working?
0
votes
1answer
161 views

How to drop a column named 'column'? [closed]

I need to delete a column named "column" form a table but the standard: mysql> ALTER TABLE table_name DROP COLUMN 'column'; returns: ERROR 1064 (42000): You have an error in your SQL syntax; ...
1
vote
1answer
79 views

Where Mysqldump gets data from

How mysqldump is processing backup? From which all files is it reads data for creating dump? Is the process differs if storage engines are different?
0
votes
1answer
262 views

Can I speed up this slow query which should be limited by MIN/MAX values?

The intention here is to look at the user retention over a period of time. My current solution employs a temporary table, since I do this over several ranges. CREATE TEMPORARY TABLE ...
2
votes
1answer
484 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 ...
0
votes
1answer
157 views

Using an enum index to limit records

A common issue, just not sure on the keywords. I have a table of assessments (14k+ records). Each assessment can be in many different states ('draft', 'proofing', 'amends', etc... and 'done'). Most ...
1
vote
2answers
3k views

Order by and then Concat

I have a table table1 like: col1 col2 A1 A2 A1 B1 A1 B2 A2 A1 A2 B1 A2 B2 B1 A1 B1 A2 B1 B2 B2 A1 B2 A2 B2 B1 I need to concat col1 and col2 such a way that it ...
1
vote
2answers
3k views

mysql… compare and match items in same column

I have a column full of server names. For example; LouServer01 VgsServer01 LouServer02 VgsServer02 I need/want to be able to create a query that can match those two names based on everything after ...
0
votes
1answer
148 views

Unify case when changing fields values

I have the following code: $sql= 'select *,'. 'case when fisicas_cpf is not null then null else razao_social end as razao_social,'. 'case when fisicas_cpf is not null then null else ...
0
votes
1answer
488 views

MySQL : LEFT JOIN not fetching required data

I'm using left join in MySQL as mentioned below: SELECT Q.QUESTION_ID, school_id FROM Q_MASTER AS Q LEFT JOIN ( SELECT keyword, school_id FROM ...
0
votes
1answer
340 views

MySQL 1366 Error on Replication Slave

I've got two pairs of servers (each identical hardware and OS) with replication running on both pairs. Running MySQL 5.5.34 on Windows Server 2008 R2. I'm trying to track down a problem that has ...
0
votes
3answers
6k views

COALESCE returning NULL when there are no records

When running the following query on a device_id that has no entries in the inv_zfs_replication table the result is a NULL value. How can I make the 2nd sub-query return a zero if there aren't any ...
0
votes
1answer
5k views

Why should I use InnoDB and MySql instead of XtraDB and MariaDB? [closed]

Why should I use InnoDB and MySql instead of XtraDB and MariaDB, except that InnoDB and Mysql happen to be installed by default on my servers? Why isn't MariaDB and XtraDB installed by default ...
2
votes
1answer
814 views

Experience using ScaleArc in test or production?

Has anyone had any experience using ScaleArc? My CTO has asked my thoughts on it, and I have seen no information out there regarding real-world experiences.
9
votes
2answers
10k views

Why does MySQL allow HAVING to use SELECT aliases?

In SQL, as far as I know, the logical query processing order, which is the conceptual interpretation order, starts with FROM in the following way: FROM WHERE GROUP BY HAVING SELECT ORDER BY ...
1
vote
1answer
109 views

How and where do I upload a mysql database?

I am new to Ubuntu (server edition) and mysql. How do I upload (import) an existing database with a MYSQL or (mysql.zip) extension that was downloaded from a web host (cPanel?) I do not have any of ...
0
votes
1answer
2k views

Why is MySQL database data-files deployed under /var/lib/mysql?

I was wondering why do we deploy data files under /var/lib/mysql/. Is there a logical reason for that, or is that just a "traditional" place for the data? I will value more answers based on standards ...
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 ...
60
votes
5answers
6k views

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
0
votes
2answers
674 views

What am I doing wrong in mysql database?

I'm creating a trigger that deletes rows from a table when they are inserted. However, when the trigger executes I get the following message: MYSQL ERROR: Can't update table 'player_viplist' in ...
1
vote
2answers
854 views

How can I return column names in dot notation in MySQL?

I want to return column names into a table. But I also want to avoid redundancies like 'id' and 'id'. I did: INSERT INTO columns(name) SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE ...
3
votes
1answer
2k views

AWS performance of RDS with provisioned IOPS vs EC2

Has anyone done a performance comparison of AWS RDS with the new provisioned IOPS vs EC2? I've found plenty of non-high IOPS RDS vs EC2 but nothing with the new high IOPS feature in RDS.
0
votes
1answer
127 views

How to reduce table duplication, while ensuring integrity of the database?

For example, in my app I have some class called Contact: case class Contact(name: String, email: String, phone: String) And several other classes have one-to-many relation to that contact (for ...
1
vote
1answer
436 views

How to UPDATE a table for the level of parents in a column?

I have a table with internal parents as id parent parent_level 1 NULL 1 2 1 2 3 1 2 4 3 3 where parent has a FK to id. How can I update the table ...
0
votes
2answers
61 views

Guaranteeing a FK relationship through multiple tables

I'm using MySQL / InnoDB, and using foreign keys to preserve relationships across tables. In the following scenaro (depicted below), a 'manager' is associated with a 'recordLabel', and an 'artist' is ...
5
votes
1answer
2k views

Best strategies to have a backup of an RDS MySQL database

I have an AWS RDS MySQL database. I'm not a DBA, so my knowledge is limited. I wish to design strategies to have zero data loss in case of a failure, in terms of replication or data backup. I know ...
0
votes
1answer
644 views

Index for ORDER BY on aggregate in MySQL

I'm working with the following query: SELECT *, SUM(entries_count) AS entries_count FROM leads WHERE promo_id IN (1, 2, 3) GROUP BY user_id ORDER BY entries_count DESC LIMIT 25 OFFSET 100; It's ...
3
votes
0answers
2k 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
285 views

Is there a way to achieve Workload Management in MySQL?

Is there a way to set up Workload Management in MySQL (in particular v5.5 on AWS RDS)? By Workload Management I mean the functionality implemented in e.g. Teradata, RedShift that allows ...
4
votes
1answer
5k views

Can I reset the ibdata1 and ib_logfile files when innodb_file_per_table is already in place

I am running into an issue where sensitive data was not encrypted properly at the application level of our production system. Even though the guilty records have been removed or corrected, scanning ...