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

learn more… | top users | synonyms

6
votes
1answer
5k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found after upgrade to mysql 5.6

I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup I found a possible solution here, but it does not seem official. ...
0
votes
2answers
343 views

Warning before issuing delete/update command in mysql/oracle , then executes

Is there any utility , function either in mysql /oracle that if i issue some delete/update command wrongly , a message must prompt at command_line* (if possible in ubuntu) asking whether "you sure to ...
1
vote
1answer
98 views

addition in datetime column during insertion in mysql

i have a table with below 5 columns..timestamp is data type for timedate related column : Region event_time start_time mid_time end_time India 2013-11-03 13:00:00 America ...
1
vote
1answer
69 views

MySQL geospatial query optimisation suggestions

I am new to DB Administration. Trying to sort out this query which is the heart of our application. I am searching google on similar questions, but would like an experts opinion on my particular ...
3
votes
1answer
127 views

How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime

Our existing Database size around 200GB. We want to use consistent, parallel backups methods. For testing, I migrated my Development DB from 5.5 to 5.6 by running mysqldump with option ...
1
vote
1answer
657 views

Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc. I'm using Mysql 5.5.x Before doing the production system, I am doing a trial run on a copy ...
1
vote
1answer
321 views

wordpress table is crashed and should be repaired

I run a wordprees website and use a managed VPS server with cPanel/WHM. Got bellow error log and check the table. I repaired the table with repair table command from phpmyadmin. But this problem occur ...
0
votes
1answer
173 views

Data Analysis with MongoDB or MySQL

I have a need for processing some data in order to come up with some statistic. This is the scenario: Each user can answer several questions from the question list. They pick their answers from a ...
1
vote
3answers
620 views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
0
votes
1answer
405 views

Datetime Difference in MySQL when format is Varchar

I have two columns in a table: STOP and START, of type varchar. I want to have the difference between both columns, and I'm using : SELECT DATEDIFF(stop, start) AS difference from T1; It is ...
0
votes
1answer
1k views

MySQL - Server collation utf8_unicode_ci vs table collation utf8_bin: compatibility and performance

I'm not sure whether this belongs to SE DBA, but it sounds more logical ... Our MySQL 5.5.32 on Ubuntu 12.04 64 uses utf8_unicode_ci for the Server collation and some tables, like phpBB3, uses ...
0
votes
2answers
874 views

Master slave replication in mysql seconds behind master goes on increasing

In replication at slave end seconds_behind_master goes on increasing. The dump file for upload is of 6.7 gb large, can it be the reason for the lagging of seconds behind master?
0
votes
2answers
176 views

avoiding unique key collisions across tables

This might be related to the idea of data warehousing, but i'm not sure, though it is extreme theory. We have a table where records get written to first, let's call it tbl_recording which looks like: ...
0
votes
1answer
139 views

Update the host field in Grant privileges in mysql

I have an existing table and I want to update host part in privileges using Grant command mysql> show grants -> ; ...
0
votes
1answer
105 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
1answer
559 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 ...
2
votes
0answers
94 views

Understanding Indexes and using them better

I've been spending some time trying to optimise our MySQL queries that we use which essentially joins one table with 2 million rows onto another which has 1-2 million. I know MySQL is able to easily ...
1
vote
1answer
210 views

Squeezing Maximum Out Of My Server

I have a dedicated server only built to hold MySQL databases. Currently I have a database that has 173 million records and increasing by 5-15+ million records very day. My question is what and why I ...
0
votes
0answers
40 views

Is there any way to set server-wide and schema-wide default row formats for InnoDB tables in MySQL?

Is there a way to set server-wide and database-wide default row formats? Currently it seems that I must specify ROW_FORMAT in CREATE TABLE or ALTER TABLE statements. Am I reading the documentation ...
0
votes
0answers
105 views

How to set up a pivot table view?

I have three tables: Entry Manager Employee The Entry table has as date field and a bunch of other fields about the entry including the emp_id. Manager Table has columns: Manager_id ...
1
vote
0answers
517 views

Fastest way to perform simple operations on 1 million rows

I have a simple table with more than 1 million rows in an InnoDB MySQL table. Here are the steps that I need to perform on each row: SELECT * FROM tbl where work_done=0 make some data processings ...
0
votes
1answer
39 views

Migrating database with views trough phpMyAdmin causes error in second host

I'm trying to migrate a database which contains some views and I'm doing trough phpMyAdmin export feature. MySQL version on servers are: Server to migrate: Server version: 5.5.34-0ubuntu0.12.04.1 ...
0
votes
0answers
27 views

Anyone know the fix for an older innodb inspection query

This post talked about an information_schema query to give you a nice report of how much percentage wise indexes are fitting into the buffer pool The query looked like > SELECT `schema` ...
11
votes
2answers
1k views

Fastest way to change a 600GB table indexed key datatype from INT to BIGINT

I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much ...
0
votes
1answer
111 views

How to make slave is in sync with master in tungsten replication?

we are using Tungsten master to slave replication in MySQL. we have configured this setup very long back. from last week onwards slave is in lagging with master around 5 to 10 binary files. we have ...
0
votes
1answer
320 views

Replication - Binary log parsing(Tableid generation, ON DELETE CASCADE handling, Changed columns for update event)

I am using MySQL 5.5 with InnoDB engine. I have set binlog format as row-based replication. I have some doubts regarding replication: 1) How is the tableID generated? Is it stored somewhere? 2) ...
1
vote
1answer
368 views

Two different MYSQl instances on same machine?

I have MySQL 5.6 installed (Linux). /var/lib/mysql /etc/my.cnf I have a cold backup that I am recovering: /var/lib/mysql_backup /etc/mybackup.cnf In April I was able to get the backup started ...
0
votes
1answer
956 views

Restoring a single database from mysql enterprise full backup

I am having command to restore all databases from full dump taken by enterprise full backup .Can anyone help me how to restore single database from full dump .
1
vote
1answer
107 views

How to prevent Cartesian joins when value can be in either field?

I have a table of articles where the same article may be presented to different audiences with slightly altered text depending on the user's access level. When this occurs, the article will have the ...
1
vote
1answer
113 views

Change normal user to admin in MySQL

I have set up a MySQL database. My problem now is I can't add new users with the user I have created. I try to grant access but I can't still add new users to my system. I use Workbench to add new ...
0
votes
1answer
32 views

Count issue in Mysql

I query a table SELECT COUNT(*) FROM sf_guard_user_group return count 1 but table has total 28 records. But if I test it via putty from another machine, it shows count 28. Help to sort out.
0
votes
2answers
720 views

How to enable the Federated/FederatedX storage engine on MariaDB 5.5?

I upgraded from MySQL 5.1 to MariaDB 5.3 and then to 5.5 on a Windows machine. Everything works well, except I can't enable the federated storage engine. With MySQL 5.1 and MariaDB 5.3, I just added ...
0
votes
1answer
36 views

Cannot create stored procedure

I have the following piece of statement entered into MySQL5.6 Command Line Client. However, the following error was received. I haven't even been able to add in END// Delimiter; after the select ...
0
votes
0answers
29 views

Accessing Database from Slave server

I have an application running in one server eg: My Server's Ip is 192.168.10.10, and I have the MySQL database server in the same machine. The application connects database server as localhost. ...
3
votes
1answer
246 views

How to store static list of hashes in MySQL effectively?

Problem: I have a list of pairs (md5_hash, id). I want to store the data in MySQL and I want to make queries like this SELECT id FROM table WHERE md5_hash = <some_hash> The number of pairs ...
2
votes
1answer
52 views

Unknown column error with an index

I created a simple index associated with a timestamp column CREATE TABLE `data` ( `id` INT(11) UNSIGNED NOT NULL, `ticker` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci', `comment` TEXT ...
5
votes
1answer
758 views

Possibilities to speed up InnoDB INSERTs and UPDATEs

My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower. I'm monitoring when, and it ...
0
votes
1answer
88 views

How to drop a column named 'column'? [closed]

I need to delete a column named "column" form a table but the standard: mysql> ALTER TABLE table_name DROP COLUMN 'column'; returns: ERROR 1064 (42000): You have an error in your SQL syntax; ...
0
votes
1answer
107 views

Using an enum index to limit records

A common issue, just not sure on the keywords. I have a table of assessments (14k+ records). Each assessment can be in many different states ('draft', 'proofing', 'amends', etc... and 'done'). Most ...
0
votes
1answer
171 views

MySQL 1366 Error on Replication Slave

I've got two pairs of servers (each identical hardware and OS) with replication running on both pairs. Running MySQL 5.5.34 on Windows Server 2008 R2. I'm trying to track down a problem that has ...
1
vote
1answer
84 views

How and where do I upload a mysql database?

I am new to Ubuntu (server edition) and mysql. How do I upload (import) an existing database with a MYSQL or (mysql.zip) extension that was downloaded from a web host (cPanel?) I do not have any of ...
1
vote
2answers
354 views

How can I return column names in dot notation in MySQL?

I want to return column names into a table. But I also want to avoid redundancies like 'id' and 'id'. I did: INSERT INTO columns(name) SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE ...
2
votes
1answer
145 views

Is there a way to achieve Workload Management in MySQL?

Is there a way to set up Workload Management in MySQL (in particular v5.5 on AWS RDS)? By Workload Management I mean the functionality implemented in e.g. Teradata, RedShift that allows ...
0
votes
1answer
110 views

how auto_increment work in mysql

im confused here , i create a table and have 2 columns id and name. Now id is primary key , not null and auto increment. Suppose i fire: insert into table (name) values('test'); this will give me: ...
2
votes
1answer
1k views

MySQL: Alter Table on very active table gives “Waiting for table metadata lock”

At my work, we've recently upgraded to MySQL 5.5 and since then, have been bitten a few times by the new (and intended) behavior of metadata locking. Basically, whenever we run an ALTER TABLE ...
0
votes
1answer
382 views

How to update a table by count from another table?

I have two tables as CREATE TABLE country_statistics ( id int(11) NOT NULL AUTO_INCREMENT us int(11), uk int(11), ca int(11), PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CREATE TABLE entries ( id ...
0
votes
1answer
58 views

MySQL Partitioning: why it's not taking appropriate partition

DROP TABLE temp; CREATE TABLE `temp` ( `CallID` bigint(8) unsigned NOT NULL, `InfoID` bigint(8) unsigned NOT NULL, `CallStartTime` datetime NOT NULL, ...
0
votes
0answers
98 views

Query Execution Plan for MySQL

In Microsoft SQL Server you can see Execution Plans for queries to see how a query is executed, what joins take the most time and see any missing indexs now i want to do the same for MySQL scripts ...
1
vote
1answer
69 views

Create user for alternate port

I am running a private MySQL instance on WebFaction WebFaction is a shared host who provides both for their public MySQL instance and for your own private MySQL instance. The private instance is a ...
0
votes
5answers
3k views

MySQL gives me:“Can't open and lock privilege tables: Table 'host' is read only”

I am facing problem restoring a MySQL database. My primary database was MySQL 5.1 and now I am trying to copy it to MySQL 5.5. The database was backed up by using Xtrabackup. I am using Ubuntu ...