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

4
votes
2answers
12k views

Is there a way to stop MySQL Replication on the master?

I want the master to stop replicating data to the slave. I know I can do that on the slave with STOP SLAVE;, but I wonder if there is a way to do it in the master. One possible solution might be to ...
1
vote
0answers
1k views

How can I join one to one to (many to one) without using group_concat

I have a table structure as follows: -- -- Table structure for table `emails` -- CREATE TABLE IF NOT EXISTS `emails` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `email` varchar(255) COLLATE ...
1
vote
1answer
291 views

mysql ibdata1 file corrupted or increased unusually

We are using MySQL 5.2 version all InnoDB tables. I have observed that ibdata1 file got increased very unusually like occupied 500 GB in 2 days. In last 6 months, it had taken only 6 GB though ...
1
vote
1answer
304 views

Linux 32b MySQL periodically crashing with signal 11 and OOM errors [closed]

I have a MySQL DB with mostly InnoDB tables that I'm fairly certain isn't tuned properly based on what's happening, but I don't have enough know how to pinpoint what to change. The server is a 8G 32b ...
0
votes
1answer
232 views

How do I stop a query from running if the row is locked?

I have a section of code that locks the database, updates it and then confirms. This is all working fine, if another user attempts to update the same row they cannot and their changes are discarded. ...
1
vote
2answers
3k views

In MySQL Multiple instance, default instance is running, second instance is not

I was made a setup to create multiple instances on the ubuntu machine. When I start mysql instances mysqld1 is running but mysqld2 is not running. root@ubuntu:/var/lib# mysqld_multi report Reporting ...
8
votes
4answers
43k views

Call a stored procedure from a trigger

I have created a stored procedure in mysql using the following syntax. DROP PROCEDURE IF EXISTS `sp-set_comment_count`; DELIMITER $$ CREATE PROCEDURE `sp_set-comment_count` (IN _id INT) BEGIN -- ...
1
vote
4answers
1k views

Unsure how to implement certain constraints and relations in this relational database schema

I am in the middle of designing the dB for school project- airline ticketing database.(Using MySQL workbench) So far, I came up with the following design: Here are couple things I can't figure out: ...
0
votes
2answers
12k views

MySQL: Join one Row in a table with two Rows in other table

In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations): `users` `user_login` +--------------+ ...
1
vote
1answer
287 views

Equivalent of MRG_MYISAM in databases other than MySQL? [closed]

Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)? I know about fragmenting, but this is not quite the same AFAIK. We're using ...
2
votes
1answer
680 views

Replacing a PHP loop with a MySQL Query

I have a MySQL database table where I store a user_id and a hash each time someone logs in. The hash is stored in a persistant cookie so it doesn't change as users log in and out of the site. The ...
0
votes
1answer
2k views

Reinstall MySql but keep database tables and data

Please help! There are server issues and MySql is no longer running on our server (Ubuntu). The service is not recognized and needs to be reinstalled. Unfortunately, the database has not been ...
0
votes
2answers
2k views

Steps to upgrade MySQL from version 5.0 to version 5.5

I have been developing under MySQL v5.0 for a while now and need to upgrade to v5.5 to be in step with the hosted production server. After having searched for precise steps to do this migration, I am ...
1
vote
1answer
127 views

Issues with a Table having 120 fields in mysql

I am creating a table with 120 fields in MySQL. It will have 50 varchar(255) fields, other are maximum fields with char(1) , 1 date field and 5 double fields and others are int(11) fields. My ...
1
vote
1answer
109 views

Load balancing MySQL ndbcluster

I have successfully setup ndbcluster version 7.1.26. This contains 2 data nodes[NDBD], 2 mysql [MYSQLD] nodes and one management [MGMD] node. Replication works successfully. My Web application is ...
1
vote
1answer
2k views

MySQL: logging queries which would execute without using indexes

I am trying to use log_queries_not_using_indexes = 1 to find queries which are not executing optimally on a MySQL server. However, I find the resulting log file of rather limited value. Apparently, ...
2
votes
2answers
3k views

Loading data in mysql using LOAD DATA INFILE, replication safe?

I am trying to load data into mysql database form CSV file. I found that we could use LOAD DATA INFILE command to do it. But as per the mysql documentation it is not replication safe. (See here) Is ...
0
votes
1answer
405 views

Need ideas about OPTIMIZE TABLE

Looking at a database with 10 tables and fairly active at by-the-hour changes. On the first of each month, I purge some rows from 3 tables to remove outdated material and keep the size down. All of ...
5
votes
3answers
10k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
0
votes
0answers
451 views

RDS MySQL instance swapping lots of temp tables

I have an application that has few concurrent connections but does a lot of uncacheable fulltext searches sorting, limit with offset, etc. I have an xlarge instance with innodb buffer pool size @ 75% ...
2
votes
1answer
6k views

How to recover data from corrupted innoDB table from a crashed mySQL server?

Some background: I had a MySQL server running on Ubuntu Server. I don't remember which versions they were running but it was likely a Ubuntu 12.04 with MySQL 5.5. During the course of a day, ...
0
votes
1answer
58 views

How should I set up these tables in my relational database?

I'm working on a project where a person can create a survey with custom questions and display the questions in whatever order they choose. They can break the questions into sections. The problem I'm ...
2
votes
2answers
68 views

Backing up MySQL database

I have quite large and rather important DB to backup. First off, whats best way to do it? Secondly, easiest way I know is to export it through phpmyadmin, but, i'm worried about options, although ...
2
votes
4answers
2k views

search for something in the first 1000 records in the table

If I do the following MySQL query select * from table where name Like '%john%' limit 5; that mean the query will search all the mysql database table and return only the top 5 results that match the ...
1
vote
1answer
610 views

Redo Logs Mysql Cluster

The mysql cluster has 4 directories namely D8 D9 D10 D11 for redo logfiles. But while inserting into table only one redo directory is used. I ran following command to get redo information "all dump ...
2
votes
1answer
384 views

pt-table-sync error: Called not_in_left in state 0

I have setup a Mysql replication between 2 servers, using Percona Xtrabackup: Master is a MySQL 5.0.91 Community Edition (CentOS 4.8) Slave is a MySQL 5.1.68 Community Edition (CentOS 6.4) When ...
2
votes
1answer
1k views

MySQL statement-based replication and stored procedure

When you have a MySQL statement-based replication and you create a stored procedure that modify some rows (INSERT, UPDATE, DELETE commands...), What is really replicated on the slave when you call ...
1
vote
2answers
46 views

Order by on an alphanumerical column

I have a column that has strings of the following type: Miller 10 Allen 20 King 10 .... Jones 100 I try to sort the column based on the numerical part of the data. I tried the following: SELECT ...
0
votes
1answer
2k views

Optimizing ORDER BY for simple MySQL query

I'm trying to optimize this really simple query, and it gives me grief for a day now :( Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a ...
1
vote
2answers
1k views

MySQL: CAST + SUBSTR vs. FLOOR + MOD

I have a field in a mysql database where dates are stored as BIGINT like YYYYMMDDHHMMSS (PHP: date('YmdHis')). I'm building a query, where I check the amount of rows per hour and have tried two ...
2
votes
1answer
1k views

Trying to allocate 3G for buffer pool in InnoDB fails

I'm trying to boost my InnoDB driver on my Windows 7 PC since each page request takes just about 1.7 seconds to load versus 0.002 seconds on my Ubuntu 12.10 workstation. The setting I'm playing ...
0
votes
1answer
271 views

Large queries on MyISAM tables crashing MySQL

I have a PHP website, which is using the third-party software ResourceSpace, that when I installed it, it used my default storage engine to create the tables, which at the time was MyISAM. Now that ...
0
votes
0answers
23 views

Myisam Mysql server crashed

I have a server with linux fedora 11 Operating System. Recently , I discovered that every morning mysql has been crashed, so I have to restart mysql service. Can any body help me?
1
vote
1answer
2k views

Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?

We have a mysql database that has roughly 80 gigabytes (GB) of data using inno_db engine. We are looking to increase performance on the database (we looked at the slow log and gone through and ...
2
votes
2answers
1k views

mySQL data table seems to stuck at 16777215 rows

I am creating a MySQL table and loading data from a local file. CREATE TABLE `patent` ( `seq_number` mediumint(9) unsigned NOT NULL auto_increment, `seq_id` varchar(24) default NULL, `date` ...
0
votes
1answer
305 views

Memcache implementation

I have a Wordpress site that uses WP Super Cache plugin in order to make my blog posts and pages stay as HTML so that less PHP/MySQL code is executed. However, there's a featured box that can't get ...
0
votes
1answer
766 views

MySQL: replicating to a different table engine type

According to the replication documentation from MySQL, it is possible to set up replication from InnoDB source tables to MyISAM destination tables. Unfortunately, the documentation has little to say ...
0
votes
2answers
87 views

Error log - What does this mean and what can I do about it?

I have the following repeated errors in my error.log and I don't know what it means or what I should/can do about it: Access denied for user 'root'@'[some IP]' (using password: YES) There are ...
4
votes
1answer
376 views

High amount of Read Misses and Pages To Be Flushed

I am running a mysql database backend for a Moodle installation, and after a few months performance really starts to suffer (Up to 30 seconds for some pages to load). Under investigation in the InnoDB ...
1
vote
1answer
4k views

Getting the data from a foreign key table

I have a table that relates a user to client using both their table id's UsersClients Table --------------------- | userid | clientid | --------------------- | 2 | 32 | | 3 ...
1
vote
2answers
1k views

VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed?

For a log table, I have a custom message column. It will never be updated. It will be searched with LIKE conditions. It will be capped at 1KB. TEXT fields are slower than VARCHAR fields for ...
1
vote
1answer
136 views

Proper indexes or anything else to optimize

I need help to optimize the query to avoid using "Using temporary", "Using filesort". CREATE TABLE `target_bahrain_pepsi` ( `id` int(11) DEFAULT NULL, `col_value` varchar(50) DEFAULT ...
-2
votes
2answers
5k views

How can I search for a name in two columns in MySQL? [closed]

I want to search a name 'Hamed Haddadi'. 'Hamed' is first name and 'Haddadi' is the last name of the user. When I search individually in the first name and last name columns it shows a record. My ...
2
votes
1answer
39 views

Which MySQL directories to back up using FTP

My server has been hacked and I only have FTP access to recover my data before reinstalling the OS. Which/where are the directories that needs to be backed up? Will MySQL be recovered back to its ...
4
votes
1answer
3k views

What are the disadvantages if indexes are added on all columns?

I know, that it is not a good design to set too many indexes, but just for understanding the theory: What exactly are the disadvantages if you add an index on all columns in a table in MySQL? Are ...
3
votes
1answer
2k views

Db design for historical option data (~200GB)

I need some help determining how to best design my tables. I have about 200GB worth of data I wish to place in a table (historical option price data). The table looks something like: String symbol ...
2
votes
1answer
82 views

Do I need to reenter old data after adding an index to a table?

I want to add index to my tables. Some of my tables already have couple thousand rows. Do I need to reenter my stored data after adding index to columns (to make them aware of index/so the indexing ...
3
votes
2answers
4k views

How can I improve this nested MySQL query to avoid redundant WHERE statement?

I am trying to improve on a nested MySQL query, using the following table structure (omitting a few dozen not relevant to my question): tbl_users: ID | email (all subscribers to our email list) ...
0
votes
1answer
714 views

Mysql suddenly stopped after is said to be crashed

We have a mysql instance running well and suddenly it stop. I have gone through the logs file but found no indication why it stopped? But there have some instance start and stop before this? Below is ...
2
votes
3answers
2k views

do MYSQL views occupy physical space?

do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?