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,096
questions
0
votes
1
answer
667
views
Safe method to rename a crashed myisam table
I have a large MyISAM table which has crashed. Repairing the table will take some time. The table only INSERTed to and SELECTed from, never updated. To allow the application to continue working, ...
CommunityBot
- 1
1
vote
1
answer
735
views
MariaDB: Duplicate entry for key on OPTIMIZE with no duplications
There is a table named contracts with following structure:
MariaDB [db_name]> DESCRIBE `contracts`;
+-----------------------------+--------------+------+-----+---------+----------------+
| Field ...
CommunityBot
- 1
-2
votes
1
answer
19
views
In what format do I need Insert 3 table sql?
In what format do I need Insert 3 table sql?
animals(`AnimalsID`, `Animals`, `IUCN_Red_List`, `TypesID`, `BloodedID`)
types(TypesID,Types)
blooded(BloodedID,Blooded)
- 13.1k
0
votes
1
answer
545
views
MySQL Conditional Counter Based Other Columns?
I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is ...
CommunityBot
- 1
0
votes
2
answers
931
views
MySQLWorkbench - error message - Unhandled exception: 'ascii' codec can't decode byte Oxe4 in position 9: ordinal not in range (128)
I was trying to use table data import wizard to import a csv file that uses utf-8 character set, and then the following error message shows up.
MySQLWorkbench - error message - Unhandled exception: '...
- 1
0
votes
1
answer
30
views
Connecting to MariaDB server: ERROR 2013 (HY000): Lost connection to server at 'handshake: reading initial communication packet', system error: 11
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 ...
- 1,662
2
votes
1
answer
1k
views
What is the impact of converting latin1/latin1_swedish_ci to utf8mb4/utf8mb4_unicode_ci?
I was facing some issues with the character's encoding. Those are resolved by updating the CHARACTER and COLLATE for some columns in the table. So my concern is
Is this conversion safe?
Or can this ...
CommunityBot
- 1
0
votes
1
answer
8k
views
Mysql Alter table add column with ALGORITHM=INPLACE, LOCK=NONE
Am running below query
Query
Alter table dbname.tablename
add column column1 varchar(50),
add column column2 text ,
ALGORITHM=INPLACE, LOCK=NONE;
It takes more time to execute.
Ideally running ...
CommunityBot
- 1
0
votes
1
answer
627
views
Query goes in Copying to tmp table stauts in MySQL and never returns
I am getting some strange behaviors for my MySQL query status. Here is my query code:
SELECT a.id as art_id, b.album_artist_id,
s.song_title,s.song_seo,a.artist_seo,a.artist_name,
b.album_title, b....
CommunityBot
- 1
0
votes
1
answer
3k
views
Mysql_upgrade fails, table is corrupted
I have a mariaDB with a table that is corrupted. It's useless data to me, so I've tried to drop the schema but it won't drop. Instead, it gives me this error when trying to drop it:
"Error Code: 1805....
CommunityBot
- 1
0
votes
1
answer
771
views
Subquery not using Index, even if forcing
Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1
If I run:
EXPLAIN SELECT COUNT(*) FROM ips WHERE ip="1.2.3.4"
This query ...
CommunityBot
- 1
1
vote
2
answers
58
views
better database design for nested relation (many-to-many-to-many?)
I am a student learned about database theoretically, not as practice.
But now I have a chance to design database while doing this little team toy-project.
Here is a diagram (omitted 1:1, 1:n, n:m) as ...
- 1,454
0
votes
1
answer
724
views
cannot create a procedure with error [1064] [42000]
I am using mysql 5.7 and I have some code which works. But when I try to create procedure with the code, it reports error [1064] [42000]. I try to update the content of an old procedure with my ...
CommunityBot
- 1
3
votes
1
answer
2k
views
PHP MySQLi Lock Tables Query Sometimes Does Not Complete
I am attempting to lock some tables while running some code and unlocking the tables when I am done.
Process:
Run Lock Tables MySQL Query.
Run some PHP Code.
Run Unlock Tables MySQL Query.
While ...
CommunityBot
- 1
0
votes
1
answer
343
views
LOAD DATA INFILE output log file to txt
I am doing data load from txt file into table, most of the loads are working.
Some load errors out.
I want to save the output of the load into a log file to verify later.
I have HTML and special ...
CommunityBot
- 1
13
votes
3
answers
149k
views
How to insert values into a table from two different tables?
I have three tables
students table
------------------------------------
id(PK, A_I) | student_name | nationality
teachers table
------------------------------------
id(PK, A_I) | teacher_name ...
1
vote
2
answers
2k
views
Mysql: Create a view with multiple self joins without duplicates in result
Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you.
So i have a table entities_attributes_values where an ...
CommunityBot
- 1
0
votes
1
answer
696
views
MySQL - Turkey/Istanbul Daylight Saving Time Change
Until this year Turkey was using daylight saving time, staying UTC+2 on winter period, UTC+3 on summer. This year it is decided to stay only in UTC+3.
Our DateTime's are persisted on MySQL (5.26.34) ...
CommunityBot
- 1
2
votes
1
answer
4k
views
MySQL 8 Replication
I've got this error message:
Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin....
CommunityBot
- 1
1
vote
3
answers
7k
views
MySQL V8 replication lagging waiting for handler commit
let me start with some background.
We've decided to upgrade our Master DB to a more performant machine. We got a server with 1.5TB of RAM, Quad Intel(R) Xeon(R) Gold 6254 and IO is all NVMe drives. ...
CommunityBot
- 1
1
vote
1
answer
2k
views
Maxscale read write split and route filter
I have trouble to forward all SELECT requests to the slave in a MySQL master-slave architecture.
I'm using maxscale's read-write router, which does its job except for
all statements using ...
CommunityBot
- 1
1
vote
1
answer
286
views
In MySQL 8, can we make group replication db as readonly?
I have configured group_replication on 4 db. Out of which I am trying to make 2 db as slave(only read). Even though I make read_only, user can write data in it.
Is possible to make 2 as master and 2 ...
CommunityBot
- 1
0
votes
2
answers
283
views
Aggregate sampler function for programmer express predictions
Are there any SQL-standard or DBMS implementation that offers "sample aggregator" for predictably repeated columns?
I will explain, see bold text at the end.
PS: this question is about "little ...
CommunityBot
- 1
1
vote
1
answer
502
views
MySQL stops in windows VM and shows innodb os error in logs(error no. 5)
2020-08-23 17:22:36 5688 [Note] Plugin 'FEDERATED' is disabled.
2020-08-23 17:22:36 5688 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020-08-23 17:22:36 5688 [Note] InnoDB: The InnoDB ...
CommunityBot
- 1
0
votes
1
answer
4k
views
Problems in opening MySQL Query Browser
My MySQL Query Browser doesn't run. I just started to work with XAMPP and PHP.
I just installed XAMPP on my local machine. Now I have a problem when opening MySQL Query Browser. What I'm getting while ...
CommunityBot
- 1
1
vote
1
answer
2k
views
What are the side effects of changing MySQL `character_set_server`?
I am having some strange issues with character sets on my system. After digging through all kinds of settings, I found a discrepancy in my character set settings.
When I run show variables like '...
CommunityBot
- 1
0
votes
1
answer
45
views
Update/merge row data from the same table with common column name
I tried-->
UPDATE tbl AS r
INNER JOIN tbl AS f ON r.columnA = f.columnA
SET r.columnB = f.columnB, r.columnC = f.columnC
WHERE f.columnA = r.columnA
tbl as table name
before
ID
columnA
columnB
...
- 76.3k
0
votes
2
answers
55
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 ...
- 76.3k
1
vote
1
answer
24
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 ...
- 76.3k
2
votes
1
answer
916
views
MySQL Semi-synchronous replication with Multi-Master
Is it possible to use semi-synchronous replication with a Multi-Master setup?
I've tried to follow this guide to setup a semi-synchronous replication for a master-slave setup: https://avdeo.com/2015/...
CommunityBot
- 1
0
votes
1
answer
477
views
Restoring a MySQL database to a failed master
I have a master-master configuration in MySQL with two servers. One server should stay live on the network to serve requests (call it server A) and the other should be taken offline to push new code ...
CommunityBot
- 1
0
votes
1
answer
3k
views
MySQL: Privileges to view Views/Stored Procedure for user Regardless of Host
I've created stored procedures on a (remote hosted - DreamHost) MySQL Database using the HeidiSQL client.
One problem i seem to have is that viewing/editing the content of stored procedures is only ...
CommunityBot
- 1
2
votes
3
answers
1k
views
Maximum possible memory usage: 3.2G (163.34% of installed RAM)
I get frequent database crashes... explainable since mysqltuner tells me mysql has an appetite for memory. Since I'm not a database expert I hope you can point me in the right direction where to look ...
CommunityBot
- 1
3
votes
1
answer
2k
views
MySQL Import Super Slow
I'm running MySQL on a HyperV virtual machine. The host has 384Gb of RAM and is nowhere near being fully committed. I've tried giving the VM 128G of RAM and 8 CPU and I've tried 2 CPU's and 32GB of ...
CommunityBot
- 1
1
vote
1
answer
33
views
Migrating from PostgreSQL to MySQL (data with \r\n)
I'm trying to migrate a database from PostgreSQL to MySQL on an Ubuntu 23.04 system (latest version of both).
So far, it looks like things are fine, except for one problem. In one of the character ...
- 869
0
votes
3
answers
364
views
How to get last inserted rows where a condition is satisfied
table1:
esps
--------------
A
B
C
--------------
table2:
id _name esp
------------------------------
1 Ana A
2 Ana B
3 Mario A
4 Jorge A
5 ...
CommunityBot
- 1
0
votes
0
answers
19
views
MySQL is corrupted and not able to start mysql even after innodb_force_recovery=6
Here are Error logs from MySQL:
2023-09-25T23:38:12.380772Z 0 [System] [MY-013105] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Normal shutdown.
2023-09-25T23:38:14.418767Z 0 [...
- 101
0
votes
0
answers
39
views
How to Count calculated work hours in one month for each labors
Good Day.
I have some data like bellow
id name in out
1 lala 2023-09-29 07:00:00 2023-09-29 17:00:00
2 lili 2023-09-29 07:30:00 2023-09-29 16:00:00
1 lala ...
- 3,685
1
vote
2
answers
755
views
csv import fails without throwing an error
Ι am trying to import a csv file into an existing mysql 5.7 table.
Here is the command (the db is in docker with bind mount for persistence)
mysql -h 127.0.0.1 -uroot -prootpass --local_infile=1 ...
CommunityBot
- 1
-1
votes
1
answer
1k
views
mysql trigger to trigger only if record doesn't exist
The following trigger :
DELIMITER //
CREATE TRIGGER auo_mtltp AFTER UPDATE ON master_table_linfomasltp2016
FOR EACH ROW
BEGIN
IF (NEW.IMHQ_solicitado = 'SI')
THEN
...
CommunityBot
- 1
8
votes
1
answer
1k
views
RDS MYSQL instance rebooted and won't load INNODB table
So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
CommunityBot
- 1
0
votes
2
answers
625
views
Query other database's INFORMATION_SCHEMA tables
I have a shared database that stores procedures for all the other databases on the same server. This helps maintain one instance of a procedure rather than copying it across all databases. As long as ...
CommunityBot
- 1
0
votes
0
answers
18
views
mysql group replication, how to relocate replication channel from one master to another
Here is the scenario:
Site A → Replication → Site B
On Site A, there is a source database running, denoted as SDB.
On Site B, there is a Group Replication cluster (GRC) consisting of three servers: M1 ...
1
vote
2
answers
578
views
mySQL Workbench new Connection : There is no disk in drive. Please insert disk in \Device\Harddisk2\DR2
I'm trying to migrate the data at my company from Excel to mySQL so I'm very new at this. I just installed the latest mySQL package via the standard installer and when I try a new connection (I'm ...
CommunityBot
- 1
1
vote
0
answers
15
views
There was a recoverable, provider-specific error, such as an RPC failure. SQL to MySQL linked server no longer working
I have been searching all day and trying every manner of solution that I come across, yet nothing has resolved this issue.
Here is my was working code:
delete RemoteTable from OPENQUERY (LIGHTSAIL, '...
- 11
0
votes
1
answer
430
views
How to re-sync the Mysql DB if some Masters have different database incase of MySQL Group replication?
Somehow one of my server got into split brain situation, how can we reset & sync back one of servers's data with another master.
i won't mind lossing one's set of new data but i want now that ...
CommunityBot
- 1
0
votes
1
answer
408
views
How to downgrade between General Availability versions of MySQL?
There are some things that are not compatible with .NET framework for MySQL version 8.0.29.
Now i want to downgrade to MySQL version 8.0.28 but unable to find the process to do so.
Any help is ...
CommunityBot
- 1
2
votes
2
answers
12k
views
Error on re-import: #1231 - Variable 'character_set_client' can't be set to the value of 'NULL'
Using phpMyAdmin, I was getting an error when importing a MySQL dump file to which I'd made a simple global find-and-replace:
Error
SQL query:
/*!40101 SET CHARACTER_SET_CLIENT=@...
CommunityBot
- 1
2
votes
1
answer
3k
views
How to configure AutoMySQLBackup?
How I can configure AutoMySQLBackup to backup some selected tables ones per week while the other tables will be backed up every day?
Thanks.
P.S. My google search ninja skills is not working. :)
CommunityBot
- 1
1
vote
1
answer
465
views
Connection rejected on MySQL server (error 10060)
I have a small database on a server in my LAN that I try to access remotely via ODBC.
Currently, I am making a program in Visual Basic to access, retrieve and display data from the server to an Excel ...
CommunityBot
- 1