MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
2answers
55 views

Freeing up disk space in 'mysql\data'

I am trying to free up some space used by a MySQL instance, but I know pretty much nothing about MySQL. I notice that in the mysql\data folder there are lots of localhost-bin.0001** files, as ...
1
vote
1answer
57 views

Slow queries InnoDB occasionaly

I have the next table: CREATE TABLE `sessions` ( `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL, `session_expires` int(11) NOT NULL DEFAULT '0', `session_data` varchar(10000) COLLATE ...
1
vote
1answer
22 views

Is there any value in adding an already indexed column to a composite index?

The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE ...
1
vote
1answer
41 views

Adding new tables — memory usage increases

I added more tables to my mysql server. 20% increase in tables that are all innodb. I have 200 + databases that use the same schema. Does adding more tables increase memory usage? I am using about ...
1
vote
2answers
160 views

Securing Your MySQL Server within your team?

Recently I have been having an issue with our internal team members changing the password of the root user. I noticed the following things: OLD_PASSWORD = 1 (So all passwords on our development ...
1
vote
1answer
57 views

Is there any way to get the estimate cost of executing a query in MySQL?

In PostgreSQL, EXPLAIN or EXPLAIN ANALYZE will show the estimate cost of executing a query. But EXPLAIN in MySQL doesn't provide this information. How can I get the estimate cost without installation ...
1
vote
1answer
68 views

creating table result in errno 150

I'm trying to create the following schema, but I'm getting errors when I'm trying to create project and staffphone tables. Not sure what is it complaining about. mysql> CREATE TABLE staff( ...
1
vote
1answer
78 views

Migrate from innodb_file_per_table to off in MySQL

We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large ...
1
vote
1answer
74 views

How does the transactions interact if you set a different isolation level per connection?

I read that I can have different isolation levels per connection and in the server. The default isolation level is REPEATABLE-READ. So if I have a transaction that issues a SERIALIZED to the client ...
1
vote
1answer
236 views

MyISAM with Innodb in the same Wordpress database reduce performace?

My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine. I use MySQL ...
1
vote
3answers
2k views

How long will a temporary MEMORY table persist if I don't drop it (MySQL)

I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary ...
1
vote
3answers
255 views

What happens when we take a timediff between timestamp and datetime

Say I have following table in mysql (5.5+): CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL, `x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ...
1
vote
0answers
1k views

mysql 5.6 GTID replication 'Got fatal error 1236 from master when reading data from binary log'

I Checked SHOW SLAVE STATUS error is "'Got fatal error 1236 from master when reading data from binary log: ''The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master ...
1
vote
2answers
98 views

Is column order reliable in UPDATE?

Can we reply on the order of columns in an UPDATE? SET @x = 0 UPDATE table1 SET col1=IF(col1 IS NULL, NULL, @x:=@x+col1), col2=IF(col2 IS NULL, NULL, @x:=@x+col2), col3=IF(col3 IS NULL, NULL, @x:=0) ...
1
vote
4answers
104 views

Amount of data in a database table

I am trying to run an alter query on a table, and its giving me a Lost connection to MySQL server during query MySQL server has gone away. The query that I am running is alter table table_name ...
1
vote
1answer
4k views

MySQL import csv file ERROR 13 (HY000): Can't get stat of /path/file.csv (Errcode: 2)

I am an absolute beginner to MySQL (5.5.34) / Linux (Ubuntu 12.04 LTS)` I have created a simple database with one table. When trying to import data into it via a 'data_test.csv file an error message ...
1
vote
1answer
95 views

Is there a Successor or an Alternative to mk-parallel-dump?

Just read about mk-parallel-dump in a possibly-outdated version of High Performance MySQL. Sounded great, but when I went to download it I found out that it's deprecated with no equivalent command in ...
1
vote
0answers
79 views

problem installing mytop , need a up to date tools for monitoring mysql

so i'm trying to installl mytop to monitor the database i'm following this instruction : http://www.sohailriaz.com/how-to-install-mytop-a-top-clone-for-mysql/ when i try to install it with yum i get ...
1
vote
1answer
167 views

MySQL auto_increment delivers different primary key in MyISAM and InnoDB

I have a table with auto incremental primary key. If I want to change a row and add new rows I got different primary keys depending on engine MyISAM and InnoDB. The original table is: pk.. ...
1
vote
1answer
920 views

filling a table by triggering multiple tables on insert

I have a table m contains the following fields: id int(11) not null auto_increment, //PK name varchar(100) null, something_else varchar(100) default 'not important' I need to fill up table m by ...
1
vote
0answers
165 views

invalidating query cache entries(key) [duplicate]

when I tried to delete some records from a MYSQL server (version 5.0.95-log) and checked the process list I got the result as follows mysql> show processlist \G *************************** 2. ...
1
vote
1answer
67 views

How to delete duplicate records

I'm able to find duplicate records using the following query, however, I'm not sure how to delete duplicates records, and ignoring any records that starts with 0 in starttime field. SELECT ...
1
vote
1answer
145 views

In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index

Let's assume that I have table data: CREATE TABLE `test`.`data` ( `row_id` INT NOT NULL , `data_id` INT NOT NULL , PRIMARY KEY (`row_id`) ); Now, let's assume that data_id has unique values. Is ...
1
vote
2answers
555 views

INT or TIMESTAMP for the change time column in a logging table?

We're having an argument over what to use for storing change date in a new log table for our site. One side says, use INT for the timestamp, storing the UNIX timestamp. We can convert the datetime ...
1
vote
1answer
2k views

Get top most parent by nth child id?

Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the ...
1
vote
1answer
825 views

Is it possible to force drop of mysql tables with FK?

For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table. Is there a short way to drop a table, ...
1
vote
2answers
113 views

Where condition execution sequence in MySQL

I am using MySQL 5.0.88. I have a query like: select DISTINCT party.­id FROM party­ LEFT join party_­identifier AS pi ON pi.­party_­id = party.­id­ WHERE ­(pi.­source_­note=? AND ...
1
vote
1answer
246 views

How to add additional tables/fields into a query with multiple inner joins?

Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result? SELECT ...
1
vote
1answer
272 views

Select records that do not have associations outside a certain list

I have a mySQL table of nodes and each node can be associated with many categories. I have a list of category ids that are acceptable (498, 499). I want to select only nodes who do not have ...
1
vote
1answer
249 views

MySQL localhost vs Amazon RDS instance

I am seeing unexpected behavior with some MySQL performance. When I run a simple query SELECT 1; on my local host (MySQL 5.6.x) using workbench, it executes in 0.000s, but the same query run on ...
1
vote
1answer
1k views

Varchar index - will hashing value make it faster?

I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
1
vote
1answer
226 views

Issues converting MyISAM table to InnoDB (auto column issue)

I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6. The following is the table dump: -- -- Table structure for table `companies` -- DROP TABLE IF EXISTS `companies`; ...
1
vote
1answer
139 views

Is there a way to recover this database (MySQL)

I did a "cold" (db offline, all filesystem files) backup of a MySQL db. I wrote the backup to an ISO image. Unfortunately Joliet filenames are not default. I didn't realize that until attempting to ...
1
vote
2answers
2k views

Recursive query in mysql

I am using GetAncestry function found at this post, The problem is when I try to use it with select query Mysql hang, I am not sure why it is happens my Mysql version is "5.5.16" any help appriciable ...
1
vote
1answer
52 views

After adding a slave to Master - Master configuration, slave syncs from only one master

Futher to my previous question, Here is what we have +<------+ | ^ V | S1<---M1 M2 | ^ V | +-------+ what happens is Upon ...
1
vote
2answers
144 views

Breaking Semisynchronous Replication in MySQL 5.5

I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7. My application is running and updating the database of the master server and same is being updated in the ...
1
vote
1answer
826 views

Why does MATCH() give Score 0, if there are less rows in the database?

I followed this example from the Mysql Documentation: CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT ...
1
vote
2answers
5k views

MySQL Installation error: Unable to update security settings

I tried to configure MySQL server 5.5 on Windows and this is what I get mysql-server-5.5-winx64:56 - Attempting to update security settings. mysql-server-5.5-winx64:100 - Unable to update security ...
1
vote
2answers
389 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
1
vote
1answer
589 views

MySQL Point in Time Recovery For Just a Single Schema?

Is it possible to simply restore a single schema and bring it to a point in time (Say three hours ago) without bringing the entire database back? I can think of a lengthy method of backing up each ...
1
vote
1answer
496 views

Master to Slave to Slave Configuration in MySQL

We currently have 3 MySQL Servers running; the first which contains production data and is due to be retired mysqld Ver 5.5.28-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64 ((Ubuntu)) mysql> ...
1
vote
2answers
2k views

Getting repeated value in a group_concat only if ids are different

I have two tables, first the table Product: id|category_id --+----------- 1 | 12345 2 | 12345 3 | 12465 And then a table activity: id|prod_id|activity_type |description ...
1
vote
2answers
89 views

Change the Existing Topology of a MySQL Replication Setup

I want to be able to edit the topology of an existing replication, is that possible? For example if I have a star topology, I want to edit it to make it a ring one. How can I do that? I'm thinking ...
1
vote
1answer
336 views

Can I use symlink or data directory for ARCHIVE MySQL tables?

All mention of either using a symlink or data/index separate data directories state that MyISAM is the only supported (https://dev.mysql.com/doc/refman/5.5/en/symbolic-links.html). However, I found ...
1
vote
1answer
442 views

Experience using ScaleArc in test or production?

Has anyone had any experience using ScaleArc? My CTO has asked my thoughts on it, and I have seen no information out there regarding real-world experiences.
1
vote
1answer
722 views

How to check growth of database in mysql?

I want to know is there any method to check the growth of database on file EXAMPLES Database A contains all tables in INNODB storage engine Database B contains all tables in MyISAM storage engine ...
1
vote
1answer
131 views

Performance impact of including result column in indexed columns

I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of: texelid ...
1
vote
3answers
3k views

Automatic MySQL Replication Fail-over Strategy

I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the ...
1
vote
1answer
458 views

MySql proxy to replication configuration

Is there any solution (say a proxy) that makes MySQL replication cluster behave like one database towards developers? And developers then don't need to worry about using master to write and slaves to ...
1
vote
3answers
158 views

Where can I learn about MySQL master/slave, master/master, or clustering setups?

What are the best resources you've read or watched about setting up master/slave or clustering for database replication? I don't know much about this. I have a database that is getting rather large ...