Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
47
questions
8
votes
1
answer
5k
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
6
votes
2
answers
19k
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....
- 161
6
votes
1
answer
11k
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
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
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
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
0
votes
2
answers
133
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,
`...
- 249
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
12k
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
8k
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
1
answer
4k
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
4
answers
38k
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
2
answers
21k
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
3
answers
10k
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
5
votes
1
answer
6k
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
4
votes
3
answers
7k
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 ...
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 ...
- 798
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
17k
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
...
- 21
2
votes
2
answers
201
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
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
449
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
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
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
1
vote
1
answer
104
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
0
answers
101
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
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,086
1
vote
1
answer
270
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 ...
- 679
1
vote
1
answer
621
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
1
answer
707
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 ...
- 125
0
votes
1
answer
253
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
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 ...
- 103
0
votes
0
answers
147
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
0
answers
40
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
1
answer
442
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
168
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
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
3
answers
6k
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
0
answers
216
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 ...
- 23
0
votes
2
answers
556
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
0
answers
214
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
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,029
0
votes
2
answers
262
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 ...
0
votes
1
answer
445
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 ...
- 679
0
votes
1
answer
465
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