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

Does reordering table rows reduce the time for subsequent ordering with the ORDER BY clause?

Is there any run-time advantage gained by changing the order of a table's rows to match the expected ordering of the ORDER BY that is in a slow select? (assuming unique alphanumeric index and no auto-...
0
votes
1answer
19 views

galera cluster with mariadb 10.3.13 is not replicating on RHEL 8.1

I'm installing a galera cluster using mariadb 10.3.13 with two nodes in redhat 8.1 server. I enabled ports 3306, 4567, 4568 y 4444 ports. selinux is disabled. firewall disabled. The first node is ...
0
votes
1answer
27 views

Complex user interests query

I'm trying to create a query that will return a list of results based on a series of conditions. For example - a list of movies based on interests/preferences. Example Source Data: +--------+--------...
1
vote
1answer
30 views

MySQL cycling between periods of high I/O and high lock contention

Starting about 2 days ago, my CloudSQL DB started having this periodic cycle of high latency. The time between these latency bursts vary, from 30 minutes to 50 minutes. I finally set up a PMM ...
0
votes
3answers
46 views

How to create a table to a specific user

First good evening every body. assume that you are logged in as a super user and you want to create a user. CREATE USER 'user1'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*...
-2
votes
0answers
45 views

Why does using table alias in JOIN clause causes syntax error, when it didn't do so before?

How would you explain peculiarity where SQL used to work before and now does not: SELECT * FROM employee.user staff LEFT JOIN support.groups groups ON staff.group_id = groups.group_id Gives ...
0
votes
3answers
44 views

adding cluster index other than primary key default cluster index

create table t1 (id int primary key auto_increment, name varchar(20)) In the above table id is primary key and by default cluster index is added to primary key column. My question is, can we add ...
0
votes
0answers
37 views

MySQL Group Replication - Peer address is not valid

I have been trying to set up an MySQL Innodb Cluster, initially with two databases, both MySQL 8.0.19. But I keep getting the same errors. On the first one (username@1.2.3.4:3306), I have created ...
0
votes
1answer
49 views

Prometheus High Memory and CPU Usage in PMM

We are running PMM v1.17.0 and prometheus is causing huge cpu and mem usage (200% CPU and 100% RAM), and pmm went down because of this. We are running PMM on a VM with 2vCPUs and 7.5G RAM, and are ...
0
votes
1answer
13 views

Archiving of old data combined with replication to data warehouse

I have a setup that I'm sure is not unheard of (this is MySQL 5.5, but upgrading): I have a fast growing OLTP database, that I am replicating to an OLAP database. I want to start archiving the ...
0
votes
0answers
24 views

Improve MySQL query efficiency for first row in a group

I've written the query below in MySQL to get the top 10 top landing pages across all browser sessions. Reading other similar posts about how to access the first row in a group, it seemed like the ...
0
votes
1answer
27 views

Speedup restore of 160 GB SQL in 24 core + Optane NVME system

i set up a new sql-server, ubuntu 18.4 (MYSQL 5.7), 24 core (48 threads), 256G RAM, System is SATA Raid1 XFS SSD /var/lib/ is mounted on Optane SSD XFS. In restore i can see that the server has unter ...
0
votes
3answers
17 views

Overwriting MySQL database to only store 1 month of data

We are logging data on hardware with only small memory (short on disk storage) only 4GB. We only require the data to be stored for 1 month and then be over written in a way that it overrides older ...
0
votes
2answers
60 views

Mysql Performance issue with millions rows and group by

I have three table with more than 1 millions of rows and one of my query take more than 30 seconds with a date range of 3 months. I have tried multiples index and I think I have respect index orders. ...
0
votes
1answer
27 views

How to store file paths, which collation to use?

I have a table that stores Windows system paths. I am scratching my head about how to store them and in particular which collation to use. The paths are unicode strings. They are case insensitive, ...
0
votes
1answer
29 views

Query with left join not using index

The below query is taking about 1 second to run SELECT `taxi_driver_mapping`.*, concat("dpk00", taxi_driver_mapping.mapping_payment_plan_id) AS package_text, `people`.*, ...
1
vote
0answers
26 views

MySQL 5.7 - JSON - Searching array of KV objects

Version: MySQL 5.7.25, GCP CloudSQL. I'm looking to store Twilio SMS delivery status updates in MySQL. I wanted to try using JSON for this to store one record per SMS, but then have a JSON column to ...
0
votes
1answer
51 views

MySQL 8.016 master with MariaDB 10.2 slave on AWS RDS, Character set '#255' is not a compiled character set

I have a MySQL 8.016 master and a MariaDB 10.2 slave, both running on AWS RDS. This was working perfectly until today, when I figured I wanted to do chain replication instead, like this MySQL 8.016 ->...
-3
votes
1answer
35 views

problem with mysql

mysql or mysql_secure_installation ERROR 1045 (28000): Access denied for user 'pc123'@'localhost' (using password: NO) mysqld [Warning] [MY-010091] [Server] Can't create test file /var/lib/mysql/...
0
votes
1answer
21 views

Migrate MySQL to another partition in same server

I have a instance of MySQL in a local server. To improve the performance, I put a SSD just for MySQL. Now I need migrate the MySQL instance, with my databse (a data has 25GB) for the SSD. What the ...
0
votes
2answers
38 views

linux mysql case insensitive lower_case_table_names=1 make server cannot be started

I use mysql in linux ubuntu an I want to set lower_case_table_names = 1 in /etc/mysql/my.cnf file. But after I inserted the variable, the mysql server cannot be started. o Is there any way to set ...
-1
votes
1answer
21 views

Update table column only if the ids don't exist in another table

I have two tables that are in play here. A users table and a users_roles table. I need to update the users status column to 0 for almost all users except for a few, we can target these users by uid ...
0
votes
1answer
26 views

How to get goup concat of count value using sub query

I'm trying to get group concat of count value here table below I want this structure: | y | m | monthName | RowCount | model | | 2019 | 2,6,8,9,11 | ...
0
votes
1answer
15 views

Status show SSL is in use even though I did not set up certificate on the client side?

I want to set up SSL connections to a distant MariaDB server. I followed the instructions here. I generated the certificate for the server, restarted it, and got this: +---------------------+---------...
0
votes
2answers
39 views

MySql LOAD DATA with subquery performance issue

I'm migrating data into a MySql (5.7.26) database (32GB ram), running as a managed service on AWS. While importing the data, I need to map one of the columns of the CSV being imported to another value ...
0
votes
0answers
14 views

My PHP file wont insert HTML data into a MySql database table [migrated]

In Visual Studio Code, i am working on creating a website. An HTML page i have has a form that gets the user data... <form action="Data.php" style="position: fixed; top: 60px; left: 5px;" method="...
0
votes
1answer
29 views

Replace part of a string in MYSQL, when only the substring's starting characters, ending characters & a unique string in the middle is known?

I'm trying to find a way to replace part of a string in a database without knowing the exact substring content other than how it starts, ends and has a specific ID string somewhere in between that. ...
0
votes
1answer
23 views

Do I need to set “ConnectionReset” parameter in the connection string when using isolation levels occasionally?

I have a MySql 5.6.44, and an dotnet application using Mysql.Data 8.0.12. The application code has not specified any isolation level till now because the default one was enough. Suddenly we need to ...
0
votes
1answer
21 views

MySQL query to get same column data in two rows

I have a database and I am facing issue to getting the data in the format below. Table is as below +----+---------+----------+ | ID | ign | time | +----+---------+----------+ | 1 | NULL |...
2
votes
3answers
51 views

Table Creation Order

I have designed my database model with entities and they have relations along with normalization. Now I want to implement my design in my database engine. Before implementing I am puzzled with the ...
0
votes
1answer
29 views

Need help with indexes on 2 slow wordpress query

Here are my my 2 slow query i want to improve. SELECT object_id, term_taxonomy_id FROM wp_term_relationships INNER JOIN wp_posts ON object_id = ID WHERE term_taxonomy_id IN (525627,516360,525519,...
0
votes
1answer
16 views

Innodb cluster options missing

I have successfully installed and run an INNODB Cluster on mysql 8.0.19 However I cannot set options for the cluster when we run cluster=dba.getCluster(); cluster.options(); We get the following ...
0
votes
1answer
36 views

MySQL database cluster vs. replication setup for large crawler indexes

What is the most efficient way to set up a MySQL cluster or replication in an application that has a crawler/indexing engine with thousands of read/writes and then a separate front-end for public ...
0
votes
1answer
38 views

How can I improve this slow query in my wordpress site?

SELECT object_id, term_taxonomy_id FROM wp_term_relationships INNER JOIN wp_posts ON object_id = ID WHERE term_taxonomy_id IN (525627,516360,525519,535782,517555,525186,517572,549564,1,517754,541497,...
-1
votes
2answers
36 views

Why mysqld costing so much memory?

I am a beginner of Linux. The memory of the server computer is 2GB. I just installed a MySQL-server:latest version in docker and run it in a container. Now the MySQL has not any database tables, ...
0
votes
1answer
25 views

How to dedicate server to MySQL for max performance?

I am using MariaDB 10.5, I see this in MySQLTuner report Dedicate this server to your database for highest performance. How do I do this? My server is on Centos 8.
0
votes
1answer
50 views

Question marks instead Hebrew characters after mysql dump and import

I tried to migrate my AWS RDS instance from Aurora to MySQL. I created a dump file from the Aurora and imported it into the MySQL instance. Both instances have the same characters set. mysql> ...
1
vote
1answer
35 views

MySQL query of Hotel database [closed]

How to List the room numbers that are ‘currently’ occupied at Holiday Inn Express. I wrote the MySQL query ,but I don't get any output. Can anyone help to solve this? SELECT r.rm_number FROM ...
0
votes
1answer
37 views

How optimization select in Msql 8? [closed]

i have a problem with optimization select on Mysql ver 8.0.17 I'd like to optimize a query that takes 60 seconds to run the first time, but when I run it the second time it takes less than a second (...
0
votes
1answer
45 views

Left outer join ruins query in MySQL

I have two tables. One of them is big, long with more than 500 columns (most of those are tinyint) and 360000 rows. It contains info about photos. Second one is small with just three int columns (...
1
vote
0answers
21 views

MariaDB REGEXP_REPLACE Invalid utf8 byte sequence

I'm working with a fairly old (15 years) old database. It started as a MySQL3, was upgraded several times, the frontends had problems with proper encoding, the whole database was converted to utf8mb4 (...
1
vote
0answers
26 views

CMS - Database Schema Review / Questions [closed]

Database Schema Review for CMS I'm in the midst of creating a Laravel application that will (hopefully) rival WordPress. The thought process is quite similar. Developers are able to create resources (...
0
votes
1answer
22 views

calling mysql procedure return empty result

when i call procedure it gives me empty result call hussian('prstate','table_1','column1,column2,column3',1) it run but gives empty result, please help me... DELIMITER // CREATE PROCEDURE ...
0
votes
0answers
22 views

ER Diagram: A relation is dependent on another relation

Take a look at the ER diagram here: ERD. This is a fragment of the whole ERD. I've included only the part relevant to this question. This somewhat hypothetical database has 4 entities. Course, ...
-1
votes
1answer
27 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 ...
0
votes
2answers
23 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
1answer
10 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 ...
0
votes
2answers
33 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
1answer
27 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
23 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 ...