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

0
votes
1answer
2k views

How do I change the MySQL data folder path in a Mac?

How do I change the MySQL data folder path in a Mac? I am running OS X 10.10 & MySQL 5.6 In Windows I can edit datadir in my.ini For Mac, mysqld does not have parameter for --defaults-file=
0
votes
1answer
8 views

MySQL database has way too many connections

I have a MySQL version 5.7.12 database that has 4 applications running against them. Two of these applications are single webservers running Rails apps. The other two applications are also Rails ...
0
votes
1answer
19 views

Some DB's don't have primary key, how can I fix this issue?

I have the following message when runnning myqsltunner.pl in past this was not present, how can I solve this issues? -------- Galera Metrics ------------------------------------------------------ ...
0
votes
1answer
310 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
-3
votes
0answers
37 views

Get user rankings based on points from database [on hold]

I've created a website where members collect points to get a reward at the end of the contest. I want to create a page that shows the top 10 members who have the most points. I've tried a few pieces ...
2
votes
0answers
44 views

Database design for auto-incrementing, grouped, unique hashes?

I've come up with a (hopefully) better way to save unique company data, which now consists of unique hashes for each entity. Basically, there are multiple hashes associated with a company: hash1: ABC ...
0
votes
1answer
5 views

Setting sql_slave_skip_counter is not working on Percona 5.6.24

I am currently running a master - slave replication where on my slave there is an sql error due to which my replication was stopped. when I tried to run the following good old solution SHOW SLAVE ...
0
votes
1answer
583 views

my.ini configuration for a huge server

I have developed a web based app in PHP MySQL and Flash Builder. It was running fine until a few days ago when the client began to connect a lot of users. Then the app started to freeze and sent ...
1
vote
1answer
22 views

ERROR 1091 (42000): Can't DROP 'PRIMARY'; on “mysql_upgrade”

I updated MySQL from 5.5.50 to 5.6.31 on a Centos 6.8 box with Plesk 12.5.30. Using those steps. The upgrade was successful but I ran into trouble with mysql_upgrade. I had to rebuild the permission ...
0
votes
0answers
12 views

MySql - Update the defualt table schema on production server

The story starts with a MySql table called table on the production server: As you can see, I f***ed up when specifying defaults - 0000-00-00 00:00:00 is not a valid MySql datetime. I need to ...
2
votes
2answers
388 views

Java Spring application Database triggers auditing - how to provide the right user that made the change

We are developing a new web application. one of the most basic requirement is to audit all entities changes into a separate table. We would like to use DB triggers for that purpose. We use MySQL as ...
0
votes
1answer
21 views

How to restore MySQL databases from database files?

Our MySQL on Ubuntu 15.04 stopped working after we moved the computer to a new location and kept giving the missing socket file error. We couldn't really fix it after trying sudo apt-get install -f ...
4
votes
1answer
711 views

Stuck in “query end” status every 5 minutes

I'm sick of seeing data in the slow query log, so I decided to check out what the fuss was all about. An UPDATE query that is running up to tens of times per second is sometimes really slow. Here is ...
0
votes
1answer
12 views

In MySQL how exactly data flows from query to disk?

I would like to have clarity on how exactly data flows from the the time of firing a query till it goes to disk. (I have rough idea it will go to buffers, then to redo logs then disk files) but then ...
0
votes
3answers
527 views

MySQL - Alter Table Fails As Workbench Tries To Create Existing Table

I have been trying to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as it fails attempting a 'CREATE TABLE, which it ...
0
votes
0answers
5 views

MySQL - Move data between partitions aka re-partition

I have a mysql table whose partitions look as below p2015h1 - Contains data where date < 2015-07-01 (Has data from 2016-06-01. Hence only month worth of data) p2015h2 - Contains data where date &...
4
votes
1answer
1k views

SQL set profiling

I'm setting up an automatic database backup for a website. When I try to do a backup it comes up with the error" MySQL error: The 'SHOW PROFILE' feature is disabled; you need MySQL built with 'enable-...
2
votes
1answer
149 views

Why is innodb strict mode not enabling?

I tried adding this line in /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu) innodb_strict_mode = 1 This is within the [mysqld] section, where I have seen this line in other examples. I know this config ...
1
vote
0answers
15 views

MySQL lost connection during query after specific record

I started getting the usual Error Code 2013 Lost connection to MYSQL server during query at a random moment when working on a PHP script. Some stuffs in the software still works, but the specific ...
1
vote
1answer
23 views

mysqld is terminated because the system is low on memory; what to do?

I did recently a site migration; during that I had to erase all plugins and the reinstalling only the minimum as the site was causing the server to crash (A Vps with 1gb of ram), the most problematic ...
1
vote
1answer
751 views

MySQL: SELECT GET_LOCK slow/timeout

I am using "Zebra Session" as a replacement for PHP's built-in session management. Zebra Session stores sessions in a MySQL database, and uses "session locking" in order to ensure that data is ...
1
vote
1answer
12 views

Trigger not functioning correctly in MYSQL

I am trying to set up a trigger to get it so if a new entry is either null or set to '000-00-00 00:00:00', that it will set the current datetime (NOW()). I can get the trigger to set the datetime if ...
1
vote
2answers
75 views

High CPU usage from sorting result queries

I have two machine have two master-master mysql replicate, only problem i have that i got massive queries like "sorting result" queries and i don't know how to fix these queries . mysql process cpu ...
2
votes
1answer
17 views

Is DROP/ADD UNIQUE constraint in ALTER TABLE atomic?

I have a UNIQUE constraint of a number of fields on a table. Image that I want to change the fields in this constraint (remove one field). Normally I would do something like: DROP INDEX unique_name ...
3
votes
0answers
50 views
+50

MySQL locking in Duplicate Key Error

From the docs: If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to ...
0
votes
1answer
321 views

Unresponsive database, sleeping processes

my mysql database suddenly became unresponsive: OperationalError: (2003, "Can't connect to MySQL server on '<IP_address>' (110)") After killing the processes: +-------+-------+---------------...
3
votes
2answers
3k views

More than 20 columns in database table MySQL

I have an object, that is described by 40 properties, is it good a idea to create a table with 40 columns? I think that I cannot break these properties into another table (such as table_properties_1 ...
2
votes
1answer
51 views

How to identify consonant/vowel pattern matches in MySQL?

Evening all, I'm having a bit of trouble with a MySQL table full of vocabulary words today. I need to be able to quickly retrieve words with the same arrangement of consonants and vowels, so my ...
1
vote
2answers
74 views

Help combining two MySQL queries to improve performance

I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query: SELECT d.field_id_105 AS code, title FROM ...
3
votes
1answer
133 views

SQL Freezing on Temp Table Creation

I have done an update to my server yesterday to allow for tracking of user page views. These page views are sent to a database where it records their ip address, time and which page was viewed. On ...
2
votes
2answers
44 views

Importing from CSV file, is crashing mysql server

I am facing a problem with importing large data into single mysql table. Basically I assume it is happening because limited machine resources but, I am having troubles to proof it. this is my CSV ...
0
votes
2answers
141 views

MySQL - installed version 5.5 but mysql --version says 5.0.95

I'm running CentOS 5.2 and upgraded MySQL to 5.5. When I run mysql I get this: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.5.45 MySQL ...
1
vote
2answers
2k views

mysql replication - missing table for foreign key on slave

i have the following situation: We're running a simple master slave replication with table filters, so the slaves doesn't have all the master tables. Master has table A with FK to table B Slave has ...
1
vote
1answer
56 views

Fix MySQL server crash

We recently suffered a pretty severe server crash on one of our linux machines (Fedora Core 5), which has resulted in a lot of corruption of data, unfortunately the mysql database is amongst the data ...
1
vote
1answer
19 views

Import .sql file into multiple databases

How to import .sql file into multiple databases at one shot. I have tried this: mysql -uroot -p -all-databases < test.sql But it asks for database name. Whatever there in test.sql file should ...
0
votes
0answers
12 views

Joining tables in Eloquent ORM based on condition to disallow duplicates

I have two tables I am looking to join data. This is a friendship table based on a recommendation I've seen on here. It's an AB BA relationship between friends. I want to join this with a profile ...
2
votes
3answers
32 views

How to merge multiple query results into one

1. SELECT COUNT( * ) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '' GROUP BY prtner_name 2. SELECT SUM( inscan ) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != ...
0
votes
2answers
218 views

How can delete an orphaned temporary table on Mysql Innodb?

I was issuing an Alter table statement and suddenly the server crashed, Now i can not run any alter table to that table because it shows me the error 1050 and it says that a table named #sql1504d ...
1
vote
0answers
18 views

How can i sync a table between two MySQL RDS insatance

I am trying to sync one table in two of mysql RDS instances in AWS something like a db link or FEDERATED engine.
1
vote
0answers
15 views

New percona xtradb node shuts itself down shortly after launch

We currently have a 3 node percona xtradb cluster running on premises. We are trying to add a 4th, remote node ( Obviously split-brain is a concern in this situation but it is only temporary while we ...
0
votes
0answers
18 views

Auto increment id column vs index VARCHAR columns

I'm wondering the differences or comparisons of having an Auto Increment int Id column or an indexed VARCHAR(32) column. Which one would be faster if I had a second table that references the first ...
1
vote
1answer
27 views

Change all varchar date formats with a single query

|date(varchar) | |--------------| |2016-02-16 | | -------------| |03-12-2015 | |--------------| |07-07-2007 | |--------------| |2016-03-14 | Dates are stored in different formats and ...
1
vote
1answer
23 views

How to join multiple results into one row with dynamic columns?

Foreword - I don't know how to formally describe the situation in a single question so the below is as verbose as I can make it... Taking wordpress as an example, there's the "wp_users" table which ...
1
vote
1answer
33 views

How to select user that doesn't exist in the second table

I am trying to understand how to select a user from users table that is not associated with current user in user_follows table: neither in users_id not in shown_users_id. Basically a new user that ...
0
votes
1answer
129 views

MySQL Performance Tuning for MyISAM

I need to performance tune a mysql server and need help. I have a 16G server dedicated to MySQL with 1 MyISAM table with about 2 million rows that gets a significant amount of traffic. There are ...
0
votes
1answer
37 views

how to update information_schema.tables in mysql [duplicate]

i have a table A and i want to update the creation time of this table. I understand i cant simple update ( using update query) as it is not any table. Is there any way i can update it. for example ...
1
vote
0answers
27 views

Dynamic table name in Insert query

I have asked a question about dynamic table name in a prepared statement here , but I want to know if Mysql server support dynamic table name like this : SET @tbl = 'Dawin'; INSERT INTO @tbl (col1,...
2
votes
1answer
24 views

MySQL table concatenation - sort of!

I have a db with the following: Name, Team, Score, Points I want to be able to do a query that returns Name, Score, Points for a particular team and another table that returns Points, Score, Name ...
1
vote
2answers
2k views

Mysql closing immediately after new installation

I am using mysql as an backend for my webapp ,the software ran without any glitch but today it gave unexpectedly ERROR:2003 i tried many solution on the internet but none worked. so reinstalled the ...