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
-1
votes
0answers
11 views

How to spot all transitive dependencies in mysql database design?

I've read up on 3NF and transitive dependencies but had a couple of questions I'm unsure if I have achieved 3NF with my database design. Context: I want to create a basic banking design where ...
0
votes
1answer
10 views

Mysql Deidentify names of users

I am trying to deidentify names of users for use in a test instance. I am referring to this post here. And so far my code looks like this : INSERT INTO tbl_client_copy2 VALUES (id, f_name, m_name, ...
0
votes
2answers
26 views

Confusion over using LEFT JOIN on multiple tables

I may be misunderstanding something basic here, but I'm struggling to find this issue being explained in my research. Let's say we have a users table and other tables that relate to the user, let's ...
0
votes
1answer
23 views

do_table or ignore_table alternatives on MariaDB Galera Multi-Master

As far as I can see, there are no do_table or ignore_table configs in MariaDB Galera multi-master (master-master) to skip or ignore specific tables, and there are only binlog_do_db and ...
-1
votes
0answers
41 views

Running query status is 'waiting for handler commit'

I'm inserting data to a MySQL table. The number of records is about 19 million. However, the query never ends, and it continues for ever. When I see the query status by SHOW Processlist, it stays on '...
0
votes
2answers
28 views

MySQL update query takes too long

I'm currently testing my website and have come across a slow query and I am not able to fix it. I have added an index on (uid, deleted) and still the update query takes some time. Let me start with ...
0
votes
0answers
17 views

Monitor existing cluster using Galera Manager throwing: deployment status error: DB IP address must be provided

I am trying to monitor my existing galera cluster using galera manager. I installed galera manager and chose Monitor existing cluster. I added the ssh keys provided in the manager in the respective ...
-3
votes
1answer
20 views

Returning the Count and details of multiple items sharing the same order number

I have a table that looks like this: I want my output like this whereby the count of the number of orders for products with the same order number is returned: Here's a visualization of the output: ...
3
votes
4answers
343 views

How to import only a specific database from a .sql file?

I have a file dump.sql with many databases in it: mydb1, mydb2, mydb3, etc. How to import only mydb3 and not the other databases? Won't: mysql> create database mydb3; mysql> use mydb3; mysql> ...
0
votes
1answer
12 views

Removing router from MySQL InnoDB cluster

Currently I have configured 3 node MYSQL InnoDB Cluster with two routers. Is it possible to remove one of the router from cluster? what are the implications ? using cluster.listRouters() I could see ...
0
votes
0answers
16 views

MySQL regexp_time_limit timeout in one server not others

I'm running into an odd issue with some of my AWS RDS's. I'm running this query as a test: SELECT id, current, CONVERT(AES_DECRYPT(payload,UNHEX(SHA2('PASSWORD',512))) USING utf8) AS payload FROM ...
0
votes
2answers
24 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
18 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
22 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
17 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
26 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 ...
-2
votes
0answers
19 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
23 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 ...
1
vote
1answer
33 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 ...
1
vote
1answer
18 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
12 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
42 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
32 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
25 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
22 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
37 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
22 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
7 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
53 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
28 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
12 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
63 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
27 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
11 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
11 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 ...

1
2 3 4 5
418