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
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
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
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
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, ...
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
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
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
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 ...
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 ...
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
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
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
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 ...
-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
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 ...
-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 ...
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
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) ...
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
1answer
19 views

How can I enforce that my composite key columns do not equal one another in MySQL?

I have a pivot table in which there is a composite key made up of two columns that reference the same table. The columns are requester_id and addressee_id and both of them refer to the id field on a ...
0
votes
1answer
23 views

Can I connect mysql client to a mariadb server?

I am new to databases, so I might have done something very simple wrong. I cannot connect to my Mariadb server via my mysql client. Is this possible at all? cmd> mysql -h 192.168.0.17 -u root -P ...
-2
votes
0answers
13 views

Calculating percentiles of a FROM command [closed]

I have a query which generates a table of trials and values from a database (example pic below) SELECT * FROM [ReMetrica].[ReMetrica_Result].[GetTrialValuesAsDouble] ('BD5E92F3-3A3B-43B4-BC22-...
0
votes
1answer
21 views

Transfer rows into a new table, forcing to generate unique primary keys till its insertable

Lets say we have the following scenario... +-----------------+-------------+---------------+ | Table | PK | Additional | +-----------------+-------------+---------------+ | ...
-3
votes
0answers
43 views

My SQL assignment having issues with some queries [closed]

The number of users, for whom the last activity was done with device_name = “phone”, but who have also at least one additional activity with another device. Select d.device_name, COUNT(DISTINCT a....
0
votes
1answer
20 views

MySQL write performance after truncate & optimize

Once a year we truncate a table of several GBs and millions of rows and then run optimize in order to shrink the size of the corresponding ibd file on the file system. However, as this is write ...
0
votes
0answers
25 views

Mysql randomly restarts with different args?

I'm facing a rather strange problem and I'm not sure how to make sense of it. It looks like the mysqld process is being started with different args by something other than systemd after the mysql ...
2
votes
1answer
17 views

Performing SQL string manipulations on a single column

I recently came across a problem at an interview which I wasn't able to answer. The ask was to write some SQL fetch the first three number(s) after crossing two 2 decimals. For example: Input table: ...
1
vote
1answer
24 views

My Mariadb fails and shuts down after running with a timeout

My Mariadb version is $ mysql -V mysql Ver 15.1 Distrib 10.3.23-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 My machine is a ubuntu server: $ uname -a Linux mypc 5.4.0-42-generic #46-...
1
vote
1answer
22 views

How to write concat query for multiple 'select into' statement in mysql stored procedures?

I am trying to assign values to variables in mysql stored procedures. Here is my code declare count int; declare ParamOrgId int; declare isPremium int; SET @sql = CONCAT(' select count(id) , orgid , (...
0
votes
0answers
15 views

How to design MySql database design for Jira-like application?

I am new to software development and developing one application for learning new skills, I am trying to implement some of the basic features of Jira software. I am stuck at database design, I have ...
0
votes
0answers
31 views

ERROR 3911 (HY000) at line 24: Cannot update GTID_PURGED with the Group Replication plugin running

heyy all, we want to move our current mysql 5.6 server to mysql-8 innodb cluster. for this, we are setting up a replication like 5.6 -> temporary 5.7 -> mysql-8 cluster. We already have the ...
0
votes
1answer
26 views

How to select rows after a certain row using auto increment temporary columns

I have a table that has ID varchar(255) as PK. I have managed to create an auto increment temporary column rowNumber to use with a SELECT, as follows: SELECT (@cnt := @cnt + 1) AS rowNumber, ID ...
0
votes
1answer
24 views

MySQL seek-method / keyset pagination?

I am trying to change the pagination from OFFSET, LIMIT to keyset pagination. My problem is, the resultset consists of several groups (plz) ordered by uid. I added a fiddle, see http://sqlfiddle.com/#!...
-5
votes
0answers
32 views

convert sql query into laravel (urgently) [closed]

function show($status,$main_table,$table) { $status=''; $check=''; $current_date=date('Y-m-d'); $provision=''; if($main_table=='companies') ...
0
votes
1answer
24 views

Best way to optimize an aggregated issues over time query?

I have a query that creates an aggregated issues over time report that includes back-filling data from entities with missing dates. I've been using the following query for a few years but it is ...
0
votes
1answer
23 views

Large MySQL import stuck at “Waiting for table flush”

Two weeks ago (!) I started a large import (dump sql file is 540GB). Now the import is stuck at this state: "show processlist" shows: Command = "Field List", State="Waiting ...
-1
votes
2answers
35 views

Avoid writing duplicate query

Is there any way to collect somehow or write in another way query's which is repeted couple of times. I have a lot of query which are repeted a couple of times, and I dont know to to avoid this issue....
0
votes
0answers
14 views

How to synchronize a local database with an external database continuously without replication

I created a database on our internal network (localDB), it receives data via CSV (LOAD DATA INFILE) coming from our ERP, it is continuously updated I have an external database (remoteDB) with which ...
1
vote
1answer
28 views

MySQL CASE WHEN is not working as expected with user-defined variables?

i need help please with the following query. I am pulling zip codes (plz) along with user id (uid) from DB and ORDER them by zip (plz) and uid. Also I use a user-defined variable @prev to check if ...
0
votes
0answers
28 views

What is mysql version?

I have installed msyql and mariadb. When checking for the versions, everything I see is the version of mariadb. How can I know the version of mysql itself? mysql -V mysql Ver 15.1 Distrib 10.3.23-...
0
votes
0answers
14 views

Convert MySQL table date/datetime column to int (unix_timestamp) with existing records

I have tables with existing data, that use date? and datetime` fields, that I want to convert to unix timestamp values (ints). For some of the tables, creating a view that calls unix_timestamp() on ...
1
vote
1answer
39 views

Why does MySQL think I'm using SRID 1?

I can run the query on my local machine no problem (Server version: 10.4.6-MariaDB), but when I run it on my remote one (Server version: 8.0.17 MySQL) it poops out this error message: ERROR 3548 (...
1
vote
2answers
32 views

What is best for a primary key? Bigint of 20 numbers or varchar / varbinary of 15 characters? [duplicate]

I have already seen some posts making comparisons of varchar with numeric fields, but most only compare with equal values ​​and do not talk about performance in primary keys. I have a user ...
-1
votes
1answer
17 views

getting last entry with ORDER BY with inner join not working [closed]

I just realized that this statement is no longer getting the last entry from attributes. I was working before I believe I had added the inner join. But adding this inner join I no longer get the ...
0
votes
0answers
7 views

System for managing matches and teams

I would like to create a system for managing matches and teams in an amateur championship. I had thought about this type of relationship. By default ONEtoONE has been set. Can anyone tell me if the ...
1
vote
0answers
7 views

~/.my.cnf settings for a custom .mysql_history file

In my development environment I have a lot of databases, and I run the same query a lot of times. Long time ago I saw a post how to have a separated mysql_history per database. That allowed me to ...
0
votes
1answer
13 views

MySQL: how to use audit_log plugin to audit user modification

I am trying to audit any user modification action (create user/alter user/drop user/rename user) on a MySQL Enterprise server 5.7.24. I have installed the audit_log plugin following the document, and ...

1
2 3 4 5
383