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.
20,724
questions
1
vote
1answer
44 views
mysql tunning with larga database
soo.. I have a server in a VM with a large database in MySQL 5.7 (centos), and I was trying to tune a little bit the server (32gb RAM) and, so far I'm running this my.ch
I did use the perl script ...
0
votes
0answers
32 views
UPDATE rows and SET ordering depending on level and parent
I'm trying to write a small UPDATE query that makes sure all rows are reordered considering the parent_id.
Needs to work under MySQL 5.6 / MariaDB 10
This is a DB Fiddle made for testing.
So this is ...
0
votes
1answer
51 views
Why does this sql join query take much longer using a reference to a row from the first table in the 'on' clause versus using a literal variable?
I have two tables, 'auth' and 'ips', which I'd like to join in a query.
Table 'auth':
column name
data type
Extra
id
int
AI
date
varchar(6)
-
timestamp
varchar(8)
-
result
varchar(8)
-
ip
...
0
votes
1answer
28 views
Data too long for column when to alter column definition
The column definition is as below:
`code` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
I want to alter another table(option)'s code field as same definition:
ALTER TABLE `...
1
vote
1answer
18 views
Altering mysql database column causes error in another column
First of all sorry everybody if this question sounds too basic.
I have a Wordpress database and a table wp_comments.
In this table, I have two DATETIME fields, whose default value is 0000-00-00 00:00:...
0
votes
1answer
23 views
service mysql reload results 'Access denied', but service mysql start works
Using Debian 10.10.
I have a MySQL problem.
service mysql reload
results 'Access denied'
service mysql start
executes correctly.
All databases are working correctly after starting mysql. Strange, ...
3
votes
2answers
67 views
Apply nth occurrence number on each distinct value in a column
I have a table with a column of values where each value occurs a variable number of times (i.e., one value may occur 1 time, and another value may occur 3 times). I need to add a column that ...
0
votes
2answers
52 views
database changes are lost in the next session
We just move to Amazon RDS. Now all the CREATE, UPDATE, and INSERT commands are reverting in the next session. All the tables are InnoDB and query caching is on. What could be the problem?
0
votes
1answer
101 views
What does it mean when slave status shows SQL running but IO not running?
What exactly does this mean, when the SQL is running but the IO is not running on the Slave in a Master-Master replication setup?
There are no other errors showing when running show slave status\G
...
2
votes
3answers
44 views
Preserve Autoincrement ID
I am working on an e-commerce webapp and here's what I am thinking about.
To make it simple in this question, the user table contains two columns:id (int,PK,NN,AI),name(varchar(45),NN). I want the ...
1
vote
1answer
46 views
calculate working hours mysql based on in out punch
I have data of employees in and out punch in one mysql table and i want to calculate work time according to time between in and out punch. when employee's first punch found consider it as in and ...
-1
votes
1answer
128 views
How to resolve errno: 150 "Foreign key constraint is incorrectly formed" [closed]
CREATE TABLE `account_transfers` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`uuid` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`from_account_id` bigint unsigned ...
-1
votes
2answers
80 views
Help Me For Implementing MySQL tuner suggesstions
Mine is Digitalocean with 1vCPU, 1GB -25GB SSD Ubuntu server with nginx, MariaDB, phpmyadmin and running a wordpress blog. Mysql is taking too much cpu and on running running MySqlTuner, I got these ...
0
votes
1answer
27 views
Adding Virtual Column after column x is very slow
In our environment, we usually add and drop generated columns every now and then. There is a very large table in the database with more than 3 million rows and adding/removing column was extremely ...
0
votes
3answers
53 views
Need Advises on Updating MySQL Database(Both software and hardware)
I have three in production MySQL database (5.7) with about 160GB of Data. The largest table is about 20GB, and the largest schema is about 40GB. I think all tables are innodb table. Currently I don't ...
0
votes
1answer
100 views
How can I create a mysql database (if it doesn't exist) or replace it (if it exists)?
My dump:
mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt --databases mydatabase > out.sql
My restore
mysql --host=...
1
vote
1answer
23 views
Can I order/group on a column and show a limited number of the results at the top and then show the remainder in a second sort order?
I have a simple table for product data with a column called special which can be 1 or 0, indicating if the product is special and should be highlighted in listings of products that I need to generate.
...
1
vote
1answer
17 views
How to restore one mysql database with mysqldump
to make a backup I run:
mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt mydatabase > out.sql
Is it possible to do the ...
1
vote
1answer
37 views
Optimizing a distinct on a parent table in a multi-level join in MySQL 5
I have the following tables: in a MySQL 5 database:
projects - 1,000 rows
tasks (FKed to projects) - 10,000 rows
task_tags (FKed to tasks) - 350,000 rows
task_comments (FKed to tasks) - 750,000 rows
...
0
votes
1answer
32 views
JOIN only latest row through a pivot table in one to many relation - MySQL [closed]
I have 3 tables and I only need to compare data between 2 of them but only taking in mind the latest row. The structure of the table is as follows
Table Places
id
brand
phone
email
1
Place A
000000
...
0
votes
2answers
18 views
MySQL slowlog optimsing for Having and Group By Keywords
I have below entry in the slow-log, i just wonder how can i optimise this below query as it looks very simple query that, i don't see any way of optimising it,
# Query_time: 32.797535 Lock_time: 0....
0
votes
2answers
32 views
Group_concat with joining multiple tables
I have the following scheme
users: researches: researchyear:
uid | name rid| titleAr rid | uid |
---+-------- ---+------------ -----...
1
vote
1answer
25 views
Need to select data in every nth time 30 min, 60 min, 180 min interval
I have data in mysql which is stored in every 15 minutes.
e.g. 2021-08-24 01:00:01
2021-08-24 01:15:01
2021-08-24 01:30:01
2021-08-24 01:45:01
Now I want to select data with every 60 (...
0
votes
0answers
23 views
Creating a foreign key on an empty MySQL InnoDB table takes a very long time
I have created a table called asset_versions:
CREATE TABLE "asset_versions" (
"asset_id" int NOT NULL,
"version" varchar(50) NOT NULL,
"created_at" ...
0
votes
0answers
17 views
rotate error log file of MySQL 8.0 server over windows
we have MySQL 8.0 installed over windows and my err log file (.err) is very large (11 GB),
so we need to rotate the error loge so it writes to a new file when the current file is 200 MB
we tried to ...
0
votes
1answer
48 views
MySQL table + partitioning + spatial data
We have a table that currently has every postal address for a single state, currently around 13 mil records. Among postal addresses, it also contains spatial coordinates for each address along with ...
1
vote
2answers
138 views
Under what conditions is it safe to use innodb_flush_method = O_DIRECT_NO_FSYNC?
There seems to be some contention about whether one should use innodb_flush_method = O_DIRECT_NO_FSYNC on XFS or EXT4, and MySQL 8 even turns this on as part of the innodb_dedicated_server=y option.
...
0
votes
0answers
26 views
How to restore mysql database schemas with its data in ubuntu?
Accedednlty
one of my employees ran a script into the production server instead of the development server.
so he dropped 7 columns with its data.
i everyday backup all my server files into another ...
0
votes
0answers
20 views
Foreign key on another foreign key && Pivot Table
I need to design a web app that function as poll system. It should have multiple polls with only 2 contestants which can be repeated in other polls too. For each contestant in the poll I need to count ...
1
vote
1answer
29 views
Multiple Joins With A Count
I have several tables like this
A posts table
+------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------...
0
votes
2answers
50 views
Default 0000-00-00 00:00:00 DateTime values in InnoDB
I have changed the MyISAM tables to InnoDB. During the conversion, I changed SQL_MODE (SET SQL_MODE='ALLOW_INVALID_DATES';) because in some tables I had DateTime records with the default value of '...
0
votes
0answers
21 views
Detail about communication packets error log
On MySQL error log /var/log/mysql/error.log I'm getting this error couple of times during the day:
2021-08-23T07:30:17.660730Z 455148 [Note] Got an error reading
communication packets
My question is ...
3
votes
1answer
16 views
Clarification: Replication Filters for MySQL Group Replication
As I understand it, replication filters in MySQL are not allowed for MySQL Group Replication. Is this correct?
Does this imply that all databases among the group replication instances must have the ...
1
vote
2answers
61 views
Error: Dynamic SQL is not allowed in stored function or trigger
Im trying to convert multivalued column in Table A into rows of Table B(The data type of the column is varchar).
For ex:
Table A
id value
ae [1,2,4,5]
ac [1,6]
Table B
id value
ae 1
ae 2
ae ...
2
votes
0answers
24 views
mySQL cluster with Active Active DC
Let me give you a bit of background before jumping into the discussion.
Our company is running two DC's in active active scenario, we spent a lot of money on getting specialized hardware to be ...
-2
votes
1answer
20 views
Mysql is shutdown unexpectdly
I am running magento in xampp, but mysql is not starting , here is the log file
here is the mysql config file
# Example MySQL config file for small systems.
#
# This is for a system with little ...
2
votes
1answer
168 views
MARIADB default users
I just installed mariadb 10.6 and can't understand...
SELECT Host,User,Password FROM mysql.user;
mariadb.sys@localhost password is empty.
root@localhost password hashed.
mysql@localhost password has &...
0
votes
1answer
45 views
Recover MySQL from copied /var/lib/mysql
I have spent most of the day trying to recover from a crash followed by a couple of bad decisions.
While copying a table from DB1 to DB2, using phpmyadmin, MySQL crashed and from that point, I just ...
0
votes
1answer
70 views
MySQL keeps crashing - Error establishing a database connection
I had installed wordpress on ubuntu 20.04 on 2GB/2CPU and I have a problem with my MySQL server that keeps crashing. Everyday we are getting below error :
error establishing a database connection and ...
2
votes
1answer
30 views
Conditional aggregate on existing query
I have following table structure (I'm using MySQL 8):
CREATE TABLE `listings` (
`id` bigint unsigned NOT NULL,
`url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`...
0
votes
2answers
39 views
MySQL Transport Protocol Used
How can I find out which Transport Protocol MySQL is using? I know how I can change it but I don't know how to show the current one.
2
votes
0answers
26 views
How to; Schema for fitness data ("Analytics") [closed]
I'm currently working in renewing my fitness synchronisation product, and with it- the database logic/structure. I'm hoping to gain some expertise in the matter and how to think when this database ...
0
votes
2answers
24 views
Removing large amounts of rows from MySQL 5.7 table + ibdata increase question
I'm working with MySQL 5.7 on Windows.
I've got several tables to clean data from.
Largest table's ibd file is 300GB in size and it has almost 1.5 billion lines. I need to leave about 290 million rows ...
0
votes
1answer
26 views
MySQL tables statistics over time
I used MySQL WorkBench to get the most used tables in the MySQL database. I went to
Performance > Performance Reports > Database Schema Statistics > Schema Tables Statistics
and saw all ...
1
vote
1answer
33 views
Aurora MySQL: how do I keep binlogs around after reboot?
We have a request to enable binlogs on an Aurora MySQL instance and to keep those logs around for a certain amount of time. To test, we enabled binlogging with binlog_format=ROW and set our retention ...
0
votes
0answers
30 views
MySQL: Migrate MyISAM table with multicolumn primary index with autoincrement to InnoDB
I have a large (10M rows) MyISAM table with a primary index on 2 different columns where one of them is auto_increment.
How can I migrate it to InnoDB in MySQL 5.7?
The table is something like:
CREATE ...
0
votes
1answer
48 views
How bad is to prefix table names to order them exactly as I want?
When using an interface like phpmyadmin/heidisql or others, I have this tables:
account
account_post
account_post_status
account_post_type
account_status
account_type
Ideal for me is this:
account
...
1
vote
1answer
34 views
Can I split text from one column into to two existing columns in My SQL [closed]
SELECT `Appointment`,
SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 1), ' ', -1) AS Date,
SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 2), ' ', -1) AS Time
FROM go_applicants
...
0
votes
1answer
40 views
How to count id up in sql? [closed]
I want to create a small quiz and I have all the questions in my sql which kinda looks like this :
id
question
1
how long...?
2
how many...?
I want to start with question with id=1 and after a ...
0
votes
2answers
75 views
MySQL 8.0 Hanging and Disconnecting At Random Times - Slow Downs - Need Help
Yesterday, I noticed in my web logs that most traffic disappeared for 2 hours. I decided to do a routine Ubuntu 20.04 upgrade yesterday that I haven't done in 1-2 months, and it seems like a lot of ...
