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

mysqld_pre_systemd fails when starting MySQL on Fedora

I installed MySQL using dnf (I'm on fedora) and from the officials rpm on the website. Both had the same problem : I have an error when I do systemctl start mysqld.service. So I checked systemctl ...
0
votes
1answer
21 views

Count between dates and operate between rows - SQL [closed]

I try to count elements between dates and operate between rows agnostically. As an example, suppose the month starts from day 2 until day 2 of next month. The table has fields {timestamp, status} and ...
0
votes
2answers
20 views

How to profile MySQL during a period of time

I want to know during the execution of sysbench --time=100 ..., the bottleneck (function) of MySQL. So I want to profile MySQL when executing sysbench --time=100 .... I know by set profiling = 1;, I ...
0
votes
2answers
30 views

mySQL subqueries calculating winning percentage

I have a table where I store the data of a random dice game where you win if you get a sum of 7 between the two dices. Here's the table: id result dice1 dice2 player_id 1 LOSE 4 4 9 2 LOSE 4 5 7 3 ...
0
votes
2answers
26 views

Need help designing database, Create a table with additional columns/fields in the table, or create a new table for each different type?

So the tables that I have are Certificates, Approvers, CertificatesApprovers, Categories, Approvees, Status, AdditionalFields, AdditionalFieldsValues. Many Certificates to Many Approvers relationship. ...
0
votes
1answer
19 views

Enabling mySQL replication using GTID after purging binlog

I am currently working to get GTID based replication established between mySQL 5.7.33 on 2 servers. Amidst this work, I ran into a disk space issue and purged the binary logs on the master. Now I have ...
0
votes
2answers
38 views

Moved site to new, similar server - same query is taking 10x longer?

Edit: Old server SHOW GLOBAL VARIABLES: https://pastebin.com/raw/8GPTpub0 And for the new (problematic) server: https://pastebin.com/raw/XnfbFzh7 This morning I moved my WP website from one server to ...
-1
votes
0answers
18 views

Why my rds instance has so high write iops?

the sum count of the tps is about 500 (Com_insert: 30, Com_delete: 3~4, Com_update: 450~500) while the write iops is 8k+ and I found that the Create_tmp_disk_table is nearly always 0 ...
1
vote
2answers
27 views

group by left(col, x) text column - possible to index?

I'm looking for a possible index for the following: select left(name,10), count(*) from tab group by left(name,10) This is the table config: create table tab( id int(11), name text, primary key(id), ...
0
votes
1answer
36 views

MySQL - terrible performance for simple query on large table

I'm having huge troubles with the performance of a very simple indexed query on a quite large table. Configuration: 48 cores, NVMe Raid, 376GB RAM (144GB used) Mysql 8 (Percona server) Server ...
0
votes
1answer
13 views

changing the value of a column in a table based on the value of another column

I have the following table that is the "Class" table: class_size class_number student_id 2 4005 1 4007 2 4001 3 4010 I want the "class_size" column to be generated ...
0
votes
1answer
11 views

Child, Parent and Grand-Parent relationship where a child may not necessary have a parent or a grand parent

Here, I have some problem representing a hostel-room-inmate entities. First an inmate is given a hostel seat, Then he is allotted a particular hostel, Then he is allotted a room. My approach is that ...
0
votes
1answer
28 views

How important is drive speed for a MySql server

I'm trying to invest in a self managed server for MySQL. My use case will involve querying a full text index of anywhere from 5m to 20m records. About 5 thousand full text searches per hour. Usually ...
0
votes
2answers
39 views

Delete from GROUP BY with multiple GROUP columns

I have a simple table consisting of id, channel_id, value and timestamp: CREATE TABLE `data` ( `id` int(6) NOT NULL AUTO_INCREMENT, `channel_id` int(11) DEFAULT NULL, `timestamp` bigint(20) NOT NULL, `...
0
votes
2answers
33 views

select insert query optimization on large volume MYSQL 8.x

Im trying to run this SELECT query from table bidPrice that has over 160 million rows into a second table called spreads_v2 INSERT INTO spreads_v2(SELECT bp1.timestamp, bp1.idExchangePair, bp1....
0
votes
1answer
33 views

'user' is not valid at this position, expecting an identifier

CREATE TABLE 'users' ( 'id' int(11) unsigned NOT NULL AUTO_INCREMENT, 'name' varchar(75) NOT NULL, 'password' varchar(100) NOT NULL, 'email' varchar(100) NOT NULL, PRIMARY KEY ('id'), UNIQUE KEY '...
0
votes
1answer
19 views

Search database name in information_schema database

This is my sample data. I would like to find other database besides information_schema using only information from information_schema. mysql> SELECT * FROM TABLES LIMIT 2; +---------------+---------...
0
votes
1answer
20 views

Why turning off condition_fanout_filter speed up query

bin/mysqld_safe --user=mysql --optimizer-switch=condition_fanout_filter=off --innodb-flush-method=O_DIRECT & Query (from TPC-H query-19): SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS ...
0
votes
2answers
47 views

Car Dealarship Database Design

I have to design the following sentences for a company that sells different vehicles: "The database must keep track of everyone involved(customer and staff) in the sales for vehicles. The sale ...
1
vote
2answers
143 views

how to change “localhost” in MariaDB

I wonder if there is any way to change localhost in MariaDB. For example, while creating a user in database, it shows 'user'@'localhost' I would like to know how to change localhost to any other ...
1
vote
1answer
22 views

Group by data by month with selected fields not to be included in group by class

Is there any way to group by the data by the only month but I need the no_inv and date_inv columns to be selected too. Mysql query : SELECT no_inv, date_inv, SUM(total_unpaid) AS unpaid, SUM(...
1
vote
2answers
38 views

Nosql vs sql for genre management

I'm looking to keep a simple database for movies, with details like Name, YearofRelease, Genre etc. The key factor is the genres entry, where a movie could fall into multiple genres. Using a sql based ...
2
votes
1answer
38 views

Lock wait timeout exceeded while altering table

I'm trying to ALTER TABLE on a table with 3 million rows, on a local development database on my PC. No other queries are running at the time, in fact i tried it just after restart. The query runs with ...
0
votes
0answers
38 views

Write the SQL code that will list only once the Physician-Person appointments

Write the SQL code that will list only once the Physician-Person appointments. In other words it will not list twice an appointment between the same physician and person Can someone help me solve this ...
0
votes
1answer
17 views

Contact table for customers and employees

I have this relationship that I need to define in my database. What would be a good way of referring contact details to my employee and to my customer in MYSQL? I am not sure if the ||------|| is one ...
0
votes
0answers
12 views

How to resolve the corrupted innodb table

I had a corrupted InnoDB MySql database after the customer downloading the wamp server on the computer again for wrong! so I worked on a lot and I got back most of the data, but one of the tables not ...
0
votes
0answers
7 views

what is good approach for storing different category product and also to implement filter on it?

I am having Product table where I am having multiple product category, and different product category have different attribute. as given is my table design images ----- id desc url timestamp images --...
0
votes
0answers
51 views

mySQL workbench Unable to access Performace Schema (sys)

I'm facing a problem on mySQL workbench (ubuntu 20.04). Apparently I get a message saying that is unable to access Performance Schema helper (sys). Can't open file '/etc/debian_version'. enter image ...
0
votes
1answer
14 views

Is there a way to see completed process list in mysql (SHOW FULL PROCESSLIST)

I am new to Mysql server, I use the below query to see what are all processes or queries that users are currently executing. SHOW FULL PROCESSLIST; I needed to check those processes, which users ...
0
votes
0answers
8 views

Flushing hosts without being able to connect to the host

I'm seeing this dreaded error: Host '10.0.12.34' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' The server is an RDS instance running in AWS. There are VPC rules ...
0
votes
1answer
14 views

Identifying processes that open MySQL connections

I need to identify which application process issues a large number of MySQL connections on a Linux system. The show processlist sql command or tools like innotop do not really help, as they only show ...
0
votes
1answer
23 views

SELECT JOIN same table prevent duplicates MySQL 8.x

I have a table called 'price' with the following rows idRequest | idPair | idExchangePlatform | lastPrice 1 1 1 95 1 1 2 ...
-5
votes
1answer
28 views

Write the SQL code to find the average charge by the family medicine physicians

We need to write the script to find the average charge of physicians who specialize in "family medicine. But i'm confused on how to write this because when we look at the appointment table, where ...
0
votes
0answers
27 views

Unable to Execute Script files (.sql) in MySQL using DBeaver plugin, in Eclipse CDT with macOS Catalina (Shows, “No Active Connection”)

Unable to execute the script files (.sql). It shows "No Active Connections". But, test connection is complete, and localhosr has MySQL Icon with connection check mark, and I refreshed the ...
0
votes
1answer
36 views

Table design comment_seen vs comment_unseen — migrate data from one to another

I have system that allows comments posted by users and tracks which users seen which comments. The read/unread state is tracked within comments_seen table. Surely enough, it grow tremendously. My goal ...
0
votes
1answer
18 views

Would current configuration be better as a staging environment and how to determine best practices to enhance production environment

Task: Database Revision Legacy System: LAMP (MySql v5.5) Current System: Node Express MySql v8.023 Data is acquired from 14 sources as .txt files with different delimiters and imported to their own ...
2
votes
1answer
50 views

MySQL performance issue when a hash join is used

I'm having an issue where the optimizer seems to be doing a full table scan and exhibiting a big performance hit when a hash join is used. Schema and sample data: CREATE TABLE `px_po_status` ( `...
0
votes
1answer
39 views

Mariadb concurrent insert not working for MYISAM tables

I am using mariadb 10.1.13. I have a large MYISAM table ( > 2000000 rows ) which used to have frequent 'INSERT' operations from multiple scripts. Recently I had to add a new 'SELECT' query to the ...
0
votes
0answers
15 views

How recover Tablespace from table in MySQL

my database fail and I dont have a backup so I have the .ibd file each table in database but when I try recover in other database coping the files there the database show me the structure and all ...
0
votes
1answer
10 views

MySQL - Information_schema Events - How persistent or retention is managed

I'm a MS SQL guy and understand agent job history. I'm now working with MySQL. I've searched hi and low but I can't find any solid info on how event data within information_schema.events is persisted, ...
0
votes
0answers
17 views

MySQL keeps ignoring MAX_EXECUTION_TIME

I have an instance running which have mysql8 and a nodejs application server. This query keeps popping up and freezing. SELECT /*+ MAX_EXECUTION_TIME(2000) */ COUNT(*) FROM my_table; Even though this ...
1
vote
1answer
22 views

MySQL Like for Like - 100% accurate Backup/ Restore

I've struggled to find the answer to get a perfect backup and restore for MySQL. I need to do performance test on a database so I need to make sure that the page files, indexes, fragmentation - the ...
0
votes
0answers
64 views

importing tablespace showing error 2013 (HY000): Lost connection to MySQL server during query [closed]

trying to recover innodb table from ibd file (576kb). this is the exact steps i do: getting create table query using mysqlfrm command create the table Alter Table discard tablespace moving the new ...
1
vote
1answer
17 views

max_connections set to 500, Max_used_connections never go over 151 [closed]

I have this set in the mysql config file, rebooted, seems to be correct. SHOW VARIABLES LIKE "max_connections"; +-----------------+-------+ | Variable_name | Value | +-----------------+----...
1
vote
2answers
11 views

MariaDB - Changing explicit_defaults_to_timestamp

I am trying to check from MariaDB docs online how I can turn ON explicit_defaults_to_timestamp global variable. I encountered this as part of Apache Airflow db setup. Is there any solution to do this? ...
1
vote
2answers
13 views

Can text fields be not loaded for join memory purposes in MySQL?

I want to use many text fields for a table but they can be very large so doing complex operations with rows of this table, like joins with big tables can become intractable or very slow I understand ...
0
votes
1answer
12 views

MySQL on Windows Refuses to Start as a Service

I am trying to install MySQL 5.7.33 onto a windows 2012 server. A process I have done many many times. I have copied the Zip file onto the server and expanded it into G:/mysql57/ (the same zip file I ...
0
votes
2answers
20 views

MariaDb mysqldump configurations

We have extremely large database (mariaDB 10.3) and mysqldump taking 2-3 hours to take the full database dump and also causing downgrade in application performance during the dump process. After some ...
2
votes
2answers
197 views

MySQL best way to define a couple of columns as both null or both not-null

On a MySQL DB, I would like to find a way to force a couple of columns be both null or both not null at the insert / update time. This means that on each record, both columns are null or both columns ...
3
votes
1answer
251 views

Is it possible to automatically generate a Unix millisecond timestamp in MySQL 5.7

I have created a created_time field with BIGINT in a MySQL 5.7 table, now I want to auto generate a Unix millisecond timestamp when I insert a record. Is it possible to do this? I have tried the code ...