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.
21,888
questions
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 ...
- 101
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 |...
- 11
-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 ...
- 123
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.
...
- 237
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.
...
- 111
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 ...
ArUnio
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]' ...
- 151
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 ...
- 17
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 ...
- 1
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 ...
- 121
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 ...
- 13
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 ...
- 111
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 ...
- 161
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 ...
- 476
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 ...
Vaibhav E
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)
...
- 3
-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 ...
- 229
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'...
- 109
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. ...
- 1
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 ...
- 103
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).
...
drk1
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 ...
