MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.

learn more… | top users | synonyms

0
votes
3answers
51 views

MySQL backup breakes a table every time

I've a MySQL Server 5.1.58-1ubuntu1 installed on an Ubuntu 12.04 LTS. Every night we do a backup of all the databases with this script: rm -rf /var/backup/mysql/ * for i in $(echo 'SHOW DATABASES;' ...
0
votes
1answer
47 views

Immigrate to file-per-table mode in MySQL

I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini ...
1
vote
1answer
41 views

What's the complexity of renaming a column in MySql?

We want to rename a column in our MySql (version 5.5) database with something like this: ALTER TABLE t1 CHANGE a b INTEGER; Is this an O(N) (N=number of rows in the table) or an O(1) operation? I ...
0
votes
2answers
95 views

Show Users That Connect To DB

We are using Maria DB (v5.3 I believe) in our production environment. One issue that we have, is that historically, the dev user (which has full rights and privileges) is used in all applications ...
2
votes
2answers
34 views

Mysql used key changes between two consecutive EXPLAINs

I have a huge unmaintained innodb table (~1.5billion rows), I just started to try to query it. I write && instead of AND once; and surprise! it uses different key! EXPLAIN EXTENDED SELECT * ...
1
vote
1answer
225 views

Generate INSERT statement from SELECT query

I have two databases, in first database I have a table called zoom_city and in the second database I have a table called courrier_city. The SQL for each is below: CREATE TABLE IF NOT EXISTS ...
-2
votes
1answer
531 views

how to autoincrement varchar datatype depending upon the product details name using trigger

I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Slno*. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id ...
5
votes
3answers
413 views

MySQL varchar(255) limitation & Anomaly

I have defined a column in my MySQL database as : ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL However, when I enter text ...
1
vote
3answers
66 views

Contains statement Query

I need to check the string with the column if the column contains that string or not. example:String 'how to make pizza' and the column if has 'pizza' i need to fetch that.. like SELECT * FROM ...
1
vote
0answers
85 views

Mysqldump --flush-logs results in small backup files?

New to MySQL and just learning how to setup a daily full backup (binary logs enabled). My goal is to run a daily full backup of the databases/schemas and reset the binary log. I'm currently using an ...
1
vote
0answers
68 views

MySQL query with order by decreased performance

Here is my schema: CREATE TABLE `UserMst` ( `UserID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `EMailID` varchar(50) DEFAULT NULL, `UserName` varchar(20) DEFAULT NULL, `FullName` ...
1
vote
3answers
786 views

Backup / Export data from MySQL 5.5 attachments table keeps failing!

Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million ...
0
votes
0answers
16 views

MySQL: Archival How to?

I would like to know the correct Archival Strategy for my DB Schema. We are using Mysql 5.5. Here is the full scenario: We have around 35 tables in DB out of which 10 would be transactional in ...
12
votes
3answers
5k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
0
votes
1answer
499 views

SQL Query is sometimes slow, sometimes fast

I have two hosts, one with my rails app, one with my MySQL database. Going through my rails Logfile I can see that sometimes queries take much more time than the same query at other times. Think 200ms ...
1
vote
1answer
466 views

InnoDB - How to get top locked tables and rows which are locked

I was searching for a tool OR query which can give me top locked tables and which particular row is locked, Is it possible to get ?
0
votes
1answer
30 views

Check whether MySQL Innodb Rows are compressed

After setting the various variables to enable compression on the Innodb Barracuda tables, what query will you use to check that those tables are indeed compressed?
-4
votes
2answers
79 views

Performance with IN clause

Does indexed data affect query performance? means If I passed value as 1,2,3,4... in ordered form in any IN clause, can it improve query performance? and what happen in case I pass value randomly ...
0
votes
1answer
135 views

XML dump of MySQL database don't include foreign key constraints. What can be the reason? sqldump(.sql) file list it

I am accessing schema information of databases in XML format. I am taking dump of the database in XML format using the command below. mysqldump --no-data --xml -u root -p bakerydb > bakerydb.xml ...
2
votes
3answers
408 views

Getting 0KB of size file while backup mysql database using mysql dump

I am using below code to backup my database using php script but I am getting 0KB size file. How can I backup my full database with all routines and functions? $dbhost = 'localhost'; $dbuser = ...
3
votes
1answer
168 views

Online Schema migration in MySQL : When to start?

I'm the tech lead of a Social Media SAAS, where we're currently experiencing a big increase in data amount we're storing in our mySQL-database. We're having a single MySQL db, and besides many ...
1
vote
0answers
40 views

crossplatform mysql_install_db script in mysql 5.5.34 on Windows

I downloaded mysql-5.5.34-win32.zip and found out there is mysql_install_db.pl script. However mysql 5.5 documentation says: mysql_install_db is a shell script and is available only on Unix ...
2
votes
1answer
208 views

In MySQL, does the order of the columns in a WHERE clause affect query performance,why? [closed]

I have a query that doesn't use any indexes: SELECT 32, guid, 1, 1, 1, 0, 5 FROM test WHERE level >= 20 AND ( ( fun_GetIndexValue(data, 354) ...
21
votes
3answers
26k views

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
1
vote
2answers
298 views

MySQL Synchronous Replication on Ubuntu 12.04 with DRBD

I have been looking around for some easy and open-source ways to handle database synchronous replication. DRDB on a Linux server seems to be one of the fastest and easiest methods to handle database ...
1
vote
0answers
143 views

Upgrading from MySQL 5.5 to 5.6 - timestamp issue?

I'm looking at upgrading my Percona Server setup from 5.5 to 5.6. Reading the upgrade docs from MySQL sounds mostly straightforward except for anything involving time/date columns. As far as I can ...
1
vote
2answers
498 views

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
0
votes
2answers
257 views

Cannot stop MySQL 5.5 replication threads

We have a master-master replication setup using MySQL 5.5. On one of the servers I am trying to stop the slave thread, but without success. To ensure that the replication does not connect to a real ...
0
votes
0answers
30 views

On replicating all tables of a given database except for those starting with some pattern

I'd like a slave to replicate all tables of a given database except for those starting with some pattern, eg. those with names starting with 'nc' (non-critical, redundant denormalized tables). So I ...
1
vote
2answers
110 views

MySQL replication: slave not updated

This has been asked many times over but we really cannot get a simple Master-Slave Mysql (5.5.25) replication to work for our two dbs (FatTree and Torus). And no trace of error in the logs. The ...
1
vote
1answer
1k views

How to create trigger to update timestamp when record is updated or inserted in another table

I have a table t_task and a table t_taskaction with a foreign key to t_task. I would like to update timestamp from t_task that is refering the foreign key form t_taskaction with every update in ...
0
votes
2answers
105 views

mysql forbidden database names

I was wondering whether there were a list of forbidden (reserved) database names in mysql. For instance, mysql is obviously one. But is there a determinated list? I don't want to cover 99% in my ...
1
vote
1answer
210 views

Can I disable an account in mysql after x failed login attempts?

The question is in the title. I would like to disable accounts after x failed login attempts. Is it possible? Thank you
0
votes
0answers
29 views

Request for Help Optimizing a Query in MySQL

I have a query that is being used to report on the number of times a trigger in Zabbix, was fired by an event. For example, an event (not enough disk space) fires a trigger that sends an email to ...
0
votes
1answer
103 views

Database design [closed]

I have tables college and department. In college, I have serial-number and college-names, In department, i have nos, college-names and department-name. My challenge is that having a repeated ...
0
votes
0answers
60 views

InnoDB crashes frequently

My Innodb Engine is getting crashed frequently. I could not find the reason behind this. This is the error log: 140129 9:22:49 [Note] Plugin 'FEDERATED' is disabled. 140129 9:22:49 InnoDB: The ...
0
votes
0answers
58 views

Is this a normal query time for a left outer join type?

I have Employee Table - 80K records about 100 fields I have an app table - 8K records and 30 fields Then a subset of employee table also stored as table that has our divisions subset - call this ...
0
votes
2answers
285 views

Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!

I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour. I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has ...
0
votes
0answers
54 views

Caching data with MySQL and asp.net 2.0

I want to cache frequently used data. Is there simplest way to do this with asp.net itself? I am using framework 2.0. If i do this with MySQL technique how about innodb?? will this allow me to cache ...
0
votes
0answers
64 views

Mysql query getting hung!

I am using mysql 5.5. I create following temporary table: CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.%s ( `count` int(10) NOT NULL, `m_class` enum('SMS','EMAIL') NOT NULL, ...
1
vote
2answers
1k views

Trouble with triggers and insert .. on duplicate key update

I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For ...
0
votes
0answers
19 views

How do I keep old employees from LDAP data?

I have an LDAP feed that I run nightly and it makes several tables that run a lot of our internal websites. I simply get about 40 fields times 80K employees. The tables get overridden every night ...
1
vote
0answers
1k views

Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL?

If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example: $ mysql -u root -p --column-type-info Enter password: --- Copyright and help message snipped for brevity --- ...
1
vote
1answer
54 views

Poor query performance in MySQL

I have a 10 million record table in a MySQL 5.5 InnoDB. It's running in a 16GB RAM server with good CPU and fast HD. When I run the following query SELECT DISTINCT knowledge_id, ...
0
votes
1answer
82 views

Slow MYSQL performance due to sub queries

Here is my query: SELECT BAL,NAME, (SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS DR , (SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE < ...
0
votes
1answer
59 views

Annonymous user has no privileges, but still able to show databases?

Output of SHOW GRANTS FOR ''@'localhost'; GRANT USAGE ON *.* TO ''@'localhost' However, when I log in using my default user account, which is later defaulted to an annonymous account by mySQL as ...
0
votes
0answers
12 views

Performing a dump breaks replication

I have a master/slave setup in our production environment (MariaDB). I've found that when a dump is done on a schema that is heavily used, it causes the replication between the master & slave to ...
0
votes
0answers
47 views

What happens when an SSH session is terminated while a query is running?

I have a huge database on a dedicated server, whenever trying to modify the db I use PUTTY to establish an SSH connection and then do the work. Yesterday I started an INDEX query on an innodb table ...
0
votes
0answers
121 views

Alternative to TEMPORARY tables capable of re-opening in a query?

I use TEMPORARY table for calculation of temporary data, but TEMPORARY tables in mysql has a big limitation, which made it useless for me. A table cannot be re-opened in a query. This mean I cannot ...
0
votes
0answers
32 views

Query for multi-table search with concatenated fields

Thank you in advance for bearing with me, as I am new to database design and implementation. The Setup: I have a MySQL 5.5 InnoDB database with several tables, 4 of which are relevant to the problem; ...