Tagged Questions

MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
217 views

Master port is disabled from Public IP, MySQL log is not getting refreshed

My MySQL Replication setup is over internet. Master is on Public IP. If I stop mysql service in master, immediately an error message is thrown in slave. And when I start the service, slave is ...
5
votes
3answers
11k views

Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql

I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log). I have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log). The DB on S1 was using one data file ...
3
votes
1answer
622 views

Will {fn concat} work without issue in MySQL, SQL Server and Oracle?

According to this (quite old) article the principle behind ODBC escape sequences is that The ODBC driver reads the escape sequence and translates it into the DBMS-specific syntax before sending ...
4
votes
1answer
2k views

Suddenly have to rebuild indexes to prevent site from going down

I have been having this problem for a while: http://stackoverflow.com/questions/7770695/mysql-query-slow-until-reindex-column It seems all is good and when I suddenly get a lot of writes to the table ...
1
vote
2answers
827 views

MySQL Community Edition: Can I replicate my entire server with another server in one go?

I need to migrate all the databases from one server to another. There are about 100 databases, and all databases are used daily and updated/inserted too and read through their respective websites. ...
4
votes
1answer
1k views

MySQL dump restore - how to retain comments

So I have that complex MySQL dump as a backup. Unfortunately, that backup is complex and we use evil triggers. Some of our evil triggers had a comment lines starting with the # character. When I ...
2
votes
1answer
665 views

Will a row update check all child tables which have foreign key relationships in InnoDB?

I have a master table and around 60 to 70 child tables, all are InnoDB tables and have foreign key relationships with the master table. Foreign key relations are On-Delete Cascade and On-Update ...
2
votes
2answers
159 views

How to index this table (a_level, b_level, item_id)

I have a table of items, each of it has an a_level, b_level, and an item_id. Any b_level is dedicated to only one a_level (example: b_level 14 is "child" of a_level 2 only) Lets say we have million ...
4
votes
1answer
604 views

What are the concepts that need to be gone through for MySQL DBA?

I am a MySQL Developer, besides I am handling some DBA activities like backup, restore, mysql installation, etc at a top level. Now I wanted to shift to DBA, So I wanted to know in depth, like which ...
1
vote
1answer
683 views

How to Prevent an Ever-Growing index file (MyISAM engine) From Causing Disk Fragmentation in Windows (NTFS partition)?

We have a keep-growing table which using MYISAM storage engine. Almost every half year, this table will crash, and need to be repaired. I checked the defragment result reported by windows ...
3
votes
2answers
3k views

mysqldump vs LOAD DATA INFILE

Why would you ever use mysqldump to repopulate a table over load data infile? Here's the back story to my question. We had a table that was incorrectly partitioned. It was supposed to be ...
3
votes
3answers
255 views

Effect of Oracle on MySQL

While the future of MySQL is unknown, I haven't had time to keep up with the known effects that Oracle has had on MySQL since it was bought from SUN. Have there been changes of note, either by Oracle ...
2
votes
1answer
834 views

MySQL - How do you enter a record with only whitespace “ ”?

I want to enter the record with white-space as a record in MySQL. I had tried to enter it through shell and phpmyadmin, the record enters but when I try to retrieve the record it returns as null. I ...
4
votes
3answers
7k views

Need to Check if a Table Exists Before Deleting a Record

I have the following SQL query in my Ruby app: sql = "DELETE FROM #{database}.table1 WHERE same_id = #{some_id};" The problem is that in rare circumstances 'table1' might not exist. I need to ...
1
vote
1answer
365 views

SQL Schema for XBRL Document storage [closed]

Does anybody know of a relational database schema that can be used to store XBRL data? I am looking to store financial report data (balance sheet, income statement and profit/loss statement data) ...
5
votes
1answer
586 views

Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?

There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, ...
0
votes
4answers
143 views

Resetting MySQL Password [closed]

Please see the following link from Super User. I cannot get anything working: http://superuser.com/q/342609/93569 Update: This link has been dramatically changed! Please see the link.
15
votes
3answers
4k views

How Does Table Partitioning Help?

I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
4
votes
2answers
369 views

Binlog - 'Race-like' Condition on Replication

I have a typical master-slave MySQL setup. Slave is in test mode currently. Master is heavily used. I am noticing something that looks like a potential race-like condition on the slave. On occasion, ...
1
vote
1answer
135 views

String Pooling for MySQL

So I'm having this table that logs all errors happened in our production code. It looks like this: id | date | type | message | file | line ...
2
votes
2answers
536 views

What is an “empty query” in information_schema.CLIENT_STATISTICS?

The client stats table has an "empty queries" column. What does that mean? Simple googling just tells me "The number of times this client's connections sent empty queries to the server." /facepalm
0
votes
1answer
5k views

Setting default charset/collation for MySQL database

noob sysadmin here... I have a MySQL DB that used to be latin1, and I changed all the columns in all the tables to be utf8. However, I evidently didn't do all I had to do, because I just found that I ...
3
votes
1answer
3k views

Can I rename the values in a MySQL ENUM column in one query?

Suppose I have a Database table with an ENUM('value_one','value_two'). I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows: ALTER TABLE `table` MODIFY ...
4
votes
2answers
5k views

Query runs a long time in some newer MySQL versions

I have created a database on MySQL 5.0.15. I have a query and when I run this query on this MySQL version, I get 0.9 s run time. When I import this database to another MySQL server with same hardware ...
3
votes
1answer
210 views

Is SQL_CALC_FOUND_ROWS Safe and Accurate for a Single DB User Environment?

I have a large MySQL web application that uses a single user for all of its database queries. We have multiple databases running off of a master-slave replication setup. My question is this: We use ...
1
vote
3answers
239 views

How to convert MySQL Keyword to normal column

We thought of upgrading MySQL to new version 5.5.16. While testing the scripts with the new version, I had found one column Maxvalue is a Keyword in latest version. For this one, we need to change ...
2
votes
3answers
431 views

What's the easiest way to re-format CSV exports from MySQL into Excel/ flat-file fields?

I've just had some database backup problems on a website I run containing some important lead data. Unfortunately, I've now lost one of the key MySQL tables, which means my data has lost its ...
3
votes
1answer
6k views

Can mysqldump dump triggers and procedures?

Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db? Some time ago I read that mysqldump will also save my triggers, but it doesn't look like ...
3
votes
2answers
1k views

Quick MySQL Backup

I have a database which is grown to 3GB and it has 3 MyISAM tables. And I have 4GB RAM. What will be the best (quick) way to backup this database without any performance lack. Or mysqldump will work ...
4
votes
2answers
503 views

Multi-threaded replay of master activity for load/performance testing

Does anyone know of any existing tools/products that accomplish what I'm trying to do? After searching around for a bit the only thing I could find surrounding what I'm trying to accomplish is ...
4
votes
2answers
2k views

Running multiple instances on the same host

I need to set up multiple MySQL instances on the same host for one of our customers. As far as I see I could go with: mysqld_multi MySQL Sandbox This should be a long term solution for an ...
3
votes
1answer
2k views

Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL

I wanted to capture only status/result of Slave_IO_Running and Slave_SQL_Running column values, which I will be using to integrate into our application. Is there any way to get only the values of ...
4
votes
1answer
3k views

Consistent logical backup of databases that use MyISAM and InnoDB engines

I have a question regarding the logical backup of MySQL databases that use both MyISAM and InnoDB. The mysqldump utility supports these two options: --single-transaction - Creates a consistent ...
4
votes
1answer
76 views

Internals of Windows-to-Windows SQL Server data transfer

This is a sort of generic question and I don't require a very specific answer, but let me ask it by describing a scenario. You connect to a remote db in SQL Server Studio 08, and run a select * ...
3
votes
6answers
3k views

MySQL Performance Monitoring

I already have system availability monitoring for all my MySQL instances in Nagios. I'm looking to get some trending monitoring going for activity. It'd be nice to see how all the information_schema ...
1
vote
2answers
2k views

I cannot access local database on terminal (MAC)

I have access to my databases on phpmyadmin and mysql Workbench on my local computer but I have a problem when I try to access the server using Terminal. Originally, I had uploaded the databases on ...
5
votes
5answers
56k views

How to show the column names of a table with a simple sql query?

The case is simple: You have an mysql database where you have only sql query interface and you want to know the database structure with queries. You can list tables with show tables; command but how ...
3
votes
1answer
222 views

How do I configure a MySQL Cluster with a redundant mysqld host?

this is my first post here, so go easy on me. I'm trying to rebuild my data architecture (which is currently a set of one primary MySQL server and a redundant fallback; the tables are primarily ...
3
votes
1answer
2k views

Restore dropped mysql database

I create a database each year from the previous year, back up and then clean up the tables. So I have databases for the past 3 years. This year I did not do a backup and accidentally dropped the ...
2
votes
4answers
450 views

MySQL Database Poisoning: How to recover to a known state quickly

If you want to avoid database poisoning (ie want to recover back to a point-in-time quickly) which methodology do you prefer? Let me define data poisoning. You insert some things in your database ...
4
votes
1answer
18k views

How to Recover an InnoDB table whose files were moved around

So I have a test db server that was setup on a replication stream. Over the name an optimize came through that quickly filled up the space on the slaves datadir. Mysql dutifully was just waiting for ...
2
votes
1answer
509 views

MySQL proxy and SHOW PROCESSLIST query time

I have MySQL replication currently occurring between two masters bidirectionally, one in a data center locally, another over at Amazon EC2. Everything seems to function normally replication wise, no ...
0
votes
1answer
38 views

Should I record ID numbers in a table where I record who look whom profile page

This is how I have designed: visitorID visitedprofileID datetime Is it a good idea to add an ID (primary key) to the table, I mean recordID (primary key). (MySQL with PHP)
6
votes
4answers
3k views

What should I ask at a MySQL DBA interview?

I am a software developer and am helping my team hire a MySQL DBA. The core challenges that we are facing are: Slower queries and performance due to Hibernate. Database management (backups, tuning, ...
3
votes
3answers
854 views

Convert Oracle query to MySQL

I've just tried to convert this update command, which is Oracle sql, to MySQL with SQLWays and it failed with the following error: You can't specify target table 'animal_table' for update in FROM ...
1
vote
2answers
972 views

MySQL-NT is crashing frequently

MySQL is crashing frequently and there is no error in the err log file. The following error message was found in the event viewer: faulting application mysqld-nt.exe, version 0.0.0.0, faulting ...
1
vote
1answer
260 views

MySQL Performance Planning

I am considering moving my existing script which is downloaded to be a hosted script. One of the things it does is to keep track of some stats. My concern is with heavy hitters (100,000 ...
2
votes
2answers
133 views

How do I configure MySQL '5.1.49-1ubuntu8' to show multibyte characters?

I am using MySQL version 5.1.49 and I have now enabled UTF8 character encoding. The default character-set for MySQL is latin1. How can I change it show UTF8 characters? Even when I query a table ...
12
votes
4answers
17k views

How do you identify InnoDB table corruption?

I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
1
vote
1answer
3k views

Invalid default value for DateTime when changing to utf8_general_ci

I'm trying to run a script to modify a database, this query ALTER TABLE cmContentVersion CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; gives this error ERROR 1067 (42000) at line 68: ...