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

Mixed based replication between two databases on the same server

I have two apps with a current configuration as follows: App1 DB on server A/MySQL Instance A App2 DB on server B/MySQL Instance B Students can apply for internship using App1 and those ...
0
votes
1answer
9 views

Could I set my InnoDB MySQL Server session state for 1.) SQL_SAFE_UPDATES and 2) INNODB_FLUSH_LOG_AT_TRX_COMMIT using MySQL Workbench?

Could I set my InnoDB MySQL Server session state for 1.) SQL_SAFE_UPDATES and 2) INNODB_FLUSH_LOG_AT_TRX_COMMIT using MySQL Workbench's SQL Query window or mMYSQL Connection Navigator instead of in a ...
0
votes
0answers
4 views

How to add a trigger on table create in Mysql

I want to apply a trigger on all tables if my database. I already created a script in PHP which will add triggers in all existing tables. But if any new tables are added in database, I have to run ...
1
vote
1answer
26 views

Between optimizing a 1-billion rows table and dump-restoring it, which would be faster?

Have multiple tables which had more than 1 billion rows. Archived and deleted many of them, now want to recover the disk space. Have option to either optimize the table, or dump the table and restore ...
0
votes
1answer
38 views

Index on integer and string fields

A table has about 300M records. Each has an unique ID that starts with 13 to 14 digits (with the first digit on the left being either 1 or 2), and is optionally followed by a string of up to 7 ASCII ...
3
votes
1answer
5 views

Checking for fields not on SET clause on before update trigger on MySQL

I want to set a before update trigger that sets a column to a given value if an update query is done and that column has no new value on the set clause. The table I'm using. (Sort of) DROP TABLE IF ...
0
votes
0answers
15 views

MySQL Errror “Cannot Connect Invalid Settings”

I was working with mysql using phpmyadmin on a local server and when I clicked on a table it took a very long time to do anything so I let it do its thing, I walked away and when I came back I had the ...
5
votes
1answer
373 views

Deadlock free MERGE-alike in MySQL?

I am trying to implement some parts of MERGE in the MySQL driver in Drupal. Of course, Drupal has something but in truth it only works because the most frequent MERGE issuer just eats exceptions. So, ...
0
votes
3answers
146 views

9 node percona xtradb cluster

We are looking to launch a 9 node percona xtradb cluster on Amazon where each ec2 node uses 20k iops disks. I was afraid if a 9 node cluster will work and mostly what I see is 3 node clusters. We ...
1
vote
1answer
138 views

Optimizing intersect query in MySQL

I'm facing a performances problem in our MariaDB server. We have a table linking users to pages, a user belongs to 1+ pages. I have a page that have users and I want to get the 10 best pages based ...
0
votes
0answers
5 views

Cannot Connect to Database Server; Can't get 'MysqlSqlFacade' module

I've installed MySQL Workbench 6.3.4 on Sabayon 16.02 from Rigo, and whenever I try to connect to root account created previously, the following error appears: Cannot Connect to Database Server ...
1
vote
0answers
40 views

Is there a way to throttle MySQL requests to mitigate DoS traffic?

I've a server with ~200 wordpress websites hosted. When Google Favicon bot pays us a visit it searches all the sites indexed on our IP, that means it's requesting ~200 favicons. Most of the sites ...
1
vote
1answer
142 views

Can having a LIMIT on DELETE degrade performance dramatically?

I have a database project in php and mysql. For, hopefully redundant, data security I added a LIMIT 1 to the delete($uniqueID) php function we use. Nobody cared because records really aren't ...
2
votes
2answers
110 views

Is there any reason not to use foreign keys in MySQL?

I've never really used foreign keys before but I am building a project where they could come in handy. I have looked at some other large software such as some forum systems and they don't use foreign ...
2
votes
1answer
9k views

MySQL got signal 11 error and slave is down

My slave server is down and mysqld unable to start. What should I do? Below follows the error logs: 2014-09-26 12:51:53 14166 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11 2014-09-26 12:51:53 ...
8
votes
2answers
11k views

Taking MySQL dump from another machine

I have a MySQL db on Machine A and I want to access the mysqldump from machine B. Both machines are running on Linux and I also have the credentials of both, but I can't access the mysqldump. What ...
4
votes
2answers
3k views

Trigger in MySQL to prevent insertion

I want to create the trigger which prevents insertion in the reservation table of flights (numflights) which does not exist. Tables ---------------------------------------------- flights(numflight, ...
8
votes
1answer
8k 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, ...
8
votes
2answers
9k views

MySQL equivalent of WITH in oracle

Is there a MySQL equivalent of the WITH clause in Oracle?
1
vote
1answer
211 views

“Remote” connection to a local MYSQL database on LAN

I have a mySQL (5.5.38) server running on my desktop (windows 7), I can connect just fine into any of the servers Users, and skip-networking is off when checked via show variables. I am trying to give ...
1
vote
2answers
17 views

Interested in counting number of threshold crossings

I would like to determine the number of times that a value has crossed a threshold in either direction in a specified time frame. As an example, lets say that I am interested in the number of times ...
1
vote
1answer
209 views

Can't see temp table from subquery

I have a query that selects data from tables and one temp table. This works fine, but there is a GROUP BY/ORDER BY combination that doesn't work well for me. Indeed, I need to order the results and ...
2
votes
1answer
25 views

Passing multiple value for a parameter in my sql

I have written the query below: SET @City = 'London,Lyon,Kln'; SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@City, ',',@r:=@r+1),',',-1) City FROM (SELECT @r:=0) x, (SELECT 'x' xx UNION SELECT ...
0
votes
2answers
23 views

ulimit 'open files' and MySQL max_connection

When running mysql on SSD, does max_connections, if set more than the kernel's ulimit open files, be limited to the kernel's limit? My understanding is that if the kernel's ulimit open files is 1024 ...
2
votes
1answer
39 views

Can I use an index to optimize select clause, versus where clause?

I'm not sure if this question has been answered, because I'm not sure of the proper wording to describe what I need. So here it is: I am trying to optimize a database that is running very slowly, ...
0
votes
1answer
508 views

MySQL: Transaction and Deadlock

I have table "tbl_data", where have a lot of rows (3 - 5 million). I have JAVA (*.jar) module from which I INSERT/UPDATE data into this table. If I start *.jar file (for example, "run.jar") and after ...
0
votes
0answers
6 views

mysqlworkbench nasty result grid with text columns

On MySQL Workbench 6.3, when you query a table with text columns that have carrier return \n in it, the Result Grid expands record's height accordingly. In the image, I've reduced the height of the ...
3
votes
2answers
114 views

View active MySQL connections per user

I need a query that will give me the active or open number of connections to a certain database for a certain user. I have been searching for hours and have come up with nothing so far. I am close, so ...
2
votes
2answers
186 views

What is more efficient: text (765) or varchar (765) to store a freeform searchable description?

EDIT : To paraphrase quesiton - at what point do I switch from varchar to text and back? A little background on the question. Let's say we have sales_orders table and we want to be able to keep a ...
0
votes
2answers
356 views

Percona XtraDB - Random deadlock

Randomly my database gets locked completely and queries hang and they pile up until it gets "Too many connections". Below is my mysql log file, I couldn't find anything related to the error system ...
0
votes
2answers
21 views

Spanish characters saved incorrect in Mysql

I have googled this already, but I couldn't find what's wrong in my situation. I have a wordpress site that has a lot of non-english users which type in words such as "español" it is printed ...
0
votes
1answer
110 views

Debugging lock time out with show engine innodb status

I am keeping getting this lock wait time out. Below is my current settings. [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to ...
1
vote
1answer
107 views

Finding out the exact size of the database after inserting records?

I have this query: SELECT table_schema "db.name" , Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; Does this ...
2
votes
1answer
130 views

innodb , mysql Indexes difference when adding index

When I am altering one of my MySQL table to add an index I am getting below error in error log. Table a/b contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined ...
1
vote
1answer
28 views

Does varchar(n) affect the number of records which can be stored per page even if you store less than n?

I'm struggling to find enough documentation to help me answer my own question. The common story seems to be that it doesn't make a difference to actual database size when you use a much higher value ...
0
votes
2answers
107 views

Please recommend changes for my.cnf

I am not having any performance problems per se but want to improve my sites' performance if possible. Would appreciate any advice for how I can improve my.cnf. I have just one server, not ...
1
vote
1answer
123 views

Fastest engine for Mass Updates (only updates done on table)

I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the ...
1
vote
2answers
2k views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
14
votes
2answers
39k views

How to grant super privilege to the user?

I have created a user and given privileges to the user1. `grant all privileges on db1.* to user1@'%' with grant option; Am using mysql workbench to import dumps to my database. While importing ...
7
votes
1answer
158 views

Data Warehouse: How can i query daily snapshots?

I have some snapshots of a database that are not timeseries. For example: Snapshot day 1: +----+---------------+------------+------------+ | ID | Title | Category | Date | ...
0
votes
0answers
37 views

MySQL 5.6 Performance Tuning Issue

I have read and try lots of things about MySQL optimization and tuning. I tried most of them one by one according to our needs but nearly I got nothing. I am doing my test using some simulation ...
1
vote
1answer
33 views

MySQL Database Instance size

We found 3 different size of a MySQL Database Instance as follows: Mysqldump output Data directory size (even if we exclude size of bin-logs,error-logs) SUM (data_length + index_length ) ...
2
votes
2answers
41 views

Is better to create a table for each room or make a single table that handles all the rooms in my case?

I need to make a server-side for iOS and android application that does something similar to chat(Is not really a chat), and I'm a PHP and MYSQL newbie. The process is that 2 users join a room and do ...
2
votes
3answers
56 views

Remove duplicates in sequence based on keys

I am looking to remove duplicates on a row by row comparison based on keys Member and Group and data sorted ASC on Member, Group and StartDate. Just return the records in the select query and not ...
1
vote
0answers
34 views

InnoDB AUTO_INCREMENT resetting itself without MySQL restart

We've recently noticed the following scenario happening in many of our databases: A record is inserted, and receives an AUTO_INCREMENT primary key. The record is deleted. Another record is inserted, ...
1
vote
1answer
132 views

MySQL Workbench can either Dump or Restore - How Do I Configure To Do Both?

I have previously posted this question on Stack Overflow, but have not solved the problem. Hopefully it is not incorrect to post it here in the hope that it will be seen by a some different ...
0
votes
0answers
17 views

Access a remote mysql from a remote node application

I'm approaching MySQL for the first time in life. I have a machine with a MySQL and Wordpress environment correctly working (let's say at 1.1.3.4) but I want to access it from a remote node.js ...
1
vote
2answers
108 views
+100

Mysql 5.5.45 crashing after refreshing web page rapidly(Too Many Connection warning)

I am using ec2 aws instance for my wordpress webiste.after every 5 to 10 minute and randomly my website going down i tried lots of things like increasing buffer memory ,memory limit but that not ...
2
votes
2answers
337 views

Replication with only update and insert

I have set up my replication but I want to delete some outdated data from my master tables. Is it possible that say if I have 10 records at master and I delete 5 of them but I want those 10 records to ...
3
votes
5answers
324 views

How do I prove that the act of removing foreign keys doesn't corrupt existing data?

TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...