MySQL : Open-Source, Relational Database Management System
1
vote
1answer
12 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
votes
0answers
23 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
13 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)
2
votes
1answer
17 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
22 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
13 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
18 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
0answers
29 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
18 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
59 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
0answers
39 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
0answers
26 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
24 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
35 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
40 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
29 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
36 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
68 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
30 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
22 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 ...
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 ...
1
vote
1answer
25 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
15 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 ...
1
vote
1answer
22 views
Insert Row With Foreign Key Equal to ID - Circular Reference
In MySQL database I have an events table which has a circular reference to itself with the following columns.
id (PK) | user_id (FK) | event_id (FK) | title
Each new row inserted has a circular ...
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
4answers
33 views
Master/Slave without populating tables
So I have some Zabbix instances which are using MySQL for their backends. What I would like to do is, for DR purposes, backup the DBs for each instance using a Master/Slave configuration, with the ...
0
votes
1answer
29 views
“relation does not exist” trying to import mysql dump into postgres
environment:
ubuntu 10.04
mysql server 5.1.69
postgres 9.2
Here's the sequence of steps:
created a new postgres database, myDatabase
executed this command: mysqldump -u root -p ...
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
23 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
2answers
25 views
Query WHERE clause and JOIN
I'm having an issue using a WHERE clause and JOIN.
SELECT * FROM `CallDetailRecord`
WHERE `StartTime` >=1357102799000
AND `StartTime` <=1357880399000
JOIN `CallEvent` ON `EventID` = ...
1
vote
2answers
27 views
Federated tables and triggers
This is the scenario.
Have two MySQL servers (S1, S2) on different machines, with a database on each (DB1, DB2).
I have a table (T2) on DB2 that needs to "fectch" rows from another table (T1) on ...
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
1answer
9 views
Simple MySQL table copy from one server to another?
this question has kindof been answered in a number of ways. Nothing that quite fits the bill (as far as I understand it).
I have MySQL Table X on Server A. I would like an exact copy of this table, ...
0
votes
2answers
50 views
Hierarchical Structure
Every single user has say, 3 of GROUP_A, 10 GROUP_B's per GROUP_A, and 20 GROUP_C's per GROUP_B. And each of the 20 GROUP_C's involve lots of inserts/deletes...
I'm not an expert, but I've done ...
0
votes
2answers
44 views
Is it a good practice to create tables dynamically in a site?
A friend asked me to build a site with a few "static" and "dynamic" tables.
In fact he wants to have a few tables which can't be deleted, and some "dynamic" tables which can be created directly from ...
0
votes
2answers
37 views
Time series data for ad platform
I am trying to figure out how to store time series data for an ad platform I am working on.
Basically I want to know some strategies/solutions for storing billions of rows of data so that I can ...
0
votes
1answer
43 views
Search speed increase
I've heard that some queries like DELETE, or changing the structure of a table can decrease the speed of the search in a table.
First: is this true , or not?
Second: is there any way to increase ...
1
vote
3answers
60 views
Issue on mysqldump in MySQL5.6
I did upgrade the MySQL server from 5.5 to 5.6
I am trying to take dump for a table like this
mysqldump -uroot -p dba admin > D:\admin.sql
But it gets following error
mysqldump: Couldn't ...
2
votes
2answers
61 views
Recommendation for mySQL table structure (no code)
I need some advice on how to layout the tables for a mySQL database.
I will first describe what I am trying to do:
I have a table for users. (This covers basic info for now)
A user will select a ...
0
votes
0answers
18 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
37 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
1answer
29 views
Why does copied MySQL database have different data than source?
I am trying to migrate database A to database B. I have tried various methods for this, including...
Using Navicat, do Tools > Data Transfer, copying structure and data from A to B
Using Navicat, ...
1
vote
2answers
25 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
2answers
66 views
+50
Drop partition not freeing disk space
I am running MySql innodb with innodb_file_per_table.
As a daily script, I create a new partition for a set of tables, as well as drop yesterday's partitions. I have noticed that through the df ...
3
votes
1answer
34 views
skip columns while using LOAD command mysql
I have a csv file having 5 columns and i want to load only one out of that, is there a way i can do it. I want to use LOAD statement of MySQL 5.5
Any pointers are welcomed, help would be much ...
0
votes
0answers
23 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 ...
1
vote
0answers
26 views
How to sync mysql remote database daily ?
I have a database that contains orders , products ..etc. I have another system that generates reports based on this database. Due to heavy load i want to separate those systems Where i have the master ...
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
18 views
Partitioning MySQL for “expired” transactions to improve performance
I am dealing with an existing application that uses the database as a sort of transaction log in several cases, for example orders or payments. These tables are large (20 - 60 million rows) and poorly ...
