MySQL : Open-Source, Relational Database Management System
1
vote
1answer
112 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 ...
1
vote
3answers
13 views
mySQL - Drop tables where 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 a SQL query to drop any tables in the database that have a last update time of more than 1 week ago?
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
15 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
43 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
30 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
18 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
1answer
296 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
25 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
29 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
432 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
855 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
...
1
vote
2answers
34 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
35 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
60 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
54 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
634 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
169 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
31 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
42 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
21 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
77 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
...
1
vote
2answers
30 views
Is it better to use AUTOCOMMIT = 0
Is it better to use (for perfomance tuning) AUTOCOMMIT = 0 before bulk inserts of data in MySQL, if the insert query looks like
INSERT INTO SomeTable (column1, column2) VALUES ...
2
votes
3answers
7k views
Cannot GRANT privileges as root
Because of having some problems, I decided to re-create all users except for root@localhost. This works fine, but the newly created user has no right to do anything. What I want is to simply give all ...
1
vote
1answer
571 views
MySQL 5.6 on OS X 10.8.2 disconnects on user creation?
Via Homebrew I just upgraded from MySQL 5.5.29 to 5.6.10 and all my queries and existing users are working fine, but whenever I try to modify permissions or create users now MySQL disconnects me. Are ...
2
votes
2answers
23 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 ...
3
votes
1answer
22 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 ...
0
votes
2answers
39 views
TokuDB/InnoDB Question
What's the faster scheme for insert data to TokuDB or InnoDB Engine? Over 200 Insert per second.
That's okay to answer of one engine.
TABLE: `game`
`gameId` bigint 20 (PK)
`gameMapId` int 4
...
0
votes
0answers
14 views
Setting master user not working in MySQL [closed]
I am follow these instructions to set up a master user in MySQL:
Log in to your Slave server with root privilege as:
mysql -u root -p
Create a new database named magentodb and import the ...
-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)
0
votes
1answer
74 views
Second time query execution using different constants makes faster?
Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with ...
