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
2 answers
10 views

Error with SQL DELETE statement with MariaDB

I have a multi-database application that has a query that is giving me execution problems with MariaDB 10.6.12 (Windows). The same query works perfectly when run against MYSQL 8.0.27 However, when it ...
1 vote
0 answers
9 views

MySQL - multiple CASE ordering with joined table

I have two tables: EVENTS +----+--------------+ | ID | Name | +----+--------------+ | 1 | First event | | 2 | Second event | | 3 | Third event | | 4 | Fourth event | | 5 | Fifth event |...
-1 votes
1 answer
14 views

SQL Query for Time in , Time out Report

I have a table with the below sample output. personnel_employee emp_code |first_name |last_name ---------------------------------- 1 |name 1 |last 1 4 |name 4 ...
0 votes
1 answer
18 views

SET BIGINT fieldname value to 0 automatically if inserting empty string

How do I do something like : INSERT INTO tablename SET customer_id = '' WHERE <condition>; customer_id is BIGINT NULL Default NULL But when I execute SET customer_id = '' it gives Incorrect ...
  • 227
0 votes
0 answers
6 views

Change replication name when importing dump with master log position

I exported data from an existing database to bootstrap replication. When setting --master-data to 1 or 2 for mysqldump it writes the log position to the dump file. However the generated command does ...
  • 101
0 votes
1 answer
26 views

unlock the only MySQL account

By accident I locked my root account in MySQL which I launch using wamp server, the error message from phpMyAdmin is Access denied for user 'root'@'localhost'. Account is locked. How can I unlock ...
2 votes
1 answer
24 views

MariaDB upgrade requires table rebuild. Rebuild takes too long. Options?

I've got a MariaDB instance (10.6.8) hosted on Amazon RDS (db.m6g.4xlarge). It had automatic minor upgrades enabled, which unfortunately caused hours of downtime last time it triggered. In reviewing ...
0 votes
1 answer
13 views

How to create mysql master slave replication from backup?

I am using sqlbak to back up my database. Here are the backup files look like: Here is the content: Typically, there should be one bin log file and one location to restore and be a slave. We back up ...
  • 101
1 vote
1 answer
12 views

how to search history in mysql client on macos?

I understand that at some point in the 5.x line, MySQL switched from readline to editline for their shell. I cannot seem to find any references on how to make the history search work in my setup. ...
0 votes
0 answers
23 views

MySQL replication order

I set a new slave (mysql 8.0) where by default the slave_parallel_workers=1; The steps i do to setup a new slave is to copy a physical slave and run the following: SET sql_log_bin=0; select @@GLOBAL....
  • 19
1 vote
1 answer
11 views

Unable to create a very specific user with host other than %

I have a very strange issue, which i'm not sure even how to debug I have two users: user1, user2 Created user1 just fine with ansible playbook (mysql_user), trying to create the 2nd user - fails. ...
2 votes
0 answers
10 views

MS Azure Mysql flexible server consumes all Memory

I have MS Azure MySQL Flexible server running in Azure with 4vCPU and 32GB of RAM. It consumes all memory it gets and definitely is choking at lower configurations than 32GB which seems to be an ...
user avatar
1 vote
1 answer
30 views

How can I utilize an index with a different type or collation in a LEFT JOIN in MySQL?

I am trying to debug a slow query, and when I EXPLAIN the SQL, I get a warning: Warning: #1739 Cannot use ref access on index '[index name]' due to type or collation conversion on field '[field name]' ...
0 votes
1 answer
20 views

MySQL Replication lag on linux [closed]

We're in the process of moving from windows to linux - i did a new slave setup on a linux machine (centos 9 stream) - the slave just can't keep up and the lag keeps growing. I tried EXT4/XFS with all ...
  • 19
1 vote
1 answer
36 views

MySQL restore of "mysql" database fails with error 3723

I have a backup process that once a day dumps all the mysql databases on one server (with mysqldump), rsyncs them to another, and then they are imported there (one at a time). I did it this way to ...
2 votes
3 answers
364 views

Find out students max no of times not enrolled consecutively in a subject

This is my sample data set. Looking for code in MySQL Desired output Student Times abc 3 (Student abc didn't enroll for any subject consecutively for T3,T4 in 2021 and then for T1 in 2022) xyz 4 (...
  • 23
0 votes
1 answer
46 views

Alter command showing syntax error in MySQL 8.0

Below is my Table schema create table employee( emp_id numeric(4), fname varchar(10), lname varchar(10), mgr_id numeric(4), hire_date date, job_id numeric(2), dept_id numeric(3) ); Now I want to use ...
0 votes
0 answers
24 views

Mysql get 2 latest record and display on same line

I already have the query to get the 2 most recent records from Mysql DB. Problem is, it displays the first, then the second, then the next client and so on. I am looking to get these results as shown ...
0 votes
0 answers
21 views

Trying to drop UNIQUE but fails due to dependencies, but can't find any

I have a table with a UNIQUE KEY which I need to remove, however I get the following error when I try to: SQLSTATE[HY000]: General error: 1553 Cannot drop index '...
0 votes
0 answers
15 views

Using MySQL 8's "--no-monitor" mode in a Windows service?

Cheers. For (reasons) I want to run MySQL 8.0.30 in the "no-monitor" mode, which keeps the service running as a single process. Docs here. The documentation makes no reference to windows ...
  • 23
1 vote
0 answers
10 views

Mariadb keeps crashing after sometime

I am getting a weird problem with Mariadb server. After starting the service after few mins the service will go down automatically. Upon checking status this is what I find. mariadb.service - MariaDB ...
  • 11
2 votes
1 answer
25 views

MySQL db optimization error on innodb tables

I have a database named 'mysql' which is being used on RHEL 8 server running MySQL 8.0.32. When trying to optimize the entire database using the mysqlcheck command 'mysqlcheck -o mysql' the below ...
0 votes
1 answer
27 views

Fetching 88 records via DISTINCT from a table of 1.5 million rows is taking 3 seconds

There is a table which are populated from an online service and updated every week. This goes on a separate (second) database. The table is like 300MB in size (1.5 million rows) processed by a cron ...
  • 227
2 votes
1 answer
31 views

Why is the MySQL server-id necessary for replication when the server-uuid exists?

We're trying to setup some basic MySQL Master/Slave replication at our company, and we're confused regarding the unique server_id requirement on replica servers. Quoting the mysql docs: On the source ...
0 votes
0 answers
27 views

Help me understand EXPLAIN PLAN of mysql 8

-> Sort row IDs: users.userid (actual time=24875.751..34999.822 rows=2547647 loops=1) -> Table scan on <temporary> (cost=0.00..0.50 rows=6083517) (actual time=0.027..2488.740 rows=...
0 votes
0 answers
22 views

Not existing row as 0

I have 3 tables with a list of users, items and a relation between them. User can have an item (1) or not (0), or can have a shipped (0/1). Table1 CREATE TABLE table1( id NOT NULL AUTO_INCREMENT, ...
0 votes
1 answer
30 views

Memory usage for MySQL

I have created the following procedure: CREATE DATABASE memory USE memory DELIMITER $$ DROP PROCEDURE IF EXISTS `my_memory` $$ CREATE PROCEDURE `my_memory` () BEGIN DECLARE var LONGTEXT; DECLARE ...
1 vote
1 answer
23 views

How can I get non-primary key fields in a GROUP_CONCAT that goes through a linked table?

I have two MySQL tables, users and supervisors. In this simplified example, users has just an ID and a Username, and supervisors is a table that links two users together. I would like to construct a ...
1 vote
1 answer
28 views

How to detect a trigger set up on database INFORMATION_SCHEMA?

Some time ago I developed a utility Python app which does certain actions if it finds that any data in a database has been updated recently. To do this, for each database I created a table ...
1 vote
2 answers
24 views

combine multiple sql with same FROM clause and diff where clause

I am relatively new when working with queries so let me apologize in advance. my sql is $sql1 = SELECT AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`...
1 vote
2 answers
40 views

Identify “duplicates” within a table by using GROUP BY column1 OR column2

Generally speaking, I want to group by one column OR by another one: SELECT count(*) FROM foo GROUP BY column1 OR column2 In particular, I want to identify duplicates within a given table, below an ...
  • 111
-1 votes
1 answer
35 views

MySQL - Show Month and Dates

MySQL version 5.7.29-log My table (events) has EventID, EventTypeID, StartDateTime, EndDateTime I want to extract the month's name and then the dates of the records for that month, and output as ...
  • 1
0 votes
0 answers
26 views

Extra Second Added When innodb_lock_wait_timeout Variable is Changed

I am playing with changing the innodb_lock_wait_timeout locally on a MySQL 5.7.41 and I found that it seems like there is an extra 1 second before the timeout is enforced. Here is my test setup: 127.0....
0 votes
0 answers
27 views

Database schema to manage customers subscriptions

I'm currently developing a system that allows users to select a plan and subscribe to it. For simplicity, let's assume there are 3 plans: Silver, Gold and Platinum. Also let's assume that the prices ...
1 vote
1 answer
20 views

Revoke access to database from user with global permission?

Consider a user with global database access: GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD 'xxx' WITH GRANT OPTION Due to legacy issues and number of databases involved, I'd like ...
  • 175
1 vote
1 answer
27 views

MySQL upgrade 5.7 to 8.0 with Keyring encrytption failed to complete upgrade

I ran the mysql community installer on Red Hat 7.9 over a 5.7.36 installation. First, I ran xtrabackup for all dbs Then I stopped mysqld Then I ran yum install Everything ended ok I got the below ...
0 votes
0 answers
23 views

Xampp-MySql: ibdata1 file deleted, can't open or access tables

When mysql couldn't start in xampp I tried to fix it using some solutions in internet where I replaced ibdata1 file somehow. Converted frm files to text using mysqlutilities but couldnt find schema in ...
user avatar
1 vote
0 answers
35 views

Use 2 columns of a table as foreign key in an other table to avoid circular-reference

I have two tables: ObjectContainer containerID INT NOT NULL PRIMARY KEY mainObjectID INT NOT NULL FOREIGN KEY REFERENCES Object (objectID) Object objectID INT NOT ...
  • 11
0 votes
1 answer
10 views

Paginate only root nodes but still show child nodes when recursively retrieving child nodes in mysql

I am implementing pagination of a tree structure table in mysql. I have a Table: account with recursive association, an account can be boss of other account. A self relationship of kind (SuperVisor (1)...
1 vote
2 answers
44 views

Equality Range Query in MySQL runs very slowly

Table event (id, source_id, start_time), 140k records Index (source_id asc, start_time desc) My query: SELECT * FROM event where source_id in (4, 5, 3, 10) order by start_time desc limit 100; Run ...
  • 113
0 votes
1 answer
25 views

Reliable database server solutions for industrial mini PCs

Although my issue may lean more toward hardware, I hope someone can help me with a solution to the issue I am experiencing. I manage a series of industrial PCs that run a standalone MySQL server on ...
  • 1
0 votes
1 answer
41 views

Insert Statement on large MySQL table does not terminate

I have a MySQL database with several tables. One table (InnoDB) is quite large (about 490 million rows). The table consists of 4 columns: id: int(11), auto_increment term: varchar(255) docId: int(11) ...
-1 votes
1 answer
33 views

Stored procedure to Insert data into table with select statement and argument parameter [duplicate]

I am creating a stored procedure which will populate my table with data passed from procedure argument and from other table. I have a table with three columns (cart_id, product, sold_quantity). Now, I ...
1 vote
0 answers
40 views

How do i found total counts from joining tables? it shows wrong count for some columns

I have tried below query but it shows wrong count select count(DISTINCT schools.id) as total_schools, (select SUM(CASE when schools.is_vts_enabled=true then 1 else 0 END) as vts_enabled from ...
  • 123
-1 votes
1 answer
69 views

How to fix MySQL using large amount of memory

I am running a MySQL database on my computer (in Docker) as part of a local development environment. Since this is far from a production instance, it is basically just using default settings and hasn'...
0 votes
1 answer
24 views

MySQL Select query for a calculated column that uses previous row value for current row

I am stuck at this. Trying to help PM team to translate a calculation from Excel using a MySQL select query to do reporting. Your help is appreciated. Table has 3 columns - Employee, Month, Salary. ...
0 votes
1 answer
61 views

mysqld how to only listen on unix socket?

how do i make mysqld not use ip at all, only listening on an unix socket? if i do mysqld --socket=mysql.sock it still opens port 3306, and if i do mysqld --bind-address='' --socket=mysql.sock it ...
1 vote
1 answer
30 views

MySQL - Sum rows in one column based on values in another column - grouped - using variables

I am trying to sum data based on the values in one column grouped by the values in another column. Specifically I am trying to sum data so that cumesum is reset to zero when the Ref column changes as ...
  • 13
0 votes
1 answer
17 views

Mysql MYISAM force operating on a crashed database

Is theres a way to forcibly operate on a database that crashed (even if it's not possible through mysql cli at least a way to remove the dirty bit that marks it as crashed by editing with an editor). ...
user avatar
0 votes
1 answer
13 views

Handling IP Changes for Master MySQL Server

I have been working with master-slave replication in MySQl and was wondering if there is any possibility to set the listen-address property of the MYSQL config file to read the IP statically assigned ...

1
2 3 4 5
438