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
1answer
8 views

MySQL incorrect nested query output when use table name

I have encountered a very odd situation with MySQL (replicated with same results in PSQL) when a query return the right result when is used a table name alias but wrong result when is used full table ...
0
votes
1answer
14 views

Adding disk space to a running MySQL Server

I'm running a MySQL (5.7) on a Centos7 server which also has a replica (slave) on another machine. The problem is that the master instance is running out of space and I need to extend its disk space ...
0
votes
2answers
16 views

MYSQL - Cannot update a parent row: a foreign key constraint fails

db-fiddle link SCHEMA QUERY -- GRAPH INFO TABLE-------------------------------------------------------------- CREATE TABLE GRAPH ( graph_id CHAR(32) NOT NULL PRIMARY KEY, name ...
0
votes
2answers
13 views

Improving performance for large volume of simultaneous reads/writes on Percona 8

A general description of the environment and the use case: Technical: We're running a distributed application with a single server and ~25 clients; each client is running ~75 threads. At one ...
0
votes
1answer
20 views

Optimize MariaDB query large dataset

We have a web application that uses MariaDB relational mode using tables with "InnoDB" engine, because our application inserts and updates many data each second. Also, we have a table to ...
-1
votes
0answers
14 views

Trigger implementation help

COMPORDER(CodA,CodO,quantity,price) /it describes the details of the order ,the quantity and price/ STORE(CodA,description,allocation,quantity ) ORDER(CodO,OrderDate,CodCustomer,SentDate,complete) ...
0
votes
0answers
15 views

Trigger implementation , did i do it properly?

FOLDER(CodFO,name,path,dim,ContainerFolder) FILE(CodFI,name,CodFO,path,dateC,dateE,dim) After a file is inserted into the FILE class , its path field gets updated after the instert in a way that it ...
0
votes
2answers
22 views

Is inserting in a MySQL table with a Full-Text index really slow?

Reading the documentation, I see this mention : For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load ...
0
votes
0answers
22 views

WHERE + GROUP BY + HAVING to SELECT only the rows where I have different column ID's

I am trying to select only the rows where I have different columns of the PartnerID column. I have 1 simple table which contains 3 main columns - ID, Acct and PartnerID. I am trying to GROUP BY Acct ...
0
votes
0answers
10 views

MySQL replication with read/write tables

I'm trying to implement replicas between master database and secondary databases. I've tried binary replication - it works as suggested. But I want use some service to manage this without manual ...
2
votes
0answers
32 views

When/how does removing an index cause a table to lock in MySQL?

Dropping an index in MySQL 8 is INPLACE, not INSTANT, per https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html. As I understand it, INPLACE means that there is potentially some ...
0
votes
1answer
13 views

gtid_binlog_pos variable contains two values

I am trying to setup a master-slave replication with galera-cluster (choose 1 node as the primary master) and a standalone mariadb server (slave). I have successfully setup the replication and data is ...
-1
votes
0answers
9 views

Returning the Count of multiple items with details from a single column

I have a table that looks like this: <table> <thead> <tr> <th>Product</th> <th>Order no</th> </tr> </thead> <tbody> <tr> <td>Tv&...
-1
votes
2answers
39 views

query performance within a foreach loop

In the app I build users will be able to book appointmnets...on (a specific) page load I must display all appts-past and future-a query does that...each row holds the details of an appointment. Each ...
0
votes
2answers
30 views

very high Temporary tables created on disk, how to fix them?

Hello we use nextcloud hand use as backend a mysql-db on Debian 10. I run ./mysqltuner.pl which said Temporary tables created on disk: 99% (48K on disk / 48K total) (I recently restart the mysql-...
0
votes
1answer
27 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
1answer
24 views

How to make mysql trigger to access another table's entry?

Say there is a DB with 3 tables Books table - contain book_id and its title Inventory - A list of book_ids in the inventory Logs - A list of log strings. Every time I add a new book_id to the ...
1
vote
1answer
20 views

What does running "show slave status" on a master server show?

We are using monitoring software that is throwing an error saying that Slave_IO_Running is returning No for replication on our master server. It is in fact true that when running show slave status on ...
1
vote
0answers
36 views

Why Isn't the MySQL Index Immediately Available?

We are in the process of moving from AWS RDS MySQL 5 to AWS RDS MySQL 8. We have a script that runs a few dozen queries. The last few look like this: ALTER TABLE my_right_table ADD INDEX (col_a, col_b)...
-1
votes
0answers
27 views

What does the sql code look like to count the amount of matches between two tables

I have two tables (USER1 and USER2) they each contain a user's email, first and last name, interest department and project ID I have the following questions: 1.) What would the sql code look like to ...
0
votes
1answer
20 views

Compare today's and yesterday's data in MySQL

I have the following table: location eventdate value loc1 2021-11-28 1 loc2 2021-11-28 2 loc1 2021-11-29 1 loc2 2021-11-29 3 loc1 2021-11-30 2 loc2 2021-11-30 5 And I'd like to compare the ...
0
votes
1answer
6 views

problem installing mariadb using mysql_secure_installation

I started secure install of MySQL with sudo mysql_secure_installation command, and I got that message: Securing the MySQL server deployment. Enter password for user root: Error: Can't connect to ...
0
votes
0answers
52 views

Is this database correctly structured?

I am in the process of converting a bus timetable to a database structure. I am building a free mobile app for a specific itinerary in my country that will: let users know when their next bus is due ...
0
votes
1answer
25 views

Unable to import data into MySQL table

We are having an issue importing entries into a table on MySQL version 8.0.27-0. I have been able to reproduce the problem with a simple example. Kindly note that all privileges have been granted to ...
0
votes
1answer
17 views

Best practice for data archiving

I am trying to figure out the best practice for data archiving. I have 1 pair of Master/Slave MariaDB servers where data is replicated from Master to slave. Since this is a very old app I am not able ...
0
votes
0answers
9 views

how to setup new user and password in mysql server ubuntu?

how to create a new user and password in MySQL server. And access this mysql user with multiple IP addresses by my other pc? The most important is how to improve MySQL server security?
0
votes
2answers
27 views

MySQL: Is There a way to dynamically generate multiple date range based on its date

The data I got is id date cnt 1 '2021-11-26' 1 1 '2021-11-25' 1 1 '2021-11-24' 1 1 '2021-11-23' 1 The result I want is id from_date to_date cnt 1 '2021-11-23' '2021-11-24' 2 1 '2021-11-23' '...
1
vote
0answers
11 views

Mariadb galera cluster with galera load balancer and master-slave replication: Errorno 1062 handler error HA_ERR_FOUND_DUPP_KEY

I have the following setup in my environment. Galera cluster with 2 nodes: db01 and db02. Server with galera load balancer installed: glb01, applications/clients connect to the cluster via port 8010 ...
-1
votes
1answer
28 views

Command basics, look through three tables

Hi, I'm starting to learn mysql and am confronted with the commands. I think I understand the basics, but with this example I'm failing to set up the commands correctly. The underlined keys are ...
-1
votes
0answers
62 views

Achieving 3NF in a online banking system

I'm learning MySQL with XAMPP and am trying a practice scenario. I want to achieve 3NF. I think I have got 1NF and 2NF down but not 3NF. For example, in my Account table would I need to split out sort ...
0
votes
2answers
26 views

Prepared statement looks right but throws error 1064

I've prepared a statement for updating users address. However I am getting error 1064, and I just do not know what could be wrong anymore. I would appreciate help with this. Thanks! Also I am 100% ...
0
votes
2answers
19 views

Moving from MySQL 5.x to 8.x - fails due to column called "over"

Interestined and frustrating one, this. We have an old database that is still very actively used via multiple Excel files with VBA in, including archived projects with such files in. There's a bit of ...
-4
votes
1answer
30 views

Best frameworks to use to make a low latency database website for begineers? [closed]

My idea is to do a data analytics project where I gather and clean some data into a database and make a website out of this database for users to interact with. However, I'm wondering what are some of ...
0
votes
1answer
10 views

Create "create table and schema script" for MYSQL via workbench

I've only seen questions where to create just the database from workbench for MYSQL. on SQL SERVER we can create the table script and with all the inserts with all the data. is there a way I can't do ...
3
votes
1answer
30 views

Finding number of Innodb pages to read

Is there any way to find number of pages that needs to read when querying for a set of pks? Eg., select * from Table where id in (1,2,3,4,5,6); Now is there a way to find the innodb pages that'll be ...
0
votes
1answer
22 views

How to create a mysql user that can access from any host but localhost

I was asked to create a mysql user that can connect to the server from any host but localhost. I tend to believe that "%" also means localhost but according to my teacher, localhost is not ...
-1
votes
1answer
15 views

Nested query is throwing Error Code: 1054 (unknown column)

SELECT booking.batch_id, (SELECT COALESCE(JSON_MERGE_PRESERVE('[]', CONCAT('[', GROUP_CONCAT(JSON_ARRAY(v0)...
0
votes
1answer
19 views

How to join an exsiting table to self-join query table?

This is the self-join query: select g.discription, m.title from genre g join genre m on g.title = m.main_title This is the table that I want to join: select band.name, band.genretitel from ...
0
votes
0answers
10 views

fix InnoDB: corrupted TRX_NO after upgrading to 10.3.31

After upgrading mariadb from 10.3.25 to 10.3.31 it fails to start like described in this bug report I temporary fixed it by downgrading mariadb to 10.3.25 again like I described here. To fix this ...
0
votes
0answers
5 views

Which rights does a user need to backup procedures in MySQL 8.x?

I am performing a backup on MySQL 8.0.26 which needs to include all routines, triggers, events etc. in order to be fully recoverable. The command I am using: /usr/bin/mysqldump -u $MUSER -p$MPASS ...
0
votes
1answer
35 views

How to get the maxium row value for a similar values

In my table below, I have id and job_id fields with some of the job_id fields being duplicates. I want to get the MAXIMUM value of the id field corresponding to each unique job_id. I have now tried ...
1
vote
0answers
22 views

PHP PDO Connect sometimes takes a long time to connect using mysql percona

I have a more or less simple application, written in php 7.4, using PDO to connect to my database. This connect usually takes under 0.0002 seconds > we are talking about 99.9% of my requests. On ...
0
votes
0answers
13 views

Actualizar en Trigger de delete en Mysql8

tengo una tabla en MySQL8 y una situación similar a esta: Tengo solucionado el trigger de insert, pero al querer hacer el trigger de delete (por ejemplo) me dice que no puedo modificar la tabla ya ...
0
votes
1answer
40 views

Multiple files found for the same tablespace

how to fix this error? 2021-11-22T19:40:23.065929Z 0 [System] [MY-010116] [Server] c:\openserver\modules\database\MySQL-8.0-Win10\bin\mysqld.exe (mysqld 8.0.24) starting as process 4892 2021-11-22T19:...
0
votes
1answer
25 views

Require assistance to write Query joining 3 tables

I have a table called form_submission_entries which contains data like this: id submission_id field_id value 1 1 1 John 2 1 2 Doe 3 1 3 27 4 2 1 Jane 5 2 2 Doe 6 2 3 23 The field_id ...
1
vote
1answer
15 views

WSREP connection to peer timed out, no messages seen in PT3S

I'm running 3 node Galera cluster using MariaDB-server-10.3.13-1.el7.centos.x86_64 and periodically seeing following errors in logs. I did google about this error but didn't find any good answer. Does ...
0
votes
1answer
17 views

Need help with join, keep getting Unknown column 'a.shop_id' in 'field list'

Sorry new to this: This is my objective: Develop SQL code to create a query, the query should join together three different tables and include attributes from all three tables in its output. This is ...
-4
votes
1answer
13 views

I have provided the database script where I have to run Sql script that answer the following question. I want Sql script for question given below

/******************************************************** * This script creates the database named music_store_system *********************************************************/ DROP DATABASE IF ...
0
votes
1answer
31 views

How to improve performance of a stored procedure called from a web page?

This is my first SQL stored procedure $sql = ("CALL Login_Details($ID);"); $result = mysqli_query($conn, $sql); $row = mysqli_fetch_array($result); mysqli_next_result($conn); When I run ...
0
votes
0answers
10 views

Mysql dump and live data syncing

I was hoping someone here could help me figure out a solution. I have a MYSQL database that logs live data. This comes from an API that continuously updates the database and this data is being fed ...

1
2 3 4 5
418