MySQL : Open-Source, Relational Database Management System
0
votes
1answer
23 views
How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
I have innodb_file_per_table set and just today my ibdata1 file jumped from 59M to 323M after I made several changes to an 800M table to reduce it to about 600M. That particular table's .ibd file was ...
0
votes
1answer
169 views
MySQL Dump configuration file
I modified th my.cnf file, so that the innodb_data_file_path points somewhere else from the default path. But the mysqldump command seems like trying to dump from the default path.
I found that if i ...
2
votes
1answer
41 views
Optimize UNION query in MYSQL
I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, > 35 ...
0
votes
1answer
135 views
consequences of using “innodb_flush_method = O_DIRECT” without having a battery backed write cache? or on a KVM guest?
Mysql 5.5.29 Innodb- 128GB Ram - 32 cores - Raid 10 SSD.
Our server which is a dedicated KVM guest on a 'baremetal' is hosting our heavy read-write DB server.
Everything is file-per-table. ...
0
votes
2answers
39 views
Is there slowdown inserting into an InnoDB table that has no index set?
I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such.
Those tables only contain a few thousand rows.
Would it be faster to INSERT data ...
0
votes
1answer
266 views
How can I set a default session sql_mode for a given user?
I want to have a default session sql_mode that is different from the global sql_mode.
I can set it manually, but I'd like to do it automatically.
I thought I could add it to .my.cnf, but that ...
1
vote
3answers
202 views
+50
TRUNCATE TABLE statement sometimes hangs
Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue?
I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB.
...
4
votes
2answers
8k views
MySQL: how to restore table stored in a .frm and a .ibd file?
I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install ...
2
votes
1answer
18 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
53 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
vote
1answer
114 views
MySQL - run SELECT statement on another server without defining the table structure
In MySQL I can query information on another server using federated tables, as long as I've defined the same table structure locally.
In MS SQL Server, however, I can run any SQL statement against a ...
4
votes
3answers
2k views
Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?
I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL?
I understand that where platform interoperability and/or backwards compatibility may be ...
0
votes
0answers
16 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
1answer
44 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 ...
1
vote
2answers
34 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
22 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
0answers
299 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 |
...
0
votes
2answers
27 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
votes
0answers
32 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 = ...
1
vote
1answer
434 views
Replicating data from Oracle to MySQL
I work with a vendor that does data analytics, and they currently receive a replication stream from some of our databases using a product called Goldengate (which is very expensive). Goldengate has ...
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 , ...
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, ...
3
votes
1answer
857 views
MySQL: Lock wait timeout exceeded
I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running
ALTER TABLE foo DROP FOREIGN KEY fk_foo_1;
Today while it was running I checked in ...
0
votes
1answer
53 views
Why would SequelPro only import 23k rows out of 130k?
I use SequelPro for mysql on a Mac OS X -- and I used the import function to upload a 130k .csv file to my database. Everything seems to work fine, then I get the message File Read Error: An error ...
1
vote
1answer
9 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
56 views
Why some of mysql system variables are readonly
There are almost more or less 277 mysql server system variables .
In these variables some of the variables are dynamic and few are not.
I don't under stand what is the reason behind read only ...
0
votes
1answer
35 views
MySQL server crashed.
Help! I managed to crash MySQL last night. I am on a Mac using the native version that came with Mountain Lion. I was upgrading from 5.5 to 5.6. I have followed instructions in this forum to delete ...
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
37 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 ...
3
votes
1answer
36 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 ...
1
vote
1answer
61 views
Is there a way to recover this database (MySQL)
I did a "cold" (db offline, all filesystem files) backup of a MySQL db.
I wrote the backup to an ISO image. Unfortunately Joliet filenames are not default.
I didn't realize that until attempting to ...
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 ...
4
votes
2answers
81 views
Partitioning and freeing disk space
I am running MySql innodb with innodb_file_per_table.
As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need ...
0
votes
1answer
35 views
How to migrate data from SQL database to MySQL including flat files?
one of my friend has asked some asp developer to make online application where user can upload files also. jpg, doc, pdf and all these files are saved in SQL database as flat file. Now my friend asked ...
0
votes
1answer
72 views
Backup plan for MySQL NDB cluster databse not innoDB
I have a Database which will grow more than 250GB all data is in NDB engine(2 datanodes) and no other mysql engine used for data store.
What are the best approaches or plans for MySQL Cluster NDB ...
1
vote
3answers
30 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 ...
3
votes
1answer
635 views
Linked tables versus stored procedure performance in MySQL
Suppose I have a sub-form, in datasheet view for multiple records, that gives the user full CRUD control over a particular MySQL table. (The records are filtered by the parent-form).
Option 1 is to ...
6
votes
1answer
176 views
MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`
I know very little about database administration but I have to deal with some very large tables on my site.
This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
2
votes
2answers
78 views
How to optimize a log process in MySQL?
In my project, I have about 100.000 users and can't control their behavior. Now, what I would like to do is log their activity in a certain task. Every activity, is one record which includes columns ...
1
vote
1answer
30 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
1answer
83 views
MySQL database drop insanely slow
I just installed MySQL 5.0.45-log Source distribution on my Debian 6.0.6 server.
I installed it under my user home directory like I'm used to doing.
But this time the queries are extremely slow to ...
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
...
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
1answer
16 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 ...
4
votes
1answer
477 views
Designing a database for a site that stores content from multiple services?
I'm building a site that implements David Allen's Getting Things Done that pulls in your email, Facebook newsfeed, tweets from those you follow on Twitter, and more services are planned. The problem ...
0
votes
2answers
39 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
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,
...
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, ...
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 ...
0
votes
2answers
78 views
High Mysql Load , over 700% CPU
I had high mysql load on server linux 64 bit , 24 G.B ram , Intel(R) Core(TM) i7 CPU 950 @ 3.07GHz ,
Alot of quiers in sending data mode
Here is mysql status
...
