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