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,031
questions
0
votes
0
answers
108
views
Duplicate entry for key [ERROR:1062, SQLSTATE:23000] - but key doesn't exist in table
I get the error:
Duplicate entry '978e9917-4191-472a-a6dc-729a92aed887' for key 'Component.FKvzrqjhn2bz1iobdd2ztu0eyc' [ERROR:1062, SQLSTATE:23000]
My table looks like:
CREATE TABLE `Component` (
`...
- 121
0
votes
1
answer
40
views
How can I handle cycles / infinite loops in recursive CTEs in MySQL
Cycles in graphs lead to infinite loops in CTEs.
Dealing with them in Postgres is straightforward.
Since 8.0 MySQL also allows for CTEs. How I can detect cycles and infinite loops in MySQL CTEs?
The ...
- 729
2
votes
1
answer
39
views
Displaying items between 2 dates
I am creating a commission tracker for a sales team
Commission is payable on a new account on any business brought in for 3 months from the date of the first sale.
The accounts table will have the ...
- 21
0
votes
1
answer
35
views
Using the correct JOIN
I am trying to create an app for the salespeople who work for my company to keep track of their sales/commission
When a salesperson brings on an account, their name is noted on the account and the ...
- 117
2
votes
1
answer
67
views
How to calculate latlong difference from last 15 min ago and last 1 min ago without using correlation subqueries
Problem
I would like to find logs that are active and do not hit a distance quota of 12.5km, comparing from last 15 min ago and latest (within last 1 min ago)
Sample data
select version();
version()
...
- 67
0
votes
1
answer
71
views
MySQL replication fails with permission error for unknown reason
Since 36h I'm struggling to setup a simple Master <-> Master replication with MySQL 8. None of the found solutions on the Internet works here and meanwhile I'm a bit desperate. Even the first ...
- 111
0
votes
1
answer
41
views
I'm Getting MySql max_connect_error, but Max_used_connections shows it didn't reach the limit
From time to time, I'm getting an error "mysqli_connect(): (HY000/1203): User already has more than 'max_user_connections' active connections." I checked the MySQL max_connection variable, ...
0
votes
1
answer
18
views
MySQL 8.0.23 Snapshot Restoration and Buffer Pool
The company I work for recently had a production outage. This outage occurred because we restored a snapshot of our MySQL database, and the MySQL buffer pool needed to be refreshed. The database had ...
- 101
0
votes
0
answers
107
views
MySQL 8: Problems when granting privileges on new user
I'm using a fresh installation of MySQL 8.0.31 on Ubuntu 22.04.1, and I've been following the instructions in an article by Digital Ocean.
I created a new user:
CREATE USER 'sammy'@'localhost' ...
- 101
0
votes
0
answers
19
views
MySQL Replication wont start
When starting my replication I get the error message Last_IO_Errno: 2003.
After trying everything I found in several forums it should have to do something with access rights, but I don't know what. So ...
- 111
1
vote
0
answers
42
views
Difference between CTEs in MySQL and PostgreSQL
This is probably a bit of a lazy question, but I would appreciate any quick pointers.
I have used CTEs considerably in PostgreSQL. I know that since 8.0 MySQL supports CTEs and now I want to take a ...
- 729
0
votes
0
answers
30
views
I want to simplify my sum query using loop function
I have a query to summerize my data based on month between year 2021 - next years (depend on the real date)
i used simple query like this to summerize the amount monthly
SUM(IF(pt.description like '%O-...
0
votes
2
answers
32
views
Database to Store Information about Unique Locations
I am starting a Database of multiple locations in a specific area (upwards of 8,000 unique locations). We remotely collect information routinely about these locations and data is collected over ...
- 103
0
votes
1
answer
43
views
MySQL query using Update, Set, Sum, Where, and Group By
I have a table below named deposit
dep_id
deposit_amount
comp_id
1
100
1
2
100
1
3
300
2
4
200
2
5
100
2
6
500
3
When I update the table with the query below I get the following table, which ...
0
votes
2
answers
24
views
Always second row gets multiplied by number of rows
Asalamualikum!
Always the second row gets multiplied by the number of rows, If Any-one knows the solution please comment.
Select Medicine.MedicineID,Name, IFNULL(Sum(Purchase.Quantity),0) as ...
0
votes
1
answer
25
views
How to join on "replace and substract"?
Using MySQL 5.5, no need to upgrade since it's on an old server and only used for Grafana.
I have two tables, foo, and bar. Foo has id and the title is null. I want to get the titles from bar and ...
- 175
1
vote
1
answer
91
views
Optimize the number of connections of the connection pool and MySQL
We are using Oracle Cloud's Ampere A1 instance consisting of 4 OCPUs (equivalent to 4 vCPUs) and 24GB of usable memory on Oracle Linux 7.9.
On the server is running an java game server and a web ...
- 13
0
votes
1
answer
57
views
optimization with subquery not working as expected
I have a MySQL InnoDB database. One table called affymetrixProbeset contains more than 300 million rows.
Querying this table with INNER JOIN to other tables, with an ORDER BY and offset/limit takes ...
0
votes
1
answer
37
views
mysqli_fetch_array exceeds memory
I a running a relatively simple query:
SELECT gb2_designs.indx,
gb2_designs.response_required,
gb2_designs.user_design_name,
...
0
votes
1
answer
43
views
Load Balancing in mysql InnoDB cluster
I have set up an InnoDB cluster with one primary(R/W) and two secondaries (R/O) and bootstrapped a MySQL Router. The router has 2 port 6646(R/W port) and 6647(R/O) port. My application is currently ...
1
vote
3
answers
81
views
What is best practice on creating an index?
what is the best practice on creating an index?
example 1:
createdd_at_indx (created_at)
updated_at_indx (updated_at)
OR
example 2:
date_indx (created_at, updated_at)
- 25
4
votes
2
answers
186
views
Group BY DESC being ignored after join - Mysql 5
The problem I wish to solve
I am trying to select the trucks that are on duty. I tried using sub queries. However, I heard that it has bad performance. I also tried adding a is_onduty flag to the ...
- 67
2
votes
1
answer
69
views
query explain output
I have a table with many rows. And some queries are slow, they use indexes but the queries still cost a lot of time, how can I optimize these queries?
Cluster, temporary table ... ?
CREATE TABLE `...
- 55
2
votes
2
answers
171
views
Why is MySQL Delete on Foreign Key Index locking a record not part of the index (leads to DEADLOCK)
I have a situation where I am executing a delete statement by a FK index that sometimes tries to lock a row that is not part of that index.
I'm not sure why this could happen.
Here's my scenario:
...
- 21
0
votes
2
answers
49
views
MySQL YEARWEEK function is not using index
Given are two tables:
create table table_a
(
id_k mediumint unsigned not null,
id_m tinyint unsigned not null,
id_a char(10) not null,
v tinyint unsigned not ...
- 45
0
votes
1
answer
41
views
Table design for 1:N relations with ordering and filtering on both sides of the relation in mysql
we have some tables with a lot of records (millions) and try to keep everything up to speed, but on the other hand we are setting our application up in new countries and keep some data per country. At ...
- 1
0
votes
1
answer
26
views
MySQL Not Resolving % in Host
Running MySql server 8.0.31 on an Amazon EC2 instance (52.99.189.74) in Ubuntu 22.04. When I log in to the mysql prompt with sudo, this is how the user and host columns of the mysql.user table look ...
- 103
0
votes
2
answers
57
views
High mysql write activity and high 'Created_tmp_files'
After using iostat and iotop, I can see that mysql read and write activity is consistently in the several M/s.
I have been monitoring long queries with slow_query_log and all the slow queries longer ...
- 103
2
votes
1
answer
86
views
Accessing a database on the Mysql server with the same user, is there a performance problem?
I have a MySQL server with many databases, access each database with a specific user or access each database using a single user, is there any difference in performance.
Can I create a single user to ...
0
votes
1
answer
59
views
MariaDB / MySQL performance optimization Azure disk access
We have a lot of MariaDB's that we have recently moved to "Azure Database for MariaDB server". Most of the time it works OK, but we sometimes experience some snags with slow queries compared ...
- 101
0
votes
0
answers
24
views
Replacing Part of a string within a group Concat
I have part of a string that I would like to remove when selecting. The Problem is that there are a few pieces within the string that changes. This is what I have so far.
Select
GROUP_CONCAT(...
4
votes
2
answers
277
views
Unable to use result of a "subquery in select clause" in a "insert.. select.. on duplicate update" query
Possible Unanswered Duplicate Question: Insert select - using selected value in ON DUPLICATE KEY UPDATE
I am not able to use a subquery result column in the select clause as a value to update in a ...
- 207
2
votes
3
answers
190
views
Why Optimize Table does not shrink table size?
I implemented this solution for big database, only keep data for 14 days (remove data daily based on date). When I run optimize TABLE table1; The size supposes to decrease but in my case, it increases....
- 210
2
votes
1
answer
23
views
Is it possible to get MySQL to retry PK generation n times?
I'd like to use random bytes as PK in my table and to ease the application implementation I thought about leaving the generation to the database server so collisions can be handled transparent to the ...
- 123
2
votes
2
answers
104
views
How bad is it, really, to use InnoDB table as a queue?
I have a product which uses two separate tables as queues for some processes. Generally speaking:
Interesting events occur in the application, and we write a record to the appropriate queue. That &...
3
votes
1
answer
41
views
is it possible to manipulate a temporary table with triggers?
I created a temporary table and I want to manipulate the data of this table with a trigger, doing update, insert, delete in this temporary table is it possible? Mariadb
- 55
2
votes
2
answers
101
views
mysql takes too long to just add a column
MySQL took 4.5 minutes to just add a column to a table having 150,000 rows.
Is there anything wrong in it or this is just normal. Is there any other alternative to fasten things?
ALTER TABLE job_posts
...
0
votes
0
answers
15
views
MySQL trouble (wrt connector, and now password)
It all started with a project I must submit as an assignment.
This requires me to use "mysql.connector" in python.
However, it gave an attribute error, saying mysql.connector doesn't have an ...
- 101
2
votes
1
answer
78
views
Why would INSERT, REPLACE or UPDATE statements sometimes take over 1 second to execute?
I have a very good server (great CPU, huge RAM, NVMe) with a huge InnoDB buffer pool that's not full yet.
The website is very write-intensive, but it runs fast enough (SELECT queries well optimized, ...
- 748
0
votes
3
answers
42
views
Is storing product information as strings acceptable in OrderLines?
When a user makes a order I create a Order-object with the following data:
+-----------+
| Order |
+-----------+
| ID |
| user |
| vendor |
| timestamp |
| ... |
+-----------+
...
- 111
0
votes
0
answers
77
views
Does the "max vCPU" limit only apply to the "CPU" wait state?
In the Average Active Sessions chart of Performance Insights for Aurora MySQL 8, there is a horizontal Max vCPU (at 16, in my case). Now I wonder if this limit applies only to the CPU wait state (...
3
votes
1
answer
19
views
MySql 5.5 intermittently allows access to command SHOW BIN LOGS
I've written software that reads the MySQL bin log and uses it to sync data to another, external application. I did my development and testing on versions of MySql and MariaDB without issue. We've now ...
- 133
0
votes
1
answer
505
views
SQLSTATE[HY000] [2002] Connection refused when trying to initiate connection via Dockerfile's CMD
I'm trying to run a Docker container based on:
PHP 8.1
Apache 2.4
MariaDB (latest official docker image)
Dockerfile:
FROM php:8.1-apache
WORKDIR /var/www/html/
RUN pecl install xdebug \
&&...
- 161
0
votes
1
answer
18
views
MATCH AGAINST gives 0 for 'Depression' against 'Depressed'
My MATCH AGAINST is otherwise working, I am just curious what is going on under the hood that
SELECT `rfv`, MATCH `rfv` AGAINST('Depressed') FROM `rfvs`
returns a match score of 0 when rfv is '...
5
votes
3
answers
682
views
Migrating existing tables to use INT primary key instead of UUID mysql v8
I'd love to hear everyone's opinion on this issue. I currently use Innodb mysql v8 in RDS. Our db is moderate, with ~130 tables, most are small, with our largest table having ~30 million rows over 6 ...
- 401
0
votes
1
answer
18
views
SQL data restore and space issue
I had some tables on my database that have duplicate values, in order to get rid of those I made a small program that finds duplicates remove them and write new rows.
By mistake, I commented the line ...
- 11
0
votes
1
answer
301
views
Mysql replication fails when the master's binary log increments
I am currently running 2 instances of 8.0.31 MySQL Community Server on two virtual machines. These are set up with one way replication. The replication runs fine once set up, but at some point it ...
1
vote
1
answer
23
views
Syntax issues when uploading backup from mysql5.7 to percona xtradb 5.7
Greetings to those readying the thread and thank you for devoting your time to answering the question.
I have encountered a problem when uploading a MySQL 5.7 backup database to a Percona xtradb 5.7 ...
0
votes
0
answers
18
views
query get data from one table and join other data from 2 tables where the same ID
This query get data from table products and JOIN data from product_details with rule check the same product ID.
$query = "SELECT p.id,
p.slug,
p.sku,
...
0
votes
1
answer
35
views
MYSQL - return the minimum number of records so price sum is possible to be as close to the input price
I have a table with names, prices and a user input price, for example, 100.
I want to return a minimum number of records so prices sum will be close to the input price
So If I have in records items ...
- 1