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

13
votes
3answers
23k views

mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
0
votes
0answers
5 views

Rows as columns in MYSQL

My table, t, has the following format: I would like to transform this table into: The COUNT_XXXXX columns are determined by counting the number of rows where CLICKSKEY is not NULL organised by ...
0
votes
3answers
11 views

where clause return empty row in view mysql

i have two tables with the schema below: INCOME TABLE: INCOME_ID, INCOME_NAME, INCOME_DATE OUTCOME TABLE: OUTCOME_ID, OUTCOME_NAME, OUTCOME_DATE EXAMPLE DATA INCOME INCOME_ID | INCOME_NAME | ...
0
votes
1answer
218 views

mysql Replication and Point in time Recovery

I have a master and a slave mysql database (version 5.1). Is it possible to dump the database only on the slave side? If I do a dump with mysqldump I have a fulldump. Which files can I use for a point ...
1
vote
0answers
19 views

Inserting 30k prices each minute, how can I skip an entry if the price did not change?

I receive around 30k price updates for various articles every minute and I need to write the new prices into our database (Which is a MySQL Database running InnoDB) The table containing the prices ...
1
vote
1answer
27 views

Pass a parameter from SSRS to MySQL [on hold]

Here is the query for the dataset: SELECT DISTINCT CompanyId, Company FROM User_Hierarchy_Mapping WHERE UserId = @FieldManager FieldManager is the parameter name. It's not ...
1
vote
3answers
97 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 ...
2
votes
1answer
266 views

Mysql: How can i make a host show up by hostname in 'show processlist' rather than by IP address

THought this might be something controlled via rdns but updated that and still getting connections for a couple machines showing in 'show processlist' as the ip address (and port) rather than hostname ...
2
votes
1answer
326 views

InnoDB internals - checkpoint, LSN, dirty pages

I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage. However there isn't enough information to answer my question. When I run show global status, I can get ...
0
votes
1answer
131 views

MySQL UTF-8 Encoding Problem

I have two MySQL databases that are for Wordpress websites. One db is for a live version of a site, the second is a copy of the original that is used in conjunction with a copy of the original site. ...
1
vote
1answer
804 views

MySQL: SELECT GET_LOCK slow/timeout

I am using "Zebra Session" as a replacement for PHP's built-in session management. Zebra Session stores sessions in a MySQL database, and uses "session locking" in order to ensure that data is ...
0
votes
1answer
58 views

Generate a column from different rows in sql

I have a table that looks like this: SKU DateOut DateIn Status 123 1/2/2015 5/2/2016 1 123 10/2/2016 18/2/2016 1 123 ...
2
votes
3answers
10k views

Mysqld.exe memory usage

I am using mysql 5.6.14, For a long time i got large memory usage by mysqld.exe, i was trying many different solutions and my.ini settings to fix this up but didn't succeed, This is my config file(my....
0
votes
0answers
14 views

MySQL / MariaDB change feeds

Is there a way to get a notification when a row in a table is created or updated? Much like RethinkDB's change feeds. Anything would work really - a message on a WS channel, or a HTTP request with ...
0
votes
0answers
13 views

DB Configuration Advice [on hold]

I need an advice circa databases' utilization. Which is the best configuration and why: 1) MySQL + CouchDB + ElasticSearch for CouchDB and MySQL indexing; 2) MySQL + Cloudant (previously ES + ...
-4
votes
0answers
13 views

Mysql 5.7 database [on hold]

I have a database in mysql, which yesterday didn’t open - Table is crashed and last repair failed. Which error popped up and after I opened database 2 times, and the same issue. How can I make mysql ...
1
vote
1answer
391 views

Slave Waiting to reconnect

I have added new slave host to master for replication, but missing some of the parameters. mysql> show slave status\G *************************** 1. row *************************** ...
0
votes
0answers
21 views

Structure a database for a Blog

I built a database for a blog. I am a student yet, so my knowledge is limited in this area. I post this question to get a briefly description of what I do wrong, why, and how I can fix it. I'm not a ...
0
votes
0answers
5 views

Match a column against a sub-string in natural language mode in MySQL

I need to search in a product's table for their names from a text input and show the data in a table using Ajax, the search results are almost perfect, execpt that we have to type all the word. I ...
1
vote
1answer
58 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 "...
1
vote
3answers
709 views

Why does this procedure raise a privilege error?

I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine. QUESTIONS Could somebody help ...
0
votes
0answers
26 views

How is the better way to manage the database change?

Scenario. I have a Production, Staging and Develop environment. In the development environment we develop code to fix a certain issue and maybe perform DB changes if necessary. Now we log the DB ...
0
votes
1answer
351 views

Using non-replicated read/write databases on a MySQL slave server

I have a MySQL slave server that has two databases being replicated from a master. What I'd like to do is add a third database that I will exclude from replication and use as a read/write database. ...
1
vote
2answers
342 views

MySQL: Merge Same Database on Several Location into Single Location

I have registration form, which exactly same for several location. Each of them has REG column, which this field is unique for each row. Basically each data input have different REG number for it, ...
6
votes
1answer
27k views

Converting XML to MySQL tables

I have a large XML file in the following format and I need to convert this file into a MySQL table. Please let me know how can I accomplish this? ~~~~~~~~~~ <host starttime="1392325468" endtime="...
0
votes
1answer
310 views

SET sql_log_bin = 0 in RDS MYSQL 5.6

I have moved into RDS MYSQL 5.6 and now I have a problem. I have 5 read replicas and a/z master. I am trying to alter a table and want to change a column value and also on some tables I want to add a ...
-2
votes
0answers
18 views

Email having empty body [on hold]

I have a table named 'email_queue' that contains the details of the email. (recipient's email address, subject, bcc/cc, body) and there's a bug where it sends to the right recipient but the body is ...
0
votes
1answer
195 views

New Master-Master replication has stopped working

We're building a new MySQL server pair for high availability, and of course it's not like managing a single server. We're working with Ubuntu 14.04 LTS and MySQL 5.5.43 I had master-master ...
0
votes
2answers
20 views

import geonames allCountries.txt into MySQL 5.7 using LOAD INFILE - ERROR 1300 (HY000)

I'm trying to import the allCountries.txt file from http://download.geonames.org into MySQL 5.7 using LOAD INFILE but getting the following error: ERROR 1300 (HY000): Invalid utf8 character ...
1
vote
0answers
12 views

`t2.t1_id IN (select t1.id from t1 …)` is slower then `t2.t1_id IN (1,2,3,…,150)`

Query with subquery is much slower: SELECT COUNT(*) FROM `t2` WHERE `t2`.`t1_id` IN (SELECT `t1`.`id` FROM `t1` WHERE `t1`.`site_id` = 5978) AND (t2.created_at >= '2016-02-13 08:00:...
0
votes
2answers
254 views

Logging the Execution times of dynamically constructed mysql stored procedures

I have a MySQL Server. The MySQL server contains a huge network data collection. The client API's will connect to the MySQL server and execute some dynamically created stored procedures and delete ...
3
votes
1answer
152 views

SQL Freezing on Temp Table Creation

I have done an update to my server yesterday to allow for tracking of user page views. These page views are sent to a database where it records their ip address, time and which page was viewed. On ...
1
vote
1answer
20 views

Many-many relation with specific type

Sorry if this question has already been asked, but I didn't find it performing a search. I have 2 tables (table_a and table_b), linked by a many/many relation. The relation can be of two types, ...
4
votes
2answers
35 views

Howto implement correctly Businness Logic in database

I'm searching about how to disable triggers, and seems like it's not possible under MariaDB. My problem is, how to manage then, when you import data. I have a database where almost all business logic ...
1
vote
0answers
13 views

Is there a way to synchronize mysql databases that are missing a few days worth of data

I have a primary-primary MySQL server setup that is connected to a load balancer. At one point, a failure occurred to where the replication wasn't happening and data was being written to one server ...
0
votes
0answers
14 views

MySQL Schema Design: One Possible Values Between Multiple Columns

I'm working on a schema for a MySQL database. I have tables which are derivatives of one another. For example, rather than a single "animals" table, I might have separate "mammals" and "birds" tables: ...
1
vote
1answer
20 views

MySQL “CREATE TABLE LIKE” include triggers

I'm using MySQL 5.6 (5.6.29-76.2-56-log Percona XtraDB Cluster) When I issue a CREATE TABLE LIKE the triggers on the tables are not copied - is it possible to get them on the new table as well?
0
votes
1answer
40 views

Which is more efficient: A join or a group by?

I've got a table A with a relation to another table B, where each A row has two rows in B. I want to extract all the values from a specific column in B for each row in A. Ignoring the difference in ...
7
votes
1answer
7k views

How should I interpret the “lock time” within mysql slow query log?

I'm trying to understand how to best interpret the lock time for the queries which show up on our MySQL slow query log. For instance if an UPDATE query has 10 second lock time. I presume it is the ...
0
votes
1answer
136 views

Update records that match like regex from another table

I have two tables - ProductCategories and Products ProductCategories --- productid categoryid 1 2 3 4 Products -------- productid productname 1 Apple 2 ...
1
vote
1answer
27 views

Sql get results only containing all keywords

Let's say I have mysql table called SONGS, with columns: Artist | Featuredartist | Songname | Year What I need is to get Artist and Songname, only containing my keywords (and no matter how they are ...
3
votes
2answers
1k views

Trying to recover/import/transfer database from MySQL data dir

I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused. However at the time of doing the export I ...
0
votes
2answers
35 views

Mysqldump failing while restoring database - MySQL 5.6

I have dumped one of my databases of size 5-6GB. I'm trying to restore to another instance but the same is getting failed after sometime with the following error: ERROR 2013 (HY000) at line 12006: ...
0
votes
0answers
16 views

Storage Engine in MYSQL [on hold]

What are the advantages of TokuDB storage engine in MYSQL ? How do you optimise tables with TokuDB Storage Engine ? Is it the same as OPTIMISE TABLE Tablename or any other optimisation query specific ...
0
votes
2answers
146 views

Does MySQL Workbench lock database during migration process?

I am doing a migration of my database from one server (RDS) to another (localhost) via the MySQL workbench tool. I want to know if during this process my database will be locked for write and read ...
-5
votes
0answers
44 views

Retrieving the data based on id from table contains 2 million rows [closed]

I have one table/collection contains 2 million rows/document (id(20 alphanumerical digits), name, ... etc). From that table I want to retrieve data based on the id within 1 sec Which database ...
18
votes
4answers
80k views

Access denied for user 'root'@'%'

I used to access the root user in MySQL just fine. But recently, I am no longer able to. I am able to login fine : mysql -u root -p Here is the mysql status after login : mysql> status ------...
0
votes
1answer
127 views

Laggy slave OK to have only one system user in processlist?

In all the examples of a processlist on a mysql slave I've seen, there are two system users. I have a slave that is very far behind the master. When I do show a processlist, there is only one system ...
0
votes
0answers
11 views

Unable to rename table due to metadata lock [on hold]

I am trying to rename a table but it always just hangs with a Waiting for table metadata lock. Here is how I am trying to do it: start transaction; RENAME TABLE `main_territorypricing` TO `...
0
votes
1answer
608 views

MySQL 5.6 replication causes 'waiting for table lock'

All of the sudden queries on slave server stopped with status "Waiting for table level lock" I restart mysql service and stop replications and locking does not show up anymore. Once I turn ...