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
2
votes
1answer
37 views

Adding indexes to production MySQL database without downtime?

I am looking to improve the performance of queries on a large MySQL database that is running in production by adding proper indexes. We have tried to add the indexes on the live database, but of ...
1
vote
2answers
36 views

Primary Key Int vs String [duplicate]

I have an attributes table, where I want a unique name for each attribute in order to prevent confusion between potentially similar attributes. In terms of the table design, the way I see it, there ...
0
votes
1answer
50 views

optimize query — remove nested selects

How can I rewrite the query to increase performance? Here is the query: SELECT flight.*, DAYOFWEEK(flight.flight_time) AS day_of_week, IF(SUM(...
0
votes
0answers
18 views

Fedora 30 - Install Percona Server for MySQL problem [closed]

i've created 3 virtual test servers. Connection between them is okay, to internet too. I am running Fedora 30 on them and i am trying to install Percona Server for MySQL with XtraDB Cluster. First i ...
0
votes
0answers
16 views

Aborted connections (Erros reading communication packets)

I set up a new Database Server over a SQL Dump with innoDB MYSQL 5.7 from MYSQL 5.6. The Server running external with many sleeping SMTP connections over 1 Mio a day. I made the server open for ...
0
votes
0answers
21 views

Creating a trigger which stores updated value with old value in separate table

I'm trying to create a trigger which adds an old value to a table with the new value next to it. For example, if someone were to update their address, I'd like the trigger to add the old address to ...
0
votes
0answers
21 views

Personal Expense Tracker: Database design decision for maintaining transaction

I am trying to work on a personal expense management. I do have budgets, loans, lending, savings and salary management. I am kinda confused between choosing between approaches for tracking ...
0
votes
0answers
22 views

MySQL refusing connection

Since a few days my MySQL-Server is refusing the connection after a few hours. When trying to connect I get ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/...
0
votes
1answer
11 views

How do I know which binary log file to open for continous backup?

This really has me confused, I want to have point in time recovery so I plan to take occasional full database dumps, and then between those dumps I plan to use stop-never with mysqlbinlog. But I'm ...
1
vote
1answer
22 views

MariaDB Parallel Replication Drifting at random times

I have recently been thrown in the deep-end to manage our Database servers since our DBA left. The current set-up is Mariadb (mariadb Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu), InnoDB (...
0
votes
4answers
48 views

How to use “NOT IN”

I want to find all codes from promo_id = 1 that does not exist in promo_id = 2 Table name: promo_codes Columns: id, promo_id, code SELECT code FROM promo_codes where promo_id=1 NOT IN (SELECT code ...
-1
votes
1answer
15 views

what happen you using put the ALTER statement in a transaction.?

I got a question about "What happens when you using Alter table command when a database is running? is it possible to run the alter table when database is running? and if it's possible what will ...
0
votes
1answer
9 views

Cascading REVOKE not working in MYSQL

I've read about an SQL option of cascading permissions when issuing a REVOKE statement. I've googled for how this option is implemented in MySQL, but haven't found it at all. This is the docs page ...
0
votes
0answers
16 views

MySQL stop responding

In a production environment, our Master MySQL 5.6.26 stopped responding. Our business handles about 1500 transactions per minute but there are times that nothing gets processes for many seconds (18 ...
0
votes
1answer
17 views

Backed up MySql folder… upgraded to newer version and cant seem to recover data

I copied the entire MySql folder mysql-8.0.13-macos10.14-x86_64 and installed a newer version mysql-8.0.17-macos10.14-x86_64. I should have researched but thought the installer would handle moving ...
0
votes
0answers
9 views

MySQL 8 (current) W2K16 server fails to start

Brand new Xeon, 64gb, 2tb RAID 5 W2K16 server. Installed current MySQL 8, setup as dedicated server and ran through setup just fine. Was having problems with slow restores from mysqldump.sql file so ...
0
votes
2answers
60 views

how do I change this query from SQL Server 2016 to MySQL?

This is a sample of a query that I currently use regularly in SQL Server 2016 (Standard edition). I need assistance converting this script to MySQL 5.6 syntax that will provide the same result. ...
0
votes
0answers
14 views

How to handle only failover mechanism in load balancing tools? [closed]

I am trying to implement percona XtraDB cluster. My application doesn't have much traffic, so I don't need to implement load balancing for splitting the traffic. I need to implement only failover ...
0
votes
0answers
18 views

MySQL Data Lag in local application

I am having an issue that I cannot see to find a solution to. We have a small server application that pings a GPS Server every 1o seconds and writes the values to two tables. Firstly GPSLive, ...
0
votes
0answers
21 views

MySQL event scheduler — Event running at wrong time

I have created a event to run EVERY 1 DAY at 3:15 (am). I know that timezones affects events and actually I create a query to check the START time in mysql.event (GMT timezone) and information_schema....
0
votes
0answers
16 views

MySQL: how to update a table based on changes to another table with trigger or stored procedure

Because of the limitation of a wordpress plugin I am using I cannot update tables from the website when they are created from JOIN statement. So I created a bigger table with data of all tables. But I ...
1
vote
1answer
85 views

Explain this statement about filtering in SQL Server vs MySQL

My company is in the midst of an IT overhaul and considering a shift away from MS SQL Server (2016 Standard) to a MySQL database (version 5.6.10). I have used MS SQL Server for years, so I am ...
0
votes
1answer
25 views

How to insert only one row which is going to update

I want to fire a trigger in Mysql DB before update the row. And I want to insert the entire row value in another table. My trigger fires, but the problem is, it inserts all the data from old table ...
1
vote
1answer
61 views

mysql inner join too slow

My machine disk is ssd and memory is 32G. My executed sql: SELECT `country_detail`.`country_name` AS `country_name` FROM `statistic_detail` INNER JOIN `country_detail` ON (`statistic_detail`.`...
0
votes
1answer
12 views

Tables locked(database locked) in MYSQL

We are using MYSQL INNODB,After inserted one record in table,Locked database operations resulted in proxy and application threads to increase that ultimately caused the proxy server to not accept ...
1
vote
1answer
31 views

Case statements for multiple fields when only certain cases exist for each field

We have an inventory feature where we generate Bills. There is an Edit Bill API call. We have implemented it as PATCH call. A Bill with id = 1 has 2 LineItems : | Stock Id | Qty | Rate ...
1
vote
2answers
35 views

Mysql Config Suggestions - CPU too high

My server (64GB,16CPUs) has mariadb 5.5.60. The queries are around 500 selects /second and 300 inserts/seconds. The queries are simple select of auth token and user details. Both are properly ...
-4
votes
2answers
52 views

Is a database trigger an appropriate implementation for an e-commerce application's promotions/reward logic? [closed]

I am attempting to architect an e-commerce rewards/promotions program for purchasing items. Here are some typical scenarios: $X amount of dollars off a purchase for a specific day $X amount of ...
2
votes
1answer
28 views

Problem filtering information in MySQL query

I've a little problem with a SQL query. The objective is to get an employee profile related to a role. Table EMPLOYEE (Louis,Mary,Christian): EmpId EmpName Table ROLE (Chef,Waiter,NULL): RoleID ...
0
votes
0answers
25 views

Show last line with left outer join

I have a problem with my syntax, I need to show the last record using inner joins, I used this example (https://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-...
0
votes
1answer
28 views

Mysql row-based binary logging behaving different in some versions of mysql (rows grouping)

Mysql row-based binary logging seems to have a different behaviour when grouping rows changes in some versions of mysql. Let's assume the following statement update three rows: UPDATE table_name ...
1
vote
0answers
15 views

Audit trail database design for multiple related tables [closed]

We have the database design requirement for audit trail purpose in MySQL 5.7. There is one main table and there are few sub-tables to the main table. For every record in the main table, there will ...
0
votes
0answers
13 views

Possible to automate updating development database on a VM?

Using MySQL 5.6 and VM Workstation Pro 12.1.1. At my job, the way things are set up is that my development environment is a locally hosted Virtual Machine. Every day the first thing I need to do is ...
-2
votes
0answers
12 views

convert row to column or pivot table in mysql [duplicate]

i have the following table but i want to display this pivot table manner, heading is : emp_id (non repeating), all the calendar_date row emp_id (non repeating), attendance_status
1
vote
2answers
16 views

GRANT permission ON database TO user not working on AWS

I'm having trouble granting a user permission on AWS. mysql> GRANT CREATE ON *.* TO 'hmrex_db'@'%'; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR 'hmrex_db'@'%'; +----------------...
0
votes
2answers
46 views

MySQL DBs taking up way too much disk space

I'm on Ubuntu Server 18.04 running MySQL server 5.7.27 with InnoDB. I confirmed that InnoDB file-per-table is On. I have a mysqldump containing all of my DBs that takes up about 1.3 GB disk space. ...
0
votes
1answer
15 views

mysql workbench - Couldn't find option to remove DROP TABLE IF EXIST

Recently I've been looking into export options to find an option to remove: DROP TABLE IF EXISTS and replace it with CREATE TABLE IF NOT EXISTS. I've seen on other MySQL clients like HeidiSQL or ...
-1
votes
0answers
25 views

How to fix mysql start service

I can not start my MYSQL, is having problem with give the command to start it. I use the CentOS7 system, running MySQL 5. I tried to start it but I can't systemctl start mysqld [root@ip149 ~]# ...
0
votes
1answer
8 views

Mysql - tablename.ibd file is huge in size

I'm using MySQL 5.6 and innodb file per table has been enabled. I noticed that one file named as tablename.ibd is 211GB, then query the information schema there it's showing 21GB. I have done a huge ...
0
votes
1answer
26 views

SQL order of precedence

Given a query ending in where a and b and c or d, will this behave as where (a and b and c) or d, or where a and b and (c or d)? (Or something else?) I've read the docs and they seem to suggest it ...
0
votes
1answer
20 views

mysqldump is ignoring option '--databases' due to invalid value

I have this MySQL database named capsule_corporation_database. Then, I have this my.cnf file in my home directory: ~/.my.cnf [client] host=localhost user=trunks password=mai database=...
0
votes
1answer
29 views

Size of record pointer in InnoDB?

I am trying to put on a scale redundant attributes and performance gain. Although storing redundant information causes many anomalies, I am solely focusing on the size of a redundant attribute vs. ...
0
votes
1answer
64 views

MySQL swaps on system with more than enough RAM

Om my new dual AMD EPYC 7401 24-Core (128G RAM) MySQL starts swapping even after a restart with no data in memory. NUMA # numactl --hardware available: 8 nodes (0-7) node 0 cpus: 0 1 2 3 4 5 48 49 ...
1
vote
1answer
31 views

How to get same value in a unique column but differentiated by other column values?

I have a table like this ID | PRODUCT_NAME | MANUFACTURER_ID | PACKAGE_ID --------------------------------------------------- 1 | VITAMIN A | 1 | 1 2 | PARACETAMOL | ...
0
votes
1answer
39 views

MySql date range query not showing all records

I am working on mysql query right now. I write a query from fetch data from march 2018 to march 2019. There are more than 5000 records for this year. I write a query with date range from march 2018 to ...
0
votes
1answer
34 views

optimize a 'having count (distinct ) ' query for big data

I have below query that I need to run on a table with 100million records but it's extremely slow (been running for 5 hours so far) I am not sure how to optimize it, would be grateful for any help. ...
0
votes
0answers
39 views

Is there a more efficient way to join a big table that this?

Consider the following. On an e-commerce site I have a sales_order table and a subscription table. The sales_order table has well over 1 million rows and about 25+ columns. The columns I'm ...
0
votes
1answer
26 views

Mysql Query Optimization [Match Against] [closed]

I have tried all the possible option to optimize the query, but no luck. Query 1: SELECT *, ( 3959 * acos ( cos( radians(51.5073509) ) * cos( radians(...
0
votes
1answer
33 views

Query to select specific columns based on current date

I have a table named 'mrtg' that has four columns: mysql> select * from mrtg; +----------+------+------+------+ | city | day1 | day2 | day3 | +----------+------+------+------+ | Delhi | ...
0
votes
1answer
21 views

MySQL Session sql_mode vs Global sql_mode

@@SESSION.sql_mode and @@GLOBAL.sql_mode is showing different options. @@SESSION.sql_mode STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @@GLOBAL.sql_mode NO_AUTO_CREATE_USER,...