MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.
3
votes
0answers
277 views
Convert Oracle Query for Date to Mysql queries
Need to convert the following oracle queries to MYSQL queries.
1)
select to_char(to_date(?,'DD-MM-YY'))
from dual;
2)
select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY'))
from dual;
3)
...
1
vote
0answers
26 views
Error in alter table reorganize partition for MySQL Cluster
My cluster contains 48 million rows and cluster contains 4 storage nodes . After that I add online storage node and executed alter table 'customer_tbl' reorganize partition.
Some parameters of ...
1
vote
0answers
318 views
“Lost connection to MySQL server during query” error
I have MySQL 5.5.20 and this table:
mysql> desc country;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
...
1
vote
0answers
34 views
Is there a canonical list of MySQL defaults which should be changed
I'm setting up a new mysql server as we're building a new app at work.
Most of the developers/operations guys are familiar with mysql.
Many of mysql's settings (eg strict mode) are set badly by ...
1
vote
0answers
243 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 ...
1
vote
0answers
153 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 ...
1
vote
0answers
59 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 ...
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. ...
1
vote
0answers
74 views
MySQL replication backup
How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu.
What happens if,
I issue or schedule ...
1
vote
0answers
147 views
Finding differences between a master and slave when pt-table-sync fails
I have a table that's showing some differences on a master and slave according to pt-table-checksum (PTC). I've found some verifying differences beyond that.
Certain tables in the mix have composite ...
1
vote
0answers
142 views
How to speed up this query
MySQL doesn't have nearest neighbor search so. So I try to come up with a "distance" that's reasonable so the number of returned query is around 20-200. That way innodb doesn't have to compute ...
1
vote
0answers
145 views
mysql server is not sending result set back after query execution to client and staying in sending data state
When I pass a query from my Java application to mysql server, it stays in Sending Data state and not returning result set back to application.
When I execute the same query in SQLYog directly, it ...
0
votes
0answers
9 views
mySql Event gets expired on closing of the command prompt
As i am quite new to server side, i tried to create a event using
CREATE EVENT enetname ON SCHEDULE AT '2013-06-13 18:00:00'+INTERVAL 30
SECOND DO update tablname set status= 'Expired' where ...
0
votes
0answers
21 views
mysql deadlock while updating: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction update
I am using the mysql version 5.5.29 in my production server. while executing set of update statement inside the procedure it is throughing the following error:
ERROR 1213 (40001): Deadlock found when ...
0
votes
0answers
8 views
Get turno.tipo, maquina.patente and operador.nombre and status, complex query
I've this DB design I'm trying to build a query where I should get turno.tipo, maquina.patente, operador.nombre and detalle_emisores.revisado` but it's a bit complex to my knowledge. I started this:
...
0
votes
0answers
6 views
MySQL - What is the diffrence between ClearText Authentication and PAM Authentication MySQL?
PAM Authentication:
PAM enables a system to use a standard interface to access various kinds of authentication methods, such as Unix passwords or an LDAP directory.
Cleartext Client-Side ...
0
votes
0answers
24 views
database parameter to accept void string and insert null
Is there a way in MySQL to load void strings in a flat file as NULL.I am aware of \N but i am looking more towards a database/global parameter that could be set so that MySQL takes Void Strings and ...
0
votes
0answers
18 views
Having trouble connecting tungsten replicator from EC2 to RDS
I am testing using Tungsten Replicator to help migrate large databases to RDS. I have no trouble setting up the service on the EC2 "master". I am using a second EC2 instance as a kind of relay server ...
0
votes
0answers
44 views
Replicate database without Internet connection
I'm working on a company where I have a central DB and several agency databases. See the diagram at here. All the databases are identical, just the data can change and in some cases. I've no internet ...
0
votes
0answers
24 views
Commit count while using LOAD statements in MYSQL
I have a very big text delimited file, I am using Mysql LOAD statement but i want to put some commit count, for example if i put commit count 1000 then after inserting every 1000 rows it should commit ...
0
votes
0answers
19 views
Should store procedures be cached?
Should store procedures be cached in MySQL? If so, How long it is stay in cache?
In my case, When I call one stored procedure first time, it is giving me results in 1 second, after that it gives me ...
0
votes
0answers
48 views
MM Replication and Zabbix in slave, Error code 1032
I have multi-master replication and using Zabbix for server monitoring. Since the replication is row based, I get this error in slave everytime which also breaks my replication. The error I get is:
...
0
votes
0answers
32 views
MySQL replication is not happening when piping sql into mysql client
Today I noticed a very strange issue where if I cat a SQL file and then pipe it to MySQL client, replication doesn't happen. These MySQL statements aren't also written to the binlog. But replication ...
0
votes
0answers
31 views
ERROR while doing coalesce partition Mysql Cluster
My table contains 3299000 rows. My Mysql Cluster contains 2 storage nodes with NoOfReplicas=2. By default it contains 2 partitions and after that i added 3 partitions more one by one using command ...
0
votes
0answers
26 views
After optimize table statement my data usage increased Mysql Cluster
I deleted 500000 rows in my mysql cluster table having 12468000 rows and then i tried to OPTIMIZE TABLE so that all the freed up data memory can be used by another table also, but after optimizing ...
0
votes
0answers
19 views
Re-use disk data extents after delete Mysql Cluster
In my mysql cluster i have 1 million rows and my disk data tables are using all the disk usage available to them ;i.e; free extents are 0 . Now i deleted 50K rows from ap_perf_tbl but still it show ...
0
votes
0answers
22 views
Why data usage increases when deleting rows Mysql Cluster?
My mysql cluster data nodes data usage increased to 95%. Then i tried to delete 500000 rows and my data usage of nodes started increasing initially and reached to 99% and above and TABLE FULL error is ...
0
votes
0answers
50 views
Mysql query is very slow
I am developing a website like any other social networking site using mysql.
I wish it give people suggestion to my users, and I have implemented this functionality in my application, but It is ...
0
votes
0answers
30 views
mysqlbinlog (mysql5.5) output errors out (char length)
wondering why an invalid insert (exceeds char length varchar(100)) breaks when piping mysqlbinlog output to RDS-- when pasted into a client and run separately-- it inserts, truncates, and warns ...
0
votes
0answers
20 views
View is not dropping
I tried removing a view in HeidiSQL, and it froze bunch of times. So I connected to a database with Putty, ran a command
DROP VIEW item_view;
and nothing happened, its still running and command ...
0
votes
0answers
70 views
Migrating database from OSX to Linux
I followed the setup in below link, to setup mysql. Basically, I am have a master on OS X server and want to setup a linux slave.
Create a mysql slave database from master database
After I copied ...
0
votes
0answers
23 views
How to get details of changes in point in time recovery using mysqlbinlog
I have written a shell script for point in time recovery using binary logs( Point-in-Time Recovery Using Event Times). I'm using the start time as the previous full-backup start time and end time will ...
0
votes
0answers
48 views
Returning multiple resultset from the same table
i am working with mysql procedure. final result set i have got in to one table.
now i need to return for each set i need to return the value.
example:
like this is contents in my the table.
i need ...
0
votes
0answers
194 views
What to do to reduce first byte time after removing slow queries?
I've set long_query_time to 1 sec and found only 2 query which took 1 sec to execute at 2 different points of time since yesterday. So I don't suppose such 2 queries cause my first byte time to be 3 ...
0
votes
0answers
87 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 ...
0
votes
0answers
110 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,
...
0
votes
0answers
159 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
...
0
votes
0answers
174 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 ...
0
votes
0answers
154 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
0
votes
0answers
54 views
memc_delete called from a MySQL trigger loop fails randomly
I'm calling memc_delete from a MySQL trigger's code. The call is inside a loop. Logging the results, I see that memc_delete sometimes returns 1 (ok, memcached entry deleted) and sometimes returns 0 ...
0
votes
0answers
58 views
How to perform several operations at DB level
I need to insert 25 000 or 50 000 test rows for make some performance tests in several tables in my DB. I'm using PHP but the script takes to long to run so in shared hosting is a pain! I'm asking if ...
0
votes
0answers
267 views
Potential cause for memory leak
I have a replication setup where one of those nodes started experiences a slow but steady memory leak.
The setup is kind of like this
M---->S
|
\/
FilteringSlave
| | | |
R1 R2 R3 ...
0
votes
0answers
52 views
Partition manager utility/script?
Does anyone know of any Utilities or scripts out there for partition management. Most of our partitioning is range based and I have a set of home grown scripts to expand as needed as well as maintain ...
0
votes
0answers
40 views
How can I make MySQL rework its foreign keys?
I somehow tricked MySQL into believing I have a foreign key that does not exist.
I had a table with both an Id column and a Number column. I wanted to merge the two, so I cleared the few tables ...
0
votes
0answers
99 views
slow response from MySql server
We just moved our database from an old server to a new one with more processing power and 4G RAM. However, for some unknown reason, the execution of our store procedures became even slower. An ...
0
votes
0answers
111 views
Does full text search allow partial world
Say I want all restaurants that contain sushi
My user search for sus, ush, and shi
Now I want mysql to efficiently find rows whose title or description (or whatever) contain sus, ush, and shi, ...
0
votes
0answers
144 views
Nearest neighbor search for 1.6 million points in myisam mysql
Say I want to find 20 closest business near me. Latter I want to see the next 20 closest, etc. Yes I have myisam. I have spatial indexes on the point. I am looking for actual command.
Note:
I am ...
0
votes
0answers
193 views
Mysql 5.5 hangs from time to time with ubuntu 12.04
I am using Mysql 5.5 from ubuntu 12.04 64 Bits with both MyIsam and Innodb engines : From time to time, when I do some 'big' requests like dropping a database, altering a table, adding an index on a ...
0
votes
0answers
154 views
Are there any caveats to install mysql 5.5 in CentOS 5?
I need to install mysql 5.5 in cent OS 5. As Cent OS 5 is a little bit old is there any odd behavior when you want to install mysql 5.5 on Cent OS 5?
0
votes
0answers
210 views
Making chain of Master-Slave servers in mysql
I have three servers, Server X, Y (Version 5.1.54-1ubuntu4-log), and Z (version 5.5.18-2build1~ppa1~oneiric-log).
I have the main mysql DB on X (innodb DB).
now I have created Master-Slave from X to ...
