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
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, ...
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 ...
-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)
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 ...
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: '...
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 ...
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 ...
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 ...
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....
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....
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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) ...
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....
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. ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 '...
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 ...
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 ...
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 ...
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/...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 [...
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 ...
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 ...
-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 ...
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 ...
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 ...
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 ...
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, '...
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 ...
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 ...
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=@...
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. :)
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 ...

1
2 3 4 5
442