MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
372 views

Partitioning MySQL vs PostgreSQL

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which ...
0
votes
1answer
60 views

Consolidated realtime mysql backup with master/slave setup or binlog

I have 20+ mysql servers and their have low write requirements. So, I want to see if any way I can replicate the data/binlog from these servers to a single slave so realtime backup purpose. If I ...
0
votes
0answers
22 views

mysqlcheck --repair a specific table?

I logged in my server via SSH and committed a "repair table xxx" query on a MyISAM table with 34 million rows and 69GB of data. Unfortunately the SSH connection was lost. When I logged back in I found ...
0
votes
1answer
440 views

How to grant super privilege to the user?

I have created a user and given privileges to the user1. `grant all privileges on db1.* to user1@'%' with grant option; Am using mysql workbench to import dumps to my database. While importing ...
1
vote
1answer
133 views

one node in cluster got crashed ,how to recover it?

we are using percona galera cluster with 3 nodes. In my cluster one node got crashed due to low disk space ,we have cleared some log files and provided sufficient disk space.Now we are trying to start ...
0
votes
0answers
26 views

Mysql table with only two columns, nothing unique: Will an index increase search performance?

If my table only has two columns (username, groupname) and a million or so rows, will an index improve performance at all? Wouldn't it take just as long to search the index file as it would to search ...
0
votes
0answers
13 views

set error message on out variable in mysql stored procedure

I have a stored procedure: fill_DATA (serial varchar(20), OUT oError) I am doing an Insert operation here. so what I want is to SET value of oError for any error message in case the query fails to ...
0
votes
1answer
123 views

MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

Can someone tell me how to prevent the following commands DROP TABLE <tbl-name>; TRUNCATE TABLE <tbl-name>; from being replicated to slaves?
0
votes
1answer
86 views

AES 256 data encryption on Mysql

Is there a way to encrypt the whole data of a mysql DB using the AES 256 symmetric encryption method without encrypting the data manually using AES_ENCRYPT ? This article explains about the same ...
1
vote
2answers
37 views

Large MySQL table that need to index some fields?

I would like to know some hints about how to indexing some fields of a MySQL Table that has about 28 millions of rows. Maybe i could recreate the table indexing it properly then import these rows? ...
0
votes
1answer
30 views

Varchar indexing

I am very rusty with MySQL and I'd like some advice. I have several software tools that are deployed throughout the company and I am writing a DLL to keep track of the usage, errors and stuff of some ...
0
votes
1answer
22 views

Can I generate binary logs if they weren't turned on?

Ok, so here's the scenario: Our master went down, boom! gone (Call this one DB1) We switched our backup replica server. Ok, life is good, sort of (Call this DB2) Binary logs were not turned on for ...
0
votes
1answer
43 views

A single, simple update takes 10+ seconds

We are running MySQL 5.1.71-log and have a table with 2.7 million rows. Simple updates to it are regularly taking more than 10 seconds to execute. The table structure is: CREATE TABLE `person` ( ...
1
vote
1answer
282 views

creating a report on the mysql jasperReport server

I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf ...
0
votes
1answer
35 views

How performance schema in mysql 5.6 improve performance?

Since MySQL 5.5 performance_schema database has been added likewise information_schema. As we know that tables in information_schema contains statistical information like tables, plugins, partitions, ...
0
votes
1answer
44 views

Restore InnoDB from .frm files

My database crashed and my PC and I now only have the .frm and .ibd files left. Is there any way to restore my database?
0
votes
0answers
22 views

Select groups mysql

How do I select a separate category by category groups, for example: column1, column 2, column 3 category 1, category 2, category 3 pineapple, strawberry, watermelon lemon, grape, ...
0
votes
1answer
169 views

Change MySQL database name in SQL file

I have a 2GB mysql backup file that has several databases in it and would like to change the name for one of the databases. What approaches can I use to tackle this issue ? I have used sed but it ...
0
votes
0answers
8 views

How could I prevent the mysql cli-/terminal client from converting/altering multiline-statements (in the process of logging)?

Is there a way to prevent the default mysql client from ?: 'removing newlines' a.k.a. 'converting newlines to spaces' a.k.a. 'mapping newlines to spaces' Being more specific, with 'default mysql ...
0
votes
3answers
73 views

Slow DELETE in MySQL with a subquery

I am running the following DELETE SQL on a MySQL database on a MyISAM/utf8 table. The SQL is called in a loop, once time per Users (in this case):- DELETE FROM regulatory WHERE documentid IN ( ...
0
votes
2answers
79 views

Large RAM use after import database - ubuntu server

My new vps has 4 GB of RAM. Before importing database system use about 1,2 GB of RAM. When I import database, about 250 MB sql file, 25000 tables use of RAM goes to 97%. Htop showing 3302 mb in cache. ...
0
votes
2answers
2k views

How to select only first column dynamically on mysql

I have to select only first column. Because of i dont want to select all columns like SELECT * FROM table. But my first columns may vary like following: id article_id user_id product_id page_id etc.. ...
0
votes
2answers
3k views

Insert data in current table and update another table MySQL

I'm having problem with inserting in purchase table and updating facility table. Let's say, user made a purchase with product_id and product_quantity. When user hit submit, I want to insert ...
1
vote
2answers
3k views

Multiple parents and multiple children in product categories

I am making a ecommerce site. In this site I want to categorise the items into three different layers primary category sub category sub category >>electronic ...
0
votes
1answer
35 views

What is the meaning of different roles available(DB Admin etc.)

I have recently installed MySQL and also created the basic user as scott but my doubt is regarding the roles. I don't understand different roles available for the user which I have created. I want ...
2
votes
2answers
161 views

Repeated values in group_concat

I have two tables, first the table food and Second is Activity: INSERT INTO food (`id`, `foodName`) VALUES (1, 'food1'), (2, 'food2'), (3, 'food3'), (4, 'food4'), (5, ...
0
votes
0answers
28 views

MySQL Table with 568 columns: 1118, 'Row size too large…'

I'm currently migrating a database from Oracle to MySQL5.6. One table consists of 568 columns. When atempting to create the table I received this Error 1118, 'Row size too large..' I already ...
2
votes
1answer
183 views

innobackupex is failing while exporting backup

I am using perl script which is working fine in one of my other box which used to take individual schema backups & finally take all schema together using percona innobackupex. I have following ...
0
votes
0answers
35 views

MySQL SSL replication not using SSL connection

I'm running into an issue while setting up MySQL SSL replication between two Windows servers in a Master-Slave configuration. I followed the instructions [here][1] to set up MySQL replication. The ...
2
votes
1answer
316 views

MySQL table relations, inheritance or not?

Im building a micro CMS. Using Mysql as RDMS, and Doctrine ORM for mapping. I would like to have two types of pages. Static Page, and Blog Page. Static page would have page_url, and page_content ...
0
votes
0answers
15 views

How do I build a mysql LAN out of scratch? What do I need? [closed]

I have mysql workbench, is that all the software I need? I am trying to connect 6-7 different computers together using a LAN onto a mysql server and I dont know where to begin. Any helpful links ...
1
vote
2answers
52 views

Are there benefits to using more than 4 CPUs for MySQL?

We are running MySQL 5.5 on linux under VMWare, running on 2 CPUs. We are planning on increasing this to 4, but we have un-used CPUs available, and I'm wondering if there is any benefit to increasing ...
1
vote
1answer
35 views

How to do the complex nested select SQL?

I have two tables, table1 is as following, id is primary key, table2_id is foreign key id date aid table2_id message 1 2014-03-19 16:21:02 121 1 login from xxxx 2 ...
1
vote
1answer
107 views

Archive partition before delete it?

I have manage to create an event and with the following scripts could both create and delete the partition. The issue now I need help on how to save the partition before its deleted? Should I use ...
0
votes
1answer
19 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
2
votes
1answer
49 views

Optimize Percona MySQL InnoDB 3.2TB database

(Before you will call me an idiot or start laughing, please remember it's a real deal on my side and I cannot cancel this assignment as it is a part of a bigger project) I have a database server that ...
0
votes
2answers
51 views

What is the best choice for MySQL high availability (zero downtime)? [closed]

As I know there are many solutions for MySQL HA, but most of them are congenitally not zero downtime solution, like MHA, AWS RDS... That said, if zero downtime is "must have", the only choice is ...
0
votes
1answer
102 views

MongoDB Storage Architecture (MySQL innodb_file_per_table equivalent to MongoDB)

I am new to MongoDB.I looked inside dbpath I found users.0 - 16.8MB users.1 - 33.6MB users.ns - 16.8MB Further when I dig deeper I found, The .0, .1 files are datafiles. Each datafile is ...
0
votes
1answer
2k views

Too Many database connections on Amazon RDS

We are having problems with users running queries/views in Drupal that occasionally cause our site to freeze. The freeze occurs because the query causes the number of database connections to go up to ...
0
votes
2answers
24 views

How do I extract row/column elements from multiple tables with same schema in single query

My question is as follows: Given that I have three tables with exactly the same column names and exactly the same keys for the "name" column e.g. Table r name | A | B | C ----------------- elem1| ...
2
votes
2answers
77 views

How to monitor InnoDB Disk I/O Required for MySQL?

I am doing an estimate on how much i/o[disk read write] on Mysql 5.6 [innodb storage engine], for getting idea how to calculate total number of disk read write i have searched a lot on SO on DA and ...
1
vote
2answers
38 views

SQL - query multiplying records returned - Cartesian Product

I am using the following query which is returning a huge number of multiplied records - i.e. a cartesian product: SELECT * FROM specimen, topography_index, morphology, functions WHERE ...
2
votes
1answer
41 views

Need scalable database solution for very big automated data entry database

I've been working on MySQL database in simple web applications for a long time and i now have a project which should maintain about 1,000 up to 10,000 query per minute (it is some logging database ...
2
votes
1answer
130 views

Queryplan changes depending on filter values

I created a clustered index on a table expecting it to make the queries with ranges perform better, but, different values in the where clause can produce differente query plans, one uses the clustered ...
0
votes
1answer
31 views

Access Sharing database over Network vs Mysql multiple users [closed]

Something no one mentions and I find important to stress is that Access has a file locking mechanism for sharing the database on a network share which is orders of magnitude more efficient than any ...
0
votes
1answer
52 views

MySQL: How to clean MySQL from RHEL5?

I have installed MySQL 5.1.73 community edition through RPM. It was working fine. I uninstalled this version by using yum remove mysql-server command. I wanted to upgrade MySQL from 5.1.73 to 5.6.19 ...
0
votes
1answer
18 views

Omitting [INTO] in the INSERT … SELECT statement

Does the INTO keyword only makes the query more readable, or does it bring another benefits as well? The queries beneath both seem to run equivalently . insert ignore tbl (type, details) values ('1', ...
0
votes
1answer
75 views

Report Builder cannot publish to report server

This is currently in a cloud lab environment. We're not allowed to join any AD domains. We have a linux server (RHEL 6) running MySQL 5.3 and a Windows SQL Server 2012 on Win 2k8 R2. Our laptop is ...
0
votes
1answer
26 views

Cannot start MySQL Cluster Management Service

Could not determine which nodeid to use for this node. Specify it with --ndb-nodeid= on command line. This is what EventViewer is saying
0
votes
1answer
95 views

InnoDB Failure of some kind

I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked. InnoDB is listed under SHOW ENGINES; Any idea ...