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.
21,401
questions
1
vote
3
answers
32
views
Presto/Mysql join validation
I have 2 tables, and the CASE statement below should only evaluate to 1/true if the record with the same lead_id exists in both tables. Here I have 2 ways of achieving this but just want to verify if ...
- 17
0
votes
1
answer
18
views
MySQL - Getting the percentage of an occurrence
I'll do my best to explain what I'm trying to accomplish.
I have a table called "Actions" and in Actions I have a column called "Resolution". In resolution some the entries, as an ...
- 103
0
votes
0
answers
21
views
ubutun 18.04 I did master and slave replication. before it was working fine but today it stopped all of a sudden I don't know why
2022-04-27T21:57:22.421118Z 2 [ERROR] Slave I/O for channel '': error connecting to master 'replicat@172.20.10.2:3306' - retry-time: 60 retries: 1, Error_code: 2003
2022-04-27T21:58:22.810750Z 2 [...
0
votes
0
answers
66
views
Why mysql 8.0.17 is clean after crash
I was working with my website and after I started a very huge process in the server (CentOS 8.5.2111), the server can not allocate more tasks because memory full, and MySQL (8.0.17) started with ...
EsopMx
0
votes
0
answers
21
views
How to better improve a question bank database
I am designing a database to store questions
The question bank here is an entity, each user can create multiple question banks
Each question bank has multiple questions
The types of questions include ...
- 37
0
votes
2
answers
65
views
The UPDATE queries is 50x slower than its SELECT query
The UPDATE queries is 50x slower than its SELECT query.
I have a table named sync_read and the have the following columns
id
club_id
created_at
user_id
queue_id
The table have the following Indexes:
...
- 101
0
votes
0
answers
90
views
mysql: setting default default_collation_for_utf8mb4?
How to set the value for "default_collation_for_utf8mb4" to "utf8mb4_general_ci", globally , or when the mysql 8.0 server started ?
I tried
[mysqld]
init_connect='SET ...
- 111
1
vote
0
answers
18
views
Data in one or more tables corrupt after recovering tables with alter table discard/import tablespace
I accidentally dropped my schema and had to recover all the tables per the steps in this post; Recover schema and the data in it from accidentally deleted schema
However, I notice that all of the ...
- 11
0
votes
1
answer
51
views
MySQL insert on an empty table takes 38.91ms
This DB is running in a cluster.
Insert/Update queries are taking more than 30ms to execute. Select queries are quick with less than 5ms.
We are already following this guide: here
From Laravel Query ...
- 113
1
vote
1
answer
34
views
MySQL master slave replica interrupts after service restart or reboot
I've been using the master slave replica to keep a mySQL database and its disaster recovery perfectly aligned.
I noticed that unfortunately every time the mySQL service restarts, or one of the server ...
- 11
0
votes
1
answer
37
views
very simple amateur question. Build a table then do group_concat
I am very new to SQL and don't have much intuition for all this. I have lots of coding experience but nothing in databases.
I have two scriptthat work in isolation, but I don't know how to stitch ...
- 1
0
votes
0
answers
23
views
Сan't automatically connect to RDS via the script?
I'm using script on ec2 user data, that connects to rds. But the script doesn`t work.
#! /bin/bash
yum update -y
yum install -y httpd
service httpd start
usermod -a -G apache centos
chown -R centos:...
1
vote
2
answers
31
views
Mariadb Benchmarking - Low transaction and query per second
We have a MariaDB 10.3 on a Redhat Server (VM) with 90 GB Ram and 20 VCPUs. I'm trying to optimize the Database. I installed Sysbench for Benchmarking and after a 50 seconds test I got this values (...
- 155
0
votes
0
answers
33
views
Problems exporting a database with phpmyadmin
I created a database using phpmyadmin, when I try to export it, it gives me an error on export, and later the SQL module stops and it gives me the following error on xampp:
I have already tried ...
0
votes
1
answer
33
views
Recover schema and the data in it from accidentally deleted schema
I did something careless. I deleted a schema. I have backups of the \mysql...\data\refadmin folder so I (hope) I have all the tables and data. That's backed up daily.
When I look at work bench, I ...
- 11
0
votes
2
answers
42
views
Command SHOW MASTER STATUS different in master and slave
I'm using Bitnami MariaDB. I've done fixing the replication and now both Slave_IO_Running and Slave_SQL_Running has value yes. This is the whole output:
*************************** 1. row *************...
0
votes
1
answer
39
views
MYSQL split date range columns into dates
I have a table [T] with columns id,start_date, end_date. I want to get id, date, where id is split into multiple dates using start date and end date.
For example:
Input:
id
start_date
end_date
1
...
-2
votes
1
answer
16
views
Mysql table relation for a user with multiple business
How to properly implement mysql table relation such that a user can create multiple business and each of those business can have multiple users. thanks
- 1
0
votes
0
answers
34
views
LOAD DATA LOCAL INFILE - server's temporary file storage
While reading docs, I came across following:
When LOCAL is used, the client program reads the file and sends its
contents to the server. The server creates a copy of the file in the
directory where ...
- 111
1
vote
1
answer
31
views
prevent a galera node from bootstrapping
I have bootstrapped a node as the first node in a new galera cluster. then when I joined another node to it. the node joind as bootstrap but there are no grastat.dat file in that joind node because I ...
1
vote
1
answer
85
views
Simple query with a single join very slow
I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
1
vote
3
answers
82
views
MySQL concurrent UPDATES
Say we have the following situation in an application using MySQL where a User can buy an Item and each Item has a single buyer but the Item's price may change.
Pseudocode :
BEGIN TRANSACTION
...
3
votes
1
answer
29
views
MySQL replication setup based on an LVM snapshot – am I forgetting anything?
I'm tasked with starting a brand new replica of a client's MySQL 5.7.35 database server. While I've observed others doing this in the past in various circumstances, I haven't been in charge of this ...
- 135
0
votes
0
answers
25
views
Automatically transform `VARBINARY` column in SQL GUI/editor
I use a VARBINARY(16) field to store an IPv6 address in our database.
I can use a query like the following to transform the binary to a textual representation:
SELECT INET6_NTOA(ip) FROM table
...
- 377
0
votes
0
answers
81
views
MySql Workbench not responding?
I tried to start the server , MySql Workbench shows me this error
In the Services ,MYSQL80 is already running
0
votes
2
answers
64
views
mysql cpanel database using too much memory!
I've been trying to figure out why mysqltuner is reporting such dangerous amounts of maximum possible memory usage 63.2G (404.10% of installed RAM).
I'm an amature at this and could do with some help ...
1
vote
1
answer
20
views
real-time copy of MYSQL
I don't know if this is at all possible, but I need to somehow copy in real-time to a slave MYSQL that I can access for stats, instead of risking the MYSQL database that is being used LIVE for our ...
RUSSELL HARROWER
0
votes
0
answers
28
views
MYSQL8 unknown runaway memory
First thank you, I am not very knowledgeable in MYSQL and am purely self taught.
I moved from metal to AWS(EC2, with maxed out IOPS:3000) and in the process we also upgrade from MYSQL5.6 to MYSQL8.0....
1
vote
2
answers
35
views
how to properly index a varchar column that won't use LIKE
I've been experimenting with EXPLAIN and noticed that the row count was big for our query so I tried adding an index to the VARCHAR column. First I added FULLTEXT but it ended up just making the query ...
- 113
0
votes
0
answers
63
views
Improve MySQL REGEXP query with any of multiple words MATCHING
I noticed that REGEXP query works well with few words but lose a lot of performance by adding more words.
Is there any way to improve a query like this?
SELECT * FROM table WHERE textfield REGEXP ('(...
- 207
0
votes
1
answer
32
views
Using SELECT by key value, the smaller the result count, the slower the query performance
In my MariaDB database I store combined metadata from multiple other sources (face databases). There is a 'main' table containing great majority of data, and each row is identified by both it's own ID,...
- 1
0
votes
2
answers
27
views
logging EXPLAIN output of slow queries in MySQL
I am trying to understand if there is functionality like auto_explain in PostgreSQL for MySQL. Specifically, I am interested in logging the query plan (not just the query) for all slow queries in ...
- 141
0
votes
1
answer
37
views
SQL - Find passenger who has boarded with exactly one specific company
Cheers, I have the following entities inside my database:
1. Passenger(id,name,surname, dof)
2. Company (id, name, code)
3. Trains (id, model)
4. Company has Trains (company_id, trains_id)
5. Trip (id,...
0
votes
1
answer
21
views
MySQL table data comparision
Due to some requirement i am moving data from mysql to other database which is also mysql compatible. Here, the mapping is pretty much 1:1 and few columns might be different.
I need to run a query to ...
- 77
0
votes
1
answer
95
views
0 rows from the .csv (semi colon) are imported into MySQL Workbench
I am trying to import the .csv below (columns and 3 first records in txt in differenet blockquotes) into MySQL Workbench using the Table Data Import Wizard. I have tried many ways and realized that ...
2
votes
2
answers
307
views
MySQL Innodb, why does the size of primary index depend upon the order of insertion of records
I have a table with this schema
CREATE TABLE `UserIdPhoneNo` (
`userId` int NOT NULL,
`phoneNo` bigint NOT NULL,
PRIMARY KEY (`userId`,`phoneNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
I am ...
- 123
0
votes
1
answer
12
views
For entities with common columns, which better to process separately or centrally
We all know that there are many types of invoices
Take the invoice for my environment as an example
Invoices include VAT invoices, which are divided into general invoices and special invoices
Both ...
- 37
0
votes
0
answers
13
views
Finding duplicate records in a subset
I have 3 tables:
users
orders
order_items
I am trying to get a list of all orders by the same user that have the exact same order_items.item_id within a 24 hour period representing the user having ...
- 359
0
votes
2
answers
30
views
Is the use of Stored Procedures in MySQL 5.7 recommended?
I am working in a company where we have an ERP system running on a dedicated server with LAMP (2 x Octa-Core E5-2620 V4 2.1GHz w/HT 32 threads with 32GB of RAM and two 480 GB SSD in hardware based ...
-1
votes
1
answer
68
views
how to show return zero if no row return?
I spent my half day on it, but still did not find solution, I tried COALESCE,IFNULL, IS NULL, IF conditions, <=>, IS NOT NULL etc but nothing work for me. The query is
SELECT
COALESCE(SUM(...
0
votes
0
answers
19
views
Is this ER diagram correct?
I am working on my first ER diagram, for a clothing brand case study. I was wondering if my work is correct. Any feedback is more than welcome. My most important concern is the key relationships ...
0
votes
1
answer
26
views
MySQL Group replication failover detection while node is recovering
I'm trying to get a solid MySQL group replication load balancing / failover set-up. Currently I use keepalived to share one private IP to connect to the loadbalanced MySQL GROUP REPLICATION cluster, ...
0
votes
1
answer
36
views
Why my simple searching query is too slow?
I don't know why searching in my "cities" table is so slow.
My query looking for a table "cities" located about 25km from the city.
I use this simple query and the database takes ...
- 73
0
votes
1
answer
18
views
Out of disk space while dropping columns from a mysql table
So, my setup is a little weird. I've got a laptop with 250 GB of memory and a table with roughly 100 million rows (InnoDB), which occupies around 130 GB of memory.
The thing is, this table includes ...
- 111
1
vote
2
answers
56
views
MySQL Binary Logs in Backup-Restore
I have a problem with MySQL binary log files. I want to tell you something about my database system:
I have a master-slave replication with two database instances.
Our master database creates 1.7GB ...
- 11
1
vote
0
answers
19
views
How to determine from which script/service access is denied for specific user in Mysql?
On my client server there is a lot of services and scripts running.
In MySQL error.log I get this NOTE:
2022-04-14T19:32:31.962446Z 490136 [Note] Access denied for user 'root'@'localhost' (using ...
- 11
0
votes
4
answers
69
views
Practical way of finding last update/insert/delete of any table in a database?
OS: W10.
This question is specifically about what's possible in MariaDB, specially version "Ver 10.16 Distrib 10.2.11-MariaDB".
Edit
In light of the answer by Rick James I have now upgraded ...
- 101
0
votes
2
answers
118
views
MySQL: SUM() returns multiple rows when used with joins and grouping
I expected by query, which uses SUM() to return one row, containing the total of all rows. However, it returns multiple rows. SUM() is not adding the rows.
SELECT SUM(DISTINCT `sales`.`sale_shipping`) ...
0
votes
0
answers
15
views
Deleting rows from a tag_map if parent tag exists
I have a simple tag_map
CREATE TABLE tag_map
(
article_id int(11) unsigned NOT NULL,
tag_id int(11) unsigned NOT NULL,
INDEX(tag_id),
PRIMARY KEY(article_id,tag_id)
) ENGINE=InnoDB
There is a table ...
- 4,179
0
votes
1
answer
146
views
Mysql service stops with [ERROR] InnoDB: CORRUPT LOG RECORD FOUND
In my Plesk Obsidian version 18.0.42 running on CentOS Linux release 7.9.2009 with mysql version 10.2.43-MariaDB. All of a sudden database has stopped working. Mysql service never comes up on start ...
- 130