A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

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

database design problem without duplicate data

i have a client database which has client data and users table. i store client data(Organisation details) in client data table and store user data in Users table which stores user email and hash to ...
0
votes
0answers
2 views

CASE WHEN SET vs SET CASE WHEN

I have these normalized tables: modules id | module | type -- ------ ---- 1 foo VARCHAR 2 bar TEXT 3 baz VARCHAR content id | module_id | varchar | text -- --------- ...
-1
votes
1answer
13 views

Pattern to keep trace on the record evolution history

I came across a lot of different answer relating my questions but I didn't get satisfied yet. In my team we have a table representing a list of Requirements. The table "Requirements" has a req_id and ...
-1
votes
0answers
20 views

MySQL how to make a backup while the server is not running

I made a update on my mac to catalina few days ago. After this my MySQL-Server was broken and I would like to reinstall it. Before doing this I would like to make a backup of my database. Because the ...
-1
votes
1answer
16 views

MySQL version appears to be inconsistent?

I have recently upgraded mysqld (percona server) from 5.5.60-38.12 to 5.5.62-38.14. This is on Debian, and the upgrade was done like this: # apt-get update # apt-get upgrade I am surprised to find ...
0
votes
0answers
7 views

MySQL replication - intermittently report ~4600 sec behind

I have a master/slave (MySQL 5.5.62) setup, with 1 master and 2 slaves, where someting strange occurs intermittently (but quite often) - normally, the slaves reports 0 seconds behind, then suddenly it ...
-2
votes
0answers
18 views

Splitting string into columns through loop in MySQL stored procedure [closed]

I have a table table_1 and i want to copy data of this table dynamically to other tables along with columns i.e 'table_2.column_1, table_3.column_2, table4.column_3') by passing as string. I want to ...
0
votes
1answer
8 views

MySQL database getting connected with any username and password [duplicate]

We have a website running on an custom Ubuntu server managed and hosted by the client himself, since last two days the website database is getting hacked and someone deletes all the db tables from the ...
-2
votes
0answers
21 views

Drop down table [closed]

i am new in database, im trying to create a simple db for social network like twitter, i have different tables like user, tweet, comment... My question : is it possible to use a table as a drop down ...
0
votes
1answer
17 views

MySql replication Failure

I have a mysql (Ver 14.14 Distrib 5.7.29, for Linux (x86_64)) primary -> secondary replication setup. Initially the replication was working successfully until we had to restart the primary database. ...
-1
votes
0answers
16 views

MySQL Table is Huge Where Only Text Values Are Stored

I am using LAMP Stack...And one of my db table wp_options (Rows: 10,600) is now about 194+ GB in size... Not sure why the size growing so much huge... As far as i am concern most of the records are ...
1
vote
0answers
16 views

MySQL SELECT .. WHERE .. ORDER BY … LIMIT 1000000,10 performance

I have following Innodb table with ~2.500.000 rows: ID | param1 | param2 | param3 | param4 | sort_field1 | sort_field2 | sort_field3 | sort_field4 ID - int, PK param1-4 - tinyint(1), INDEX (it has ...
1
vote
0answers
8 views

Deduplicate table based on createdate

I'm new to SQL and I'm trying to get my head round a lot of stuff but so far with little success. So I'm trying to get some help, here goes I have a table "Userdata" Email | FirstName | LastName | ...
0
votes
0answers
10 views

fixing broken convert_tz() in Mysql?

I come across the documentation for convert_tz() in MySQL and it says If the value falls out of the supported range of the TIMESTAMP type when converted from from_tz to UTC, no conversion occurs. ...
0
votes
0answers
28 views

How to learn MySQL properly to gain real experience? [closed]

Currently my career path of DBA and job duties do not include MySQL RDBMS setups. I'm seriously interested in MySQL database administration/development and as you all know real experience comes from ...
0
votes
1answer
15 views

How to select Overdue Rows with Date Frequencies?

+------------------------+--------+ | Invoice_id | due_date | amount | +-------------+----------+--------+ | 20 |2020-01-18| 1250 | +-------------+----------+--------+ | 21 |2020-...
0
votes
0answers
7 views

Combining MySQL instances on AWS RDS and EC2 [closed]

What kind of restrictions on RDS can be a problem when I am trying to combine RDS databases and multiple MySQL running on EC2?
0
votes
0answers
11 views

MYSQL - Run a query based on the results of a query

Trying to do some cleanup in the database, per manager request. However, I can't seem to get Mysql to run the inner query as written. select @tbl:=column_name as'Table', ( select max(length(@...
0
votes
1answer
11 views

How does MySQL skip ranges (nodes) in a loose index scan?

I've read the MySQL loose index scan, and it makes sense as optimization, however, when using an example B+tree, I don't understand how nodes can be efficiently skipped, due to potential matches ...
0
votes
0answers
15 views

MySQL query help

I need a query that returns the number of liked messages and orders them by the number of likes (from most to least). I have tried: SELECT users.user_id, messages.author, messages....
3
votes
1answer
31 views

Why mysql throws “invalid default value for field” only when two or more timestamp fields exist in the create statement?

In mysql 5.7.28 I create a table like this: create table t1 ( id int not null, d1 timestamp) engine=innodb; which works fine and creates d1 as non-null with default value current_timetamp,on update ...
0
votes
0answers
27 views

How to normalize a lookup table containing NULL? - or should I bother?

I have a table fee listing different fee descriptions and another table fee_amount holding the amount of the fee. The fee amount is different for each year, whether it's a joint fee or not and what ...
0
votes
0answers
13 views

Is there an option to turn existing global variant to constant in MySQL or MariaDB?

I'm trying to prevent changing some global variables which I set from the ini file. For example: server_audit_logging=ON I want to prevent it from changing by: SET GLOBAL server_audit_logging = ...
0
votes
1answer
16 views

Optimization: Increase Innodb_pool_size useful?

We run an AWS RDS instance with 8vCPUs and 64GB memory. All tables are using InnoDB engine. I have kept the default setup of innodb_pool_size to be half the memory. What I see is that almost 30Gb of ...
0
votes
0answers
15 views

MySQL: InnoDB data dictionary has unknown flags

I tried to delete and recreate a MySQL service and when I tried to start the service again I can't start it and I'm getting this error in error logs: InnoDB: Warning: table 'sampledb/table1' InnoDB: ...
0
votes
1answer
22 views

How to “properly” back up a MySQL/MariaDB database?

I have a small (<10GB) database backing an Intranet WSGI website. In the Apache logs I frequently see timeout errors whose timestamp coincides with the daily backup cron job. I'm running ...
0
votes
0answers
25 views

Why doesn't MySQL use indexes for subqueries?

This query takes forever to run (30+m - infinity). select date, sc, ( select count(fingerprint_id) from stats where hit_date >= t.date and hit_date ...
0
votes
0answers
10 views

Mysql 5.7 multiple sehemas to multiple machines on my laptop [closed]

Hello hope everyone reading this post is do well in all aspects of life. I wanted to put a software on couple of my friends and families machines (laptops, desktops, etc..). The software requires ...
0
votes
0answers
29 views
+50

Understanding INSERT … ON DUPLICATE KEY UPDATE deadlock scenario

I'm trying to understand a scenario where an INSERT ... ON DUPLICATE KEY UPDATE statement is causing deadlocks under high concurrency. The two tables in question: hosts: "CREATE TABLE `...
1
vote
1answer
20 views

Optimizing a query that gets the opening balance

I have a query like this select org, `Balance` from oss_collection_history och2 where och2.`DateFrom` = (select min(`DateFrom`) from oss_collection_history och3 where och3.org = och2.org) It ...
0
votes
1answer
20 views

MariaDB auto increment problem

Using OpenSUSE leap 15.1 10.2.29-lp151.2.9.1-x86_64 from vendor OpenSUSE (installed) CREATE TABLE testtab2( my_id2 INT AUTO_INCREMENT, name2 VARCHAR(10), comment2 VARCHAR(20), PRIMARY ...
0
votes
0answers
16 views

Why is `max_allowed_packet` shows 2048 even though it's set as 16M in mysqld.cnf

I've got the error related to max_allowed_packet: [S1000] Packet for query is too large (2,127 > 2,048). You can change this value on the server by setting the ‘max_allowed_packet’ variable. So ...
0
votes
1answer
19 views

Identify when Table Schemas were last Modified

I'm trying to identify all tables that have had indices added/altered since January 15. Is this possible? I've run: select * from INFORMATION_SCHEMA.PARTITIONS where UPDATE_TIME >= '2019-01-15' ...
0
votes
1answer
50 views

MySQL - [Note] Got an error reading communication packets

I have deployed MySQL on OpenShift (kubernetes) with GlusterFS as persistent storage and i am getting following error after every 2 seconds in MySQL Container logs 2020-01-17T18:00:04.787586Z 1541 [...
0
votes
1answer
14 views

How to force mysqldump to produce /*!40101 SET character_set_client = utf8mb4 */;

I am attempting to dump my database, which is set to, and only contains, utf8mb4 and appropriate collations. I use the following line: mysqldump --add-drop-table --add-locks --allow-keywords --...
0
votes
1answer
24 views

Mariadb is not starting

Please help me with this issue MariaDB not starting. Its a production server so not able to reinstall and clean the data at all. (This command executed by me) systemctl -l status mariadb.service ...
0
votes
0answers
13 views

Should I create a table replicating a CDN (ex. S3 storage bucket)? [closed]

This question can pertain to any CDN, but I am specifically using AWS' S3 and MySQL. Currently I have about 50 different tables that have columns referencing media stored in a single S3 bucket. For ...
0
votes
0answers
44 views

MySQL database not loading table data

My centOS 7 server had an unexpected shutdown. For some reason one of the mysql database shows that all tables are empty, but if I browse the files in /var/lib/mysq/database I can see the files(.frm ...
0
votes
1answer
17 views

MySql replication failed with error 1236 data position > file size with relay-bin file not increasing

My slave machine reset and now on SLAVE SHOW STATUS\G I have the error: Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start ...
0
votes
0answers
11 views

How to use MariaDB server_audit plugin on MySQL

I need to log connections on MySQL on Windows. When I google it, I saw some advice to use MariaDB server_audit plugin on MySQL. (for example: here) So, I gave it a try, but I failed. I copied the ...
0
votes
0answers
24 views

Should I use two indexes? [duplicate]

I would like to create some tables (including users table and products table) inside MySql Database with those index for each tables: Private Index (uses int), for relational table purpose only ...
0
votes
0answers
78 views

What is wrong with this query? I am getting syntax errors while running this query

Consider a table employee storing the details of the employee. ssn : social security number of the employee address : storing the address of the employee Looking at the address field in the ...
0
votes
0answers
13 views

MySQL replica is lagging without resource saturation

I am running MySQL 5.6.41 on AWS RDS. I have one multi-AZ master and one multi-AZ read replica. It is a vanilla configuration MySQL configuration, so the binlog is in MIXED format and the replication ...
0
votes
0answers
27 views

Does ORDER BY COUNT(*) DESC suffer a penalty?

I understand that older versions of MySQL parse the following but has no effect: INDEX (col DESC) For the following example, does performance suffer and is there a way to overcome it? SELECT col, ...
0
votes
1answer
47 views

Doubt about Database design (newbie)

I am learning about Database design, I have doubt about some database design conepts I want to know which is better design between two design Design 1 there is two tables: Object and Field ...
0
votes
1answer
46 views

MySQL: I need to return rows according to certain conditions

I have a 3 tables: work, recording, release 1 work can have multiple recordings and 1 recording appears in only 1 release and 1 release can be in m recordings. TABLE: work +---------+-----------+ | ...
1
vote
2answers
35 views

Filtering down specific criteria for all rows and return only one row for each column with the same value

I have house_leases and house_lease_terms (see table schemas below). A house_lease can have multiple house_lease_terms however there can only be one "active" term at a time. Table Definitions: ...
1
vote
1answer
28 views

How to count between two users unread messages in SQL

I am creating between two users unread message count script in SQL. My script problem not counting between two users unread messages. My script problem all users unread messages display as a same ...
-1
votes
2answers
24 views

How to count field with multiple values in MYSQL [closed]

I have a MySQL database and Table1 has a column that can get four distinct values e.g 1,2,3,4. Is it possible to create one query to count 4s and all the other values together? For example ...
0
votes
1answer
13 views

Additional Select statement within Cursor terminates looping unless Select statement returns a results for all cursor rows

I have a simple database called 'held_toys' (a held toy is a toy which is not on sale yet) which is comprised of a primary key and a foreign key for a Toy. I have a second table called 'toys_on_sale' ...