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
0answers
12 views

Mysql multisource replication : on slave map masters dbname to something like mastername-originaldbname

I need to implement a mysql multisource replication using mysql >=5.7.11, there are already a certain number of mysql servers running which will become masters for the single slave which is going to ...
3
votes
2answers
59 views

Efficient schema to handle time interval queries on a huge table?

We have huge table (+10 millions rows) where we aggregate the values by a using a time interval search on a Datetime column. And right now, to build single page on our app, we are querying this table ...
1
vote
1answer
30 views

Referring alias name in subselect returns “Unknown column in 'where clause'”

I have following table: + ---------- + --------- + --------- + -------- + ------------ + ---------- + | Field | Type | Null | Key | Default | Extra | + ---------- + -----...
1
vote
2answers
31 views

MySQL to use all cores 20 cores I have

I am currently setting up a website that uses a MySQL database having more than 40 million rows of data. I am using one server for files and separate mysql server. Two servers are having 20 core, 64G ...
0
votes
1answer
35 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 ...
1
vote
1answer
50 views

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

I have the following message when running myqsltunner.pl in past this was not present. How can I solve this issues? -------- Galera Metrics ------------------------------------------------------ [...
1
vote
1answer
20 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 ...
1
vote
0answers
29 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
70 views

In MySQL, how exactly does 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
0answers
17 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 &...
1
vote
1answer
33 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 ...
1
vote
0answers
40 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
64 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 ...
3
votes
1answer
37 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 ...
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
1answer
34 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 ...
1
vote
0answers
26 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.
3
votes
3answers
40 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` != ...
1
vote
0answers
23 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
19 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
31 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 ...
1
vote
1answer
31 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 ...
0
votes
0answers
14 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 ...
1
vote
1answer
34 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 ...
-3
votes
1answer
52 views

Get user rankings based on points from database

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 ...
1
vote
1answer
27 views

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

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
0answers
29 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,...
3
votes
0answers
96 views

Inputting auto-incrementing, grouped, unique hashes?

Alright, here's what I've come up with so far: SET @id=(SELECT IFNULL(MAX(id) + 1, 0) FROM leads); UPDATE leads SET id=@id WHERE id IN ( SELECT id FROM (SELECT * FROM leads) AS something WHERE ...
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
0answers
15 views

Getting Beginning Balance inventory of every month for the whole year in MYSQL

Good day, I was trying to create a report of inventory movement rate for the whole year, so I need to get the beginning total of every item in my inventory table every month... my sql table looks ...
2
votes
1answer
23 views

Out of range errors while with insert statements

I'm having an issue regarding every third insert line of the database I'm trying to create. Some background, I have a huge .JSON file and used software (sqlizer) to turn it into input statements, ...
1
vote
0answers
22 views

SELECT UNION from multiple tables with same schema in MySQL

I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use: SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION SELECT ...
1
vote
0answers
8 views

How to clear gcache pages in Percona PXC

I have a 4 node cluster(percona PXC) and one of the nodes have more than 150 gcache disk files each of size ~128 MB. I would like to purge or flush them so that the server user fresh gcache disk file. ...
1
vote
1answer
28 views

Sync two mysql database one in intranet and the other on internet

I'm making the training and placement module for my college. But I'm facing problems to sync the databases, one that will be used by the students and the other that will be used by the HR of a company....
1
vote
0answers
9 views

MySQL NDB cluster maximum data size

According to the limitations outlined at http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-limitations.html, there can only be 48 data nodes. Does that mean, with number of replicas 2, I can ...
3
votes
2answers
78 views

What is wrong with this SQL statement?

I cannot get this statement running on MySQL server: DECLARE @tabl varchar(40) SET @tabl = :uId; SET @location = CONCAT('data_',@tabl); SET @b = 'INSERT INTO' + @location + '(Id, uId , name , address,...
0
votes
0answers
28 views

Advantages of binary installation over the RPM installation [closed]

What are the advantages of binary installation over the RPM installation for MySQL? What's the main difference?
1
vote
1answer
20 views

Multi-table sum of cost breakdown by pricing category

Looking for help with a query to get the sum of the subtotals for each price category from a list of line items, by order. Scratching my head on the group by stuff. There are 3 categories: setup, ...
0
votes
1answer
26 views

Returning data based on if another column in another table is NULL

I'm trying to generate a list of data which is on completed status but has not been billed. I am able to produce a list of all completed orders however i am unable to remove the ones which have been ...
1
vote
0answers
19 views

How to convert MySQL master-slave to master-master

I've searched around, but can't find the exact details to point me in the proper direction. I currently have 2 MySQL instances under 5.5.41 running as a master-slave setup. I followed these ...
1
vote
0answers
45 views

MySQL Load Balancing what do i need Replication or Cluster?

So My MySQL database server is reaching it's limits with the number of queries selects, updates, inserts etc it can concurrently take resulting in high CPU usage more than what a single machine can ...
1
vote
0answers
56 views

Is there a faster way of importing 8m+ rows?

I'm creating a new feature on my website in order to provide player locations (my website is for a multiplayer gaming service), and am going to use a simple IP lookup in my database. For this I need ...
0
votes
0answers
24 views

count rows that has same values from 2 different tables mysql php [closed]

Let's say that I have 2 tables, one of them is called table1 and the second table is called table2. in each table i have 6 columns that has the same name. Table1: Columns: one|two|three|four|five|...
4
votes
1answer
88 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 ...
1
vote
0answers
24 views

MySQL Supported versions of MySQL Enterprise TDE

We are bidding for a project involving developing a portal using Liferay, which requires that we use MySQL version 5.5 which is the current compliant version of MySQL with Liferay. We need also use ...
1
vote
0answers
30 views

Can't drop table after creating table with wrong engine

I'm trying to drop a table containing several hundred thousand column-based records. Normally when creating the database I use a column-based engine (infinidb) but in this case I forgot to include the ...
1
vote
1answer
71 views

How to use a SELECT query inside an ON CLAUSE in a LEFT OUTER JOIN in MySQL

I need to retrieve data from several tables. For now I am getting maximum of them from SELECT disc.id, users.user_login as 'last_active_user', disc.discussion_sub, list....
1
vote
2answers
31 views
+250

Galera: any drawback of enabling wsrep_log_conflicts and cert.log_conflicts?

We have a HAProxy which balances reads and writes to only one (of three) Galera nodes. If this balancing works fine, the parameters wsrep_log_conflicts and cert.log_conflicts should never log any ...
-3
votes
0answers
16 views

How to write a shell script [closed]

I want to write an alert shell script. It needs to fetch the data from table a. Upon status 1 it should fetch the data from table b. There we have the contact details. It should send the alert based ...
-1
votes
1answer
35 views

Server Specifications for Web Application [closed]

I am trying to select the configuration for a web application built in PHP 5.5 and MySQL 5.1. I will also be deploying PHPmyAdmin for the MySQL database. Both the web application and the database will ...