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.

learn more… | top users | synonyms

3
votes
1answer
22 views

MySQL binary log reporting execution time as UINT_MAX

Looking through the MySQL Binlogs, I see some data that just does not make sense. There are queries that say they have been running for 4294967295 seconds (which also happens to be UINT_MAX on this ...
1
vote
1answer
56 views

Complex key performance problem (MySQL, InnoDB)

A query is painstakingly slow in both production environment and (fortunately reproducable) on my local dev box. Production is Linux and my dev is currently Windows 10 with MySQL 5.7.11: SHOW ...
3
votes
2answers
48 views

MySql - Ranking so it only needs to be done once

By reading around, this seems to be a reasonable way of ranking people. The table: UserId (int primary key) Name (varchar(100)) Score1 (int highest scores rank 1) Score2 (int, if Score1s are equal, ...
0
votes
0answers
20 views

Detect IP subnet overlap from mySQL database

I have a database with a list of IP ranges in my corporate infrastructure. I want to identify any subnet ranges that are overlapping. I was using the code below which converts the IPs into decimal ...
2
votes
1answer
19 views

In [Percona] MySQL, is the Adaptive Hash Index included in the Total memory allocated count?

I'm debugging a memory problem with MySQL 5.6 (I'm using the Percona distribution), but I can't figure out, searching around the web, if the Adaptive Hash Index amount is part of the Total memory ...
1
vote
1answer
62 views

Why is MySQL MariaDB GREATEST(timestamp,NOW()) padding appending results with zeros?

Everything works as expected on my development machine, but when deploying to quality assurance environment, tests are failing because MariaDB is padding the result set with extra precision zeros ...
0
votes
0answers
16 views

Sending Data Across Databases in Realtime [duplicate]

I have a device that sends data to a database say, DB1. Now, for every insertion or update done in DB1, I want it done on DB2 in real time. Please somebody help me out on what to do here. Thank you in ...
3
votes
1answer
23 views

How can I assign values (-1, 0, 1) to the difference between consecutive rows in a MySQL table having 20 columns and 20 rows

I have a table from MySQL called References with 20 columns and 20 rows: CREATE TABLE bop_ppy ( start_time DATETIME (6) , end_time DATETIME (6) , high DECIMAL (5,2) , low DECIMAL (5,2) , close ...
0
votes
0answers
28 views

myisamchk: Why is there a huge time difference running myisamchk in real and user time?

We have some MySQL MyISAM databases, whose backups are taken using mysqlhotcopy Thought to check the backups integrity, and errors (if any) and hence run myisamchk. I was tracking the possible ...
3
votes
1answer
33 views

Is there a non-blocking operation to back up MyISAM tables using Percona XtraBackup?

I've been reading through the website of Percona XtraBackup and it claims that: Percona XtraBackup can back up the following storage engines by briefly pausing writes at the end of the backup: ...
1
vote
1answer
99 views

Which field should be the candidate for index in this case?

You're tracking visits already in another table that contains quite some information about the visit. In order to block duplicate visits after a certain amount (Let's say 5) you think its best to use ...
0
votes
1answer
25 views

How to whitelist all connections in MySQL

It would be convenient for me to whitelist a server so that anyone with the username and password can connect to it remotely? Is this possible? I do understand this is horrible for security purposes ...
1
vote
1answer
30 views

Is it possible to create a function for transactions that use more than one database?

As an example, below is a transaction. Would it be possible to write a function defining each database/table, so that in the future I could simply plug in the fields and corresponding values and have ...
1
vote
1answer
45 views

Grouping similar rows together based on time

I have a table that records all the changes done to a model by a user. The table looks something like this: (SQLFiddle) In the table an action_hash is generated based on the model, model_id and ...
3
votes
2answers
76 views

Better understand table indexing best practices

I'm not completely sure when would I start indexing my tables, when there are some criterias that I should consider: The tables are not that big, I think (1000 - 10,000 rows). The searches are made ...
1
vote
1answer
27 views

Constraint for a unique column if other column has same value

I'm wondering if it's possible to have a column be unique for each value of another column in MySQL. What I'm wanting to have is the same functionality as what a GROUP BY would do. Here's the table: ...
0
votes
2answers
34 views

Getting multiple out when using NOT EQUAL operator

How to display name of the department managers (fname, lname) who have no dependent? Tried this: Select DISTINCT EMPLOYEE.Fname,EMPLOYEE.Lname from EMPLOYEE INNER JOIN DEPENDENT ON ...
1
vote
0answers
27 views

Groupwise max then join

I have a votes table that has many subitems. I have a subitems action table that records what action was performed by which user on which group, on which subitem, on which vote Users can either ...
0
votes
1answer
55 views

mysql 5.7 json filter and features products

sorry for my english! I need use this column(feature) for show features, filters for products and in some language(It depends on the country). I have products table(innodb) and a column(json) ...
3
votes
2answers
55 views

Multiple Update with Multiple Conditions

We are told to watch out for things being set to null (we dont want) by using the IN clause in the following: UPDATE Tests SET TestScore = CASE WHEN TestId = 10 ...
1
vote
1answer
39 views

How can I make an Aging Report using mysql?

I have a table called supplier CREATE TABLE supplier_invoices ( id INT PRIMARY KEY AUTO_INCREMENT, supplier_id INT NOT NULL, date DATE NOT NULL, invoice_no INT NOT NULL, dr_amount DOUBLE NOT ...
0
votes
0answers
35 views

MySQL Upgrade query

I have 5.5.44-log MySQL Community Server (GPL) running in one of my RHEL 6.4 server. I need to upgrade the server to Percona Server-5.6.29-76.2. How to go about the upgrade? Should I completely remove ...
1
vote
1answer
42 views

Host not Allowed Connectivity to MySQL Server

I currently have a website and it references mySQL database. The website uses HTML, CSS and PHP. PHP code embedded inside HTML website, fetches a value and stores it on the SQL database. Then the ...
1
vote
1answer
23 views

Partition Wise Joins in Mysql

Is it possible to create or simulate a partition-wise join in Mysql/Innodb?? Here is a good description of partition-wise joins.... ...
0
votes
2answers
44 views

How can I create a join on three tables, each from a different database?

Here is a simplified example of what I'd like to do: SELECT a.create_time, b.create_time FROM DOB.dob_state AS a JOIN numID.numID_history AS b ON a.create_time = b.create_time; I'd like to join ...
0
votes
1answer
23 views

XAMPP: MySQL not starting

I was in the middle of developing my site and both Apache and MySQL were running normally until suddenly, the database failed to connect. I checked my XAMPP control panel and MySQL had stopped ...
1
vote
1answer
43 views

How to skip the current 'Relay_Master_Log_File' in AWS MYSQL RDS?

Below is the status of my slave. The master log file is at position "mysql-bin-changelog.278337" but the slave log file is at position "mysql-bin-changelog.278008". How to skip the current executing ...
0
votes
0answers
44 views

MySQL: So many prepared statements in general log file

One of my clients complained serious performance issues in a web-application written in Java. I was suspecting that there might be some unoptimized queries which are causing the problem. I've enabled ...
0
votes
1answer
26 views

Automatic Recovery of MyISAM table

I have an application in C language which inserts the report data in MyISAM table by using mysql_stmt_execute() function. Due to load on the table, it gets crashed. For auto repair of crashed table, ...
1
vote
0answers
27 views

What is the maximum value for the index_length of a MySQL 5.5 table?

I'm trying to find out why a series of inserts is painfully slow (1 minute 15 seconds, on average) on a dedicated MySQL 5.5.47 server, while the same inserts are normally fast (less than 5 seconds) on ...
0
votes
2answers
41 views

select only one element from an priority ordered group with limits?

This question searches for an answer in pure sql (no db-server specific code) -edit- Due comments/answers: "pure sql" - SQL92/99 because this works on all servers. I do not only use this code on ...
1
vote
1answer
14 views

Many-to-many tables tracking participation at multiple events

I am working on a side project to track observations of different birds, and the time spent during each observation. Right now I have a table called Observations: obs_id | start_time | end_time ...
0
votes
1answer
22 views

Remotely Connect to MySql from MS Access

Does anybody know if it is currently possible to remotely connect to a MySql server from MS Access? What does one need to do to go about doing this?
10
votes
1answer
111 views

Server responds with empty packet during session negotiation resulting in client giving a malformed packet error

I'm attempting to connect to a remote mysql server. This happens 100% of the time. client: mysql Ver 14.14 Distrib 5.7.12, for Win32 (AMD64) server: 5.0.95 This is the error I get: C:\>mysql -h ...
0
votes
0answers
21 views

Parent–child relationship and querying strategies

I am using a MySQL database with an InnoDB engine and primarily interacting with it via SQLAlchemy in Python. My models / tables are defined in the SQLAlchemy ORM that allows for several convenience ...
2
votes
2answers
25 views

MySQL lock on MEMORY storage engine, when does it apply?

I want to use MySQL MEMORY storage engine to handle my PHP session variables (actually I already implemented it but since all my tests are being done by a single user, me, I am unable to notice this). ...
0
votes
1answer
32 views

How to use sequential UUID primary key for range query by date

I'm using a sequential timebased GUID as my primary key in MySQL 5.6 in a transaction table with over 1 billion rows. The application is written in JAVA and GUID is generated as described here: ...
0
votes
0answers
5 views

PDO does not fetch any results

I was using PDO queries directly in index.php file.Now I want to create separate file where I will store functions. I created one, but when I call function returns only "0". Old version works: ...
1
vote
1answer
58 views

MAX of a varchar column

I have a table with a column named price typed as varchar. I want the maximum value of this column. I am using this query: SELECT MAX(price) FROM product_management.`item_list` but the result is ...
0
votes
1answer
22 views

Speed benefits of Mysql Multiple Delete/Insert vs Select

I am working on a web application which involves a regular API call to the Amazon Product Advertising API. Given that Amazon limit the calls to one per second, I am looking at the fastest ways to ...
0
votes
1answer
26 views

mysql selecting a distinct summed column while having

I have a one-to-many relationship between two tables, where I need to get distinct values out of a summed column b while limited by another summed field a SELECT t1.a - sum(t2.a) as a, t1.b - ...
0
votes
1answer
32 views

mysql CREATE TRIGGER after insert on Update column with count

it gives error. I don't understand why. CREATE TRIGGER `estatecat_piece` AFTER INSERT ON `estate` FOR EACH ROW BEGIN UPDATE estate_category set piece = (Select count(*) from estate where ...
0
votes
1answer
27 views

Is the syntax of Oracle 10g and MySQL the same?

Is the syntax of Oracle10g and mysql are the same? For example the select query? Thanks, I'm using MySQL and want to try Oracle 10g for my java.
1
vote
1answer
38 views

Select Units Sold and Total Units Sold from a Sales record table

I am trying to Select Units Sold and Total Units Sold from a Sales record table using. When I use this Query, I can get the Units Sold for '2016-04-14' SELECT salesrecord.itemID, ...
1
vote
2answers
33 views

InnoDB file per tablespace

I am using following syntax to create an InnoDB table: create table InnoDB_Test(a INT) DATA DIRECTORY='/var/log/storage/MySQL' INDEX DIRECTORY='/var/log/storage/MySQL' engine=InnoDB;` I want to ...
0
votes
1answer
23 views

Check is there is an Empty Column Value

Let's say we have a Table in MySQL Database called TAB1 that Contains two Columns col1 : col2 . the question is : is there a way to check is any of these Columns Contain a Null or Empty Value? for the ...
0
votes
0answers
12 views

Amazon Aurora Replication Error

I'm creating a replication between two Amazon Aurora Cluster following this: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Replication.html#Aurora.Replication.MySQLReplication.Monitor ...
0
votes
0answers
21 views

Is that hybrid model i thought with Mongo and MySQL fits?

I'm building a System with Node.js-Express.js and at first i decided to use MongoDB to store. But a moment ago i read that article and it made me think about it. Article talks about a MongoDB exp. ...
1
vote
0answers
16 views

Find entire hierarchy based in all hierarchy levels [duplicate]

I have a table that looks like this: Where EMP has a child SEG and it has a children SUP and so on... What I would like to do is find the full hierarchy EMP>SEG>SUP>DIR>REG>GOP in any of the ...
2
votes
2answers
61 views

Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen

I have a table with about 8.5m rows in it. The table is tokudb and it has the indexes described below. I'm experiencing dismal performance when trying to run update statements like the following: ...