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
17 views

SQL Min and Max Amounts of Books Sold

Below are the book store tables which displays which book store belongs to which store category and the book trade table exhibits the total amount of books that one customer purchased from the store ...
0
votes
0answers
41 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
21 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
16 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
6 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' '...
0
votes
0answers
8 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
24 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
56 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
0answers
4 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
14 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
9 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
0answers
11 views

MySQL Replication: AWS RDS as primary and external self-hosted MySQL as replica

I have seen documentation on how to make AWS RDS a replica for a MySQL primary running elsewhere, but I am wondering if it is possible to do it the other way round (which I am surprised not many ...
0
votes
1answer
34 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
20 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
25 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
14 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
28 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
9 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 ...
0
votes
1answer
31 views

Get the date of a dump file from mysql database

I have created a MySQL database from a .sql dump file. I have lost the dump file, but I want to know when that dump was taken. Is it possible to know when the dump was taken from the MySQL database? ...
-1
votes
1answer
16 views

MySQL Stored Procedure Generating an Error during creation

I have a MySQL stored procedure (shown below) that's supposed to build a list of IDs from a table of hierarchically related items. I'm having to re-engineer the stored procedure to switch from using a ...
0
votes
2answers
26 views

Downgrade mysql 8.0.27 to 8.0.26 ubuntu 20.04

I set up a new AWS EC2 instance with Ubuntu 20.04, php, Apache, and mySQL. I also installed N-Able (Solarwinds) server backup. I am able to do system and file backups but the mySQL backup fails. N-...
0
votes
1answer
22 views

Why does auto increment jumps by more than the number of rows inserted (By 3)?

I have MariaDB configured frot 3 node Galera Cluster and haproxy and auto increment is going 1,4,7,10,13 etc Why and how do I fix it? [mysqld] port = 3307 user = mysql pid-file ...
0
votes
1answer
19 views

Linux MySQL Optimization

Hey we moved to a much bigger Server and we want to optimize our performance from MySQL. Does anyone know how to increase the performance? Or like a config generator or something like that. I don't ...
-1
votes
0answers
4 views

MySQL error log shows try to connect unknown database unknown user Got an error writing communication packets

I can see that there are lots of aborted connections every minute. There is no database called unconnected and there is no user in unauthenticated. Also when I run telnet to port 3306 its connection ...
1
vote
2answers
20 views

Can connect to virtual MySql instance using 127.0.0.1, but not localhost

Running MySql 5.7.33 on a local VirtualBox instance. I have forwarded port 3306 from the virtual server to be accessible locally. On my virtual host, I have this in my /etc/mysql/my.cnf file ... [...
1
vote
1answer
19 views

Why am I unable to edit users privileges in MySQL Workbench

I just created this brand new database (MySQL 5.6 on Windows) and created a new admin user but now I can't modify any of the users privileges. I can click the Add Account button (notice there's one ...
-2
votes
0answers
37 views

Is it possible to create a .exe installer for the client/user program i plan which connects to the server-based database program i plan? PHP MySQL

I'm planning to make a PHP and MySQL Point of Sale program through XAMPP. The plan is to make a whole database program server that will contain the stats while the client program that's intended to be ...
0
votes
0answers
19 views

mysql replication duplicate entry

I replicate my mysql master to a slave. They works very well but once in a while, duplicate entry would happen and I don't know how to resolve it ending up copy the master over and start replicate ...
0
votes
2answers
21 views

How to increase range of values?

We're doing database design and implementation work in my SQL course, and I accidentally made the value range too short. We're supposed to be building a DB for payment information, and when I put in ...
-1
votes
1answer
32 views

Should i use a composite key or 1 primary and 2 uniques in a branch table for banking database? [duplicate]

CREATE TABLE branch ( branch_id INT NOT NULL AUTO_INCREMENT, address_id INT NOT NULL, branch_name VARCHAR(30) NOT NULL, PRIMARY KEY (branch_id), UNIQUE (branch_id, address_id, ...
0
votes
0answers
22 views

Replica is significantly behind Source on MySQL 8.x

I am running a MySQL 8 source/replica setup on two dedicated bare mettal AMD servers, each on Ubuntu 20.04. Connection is fast over 1GB ethernet switch on dedicated NICs. The database replicated has ...
0
votes
3answers
28 views

Automatically start SLAVE (mysql) Master Master

I performed the configuration of a database replication (slave), performed it as MASTER MASTER, however, every time I restart as machines, I need to manually start the slave I use the command: START ...
0
votes
0answers
23 views

Sum and Count of user quantity

Table 1 Table 2 I am struggling to write a query in which I want how many times a users have purchased a quanity and sum of total quantity of one users Sample Entries Table 1 Table 2 So my result ...
0
votes
0answers
16 views

What exactly is a persistent storage database?

I have to load some stuff(data) onto a "persistent storage database." The assignment says: 'Please use some sort of network-accessed database like MySQL or Cassandra, but it may be any such ...
-1
votes
1answer
21 views

Explain doesn't find index when joining table

We use EF Core to work with our MySql 5.7 database. Table1 has a unique key named Guid: DESCRIBE Table1 Field Type Null Key Default Extra Id bigint(20) NO PRI (NULL) auto_increment Guid varchar(36) ...
-2
votes
2answers
86 views

Seeking advice on 3NF on my first database design for a banking system [closed]

Like I say in the question this is my first go at database design and normalization I want to achieve 3NF. The general context is a basic banking system, that allows for loans and multiple ...
0
votes
2answers
42 views

Understanding SQL aggregation efficiency

How would the first query compete in terms of performance against the second alternative: Query 1: select count(*) page_views, count(distinct(session_id)) sessions, (count(*) / count(distinct(...
0
votes
1answer
28 views

Division By 0 error when running an update query MySQL

I am trying to run an update in a table and I get an "Error Code: 1365. Division by 0". The sql commands I use are these: SET @@autocommit = 0; start transaction; update XE_General join ...

1
2 3 4 5
417