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

6
votes
3answers
13k views

Mysql slowly uses memory until it starts to use swap

I am running a 1gb of RAM rackspace database server. For some reason in about 2 days the memory usage goes from using very little swap, to using 100mb. If I don't restart sql it will keep using more ...
2
votes
2answers
191 views

Slow subquery in SELECT statement

Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows. The table is a list of hotel bookings, containing (most relevant) Check-...
2
votes
1answer
478 views

Can a slave with a dynamic ip update the master

How can I setup mySQL replication in such a way that a slave on localhost with dynamic IP can update the master as well as get updates from the master which has a static IP. The slave will not be ...
1
vote
1answer
255 views

MongoDB to MySQL Replication Automatically

i have Mongodb server and mysql server. i want to use mongodb for "write" and mysql for "read". so i am looking for some tool or some process by which we can tranfer mongodb to mysql server on ...
1
vote
2answers
68 views

How to decide that a new table is needed?

This might seem a vague question but I am going through a bit of a dilemma here. How do you people decide when a new table will be need? For example, when creating a table for storing user ...
1
vote
1answer
48 views

1054 - Unknown column in subquery

i have 2 table one store data of relation user other buy user, in my senario must compress realtion with at last 1000$ path table +-------------+---------------+-------------+ | ancestor_id | ...
2
votes
1answer
143 views

optimize mysql with mysqltuner Recommendations

Every day at 18:00 mysqlserver became very slow How do I apply the suggestions by mysqltuner for optimize mysqlserver CentOS release 6.6 (Final) MySQL version 5.6.28 64-bit architecture 32GB RAM ...
1
vote
0answers
16 views

Command wbcopytables --table-range not working

I am migrating sql server tables in MySQL using wbcopytables command. I want to copy a specific range(id 1-10000) of data from MSSQL to MySQL, so I am using below syntax. wbcopytables --odbc-source="...
0
votes
1answer
21 views

Errors declaring variables in stored procedure?

I'm sort of new to SQL and stored procedures. I'm trying to set some variables to use later on in a transaction while also setting a rollback variable on an exception, but I'm not able to do this. I ...
1
vote
2answers
179 views

ALTER DATABASE script preventing restore of MySQL database in another server

While backing up the database with mysqldump --routines options, I see ALTER DATABASE in the middle of the output dump, near the routines. /*!50003 DROP FUNCTION IF EXISTS `f_my_function` */; ALTER ...
0
votes
0answers
13 views

get minimum from result with group by mysql

i have table it store hierarchy data in mysql this table store stable relation but if each user les than 1000 buy removed and user User a lower level replace thia is my code and work fine, after qroup ...
0
votes
2answers
242 views

How to create function using another functions mysql

I want to create a function in MySQL using another function but I am getting an error "Explicit or implicit commit is not allowed in stored function or trigger". This is my code: DELIMITER $$ ...
2
votes
1answer
223 views

Services don't have MySQL Server. How to run it?

I need to run MySQL Server on a new for me laptop. There's Windows 7. And MySQL have been installed on it. In order to verify this, I opened the Programs and Features window and saw it there: ...
1
vote
2answers
88 views

MySQL doesn't log queries anymore

Some time ago I updated my MySQL server to the v5.6: SELECT VERSION(); 5.6.30-0ubuntu0.14.04.1 Now the query logging isn't working though the configs in the /etc/mysql/my.cnf seem to be correct: [...
-1
votes
0answers
45 views

MySQL Quesries give me a High CPU usage - howto fix it?

I use automatic wordpress plugin that keep my site feeding by using cronjobs -every minute. I use 30 cronjobs on every single minute. Now i have a super slow server and a high cpu usage because of ...
1
vote
2answers
4k views

Cannot Add Foreign Key Constraint

Can anyone tell me why there are errors such as: Cannot Add Foreign Key Constraint And am I on the right track? My schema looks like this: DDL: DROP DATABASE technology_university_hospital;...
1
vote
1answer
162 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
22 views

How to automatically update a table derived from another table when the latter is updated

I have a table from MySQL called References with 20 columns and 20 rows: CREATE TABLE bop_ppy ( start_time DATETIME (6) , end_time DATETIME (6) , high DECIMAL (5,2) , low DECIMAL (5,2) , close ...
1
vote
1answer
292 views

mysql replication missed quite a lot SQL statement

I have set up a master/slave with mysql-5.1.73 The master's binlog format is "statement". The master and slave seemed running very well with slave status: Slave_IO_Running: Yes ...
2
votes
2answers
74 views

How do I guarantee inventory numbers are unique across all tables?

I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords ...
3
votes
1answer
167 views

drop table from outside?

A poorly designed script ran on my MySQL 5.6 instance for 24 hours before I stopped it by killing the MySQL command line client and rebooting the machine while the script was still running. The ...
1
vote
1answer
34 views

How to UPDATE NULL values by MAX()+1?

I want to assign values to null cells of a column as UPDATE table 1 SET number = MAX(number) + 1 WHERE number IS NULL but mysql return error Invalid use of group function.
0
votes
0answers
18 views

Import databases using /var/lib/mysql

I've got some issues with my server, leading me to completely reinstall my debian OS. Before reinstalling, I backed up some files on the server and now it reveals that I have a backup of my previous /...
2
votes
1answer
143 views

join to other table multiplied by loop foreign key in mysql

i have 2 table one node and relation and Second store buy each node. my code work fine but when sum multiple buy for each node result multiplied by number of purchases . table nodes +-------------+--...
0
votes
0answers
27 views

Does max_allowed_packet shows in bytes in query result?

We have 2 different same version of mysql server both max_allowed_packet set to 1G. The only difference is max_allowed_packet is set as 1G and the second is 1024M. The 1024 setup encounters and error ...
1
vote
1answer
21 views

Rows Holding the Group-wise mysql

i have simple table and need to group by duplicate items , also i need to get min with holding row i read this tutorial work , but not sum grouped item level http://dev.mysql.com/doc/refman/5.7/en/...
1
vote
1answer
35 views

Setting up Master-Master Replication in MySQL on a live system

I've just recently taken on the task of looking after our mySQL databases, and have been given the task of setting master-master replication. We have a live server which has a mySQL database which is ...
0
votes
1answer
209 views

How to manage very large amount of data in mysql

I have a table in MySQL in which I write logs of users in a specified format. It have just 2 columns:id (auto-increment), log. Currently data is very less, so I'm using this. Now there is a ...
0
votes
3answers
154 views

What is the best way to have full text search where data in database is frequently updated

I am working on an application where database is continuously and periodically gets updated , the frequency of updating of data is daily, weekly, monthly, and yearly based on their type. I want to ...
0
votes
1answer
34 views

How do I get mysql to start for the first time?

I want to get mysql to start for the first time. I installed it on RHEL version 6. All of the commands posted were executed as root. As a test, I ran this: # yum install mysql This is the output:...
3
votes
1answer
656 views

Select * from statement execute very slowly, innodb io read speed is low

I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5....
2
votes
2answers
184 views

Find all users who rated all the movies rated by a specific user

So we have the following relational schema: users(user_id, ..) movies(movie_id, ..) ratings(user_id, movie_id, rate) So, we want to find all the users who have rated all the movies which are rated ...
2
votes
1answer
104 views

Compare two tables with same structure, but differing membership numbers

I have two tables with the same structure and most of the data is the same. I also have a third table that contains ids and names. The ids are connected to the membership numbers discussed below. The ...
0
votes
1answer
131 views

How to compare all rows in a table and extract data using triggers

I have two table one table stores the gps data of various users another table stores the idle time of the users based upon the gps data of latitude and longitude values and timestamp difference. ...
0
votes
1answer
476 views

MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations

I had a table with ~0.5M rows and tried to exec simple query, something like UPDATE table SET col1=-90 + RAND()*90, col2=-180+RAND()*360 WHERE col1=9999 but result was ERROR 1297 (HY000): Got ...
0
votes
0answers
51 views

MySQL: Log only 'select' queries executed on a specific set of tables of a database

I have a Magento site in which I want to log only SELECT queries(after SELECT it could be any column or all columns, in short all SELECT statements) executed on a specific set of tables, also as it's ...
1
vote
2answers
321 views

XOR binary values greater than 64bit

I have a binary blob holding the results of a SHA256 hash that I XOR with a script generated SHA256 hash. For testing I am using the values usertokens and methodtoken for the test hashes. hash1: ...
15
votes
3answers
6k views

Is the overhead of frequent query cache invalidation ever worth it?

I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
30
votes
8answers
117k views

MySQL DB import/export command line in Windows

How to export / import a database in MySQL through the command line? MySQL system configuration, WampServer server installed. OS: Windows The advice for my local host, not an live hosting...
0
votes
0answers
22 views

InnoDB LSN in the future - how to insert the “required amount” of data?

Please forgive me - I'm not a DBA, I'm a lowly devops whose mysql DBA went on vacation the day before this happened and is gone for 10 days. I'm learning as much as I can about mysql/innodb/LSNs but I'...
0
votes
1answer
1k views

Deadlock caused by INSERT trigger - thread looking for lock it already has?

Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, ...
0
votes
0answers
32 views

MySQL insert with sub select slow

We are using Python and LOAD DATA INFILE to load data from CSVs into our staging database. From staging we have sql scripts moving the data into our actual production database. LOAD DATA INFILE is ...
0
votes
1answer
24 views

MySQL Semi-synchronous replication with Multi-Master

Is it possible to use semi-synchronous replication with a Multi-Master setup? I've tried to follow this guide to setup a semi-synchronous replication for a master-slave setup: https://avdeo.com/2015/...
0
votes
2answers
122 views

Which files in the “MySQL-5.0.27-win32” folder do i need to backup?

Ok, I am using a pretty old MYSQL "mysql-5.0.27-win32" and the file and the structure maybe different from the latest Mysql (mysql 7). Here is the current structure of of mysql-5.0.27-win32: In "...
1
vote
1answer
512 views

Syncronize mysql databases between local and hosted servers automatically

We have many websites with Development, Staging and Production servers. We have many developers for many projects. We need a solution to synchronize the developer and staging databases. After that one ...
4
votes
1answer
8k views

Problem with View When Restoring a MySQL Backup

I have a database (InnoDB) with some tables, views and routines... Periodically I do a backup using the command line: mysqldump -u user -ppassword --routines db_name > backup.sql But I have a ...
1
vote
2answers
3k views

Mariadb on Centos 7 not changing max connections based on my.cnf

The following configuration in my.cnf is not being followed. Max_connections go back to 151. please help as i have 16gb of dedicated ram that i would like to use properly. [mysqld] datadir=/var/...
0
votes
0answers
28 views

In which cases is a full table scan preferable to an index scan with low cardinality?

Are there real-world scenarios where to perform a FTS is preferable to scanning an index with very low cardinality? Why would MySQL do so? Doesn't an index scan always involve reading less data (...
0
votes
0answers
44 views

Mysql: slow down with order by, global database problem

i create nasty query for testing reasons. Only for speed testing. EXPLAIN SELECT m.post_id FROM wp_postmeta m LEFT JOIN wp_postmeta mi ON m.`post_id` = mi.`post_id` LEFT JOIN wp_postmeta md ON (md.`...
0
votes
2answers
464 views

MariaDB Galera cluster nod won't start

I have configured a MariaDB Galera cluster with three nodes. The master node and one slave-master node is up and running, but the third node just won't run. The system specs and cluster config are the ...