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.
18,290
questions
-1
votes
1answer
19 views
MySQL - Which options in the configuration file have an impact on memory usage?
I've been wondering how to manage MySQL memory usage since by default it takes up to 350MB while idle on my machine, I don't have memory issues, I honestly just wondered how could it be done.
I found ...
0
votes
1answer
12 views
I have account_id on all of the tables in the database that belong to an account. Could this be why our database is using a lot of memory?
The application makes heavy use of data. It is a Real Estate Lead Management System each lead could make our users thousands of dollars, and accidentally returning other people's leads would be a sure-...
0
votes
1answer
21 views
How to prevent 'table is mark as and last (automatic) repair failed'?
This is the second time within the last 4 days that one of the tables in my database got corrupted. The is the error that I have seen in my apache log:
PHP Fatal error: Uncaught exception '...
0
votes
0answers
7 views
Increment a value if another value exists, if not create a new row
Below is what my table looks like, how would I go about increasing the "record" value of a row if the "first" and "last" match a given value and if there is no matching "first" and "last" value then ...
-1
votes
1answer
25 views
How does MySQL store the row data internally?
Does a RDBMS store the row data one after another in a data block? Once data block is full, is the new block assigned continuous?
Per my understanding when table is created, DBMS vendor must be ...
0
votes
0answers
6 views
mysql 8 high availability with drbd, corosync and pacemaker
I would like to implement High Availability for MySQL8, is this version supported by drbd, heartbeat, corosync, pacemaker? I've looked around online to that regard but there isn't much information ...
0
votes
2answers
995 views
MySQL Cluster setup, receiving error “Specify node id”
I am attempting to set up a MySQL Cluster.
I have the following setup:
One management node
Two data nodes
One SQL node
Below is the config.ini file:
[ndbd default]
# Options affecting ndbd ...
0
votes
0answers
5 views
MySQL tables_priv corrupted
I have a mysql database and i need to create an user for the login.
This user need the privileges to only select on only one table where the login info are.
on phpmyadmin when i use the gui i receive ...
0
votes
1answer
23 views
Approaching database design
I have 1000 sensors, and I need to hoard the value of each sensor at every second, for a month. This is just theory, the most realistically achievable unit of time will be determined when I start ...
1
vote
4answers
45 views
Unique over 2 columns
I have 2 columns col1 and col2
INITIAL QUESTION:
is it possible to make a constraint to not be able to add the same email two times in this columns, only using indexes?
The Question has been edited ...
0
votes
1answer
56 views
Slow query when matching columns from second table in JOIN with GROUP BY
Update: so, as mentioned in the comments thread, it seems the ORDER BY might be the most direct cause of slow execution. So, the question should be: how can this query use ORDER BY score and execute ...
0
votes
1answer
26 views
ORDER BY with IF-ELSE statement (DESC doesn't work)
Descending sort type does not work with this syntax, please help.
<?php
require_once("../connection/conn.php");
$compare = $_GET["sortfield"];
if(isset($_GET["sortfield"])){
if(isset($_GET["...
1
vote
1answer
10 views
Add data from rows to different columns if more than 1 row have a partial match
I tried to find a solution but do not find anything to add multiple columns from partially matching rows.
Data:(Maximum of 3 programs per person)
name surname program
Jan Peters ...
0
votes
1answer
17 views
Convert rows into columns MySQL DataBase
I have two tables and I would like to generate a query relating the tables.
Until now I have this query:
SELECT Table1.*, table2.* FROM Table1 INNER JOIN table2 ON table2.FK_ID = Table1.ID
but I ...
0
votes
1answer
25 views
How do I know if an index has been successfully created in MySQL?
I use Capistrano to deploy a Rails app. Capistrano handles running database migrations via essentially a shell script that runs from my local computer.
About 24 hours ago I started a migration that ...
-1
votes
1answer
18 views
-bash: PURGE: command not found
I want to delete binary log files
following are my binary log files
binary2.000001
binary2.000002
i want to delete binary2.000002
mysql> PURGE BINARY LOGS TO 'binary2.000002';
It shows me:...
0
votes
1answer
224 views
MySQL multiple table joins and multiple data levels - best practice
I have multiple tables i wish to join up to produce one big query with specific columns summed.
I was having a problem getting the correct summed columns in the final query
(i believe this was ...
1
vote
1answer
214 views
Mysql stop working woocommerce
I have a WooCommerce website hosted on an Amazon EC2 centOS server with 4GB RAM and 4GB swap.
My problem is that sometimes MySQL stops working due to failure to allocate memory and also the website ...
8
votes
1answer
2k views
Appropriate use of mysql Optimize Table
I want to come up with some best practice for maintaining our MySQL database, versions 5.5/6 and using InnoDB.
I came across this article which is basically saying that Optimize table:
not going to ...
1
vote
1answer
169 views
Index Strategy for MySQL Query that matches on one column and between two others
I have a table that contains a set of measurements for a continuous stream of processes. Although each process is individual, they are categorised into groups. The events have a start and end ...
0
votes
2answers
4k views
ClearDB Granting Created User Access
I added the ClearDB addon to Heroku, but upon using the created username to create a new database, the error appeared:
Executing:
CREATE SCHEMA `androidchatterdatabase` DEFAULT CHARACTER SET utf8 ;
...
0
votes
1answer
92 views
Mysql 5.7 - select where json
I have some JSON data stored in a filters column. An example from one row:
name | filters
---------+----------------------------------------------------------
John Doe | [{"Year":2004, "SportID":...
0
votes
0answers
20 views
How to trigger Mysql parallel query function to start?
Please help,how to trigger Mysql parallel query function to start?
MySQL version is 8.0.19.
mysql> show variables like 'innodb_parallel_read_threads';
+------------------------------+-------+
| ...
1
vote
1answer
156 views
Store and search leveled categories with mysql json
I have leveled category triplets column, that tag the specific blacksmith with items he can produce:
blacksmith_id (primary) | blacksmith_name (varchar) | item_tags [json] | blacksmith_lvl (int)
...
0
votes
1answer
387 views
commit or rollback on a statement based replication, effect on auto increment id
How commit or rollback is safe on statement based replication in terms of auto_increment column?
If I execute below queries, shoudn't there be discrepancy between master and slave in auto_increment ...
2
votes
2answers
6k views
Creating Event that execute at end of each month
I need to create an event in mysql that runs at the end of every month 11 PM.I have tried creating an event that creates another events with code below:
DELIMITER |
CREATE EVENT `2014-10-...
1
vote
3answers
478 views
Is there a way to see if database has been accessed through SQL Injection attack?
I recently discovered that my website had a page with a SQL Injection vulnerability. Upon testing, it was easily exploitable with a pentesting tool like sqlmap. How do I figure out if the site ...
2
votes
1answer
67 views
Why does MySQL take so long to execute this query on a large dataset?
Been banging my head against the wall on this one for a while now and hoping someone can help me out.
The SQL query:
SELECT
`elements`.`id` AS `elementsId`,
`elements_sites`.`id` AS `...
1
vote
1answer
245 views
MySQL: Overlay modifications on original data while indexing on a field that itself has modifications
I have a MySQL table of original_data that is important for historical/auditing purposes and should not ever be changed. I want to be able to mark up these data with modifications, e.g. to correct ...
1
vote
2answers
62 views
mysql 8.0 in-memory table slow insert
Please help with following strange issue.I defined in memory table but insert of 600.000 rows takes 13 minutes and causes heavy write to disk. The CPU is underutilized and I have enough free memory. ...
0
votes
1answer
357 views
Calculate Total Working Minutes in MySql
I have a design a table for attendance and here are the genral fileds
AttendanceMachineLoginId
EmpId
AttendanceDateTime
Whenever user will come to office, he has to make his attendance.
The first ...
0
votes
2answers
32 views
create a related row if no related row exists
I have two mysql tables with a one-to-many relationship.
I want to find all rows in the 'one' table which do not have a row in the 'many' table and add a single row in the 'many' table for each one.
...
0
votes
1answer
43 views
How is it possible that some people are able to juggle several different SQL variations in their heads at once? [closed]
I have spent the last 20 years dealing with first MySQL (for about 6 years), then 14 years old PostgreSQL. In 2006, I naively thought that it was going to be painless, "because both use SQL"... man, I ...
0
votes
1answer
84 views
Do I need to `mysql_upgrade --verbose -u root -p` each time when `source backup.sql`?
I am upgrading mysql 5.1 to mysql 5.5 in centos 7 server.
As time limit,I only mysqldump and source several small database to mysql 5.5,and then mysql_upgrade --verbose -u root -p,everything is ok. ...
0
votes
1answer
18 views
Column Terminology
I'm a MySQL student here trying to learn the proper way to call things in the SQL language.
CREATE TABLE tbl_name (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
width SMALLINT ...
1
vote
2answers
85 views
Cleanup / Prune Unreferenced Data in Dimension Tables
We have a star schema data warehouse running on MySQL 5.6. We keep a rolling 18 months of data in our fact tables using partitions by month. We have a number of dynamic dimension tables that are ...
1
vote
0answers
7 views
MySQL 8.0.18 - ST_Area returns invalid value
I have a problem with ST_Area function in MySQL 8.0.16.
The result of the procedure call returns a different result than any library used to calculate the area of a polygon.
Example:
I have a ...
0
votes
1answer
235 views
Tracking MongoDB execution times using PHP
I am currently working on a project using PHP, I have done research and found that microtime() helps with tracking the time of a query in a MySQL database..
how do I do the same for MongoDB, I have ...
-1
votes
1answer
38 views
Why is the order of `OFFSET` and `LIMIT` treated differently between PostgreSQL and MySQL?
I have a schema like this (works for both MySQL8 and PostgreSQL12)
# 1. Build schema
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee (
Id INT,
Salary DECIMAL(6, 2),
PRIMARY KEY(Id)
);...
-2
votes
2answers
25 views
how to hide the columns and data in table based on the header and data values by using php and mysql [closed]
I have two queries for table header and table data so first query is table header I am fetching and second query is table data,my queries are like this,
select a1,a2,a3,a4,a5 from test1
select b1,b2,...
0
votes
1answer
20 views
MySQL: Should I keep a running total column in one table instead of summing a second table?
I have 3 tables named users, achievements, and userAchievements. The users table contains information like username, password, e-mail, etc... and the achievements table contains a list of possible ...
0
votes
1answer
220 views
how to judge sql performance through these two explain?
If I give you 2 explain results from MySQL,how do you know which query is better?
the first sql explain
*************************** 1. row ***************************
id: 1
select_type: ...
0
votes
1answer
12 views
After change to 13306, Galera logs error: Slave I/O: error connecting to master 'repuser@<master node ID>:3306'
Working with Galera 25.3.23 on RHEL 7.3
Galera works good before. After changing the MySQL server port from 3306 to 13306, one of the node report error, after restart:
Slave I/O: error connecting to ...
3
votes
1answer
6k views
Drop privilege to tables only, Not to database
I created a user with drop privilege to a database.
How can I create a user who has the privilege to drop tables of a specified database, but not database.
0
votes
1answer
344 views
MYSQL Patching -Roll back
MYSQL patching.
I took all database backups before starting patching.
If we patch mysql 5.6.30 to 5.6.32 and if we want to rollback to 5.6.30 due to some issues. Can we do that? if so should we have ...
0
votes
0answers
30 views
How to reconcile databases after split-brain?
We run three on-premise datacentres with several databases. The network between the three datacentres is not constant and goes down several times a year for lengthy periods of time, but the ...
0
votes
1answer
26 views
Table contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in MySQL
The database is an old version of MariaDB, the equivalent of MySQL 5.5. The table contains those columns
id
auth
old_auth
device_id_fk
This error is showing
Table contains 4 indexes inside InnoDB, ...
0
votes
1answer
17 views
problem to import load data infile
I am trying to load data into mysql database form text file.
LOAD DATA LOCAL INFILE 'P:\file.txt' INTO TABLE table_january
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ...
0
votes
1answer
327 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
3answers
25 views
mysqldump interrupts always with same file size
mysqldump -h127.0.0.1 -u user -p'password' -t db mail --where 'status=3' > /var/www/project/storage/web/dump2.sql
I try it on ubuntu 18.04, mysql 5.7.29.
And the file is always 141.8MB.
Data just ...
