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
4answers
138 views

Searching large dataset, best way to speed this up

i'm querying a table full of vehicles, however each one of these vehicles MUST have another row within another table. This query is currently taking way too long! Do I need to index the table, or is ...
0
votes
0answers
20 views

How to properly perform database maintenance on a live website

I'm using WordPress to host a website, and for years, I've been using plugins to perform maintenance (delete revisions, expired transits, optimizing table, etc.) on the msyqls database, but I've been ...
0
votes
0answers
14 views

Is my connection using SSL or not?

I have two MySQL 5.7 databases ([db1] 5.7.27 and [db2] 5.7.29) (n.b. db1 is Linux, db2 is Windows) I have created a basic user on db1: CREATE USER 'ssltest'@'%' IDENTIFIED BY 'ssltest'; GRANT ALL ON *....
0
votes
0answers
15 views

Why is MySQL dump not restoring all my tables?

I have Mysql 5.5 server on a EC2 instance which have multiple sql users to perform operation , now i want to create a back up db server . For which i have created a mysql dump using root user . ...
0
votes
0answers
9 views

Replication refuses to use SSL

I have two MySQL 5.7 Servers (5.7.29 and 5.7.31) I have created a set of SSL certificates and copied them to both the source and replica DB's so they both have the same certificates in place. On my ...
1
vote
1answer
15 views

How to both reference and return a result set from a stored procedure in mysql?

I have read that a select query that stores a result into variables does not return a result set. Is there an efficient way to do both? BEGIN #return this and also use it in the following query ...
0
votes
1answer
42 views

Something is totally wrong with my query. .. Slow. .. Why?

i have query with multiple JOINS and SUBQUERYS and it seems i have messed something up here. .. It did worked when there was low data (data is not even that much now too) but now it is not even ...
0
votes
2answers
12 views

how to nest query with avg and sum group by outer in MySQL 5.7

I have a dataset like this: tenant_code app_id reg_count 1 1 0 1 2 0 2 1 1 2 2 1 3 1 5 3 2 5 and ...
0
votes
1answer
17 views

MySQL subquery with WHERE filters not working as expected

I have a question/tagging system like StackExchange. I want to display the tags associated with each question and also show the total number of times the tag is used, unless the question has been ...
0
votes
1answer
13 views

How to perform multiple joins onto a table and then join resulting table onto multiple columns into another table

| family_id | parent_id_1 | parent_id_2 | ----------------------------------------------- | 1234 | 9999 | 9567 | ----------------------------------------------- | ...
0
votes
1answer
21 views

Foreign Key Settings

I will start by mentioning that I am very new to databases. I am working in MySQL. Can anyone give me a layman's view of when you would use which settings for the foreign key options so when you would ...
1
vote
1answer
15 views

Avoid multiple Case When - MySQL

I have a query: Select <CASE WHEN>, myfunction(param1, <CASE WHEN>) FROM table Both the Case When instances are the same set of conditions. So here I am clearly repeating the same ...
1
vote
0answers
23 views

Check for global read lock on MySQL

Can I check whether a global read lock is in place? My application uses MySQL (actually MariaDB) on InnoDB. I'm going to run mysqldump --all-databases, which will impose a global read lock for an hour....
1
vote
1answer
79 views

Issues with MySQL 8.0.17

Recently we upgraded MySQL version on our UAT database server to 8.0.17 from 5.7.18. We are facing issues with our web application, Website is loading slowly if there are more users accessing it I ...
1
vote
0answers
17 views

MySQL: order tables by entity relations?

Sorry if the title is a bit ambiguous - I'm not asking about anything to do with SELECT. This is the problem: I have a database with tables and a number of foreign keys defined between them. In many ...
2
votes
1answer
26 views

Why does MYSQL use blocking locks instead of optimistic attempts that fail immediately?

Why isn't optimistic concurrency control more commonly supported at the database layer? It is a valid alternative and can completely do away with any blocking locks. For certain usage patterns, this ...
0
votes
1answer
35 views

Mysql : what are the steps for creating a database schema for a big project with many tables?

I am creating a project with lots of tables like one for taxes, one for employees, customers etc. So how should I go about it? For eg: building the ER model first or something like that
0
votes
0answers
33 views

MySQL Group Replication - Member won't join after clone, access denied for for user mysql_innodb_cluster

I have a problem with a MySQL group replication cluster member not joining the cluster group after cloning operation. Question: How do I get the failed member to join the group again and be able to ...
0
votes
1answer
20 views

Is it possibly to sync more than 2 MySql databases schema (except table row data)? If yes, how could I achieve it? [closed]

I'm developing a multi-client web application for different company. Each company data is private, and I'm considering to create each database for each company to keep the data private and easier ...
0
votes
0answers
7 views

Conecting to MySQL through a 'double tunnel'?

I am working on database servers in what seems to be a typical setup now-a-days: the database servers can only be accessed from a bastion server, and they don't accept connections on the relevant port ...
1
vote
1answer
20 views

How to improve performance merging multiple table via cursor

I have the following stored procedure to merged multiple tables into 1 table. The records that im tryin to merge is about 150M. The procedure is quite slow. Here is the query BEGIN DECLARE done INT ...
0
votes
0answers
22 views

MySQL TPS Speed down with process hang on status 'Update' or 'Updating'

I have mysql running with a high concurrency press test. MySQL can running with around 1000TPS for 15 minutes with 200-400 running thread. Then performance speed down, with process waiting for status '...
0
votes
0answers
14 views

Does CREATE INDEX on Aurora MySQL 5.6 block INSERT / UPDATE?

I want to add a secondary index to a table on AWS Aurora MySQL engine 5.6.10a in production. The MySQL doc says for an index create like this: CREATE INDEX name ON table (col_list); The table remains ...
1
vote
4answers
33 views

Find timestamp for highest number of concurrent users by date

I have a table including these columns: Sample data: concurrent_users concurrent_timestamp concurrent_date 3 2020-06-24 07:20:00 UTC 2020-06-24 7 2020-06-24 ...
1
vote
1answer
28 views

Why Am I Getting Inconsistent LEFT JOIN Results in MySQL

Background I have a database in 3 different locations with the same exact static data (nothing updates it, it was a one-time import from another system). In 2 of the 3 locations, a query similar to ...
0
votes
1answer
30 views

How to upgrade Mysql 5.5 to 5.6 without data loss on aws-ec2?

MySQL server (5.5) is running on aws-ec2 instance with following linux details NAME="Amazon Linux AMI" VERSION="2015.09" ID="amzn" ID_LIKE="rhel fedora" ...
-1
votes
0answers
21 views

MySQL keeps on crashing while I'm starting it

I am running a webserver in Ubuntu Linux 14.4 Way back it only experiencing some downtime and I only simply start it using service mysql start But now it can`t start. Can anyone give me an insight to ...
0
votes
0answers
13 views

Lost over 400k rows after scheduled system restart in MariaDB DB

I have lost over 400k rows (not a lot considering I have over 3000 thousand rows per minute) I have hypothesized that it is not being written to disk often enough. Is there something that I can do to ...
1
vote
2answers
17 views

How can I use LOAD DATA LOCAL INFILE for specific columns?

I have a directory of text files, as the filename is the id, and the content text for this table: CREATE TABLE texts ( ID int(11) unsigned, Added date, Text MEDIUMTEXT, PRIMARY KEY(ID) ) ENGINE=InnoDB ...
1
vote
0answers
28 views

Migration from Postgres to MySQL using MySQL Workbench (Error in Reverse Engineering catalog. A task has failed while execution)

When I am trying to migrate database from Postgres to Mysql, I am facing an error in the Reverse engineering source step in which the log shows Starting... Connect to source DBMS... - Connecting... ...
0
votes
0answers
40 views

How to get the list of foreign key together with the index name on that foreign key?

I am using this query to list all the foreign key, but I also need to have the index name on that foreign key. select a.* from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as a where a.CONSTRAINT_TYPE = '...
0
votes
1answer
11 views

Where is MySQL template directory for pristine Data?

I'm using MySQL 8.0 in Windows. I'm trying to set up multiple servers. And I need to copy the Data directory for it. The MySQL documentation says Windows MSI package installers create and set up the ...
0
votes
0answers
13 views

How to exclude data if another column is null?

This SQL query returns a column called "organisation_names" however the data is coming from a column called organisation_users and I would like to exclude "organisation_names" if......
1
vote
1answer
21 views

How do you combine multiple update statements for the same row using MySQL trigger

Background: Each time a column is modified, I need to update the associated column (which has the same name) in a second table. This is my first attempt at using a trigger. Code: Here's a simplified ...
0
votes
1answer
28 views

What is branching factor of mysql myisam's b-tree

I am doing some research but I am struggling to find what is branching factor of MYISAM's b-tree, e.g. how many keys per page (node). I also would like to know how the char/varchar keys are stored - ...
0
votes
0answers
15 views

Change Data In Field To Row Header [duplicate]

I have this mysql ddl Create Temporary Table #empData ( workDate varchar(100) ,numOfCustomers int ,numOfSales int ,avgTimeToGreetCustomer varchar(100) ); Insert Into #empData Values ('07/01/...
0
votes
0answers
21 views

Perform SELECT using WHERE IN clause on JSON Object in MySQL

I have a json column in MySQL which contains a list of items for eg. "style": {"style1", "style2", "style3", "style4", "style5"} I want to ...
2
votes
0answers
26 views

Cannot access MYSQL safe mode using skip-grant-tables on Windows

I am running a MySQL server 8.0.21 on a Windows Server system. Recently I ran into an issue where my root user lost all its privileges. I am trying to gain access to the MySQL server using: mysqld ...
1
vote
0answers
32 views

Non-existent table that actually exists?

I've been tasked to solve a very difficult and interesting problem. Given is a MySQL database created on the AWS-Aurora service. The MySQL version reported when connecting is 5.6.10. We have an ...
1
vote
1answer
40 views

InnoDB Buffer pool not showing as much I configured in MariaDB 10.5

I'm using MariaDB 10.5 on Centos7 with 4G RAM and 2 CPU. Here's my.cnf configuration [mysqld] log-error=/var/lib/mysql/mysql-3.kannel.com.err max_allowed_packet=1G event_scheduler = ON ...
1
vote
1answer
10 views

Adjusting the my.cnf file for DB servers after an instance resize

I am trying to optimize the DB server of one of our production environments (MySQL DB, centos7), and I've noticed that there is a very high memory consumption of the DB. After further investigation I ...
1
vote
2answers
25 views

MySQL - GROUP BY not working with multiple CASE WHEN statements

I am working on a database with Kickstarter database, and I'm trying to: Define short, medium-lengthed, and long campaigns See how much each length of campaign raise Convert different currencies into ...
1
vote
0answers
19 views

Optimizing UNION

In MySQL 5.7 is it possible to optimize a query that coalesces values from three different tables? In pseudo-code something like: SELECT id FROM table_1 WHERE name = ? ELSE SELECT id FROM ...
1
vote
1answer
18 views

Query To Show YTD Total Summing Each Month Prior To Current

I need a query to give a YTD total that should sum each month before it. For example, January YTD would equal January Total, February YTD would equal January Total + February Total, March YTD would ...
1
vote
2answers
37 views

Bridging Table Properties

I have a question on bridging tables: if I have a bridging table linking two tables (standard) with the only values in the bridging table being the Primary Keys for both tables it is linking, how ...
-3
votes
1answer
17 views

Problem regarding SQL [closed]

im a comopletely newb in SQL and dont see what im doing wrong. tried to execute the following script in order to install the db: <?php // Parametros a configurar para la conexion de la base de ...
0
votes
2answers
74 views

MySQL taking up 400% CPU

LEMP stack on DigitalOcean (48GB ram, 960GB SSD, 12 vCPU) with WordPress on it. Added a new theme and mysql went through the roof. 400% CPU usage taken up by MySQL itself! Looked at slow-log to figure ...
0
votes
3answers
33 views

mySQL foreign key and primary key

Consider table A with columns id (primary key), name and table B with columns id, a_id (foreign key linked with table A id column), address. What will be the sequence of columns if the query is: ...
1
vote
0answers
15 views

subquery returns more tha one row

I want to update data of a table where data-name matches with another table and also one data are common with many items. For example, I have a cycle in which tyre is a part and quantity of tyre is ...
0
votes
0answers
11 views

How much time would it take for synchronizing between master and slave in Mysql on AWS RDS?

I implemented Master Slave for RDS Mysql database. The sync time for 1 GB was very less. Can I predict time for 20GB to synchronize? Master DB Slave DB Can I also predict the time and load on the ...