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
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 ...
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' ...
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 ...
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)
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 `...
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: ...
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 ...
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
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 ...
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
3 4
5
6 7
441