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.
22,628 questions
0
votes
2
answers
29
views
How to use mysqldump to transfer data to another server with a different database name
I have a production MySQL server and I want to copy everything to a development MySQL server (actually, I believe it is really MariaDB on the development side). Both servers are hosted (one Azure, the ...
- 101
0
votes
1
answer
108
views
How to reduce running time? Execution time exceeded
Excuse my grammar Im dyslexic:
The main issue is that after calculating all the lags and flows at the ISIN level, when I try to do the final grouping by bucket, asset class, and date, I'm forced to re-...
- 11
0
votes
1
answer
14
views
`--single-transaction` equivalent for mysqlsh util.dumpSchema
My database is using InnoDB, and when using mysqldump I have the option to set --single-transaction to avoid locking the tables while still having a consistent view of them.
I'm trying to switch over ...
- 103
0
votes
0
answers
53
views
MySQL loses connection on accessing some tables
MySQL:
On some specific tables in one database, SELECT or other commands immediately fail with "2013 Lost connection to server". Sometimes the initial "USE database" fails the ...
- 1
0
votes
1
answer
16
views
Unable to create root@localhost mysql 5.7 or 8.0.31
I have received a mysql environment to work on and currently Audits are being performed.
What I see in mysql.user is 'root'@'%' created everywhere and it is required that access to root is to be ...
- 226
0
votes
1
answer
20
views
How to calculate the intersection area between polygons in MySQL
I have a table parcels where I store polygons in a column. I'm trying to find all parcels that intersect with a user-provided polygon and calculate the intersection area in square meters.
The table:
...
- 1
0
votes
0
answers
20
views
MYSQL Authentication Error
I have a MYSQl Database hosted by our ocal ISP. IT recently crashed and they attempted to recover what they could from the VM.However, on core application (a Student Reporting System) fails to ...
0
votes
0
answers
17
views
varchar or blob for encoded data
I plan to store a lot of customer responses in a table I have used snappy.compress with utf_8 but now the table seems to occupy more spaces I tried with varchar and blob and blob seems to take twice ...
0
votes
0
answers
15
views
Issue when performing mysqldump - Matomo - MySQL error 2013
On premise setup, **Ubuntu 20.04:
CPU: AMD Ryzen 5 3600 6-Core Processor
Memory: 64GB RAM**
mysql Ver 8.0.40-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
We’re recently being confronted by an ...
- 1
0
votes
1
answer
31
views
Should I break a large user table into smaller tables for specific roles and information?
I am designing a database for a system that has a users table. Currently, the table has around 50 columns, which include:
Personal information (e.g., name, email, phone_number, address, etc.)
Work-...
- 101
2
votes
1
answer
32
views
Innodb - Can you safely ignore the warning "Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size"
We get this warning in our online shop
report.WARNING: Memory size allocated for the temporary table is more
than 20% of innodb_buffer_pool_size. Please update
innodb_buffer_pool_size or decrease ...
- 125
0
votes
0
answers
26
views
Failure Case of "drop_swap" Option in alter-foreign-keys-method for Percona's pt-online-schema-change Tool
I am looking at the option called "alter-foreign-keys-method" in the pt-online-schema-change tool where it uses the drop_swap. In the documentation, it says there are 2 risks:
First, for a ...
- 114
0
votes
1
answer
14
views
Mysql 8.4 tries to use mysql_native_password instead of caching_sha2_password ater master firewall reconfiguration
Faced strange mysql 8.4.3 behaviour with replication, using docker on both master and slaves.
Replication was initially set with caching_sha2_password and GET_SOURCE_PUBLIC_KEY=1 and everything was ...
- 11
0
votes
0
answers
27
views
Migrate thousand of data from excel into DB
I have rental data from year 2019-2024 in an excel
In that excel, the data consists of tenant name, monthly payment details from jan - dec each year
I have done massaging the data
I need to mapping ...
0
votes
2
answers
32
views
Disable MySQL ONLY_FULL_GROUP_BY while logging violations
We have attempted to upgrade a legacy database from MySQL 5.6 to 5.7 in the hopes of bringing it up to current, but quickly experienced queries that failed with this is incompatible with sql_mode=...
- 195
0
votes
1
answer
19
views
How can I replicate transactions one by one in mysql?
I have one source server and one replica server. And I set source_delay 30mins, replication is gtid based, mysql version is 8.0.24
If I accidentally drop table A, Can I apply transactions just right ...
- 1
0
votes
0
answers
42
views
MySQL Replication, Master Slave- Last_SQL_Errno: 1062, Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin
My mysql replication (master - slave) is failing with following error:
Last_SQL_Errno: 1062
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure ...
- 101
0
votes
0
answers
29
views
Does sorting reduce MySQL deadlock likelihood of INSERT/UPDATE batches?
We know that MySQL INSERT/ON DUPLICATE KEY UPDATE statements create gap locks which may lead to deadlocks between multiple threads.
If the INSERT/UPDATE is done in batches (either in a separate ...
- 97
0
votes
2
answers
40
views
innodb fetch time, not query execution time
A very basic query but from a huge table
SELECT profile_id FROM clients WHERE is_vip = 0
execution has a duration of 0.716s and data fetch time of 15.072s
Obviously my question is if I can speed this ...
- 101
0
votes
0
answers
25
views
Switch to master auto position from master_log_file and master_log_pos MySQL 5.7.44
MySQL Server - 5.7.44
I have a master and three slaves running on MySQL 5.7.44.
The GTID is enabled and I am using gtid_enforce_consistency.
I took a backup from a slave and built a fourth slave using ...
- 226
1
vote
1
answer
78
views
PostgreSQL equivalent to checksum table
What would be the fastest operation of checksum table, which returns the same value in MySQL and in Postgres if there is the same dataset in tables?
It could be a function, or just an equivalent of ...
- 275
1
vote
1
answer
34
views
Selecting Two Entries From a Related Table
I have two tables; "Flight" and "Person".
Person has three columns; "id", "last", "first".
Flight has three columns; "id", "P1_id",...
- 15
1
vote
1
answer
16
views
mysql dump produces strange binary blobs that cause problems on import
i am running backups of my mysql database with this command:
mysqldump -u admin -p`cat /etc/psa/.psa.shadow` --hex-blob --skip-opt --single-transaction --add-drop-table --create-options --quick --...
Daniel Dinkel
0
votes
2
answers
70
views
Will the query time increase?
I'm a bit of a newbie when it comes to DB Administration, so, please take it easy!
I have a MySQL database. In which there are about 100 tables. Right now, each table has approximately 50 rows. So, ...
- 111
1
vote
1
answer
42
views
Take a backup from specific range
I need to backup the data of a specific table by week and then restore it but without truncate table . This table contains the fechaRegistro
I prepared the following script but I'm not sure about the ...
- 49
0
votes
1
answer
34
views
Running numerous MySQL table creates and drops causes a sudden slowdown in query times [closed]
I have a PHP script that creates several tables, then later drops all of them. CREATE queries average about 0.05s, while DROP queries average about 0.03s.
However, if I loop the script and run it for ...
- 101
0
votes
1
answer
30
views
Where can I find more detailed MySQL deadlock logs?
We have a MySQL database in AWS RDS that was migrated recently from 5.7 to 8. One of our microservices running in ECS executes bootstrapping code at launch to iterate through a large list of table ...
GS-Scooter
0
votes
0
answers
19
views
xtrabackup - incremental backup needs target prepared with --apply-log-only
I Use Xtrabackup to backup several of my MySQL backups (all 8.0.39).
They all use an identical script.
The initial backup is:
xtrabackup ${MySQLConnectionString} --backup --slave-info --extra-lsndir=&...
- 2,186
-1
votes
0
answers
22
views
Which configurations should be set in order to perform (Query-)Benchmarks for (partially) encrypted databases for MySQL and PostgreSQL? [closed]
I am trying to measure the impact of encryption on the performance of a SQL-Database.
I want the database management system to always perform the same, replicable actions to display the results of a ...
- 1
0
votes
0
answers
8
views
Issues with SSL Authentication Using Multiple Certificates in MySQL Client (MySQL Java Connector)
I'm working with two MySQL servers, both of which are authenticated using SSL. On the client side, I have successfully connected to the first MySQL server after adding one certificate to the keystore. ...
1
vote
1
answer
27
views
Grant execute only in localhost
I have a user that can be accessed from any host (we are currently in development). But I want to limit the execution of stored procedures only for localhost. The problem is that the localhost user is ...
- 125
1
vote
3
answers
49
views
Are MySQL memory calculators accurate for complex workloads?
I recently used the MySQL Memory Calculator to optimize the configuration of my database. While the tool provides suggested values for settings like innodb_buffer_pool_size and query_cache_size, I’m ...
- 21
0
votes
0
answers
94
views
How can I install MySQL 5.7 on Ubuntu 24.04? [closed]
The company I am working uses an older stack, and I need to install MySQL 5.7 on my Ubuntu 24.04 to work on some projects locally. Does anyone know how to do this? I just can't find anything besides ...
1
vote
0
answers
18
views
MySQL reads the data directory at a wrong location
I have installed a MySQL 8.4 Server for a Drupal migration. I have done the migration and the files were transferred but nothing showed on the new Drupal page, but that is another issue. The MySQL ...
b.andreas04
0
votes
1
answer
25
views
MariaDB Server Overloading
I have a docker host on which have multiple mariadb containers each for one application.
But I have one particular application, my main application which overload the server eachtime that I start it ...
- 101
0
votes
0
answers
24
views
mysql innodb - copying db manually and register it
I'm creating a test setup such that at the start of each test, I will create an isolated db based on the original DB (40 tables, 14mb size total).
When using mysqldump+mysql or mydumper+myloader, the ...
- 101
0
votes
1
answer
38
views
Innodb fulltext search index broken / not updating
I have a table with a fulltext search column that was apparently broken and a regular query I run was not producing expected results. After quite a bit of tinkering and head scratching I decided to ...
- 736
0
votes
0
answers
43
views
Hierarchywise Sales Targets
I having the following data with me
Region
Designation
Employee Name
Sub Region
Sub Reg. Emp. Desigantion
Sub Reg. Emp. Name
Sub Reg. Target
Sub Reg. Emp. Reporting to
North
M.D.
MP
N1
A.S.M
AA
300
...
Manish Papreja
0
votes
1
answer
62
views
Loading an SQL dump is slow
note: I initially asked this question on stackoverflow, but was told superuser would be a better place for it. I was then made aware of the existence of dba.stackexchange, which seems like the right ...
- 101
1
vote
1
answer
39
views
MySQL order by with conditional clauses
I have these 2 tables
CREATE TABLE `users` (
`id` bigint NOT NULL AUTO_INCREMENT,
`status` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=330031656 DEFAULT CHARSET=...
- 203
0
votes
1
answer
30
views
Amazon Aurora RDS - invalid undo record
Context
Aurora RDS, MySQL 8.0.mysql_aurora.3.05.2
Problem
The writer instance is constantly cycling. In the AWS RDS console, under Logs & events for the writer, I see
Attempting to restart the ...
- 121
0
votes
3
answers
59
views
MySQL where clause with conditions based on other columns
I apologise for the vagueness of the title, but I couldn't find a better way.
There's this MySQL table which consists of entity config mapping in a flattened structure.
CREATE TABLE `entity_config` (
...
- 203
0
votes
1
answer
33
views
MySQL Migration from Keyring Plugin to Keyring Component [MY-011084] [Server] Keyring migration failed
For MySQL 8.0.40 on Rocky Linux, my database currently uses the keyring plugin. I want to migrate to the keyring component. It shows
[ERROR] [MY-013106] [Server] Can not perform keyring migration : ...
- 1
1
vote
2
answers
39
views
When can a unique index allow duplicate values in MySQL?
I have an intersecton table to record many-to-many relationships among three tables. Each combination must be unique, so I have a unique index on the three fields. However, using the PHP application I ...
- 121
1
vote
0
answers
28
views
Having issue with YOY query
I have following YOY query, works fine but the vendors which are not common is missing from the final result, can anyone please guide on what am I doing wrong?
SELECT `current_period`.`vendor_name` AS ...
- 111
1
vote
1
answer
33
views
MySQL table with partitions on multiple remote servers (only partitions)
in PostgreSQL you can create a table with let's say 4 partitions (hash)
and locate each partition on different servers (remote PostgreSQL servers) and it's called (Foreign Data Wrapper). Now in MySQL ...
- 13
0
votes
0
answers
22
views
SSIS "No data supplied for parameters" Error migrating from SQL Server to MySQL
I'm currently attempting to migrate a database table from my local SQL Server to a cloud-based MySQL database (SingleStore). As a preliminary test, I've created a simplified test table with a single ...
- 101
0
votes
0
answers
46
views
mysql 100% disk i/o with no query
I have a mysql instance running on my windows dev box that is using 100% disk i/o with no active query.
SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB MUTEX;
If I refresh the mutex list the count in the ...
- 101
0
votes
0
answers
20
views
Variable only resets per row when query has a static IN condition
I have a complicated query. I have a bunch of users. I have a ledger of debits and credits. I want to filter a user's subscribers by how long the subscribers have been negative. To do that I find ...
- 101
0
votes
1
answer
37
views
using field of subquery in output
Query:
SELECT tbl1field
FROM tbl1
WHERE id = (SELECT t2id
FROM tbl2
WHERE anotherid = (SELECT t3id FROM tbl3 )
)
here tbl3 is having 2 fields : t3id and ...
- 101