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
0 votes
0 answers
19 views

Verify if data exists within the queried table or not?

select * from table_name where conditions table_name is a join of various tables A,B,C... conditions are the conditions. Now, I want to verify if the above table contains data with B.ENABLED='N' or ...
barnyard9's user avatar
  • 103
0 votes
0 answers
32 views

Mysql replication slave is not sysnc gettting error Slave_SQL_Running: No Error executing row event: 'ABC is missing for table [closed]

MySQL replication was set up using docker-compose mysql:8.0.20 as the current running DB master. All databases had a size of 50GB. After a few days, the slave was down for 6 hours and was unable to ...
V S devops's user avatar
0 votes
1 answer
8 views

Google Datastream errors on larger MySQL tables

I have set up a Datastream service, in order to replicate data from Cloud SQL (MySQL) to BigQuery. Everything is set up correctly, connection works. But the weird thing is that only tables < 10mb ...
Spock 's user avatar
  • 101
1 vote
1 answer
54 views

MySQL default TRANSACTION ISOLATION LEVEL

AFAIK, in MS SQL, PostgreSQL, Oracle and probably others, the default transaction isolation level is READ COMMITTED. Why in MySQL and MariaDB it is REPEATABLE READ?
i486's user avatar
  • 225
0 votes
1 answer
20 views

In MySQL master-slave could it happen that the oldest log bin data gets deleted before the slave has read it?

I have a MySQL database with a large number of individual tables having the same definitions. The number of tables is about 2000-3000 and their total size is about 50 GB. Of course they have different ...
0xC0DEGURU's user avatar
-1 votes
2 answers
52 views

MySQL 8 - very slow query when ORDER BY is used with hash join

I encountered an issue today and I'm not sure if this is a bug in MySQL 8 or if I'm doing something wrong. Preface At the bottom of the post I attached a query to reproduce the issue locally. I have ...
samu's user avatar
  • 101
0 votes
1 answer
19 views

Handling Id Uniqueness With a Single Key or with a Composite Key

First off, I'm new to architecting a DB and I'm looking for help from people who are more experienced and are able to point out gotchas and issues that I might not see. I'm building out a new DB ...
jeremysawesome's user avatar
0 votes
1 answer
47 views

Combine tables to find date of last value changed

Trying to query the date that a field changed to a zero amount. Here is some of the data from the tables involved: -- mbMaster is the master table for all 'm' type tables (mvTable, mcTable), but does ...
tkmagnet's user avatar
0 votes
1 answer
28 views

drop database hangs with "Waiting for table metadata lock" in MySQL8 RDS on AWS

We have a large table in a database that a user tried to run DELETE * FROM table on. It was still running the next day so we killed the process. I tried renaming, dropping and truncating the affected ...
George's user avatar
  • 126
-1 votes
4 answers
49 views

MySQL - Unexpected behaviour of a LEFT JOIN (or maybe, I'm confused)

I have a simple SQL query running on MySQL, which doesn't return the results I expect. I would have sworn that it used to work, and I feel like I'm missing something obvious. UPDATED - Sample data for ...
Diego's user avatar
  • 99
0 votes
1 answer
36 views

How do I change the MySQL engine ERROR 29 (HY000): File '*.MYD' not found (OS errno 2 - No such file or directory)

I have a MySQL table Blog in a database Blog: mysql> SHOW TABLE STATUS WHERE Name = 'Blog' \G *************************** 1. row *************************** Name: Blog Engine: ...
P. James Norris's user avatar
1 vote
2 answers
57 views

Why SSD performance is not better than HDD in specific cases?

I am using MySQL 8 on Ubuntu 22.04. All my tables are MyISAM. Recently I bought 1 TB SSD Samsung 980 PRO to replace the existing 7200 rpm HDD in order to boost read performance. My test machine is one ...
Ivaylo's user avatar
  • 133
1 vote
0 answers
20 views

Cyclic Grants in SQL

I wonder what the result of the following grant/revokes is according to the SQL standard. Suppose A created a table tab and there are four users (i.e., A, B, C and D). The SQL commands are: grant ...
anotherme's user avatar
3 votes
5 answers
832 views

Is COMPACT a better format for fixed length rows than DYNAMIC?

In MySQL / MariaDB I have this table with fixed-length rows (no VARCHAR, TEXT, etc) CREATE TABLE trigram ( id BIGINT(20) NOT NULL, trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci', ...
O. Jones's user avatar
  • 563
0 votes
1 answer
30 views

Mysql read speed [closed]

How fast can a single physical Mysql instance run the following query on a 2 column table that has about 100GB? SELECT short_url FROM long_to_short WHERE long_url = :in_long_url I recognize this ...
Bi Act's user avatar
  • 127
0 votes
1 answer
31 views

Table of 4 columns "definition is too large"

I'm trying to optimize a large(4M rows) and busy table of mine. It's currently CREATE TABLE `person_search` ( `token` CHAR(63), -- each word of the person's name `person_id` BIGINT(20) ...
user avatar
0 votes
1 answer
46 views

How to check data type in mysql?

In PL/pgsql,pg_type() give use data type of a variable. As Example: select pg_typeof(3) as type_of; -- output: integer select pg_typeof(true) as type_of; -- output: boolean Similarly, how to check ...
Art Bindu's user avatar
  • 101
1 vote
1 answer
16 views

How to import MySQL RAW Database files without connection error

unfortunately I did not create a 'dump' file with the backup (Data Export) process on MySQL Workbench 8.0.26. When I copy the old database files to the new Data directory, the server fails to start ...
life777eternal's user avatar
1 vote
1 answer
26 views

Does MySQL have a way to limit update/insert statement times?

This is similar to the statement_timeout of postgresql. I'm looking to have the query error if it takes longer than x seconds. I'm aware of SET MAX_EXECUTION_TIME, but that only affects select queries,...
juacala's user avatar
  • 115
3 votes
1 answer
474 views

Does mysqldump lock entire database or just a table by table?

I'm wondering if during a mysqldump executed in the following manner mysqldump --all-databases --master-data > dump.sql all tables are locked for the entire process, or just a table by table? In ...
0xC0DEGURU's user avatar
-1 votes
0 answers
19 views

Looking up a connecting flight

Suppose a database has two columns, arrival and destination. I want to check flights from Miami to Usuhaia. There are two flights I have to take. ARRIVAL | DESTINATION MIA, EZE EZE, USH So I would ...
Ken Calderon's user avatar
0 votes
2 answers
33 views

How to AUTO_INCREMENT only on INSERT?

I have one of this situations : CREATE TABLE table ( `col1` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `col2` VARCHAR(512) NOT NULL COLLATE 'utf8mb4_general_ci', PRIMARY KEY (`col1`) USING ...
Max Muster's user avatar
-1 votes
0 answers
25 views

The Count and GROUP BY and GROUP_CONCAT does not group one field and remove duplicates [duplicate]

This is my query: CREATE TABLE tab ( `id` INTEGER, `user` VARCHAR(15), `userid` INTEGER, `user_id` INTEGER, `ipaddr` VARCHAR(7) ); INSERT INTO tab (`id`, `user`, `userid`, `user_id`, `...
Saeed's user avatar
  • 111
1 vote
1 answer
27 views

Find the counts and occurrences of a column based on the name of another column in MySQL

I have a table like this: id user userid user_id ipaddr 1 'Client' 10 0 1.1.1.1 2 'admin1' 1 0 2.2.2.2 3 'Client' 3 0 ...
Saeed's user avatar
  • 111
0 votes
1 answer
25 views

How to implement historical data with this EERD?

I am looking for a way to implement historical data with the following EERD (what I have tried, below the image): What have I tried? Temporal tables Didn't work because I use MySQL and it has no ...
user23600793's user avatar
0 votes
0 answers
36 views

Is InnoDB Engine a B Tree or a B+ Tree? [duplicate]

I am confused about the InnoDB Engine. In the original document, it states that InnoDB uses a B Tree. However, in some other articles, they claim that InnoDB uses a B+ Tree. https://dev.mysql.com/doc/...
haint95's user avatar
0 votes
1 answer
40 views

What could cause replication to break on MySQL when replicating from 5.7.40 to 8.0 replica

We have a MySQL primary running on 5.7.40. We are in process of testing the upgrade to 8.0.36 (this is on AWS RDS). We had 2 MySQL-8 replicas and 2 MySQL-5.7 replicas replicating from the primary. At ...
Rahul's user avatar
  • 119
1 vote
3 answers
42 views

Last cell value in a grouped by query

I have a table related to game results with more than 300 000 records, where each row keeps player attack. This is simplified table example. I keep an attack id, date, when attack was done, unique ...
anton's user avatar
  • 117
0 votes
0 answers
20 views

Master to Slave Replication pausing

I would like to get insights from everyone here in this community. After we apply indexes on our Tables on Database. If some users generate big reports. Replication suddenly stopped and what we are ...
user avatar
0 votes
0 answers
11 views

faild to connect to Mysql at 192..x.x.x:3336 through ssh tunnel at [email protected]:22 ssh

enter image description here "This is the result when I attempt to connect: I'm trying to connect MySQL localhost to my session on the server. (I'm connected to my session on the server via SSH ...
user avatar
0 votes
1 answer
22 views

How can I sort from the least dependent view to the most dependent view?

I have this data: CREATE TABLE `ViewDependencies` ( `ViewName` varchar(255) DEFAULT NULL, `DependentOn` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;...
Saeed Neamati's user avatar
2 votes
1 answer
71 views

Composite index with a range column in the middle

My understanding is that when designing a composite index a column, which will be used for range tests should be placed at the end of this index, because the usage of the columns in composite indexes ...
Ivaylo's user avatar
  • 133
-1 votes
1 answer
22 views

Slow query on mariadb

I recently moved the database from mysql to mariadb, but the same query is executed in mysql in less than a second, but it takes 20 minutes in mariadb. query : SELECT km.beleg, km.art, ke.id as KID ...
amir hosein cheraghchi's user avatar
1 vote
2 answers
39 views

Finding files a user has access to is slow when ordering

I'm working on a system where users are sorted into groups, and then those groups are applied on files. At a high level, what I need to do is to be able to pull out the 10 most recently-updated files ...
chraume's user avatar
  • 11
0 votes
1 answer
59 views

MySQL CLI asks for password but doesn't use it

I'm trying to connect to a remote MySQL database for replication. The database is on a machine that's part of a secured network, so after opening the security enough to allow my replication ...
Spencer Ke's user avatar
0 votes
0 answers
29 views

Setting up a Docker compose for Mysql and Vitess

With the recent announcement of Planetscale shutting down its lower and free tiers I've found myself in a bit of a situation. I have a Serverless app which is connected to a Mysql database in ...
Leonardo Petrucci's user avatar
0 votes
1 answer
24 views

MySQL Amazon Aurora - why is CPU low while max vCPU is high?

In MySQL Amazon Aurora, is it normal for CPU to be be low while other metrics are above the max vCPU line?
mstrom's user avatar
  • 133
0 votes
0 answers
21 views

Using Clone for addInstance on MySQL Innodb Cluster uses 100% Memory

When trying to add a new node to a MySQL Innodb cluster using Cluster.addInstance() and selecting the Clone option, within 2 or 3 minutes the donor nodes memory usages hits 100% on a server with 36Gb ...
mwarble's user avatar
1 vote
2 answers
35 views

MySQL exclusive lock not appearing to work for new child rows

I have some logic in my application which I think results in the following MySQL calls, however when I get two of these run within a couple of milliseconds, I get two incompatible child rows. Start ...
mjaggard's user avatar
  • 125
0 votes
0 answers
21 views

mysql replication skip some batch delete query

I set up a master slave mysql replication. I have an Event that run stored procedure and delete some rows (about 3500 rows). recently it skip some delete query in slave. for example it does not delete ...
user avatar
0 votes
1 answer
38 views

After Insert and Update Trigger on the same table

I have the following table in MySQL: CREATE TABLE `userlogtype` ( `UserLogTypeID` int NOT NULL AUTO_INCREMENT, `UserLogType` varchar(150) DEFAULT NULL, `InsertUser` varchar(100) DEFAULT NULL, `...
Steve Miller's user avatar
0 votes
1 answer
42 views

Why is mycli history file appearing in home directory?

I started using mycli and want $HOME/.mycli-history to be generated and updated from $HOME/.cache/mycli/.mycli-history Moved $HOME/.myclirc -> ~/.config/mycli/.myclirc Created alias mycli='sudo ...
Vivek Jha's user avatar
  • 155
1 vote
1 answer
53 views

Which indexes create to speed up a query with where

I have a query but I do not know which index create to speed-up it. This is the query: select `id_my_table` from `my_table` where now() between created_data and ifnull(finish_data, now(...
Giacomo M's user avatar
  • 163
2 votes
1 answer
62 views

MySql Aurora - how often to run ANALYZE TABLE?

I have a database that has existed for about 10 years, with hundreds of tables, many of which have several million rows. We don't have a DBA and we have an increasing number of slow queries. We're ...
mstrom's user avatar
  • 133
0 votes
2 answers
23 views

getting records that don't have a corresponding record in another table

I'm using MySQL 8.3 and would like to get conversations that don't have a messsage with and index value of 0 based upon the following schema: conversations id messages id conversation_id index (...
timpone's user avatar
  • 647
0 votes
0 answers
23 views

Migrate cold database (freebsd) to mariaDb on ubuntu

I have a little bit of a pickle: I have a freebsd based jail that ran nextcloud with mysqldb The machine running the jail is broken. I still have the jail with the data. Now I was wondering if I can ...
chas0r's user avatar
  • 1
1 vote
1 answer
37 views

MySQL Amazon Aurora - a few slow queries causing extreme performance issues

I'm a dev, not a DBA, so pardon my ignorance. I'm running on MySQL Amazon Aurora, single-instance (with a replica for failover). A few days ago, my application had a handful of extremely slow queries (...
mstrom's user avatar
  • 133
1 vote
2 answers
50 views

Query to return latest record between 2 tables

I'm trying to pull the most current date (cusPurDt) and corresponding amount (subAmt) by customer (cusID/subID). Here is some sample data: create table customer ( cusID char(5), cusPurDt ...
tkmagnet's user avatar
0 votes
1 answer
45 views

How to check MySQL running queries when there are too many connections?

Situation: Something goes wrong, and too many queries accumulate until the limit is reached and new connections get an error: ERROR 1040 (HY000): Too many connections To list processlist, I need to ...
gilbertasm's user avatar
0 votes
0 answers
18 views

Percona XtraBackup questions about full and incremental backups

I had a few questions on the Percona forum, and I'm unsure if I was being clear in my questions or not, however I'm curious how full and incremental backups work with XtraBackup. When doing ...
alturic's user avatar