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
83 views

Fixing a MySQL query that miscounts cases where count is 0

I have the following SQL code: SELECT widgets.campaign_name, widgets.enddate, widgets.active, widgets.id_widget,COUNT(*) AS entries FROM widgets LEFT JOIN entries ON widgets.id_widget = ...
1
vote
1answer
457 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
720 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
0answers
522 views

How to increase MySQL performance with joins

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below: SELECT t1.`ARTREF1` FROM `lijnen` AS t3 INNER JOIN (`kortingen` AS t2, `...
3
votes
1answer
4k views

Lock a MySQL database for upgrade

I've looked around for solutions to lock a whole database, and the only one I found so far is FLUSH TABLES WITH READ LOCK. This is fine for backups, but unfortunately I can't use that to prevent ...
0
votes
1answer
245 views

retrieve data based on a condition

I have 2 tables, TA and TB: TA -------- id name 1 a 2 b 3 c 4 d 5 e 6 f TB -------- id p_id name 1 1 a 2 1 b 3 1 c 4 2 a 5 2 b 6 2 d I need : if ...
2
votes
1answer
295 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 ...
1
vote
1answer
71 views

How to sort the result based on a certain column when using GROUP BY in sql statement

I am new to this, so please be patient... I have a table "tFruit". The fields inside tFruit are "ID", "Buyer", "Seller", "Time", and "FruitName". "ID" is the key field, the other 4 fiends has non-...
0
votes
1answer
2k views

Percona MySQL 5.5.33 not reading my.cnf?

At some point Percona MySQL on CentOS 6.4 seems to have stopped reading my.cnf, possibly after MySQL updated to 5.5.33 a few days ago. Selection from /etc/my.cnf: [mysqld] # GENERAL # user ...
2
votes
0answers
128 views

Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?

Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION. If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, ...
3
votes
1answer
789 views

Using the auto increment id from one query as the fk of the next insert

I'm not sure if I'm overcomplicating this or whether I can't see the wood for the trees. I have two tables, both have auto incrementing ID's. Table 1: ID - auto field - int other field - varchar ...
2
votes
2answers
3k 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 ...
0
votes
1answer
365 views

MySQL Table rename and Grants

We are in the process of spring-cleaning our production MySQL 5.0 database, and this includes the renaming a number of tables. We are using the following syntax for the rename ALTER TABLE `mydb`.`...
1
vote
1answer
690 views

MySQL command querying all MyISAM database

I would appreciate if anyone could help me of what's the proper command in MySQL to query all database for having "MyISAM" engine. I need to convert all DB & tables from MyISAM to InnoDB.
-1
votes
2answers
5k views

How to get missing values in row order?

In a table, many rows have been deleted, how to get the id of missing rows for next INSERT? For example id col1 1 1 3 3 4 4 5 5 8 8 9 9 how can I get the ...
4
votes
1answer
272 views

Probable circular dependency, but query still runs

I built this ER diagram in MySQL Workbench and it will run and generate the database. My concern is that the relationship between the employees, departments, positions, and employment_statuses is a ...
1
vote
1answer
64 views

Do regular ALTERs weaken the performance?

In a project, we need to frequently ALTER the tables to delete or add columns. I wonder if this weakens the database performance in long term. If it has a considerable negative impact, what is the ...
0
votes
1answer
741 views

Install MariaDB and MySQL in CentOS 6 32bit in same server

I'm trying to install MariaDB with MySQL in the same server but got this errors: Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package MariaDB-client.i686 ...
0
votes
1answer
1k views

Why does max_connections in my.ini not match concurrent connections in Mysql instance config wizard?

MySQL 5.1.x | Windows Server 2003 Can someone please clarify why max_connections in my.ini is much larger than the manual value I specify in the Concurrent Connections Dialogue? For example, if I ...
1
vote
1answer
171 views

MySQL SUM related to parent query

I'm trying to put together a query to aggregate some information into a view, and am having trouble working out how to get that data I need from a couple of different tables. I'll start by describing ...
0
votes
2answers
102 views

Update on cascade

I have two tables: service (id_service, libel, status) agent (id_agent, name, surname, id_service, status). The status field values ​​present in both tables have only two values ​​0 or 1 (0 = ...
2
votes
2answers
140 views

Optimize query or model, to get an population overview in time

I think I've got a very simple model and query, but I cannot find a way to make use of indexes to boost performance. I wonder if there is a trick in my model, or MySQL query, to speed up the ...
4
votes
1answer
308 views

Table design problem

I'm looking for a SQL design solution for the following problem. The idea is that I have a validation form that contains the client's data. Each client can log in to his account and edit his data. ...
1
vote
1answer
545 views

Measuring SQL execution time in PostgreSQL?

MySQL has a performance_schema database that allows one to capture SQL statement execution time data in a table (e.g. performance_schema.events_statements_history_long ; useful MySQL link). I was ...
0
votes
1answer
963 views

How to avoid duplicate entries with SELECT statement?

+----+----------------------+---------+---------+---------------------+-------+ | id | translation | id_word | id_user | added | tuser | +----+----------------------+---------+--...
2
votes
5answers
26k views

Specified key was too long; max key length is 1000 bytes in mysql 5.6

one of the application server is internally creating the database on my mysql but when ever the following create table command gets executed CREATE TABLE ofRoster ( rosterID ...
0
votes
1answer
813 views

How to update a table by count from another table?

I have two tables as CREATE TABLE country_statistics ( id int(11) NOT NULL AUTO_INCREMENT us int(11), uk int(11), ca int(11), PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CREATE TABLE entries ( id int(11)...
0
votes
1answer
102 views

Multiple values for unique id? [closed]

I have two tables: customer and boxes in one-to-many relationship. The structure of the tables is in addition : Customer table : id#| name | date_purchased | address | 1 John ...
3
votes
0answers
1k views

Getting distinct list of records based on the MAX of a column

I have a status table (tblTestActionStatus) which has three columns ID_TestAction: references some test action ID_Status: a look up table reference for the variety of possible statuses for the ...
0
votes
0answers
42 views

How to optimize this query more?

First at all I am nood into SQL thing, Now I am working on a class project where I have some tables like Table user user_id | username | name 1 | nihan | Nihan Dip 2 | dip ...
1
vote
2answers
174 views

Is this compound index unnecessary?

I have a large table which contains sensor data, along with the fields: sensor_id timestamp I do queries against it using these fields almost exclusively. There are multiple sensors, and different ...
0
votes
1answer
180 views

Easily, quickly replace MySQL table?

We have a process which will regenerate a table from scratch, drop the original table, and move the new table into the original's place. Now this should happen very quickly, but sometimes a website ...
2
votes
1answer
3k views

MySQL: sysbench test - InnoDB vs Memory tables

I've done some tests in order to investigate performance issue on the new HP Gen8 server (Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz) I've created two tables, first one is using InnoDB storage engine ...
-3
votes
1answer
78 views

How can I move one table from one database to other database [closed]

I want to move one table in one database to other database using mysql dump on linux terminal How can I go about it ?
2
votes
2answers
2k views

What are reasonable privileges to grant typical users? [closed]

I find the list of privileges provided by MySQL to be a bit overwhelming. I'm not sure who should have what privileges. In my mind there are three typical users for my situation: root developer ...
2
votes
2answers
774 views

How to do Partition for MySQL 100GB table in zero downtime?

I have a Innodb engine table with 100GB,I have a planned to do partition the table.Can anyone tell me how to do it in zero downtime? In 100GB table the records are increasing for each and every ...
0
votes
0answers
246 views

Query Execution Plan for MySQL

In Microsoft SQL Server you can see Execution Plans for queries to see how a query is executed, what joins take the most time and see any missing indexs now i want to do the same for MySQL scripts ...
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' ...
1
vote
1answer
100 views

MySQL hang during “show collation” on Amazon RDS

MySQL is hanging when I try to do SHOW COLLATION on my Amazon RDS instance. I can use SHOW COLLATION fine with my local database, and other SQL commands work fine on my RDS instance. But Amazon SHOW ...
1
vote
0answers
330 views

MySql Very Slow Writes & Out of memory error

I am having a rds db instance in AWS which is used by Drupal 7 instatllation. I am getting the below error messages in the log: > 130912 11:57:39 [ERROR] /rdsdbbin/mysql/bin/mysqld: Out of memory &...
2
votes
0answers
90 views

designing mysql for large tables

Currently I've been assigned the task to research ways to speed up an existing project. It's going to be rebuild so we can start from scratch, the current implementation has been build by another ...
5
votes
5answers
3k views

How to securely connect app and database servers?

(Updated) For a start, I have my app and database on separate servers. It's easy to connect them, except I am not sure how to secure my database server. Here's what I've already done: Ran ...
1
vote
1answer
253 views

I need advice on these queries

could you help me optimize these queries? here are queries, please note that you see something like OR 1=1 OR those are values I pass via php and may change. query#1: SELECT posts.status, posts....
0
votes
1answer
4k views

Seting timezone per database in MySQL

I wanted to know if MySQL let the admin set a timezone per database ? I have three different databases for three different customers. Each of them being in their own timezone. I would like to load ...
1
vote
1answer
143 views

Create user for alternate port

I am running a private MySQL instance on WebFaction WebFaction is a shared host who provides both for their public MySQL instance and for your own private MySQL instance. The private instance is a 1-...
0
votes
1answer
5k views

Find the size of each index in a MySQL table

I am trying to figure out size of each index of a table. SHOW TABLE STATUS gives "Index_length" is the summation of all the indices of the table. However if a table has multiple indices (e.g. in an ...
1
vote
1answer
62 views

What ways are there to limit the processor / ram requirements of a specific query?

Every once in a while, I need to run some very expensive query on a production server. Think spaghetti joins. This is likely to affect the server operation, and I don't want that. What ways are there ...
0
votes
2answers
4k views

bulk_insert_buffer_size and InnoDB

I read the following on this page http://dev.mysql.com/doc/mysql/en/server-system-variables.html bulk_insert_buffer_size MyISAM uses a special tree-like cache to make bulk inserts faster for ...
2
votes
1answer
639 views

Deleting 200M+ rows from InnoDB table

Basically, we have a table with the following structure: CREATE TABLE `reqs` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `reqid` BIGINT(20) NOT NULL, `from` BIGINT(20) NOT NULL, `to` ...
1
vote
2answers
209 views

How to normalize data en-masse?

Goal: Extract address fields from a users table into a separate addresses table linked by foreign key. I have created the structure below, and have millions of rows to deal with. I need to do this ...