Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [mysql-5.7]

MySQL version 5.7 - please also tag with mysql for search purposes.

0
votes
0answers
23 views

MySQL query for free Date time slot availability

I am trying to find free Date time blocks on current month and db value DB Structure- Event_ID | dt_start | dt_end +----------+---------------------+---------------...
0
votes
0answers
18 views

How to find the MySQL query that got rolled back

I want to know if a certain transaction got rolled back. If we analyze SHOW ENGINE INNODB STATUS\G; we get the list rolled back transactions with transactionid , but how to get the actual query ...
0
votes
0answers
24 views

Error Code: 1172. Result consisted of more than one row [on hold]

create procedure incremental() begin declare id2 int; declare title2 int; select sid,stitle into id2,title2 from stbl; insert into dtbl (did, dtitle) values (id2, title2) on duplicate key update sid = ...
0
votes
1answer
13 views

mysql row returned n times based on column value n

Say I have a given table with the following data: | id | pid | email | entries | |----|-----|-----------------|---------| | 1 | 1 | foo@example.com | 3 | | 2 | 1 | bar@example....
0
votes
0answers
26 views

Optimise multiple inner JOIN

Consider the following query: mysql> SELECT GROUP_CONCAT(a.id SEPARATOR '\n') -> FROM assignment a -> INNER JOIN statements s ON a.id = s.assignment_id -> INNER JOIN ...
0
votes
0answers
17 views

Reading from two shards using ProxySQL

I have a table that has been sharded between two machines, and the same table on each machine holds different data (not replicated). With ProxySQL, how can I create a mysql_query_rule that allows a ...
0
votes
2answers
24 views

create specific DB-Admin Account

is it possible to create in Mysql an admin-user that can modify everything exept the mysql-root user? Thanks, starflighter
0
votes
1answer
27 views

schedule join multiple tables

Basically I need an scalable efficient way to select all scheduled items so it made sense that the main table is the schedule. The problem is that all the operation tables have different properties I ...
0
votes
1answer
21 views

Upgrading from mysql 5.7 on FreeBSD to mariadb or MySQL8

I am looking into upgrading some major components of a webserver (php, apache, mysql). I've done the update to 5.7 from 5.6. I've updated to php7.3. What I wonder now is: I see that a mysql ...
0
votes
0answers
8 views

Weird Behaviour Aurora Mysql queries Timing out after DB keeps running for a while

I am shifting my database to Aurora Cluster MySQL. For testing, I have created a small sample data (2 GB DB size). I am connecting to the cluster writer endpoint. Connections and queries work fine for ...
0
votes
0answers
15 views

Select Top N rows for each group

I wants to retrieve first n number of rows each group sort by date. For example I have store_id , customer_id , date
0
votes
1answer
9 views

MySQL GTID replication, slave read executed binlogs, failed to get the latest binlog

MySQL VERSION: Server version: 5.7.19-17-log Source distribution Replication mode: mysql multi source replication: MySQL-C works as slave for MySQL-A(source1) and MySQL-B(source2); real ...
0
votes
1answer
18 views

How to install keyring_aws plugin in MySQL?

I am looking for a way to install keyring_aws plugin in mysql community edition. Is it possible? I gone through couple of docs, https://dev.mysql.com/doc/refman/5.7/en/keyring-aws-plugin.html ...
0
votes
0answers
15 views

Tables missing from MySQL database after removing then restoring the mysql data direcory

I Carried out the following operations. make mysqldump of testdb # Stop service /etc/init.d/mysql stop # Create copy of directory cd /var/lib cp -R mysql mysql.backup # Delete datafile on disk rm -...
0
votes
1answer
19 views

Filtering data from a mysql table using a date range where date format is VACHAR ('AUG-18')

Sample Data TRAINING_DATE GENDER AUG-18 M AUG-18 F SEPT-18 F OCT-18 M OCT-18 M NOV-18 M NOV-18 F Expected Output....
2
votes
1answer
26 views

Sub query not working as expected trying to order by then group by, MySQL 5.5 vs 5.7

I do not know/understand DB's as well as I would like. I'm trying to ORDER BY and then GROUP BY so that I get the first record from each group of results in the database. -- Example table (just an ...
1
vote
1answer
40 views

MySQL utf8mb4 Migration - Replication Considerations

We're planning to migrate a MySQL database with a mix utf8 and latin1 tables to the new utf8mb4 character set per this article: https://mathiasbynens.be/notes/mysql-utf8mb4. Our server is 5.7.22 and ...
0
votes
0answers
28 views

“Can't find any matching row in the user table” when attempting “SET PASSWORD”

When I do SELECT User, Host FROM mysql.user I see that I have a user named myuser on %. I tried setting that users password thusly: SET PASSWORD FOR 'myuser'@'%' = 'mypassword'; Doing so, however, ...
0
votes
1answer
16 views

mysql trigger not allowing to select from another table

I have the following snippet in a trigger on a table named bugs: SELECT uat_reopened_stats.release_name (uat_reopened_stats.rate_bugs + qa_reopened_stats.rate_bugs + ...
1
vote
2answers
26 views

Using CONVERT TO CHARACTER SET for changing only collation?

I think the answer to this is an obvious yes, but I'm having trouble finding confirmation on it. All the questions related to charsets seems to be about changing the charset and not only collations. ...
0
votes
1answer
22 views

Pass parameter to trigger dynamically

I have a table on a mysql 5.7 db, containing say athletes with their mean, max, avg times in a specific sport. I have another table that lists some calculated statistics based on those values. I ...
0
votes
0answers
13 views

MySQL query is slow - Need to understand EXPLAIN output

Dear Database Administrators community, I have a query which I would like to find out what causes it to be slow and what can I do to improve it. This is the query I am using: select ereturn1_....
0
votes
0answers
28 views

add index and change int data type to bigint in mysql 5.7 with 1.3 billion records the fastest way

how do i do this without downtime? or at least minimal downtime (3hrs max) Is there any way to tweak my.cnf config to at least make this process of indexing and change data type faster? Below is the ...
0
votes
0answers
25 views

LOAD DATA CONCURRENT LOCAL INFILE from another machine

I am managing a write heavy Mysql DB and the bulk load is the predominant workload that is happening in my system. Monitoring Logs are extracted as a raw file(each file 100MB) in the application ...
4
votes
2answers
47 views

Performance issues with groupwise MAX in JOIN clause

Problem I have some assets in my application which are updated from time to time in an asynchronous fashion. The example I'm gonna use here is Vehicles. There are two tables: Vehicles: holds ...
2
votes
1answer
155 views

Incorrect datetime value: '0000-00-00 00:00:00' for column 'tmp_field_2' at row 1

I'm trying to run the following query: INSERT INTO `user_domain_rating_days` ( `user_domain_rating_id`, `start`, `...
2
votes
1answer
65 views

SELECT execution is slow on InnoDB when compared to MyISAM

We experiencing a slow select on InnoDB comparing to MyISAM - difference for execution on MyISAM is about 2 min., on InnoDB is 2 h. The number of rows involved is 33 M. The row set in result is about ...
0
votes
0answers
10 views

Does it require to change date values of tables while changing the database timezone

Currently, I am using the rds with timezone UTC. While changing the timezone from UTC to EST, How could i convert the values of date field of all tables of database in EST?
2
votes
2answers
222 views

how to count distinct value per day with start time and end time

my title may seem a bit confusing but here's the context. I have a table 1. It contains the reference code per connection to SSID with value of start time. Now I have a scenario wherein the user ...
0
votes
1answer
37 views

Using WITH in sql query

I am trying to replace # with numbers, but the order by is being ignored, and I was told to move the subquery usingWITH or adding a limit to the order by, I tried the latter but it didn't work, and I ...
1
vote
0answers
17 views

MySQL fatal error 1236 when swapping in new master, “master has purged binary logs”

Our situation: we have three MySQL 5.7.23 replica servers attached to a MySQL 5.7.23 master server running in EC2 instances in AWS. All the servers are fine and GTID replication is working properly. ...
0
votes
0answers
13 views

create /var/log/mysql/mysql.log with permissions just like /var/log/mysql/error.log

In mysqld.cnf there was by default uncommented: log_error = /var/log/mysql/error.log and commented out: #general_log_file = /var/log/mysql/mysql.log #general_log = 1 I can view error.log file ...
0
votes
2answers
31 views

Unable to fix ROW size too large even with innodb_file_per_table

I am having a row length error I've been unable to find a solution for. The error messages are all variations on "Row size too large" Error executing 'CREATE TABLE IF NOT EXISTS `db`.`table` Row size ...
1
vote
1answer
36 views

Handle 4 tables and to show 4 columns as result

i can't figure out how to build the correct query that involves 4 tables and shows these 4 columns: I want to show this 4 rows, each from a different table. EDIT: Each company belongs to a Sector. ...
0
votes
1answer
28 views

How to query database tables structures for DATE NOT NULL DEFAULT '0000-00-00' and ALTER them in MySQL

I am using MySQL v.5.7.x. In a database with 161 tables, one of the them has the following structure: CREATE TABLE `prefix_coupon` ( `coupon_id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (128) ...
0
votes
0answers
30 views

MySQL v.5.7.x: What will happen if I remove NO_ZERO_IN_DATE & NO_ZERO_DATE

I am trying to copy a database to another via phpMyAdmin and the process is not completed. Error: # 1067 - Invalid default value for 'date_start' Have a look in the following structure: CREATE ...
0
votes
1answer
35 views

Unable to turn on compression using innodb

I am unable to create a new table using compression. I'm using the following options which from what I understand should allow me to use compression. default-storage-engine = InnoDB ...
1
vote
1answer
227 views

This installation of MySQL is already upgraded to 5.7.25?

I used sudo apt upgrade and got this: E: dpkg was interrupted, you must manually run 'sudo dpkg --configure -a' to correct the problem. So I did it and I see this for more than an hour. Setting up ...
0
votes
1answer
36 views

Execute a sql query inside IF() function

Wherever I find an example of IF () function I only see something like below. SELECT IF(500<1000, "YES", "NO"); +---------------------------+ | IF(500<1000, "YES", "NO") | +--------------------...
2
votes
2answers
57 views

When does MySQL begin to start sending data to a client?

I am working on MySQL 5.7 with the default configuration of InnoDB storage engine. I have two questions each for a different scenario and need to know the internal working on how MySQL sends data to ...
0
votes
0answers
18 views

Is it possible to convince MySQL to use an index with USE INDEX FOR JOIN, even after no longer joining?

It's been a long day, but I've been wresting with a query which wont use an index, and thus takes much longer than it should. I stumbled upon USE INDEX FOR JOIN, but it didn't improve anything. ...
0
votes
0answers
18 views

mysql order by in view is discarded when outer query has where clause

I have a view v1 with an ORDER BY clause that is ignored when a: SELECT * FROM v1 WHERE <clause> is run on that view; but if that statement loses the WHERE clause, then the ordering is ...
0
votes
2answers
35 views

SELECT … FOR UPDATE returns old value after waiting for lock

It seems that if SELECT ... FOR UPDATE has to wait for a lock, and meanwhile another thread changes what would be the result, and commits, then the initial query returns the old result, i.e. from ...
0
votes
0answers
21 views

Mysql warning: InnoDB: Ignoring tablespace `foo`.`bar` because the DISCARD flag is set

Whenever my MySQL 5.7 starts I get the warning InnoDB: Ignoring tablespace `foo`.`bar` because the DISCARD flag is set bar is a table I experimented with long time ago with import tablespace, but ...
0
votes
0answers
19 views

Does Google CloudSQL Export function lock tables?

In order to create more 'data recovery' points than the standard automatic backup policy allows, I'm going to run a cron which will export sql dumps out of my CloudSQL instances at intervals ...
0
votes
1answer
36 views

cannot create a procedure with error [1064] [42000]

I am using mysql 5.7 and I have some code which works. But when I try to create procedure with the code, it reports error [1064] [42000]. I try to update the content of an old procedure with my ...
2
votes
1answer
11 views

Does mysql configuration options accepts - instead of _?

Mysql 5.7 Documentation mentions that innodb_buffer_pool_instances can be set in /etc/mysql/mysqld.conf.d/mysqld.cnf as follows: [mysqld] innodb_buffer_pool_instances = 16 However, an existing ...
0
votes
1answer
13 views

Time addition based on previous column partition by different object

I asked a question on Time addition based on the previous row. Now I have another one related to that question. Appreciate if someone could give me some help. I use the following code to create the ...
0
votes
0answers
23 views

Help with high load MySQL configuraiton

Server: Disk: 2X 500 SSD RAID 1, 3 TB HDD RAM: 256 GB DDR4 CPU: Intel Xeon 10 core Database: Percona for MySQL 5.7.23 Average number of active connections: 1700 I have 4 schemas, and ...
1
vote
1answer
37 views

Time addition based on the previous row

I appreciate if someone could give me some help. I would like to deal with time addition based on the previous column using mysql. Here is what I have. create temporary table test(id integer, ...