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,207
questions
0
votes
1
answer
24
views
Automatic ID and Created/Modified By/Datetime fields?
Is it possible to get MySQL to automagically create and manage a record ID, Created By/Datetime and Modified By/Datetime columns for all tables in a database?
Note: Not looking to manually create ...
- 1
1
vote
4
answers
33
views
Buffer Pool caching in Sql clarification
I might sound a little naive to ask this question but I couldn’t find out the answer anywhere.
So my understanding is that the buffer in a sql db maintains disk pages in cache so that when a query ...
0
votes
2
answers
57
views
How to migrate database to another server no downtime?
I want to migrate my database server to new server because it was old.
I have thinks about setup replicate between two server.
But I can't off my database server to dump database and setup replicate.
...
Huỳnh Thanh Đan
0
votes
0
answers
38
views
How do I drop this database in mysql
One of my tables got corrupted somehow and I can't seem to drop the whole schema. I have been trying to drop this database but I have been facing with lots of errors. I have tried dropping from MySQL ...
- 1
0
votes
0
answers
18
views
mysqlsh (MySQL Shell) - Import/Export without mysql.* tables
I've recently installed the mysqlsh binary recommended in the documentation because I'd like to take advantage of its performance.
However, it seems to want access to the mysql schema in order to do ...
- 131
0
votes
1
answer
40
views
Spatial query instantaneous when using GUI client; slow through CLI or PDO application
I'm having a very peculiar instance wherein we recently had to upgrade our database from MySQL 5.7 to 8.0, and I had to rewrite some queries.
If my query is complicated enough, I usually prepare ...
- 101
1
vote
1
answer
40
views
Wrong execution plan in MySQL 8.0 when using JOIN in WHERE NOT IN clause
After a migration from MySQL 5.7 to MySQL 8.0.34, we have a very strange behavior with a query when semijoin is on in the optimizer_switch.
1. The problematic query:
SELECT COUNT(s0_.id)
FROM ...
- 13
1
vote
2
answers
54
views
How to Identify Tables with Index Size Issues during MySQL 5.7 to 8.0 Migration?
I'm trying to migrate from MySQL 5.7 to MySQL 8.0 for my application's database. However, the precheck failed with an incompatibility error "25," stating that tables with redundant row ...
- 13
1
vote
2
answers
50
views
Mariadb update column during load data of CSV
I am loading .csv files into Mariadb 10.6 using LOAD DATA LOCAL INFILE.
There is a DATE column called posted_on in the inbound data, and I need store the month name in another field monther, ideally ...
- 115
0
votes
1
answer
49
views
Copy single mysql database from filesystem snapshot without booting snapshot or mysqldump
The question on how to copy without mysqldump has been answered - however, that entails duplicating the entire mysql tree.
My scenario is I have (zfs) snapshots of the filesystem at various times, and ...
- 101
1
vote
1
answer
114
views
How to get all users that have more than a certain number of messages?
I have a database which ER diagram looks like this:
I struggle to get all the users which have for example more than a five messages. I was able to write a query in python:
q = "SELECT user_id ...
- 113
0
votes
0
answers
22
views
MySql keep loosing tablespace id for random tables
In one of our databases we are seeing more and more frequent errors where the table space id of an innodb file is lost.
We are running mysql 8.0.31.
The error message in the log file is:
[ERROR] [MY-...
0
votes
0
answers
43
views
Illegal mix of collations when collation_connection does not match actual type
After upgrading from 8.0.26 to 8.0.30, I found a character set problem when calling the stored procedure.
When I call the stored procedure, the procedure returns a utf8mb4_unicode_520_ci string, which ...
0
votes
2
answers
53
views
Foreign key constraint
Table A: Author Information
CREATE TABLE TableA (
AuthorLastName VARCHAR(255),
AuthorFirstName VARCHAR(255),
BookTitle VARCHAR(255),
PRIMARY KEY (AuthorLastName, AuthorFirstName, ...
- 1
0
votes
0
answers
61
views
How to migrate a database from Oracle to MySQL using the command line?
I am trying to migrate a database from Oracle to MySQL, with exp .dmp but I run into the problem that I must convert the .dmp file to .sql so that mysql recognizes it, for this conversion I cannot ...
Zuriel_Owsla
0
votes
0
answers
15
views
MYSQL innodb error when i trying backup my database
I have a legacy project on a Windows machine, MYSQL version is really old like 5.5, and because of a table which called wp_web_api_logbak20210315 is too large like over 20 gigabytes that's why I ...
- 1
1
vote
2
answers
390
views
my mysql command line client password keeps changing automatically. i don't use it for a few weeks and it changes the password why does this happen?
I'm new to mysql and when i don't use it for a few days it changes the password somehow, on it's own.
i've had to reinstall it so many times and this happens every few weeks. why does this happen and ...
- 21
1
vote
1
answer
24
views
What MySQL Tables Should I Have for a Comprehensive Worldbuilding & Storytelling Platform?
I'm sorry if this doesn't belong here. I'm not sure if general advise questions are allowed here or if this should be somewhere like Reddit. Just let me know if that's the case.
As a fun side project, ...
- 11
0
votes
1
answer
75
views
mysql: database suddenly loading forever
so I downloaded a public database. example.myd (10.2GB), example.myi (9.7GB) example.frm (21KB). I simply installed xampp. Mysql, phpmyadmin etc working perfectly. I just put these 3 files into the ...
- 1
0
votes
1
answer
24
views
MYSQL transactions and guaranteeing inserts/deletes/updates
I have a stored procedure called processWinningBet. It takes a pendingBet and does these things.
update balance table for user
creates a row in historic bets table
deletes the corresponding row in ...
- 11
1
vote
0
answers
52
views
Adjust SQL query to select only one record
I have three tables:
An adjacency-list table, territories with fields id, alignment_id, three_left, tree_right, depth and deleted_at (omitted from table for brevity, all values null).
id
alignent_id
...
- 61
0
votes
2
answers
35
views
MySQL is not utilizing the index when the IN operator is used
I have a mysql 8 query like below. the ch_site_user table is indexed on user_id, site_id, status.
Explain plan shows the correct index is used. But usersite.site_id IN (2315, 2316, 2324, 2323, 2320, ...
0
votes
2
answers
81
views
How to choose index to optimize this MySQL query?
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' AND symbol = 'ZROZ' AND blacklisted = 0
ORDER BY high DESC LIMIT 1;
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' ...
- 101
0
votes
0
answers
74
views
How to Optimize SQL Restore Performance with MySQL Dump [closed]
I'm currently working on a cron job that involves masking and modifying specific columns in a MariaDB 10.6 database. The process includes taking a dump of the production database using mysqldump and ...
3
votes
1
answer
252
views
Why MySQL binlog contains only one record: log rotation
One of my slave mysql instances contains several unusual binary log files
mysql-bin.023896 250.01MB 2023-10-11 08:30:06.478997000(last modified)
mysql-bin.023897 2.27GB 2023-10-11 11:27:...
- 41
0
votes
1
answer
106
views
How to compress data in SQL Server? The data warehouse raw data is times bigger than the MySQL OLTP database for the same tables
For reporting purposes, we have a DWH (data warehouse) doing ETLs (extract-transform-load) to retrieve data from selected tables in a production OLTP (on-line transaction processing) database.
The ETL ...
- 139
0
votes
0
answers
67
views
Remove not existing table from MySQL INFORMATION_SCHEMA
I removed (dropped) not used (in fact it was corrupted and that's why not used) table from a MySQL DB.
I did it from MySQL Workbench. It was removed and it's not existing here.
mysql> SELECT * FROM ...
- 13
0
votes
0
answers
29
views
Update character set for all columns in entire MySQL server
I'm upgrading from MySQL 5.7 to 8.0 and right now cleaning the old stuff. I used Upgrade Checker to verify my environment. Lot's of things are cleaned now, but one of the issues I have currently is ...
- 13
1
vote
1
answer
62
views
1
vote
1
answer
127
views
How can I subtract or get compare difference in values within the same column from the top result column result as the base?
Table tbl
id
name
result
1
v
10
2
w
11
3
x
9
4
y
7
5
z
15
Table tbl2
id
name
result
result-diff
1
v
10
0
2
w
11
+1
3
x
9
-1
4
y
7
-3
5
z
15
+5
Column result-diff is based on the ...
- 27
1
vote
1
answer
40
views
How to list non-default parameters in mysql?
How to list non-default parameters in MySQL?
I am looking for something like \dconfig+ from postgresql
- 630
0
votes
0
answers
47
views
How to merge two MySQL databases
I set up a database a few months ago as a bit of an experiment and it sits happily on my hard drive having been set up with Homebrew.
Then a few days ago I was looking at a project and remembered MAMP ...
- 101
0
votes
0
answers
31
views
Why, MariaDB user allowed host IP address also allows a virtual machine on the host with NAT network adapter
The MariaDB user specifies an allowed IP address in the Host column of the mysql.user table. So, it only allows logging in from the host with the specified IP address. And MySQL has the same design.
...
- 139
0
votes
0
answers
23
views
Database Design Suggestion for Ordering System
This migration was made using Prisma ORM and using MYSQL as the main database. This schema is exclusively made for an ordering system
I would just like to ask for suggestions on whether or not the ...
0
votes
1
answer
36
views
If I UPDATE one column in one row in the MySQL InnoDB engine, do I have to wait for the lock to be released when I SELECT another column?
For example, suppose you run the queries below.
UPDATE `user`
SET point=30
WHERE id=1;
SELECT nickname
FROM `user`
WHERE id=1;
In this case, should the second SELECT query wait for the lock from the ...
1
vote
1
answer
80
views
Moving from MySQL 5.7 to MySQL 8.0
I'm currently running MySQL 5.7 in one of the key servers. This version reaches end of life soon, so it's time to move to 8.0. Or give it a shot and jump on 8.1?
This one is also running on dated ...
- 13
-1
votes
2
answers
37
views
Problem accessing MySQL in Xampp
I installed Xampp on my Ubuntu 22.04 box.
In its my.cnf, I set
[client]
password =mypassword
port =3306
socket =/opt/lampp/var/mysql/mysql.sock
I saved this and restarted MySQL.
When I try ...
- 101
1
vote
1
answer
70
views
Restoring MySQL Database from mysqldump file produces seemingly random syntax errors
Sorry for the long title I wasn't sure how else to make it distinct from the auto-search results.
I have a large-ish dump file (~44GB) and I'm trying to import this into MySQL on a different server.
...
- 25
0
votes
0
answers
19
views
ProxySQL with multiple reader_hostgroups
I have a ProxySQL instance that is currently configured with one master and two replication slaves for basic read/write query splitting.
However, I would like to configure different sets of slaves for ...
- 101
1
vote
0
answers
30
views
Is there any tool that can be used to backup and restore individual schemas and not the entire database?
Can someone help me with any tool that can be used to incrementally back up the individual schemas and restore them instead of backing up the entire DB? We need this for the Aurora MySQL DB RDS ...
- 11
0
votes
0
answers
21
views
Is it possible to backup AWS RDS Aurora MySQL per database by any AWS services?
I have several AWS RDS Aurora clusters, and user data is stored in different database.
Currently I have my own script to backup per database, but I am considering to backup through AWS provided ...
- 1
2
votes
3
answers
567
views
Conditional selection in a GROUP BY clause
I am an SQL beginner and ran into the following problem. Consider a table with data on countries, cities within that country and their population:
CREATE TABLE cities (
country VARCHAR(20),
...
- 121
0
votes
1
answer
52
views
mysql hot backup with upgrade support
I perform a hot backup of a production mysql database using an LVM snapshot, and then I attempt to restore said backup on a development machine. However, the dev mysql database will not start, and ...
- 123
0
votes
1
answer
57
views
The most recent rows in for each type/agent
I have a table (still on MySQL 5.7) with lots of data. This table is written by some scripts. It works like a "checkin", where each agent is checking in in some period of time. It looks like ...
- 13
0
votes
1
answer
38
views
Select distinct
I had some confusion regarding distinct keyword...
For example Table test has 5 columns a b c d e, and column a has repeated value, and I want to use distinct keyword to get distinct values while also ...
1
vote
2
answers
104
views
Locking/Isolation - MariaDB
In MariaDB the default isolation level is repeatable read. I understand this means when I open a transaction I will not see any concurrent writes to the DB.
I also have a situation where I need to ...
- 113
-3
votes
1
answer
20
views
In what format do I need Insert 3 table sql? [closed]
In what format do I need Insert 3 table sql?
animals(`AnimalsID`, `Animals`, `IUCN_Red_List`, `TypesID`, `BloodedID`)
types(TypesID,Types)
blooded(BloodedID,Blooded)
0
votes
2
answers
60
views
Which one is better, the OR operation or JOINING two tables in MySQL?
I have a table let us called table_A. This table has a field let us called Entity. This field could have either zero, one, or two values for each row. Min is zero, max is two, and most rows have ...
- 218
1
vote
1
answer
35
views
Finding the latest status
I have 2 tables events and event_status.
The event_status table is the history table, and latest being the current one. The events and event_status have a one-to-many mapping.
I need a optimized query ...
0
votes
1
answer
294
views
Connecting to MariaDB server: ERROR 2013 (HY000): Lost connection to server at 'handshake: reading initial communication packet', system error: 11 [closed]
I am trying to connect to an AWS hosted (RDS) MariaDB database (engine version 10.6.14). The database is hosted on a private subnet but it's accessible through a bastion host. I use an SSH tunnel to ...
