MySQL : Open-Source, Relational Database Management System
1
vote
0answers
62 views
MySQL: Assessing performance of a query by using EXPLAIN
It is said that the number of rows are scanned is smaller the performance is higher when use Explain to optimize query. So I wonder
When the EXPLAIN output includes many rows, is there any formula ...
1
vote
0answers
96 views
MS SQL to MySQL Conversion
Is there a tool available that converts queries written as triggers, stored procedures and functions written in MS SQL 2008 to MySql? We switched databases and now I need to rewrite a bunch of queries ...
1
vote
0answers
138 views
MySQL: Writing log entries from stored procedure
Is there a standard way to write log messages in MySQL stored procedures?
I can write log messages to a designated log table, but I prefer the standard log mechanism where each message is coupled ...
1
vote
0answers
62 views
need a sql query to find out the Database wait time ratio for MySQL
Oracle supports sql queries to SYS.V_$SYSMETRIC to find out Database Wait Time Ratio (% of wait or bottlenecks experienced in time spent on user-level calls) ...
1
vote
0answers
69 views
What would make a MySQL database create tables in a different collation other than its default?
I am adding tables to a Wordpress database on my localhost and it is changing my collation from utf8_general_ci to latin1_swedish_ci. I'm kinda stumped because I'm not familiar with working directly ...
1
vote
0answers
42 views
Partition guides and possible solutions?
We have a table called tblLink which is the main table and have its primary key in many other tables as shown below. The issue now we want to keep only the latest 3 months data and the rest should be ...
1
vote
0answers
76 views
MySQL: Fixing Unique key constraint violation
I have a database with an unique key constraint.
When I import this database from a dump created by mysqldump I get
'14698-3' for key 'UNQ_CATALOGS EARCH_FULLTEXT_PRODUCT_ID_STORE_ID'
But when I ...
1
vote
0answers
321 views
MySQL join for multiple child records in a single joined row
I have one master table (teacher) structured like
teacherId Name Class
1 Praveen 10
2 John 9
and having a child table (student) structured like
studentId ...
1
vote
0answers
65 views
migrations within transactions
I have a bash script to run migrations on our MySQL databases.
Is it possible to wrap migrations within a single transaction when running multiple migration scripts from the command line?
...
1
vote
0answers
89 views
phpMyAdmin slow for small database
I have a small database (<100 KB when dumped) that will hang in phpMyAdmin when I try to sort by various columns.
Sometimes it hangs when I try to edit columns by directly clicking on their ...
1
vote
0answers
70 views
How can I force myisamchk to modify the original data file in case of duplicate keys?
In the documentation for myisamchk, it says:
--quick, -q
Achieve a faster repair by modifying only the index file, not the data file. You can specify this option twice to force myisamchk to ...
1
vote
0answers
384 views
MySQL Server hitting 100% unexpectedly (Amazon AWS RDS)
We've been struggling with this issue for months.
This week we upped our RDS instance to the highest performing instance and although the occurrences have reduced, we're still seeing our database ...
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
114 views
Improving database design for category based site
I'm working on a site that gets about 30k visits a day and it makes use of a lot of queries which uses UNION's over about 45 different tables, these tables generally range from about 15k rows to about ...
1
vote
0answers
139 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
177 views
MySQL GROUP BY and ORDER BY giving inconsistent results
I'm currently working with WordPress, and I've added a new meta value which is a number between 0 and 30. Zero is ignored and up to 30 need to be organised in order and only the latest (based on the ...
1
vote
0answers
85 views
Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id
I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
1
vote
0answers
746 views
mysql taking too long to send data
I have a simple table with million of records (14,000,000) and for a simple query it is spending too much time "sending data".
The table
CREATE TABLE IF NOT EXISTS details (
id int(11) NOT NULL,
...
1
vote
0answers
206 views
Index usage for database server
How can i fix the Index usage on one of our database server?
It says from our Nagios control panel that:
Service: DB Index Usage
Host: MySQL Cluster
Address: ( our IP )
State: CRITICAL
Additional ...
1
vote
0answers
507 views
SQL query optimization in Prestashop e-Commerce solution
I'm working on a layered navigation module within Prestashop e-Commerce solution.
I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a ...
1
vote
0answers
87 views
Why do we need myisam spatial extension anyway?
If just to determine whether a point is within a rectangle,
What is wrong with this query?
EXPLAIN SELECT DISTINCT
TB.ID,
TB.Latitude,
TB.Longitude
FROM
TableBusiness AS TB
WHERE
...
1
vote
0answers
143 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 ...
1
vote
0answers
409 views
Tables don't exist after MySQL restart
I recently made changes to optimize performance for InnoDB engine-based DB and soon after when restarting I encounter into the following strange behavior:
I have some tables with data in them
I made ...
1
vote
0answers
94 views
Enforce a hard limit on write execution time during Amazon RDS write stalls
I am trying to recover from the situation where Amazon RDS write operations stall (some discussion on why this is here). If I show full processlist, I will see something like this:
318263 myapp1 ...
1
vote
0answers
70 views
Can't connect to MySQL Cluster Storage (data) nodes
I've deployed a MySQL Cluster with a few nodes, and I want to access the storage nodes directly from another machine using NDBAPI.
When I tried to connect from the same host it worked, but when I try ...
1
vote
0answers
727 views
My SQL 5.6.4 DATETIME column milliseconds is not getting stored
Beginning from MySQL 5.6.4, milliseconds support is provided for date columns like DATETIME etc.
I just created a table to check if its working:
create table testdata(startime DATETIME(5));
...
1
vote
0answers
314 views
mysql pre-start process (23901) terminated with status 1
I installed a new Mysql server version 5.1 on machine which was running on 5.0. Now when I try to start the mysql server using command service mysql start it gives me an error start: Job failed to ...
1
vote
0answers
558 views
stored procedure returns ERROR 1305 (42000): FUNCTION does not exist
I have a stored procedure and function as below
CREATE PROCEDURE ProcName()
begin
....
DECLARE curs CURSOR FOR select A.PList, B.nid from persons A inner join articles B on
A.aid=B.id;
....
...
1
vote
0answers
735 views
Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2?
Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2 ?
updated to answer the 3 questions:
mysql> show variables like 'version%';
...
1
vote
0answers
836 views
MySQL 5.5.20 Unable to configure service on Windows SBS 2011 Essentials
I am trying to install MySQL Server 5.5.20 using the latest installer package mysql-installer-5.5.20.0.msi. The Server installs correctly but when I get to the configuration section of the installer ...
1
vote
0answers
218 views
Oracle Flashback Archive aka Total Recall in MySQL
What would you suggest as a replacement system to achieve the same functionality of Oracle Flashback Archive aka Total Recall in other DBMS, in general or specifically in MySQL or MariaDB?
...
0
votes
0answers
4 views
MySQL Relational query
I need a query suggestion.
I have 3 tables with table schema as below:
clause_type:
-------------
clause_type_id clause_type_title
-------------- ------------
1 clause type 1
2 ...
0
votes
0answers
9 views
Would Mongo's ObjectID work well in InnoDB's clustered index?
Mongo's ObjectID is defined like this:
ObjectId is a 12-byte BSON type, constructed using:
a 4-byte value representing the seconds since the Unix epoch,
a 3-byte machine identifier,
...
0
votes
0answers
20 views
Properly index magento mysql tables to improve specific query performance
I am running magento community edition and its db performance is horrible. I have the following query:
SELECT `width_idx`.`value`, COUNT(DISTINCT width_idx.entity_id) AS `count` FROM ...
0
votes
0answers
26 views
MySQL binary log failed to generate a new one
In my Linux standalone machine, I have set up MySQL on it.
In my.cnf, I have the following cofiguration:
[mysqld]
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days = 10
max_binlog_size = 100M
...
0
votes
0answers
14 views
Import - database not found error
When I issue the following command:
mysql -u user -ppassword database_name < /path_to_database.sql
I get:
sh: database_name: not found
And it stops processing. I have created database_name ...
0
votes
0answers
16 views
Percona Xtradb cluster only replicating DDL statements
I'm having a hard time with percona xtradb cluster. The cluster has 3 nodes, and is up and running (all tip top according to wsrep status indicators).
It did the SST perfectly fine from one node to ...
0
votes
0answers
15 views
packages that php need to connect to mysql
I want to know minimum packages that php need to connect to mysql?
I know some packages such as php5-mysql, mysql-common, mysql-client,...
Also I want to know a short description about architecture of ...
0
votes
0answers
15 views
import openoffice .ods file with specific structure like date and value fields
How I can import with keeping the structure of the data like date fields and numiric fields the rest of the fields is text.
With import into mysql with import function from phpmyadmin from an .ods ...
0
votes
0answers
25 views
Mysql 5.6 and install php5-mysql , mysql-common
I install Mysql 5.6 with this documentaion(in ubuntu 13.04) , now I need to install php5-mysql and mysql-common packages. can I install them with apt-get? I think they will install mysql5.5 ...
0
votes
0answers
19 views
mysqlnd: same mysql server and php version, can't connect from Windows
(This question is also in StackOverflow http://stackoverflow.com/questions/16575994/mysqlnd-same-mysql-server-and-php-version-cant-connect-from-windows)
I'm migrating mysql functions to mysqli. As ...
0
votes
0answers
23 views
Optimizing “Removing duplicates” thread state
I'm trying to hunt down MySQL variables/settings and metrics relevant to the "Removing duplicates" thread state.
Here's a snippet from show full processlist.
Id User Host db Command ...
0
votes
0answers
38 views
How much RAM for a large database on a dedicated MySQL InnoDB server?
Hereunder my hardware config:
CPU: 1x Intel Xeon X3470 Quad core @ 2.93GHz
RAM: 4x 8GB DDR3
Disks: 2 x SATA 320GB for the system & 4 x SAS 300GB for the DB
I've upgraded from 12Gb to 32Gb and ...
0
votes
0answers
24 views
Procedure after commit
I have an application which periodically will insert large amounts of data, from that data I build a few materialized views (well emulate by creating a table from a view and replacing it with the ...
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
0answers
5 views
InnoDB Tablespace critical error in great need of a fix
Link to screenshot : http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG
Question: Could the error shown on the screenshot be the reason why my site is very slow?
0
votes
0answers
21 views
developing procedure in sql for global use
what type of complex procedure can i develop in SQL which will make others work easy as well ,
like in Java , i had developed a program for FTP so that anyone can import this program and can use it ...
0
votes
0answers
28 views
How to perform ETL from RDBMS to Neo4j
I have a mature 50+ tables web application based on mySQL. In order to do some advanced data mining I want to use Neo4j and the goodnes of cypher. However I'm having a hard time migrating my data from ...
0
votes
0answers
20 views
'Offline' MySQL backup - server not starting
The MySQL server is no longer starting because the partition is too full. The issue seems to be ibdata (40GB). I wanted to follow these instructions (at 'InnoDB Infrastructure Cleanup')
Now I already ...
0
votes
0answers
31 views
Are there any disadvantages to partitioning on financial year?
Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year.
The majority of ...
