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
1answer
32 views

need help with joining the 2 tables on closest date

I am a newbie in SQL and need help with the joining of these 2 tables on the closest date. Thanks in Advance! Table A billing_start_date id status 2020-07-27 100 active 2018-12-27 101 inactive ...
0
votes
0answers
10 views

how to sync one table of AWS RDS to another RDS

I have requirement like need to synchronize one table of data (contain user list) in AWS RDS instance to another RDS Instance. only particular user's table need to synchronize. ex: RDS-1 have schema ...
1
vote
3answers
51 views

Getting fatal error 1236 from master when reading data from binary log in mariadb master master replication

I am facing the situation similar to this Error 1236 - "Could not find first log file name in binary log index file" I have tried all the answers but I am still not able to get rid of this ...
6
votes
1answer
295 views

How do you tune innodb_read_io_threads?

The default value of innodb_read_io_threads and innodb_write_io_threads is 4. How do you check if your server load needs a higher number of threads? As I regularly check show engine innodb status \G, ...
0
votes
1answer
18 views

Mysql - Set timeout for Alter table & Update scripts

On MySQL 5.7. I would like to prevent all alter table scripts \ update scripts that are taking longer than 10 minutes to run. Is it possible? I've tried to set MAX_EXECUTION_TIME and it does work but ...
0
votes
0answers
10 views

Mysql error slave does not connect to master db

I'm setting up a replica, made every configuration as I should. but when I run show slave status;, I get the result below +----------------------+----------------+----------------+-------------+-------...
0
votes
1answer
54 views

SELECTing n rows for each GROUP with WHERE condition

I SELECT n rows for each ArticleID from TagMap table SELECT a.ArticleID,a.TagID FROM TagMap a LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID GROUP BY a.ArticleID,a.TagID ...
1
vote
1answer
18 views

mysql - import only data from full dump?

I have a full dump from database A. I want to import only the data of this dump into database B. Database B's structure is the same as database A only it misses some tables. How can I do this? The ...
0
votes
1answer
14 views

MySQL 8 install doesn't include mysql.exe (Windows)

I've installed MySQL 8.0 using mysql-installer-community-8.0.26.0.msi. Mainly these options: But there's no binaries for connecting (mysql.exe) or dumping (mysqldump.exe): What else do I need to ...
0
votes
1answer
25 views

mariaDB with a 3 node galera cluster is eating more and more RAM

I am running mariaDB 10.3.29 in a 3 node Galera Cluster on 3 VMs with 14G RAM each. Despite configuring to best knowledge, it continues to eat more and more RAM and after around 3 months the machines ...
0
votes
1answer
36 views

How to speed up queries for 2m Datasets in PHP & Mysql

We have a SaaS Application site, where we would have over 2M records in a table. Server version: 8.0.19 - MySQL Community Server - GPL Protocol version: 10 Even with the indexes these queries are ...
-2
votes
2answers
18 views

Create Table in MySQL with Exactly One not NULL value in a combination of columns

I have 3 tables namely: 1. department_heads DepartmentID StaffID 2. sub_department_heads SubDepartmentID StaffID 3. branch_managers BranchID StaffID Now what I want is not have staff being a ...
0
votes
2answers
61 views

CASE WHEN with Multiple conditions [closed]

I am trying to create new column using following conditions in MySQL Workbench. I followed this example Nested case statements vs multiple criteria case statements and i am able to get all the results ...
1
vote
0answers
30 views

Mysql 5.6 tmpdir change deletes some users

We have a Percona-mysql 5.6 setup with one master and one replica. After trying to do a big ALTER TABLE on it, it failed because the tmpdir was inside of the memory and therefore wasn't big enough. ...
0
votes
1answer
35 views

MariaDB lost database MYSQL after 1-2 days

I have a big problem and i tried to fix this since weeks. After 1-2 days i lost the database "mysql" in MariaDB (Server version: 10.5.12-MariaDB MariaDB package) running on OpenSuSe Leap 15....
0
votes
4answers
23 views

Select the rows only when all sub columns contain the same value [duplicate]

Suppose I have 3 columns, Item, SubItem, Number. I want to select the rows only when all the subitems have number as 0. For example in the below table, only Phone and Pant should be selected since all ...
0
votes
0answers
24 views

MySQL Group Replication Vs InnoDB cluster

I am new to MySQL world. I would like to understand the difference between MySQL Group Replication and InnoDB cluster, is there any other differences apart from router configuration for InnoDB cluster?...
0
votes
1answer
28 views

Best way to start Master - Slave replication

I'm trying to get replication set up between two servers with the eventual goal of switching the slave over to be the master (I'm trying to upgrade the hardware stack with as little interruption as ...
1
vote
1answer
26 views

Find all the artist names with songs in all the possible mediatypes

I have a basic problem about create a query for listing artists with a condition of the artist have songs with all format type, the database have three table ( check ER diagram ). The Question is: ...
0
votes
2answers
86 views

Scheduling - First or Next Available Appointment with MySQL

I am looking for first available appointment with all doctors or next available appointment based on the given date. Tables:- doctor_availability --> This is to store doctors availability for all ...
1
vote
0answers
42 views

JOINing two mapping tables

I have two mapping tables as CREATE TABLE ProjectDevelopers ( DeveloperID int(11) unsigned NOT NULL, ProjectID int(11) unsigned NOT NULL, INDEX(ProjectID), PRIMARY KEY(DeveloperID,ProjectID) ) ENGINE=...
1
vote
2answers
85 views

I am burning trough NVME SSDs, 3x 60GB MySQL databases (web crawler)

I made a web crawler that is split on 3 computers, each of them makes many db queries, I think around 200 3000-4000 queries per second (each server) non-stop, with short spikes to 12000-14000. I am ...
0
votes
2answers
35 views

Is it possible to update a variable after using it (rather than before)?

@x=0 select case when col>1 then @x:=@x+1 when col=1 then @x else @x; @x:=@x+1 end as x from t I want to do something like what's seen above in MySQL. The contents between the WHEN ...
0
votes
2answers
24 views

Update rows based on a recursive CTE query result

This query shows all lines recursively based on a uuid / parentUUID relationship: WITH RECURSIVE files_paths (id, parent) AS ( SELECT uuid, parentuuid FROM core_data WHERE uuid = '2c828bbb-...
0
votes
2answers
39 views

Limit and offset (pagination) for rows with related data (with joins)

I have tables Alpha and Beta. Beta belongs to Alpha. create table Alpha ( id int auto_increment primary key ); create table Beta ( id int auto_increment primary key, alphaId ...
0
votes
3answers
75 views

Storing huge file collection in database

I'm in a planning phase of developing a web site where public users will be able to store their documents, by nature it could be anything like documents, pdf, audio, video or images. We are expecting ...
0
votes
2answers
28 views

MySQL configuration. Query doesnt get returned result (DataGrip, DBeaver, PHP). Something times out [closed]

I am running a simple INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE ... query using either DataGrip or DBeaver or PHP and it always doesnt register the result. The SELECT inside takes about 10 ...
0
votes
0answers
47 views

JSON_EXTRACT always returning null in stored procedure

Hi I'm new to SP and JSON function in mysql DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `adocs_sp_web_questionnaire_has_child`(IN `var_has_child_text` TEXT, IN `var_parent_id` INT, OUT `...
0
votes
0answers
15 views

display the transaction for a given GTID in MySQL

is there a way to show the statements of a transaction for a given GTID ? I know the binlog, if the correspond binlog file was not purged, can be used, but I'd like to know if I could see any ...
0
votes
1answer
23 views

Mysql - how to create an id column for a specific column

i want to create a table that have 3 column : visit_times : have a unique id for every specific visitor visitor_ID Date i tryed to create the table as showing this code CREATE TABLE test.table ( ...
0
votes
1answer
21 views

Will mysql import overwrite my database

So if I take a mysqldump of my production database called production_database, but I've made changes to by test environment database already, called work_database, adding function/stored_procedures/...
0
votes
2answers
35 views

What exactly does this statement do?

The statement SELECT password FROM users WHERE username='' UNION SELECT 'test' FROM users returns password -------- test and the statement SELECT username, password FROM users WHERE username='...
1
vote
1answer
51 views

Creating MEMORY table with varchar in MySQL

I need to create TEMPORARY tables for processing (via JOIN) a large set of data (1M rows), which have varchar columns. If I use ENGINE=MEMORY, it will change varchar to char. Then, the data will not ...
0
votes
1answer
10 views

Selecting Values from same table with different condition

I have a table where I need to get aggregate values based on status. I need a sum where status =1 as one column and status =2 as different column. Here is my table sample: | id | name | did | ...
0
votes
1answer
12 views

Slow group by execution after update to MySQL 8.0.26

I am running a larger DB with tables > 200M datasets on a dedicated Ubuntu 20.04 machine. Since a few days queries that use group by are extremly slow. I suspect that this is new since the last apt-...
0
votes
0answers
32 views

Error Code: 1114. The table is full

Here is what I tried so far: innodb_data_file_path = ibdata1:10M:autoextend tmpdir to new path max_heap_table_size=256M tmp_table_size=256M I check the C disk, which has over 100G remaining. My ...
0
votes
1answer
51 views

RAM overflow in MYSQL

I have a server with a database in MYSQL 5.7.34 MySQL Community Server (GPL) on weekends has a very high tractability so it has been responding very slowly, I saw the logs and it gave me the following ...
0
votes
1answer
39 views

MySQLDump understanding

How come when I write the following line in my CMD: mysqldump -hHOST -uAdmin -p TestDatabase --single-transaction --routines --triggers > "C:\testdump_backup\testdump.sql" Then the file ...
0
votes
0answers
31 views

Backup a large database with MySqlDump / Import useing MySql

So I'm going to make a backup system for my database and wanted to know if there was something specific I have to know before doing too much. I've read that I mught have to change some database ...
0
votes
1answer
43 views

MySQL table with a lot of text

So I have this database (Size 3.1Gb total), but this is due to one specific table I've got, containing A LOT of console output text, from some test runs. The table itself is 2.7Gb, and I was wondering ...
1
vote
1answer
37 views

Incorrect datetime value while importing in to MySQL table

I'm facing an issue while importing a .csv file into a MySQL table. mysql> LOAD DATA INFILE '/var/lib/mysql-files/script_output.csv' REPLACE INTO TABLE incidents columns terminated by ',' ...
1
vote
1answer
43 views

mysql tunning with larga database

soo.. I have a server in a VM with a large database in MySQL 5.7 (centos), and I was trying to tune a little bit the server (32gb RAM) and, so far I'm running this my.ch I did use the perl script ...
0
votes
0answers
32 views

UPDATE rows and SET ordering depending on level and parent

I'm trying to write a small UPDATE query that makes sure all rows are reordered considering the parent_id. Needs to work under MySQL 5.6 / MariaDB 10 This is a DB Fiddle made for testing. So this is ...
0
votes
1answer
51 views

Why does this sql join query take much longer using a reference to a row from the first table in the 'on' clause versus using a literal variable?

I have two tables, 'auth' and 'ips', which I'd like to join in a query. Table 'auth': column name data type Extra id int AI date varchar(6) - timestamp varchar(8) - result varchar(8) - ip ...
0
votes
1answer
28 views

Data too long for column when to alter column definition

The column definition is as below: `code` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL I want to alter another table(option)'s code field as same definition: ALTER TABLE `...
1
vote
1answer
18 views

Altering mysql database column causes error in another column

First of all sorry everybody if this question sounds too basic. I have a Wordpress database and a table wp_comments. In this table, I have two DATETIME fields, whose default value is 0000-00-00 00:00:...
0
votes
1answer
22 views

service mysql reload results 'Access denied', but service mysql start works

Using Debian 10.10. I have a MySQL problem. service mysql reload results 'Access denied' service mysql start executes correctly. All databases are working correctly after starting mysql. Strange, ...
3
votes
2answers
67 views

Apply nth occurrence number on each distinct value in a column

I have a table with a column of values where each value occurs a variable number of times (i.e., one value may occur 1 time, and another value may occur 3 times). I need to add a column that ...
0
votes
2answers
52 views

database changes are lost in the next session

We just move to Amazon RDS. Now all the CREATE, UPDATE, and INSERT commands are reverting in the next session. All the tables are InnoDB and query caching is on. What could be the problem?
0
votes
1answer
88 views

What does it mean when slave status shows SQL running but IO not running?

What exactly does this mean, when the SQL is running but the IO is not running on the Slave in a Master-Master replication setup? There are no other errors showing when running show slave status\G ...