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
13 views

Table cache hit rate: 0%

I have an AWS RDS Mysql Server with Table cache hit rate's problem. If I do a query on phpmyadmin (also simple query) always do it without cache. Here the mysqltuner log: MySQLTuner 1.6.12 - Major ...
1
vote
2answers
55 views

mysql - ALTER IGNORE ADD INDEX removed in mysql 5.7.4 - What to use instead?

I am trying to add an index to a table, but only if one doesn't exist. I don't know what the index name is. In the mysql manual I found the alter ignore command: ALTER [IGNORE] TABLE tbl_name [...
0
votes
0answers
11 views

storing email address and associated data

I want to save email addresses and for every email address a group of tags. For example: "a1@temp.com", 2 "a1@temp.com", 3 "a1@temp.com", 7 "a2@temp.com", 2 "a1@temp.com", 12 ... What is the best ...
1
vote
3answers
28 views

Why doesn't the following stored procedure compile with MySQL Workbench version 6.3.?

I would like to know how to make the following stored procedure compile with MySQL Workbench version 6.3. use puppy; DELIMITER $$ CREATE PROCEDURE spGetSystemLicensing ( IN _licenseName varchar(...
0
votes
2answers
20 views

Innodb_buffer_pool explanation

The title might be a bit misleading, as i am fully aware of the concept of innodb buffer pool, yet want to understand something specific about it's calculation process. According to the mysql docs: ...
0
votes
2answers
38 views

How to improve query count execution with mySql replicate?

For my report I have to use count queries (name 'user_activity' for example) and then group by by another column (user_type) in another table (user table). And its really time consuming when it ...
0
votes
3answers
33 views

Unable to replicate a database

I setup a master slave replication, where the master has the following setup datadir = /mnt/DATADIR/ bind-address = 0.0.0.0 binlog-ignore-db = information_schema,...
0
votes
1answer
35 views

How to stop MySQL from replicating CREATE TABLE… on Slave for ignored databases

Mine is a simple MySQL Master-Slave setup, with --binlog-do-db option as discussed in this article. As per the documentation, logs are written for DDL statements for all databases irrespective of '--...
0
votes
0answers
23 views

Need Help Shell Script Connecting MySQL DB

I am running this script to connect to the MySQL DB and achieve the following goal. Run the script for a set period of time. Run the Script for a set number of iterations within the that time. ...
0
votes
2answers
19 views

How to convert from one type of relationship table to another type

I currently have a relationship table where it's: documentID, dataType, dataID Where documentID is the link to the document file. The dataType is if the data is a specific type of user, for example ...
0
votes
1answer
16 views

Mysql database .frm and .ibd file read to understand

I want to read .frm and .ibd files in mysql to understand how mysql database engine store data in files. Which format those file does have, since when I open those files it show non-human readable ...
0
votes
0answers
14 views

MySQL compression on Google Cloud SQL

As the Google Cloud SQL is a managed instance of MySQL under the hood, limited to an absolute max on 2nd generation machines of 100GB of RAM, should I be compressing some of my table and index data ? ...
0
votes
0answers
21 views

Database design for Lending and Servicing

I am designing Database for Gas Cylinder Filling Firm. The firm collects filling fee from customers. The firm can also lend filled and unfilled gas cylinders to each customer. My drafted database look ...
0
votes
1answer
35 views

MySQL reads Date columns in Excel as DateTime?

I'm trying to export Excel sheets to MySQL as new table using the plugin. The export fails on "MySQL error 1064" when I tried to change the data type to Date. I have a few columns in Excel that are ...
0
votes
2answers
33 views

MySQL used all drive space running an ALTER and now won't start

I tried to alter a table without paying attention to the size, and MySQL ate up all the free space on the drive. I deleted a million or so old records but the space was still being used. Googling ...
0
votes
1answer
20 views

how can I create an where in query that only check on field and ignore another fields?

query is something like this: but I can not figure out how to get phones from inner select and ignore c field. select * from users where phone in ( SELECT phone, COUNT( id ) AS c ...
0
votes
2answers
41 views

MySQL: Indexing by column which is also a part of a compound primary keys and

I have a table which has a compound primary key, but I would also like to query the table using one or more of the columns composing this primary key. For example: CREATE TABLE `user_visit_stats` ( ...
1
vote
1answer
14 views

Getting the syntax right for MySQL select with inner join

Using MySQL version 5.1.73 and phpmyadmin 4.0.10.16. I am unable to get this past the syntax checker: select * from courses as co , course_type as ct left join course_leaders as cl on cl....
0
votes
4answers
48 views

Getting rows in left table only if not present in the right table

I need to get only those rows which dont have the corresponding entry in the right table advlabel , so I am doing a full left outer join : SELECT newparsed.seqid,newparsed.wordindex,label,arg1,arg2,...
0
votes
0answers
14 views

optimize multiple match against and temporary tables

I have a table of movies filled with text data. The query is create temporary table t1 SELECT *, MATCH(keywords) AGAINST('$mk') as score1, MATCH(genres) AGAINST('$mg') as score2, ...
-1
votes
1answer
20 views

Reducing column size and data type in mysql

I have a mysql table adv(id(TEXT),index(TEXT) ) which I need to change it as follows ALTER TABLE adv MODIFY COLUMN id VARCHAR(80), MODIFY COLUMN index INT(11); will this preserve all the data ...
0
votes
1answer
25 views

INSERT INTO returns ERROR 1452

I'm trying to move data from one table to another, but having an error: mysql> INSERT INTO activities_article (category_id, created_at, title, promo, content, promo_image_url, coordinates, ...
0
votes
1answer
15 views

How can I create a view with self referencing table and a cyclic condition?

I have a self-referencing table. ID BIGINT NOT NULL PRIMAY KEY, DELETED TIMESTAMP NULL, PARENT_ID FK How can I create a view of this table which each row's DELETED is NULL and any of its parent's ...
0
votes
1answer
19 views

Ways to Upgrade Production MySQL db server from 5.5.42 to 5.7.13 with zero downtime

Our product is an enterprise application with MySQL Enterprise version 5.5.42 deployed in many customer datacenters. We're planning to migrate the MySQL db (bundled with our product) to version 5.7.13....
1
vote
3answers
20 views

Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row?

Consider a table created like this: CREATE TABLE `someTable` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `owner_id` bigint(11) unsigned NOT NULL, `device_id` bigint(11) unsigned NOT ...
0
votes
0answers
25 views

MySQL JOIN is fast, but selecting column is slow

My logs table has half a million rows. Each row has an indexed log_level_id column pointing to table log_levels (with only 5 rows). On log_levels, id is an auto-increment primary key and label has ...
1
vote
2answers
83 views

INSERT and DELETE in single operation (moving from one table to other)

Stemming from this question, I am going to use that complex sort's results to decide which rows' data to move from one table to another. Though I have not yet tried it, I think there should be no ...
1
vote
1answer
30 views

Tolerating SQL errors (orderby not in distinct, select not in group by) in MySQL 5.7.13 vs 5.7.11

This question is related to the following 2 SQL errors (line breaks added by me) MySql.Data.MySqlClient.MySqlException (0x80004005): Expression #2 of ORDER BY clause is not in SELECT list, references ...
0
votes
1answer
17 views

MySQL: How to troubleshoot when simple queries are not returning anymore?

This InnoDB database has been up for a couple years now but in the last 24h it has experienced the same issue twice; I have to restart the mysqld service to make it respond to queries again. When it ...
0
votes
1answer
16 views

Migration of records to the table with different column names

I have two databases, and I need to move data from one to another. The catch is they have a table with different set of columns. Here is how I need to rename columns in migration: NULL -> ID (...
0
votes
1answer
13 views

mysql set auto increment to 1 with foreign key

I want to set auto increment to 1 by below command, but it doesn't works: ALTER TABLE tablename AUTO_INCREMENT = 1 I searched and found out that I should use TRUNCATE TABLE. But this command fails ...
1
vote
3answers
16 views

I try to use select union with order by and now i can select it but it seem like it not order by date with what i expect why?

SELECT * FROM ( SELECT sale.NAME AS "Name" ,sale.responsible AS "Responsible:data:200" ,sale.action_type AS "Action Type:data:200" ,sale.id_customer AS "Id_Customer" ...
0
votes
0answers
24 views

MySQL 5.7 JSON_EXTRACT: how to remove string quotes?

Other than doing string manipulation after each JSON_EXTRACT, is there a simpler or more correct way to have the JSON_EXTRACT return the string WITHOUT the enclosing quotes? Or should this question ...
0
votes
1answer
28 views

Move large mysql table data from one server to another

I have a table to which millions of data are inserted every day. As the table size grows more than 10 GB, I have issues with the performance of server in fetching apis and generating reports, which ...
-1
votes
3answers
19 views

Running mysql without password and default user in Ubuntu?

Currently, I start mysql 5.7 by mysql -u root -p then type the password. Is there a way to allow to run mysql only (i.e., without -u root -p) and it login as root? Thanks a lot,
0
votes
0answers
12 views

how to transform binlog row statement into SQL statement?

I know the conventional way to recovery, but I want to transform binlog row statement into SQL statement, is it possible +----+--------+-------+ | id | name | score | +----+--------+-------+ | 1 | ...
0
votes
1answer
20 views

Live Database Sync. MySQL - SQL Server

I have a Rarer scenario, Is there any possibility to achieve the target? Here is the Situation: I have Developed a Responsive site using php with foundation framework and the back-end is MySQL for a ...
0
votes
0answers
4 views

mysql: 'Zend_Db_Statement_Exception' SQLSTATE[42S02] table not found after copying db to new server

Using the latest xampp on win2012. Copied over several sql db's from old xampp system. When I do SHOW DATABASES they are all there. But the apps cannot connect to db / cannot log in properly. Are ...
0
votes
1answer
12 views

Maximum or suggested number of bound query-parameters in InnoDB (MySQL)?

We have code that adds thousands of parameters to a query as members of an IN (...) criterion. My aesthetics say to bucket it into multiple queries, but I want an objective reason. Does InnoDB have a ...
0
votes
1answer
34 views

Speed up ORDER BY and LIMIT in MySQL

I have a table with contact records, and clients wish to search this table to find records with a matching email address (filtered by account ID). The results are paginated by a RESTful API. This ...
1
vote
1answer
59 views

Error Code: 1248 Alias needed in MySQL

I have recently started learning MySQL and am trying to join data from two tables into a third table built. The two tables are: 'Company Financials' - Column of 'Revenue' 'Fortune500' - ...
0
votes
0answers
18 views

PDO, create temporary table and select seem not to behave as expected

From a php cli script, I'm trying to create a temporary table and populatie it if it does not already exists. This works but is not optimised. By design, this script may try to execute the MySQL ...
0
votes
0answers
21 views

Can unpredictable feasibility of schema downgrades be handled better?

I have a system backed by a MySQL database, installed via RPM on RHEL-compatible systems. The RPM will automatically run a sequence of "upgrade deltas" (.sql files) to migrate the user's current ...
0
votes
0answers
9 views

IGNORE_SERVER_IDS in config file

can we add IGNORE_SERVER_IDS variable to /etc/my.cnf configuration file in percona xtradb cluster or mysql server. i am forgetting every time while issuing change master to command while setting up ...
0
votes
1answer
29 views

PHPMyAdmin very slow inserts

I was trying to insert around 70K records to mysql database through phpmyadmin and was shocked of how slow it is. It took around 5 hours to complete! Please find below the details of the table/...
0
votes
1answer
36 views

Split a column at every whitespace

Let's say I got word transcriptions (space separated) in one column. Furthermore I got a set of legal phones. How can I split every transcription @ a whitespace to check whether all used phones are ...
-1
votes
0answers
51 views

Difference between Mysql table and histats analyzer

Explaining problem immediately. I have partly huge number of transactions (~ 30k per minute) and after putting analyzer system, weird issue comes up. say we have below psudo: Request received ...
0
votes
0answers
15 views

need to set mysql alerts

What kind of MySQL related alerts can be set on Linux CentOS 7 server. I'm using monit for all monitoring activity.All I have is standalone servers so no replication alerts are there. I already have ...
0
votes
0answers
13 views

Can a MariaDB slave have local databases?

I want to have a MariaDB Master-Slave implemented on two CentOS 7 systems. However, the slave replicates only one DB from Master. I want to use the slave to have a few other local databases too. Is ...
0
votes
1answer
24 views

Doubts on Setting up Master-Master replication (Percona)

I have two 5.6 Percona servers in which I'm planning to setup Active master- Passive master replication. Secondary master will be in read-only mode. Caveat is that the servers in which i'm going to ...