MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.
0
votes
2answers
419 views
What is the difference between optimize table and analyze table in mysql
What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.
0
votes
1answer
37 views
Redo Logs Mysql Cluster
The mysql cluster has 4 directories namely D8 D9 D10 D11 for redo logfiles.
But while inserting into table only one redo directory is used.
I ran following command to get redo information "all dump ...
0
votes
1answer
89 views
How to set unique columns combination
see this table:
CREATE TABLE IF NOT EXISTS `default_messages_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message_id` int(11) NOT NULL,
`owner_user_id` int(11) NOT NULL,
`to_user_id` ...
0
votes
1answer
132 views
What database do most mobile start ups use? [closed]
mongodb?
MySQL?
PostgreSQL?
If someone would add pluses and minus, let me know.
Why I ask:
I am a mobile startup. I need a database that supports spatial indexes. The spatial index must be able to ...
0
votes
1answer
1k views
Change path for binary logs and scheduling
I want to change the default name of binary log and path. I saw the documentation where it discusses about base_name. However, I just need to confirm how to use it correctly. I want binary logs to be ...
0
votes
1answer
41 views
Misunderstanding statement in MySQL documentation
I can't understand this statement in Optimizing Data Size:
Declare columns with identical information in different tables with
identical data types, to speed up joins based on the corresponding
...
0
votes
1answer
42 views
Limit Access To mysql information_schema
In case of mysql attacks, many times user extracts information from information_schema.
can i do something in mysql that can restricts user to xtracts information from information_schema while all ...
0
votes
1answer
60 views
MySQL Slave Lagging Almost Daily -Ver 5.5.17 ( Master & Slave )
I am observing this lag in a continuous manner. Investigating in the bin log events, i found
the following information. Slave executing the relay Logs slowly.
1. Changing the Innodb File Format ...
0
votes
1answer
60 views
What would be the first things you do to find out about an MYSQL environment you are taking over?
I am new to DBA role, and wanted to know if there was any commands (or free software) that come with Mysql 5.5 (master/slave/slave) replication to quickly get familiar with
the database, and also ...
0
votes
3answers
448 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 ...
0
votes
3answers
673 views
How to Restore data from binary log
I am trying to restore some lost data , but I am not having its backup .So I am trying to restore from the binary log .Please explain how to restore from the binary log in mysql .
0
votes
1answer
138 views
Is possible to optimize this query or it's already optimized?
I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one:
--
-- Table structure for table `default_users`
--
CREATE TABLE IF NOT EXISTS `default_users` (
...
0
votes
1answer
51 views
2 Very similar queries return different number of result (one is null)
I want to find businesses near a point that contain a word that start with sea
lat=-6.185068&long=106.773121&keyword=sea&distance=5000&startfrom=0&howmany=20&prominent=5
...
0
votes
1answer
150 views
Which is the best tool to monitor replication on MySQL 5.5.20?
I have just installed and setup replication for 3 MySQL 5.5.20 server( 1 Master/ 2 slaves). I intend to add more slaves to the equation.
Which is the best software tool to monitor the replication ...
0
votes
1answer
65 views
Practices to keep SQL Server databases easy to port to MySQL?
We've developing an ASP.NET Web application that is to be designed for MS SQL Server 2008. I'd like to minimize my future dB admin's headaches should there be a need to migrating over to MySQL.
With ...
0
votes
1answer
20 views
ERROR 13 (HY000) When Creating Database
I'm trying to create a database and when I type in CREATE DATABASE epik; I get the error ERROR 13 (HY000): Can't get stat of './epik' (Errcode: 13) I am running kubuntu 13.04 with mysql 5.5.31.
0
votes
1answer
37 views
Do certain string patterns affect query performance on a VARCHAR column?
I am a beginner on MySQL Administration and performance problems. Now this is confusing me.
I have a VARCHAR column identity, the string in it is consisted of a Class type and an id, like note:123, ...
0
votes
1answer
40 views
Dealing with empty strings while loading a table from a CSV
I am trying to load a CSV file into a table. The CSV file contains some empty strings for example:
1,2,,4
I want to load the empty field as null. One way I know is to use \N i.e:
1,2,\N,4
but it ...
0
votes
1answer
34 views
Optimizing Query
Can anyone help me tuning this query.....
explain extended
SELECT DISTINCT a.msisdn FROM `std_msc_opr_wise` a
LEFT OUTER JOIN
(SELECT msisdn
FROM as_treat_pre_usage_30days
GROUP BY ...
0
votes
1answer
50 views
Creating a partial unique constraint for MySQL
I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL.
My ...
0
votes
1answer
82 views
Amazon RDS MySQL performance issues
I have published my website on Amazon EC2 (Singapore region) and I have used MySQL RDS instance for the data storage. Everything is working very fine except performance.
It seems that all queries, ...
0
votes
2answers
52 views
Error log - What does this mean and what can I do about it?
I have the following repeated errors in my error.log and I don't know what it means or what I should/can do about it:
Access denied for user 'root'@'[some IP]' (using password: YES)
There are ...
0
votes
2answers
80 views
Should I disable triggers, timestamps in a slave in mysql?
We're using MySQL. We have a master that eventually will have 2 slaves. There are triggers in the db that execute when data changes in certain tables. I am wondering whether to disable the triggers in ...
0
votes
1answer
294 views
MySQL slave stuck in “Reading event from the relay log”?
5.5.28-log MySQL Community Server (GPL) by Remi
binlog-format=MIXED
My problem is similar to this question.
*************************** 2. row ***************************
Id: 973415
User: ...
0
votes
1answer
29 views
MySQL not allowing text columns?
I'm using MySQL 5.5.27. I currently have a BLOB column, that I want to convert to text to allow fulltext searching. The ALTER TABLE command to create a text column is successful, but the ALTER TABLE ...
0
votes
2answers
85 views
How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist
I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the ...
0
votes
1answer
149 views
How to get only month from MySQL date/time?
In my MySQL server, I have rows that have the date of my created content. Example: 2013-03-17 02:53:47
For now, I am using:
$date = $fetch[thedaterownamegoeshere];
<?php echo $date; ?>
But ...
0
votes
3answers
48 views
Using MySQL InnoDB as an Archive
My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily ...
0
votes
2answers
158 views
MySQL Table not repairing
Table info:
Database name: user_motiva
Table name: wp_options.frm wp_options.MYD wp_options.MYI wp_options.TMD
when I do a mysqlcheck -r --all-databases it gets hung on that table even if you ...
0
votes
1answer
96 views
Create a mysql slave database from master database
I have a mysql master database running. I want to have a slave database. So, I stopped the master mysql database and copied it to the slave machine. I had bin log enabled on master so even those files ...
0
votes
3answers
223 views
Mysql optimization help
I've tried optimizing mysql as good as I could, but apparently I'm not to good at it :-)
So I come to you guys for help.
Server specs are:
AMD Opteron CPU 8 cores
16 GB RAM
2x2.000 GB HDD 7.200 RPM ...
0
votes
1answer
725 views
Connect Error (2054) - mysql_old_password issue still not solved
i use php 5.4.3 and i want to connect to a MySQL DB remotely (php script resides on a different server than the db) with normal oop way:
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
...
0
votes
1answer
757 views
Mysql 5.5: Optimizing all tables with mysqlcheck - is it useful to run the -check option along with -optimize?
I plan on running 'mysqlcheck --optimize --all-databases' to clear up some fragmentation.
Would it be useful to run -check with this option or does the -optimize already take care of that?
I am only ...
0
votes
1answer
44 views
Storing user search keywords
I need to store search keywords in a database to analyse user needs.
How efficient should my schema be?
I'm thinking like this:
CREATE TABLE SearchKeyword (
ID INTEGER PRIMARY KEY ...
0
votes
2answers
88 views
Best Way to Partition By City / State in MySQL 5.5
I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so:
ALTER TABLE ...
0
votes
1answer
34 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.* ...
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
317 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 ...
0
votes
1answer
53 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 ...
0
votes
1answer
586 views
MySQL 5.5 not starting after master-master configuration
The following master-master configuration worked on MySQL Server 5.1.x(Ubuntu 10.04).
Master1(192.168.2.80)
##################################
#MySQL Replication
##################################
...
0
votes
1answer
397 views
MySQL INSERTs waiting for UPDATEs (MyISAM)?
In show processlist;, MySQL 5.5, we see that INSERTs tend to wait for UPDATEs to complete before they can proceed. This is on MyISAM tables.
We tried INSERT HIGH_PRIORITY and UPDATE LOW_PRIORITY. ...
0
votes
1answer
741 views
mysql closing tables taking double the time of process
I have a delete query that is taking a long time. When I check in processlist that status is showing as closing table.
It is taking a long time to close tables.
ex: I run a query and the total time ...
0
votes
2answers
697 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 ...
0
votes
1answer
70 views
Can MySQL be patched
I just saw a note about a security issue in MySQL so thought that I'd better patch my test server just in case. The MySQL site is not very helpful and a quick google revealed lots of people asking how ...
0
votes
1answer
1k views
MySql 5.5 in ubuntu repository
Sorry for duplicate question.
I am looking for the best way to install mysql 5.5 on ubuntu.
So far, there are few options:
Installing from tar.gz
Installing from PPA.
Alien on RPM.
All of them ...
0
votes
0answers
13 views
Will Partitions and Indexes on the same table help in performace of Inserts and Selects?
I have a table containing the list of visitors and this table has the following information.
Visitor Browser Information
Visitor Location Information
Visitor Time Information
No of Visits
I have a ...
0
votes
1answer
46 views
Errors while creating multiple mysql-5.5 instances
I have installed 3rd mysql instance on my testing server.
2 instances already running without any issues.
When I installed 3rd instance by mysql-5.5.30 zip source, it installed successfully but when ...
0
votes
0answers
20 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
2answers
60 views
How do I use subquery on the same table in MySQL?
I have a query like this which takes a really long time to run. The table is around 4 million rows.
DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;
I'm hoping I could ...
0
votes
1answer
28 views
Unable to use mysql_upgrade:
I updated mysql recently and found problem related to privileges and backup. When I am using mysql_upgrade command it says-
mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check ...
