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.

2
votes
2answers
33 views

Inconsistent performance of WHERE and ORDER BY depending on size of WHERE match

Been working with MySQL for 15 years, and never seen anything quite like this before. MySQL table with 4 million records. I have found a very strange "sour spot" (opposite of "sweet spot") in ...
0
votes
1answer
24 views

Mysql 5.7 - select where json

I have some JSON data stored in a filters column. An example from one row: name | filters ---------+---------------------------------------------------------- John Doe | [{"Year":2004, "SportID":...
2
votes
1answer
20 views

MySQL 8.0 Replication filters lost between restarts

I don't know if I fell on a bug or a feature, I read the documentation several times but haven't found the information. I have two MySQL servers, both on 8.0.15 and hosted on a Windows Server (x64). ...
0
votes
1answer
29 views

How do I calculate the average weight of riders by competition?

I have an assignment where I need to calculate the average Weight of Riders by Competition. The assignment has a lot of different parts but I got stuck on this one so please help my out if you can. I ...
0
votes
1answer
23 views

Analyzing a query plan for a query with TWO predicates, but index only has ONE

Note: This is from an introductory database systems course at my university so I'm not entirely sure if it matters, but for what it's worth we are being taught MySQL. One homework question I'm doing ...
1
vote
1answer
26 views

Filtering a MySQL table on multiple columns, including one with spatial data

I have a MySQL table with linestrings: CREATE TABLE edges ( id int(11) unsigned NOT NULL AUTO_INCREMENT, geometry linestring NOT NULL, type_key varchar(255) NOT NULL, created_at datetime NOT ...
0
votes
1answer
22 views

ENUM column auto set

I have two tables "using" foreign keys: Referencing table CREATE TABLE `requests` ( `request_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, -- `status` enum('new','...
0
votes
1answer
28 views

MySQL CLustering Options

I have two MySQL Database Servers i.e. One is Active Server --> Master and second is Passive Server --> Slave. And as we know we have 3-4 MySQL HR,DR Solutions / Clustering options.For example 01 : ...
1
vote
0answers
49 views

The GET_LOCK() and RELEASE_LOCK() functions are taking too long

We are trying to use the MySQL's locking functions GET_LOCK() and RELEASE_LOCK() in our Spring Java application. The code we are using is defined in a procedure for both seperately which simply invoke ...
-1
votes
1answer
16 views

Copy a table column based on another table's condition [closed]

I have two tables : table_one table_two table_two has following columns : table_one_id OldColumn NewColumn I need something like this : $table_one_records = $table_one->find('all')->where(['...
0
votes
1answer
32 views

SQL: How to ask for multiple counts from the same table within a join

Trying to find 3 separate counts within one column of a table, each with separate WHERE clauses. I've got 2 tables in the query, so one join. Thought this looked correct but it will not run: SELECT ...
0
votes
2answers
25 views

Delete old records based on its identifier

For example, I have a table similar to this: deviceid | servertime 1 2019-05-13 12:00:00 1 2019-05-14 12:00:00 2 2019-05-13 13:00:00 2 2019-05-12 13:00:00 2 ...
0
votes
0answers
32 views

MySQL - Parent child select query

I have the following query working OK: SELECT core_condition AS name, NULL AS parent FROM condition_theme_lookup UNION ALL SELECT theme_name AS name, condition_theme_lookup.core_condition AS parent ...
0
votes
1answer
24 views

MySQL Replication using GTID

I want to create MySQL replication using GTID. According to MySQL Manual I need the following settings in my.cnf (for the master) server-id = 1 log_bin = /var/log/mysql/mysql-bin.log ...
-1
votes
1answer
35 views

How can I capture user IP address and store them automatically to my database? [closed]

am currently running an open voting system with no login form. I wanted to restrict double voting using user IP Addresses, how can I record user addresses direct to my database and use them to ...
0
votes
1answer
30 views

dynamic or static database tables

I am creating a little mulitplayer game. The game creates a session where players can join in. Then the session has to store some data provided in the game. Should I create a new session table ...
1
vote
2answers
42 views

Is it a good idea from a performance perspective to store audio files in a mysql database? [duplicate]

I have an application where I have audio files. I need to store them in a mysql table since we already have a mysql database available. I saw that some people don't advise to do so. What are the ...
1
vote
1answer
27 views

Weird error on creating table and selecting rows from table

I have db and I am trying to create table called "sales_invoice_grid" I am having bellow scenarios: error 1: while creating the table "sales_invoice_grid" manually, it gives the message that it ...
2
votes
1answer
89 views

Mysql/innodb deadlock on simple delete query

Mysql/innodb 8.0.16, read committed transactions, several statements in transaction raise deadlock on deletion of non intercepting rows. Trying to understand what is happening: -----------------------...
0
votes
1answer
23 views

Can i SELECT two user names by id in another table with join?

I have table sb_bans which including BannerID and UnbannerID. And another one sb_admins which including aid and name. This is my query: SELECT sb_bans.*, sb_admins.authid as admin_authid, sb_admins....
0
votes
1answer
38 views

Selecting N values from each group

I have 2 tables which have to be joined to get needed data. Table1 is articles, Table2 is for channels/categories. For the sake of simplicity, both tables only have 2 fields. Basically, I need ...
0
votes
1answer
50 views

Trigger not setting DATETIME correctly

I have the following table: CREATE TABLE assignment ( id INT NOT NULL AUTO_INCREMENT, course_id INT NOT NULL, title VARCHAR(128), assigned DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, due ...
1
vote
1answer
26 views

Mysql Query Optimisation For Joining a BETWEEN Date and Date query

I have some instagram API data sitting across two tables. One with post_data, and one with profile_data. The profile_data table has a timestamp of when the account was checked through the API to get ...
0
votes
2answers
37 views

Why MySQL is ignoring my indexes in a query?

I have the following query in a production database server(master): SELECT * FROM Enlace_llamadas where id_empresa IN (1,5,33,36,40,44,48,53,55,111,115,125,127,151,167,175,181,185,187,189,195,199,...
0
votes
1answer
35 views

MySQL: columns with mixed character encodings, and finding columns which have multibyte data

I have some rather large CSV files that I am loading into my MySQL 5.7 database. The files are several gigabytes in size, several million lines long, and have large column widths that must be used in ...
0
votes
1answer
56 views

How to find an entry from filtering with two associated tables

This questions has spinned off of another question I probably did not ask correctly from the get go. I have some tables, where attributes and attributes_entries that hold a pair of dynamic values for ...
0
votes
1answer
16 views

MYSQL Read Replica Bottle Neck

If my MySQL master has heavy writes, my read replicas will in-turn gets bogged down by the replication logs & executing them. Then could it serve the reads? Assume MYSQL master is executing at ...
-2
votes
1answer
27 views

I need help making a trigger in mysql that prevents/alerts when having the same Car_Plate more than once in the table

I need help making a trigger in mysql that prevents/alerts when having the same Car_Plate more than once in the table
0
votes
1answer
20 views

Cannot connect to MySQL using PHP 7 [duplicate]

The version of MySQL is 8.0.16. This is installed on an EC2 Linux and is a fresh install. I have run the mysql_secure_installation command after installation and was able to change the root password ...
0
votes
1answer
19 views

How to change all columns from one type to another, changing all FK columns as well?

In an inherited DB we found that there are around 25 tables with a primary key defined as tinyint. For a few of those, it's an imminent problem, since this optimistic optimization clashes with the ...
0
votes
1answer
44 views

How to get CPU usage in MySQL per user?

I want to know is there anyway to get CPU usage per user in MySQL? for example I have 3 different users 'user1', 'user2' and 'user3'. I want to get the average/real time memory usage and CPU usage of ...
0
votes
1answer
35 views

Where MySQL is taking time either in disk read or computation? [closed]

From SQl case study System configuration is Software: Percona 5.6.15-63.0. Hardware: Supermicro; X8DTG-D; 48G of RAM; 24xIntel(R) Xeon(R) CPU L5639 @ 2.13GHz, 1xSSD drive (250G) Query is select ...
0
votes
2answers
65 views

MySQL Create View WITH CTE

I have successfully created a VIEW in SQL Server as follows. But when I try the same in MySql, Error coming. SQL SERVER Command: CREATE VIEW [dbo].[vw_PurchParent] as WITH cte AS ( SELECT a._Id,...
0
votes
1answer
21 views

MySQL block read time?

My understanding is even if I need to read single record like select name from customer where id=1, I need to read complete specific block(block address can be found from index on column ID) in memory....
-1
votes
1answer
22 views

Grouping multiple records into one row while aggregating values in one field [closed]

Currently I have a set of records like this I need my result into one row where I basically remove Field4 and aggregate the values in Field5 Basically like this Is there a way I can group by the ...
0
votes
1answer
16 views

Recommended replication strategy for fast global access to MySQL DB

The app i'm working on has users from all over the world and due to the nature of the app, i need to minimize all delays. My application servers are located in different data centers across the world ...
1
vote
1answer
24 views

How to tell whether mysql was built with libedit or readline?

How to tell whether mysql was built with libedit or readline? I'm trying to configure the mysql client tool to use Ctrl-R for searching command history but not sure which input library my ...
0
votes
1answer
22 views

executing single query in loop increase time every time

Am executing one query which is the same as below. Now here the problem is as limit increasing its increasing execution of time. I am adding limit using a PHP script. So anyone likes to suggest to ...
0
votes
1answer
13 views

MySQL Not Recovered After Running Out of Space

I've got a MySQL 5.7.26 running on Ubuntu 16.04. I'm running an app that's loading a few GB data into the database, which was running fine until I let the root volume (with MySQL on) run out of space -...
0
votes
0answers
15 views

Restoring backup with a MySQL Database downgrade

I want to migrate a MySQL 5.7 database to a MySQL 5.1 version because the MySQL version of the server I’m migrating my WordPress webpage is 5.1. I tried to do it in the usual way but it was not ...
0
votes
0answers
15 views

How do I properly copy BIN_logs to a another mysql server for testing an “undo”?

About a week ago a user, meaning to delete one category from an app database, deleted all/most of them thru the web app that runs the database. I thought we did not have backups so we revived an old ...
-1
votes
1answer
58 views

MySQL JOIN statement

I have 2 tables like below: product_name id | productName ----+----------------- 1 | item 1 2 | item 2 3 | item 3 exchange_history id | exchangeFrom | exchangeTo ----+--------------+-------...
0
votes
0answers
31 views

innodb buffer pool size not changing

I have Percona fork of MySQL 5.7 installed where I have defined innodb_buffer_pool_size in /etc/percona-server.conf.d/mysqld.cnf as follows innodb_buffer_pool_size = 10G But, when I login to MySQL ...
0
votes
2answers
30 views

select columns with different conditions

This question focus to the COUNT result 1st query will show the COUNT with date condition. 2nd query will show the COUNT without date condition. Now I need a query which make the COUNT result in ...
0
votes
0answers
14 views

Replicate a db containing encrypted fields

We've been using aes-256-cbc in our code for some table fields and storing the encrypted strings in our DB to make sure that the data at rest is encrypted. Just a peep into what we do for the ...
0
votes
1answer
25 views

Combine multiple rows into a single row

I have the following query result +-----------+-------------+----------+----------+----------+ | person_id | question_id | answer_1 | answer_2 | answer_3 | +-----------+-------------+----------+------...
0
votes
1answer
48 views

Master Master replication rows not sync properly

I have a wordpress website that run on 3 machines Machine1 | Machine 2 {nginx+lsyncd+mysql} visitor > cloudflare > haproxy ------ ...
0
votes
1answer
17 views

Summing Column values based on group by criteria

So I have this query select *, TIMEDIFF(tbltimetracker.tt_endtime, tbltimetracker.tt_begintime) as hours from tbltimetracker LEFT JOIN tblcustomers on tbltimetracker.customer_id = ...
0
votes
1answer
23 views

Make Xtrabackup ignore myisam tables & backup only innoDB tables?

I'm using mysql 5.7 (or rather percona 5.7) Is it possible to backup only the innodb tables (easily, not to specify each table one by one because I have a lot of tables) with xtrabackup? and not take ...
0
votes
0answers
20 views

Minimizing MySQL downtime during OS update?

Alright, this one should be simple. We would like to be able to perform scheduled OS updates on production DB nodes w/o excessive downtime. Our setup: MariaDB Primary "W1" (write+read) MariaDB Async ...