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
2answers
31 views

MySQL Server Status CPU/Load Formula

Are there any variables and formula that can help me calculate CPU/Load value of MySQL ?
0
votes
3answers
196 views

MySQL V8 replication lagging waiting for handler commit

let me start with some background. We've decided to upgrade our Master DB to a more performant machine. We got a server with 1.5TB of RAM, Quad Intel(R) Xeon(R) Gold 6254 and IO is all NVMe drives. ...
0
votes
1answer
26 views

Mysql - Shink table after delete

I have some tables with millions of rows (up to 6MLN) and I need to execute a delete to reduce the amount of rows. I created a procedure with a loop that delete 10k record each execution. What I did ...
0
votes
0answers
17 views

why the date_format get prev day in MySQL 5.7

I am using this function to get the day from unix datetime: select date_format(FROM_UNIXTIME(1606406400000/1000), '%Y-%m-%d') to my supprise, I get the day of 2020-11-26. but the day I get from ...
1
vote
3answers
85 views

SQL only the first row of each value

MYSQL version 5.7.31 How do I make it show the distinct rows of the value/column mainthreadid with the highest value of date? Note that the data is filtered using WHERE approved = 1 AND section != 150 ...
1
vote
2answers
51 views

Configure MySQL to minimize file locks

*** EDIT *** 8GB RAM SHOW VARIABLES: https://pastebin.com/ANAiq1E2 SHOW GLOBAL STATUS: https://pastebin.com/pwWKRiie *** END EDIT *** To start off, I'm not a DBA, so I may have overlooked something ...
0
votes
0answers
40 views

MySQL SSL Gives Warnings about big name Certificate Authorities

I have been able to setup my MySQL to use LetsEncrypt certificates with the steps below, unfortunately it is issuing warnings about official certs being self-signed (like from DigiCert, Comodo, etc...)...
0
votes
2answers
27 views

Speedup MYSQL(8.0.13) Restore from .sql file

I am receiving .SQL(500mb) backup file and restoring to MYSQL (8.0.13) database. Currently, restore is taking 5 hrs to complete. I would like to speedup the process and started working on it. Here are ...
1
vote
3answers
49 views

SQL vs NoSQL: Fetching All records in DynamoDB vs SQL Database

I am designing database for a project which requires frequent querying of all the records. The total number of records will be less than 500K. One of the databases I am considering is AWS DynamoDB ...
0
votes
2answers
36 views

How does MySql(InnoDB) update secondary non unique index?

Let's say we have a table with the following structure: CREATE TABLE Persons ( PersonID int NOT NULL PRIMARY KEY, LastName varchar(255), FirstName varchar(255) ); CREATE INDEX Lastname ON ...
0
votes
0answers
6 views

Seed data in mysql for e2e testing using tables backups

I'm using cypress for e2e tests and I currently seed the database for each test using sql files. The problem is that seeding many sql files adds up and take a significant amount of time Currently I'm ...
-1
votes
2answers
28 views

Is this the proper way to integrate normalization? [closed]

I'm trying to wrap my head around normalization. Is this the proper way to create a database with this data? How much more effective is it to use a foreign key instead of just repeating AAPL for ...
0
votes
0answers
18 views

Master node of galera cluster is not in sync, how should I sync them?

master node server id 10 SHOW STATUS LIKE 'wsrep_local_state_comment'; +---------------------------+----------------+ | Variable_name | Value | +---------------------------+-------...
0
votes
0answers
27 views

mysql multiple transaction rollback problem

I want to have multiple procedure call and one transaction commit, for example: call CreateRestaurant(?, ?, ?); call CreateMenu(?, ?, ?, ?, ?); call CreateMenuItem(?, ?, ?, ?); but I want to wrap all ...
1
vote
1answer
32 views

Mysql JSON update by value of a key

I have a table like: CREATE TABLE `campus_tb` ( `campus_id` int(11) NOT NULL AUTO_INCREMENT, `campus_dataJSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`...
-1
votes
1answer
35 views

How can I design address table?

Hello I need designing addresses table. I designed it but I didn't like it very much. My needs: I have customers and users (admins). I have addresses for customers and users. We need design in ...
1
vote
2answers
32 views

is there some way to do this sql script, in mysql?

This script was make in postgres, but I don't find their way to make it in mysql DO $do$ BEGIN IF EXISTS (SELECT FROM pg_database WHERE datname = 'example_work_db') THEN CREATE USER ...
0
votes
1answer
19 views

AWS Aurora Mysql different instance types for writer/reader

I have a db.r5.large for the master/writer. I want to add some readers. Do they need to be the same instance type as the master? Or can i add for example 3x db.t3.medium ?
0
votes
0answers
45 views

MYSQL ASSERTION on same last name and dob

I have to make an assertion on this: No patients should have the same last name and date of birth and are treated by the name same doctor. CREATE TABLE Patient( SSN varchar(11) ...
0
votes
1answer
29 views

Would it be correct to add extra columns to the junction table according to normalization rules?

Hello I have many to many relationship. I learning normalization rules. I add quantity because quantity column is used only for products associated with the invoice. My question is: Can I add extra ...
0
votes
1answer
29 views

Product_ID in my Invoice table [closed]

I'm doing a uni assignment. I have a stock table where stock is assigned an ID (product_ID). a sales table where the stock is purchased. an invoice table where sales are processed. I want the ...
0
votes
0answers
32 views

Database schema for ecommerce with multiple attributes and multiple variants

firstly I wanted an opinion about the database that you will find in the attachment. I am building a customizable ecommerce, and searching the web for a while I have come to this conclusion. Only ...
0
votes
0answers
12 views

Best mysql partition for stats table - by date or productid?

we have a large stats table which logs hits per day, per product. date | datetime productid | int hits | int channel | enum About 200M rows+ We have two ways of querying this date - It ...
0
votes
1answer
13 views

Mysql: [match() against()] full text search order full field value match first

I am doing a full-text search against a WordPress database, I am using match() against() to test the search term against multiple fields from multiple tables, all is working great, but I want to ...
0
votes
0answers
16 views

Master node freezes when second master unreachable in master-master replication

I have a 2 node master-master setup in mySQL 8.0.22. I have successfully got the nodes to both be writeable and update each other in normal operation. However, When I disconnect a node, it is not ...
0
votes
1answer
36 views

join two tables and limit results by an ordered list

problem: given is a table with language support (translations) a user can configure a list of languages (order is priority) now i have to get all rows which are part of app and the locale is in the ...
0
votes
0answers
9 views

How to model inheritance in MySQL for adding and deleting users?

I currently have a MySQL database and our ERD has inheritance for various tables. One is User which has as children: administrators, students and professors. The tables look as follow: CREATE TABLE `...
1
vote
2answers
107 views

Which AWS RDS; current t3.medium over CPU baseline, but not sure what's the next best option

I currently have one RDS instance, db.t3.medium running Aurora MySQL. Due to increase workload (expected), the CPU is, on average running just over its baseline and therefore in about 10 days will run ...
-1
votes
2answers
62 views

MySQL 8 - optimize all tables + indexes

I have a bunch of tables (InnoDB) within several databases (MySQL 8), that I run Garbage Collect scripts on, on daily basis, removing stale records that are no longer needed. All tables do have ...
0
votes
0answers
35 views

Mysql trying to drop table and how to recover data?

I have on lamp setup where suddenly I see all the database files are missing and gone and when I checked the error logs I can see few of this lines. InnoDB: Error: table `com`.`#sql` does not exist in ...
0
votes
1answer
18 views

How would I sync tables in two separate servers in mysql?

DB: mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1 I have two separate servers running MariaDB 5.5, both in production. Both setups are live, and users might access any ...
0
votes
0answers
16 views

MySQL only supported on Windows - 3rd party vendor

We have a 3rd party vendor currently indicating that their application only supports MySQL on Windows, and cannot work with MySQL on Linux or RDS. As far as I know, MySQL is MySQL, it doesn't matter ...
0
votes
2answers
85 views

Investigating RDS MySQL 8.0.16 stalls on write-heavy workload

I'm trying to track down some sporadic and difficult-to-reproduce issues with a MySQL 8.0.16 RDS instance connected to a web application with a write-heavy workload on a couple tables with large JSON ...
0
votes
1answer
44 views

How can I design Address table with different types?

I have a Address table and different address types like Personal and Corporate. I design a table but I think is breaking normalization rules. How can I improve this design? My design:
0
votes
1answer
41 views

How can I know the progress when importing data into MySQL using LOAD DATA INFILE?

I have a 1 billion record stored in csv file. I'm using LOAD DATA INFILE to import the record to the table. create table weibo_bind ( column1 bigint primary key, column2 bigint ); And ...
0
votes
1answer
18 views

How to keep history of MySQL/Aurora stored procedure revisions? [closed]

We need to maintain the history of the procedures. I think it will be easier to save the history in a table so every time someone either deletes or modifies the content of the procedure it will go and ...
1
vote
1answer
26 views

How to escape special characters in MySQL

When I do select * .. | mysql ... > /tmp/file from a table with text, there are some problematic characters that prevent me from loading it to a different db using copy (postgres) or load into (...
0
votes
0answers
9 views

Can't purge old kernel due to unmet MySQL dependencies

I am trying to purge unneeded files from my ubuntu 16.04 server as the root partition is 99% full. Somehow there is a problem with unmet depencencies regarding mysql: sudo apt-get autoremove --purge ...
0
votes
1answer
25 views

Can MySQL server send a “server started/running” signal to a remote server?

I have a MySQL 8 server on a virtual machine. I'd like to start it up when another server wants to communicate with it, and shut it down some time after there hasn't been any connections or queries. ...
2
votes
1answer
22 views

Check the result of a math expression in mysql

Let's say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means arithmetic operation (+ - * /) here. a b oper c ------------- 2 3 + 5 4 2 / 3 ...
0
votes
0answers
44 views

AWS Aurora/MySQL server crashes with spatial query

We are using AWS Aurora/MySQL, engine version 5.7.mysql_aurora.2.09.0 and we have a spatial index on a table with ~10,000,000 rows: CREATE TABLE `foo` ( ... `lat` decimal(10,6) DEFAULT NULL, `...
0
votes
1answer
187 views

MySQL event scheduler waiting on empty queue since server restarted 12 days ago

I noticed a process on the server which has been running for more than 12 days, which I think coincides with the last time MySQL was restarted. mysql> SHOW FULL PROCESSLIST; +---------+------------...
0
votes
1answer
52 views

How to optimize a lot of subqueries on MySQL

I have an ugly query on MySQL. There are a lot of subqueries in the query. They can not change the query for now. Sometimes queries stack in statistics state. Some document says, it depends on your ...
0
votes
1answer
20 views

MySQL: carriage-return in query breaks my import

I use simple echo "select * from tab" | mysql --batch --quick --skip-column-names .... > /tmp/file from mysql to get data. Then, import to postgres using \copy tab FROM /tmp/file ...
1
vote
2answers
28 views

Can I use older MySQL workbench with newest MySQL server?

MySQL newbie here. I'm using Ubuntu 16.04 (64-bit). I've successfully installed MySQL Community Server 5.7 and MySQL Community Workbench 6.3. Unable to find these answers in the MySQL forums. I've ...
0
votes
2answers
113 views

completely remove mariadb database and root password

The system is Fedora 32. Mariadb was installed and operational; it is now necessary to reinitialize Mariadb and all data and users. I removed mariadb-server and mariadb and erased /etc/my.cnf and my....
1
vote
2answers
47 views

Dynamic pivot table filtering and performance

I have these tables and I'm trying to rotate the subscriber to column table to horizontal and filter its result based on multiple and/or conditions like the following: WHERE first_name LIKE 'm%' AND ...
0
votes
0answers
26 views

Slave_SQL_Running_State init

I have a master-slave configuration. The slave stopped replicating with no errors. The only thing strange i see is this Slave_SQL_Running_State: init in show slave status; I see enter code ...
1
vote
1answer
29 views

MySQL - Query records between yesterday and today but with a specific time

I seem to be having a case of the Fridays. I am new to MySQL (few weeks now) and I just can't seem to get a report automated. I need to be able to query data from yesterday 8 am until today 8 am. I am ...
1
vote
2answers
38 views

Column in query should be selected according to value from another column

Assuming I have table1 with following data: table1 id| coffee_sender | coffee_receiver | received_bag_size | sent_bag_size| price | 21 tux -1 m -1 ...

1
4 5
6
7 8
396