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
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 ...

1
2 3 4 5
383