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.

Filter by
Sorted by
Tagged with
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 ...
user avatar
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 ...
user avatar
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 [...
user avatar
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 ...
user avatar
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 ...
user avatar
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: ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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:...
user avatar
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 (...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 *************...
user avatar
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 ...
user avatar
-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
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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
user avatar
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 ...
user avatar
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 ...
user avatar
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....
user avatar
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 ...
user avatar
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 ('(...
user avatar
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,...
user avatar
  • 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 ...
user avatar
  • 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,...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
-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(...
user avatar
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 ...
user avatar
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, ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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`) ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar

1
3 4
5
6 7
429