Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [mysql]

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.

0
votes
1answer
7 views

How to group multiple ROW as ARRAY

This is my current Query: SELECT innings_no, Player_Name, sum(NoBalls) as NB FROM ( SELECT A.innings_no, A.bowler, ...
-1
votes
0answers
39 views

Trying to SUM two seperate columns but not together as part of a search result

Link to my current code work try { $sql = "SELECT * , (SELECT SUM(value), AS total_value, SUM(balance) AS total_balance FROM ...
1
vote
2answers
2k views

Use mysqldump to backup table with a new table name

Is there a way to back up a table using mysqldump so the table that is being backed up can have a new name in the sql script created by mysqldump. So that If I back up the table 'test_table' like so. ...
0
votes
1answer
92 views

Mysql high CPU and RAM usage

I have a mysql server which has 60gb of ram and it uses too much system resources. Top command result; Mem: 64555285k total, 64151125k used I think there is something terribly wrong with my mysql ...
1
vote
1answer
19 views

Select row with more of one type than another in a separate table

I have two tables. I'm attempting to select customers that have bought more of one type of product than another with at least one of the first type. Let's say for example that I want to get all ...
2
votes
0answers
14 views

How would I undo or edit the last line of a multi-line query in mysql?

I often make mistakes when entering multi-line queries in mysql. Is there an easier way to undo the last line instead of retyping the entire query? mysql> SELECT age, name -> FROM people ...
2
votes
0answers
22 views

Database design for ~ 2.7B records per year / 26.000 data points every five minutes

The current situation We're currently looking into a new product that will send device data back to us to interpret. These are the numbers we're looking at: Devices will most likely send data every ...
0
votes
0answers
13 views

one to many relation how to create a new row with mysql trigger

i'm really new to mysql triggers so...i have 3 tables: a,b and c. table a is related with table b with a one to many relaion and table c is the table that get the triggered infromation from the 2 ...
0
votes
1answer
19 views

5M+ rows on a view

Hy, I have the following problem: Have 1 header row on main table which is the key on the rest of my structure. 4 rows on a customer table 198 rows on a market table 90 rows on a variety table ...
0
votes
2answers
39 views

MySQL not using the correct index

I have following readings table, it has around 500 million rows. CREATE TABLE readings ( id int(11) NOT NULL AUTO_INCREMENT, value double NOT NULL, created_dt datetime NOT NULL, device_id ...
3
votes
0answers
17 views

Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

I have a big table (30 millions rows) defined like this: CREATE TABLE `drawing` ( `id` int unsigned NOT NULL, `artist` int unsigned NOT NULL, `imageUrl` text NOT NULL, `uploadDate` timestamp NOT ...
0
votes
1answer
89 views

Debuging import of a large dump file to MySQL

I'm trying to import a 50GB dump file to AWS RDS instance and have an issue. I get the following error: ERROR 2013 (HY000) at line 15850: Lost connection to MySQL server during query In the beaning ...
0
votes
1answer
32 views

delay advice - Huge resultset

I have a mysql multi select query that performs inner left joins. These queries UNION with two other tables. The resultset is huge (250k+ rows). My client wishes to expose this resultset to a webpage. ...
3
votes
2answers
95 views

Missing DB entries

I have run into a problematic situation. I oversee the administration of 5 php web pages on one server, each having their own database. All pages work like interactive forms drawing and storing data ...
0
votes
1answer
31 views

Mysql Alter table add column with ALGORITHM=INPLACE, LOCK=NONE

Am running below query Query Alter table dbname.tablename add column column1 varchar(50), add column column2 text , ALGORITHM=INPLACE, LOCK=NONE; It takes more time to execute. Ideally running ...
0
votes
1answer
21 views

Find the value between two tables

I am developing a simple course suggestion application. which suggest a degree course to students based on their diploma result. here are my tables in my database. +-----------------------------+ | ...
0
votes
1answer
3k views

Large INSERTs performance optimization

I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query). The queries are sent continuously....
0
votes
1answer
91 views

MySQL backup of incremental changesets

With a MySQL database, is there a way of logging and/or backing up incremental changesets? We have a number of large databases (100-200 GB), for which we take daily backups using mysqldump. These ...
0
votes
0answers
15 views

mysql trigger before insert

How can i make this trigger update my table DATA if there is no row to update. Until i insert data in other table lets called ABC, this table uses a after insert trigger to insert information into ...
0
votes
1answer
90 views

Track database name in anemometer queries

Hello i have installed anemometer on our server in order to see the queries that need optimization. From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is ...
0
votes
1answer
54 views

mariadb 10.3.13 table_open_cache problems

Just upgraded mysql 5.6 to mariadb 10.3.13 - now when the server hits open_tables = 2000, my php queries stop working - if I do a flush tables it starts working correctly again. This never happened ...
0
votes
1answer
20 views

Select rows with the same value in multiple columns

I have created this table: CREATE TABLE IF NOT EXISTS `names` ( `ID` int(6) unsigned NOT NULL, `C1` varchar(200), `C2` varchar(200), `C3` varchar(200), PRIMARY KEY (`id`) ) DEFAULT CHARSET=...
0
votes
1answer
15 views

MySQL replication with no connection

I configured MySQL replication between a Master and a Slave throught VPN. Everything works fine, when connection between them is down, and i make some changes to the Master (like insert a row), then ...
1
vote
0answers
19 views

SQL dump loads with Error 1064

Looks like this issue is just the wrong version of MySQL, need to go to 5.5 So I have a backup of an offsite source SQL database. I am not sure how they are preforming the data dump but I am trying ...
1
vote
1answer
96 views

Slow MySQL searching from millions record

My table looks like this. id | hash | date I've an index on hash which is in binary mode. I have about 3 million rows right now. But if I query with the following query, it takes about 4 seconds. ...
0
votes
1answer
20 views

Count rows in a table based on a DATE field

I have a table with 3 columns (order_id, client_id, date_added). The content would look something like this: order_id | client_id | date_added ----------------------------------- 14152 | NA4156 ...
2
votes
1answer
590 views

How to create a unique index with only certain values required to be unique?

I have a list of codes being stored in a table, and some codes should be unique, others can be repeated. The codes should be unique-scoped to another field, product_id. Assuming the code 11 should ...
0
votes
1answer
107 views

MySQL 5.7.9 How to get more explicit error message?

I am running MySQL 5.7.9 on a Windows machine, upgraded recently from a 5.6.17. I used to get explicit error message with my previous versio, but somehow since the upgrade I get very mysterious error ...
0
votes
1answer
97 views

MySQL - Backup new and modified entries only

I am developing a very huge system in Java using Hibernate and MySQL. Now, I want to automate the daily, weekly, and/or monthly replication (backing up) of the data that are stored from the database ...
4
votes
3answers
12k views

Master-master Replication with 3 Nodes

I need to configure mysql master/master replication with 3 nodes. Currently I have master/slave setup and I need to move it to master/master with 3 nodes. I have gone through some posts about ...
0
votes
1answer
15 views

Get column difference between same table in different schemas

Suppose I have the following tables: schema_a.products +----+------+------+-------+ | id | code | name | price | +----+------+------+-------+ schema_b.products +----+------+------+ | id | code | ...
0
votes
1answer
71 views

Innodb cluster setup error

Reframed: Here are the steps I followed but is resulting into an error: I am connected to my primary node (10.0.0.4) and I checked the config of all the nodes using below: MySQL localhost JS > ...
0
votes
1answer
98 views

how to minimize the data scanned on my database using index?

Is there anyone how to minimize the data scanned on my database?? please show me.. using index? query SELECT IF (e.channel LIKE 'IAX2%', e.calldate, IF(d.channel LIKE '...
1
vote
2answers
37 views

If count >= row then insert row (substitute for variables and else/if)

I do 99% php/javascript work so getting my head around SQL is always a struggle, sorry if I seem dense. So I've got a table that looks something like this: post_id | meta_key | ...
1
vote
1answer
111 views

Is Circular replication recommended among 4 MySQL servers

We have 4 MySQL servers and our main motto is that data should replicate across all the 4 servers if one is getting updated and the same changes has to go to all other 3 servers. And below are the ...
1
vote
1answer
92 views

How should I configure mySQL 5.7 on Ubuntu for a physical Hot Backup?

I recent upgraded my Ubuntu 14.04 server to Ubuntu 16.04. I managed to get mySQL upgraded to 5.7. I don't currently have anything in the mySQL instance. Since I have been an Oracle DBA for almost 20 ...
0
votes
1answer
94 views

Query output conditional upon values in dataset

If the number column contains values 5 AND 6, AND the value X exists in the chr column, I would like to exclude rows with 10 in the test column. But, if the number column contains values 4 AND 5, AND ...
1
vote
1answer
18 views

MySQL RDS - Unable to Kill Hung Queries

I have a MySQL RDS instance that's got 100+ queries all stuck waiting for a table metadata lock. The good news I have permission to kill all of the sessions, and have 0 concern for data loss as this ...
0
votes
3answers
146 views

MYSQL 8.0 server not found in Workbench directory in Windows 10?

I have upgraded my workbench to 8.0CE and also removed the previous 5.7 MYSQL server as well as Workbench 6.3. This is the directory: C:\Program Files\MySQL\MySQL Workbench 8.0 CE Also when I typed ...
1
vote
1answer
95 views

Getting past corrupted binary log “Error in Log_event::read_log_event():”

I have a binary log that mysqlbinlog chokes on with the error in the title. The file itself has much more activity after the cited position. Doing some basic confirmation it's not all garbage by ...
0
votes
1answer
28 views

Differences of MySQL locking for update with WHERE and IN

So I have a table with id as INT NOT NULL as the PK. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked. ...
0
votes
1answer
92 views

mysql hardware refresh + version upgrade

currently I am running MySQL 5.0.80 on windows server 2003 (x64), 64gb memory, I have a master and 2 slaves. It's a large 400 gb Database. I am setting up all new hardware (SAN + dedicated MySQL ...
1
vote
2answers
114 views

Increasing query efficiency of queries spanning multiple tables

I am looking for ways to improve the performance of some relatively complicated queries against both PostgreSQL and MySQL databases. (One of the requirements of the application which the database is ...
0
votes
1answer
98 views

How to insert values in mysql taking previous row into account

I have this table, players, with the following columns: id name player_number round and I would like to auto fill the player_number (1 and 2) and increment the round when inserting a new player 1. ...
2
votes
1answer
140 views

Memory leak in MySQL 8.0.15?

The MySQL 8.0.15 the server runs after a while [~1 week] out of memory. SLES 12.4 MySQL 8.0.15 5 GB RAM [before the VM ran with 3 GB for months without any problem]. 24 cores Running SELECT ...
0
votes
1answer
22 views

MySQL Cluster - SQL node fail to connect Management Nodes

I am trying to deploy a 2 machines MySQL NDB Cluster with the following architecture: 192.168.1.12 – management node 192.168.1.12 – SQL node 192.168.1.12 – data node 192.168.1.13 – SQL node 192.168....
0
votes
0answers
32 views

MySQL query for free Date time slot availability

I am trying to find free Date time blocks on current month and db value DB Structure- Event_ID | dt_start | dt_end +----------+---------------------+---------------...
2
votes
2answers
14k views

Query State hangs on sending data

For some reason, the query state hangs on "Sending Data" (using show processlist;) and I have no idea why. This is the table creating script: CREATE TABLE IF NOT EXISTS `esp_game` ( `gameID` int(10)...
0
votes
1answer
101 views

why binlog doesn't log the DML statement?

two method compared 1. use test; create table t (a int); 2. create table test.t1(a int); my result is the first one can log the binlog but the second one can't not use test;create table t(a int); ...
2
votes
3answers
44 views

How to group many information une one select

There are my database articles id | art_code | art_name 1 | 5678 | computer 2 | 0987 | display 3 | 8547 | keyboard action id | code | action | qte 1 | 5678 | IN ...