Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
49
questions
8
votes
1
answer
6k
views
Install MySQL for Windows from .zip and reset root password
I wanted to have on my PC (with Windows 8.1 x64) the MySQL server only, without Workbench or something. So I downloaded .zip archive from dev.mysql.com/downloads. It's a download for Win64 on x86_64 ...
- 427
3
votes
1
answer
8k
views
Mysql Pivot rows into dynamic columns with unknown number of columns
I have an output of a query as follows:
bid | code | amount |
-----------------------------
2915 | BF | -10700.00 |
2915 | YQ | -300.00 |
2915 | YR | 0.00 |
2915 | YM ...
- 131
7
votes
2
answers
21k
views
enabling BINARY LOG on AWS RDS MySQL
Context
I am trying to enable BINARY LOGs on a MySQL RDS instance.
Issue
I used CALL mysql.rds_set_configuration('binlog retention hours', 24) without encountering an error.
And using CALL mysql....
- 171
6
votes
1
answer
12k
views
Managing ibtmp1 File Growth on MySQL 5.7
We recently upgraded to MySQL 5.7, and now we're into production we're finding the ibtmp1 file is growing on a few servers. Mostly it's not a problem, but we have had a couple of scary moments where ...
- 221
1
vote
1
answer
2k
views
Mysql eat a lot of memory but innodb_buffer_pollsize is set to 50%
I don't understand why my mysql eat a lot of memory even though I set innodb_buffer_poll_size to around 50%
here is my top
user@Mulia-Abadi:~$ top
top - 11:09:31 up 12 days, 16:30, 3 users, load ...
- 31
1
vote
2
answers
2k
views
Restoring XtraBackup from MySQL 5.6 to MySQL 5.7
We are running MySQL 5.6 and taking backups using XtraBackup 2.4. We want to migrate to MySQL 5.7 using that same backup as we have datasets well over 600GB.
Is it possible to do so and what are ...
1
vote
1
answer
9k
views
"Error in query (1030): Got error 168 from storage engine" when creating table
I'm trying to create a table on a separate directory, like this:
CREATE TABLE `abcdef` (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DATA DIRECTORY='/media/sdc/mysql/';
But it returns the error:
...
- 133
0
votes
2
answers
142
views
How to merge records for query set
We have a question related to a DB query :
CREATE TABLE `Action` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`emp_Id` int(10) unsigned NOT NULL,
`name` varchar(60) NOT NULL,
`...
- 289
12
votes
6
answers
12k
views
High CPU system time usage on MySQL server
A little backstory, some time ago we started to experience high CPU system time on one of our MySQL databases. This database was also suffering from high disk utilization so we figured that those ...
- 119
12
votes
2
answers
14k
views
MySQL - UUID/created_at cursor based pagination?
For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we ...
- 145
10
votes
2
answers
11k
views
Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4
I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4. Is there a way to generate ALTER ...
6
votes
4
answers
39k
views
"Your password has expired" after restart mysql when updated mysql 5.7.8-rc(debian)
echo time login in mysql shell it shows:
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
and error log shows:
Your password has expired. ...
- 71
6
votes
1
answer
5k
views
Why mysql throws "invalid default value for field" only when two or more timestamp fields exist in the create statement?
In mysql 5.7.28 I create a table like this:
create table t1 (
id int not null,
d1 timestamp)
engine=innodb;
which works fine and creates d1 as non-null with default value current_timetamp,on update ...
- 163
6
votes
2
answers
25k
views
MySQL - Binary Log File size growing more than the maximum limit
I have a MySQL 5.7 running on a Centos 6. I enabled Binary Logging and didn't provide any custom value for the maximum size of Binary Log in my configuration file. By default, the parameter ...
- 163
5
votes
1
answer
7k
views
After mysql_upgrade to 5.7, optimizer not using index on large IN clause
I have a similar issue to some other people have described.
In MySQL 5.6.23-72.1-log, a query with a large number of values in an IN clause uses an index, and takes 10 minutes to run;
In 5.7.19-17 ...
- 51
5
votes
4
answers
13k
views
How to take mysqldump with Generated column?
I am getting below error while importing the dump,
ERROR 3105 (HY000) at line 82: The value specified for generated
column 'column_name' in table 'table_name' is not allowed.
When I describe the ...
- 183
4
votes
3
answers
9k
views
Performance issues after upgrading to MySQL 8
UPDATE (tl;dr;):
I filed a bug report here: https://bugs.mysql.com/bug.php?id=99593 which has since been acknowledged and a workaround provided. See answer below for details.
Certain queries seem to ...
- 726
4
votes
1
answer
1k
views
MySQL equivalent of a spatial ST_Union aggregate function?
I'm working on a DB-spacial project in Mysql and I was trying to perform a union operation for a group of shapes. Im looking for something similar to the SQL Server function UnionAggregate, or the ...
- 818
3
votes
1
answer
20k
views
Migrate MySQL 5.7 to 8.0 via mysqldump
I have an Ubuntu server 18.04 LTS with MySQLl 5.7
I have new Ubuntu backup server 20.04 LTS with MySQL 8.0
MySQL 5.7 backup created:
mysqldump --all-databases > "filename" -u root -p
...
- 31
3
votes
2
answers
6k
views
Accessing impact of deleting ibtmp1 file in MYSQL
There is a file called ibtmp1 and it is consuming 138 GB. I understand, this stores uncompressed data. Do we really need these files or can we delete it? if deleting is fine, then could you please ...
- 31
2
votes
1
answer
1k
views
What does MySQL actually use to decide which index should be used?
I know there is already similar question here like How does the database decide which Index to use. But it seems that those answers are not cool and useless.
In my development and use of MySQL, I ...
- 21
2
votes
2
answers
497
views
InnoDB errors and crashes with MyISAM database?
I have one server with one mysql database containing only MyISAM tables. There is no other databases on the server (except system ones of course). Since it moved to mysql 5.7.27 there are InnoDB ...
- 123
2
votes
1
answer
1k
views
Insert select - using selected value in ON DUPLICATE KEY UPDATE
In the following query, oversimplified from my actual scenario but close enough:
INSERT INTO foo(bar_id, baz)
SELECT
id,
5 AS new_baz
FROM
bar
ON DUPLICATE KEY UPDATE baz=new_baz
I get ...
- 135
2
votes
3
answers
6k
views
I can't find time zone tables in sys database of MySQL
For my Django application, I had to set the time zone to UTC, however I am not able to set it as I couldn't find any tables related to time zone in the system database.
How do I get those? the ...
- 21
2
votes
1
answer
1k
views
MYSQL refuses to set password for new user
I'm trying to create a MySQL user/password. And every time I create the user - mysql does not honor my password. It's just blank.
mysql> select Host, User, password_expired, Password from mysql....
- 23
2
votes
2
answers
212
views
How should I model a database structure to retain calculation results?
I am trying to improve the performance of the queries related to one table that I consider "weird", which could be created by the next script:
CREATE TABLE `CalcTable` (
`id` int(11) NOT NULL ...
- 169
1
vote
1
answer
2k
views
MySQL: My table size using a query does not match the IBD's file size on disk
I have a table with about 600 million rows. I ran the following commands and noticed
select table_schema "DB name (table_schema)",
-> sum((data_length+index_length)/1024/1024/1024) AS "DB ...
- 113
1
vote
0
answers
105
views
How to avoid running multiple selects with the same subquery join?
I am using MySQL 5.7.26.
I have 12 SELECT queries which run the same subquery join; since the subquery in a join is very complex (can be more complex, it is a filter query in a web app) I want to ...
- 65
1
vote
1
answer
693
views
MySQL test if function is available
Since MySQL 5.8 the function GeomFromText() is no longer available and one should use ST_GeomFromText() instead, which works exactly the same. Progress, of course :-(
Ref: https://dev.mysql.com/doc/...
- 111
1
vote
2
answers
41
views
Finding files a user has access to is slow when ordering
I'm working on a system where users are sorted into groups, and then those groups are applied on files.
At a high level, what I need to do is to be able to pull out the 10 most recently-updated files ...
- 11
1
vote
1
answer
119
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,
...
- 113
1
vote
3
answers
2k
views
MySQL 5.7 - Where is the mysql_secret file on Windows 7?
I've just installed a copy of MySQL 5.7 for testing, and I love the fact that it now only creates a single root account, and puts the password into a file called .mysql_secret.
There's just one ...
- 2,166
1
vote
1
answer
276
views
C API hangs in mysql_query() [closed]
I have this very strange issue and I cannot reproduce it - it occurs once in ~12 hours only for this table:
CREATE TABLE `ReferenceWaves` (
`ReferenceWave` datetime NOT NULL COMMENT 'DateTime of ...
- 719
0
votes
0
answers
169
views
Aurora Serverless - tables are empty after successful insert
I have MySQL 5.7.12 running on Aurora Serverless, and I've created a database with a table:
mysql> show create database tpcc\G
*************************** 1. row ***************************
...
- 231
0
votes
1
answer
462
views
mysql_query() is NOT passing the command to the MySQL server using the C API
I have this question asked but after a deeper investigation I realized that hanging is not the issue. After reading this thread I decided to ask a new question.
For example I pass this string to the ...
- 719
0
votes
0
answers
34
views
MySQL-Proxy works with HeidiSQL but not with DBeaver nor Workbench
For data analytical applications, our current development environment relies on a MySQL-Proxy (version 0.8) to connect to the MySQL data source. And the MySQL's version is 5.7.43-47-log.
We noticed ...
- 13
0
votes
1
answer
673
views
drop and re add an index on innodb table causes slow performance
We just made a code and db update, that involves a table on production.
This INNODB table was "altered" by adding a new index and dropped the old one.
The new is made by 3 params and the old ...
- 101
0
votes
1
answer
155
views
GCP Cloudsql Mysql Replica unresponsive after mass delete on Master
Directed here from S/O
We have a Master/Replica configuration for Mysql innodb(5.7.32) databases in Cloud SQL... We have a single Table (let's call it Master table) partitioned on Two keys having both ...
- 11
0
votes
0
answers
43
views
What is the best way of implementing expiration semantics on MySQL?
Presently I am using Redis as a data store, it all goes well until it doesn't namely we have records that suddenly disappear but there's no rhyme or reason we can find it and it's generally one offs. ...
0
votes
2
answers
1k
views
Unable to add primary key to innodb table without one
I've inherited myself a fun table with some peculiar indexes (and no primary key) on it, that looks as follows:
CREATE TABLE `my_wonky_table` (
`id` bigint(20) unsigned NOT NULL,
`login` varchar(...
- 217
0
votes
0
answers
248
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 ...
- 133
0
votes
2
answers
652
views
query to replace text with sequential numbers
I have an images database, the default sort is the "name" column, and the "description" column content is the exact same in all files in the table. what I need is to replace certain text in the "...
- 105
0
votes
2
answers
2k
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
...
- 101
0
votes
1
answer
517
views
config file and parameters for ndb_desc
I'm trying to execute ndb_desc from API client. The config and parameters are as below:
my.cnf in mysqld:
[mysql_cluster]
ndb-connectstring=192.168.1.1,192.168.1.2
server-id=5
log-bin=mysql-bin
...
- 13
0
votes
3
answers
7k
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 ...
- 101
0
votes
1
answer
4k
views
Partitioning with AUTO_INCREMENT primary key
I am looking to partition a MySQL table by the month of the entry timestamp column (time that the row was inserted). The table in question uses an AUTO_INCREMENT column as a primary key as this table ...
- 1,049
0
votes
1
answer
274
views
Insert mysql query results into database column with corrensponding ids
I'm trying to store some data from a temporary query into a table, but it doesn't work.
Suppose we have the following data
Table_A
Submission_id
Salesman
Customer name
Hobby
Carline
Fuel
...
0
votes
1
answer
195
views
MySQL / MariaDB granular row size
Is it possible in MySQL / MariaDB to calculate row size like in PostgreSQL ?
I'm looking to calculate in a multi tenant application how much space did the user use for the database but I couldn't find ...
- 113
0
votes
2
answers
290
views
Order by is ignored if inside a derived table of a update (only in MYSQL 5.7)
I have a update to fix positions values inside a table. The update worked fine in MYSQL 5.5, but after upgrade to MYSQL 5.7 it doesnt work anymore. Now, the "order by" inside the derived table is ...
