MySQL : Open-Source, Relational Database Management System
0
votes
0answers
8 views
Is it possible to force delete of mysql tables with FK?
For deleting a table in which a Foreign Key is used in other tables, it is necessary to delete all other tables having FK connection with the corresponding table.
Is there a short way to delete a ...
0
votes
2answers
23 views
Label data according to percentile
I have a table name temp with column customer_id , sum and I need to add extra column customer_label (may be on view) which is 1 if customer lies in top 10% according to value of sum, 2 if lies within ...
1
vote
0answers
17 views
MySQL Partitioning Performance
I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM.
I am unable to index ...
0
votes
0answers
12 views
MySQL CSV LOAD DATA INFILE … doesn't want to load more than 72 lines
I'm trying to load a .csv file into my database with many lines of input from a website I'm working on. For some reason I can load files multiple times (over and over) into the database but they have ...
-2
votes
0answers
24 views
Creating a server on client machine [closed]
I'm trying to learn SQL and this post is regarding two questions.
I know that MySQL is already installed on my machine (OS X 10.8) and I've seen the command line, but I'm not quite sure how to set ...
0
votes
1answer
23 views
How often should MySQL (stock, Percona, etc.) be upgraded?
My organization runs several data-driven web applications backed by various flavors of MySQL on RHEL (i.e. we're just like everyone else!) Some of our web apps use the standard build of MySQL, and we ...
0
votes
0answers
10 views
Unable to restore cpanel database backup
I recently created a complete backup of my cpanel to move it into another server. Now the insert queries generated by cpanel for wp-posts table (corresponding to WordPress posts) are too big since ...
1
vote
1answer
18 views
Trigger not Working
I created trigger on table PENDING.
Pending table has 3 columns
uniqueId
duration
maxDuration
I have another table COUNT with 2 columns
req_id
total
Here is my trigger--
CREATE TRIGGER ...
0
votes
1answer
10 views
Mysql settings for query_cache_min_res_unit
What is the best setting for query_cache_min_res_unit for these results:
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| ...
1
vote
2answers
18 views
Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating
Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
0
votes
1answer
17 views
MySQL Err 1032 on only 1 Slave
I'm getting a very strange error on one of my MySQL servers and I'd appreciate some help.
The Setup
I have a separate environments.
* Environment 1 consists of the Master Server and 1 Slave.
* ...
0
votes
2answers
15 views
How can I add a column to multiple tables at once in MySQL Workbench?
I have a MySQL Workbench model (diagram) that belongs to a Ruby on Rails application. I now need to add the created_at and updated_at timestamp columns to each of the tables. What is a quick and easy ...
2
votes
2answers
26 views
Trigger for updating a count of rows from a table into another table
I have these two tables:
mysql> select * from orders;
+-------------+---------+
| Customer_ID | Item |
+-------------+---------+
| A01 | CPU |
| A01 | Monitor |
| A02 ...
2
votes
2answers
37 views
MySql is not optimizing the query properly
I have a table structure as follows :
CREATE TABLE `sale_product_inventories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sale_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`size` ...
2
votes
4answers
61 views
Drop tables where the last update is over 1 week ago
I can see in PMA that it stores information for every table.
I was wondering if there was an SQL query to drop any table in the database that have a last update time of more than 1 week ago?
-1
votes
1answer
30 views
Performance issues with query of many joins on MySQL server
We have serious performance issues with a query joining a lookup table (stores key values pairs) around 20 times. The lookup table contains around 100.000 entries.
SELECT DISTINCT
...
1
vote
2answers
38 views
Where condition execution sequence in MySQL
I am using MySQL 5.0.88. I have a query like:
select DISTINCT party.id
FROM party
LEFT join party_identifier AS pi
ON pi.party_id = party.id
WHERE (pi.source_note=?
AND ...
0
votes
0answers
17 views
Where condition execution sequence in MySQL [duplicate]
I am using MySQL 5.0.88. I have a query like
select DISTINCT party.id FROM party LEFT join party_identifier AS pi ON pi.party_id = party.id WHERE (pi.source_note=? AND first_name LIKE ? OR ...
0
votes
2answers
29 views
mysqlcheck: unknown variable 'max_allowed_packet=100M'
I get the following error when I try to use mysqlcheck, mysqlrepair or mysqlanalyze:
mysqlcheck: unknown variable 'max_allowed_packet=100M'
I am trying to find whether there are any corrupt tables ...
1
vote
1answer
10 views
Percona xtrabackup Prepare Fails
I made a backup and according to Percona it completed successfully. Now I'm trying to prepare it and having the following issue:
./xtrabackup_55 --defaults-file="/etc/my.cnf" ...
0
votes
1answer
13 views
JOIN to read first table and get possible relationship in the second table
Consider a main table as
CREATE TABLE groups
(
group_id int(11) NOT NULL AUTO_INCREMENT,
group_title varchar(255),
PRIMARY KEY(group_id)
) ENGINE=InnoDB
and a second table for relationship as
...
2
votes
2answers
40 views
MySQL version confusion
How is this possible:
I installed mysql-5.6.11-osx10.7-x86.dmg which is supposed to be version Mac OS X ver. 10.7 (x86, 32-bit), DMG Archive (32 bit!)
But upon SHOW VARIABLES LIKE "%version%"; I get ...
-1
votes
0answers
34 views
How can I delete duplications with one query? [closed]
Is there any way to delete duplications with one, or only two queries? I'm not good with SQL.
Now I'm trying this solution:
$le = mysql_query('SELECT * FROM tbl_answers');
while($i = ...
2
votes
1answer
32 views
How can I include more query in one query?
These are my tables:
tbl_answers =>
aid
qid
answer
uid
dateposted
emailnotify
namedisplay
status
isbestanswer
tbl_questions =>
qid
question
...
0
votes
1answer
17 views
Does MySQL reserve memory for MEMORY tables based on max_heap_table_size
Currently I have a set of large MEMORY tables, and have previously calculated how much space I believe each will require and assigned max_heap_table_size accordingly so that the sum is not greater ...
1
vote
1answer
31 views
Migration of create statement from SQL Server to MySQL
I have a table that have the following table structure
CREATE TABLE [dbo].[ASAT](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ASATID] AS ('ASAT'+right('00000000000000000000'
...
0
votes
0answers
10 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,
...
1
vote
3answers
31 views
Creating a global temp table in MySQL
I am working on a MySQL stored procedure.
I need to create a temp table and I want to access this temp table whenever I execute this stored procedure.
I know we can't access the temp table in ...
0
votes
2answers
41 views
Is there a good reason to store big text data outside database?
I was analyzing my MySQL database performance and found that tables containing text fields are just huge, larger than 1Gb.
This data is only used when loading single record (like article or blog ...
0
votes
0answers
23 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 ...
3
votes
1answer
39 views
Best strategies to have a backup of an RDS MySQL database
I have an AWS RDS MySQL database. I'm not a DBA, so my knowledge is limited.
I wish to design strategies to have zero data loss in case of a failure, in terms of replication or data backup.
I know ...
0
votes
1answer
37 views
MySQL Workbench - question about creating FK relationships
Forgive me a little new at databases and I came across something while working with Workbench that I have a question on.
In a very simple testing database, with three tables: Company, Department, ...
-2
votes
0answers
14 views
How to fix error: Connection is busy with results for another hstmt (SQL-HY000) [closed]
Connection is busy with results for another hstmt (SQL-HY000)
3
votes
1answer
23 views
Will creating a view on a MySQL replication slave break the replication?
We need to run some very ugly queries on a MySQL replication cluster.
Does anyone know if creating a view directly on the slave will break the replication of the other, otherwise untouched data ...
2
votes
2answers
25 views
MySQL many to many relation
Sorry is this seems a stupid question, but I have a table of server addresses and a second table with a list of hardware devices which use the addresses, it is possible for multiple devices to use the ...
2
votes
1answer
22 views
Percona Node fails to join: Operation not permitted
Joining a new Percona node fails with:
130521 7:15:44 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group ...
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, ...
1
vote
1answer
19 views
MySQL Workbench connect to MS Access
I have a .mdb file on my computer that I would like to convert into a MySQL Schema and I have found that there is a migration tool within Workbench. However it doesn't seem to allow you to connect to ...
6
votes
2answers
75 views
Is there any way to trace optimizer's work in MySQL?
Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Is there anything in MySQL through which I can get the information ...
1
vote
1answer
51 views
ORDER BY optimization issue
I have a query:
SELECT
user_details.id , user_details.first_name , user_details.last_name,
user_accounts.name account_name, jtl0.account_id account_id,
user_details.title , ...
1
vote
1answer
43 views
Which one to use? InnoDB Replication or MySQL Cluster?
I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual ...
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
...
-1
votes
2answers
37 views
format of mysql query log
What is the format of the mysql query log? In particular, for lines like:
133 Query commit
133 Query rollback
What does '133' stand for? And is it true that each line represents a round ...
0
votes
1answer
41 views
Make rows immutable allow Insert mysql5
I want to make the rows in a Mysql table immutable, once they are inserted I don't want it to be possible to change them. How can I do this?
(ideally returning a suitable error message if any change ...
0
votes
0answers
30 views
Match two large tables finding similar data [closed]
I have two tables in MySQL. Table 1 contains much data, but Table 2 contains huge data.
Here's the code I implement using Python
import MySQLdb
db = MySQLdb.connect(host = "localhost", user = ...
0
votes
2answers
55 views
MySQL Master/Slave. Actual use of Slave Machine
We have a MySQL server running in a mobile production trailer. The trailer is out on the road for weeks at a time. We have cell internet and wifi access is nearly all of our locations. Our office ...
1
vote
1answer
99 views
How to recover/restore corrupted Innodb data files?
A while ago, my Windows 7 system on which a MySQL Server 5.5.31 was running crashed and corrupted the InnoDB database. The weekly backup that's available does not cover all the tables that were ...
1
vote
1answer
34 views
Allocating 8GB memory to MySQL on a 64bit system
Specs -
MySQL 5.0.x , Redhat 5.9 , Physical memory - 16GB
I am trying to set innodb buffer pool size to 8GB (innodb_buffer_pool_size=8G). When i do, and start mysql, i get following error -
...
1
vote
0answers
26 views
Storing history of full/partial tables
I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables.
I wonder if it's efficient to store a lot of rows ...
