MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.
3
votes
1answer
68 views
how to add removed partition?
This is the structure of my table:
-- Table: t_keyword_conversion_ga
-- Create Table:
CREATE TABLE `t_keyword_conversion_ga` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`own_domain_id` int(11) DEFAULT ...
4
votes
3answers
3k views
How to change a MySQL previous slave to be a master and remove slave status information?
I have a master -> slave configuration where the master failed. I've been able to reset the old-slave to be a master and the old-master to slave from it. Fine.
What I can't seem to do is to remove ...
2
votes
1answer
113 views
Does mysqld wait for init-script code to complete before opening the instance?
I'm trying to automate the execution of an application code at MySQL startup.
The init-file server option seems handy and I need to know if mysqld waits for all the code in the init-file to complete ...
0
votes
1answer
162 views
Unable to take backup of mysql database using Xtrabackup
I am trying to take mysql backup without any downtime but unable to take the backup using Xtrabackup .
Actual Database is almost about 40+ GB in size. I am trying to first satisfy myself. Hence, I ...
3
votes
1answer
114 views
What's the most unsafe MySQL configuration?
I have MySQL 5.5.27-29.0-log instance running specifically for Jenkins. Since tests data is always recreated during setup, ACID is unnecessary and I'm hoping to sacrifice it to gain more performance.
...
2
votes
1answer
1k views
How to grant access for multiple tables to a user in MySQL DB?
I have mysql db named "greats" with four tables as below
+---------------------+
| Tables_in_greatstat |
+---------------------+
| log |
| sitedet1 |
| siteindex ...
1
vote
0answers
143 views
MySQL cluster stuck in start phase 4, how to proceed?
Over the weekend our cluster we set up for evaluation had one of its data nodes lose network connectivity briefly. This was while another data node was in the middle of a restart (which had been going ...
0
votes
0answers
80 views
Please help configure my Mysql for 12GB 4cores server
I use a dedicated server with 12GB memory and 4 core CPU with Ubuntu 12.04 LTS (x64). This not just a database server. MySQL appears to be restarting frequently, I think I can tune the config to ...
2
votes
3answers
447 views
how to install mysql on mac?
My Mac runs Mac OS X, version 10.7.5., so it's a 64 bit system.
First, I downloaded mysql-5.5.28-osx10.6-x86_64.dmg.
Then I double clicked mysql-5.5.28-osx10.6-x86_64.dmg, it's mounted, and shows
...
0
votes
1answer
33 views
Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL
I am trying to create a new table new_table from t1 by comparing primary key values of another table t2
ie,
1) created table new_table
2) gave the query
insert into new_table select a.* ...
2
votes
1answer
121 views
Restriction on self referencing on insert in MYSQL?
How to restrict insert on adding self referencing rows in a recursive relation table (a table contains foreign key points itself).
mysql> SELECT * FROM Employee;
...
3
votes
1answer
199 views
How to Update same table on deletion in MYSQL?
In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.
Following is query I used to create table:
CREATE TABLE IF NOT EXISTS `Employee` ...
3
votes
3answers
3k views
How to Add Federated engine after installing Mysql
I have Mysql 5.5.18
and upon show engines:
show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine ...
1
vote
0answers
57 views
How to create multi volume file backup using symantec backup.exec
I am using symantec backup .exec for backup and restore in mysql server . Now I am in need to take larger database backup in tape .But it exeeds the tape storage and I am trying to create a multi ...
2
votes
1answer
179 views
mysql emits BINLOG rows even though binlog_format=STATEMENT
We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default)
According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in ...
1
vote
1answer
147 views
mysql session logging tables
I've been building a session logging system for our sites. We get around ~15m hits a year and at the moment we're really only interested in stats about people who logon to our sites.
Our table ...
2
votes
1answer
697 views
MySql - Changing expire_logs_days without restarting the server
I'm using MySql 5.5.
Is it possible to change expire_logs_days and have the changes take effect without restarting the server?
1
vote
1answer
117 views
Query to take partial backup in MYSQL
I tried to take full backup in mysql using enterprise backup by using the command
mysqlbackup -u root -p --backup-dir=g:\full_bck backup
But I could not take backup of a single database , I ...
0
votes
0answers
109 views
how to improve performance of my query it was taking more than one hour in my environment
How can I improve performance of below query? It was running more than one hour to execute in my production environment. Query plan:
mysql> explain
SELECT transaction_detail.trans_id,
...
1
vote
1answer
149 views
How to COUNT number of rows with LIMIT?
I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too?
Currently, I use two separate queries to do so as
SELECT COUNT(*) FROM col WHERE CLAUSE
...
1
vote
1answer
381 views
Retrieve double data type value in non exponential form
Is it possible to retrieve non-exponential value in double data type column? After the width of the column is increased to 15 it shows the values in exponential form.
Now I get the following:
...
1
vote
1answer
110 views
How can I change the timezone in MySQL?
In a MySQL DB, I have two varchar fields. One is for date and one is for time. I need to change the time from CST to GMT. In MySQL how do we convert the combination of these date and time fields from ...
0
votes
0answers
142 views
Optimizing MySQL for a shared hosting setup
This is my current setup:
[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
max_connections = 500
max_user_connections = 25
key_buffer = 64M
myisam_sort_buffer_size = 32M
...
1
vote
1answer
48 views
Query from database
I have a table named mip. The table has primary key id, a foreign key product_id and a column usage_type. product_id is a primary key id to a table named product.
It is one to many relationship from ...
3
votes
1answer
98 views
mysqld-multi with first DB as Slave and second DB as Master
Yesterday, I setup mysqld-multi in my.cnf. It works fantastic, but there is still a further Problem.
The first DB on socket 1 running as Slave from a other server. The second one from socket 2 ...
0
votes
0answers
161 views
Error connecting to DB
In regards to:
http://serverfault.com/questions/442218/remounting-mysql-under-a-new-mount-point
Everything is running. MySQL started but for websites they can not connect to the mysql database at ...
1
vote
1answer
83 views
MySQL Cluster Steps To Recover Data If Catstrophic Failure Occurs
I want to know if a catstrophic failure occurs (like a power outage), and all nodes in MySQL Cluster are shutdown in cluster then data will be lost even though a backup was taken? Or it possible that ...
1
vote
1answer
90 views
MySql - selecting into file and re-loading it with special characters
I have a MySql table called twitter_statuses which as the title suggests holds twitter statuses. here is the table structure.
this table contains all sort of characters in the text column, and some ...
4
votes
2answers
1k views
Scaling Percona datacenters: setup and replication
Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you.
We have already scaled alot of our application stack: we offloaded ...
1
vote
1answer
194 views
`show procedure status` doesn't list all of the procedures?
mysql-server-5.5.28-1.el5.remi
I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of ...
0
votes
1answer
95 views
Handling error of foreign key
How to set a FK value NULL upon receiving error for foreign key constraint?
Consider a simple table a
CREATE TABLE test
(
id int(11) NOT NULL AUTO_INCREMENT
ex_key int(11),
FOREIGN ...
0
votes
1answer
314 views
How can I enable my-huge file on windows server?
I installed Windows Server 2008 and MySQL 5.5.
Now I need configure MySQL for huge InnoDB tables and a lot of connections.
I see that on my Windows Server, there is a my-huge file(configs files that ...
2
votes
4answers
216 views
Does my table violate normalization rules?
I am developing an emulator for a game. My item table looks like this:
-- ----------------------------
-- Table structure for `items`
-- ----------------------------
DROP TABLE IF EXISTS `items`;
...
2
votes
1answer
141 views
Do I need to index a key that I partition by?
I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
2
votes
2answers
105 views
Does MySQL 5.5 support shared database with separate schema?
I am developing a SaaS based product using MySQL 5.5 as the backend database. I want to keep shared database for all the tenants, but separate a database schema of each tenant. There would be a single ...
1
vote
1answer
118 views
Null Values and Performance
I always try to build the database avoiding any NULL values in my tables, and I have successfully had done this so far. (Hey, I am very new to database design.)
But still there are times when saving ...
5
votes
1answer
171 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
2answers
104 views
Is there a way to get slow-query-log-like profiling information on a single query?
Specifically, I'm looking for this information:
# Query_time: 0.011922 Lock_time: 0.000088 Rows_sent: 107 Rows_examined: 663 Rows_affected: 0 Rows_read: 107
# Bytes_sent: 4152 Tmp_tables: 3 ...
5
votes
1answer
3k views
intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”
I'm a beginner system administrator for a small virtual server. The main function of the server is to run the open-source Moodle course management system software, written in PHP. It relies on a ...
0
votes
2answers
694 views
Some queries slower in MySQL 5.5 than 5.0?
We have an exceptionally inefficient process (1.3 million selects, 232k updates) that I'm now recoding to be 1 select and 50 updates. In the meantime, though, today's process ran.
We recently updated ...
7
votes
1answer
2k 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
0answers
139 views
MySQL import file with no column delimiter
Is there a way to use the mysqlimport command on a file that has no column delimiters?
I would like to import the ctl file used with the sqlloader for Oracle
1
vote
1answer
224 views
Does TRANSACTION/COMMIT/ROLLBACK only work on certain MySQL engines such as InnoDB?
Are transaction functions only relevant for particular database engines on MySQL, or are transactions supported under all MySQL engines, including MyISAM and InnoDB?
someone says here that ...
1
vote
0answers
55 views
How to profile individual queries using PERFORMANCE_SCHEMA
I've been looking at SHOW PROFILES, which seems ideal for what I am doing.
However, I've noticed a warning that SHOW PROFILES is being deprecated and that PERFORMANCE_SCHEMA should be used instead. ...
2
votes
2answers
899 views
mysql 5.5 corrupt - missing temporary table
One of our mysql instances crashed and failed to start:
120422 10:04:13 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './databasename/#sql-29f6_45.ibd'!
...
0
votes
0answers
104 views
MySql server answer #2002 [closed]
Since this morning, phpmyadmin is giving me the error message #2002 the server doesn't answer (or the connection to local Mysql is not well configured) and a message about control-use.
I'm ...
1
vote
1answer
206 views
MySQL Master-Slave Replication Cross Database
I am setting up Replication for cross Database as i have set 2 variables in Slave' s my.cnf as replicate-do-db=database_name and
Replicate-wild-do-table=database_name.% ,But i am not able to get ...
0
votes
1answer
52 views
Move databases on same Mac
I have just updated my MacBook to Mountain Lion. Since then, my MAMP installation does not work properly so I decided to use the build httpd/php stuff. For this, I have additionally installed a new ...
2
votes
2answers
3k views
innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition
In one of my production environment, we have two instances running on a RedHat cluster, with one production instance associated with the cluster.
We have 125G main memory with 24G InnoDB buffer pool ...
6
votes
1answer
2k views
When to switch from MyISAM to InnoDB?
We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM.
My original plan was to upgrade ...
