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.
19,141
questions
0
votes
1answer
112 views
What exactly happens when optimizer_prune_level = 1?
This documentation link states that optimizer_prune_level
Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space.
Can ...
0
votes
1answer
13 views
MySQL Query Fetch Time increases when LEFT JOIN with big table
I am fetching the same amount of data with 2 different queries. However, one of them have a fetching time of ~x130 compared to the other. Being the only difference between the two queries a LEFT JOIN ...
0
votes
0answers
8 views
About the problem of changing the data directory related to mysql
I am trying to run the sysbench by mounting it from the internal SSD of the computer to an external hard HDD. However, in order to modify the Vi code, I pause mysql for a while and then try to restart ...
0
votes
1answer
12 views
Why mysql use only one index for table?
Index merge is used very seldom and often is not efficient.
But search engines like Elastic, Sphinx can use few indexes efficiently.
0
votes
2answers
131 views
MySQL 5.6.26 high concurrency dead lock
Table structure:
CREATE TABLE `extend_0` (
`id` bigint(20) NOT NULL,
`code` varchar(30) NOT NULL,
`data_key` varchar(50) NOT NULL,
`data_value` varchar(200) NOT NULL,
`create_time` timestamp NOT ...
6
votes
1answer
1k views
Optimized MySQL “optional” FULLTEXT search on multiple tables
I need to select data with optional columns in where clause based on client input.
my query is like this:
SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt
FROM a
LEFT JOIN b ON a.b_id=b.id
...
0
votes
0answers
5 views
Manual Binary logging in Amazon's Aurora
How would I enable binary logging in RDS? I get the following error:
Binary logging is disabled for MySQL server [1020418] (mysql_endpoint_capture.c:352)
What parameters do I need to change in ...
-1
votes
0answers
7 views
How to determine the size of data being used by a user across tables in a mysql database
We are running a multi-tenant database and would like to keep track of how much data is being used by a tenant (tenant-id) and keep track when a particular tenant is updating their data.
objective is ...
-1
votes
1answer
23 views
JSON vs Record/Struct type in a DB
It seems like some databases have implement a Record/Struct type and others have implemented a Json/Jsonb type to handle nested data structures. A few examples being:
BigQuery Struct
CockroachDB ...
0
votes
1answer
384 views
Mysql not writing to binlog
Mysql is not writing events to binlog, I have tried change the binlog configurations, but can't solve.
Conf:
server_id = 2
log-bin = master
binlog_format = row
replicate-do-db = our_db
log-slave-...
0
votes
2answers
26 views
How to select the rows affected by an update
When performing an update query (the following is just an example; any update query could be used) such as:
update t1
inner join t2 on t1.id=t2.id
set t1.name="foo" where t2.name="bar&...
0
votes
1answer
28 views
Storage space requirements for a varchar(40) column to store 'abcd' with charset 'ucs2' and 'utf8mb4'?
I understand that if the length is > 255 it will require 2 bytes to store the length prefix. I just need to verify my assumptions. Please tell me the length prefix and the length of string('abcd') ...
0
votes
0answers
11 views
What is the best database design for storing survey form with different types of questions and answer formats and branching is possible?
I would like to store the format of the survey form which can branch into different question based on
Questions can be video, audio, text and answer can be text, multiple choice, video, audio, ...
2
votes
1answer
1k views
Configuring slave from EC2 master to Amazon RDS slave
I am configuring replication from EC2 master to Amazon RDS instance.
After starting the slave, I don't see any errors but I see slave_IO:
_thread is connecting.
Master version:5.6.23
Slave ...
0
votes
1answer
10 views
Replicate_Do_DB and Replicate_Wild_Do_Table - Replication does not work
I have been tasked to set replication up on a slave host. The master database is a "data store" where tables are dropped, recreated, and reloaded on a daily basis.
My initial setup of the ...
0
votes
1answer
27 views
ERROR 1064 (42000) You have an error in your SQL syntax during import
New to MySql.
I have an export file from server version 5.6.34. I am importing this on a server with the same version. I get an error like:
ERROR 1064 (42000) at line 4343: You have an error in your ...
1
vote
1answer
167 views
MYSQL - How to optimize VIEW performance?
Total profiles - 100000
Each profile is connected to one or more platform
Each profile have 50 Tags
Each profile have 4 themes
I have a user view (total users around 100000) where I am joining all ...
2
votes
2answers
109 views
mysql dump best practices for development
I have an application that has a large database that includes, among many other tables, a table called content and a table called users.
I would like to set up a development environment for this ...
0
votes
1answer
44 views
Why is turning on Event Scheduler Maxing my CPU?
I have search the web for the last week trying to figure this out.
I have exported my tables and events, re-installed the lastest MySQL and imported.
I have tried checking individual procedures.
My ...
2
votes
1answer
246 views
Get stopwords of a InnoDB fulltext index
I can set stopwords for a MySQL fulltext index with SET innodb_ft_user_stopword_table ... before creating the fulltext index.
But how can I figure out the existing stopwords of before created ...
0
votes
1answer
18 views
Get top 5 students for each class in many-to-many relationship
So I'm doing this in MySQL.
I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their ...
4
votes
3answers
8k views
MySQL Workbench copies Schema, but not data, from SQL Server 2008
I need to copy/migrate a database from MSSQL (SQL Server 2008) into MySQL. I am using the MySQL Workbench kit, and work through the process of the Migration. Each respective DB user has full access, ...
2
votes
1answer
188 views
Sync mysql database between 2 geo locations (datacenters)
We have a database cluster built on Percona MySQL. We are going to extend our redundancy with another database cluster with a new data center located in another GEO location. So that in case of ...
0
votes
1answer
179 views
How to create a SELECT statement involving a subtype
Okay, so all I'm trying to do here is "list" the managers who supervised the employee that charged a customer $100 for a rental on November 1st, 1998.
My issue is that I don't know how to select the ...
1
vote
1answer
12 views
Get max value when joining 3 tables in SQL
This is based on a MySQL database.
I'm trying to join 3 tables together and get a MAX value from the third one. I'm trying to get the po.id, for each part.id, that has the latest po.date. I cannot ...
0
votes
1answer
210 views
phpMyAdmin how to run an output query? run a batch?
In phpMyAdmin the code below just outputs a list of sql statements for each table:
SET @DATABASE_NAME = 'my_db';
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM ...
0
votes
1answer
424 views
How to maintain customer addresses for Orders?
The database I am designing is part of a project I am working on. I have come up with 3 simple "business rules":
Customers must either be Individuals or Businesses (a business belongs to a certain ...
0
votes
0answers
15 views
Best way to restore a MySQL 5.6 database from just its folder and file data? [duplicate]
I had a 5.6 MySQL Server instance that was hosted on a computer that suffered a very bad lightning related power outage. For whatever reason, MySQL 5.6 Server does not want to start/stay running now. ...
0
votes
0answers
9 views
Quick Backup/Restore of Aurora Database
We're getting slow performance (hours for a couple GB database) when trying to do a backup/restore of an aurora database. We essentially want to clone the database so we've got two side-by-side copies ...
2
votes
1answer
25 views
Deleting all records on a table except the latest 10 for each user
I have an activities table that has a foreign key reference to a users table. A user can have many activities, and I have a job that will populate their activities on a daily basis. After that job ...
-1
votes
0answers
11 views
How can I get separate records when group_concat is applied
I have this huge query which used to get the details of orders rang and convert the price according to the currency.
SELECT *,
YEAR(o.date_added) AS year,
QUARTER(o.date_added) AS ...
0
votes
1answer
24 views
Strange max(date) return in reader vs writer aurora mysql
Something very strange happening here which I can't point out. Using AWS Aurora, select max(date) in the writer gives me one date, in the reader it returns strange date and warnings.. Column is ...
0
votes
0answers
10 views
Clear performance schema on read-only MySQL instance
I am using AWS Aurora MySQL with Writer and reader instances in replication. Both instance are used by two separate applications.
I want to clear the performance schema on the read replica server but ...
1
vote
1answer
19 views
Any way to update VARBINARY value on certain position?
for example, if i have string '012345678', and i need to update position number 4 into another byte/character 'x' for example:
INSERT INTO foo(pk,pos) VALUES('abcdefghi',5)
ON DUPLICATE KEY UPDATE
...
0
votes
1answer
104 views
MySQLRouter on Windows 10 Unable to connect to the metadata server
I have set up a set of three MySQL 8.0.17 databases using group replication. They are all on my Windows 10 PC, using ports 3306,3307,3308.
I am now trying to set up mysqlrouter. I have installed it ...
0
votes
1answer
94 views
Moved database (percona mysql) to larger disk, now everything is missing (though the files exist)
I dont know anything about databases or MySQL beyond the absolute basics so lets start with that. I moved the /var/lib/mysql from the / to a mounted volume which I remounted as /var/lib/mysql after ...
2
votes
3answers
964 views
Prevent replication of ALTER commands
I am using MariaDB 10.0 multi-source replication for a specific use case.
For security reasons, I would like to prevent ALTER commands on master to replicate (such as CREATE, ALTER, DROP...) whatever ...
-1
votes
0answers
22 views
The number of daily active users per device. With no dates given [closed]
SELECT DATE(activities.activity_timestamp) AS[CRT DAY],
Count(DISTINCT(activities.activity_user_ID), devices.device_name
FROM ((devices INNER JOIN activities ON device_ID=activity_device_ID)
INNER ...
0
votes
1answer
162 views
MySQL backup of incremental changesets
With a MySQL database, is there a way of logging and/or backing up incremental changesets?
We have a number of large databases (100-200 GB), for which we take daily backups using mysqldump. These ...
0
votes
2answers
6k views
Alternative to Temporary Table with Create View - MySQL
I have a view which uses many tables, and statements and functions. It is very slow, the main reason being that I only have read-only access to the main tables which do not have the appropriate ...
3
votes
1answer
111 views
What to do after indexing and partitioning to improve performance
I have a large table with about 100 million rows.
I have done indexing and partitioning in the table, but still queries sometimes take >100 or even >200 seconds to run.
So I was thinking, what ...
0
votes
1answer
645 views
Access denied for user 'me'@'localhost' (using password: NO)
I am am trying to learn mysql 101. Doing so, I broke my ability to login as root.
At first I could simply connect to mysql by running
sudo mysql
In an attempt to alleviate a problem, I ...
0
votes
4answers
132 views
MySQL optimizer won't use my index
With the following query:
SELECT
*
FROM
geoname
LEFT JOIN alternatename
ON geoname.geonameid = alternatename.geonameid
AND alternatename.isPreferredName = 1
AND alternatename....
1
vote
1answer
78 views
MySQL - Log data with Inner Join 12 Tables or Create View?
I'm using MySQL (phpmyadmin) with engine InnoDB for my e-commerce website.
Now, I need to get the data as Log but It's need to joining more than 12 tables for logging all data we need to see, only ...
-1
votes
0answers
13 views
Java SQL Query - value pair
I wish to select data where two columns are in a set of pairs.
I have a MySQL query that works:
SELECT t.whatever
FROM t
JOIN
( VALUES (val1a, val2a), (val1b, val2b), ...) AS x (col1, col2)
...
1
vote
1answer
164 views
Mysql full text search with other indexed data
After getting some mysql optimization advice in this question. Was able to get the performance to the acceptable levels (when performing lookups using EventType and event_log_domain_id combo). To ...
0
votes
1answer
356 views
MariaDB LOAD DATA INFO can't import single column file to single column table?
I am trying to import the 10,000 common password list into a table to ban those passwords from being used in a given system.
The file is a single column TXT file. Trying to LOAD DATA INFILE that into ...
1
vote
2answers
147 views
Mysql group by multiple combinations
I'm building a chat app in which two people will comunicate on enquiry, I want to fetch conversation between two users on enquiry, table structure is like below:
Table: enquiries
id, name, product, ...
2
votes
1answer
147 views
Cross-Continent SQL High-Latency Data Synchronization
How would one go about synchronizing a MySQL database cross-continent, while keeping the distributed application using the DB running fast (<0.2s page loads) ?
My database contains about 2.5GB of ...
2
votes
1answer
120 views
How to setup two mysql servers with group replication to work individually, when no network
I have two mysql servers in two different places. Both servers must be able to read and write. All tables are designed to based on unique id (UUID) primary key. I want to sync these two databases live ...
