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
1
vote
0answers
7 views

mysql data node not connecting to node manager on AWS EC2

I am trying to setup mySql manager + data node + sql server (Server A) and another data node cluster on (Server B). Both running on Ubuntu 16.04 amd_64 Server A /etc/hosts also on Server B Both ...
0
votes
1answer
11 views

Merge multiple rows in table setting one column based on value in rows

I have some data that looks something like this: key B 1 true 1 false 2 false 2 false And I want to roll it up so that if column B has two false values then the resulting value for B is ...
1
vote
0answers
8 views

ALTER TABLE DROP COLUMN - server status 'Stopped'

I am in the process of removing 4 columns from my (MySQL/innodb) table, simple code, ALTER TABLE database_name.table_name DROP COLUMN A, DROP COLUMN B, DROP COLUMN C, DROP COLUMN D; It has been ...
0
votes
0answers
11 views

MySQL - too large temporary table on disk

I have two servers with identical MySQL versions (5.5.50, pretty old), identical my.cnf, identical SHOW VARIABLES and three identical tables with identical indexes and different data. And I have a ...
0
votes
0answers
10 views

Getting Connection Error on MySQL5.1 to MySQL8.0 ROW Based One Way Replication

Trying to replicate MySQL5.1 to MySQL8.0, when running starting the slave on MySQL8.0, I get [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'rep_user@XXXXXXXX.YYY:...
0
votes
1answer
24 views

Import a large MySQL dump 68GB on RDS instance

I tried to import a large MySQL dump.sql (68GB) on RDS AWS instance MySQL 8.0 (db.t3.2xlarge: 32GB RAM). The import takes more than six hours: More than 500 tables I have 2 large tables: the first ...
-3
votes
0answers
13 views

How to read millions of records effectively with more joins? [on hold]

Im developing an enterprise application using node js as backend and mysql for database for one of the telecommunication company. I'm currently working in rewards module like grabbing and redeem ...
0
votes
1answer
20 views

Select count date from multiple where conditions for each day

I have lot of coupons. I would like to learn for each day how many coupons from each campaign have been received by users. But I cannot write something like assigned_date for each select row SELECT ...
1
vote
2answers
50 views

SELECT LATEST ENTRY ON GROUP BY

This query doesn't return the "latest" entry, rather it returns the "older" rows: SELECT MAX(ID), ID, NEW_BALANCE, DATE, NAME, ACC_NO FROM ( SELECT ID, NEW_BALANCE, DATE, NAME, ACC_NO FROM ...
-5
votes
0answers
18 views

Please tell me query to solve the following question? [on hold]

Display the doughnut “Name”, “Cost” and “Sale Cost”. A sale cost is a calculated column of one doughnut at 30% reduction.
0
votes
2answers
17 views

Group by specific value and return results in generated column

I am attempting to figure out how I can pull this off, but I have no clue how to do it. I have a table (below) that has object_id's, types and values. The values are always unique and the type is ...
0
votes
0answers
14 views

Is this the correct/best way to create a custom column in mysql?

I needed to select certain records from a selection with a custom column that have another column's modified value as its own value. The following query just works for my needs. But I wanted to know ...
0
votes
1answer
19 views

How to select from two tables where the rows may be or may not be equal and group it if they are equal

I have two mysql tables of images ojm_pages_articles_photos, ojm_images_optimized but I want to select rows that are common to them and also rows that are NOT common to them. In case common rows are ...
0
votes
1answer
21 views

Will creating reports on R using MySQL Database update my database on MySQL?

I use MySQL to store my data. To analyze the data I want to be able to generate monthly reports on R. So far I was able to connect to MySQL using dbConnect but I want to make sure that none of the ...
0
votes
1answer
16 views

Change value lower_case_table_names on a running docker container

I have a running docker image with a database where, for some reason, the columns are in camel-case. I have been searching how to change the value of lower_case_table_names to 1. How can I make this ...
0
votes
1answer
40 views

How to optimally structure a table with sparse data

I am working on a project to create a series of tables that are easier to use and more intuitive for fairly naive SQL users. Because of the structure of our data, we are running into a trade-off ...
0
votes
1answer
19 views

MySQL Sleep Process / Task

How much "sleep process" is considered bad for a MySQL DB instance? I'm wondering if the high CPU load usage I see could be caused by the sleep processes. The DB is an "db.r3.xlarge" instance in AWS....
2
votes
0answers
23 views

Why InnoDB storage engine not loading csv files concurrently?

While loading different csv files to different tables of the same database using innodb storage engine(even tested on aria), csv files seems not to be loaded concurrently. Because, a single csv ...
-1
votes
2answers
23 views

Select all duplicate rows except for one record in MySQL

I need to select the duplicate records in this table except one. Example Data +-------+-------+------+ | nid | vid | uid | +-------+-------+------- | 701 | 209 | 11 | | 701 | 210 | ...
0
votes
0answers
13 views

phpMyAdmin — how do I figure out which DB these connections and processes belong to?

I've got about 8 databases on one server, mostly corresponding to WordPress sites. I'm using shared hosting that does not give us superuser permissions. When I log into phpMyAdmin (version 3.5.2) and ...
0
votes
2answers
53 views

MySQL - Column value depends on values from two different tables

I have a MySQL database that contains these tables (* denotes primary key): TableA: ---------------------------------------------- | *id | time | price | special_price | ---------------------...
-1
votes
0answers
10 views

MySQL 8.0: Escape Character in my.cnf - Database with # in the Name

I have a database named #text#.xx which I need to ignore in replication. According to mysql: The rules for escape sequences in option files differ slightly from the rules for escape sequences in ...
1
vote
1answer
25 views

Filtering on two separate colums

The schema I'm using MySQL Consider a table with columns id uuid type char is_active Boolean Here are some facts about the data in this table type is either 'A' or 'B' There will be a maximum of 2 ...
0
votes
0answers
30 views

“Overdesigning” tables [on hold]

I am working with a Django application on a MySQL database. My colleagues are using some kind of "overdesign", let say we have the typical product table: Table PRODUCTS: - ID - code - category - ...
-3
votes
0answers
12 views

Steps to upgrade MySql 5.5,5.6 to 5.7 and rollback [on hold]

Please help with the upgrade and rollback plans for MySql DB versions: 5.5.40 to 5.7.22 5.6.35 to 5.7.22 5.7.13 to 5.7.22
0
votes
1answer
13 views

Which Is the Used Size Unit In DiskSpace Column at Storage Table In Zoneminder Database

Trying here, without success change the storage location of Zoneminder events and images, but having trouble with denied access of www-data user, despite have already changed both Zoneminder config ...
0
votes
0answers
13 views

how to optimize mysql configuration for better performance? [on hold]

I have a docker 8.0.17 mysql instance run on 125GiB System memory AMD Ryzen Threadripper 2990WX 32-Core Processor physical server on SSD disk. I do a data science project and communicate mysql bd ...
0
votes
0answers
21 views

Multiple vs Single COUNT on same col + Multiple vs Single col index

I have two very simple questions 1. Multiple vs Single COUNT on same col I need to perform CASE on an aggregate column like this: SELECT tbl_students.school, COUNT(tbl_students.id) as students, ( ...
0
votes
0answers
12 views

Does adding an auto_incrementing number to a MySQL partitioned table that is not part of the primary key, cause consistency issues?

I have a table that has a primary key which does not include an auto_incrementing number. The table needs to be partitioned (on that primary key) for the overall strategy of reducing contention points....
0
votes
0answers
21 views

MySQL Fast Database Data Validation Master/Slave

I'm moving a MySQL database from the US to Australia and have a requirement where downtime is kept to an absolute minimum. The database is 400GB is size, replication is working without any issues. ...
1
vote
2answers
32 views

select rows with min values of multiple columns

I have been banging my head on this for a while... I am using mysql to track results from a race - we have racers and categories (beginner, intermediate, Pro - for example) -- and each race has ...
0
votes
0answers
13 views

How to create a classic session in MySql 8?

I want to make use of the mysql 8 parallel import utility: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-parallel-table.html Therefore trying to establish a session as follows: ...
2
votes
0answers
24 views

Why can't outer join simplification happen for embedding outer join?

The MySQL documentation here suggests the following: Sometimes the optimizer succeeds in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:...
0
votes
1answer
17 views

MariaDB FULLTEXT search with apostrophe, and mandatory words

An apostrophe / single quote (') should be part of a word in a FULLTEXT index. Admittedly I can't find this on the MariaDB website, but I'm assuming it should still behave like MySQL (version 5.5). ...
0
votes
1answer
22 views

Using rows from one table as fields in SELECT

I have three tables in my MySQL database - company, extra_fields and extra_fields_info: mysql> describe company; +-----------------------------+------------------+------+-----+---------+-------+ | ...
0
votes
0answers
37 views

Mariadb query optimization - Procedure

I have a complicated procedure and I have 200 clients. When these 200 clients run the procedure simultaneously, the CPU value suddenly increases to 70%. Then it's 6% again. How can I optimize these ...
0
votes
0answers
14 views

database in my xammp suddenly gone [closed]

Im just testing my codes with my database but when I tried to run my program the system found out that their is no database. I check the file and It was gone? How did this happen? I use xammp for my ...
1
vote
2answers
38 views

Show the result of a set difference between tables Q and R [closed]

Assuming that we have two tables: Table Q D E a 1 b 2 g 1 Table R D E f 1 b 2 h 3 Show the result of a set difference between tables Q and R. My answer is D E a 1 g 1 But my classmates said the ...
1
vote
0answers
20 views

Should I add my index after the table is populated or before (MySQL. Aurora)

I know this has been asked in the past, but sometimes things change over time and I wanted to double check. I have a table with about 9 Billion rows. Should I add the index before inserting the data ...
0
votes
0answers
22 views

Benchmarking MariaDB/InnoDB using TATP - Writers slow down readers?

I'm benchmarking MariaDB 10.4 with TATP. The InnoDB config file has the following parameters set: [mysqld] innodb_data_home_dir=/usr/local/mysql/data innodb_undo_directory=/usr/local/mysql/data ...
1
vote
1answer
25 views

MySQL Password Validation - No previous passwords

I wonder if there is any possibility to use the password validation for MySQL 5.6 (https://dev.mysql.com/doc/refman/5.6/en/validate-password.html) to prevent for example the last 4 passwords. The ...
2
votes
0answers
30 views

Schema for large amount of machine's sensor data variables [duplicate]

I plan to do a database for more machines. Each machine will have multiple sensors mounted that store data like temperature, current, power, fault, manual mode and so on every minute. Each machine ...
0
votes
1answer
19 views

How to repair mysql tables using myisam storage engine from multiple repairing threads?

I am loading a csv file into a mysql database table that uses MyISAM as storage engine. From the process list I can see that this is accomplished in two phases: 1) reading the csv file and 2) ...
4
votes
2answers
520 views

Different characters, same ASCII code?

I have this query that throws two results: SELECT id FROM table1 WHERE id like 'nm041033%' nm0410331 nm0410331 And this slightly different query that throws only one result: SELECT id FROM table1 ...
0
votes
1answer
18 views

MySQL: Flush Privileges of Single User

On Mysql 5.1.72 I created a replication user. Right before running FLUSH PRIVILEGES; this thought passed my mind what if there are any unapplied privilege updates? How can I flush privileges of a ...
0
votes
1answer
22 views

ERROR 1813 (HY000) at line x: Tablespace '`mydb`.`mytable`' exists

I'm starting off with a fresh mysql-server installation and am trying to import data into it. I do CREATE DATABASE mydb; and then do the following: mysql mydb < mydb_schema.sql mydb_schema.sql ...
0
votes
0answers
14 views

MYSQL - conversions to money

I have a database that has financial transactions (eg 105.30) stored in a longtext datatype. I can not change the schema, and it is mariadb 10.0.38 I chose decimal(13,2) for my casting, but when I ...
0
votes
1answer
43 views

newbie - database question [closed]

I am trying to help my daughter create an informational site. In the MySQL database I have created a few rows that can have multiple values. For instance, under "Days Open" some entries may have Mon-...
1
vote
1answer
31 views

WHERE IN subquery too slow

I have a FULLTEXT search on the database, with the WHERE IN condition which checks that search results exists in another table. The query takes more than 1 hour to complete (companies table has 67k ...
-4
votes
1answer
12 views

mysql ,,,command show database; [closed]

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1