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

0
votes
1answer
208 views

MySQL - When Command column in Processlist shows “Prepare” or “execute”

Can someone please highlight when a "Command" shows "Prepare" or "Execute" for "Show Full Processlist" Any example which shows this would be helpful.
0
votes
0answers
4 views

Tag system for multiple Item types and groups Schema

I have a groups table and users table, and a many to many relationship between them. In my app, there are different modules, like question, product,suggestion , etc. Users can create ...
1
vote
1answer
274 views

Select only rows that equal x but not x and y?

I'm having quite the challenge with selecting hierarchical data in MySQL although I feel the schema should be optimized for it since it's based on the WordPress database model. I've got 4 tables: ...
1
vote
1answer
238 views

Query multiple rooms with different conditionals (Adults, Children)

I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per ...
0
votes
2answers
5k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
0
votes
0answers
13 views

Freezing Centos server

My dedicated server (CENTOS 6.4) freeze every two day and I have some trouble to found te origin. What I know : This is not hardware issue as tests were made about (RAM, CPU, Discs, ...). At the ...
2
votes
2answers
832 views

tmp_table_size with mostly InnoDB tables

I use MySQL version 5.6.25-0ubuntu0.15.04.1 on 64-bit Ubuntu 15.04. I have 2GB RAM and the disk is SSD (so, in my mind, writing to disk isn't that costly). I currently have databases for just two ...
1
vote
1answer
634 views

Report Builder cannot publish to report server

This is currently in a cloud lab environment. We're not allowed to join any AD domains. We have a linux server (RHEL 6) running MySQL 5.3 and a Windows SQL Server 2012 on Win 2k8 R2. Our laptop is ...
0
votes
1answer
107 views

MySQL PHPMyAdmin Autocommit Off

I have global autocommits off but I was wondering if it were possible to set autocommit on for PHPMyAdmin during login without doing so manually. Turning off autocommit makes the PHPMyAdmin GUI not ...
0
votes
2answers
276 views

Remove duplicates from multiple joins

I'm using WordPress $wpdb->get_results for this query (it is simplified for question purposes): SELECT customer.id, customer.name, address.address appt.appointment_date, pay.date FROM customers ...
0
votes
2answers
32 views

Sorting by month by chronological occurrence when the year doesn't matter?

Given a table with a date column, and given a query that selects dates in a given range of the year (like "November 20th through February 20th"), and where the year portion of the date doesn't matter ...
4
votes
1answer
203 views

Query to determine start and end dates based on time overlap

Given the following data: id | user_id | started | closed | dead ------------------------------------------------------------------------------------------- 7714 ...
0
votes
1answer
21 views

Creating trigger to manipulate language

This is the table that I have. Effective date is a user input, then I need to use a trigger that produces Print depending on the language the user inputs (English or French only). For English, I used ...
0
votes
2answers
24 views

Give third-party access to AWS hosted MySQL

I have a production MySQL database hosted on AWS for an eCommerce store I work with. Employees in the firm are requesting access to our live SQL database to use with BI tools like Tableau/Qlikview. ...
8
votes
1answer
717 views

Issues with MySQL installation

First of all, excuse the length, this post is very long, and needs some background, second; it has been hard for me discerning the signal from the noise, please bear if some of this is not relevant: ...
1
vote
0answers
10 views

pub/sub database schema

I am having a hard time creating a pub/sub database schema with stateful metrics. Saying it aloud it sounds easy enough: "There are many organizations that will subscribe to checklists that are ...
0
votes
0answers
25 views

Update column with different value for multiple rows [on hold]

I often find myself using Excel to create update queries. When I need to update multiple rows with different values for a column, I simply use Excel to create the update queries (multiple). Here's ...
1
vote
1answer
38 views

Delete the maximum ID row [duplicate]

How can I find duplicate rows and for each group of duplicate rows delete the max ID row, in MySQL? Using this query, I can find duplicate rows: SELECT * , COUNT( awb_no ) c FROM tbl_cod_rpt_COPY ...
1
vote
0answers
16 views

Getting InnoDb internal Errors on every query run

I am having following errors in error.log at the run of each query due to which I am not able to use error.log properly. 2016-05-30 09:39:58 7f7c7e3cb700 InnoDB: Error: Column last_update in table ...
0
votes
1answer
521 views

Exclude IP from being blocked by MySQL

I have a mysql server running. Due to the uptime is not perfect, sometimes the HTTP server is blocked when mysql server is down intermittently. Host 'host_name' is blocked because of many connection ...
0
votes
0answers
13 views

Mysql/pivot table for two tables

I was trying to get multiple rows into single row with dynamic columns created as per the data.i have two tables combined with foreign key.here is my data follows table 1: | id | name | Invoice value ...
0
votes
1answer
79 views

Optimize query with similar fields

I have the below schema, fields owner_id,billing_id,tech_id,admin_id on carts table are pointing to contacts table. As you can see from the query result I have to JOIN for each field .Is there a way ...
3
votes
1answer
159 views

How to run OPTIMIZE TABLE on a 3-node cluster?

I've my hands on a 3-node Percona XtraDB Cluster where, according to mysqlcheck, some tables are corrupted (some indexes contain the wrong number of entries): mydb.mytable Warning : InnoDB: Index ...
1
vote
1answer
259 views

mysqld keeps automatically restarting

I am running mysqld on Mac OSX Al Capitan. After boot up, I ran mysql.server start to start mysqld server. But when I ran mysql.server stop, or sudo mysql.server stop, it just stopped for a while, ...
1
vote
1answer
23 views

how to upload a file on a mysql server if access to temp folder is not present [duplicate]

I have a CSV file that i want to enter into a table in my database which is present on a remote server. But presently I don't have access to temp folder so I cannot copy my file on that folder. Is ...
1
vote
1answer
45 views

Using indexes in ORDER BY MySQL

Here i have table with indexes and more than 700000 entries. CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` char(255) COLLATE utf8_unicode_ci NOT NULL, `cat_id` ...
1
vote
1answer
1k views

MySQL cluster error: “Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock”

I am trying to configure MySQL cluster under three Windows 7 machines with disabled firewall, cluster information is like the following: 2 hosts with each running 1 data node 3rd host running ...
1
vote
2answers
2k views

Which one to use? InnoDB Replication or MySQL Cluster?

I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual ...
1
vote
0answers
28 views

Null instead '0000-00-00 00:00:00' in mysql 5.7

Before: mysql 5.6. One of the columns in the table is created as follows: `f_stamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' When i view this field in mysql client or using DataGrip, this ...
-1
votes
2answers
29 views

How to perform multiple checks on same column of same table in Mysql? [on hold]

Table Structure : +-------+--------+-------------+------------------+---------------+----------------+ | recid | cartid | attributeid | attributevalueid | attributename | attributevalue | ...
1
vote
0answers
13 views

Add index to mysql table with ndbcluster engine

I'm having trouble while adding index on mysql table with ndbcluster storage engine below is the error . [mysqlcluster ver. 7.4 GA] mysql> alter table `sks_staff_office` add index `pid_index` ...
0
votes
2answers
245 views

Replace a specific part of a string using MySql

I have many Strings in the database Like <iframe src="https://www.youtube.com/embed/-qTiYA1WiY8&index=8&list=PLPNcLSfYNC3Dw535smncyHRCBRmdf1Bti" frameborder="0" ...
2
votes
1answer
5k views

Amazon RDS - Can't connect to MySQL database server?

So I've been working on this issue for a while now, and I'll explain what I'm trying to do. I'm trying to connect to a MySQL database server, hosted by Amazon Web Services in the Amazon RDS service. ...
0
votes
1answer
254 views

MySQL Cluster work well but ndb_desc doesn't work

I have deployed and configured MySQL cluster. It works fine. ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration [ndbd(NDB)]2 node(s) id=2@192.168.56.2 ...
-5
votes
1answer
40 views

Could someone explain how PHP, MySQL, SQL work together? [on hold]

I feel so confused on what they are and how they work. From what I've read PHP is a server-side language. Therefore, it's a language basically designed to talk with the web server to get it to do ...
4
votes
1answer
548 views

Partialy Solved - Problems Deleting Data

I did a workaround that solved the problem for today but I want to know what I did wrong for the next project. I'm stuck on trying to delete some information on my MySQL database. I've never used a ...
0
votes
0answers
13 views

What is the difference between XtraDB and TokuDB? And why are there no comparisons? [on hold]

I'm just getting into DB and doing some search on different MySQL tables. My current company's tables are all old and in MyIsam form. At first, I was going to simply convert to InnoDB, then noticed ...
0
votes
1answer
49 views

How to handle HUGE 16GB SQL file?

I've abc.sql file exported from MySQL Table. This abc.sql file is 16 GB in size with around 28 million insert statements. I'm looking for option to store it in SQL Server Database. Couple of ...
1
vote
2answers
83 views

Index not used on partitioned table

We have a table similar to this one mysql> SHOW CREATE TABLE my_requests\G *************************** 1. row *************************** Table: my_requests Create Table: CREATE TABLE ...
1
vote
1answer
59 views

Composite Primary keys and auto increment?

i'm developing SaaS app with multi-tenancy, and i've decide to use single DB (MySQL Innodb for now) for client's data. I chose to use composite primary keys like PK(client_id, id). I have 2 ways ...
0
votes
0answers
38 views

MySQL - Database Design : Encrypting user id and creating a mask for further internal references

For a food order delivery system that I am working on, I want to make sure my user's private information is well protected; from system admins too. So I thought of coming up with this concept of ...
2
votes
1answer
193 views

Mysql high cpu usage - cpanel

After an update from whm, I see a high CPU usage from MySQL (5.6). my.cfg: [mysqld] slow_query_log=1 slow_query_log_file="/var/lib/mysql/slow-query.log" long_query_time=1 skip_name_resolve ...
0
votes
2answers
52 views

Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE

I have 2 tables params and params_exceptions where params is supposed to be updated frequently by an external script. INSERT INTO params (extension, type, subtype, paramvalue) VALUES ('12345', ...
1
vote
1answer
151 views

Fastest engine for Mass Updates (only updates done on table)

I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the ...
1
vote
2answers
123 views

MySQL Junction Table as Foreign Key?

I have several tables: company, customer, company_has_customer, and transaction. company_has_customer is a many-to-many junction table using the PKs of company and customer as its PK. In almost all ...
1
vote
1answer
155 views

mysqldump collation issues

I have tried my hand at building a shell script to backup our wordpress database using tar balls and chron jobs on a Debian environment. The problem I am facing now is that our wp database after ...
1
vote
1answer
13 views

mysql replication and GRANT that doesn't quite grant

I am trying to set up mysql replication. All's well, but I see an error 1045 for the slave: mysql> show slave status\G *************************** 1. row *************************** ...
0
votes
1answer
483 views

How to check mysql password change and other events logs

Recently someone change my phpmyadmin password and drop database I think it was sql injecting. Now I want to find information about this like when, how and who change my password Please tell me ...
0
votes
1answer
17 views

Grand read privilege to the user but is not allowed to grant the same privilege to another user?

i am a noob and this is how far i got so far, thanks and also how would you do it if you want to allow user to grant the same privilege to another user. GRANT SELECT ON DATABASE_NAME.* TO USER;
0
votes
0answers
23 views

Speed up SUM() on a result with JOINS in MySQL

I have a query with a combination of 6 left joins and inner joins. The query gives a result of 25000 records in 112ms. When I do a SUM() the query takes 4s. A SUM() another table with 35000 records ...