MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
3answers
5k views

How to convert PostgreSQL to MySQL

How can I convert database from PostgreSQL to MySQL? I have the postgresql dump file already and I try to use pg2mysql script, it converted but when uploading the file it produces this error: ERROR ...
1
vote
2answers
689 views

How is it possible for mysql storage engine to be NULL?

I'm just reading a book and in one of the examples I get notices: That the engine in the information_schema.table is null, how is that possible? Can I create tables without any engine??, is there ...
20
votes
3answers
8k views

Is the CPU performance relevant for a database server?

This is a purely theoretical question. Let's say I have an application deployed on multiple servers. A load balancer, Multiple/scalable applications servers A (single) database server (for the ...
4
votes
1answer
257 views

MySQL: A better way to write this query?

We are using the following query in our system: SELECT f.type AS type, COUNT(i.id) AS submissions, SUM((CASE (e.oiPost + e.oiPhone + e.oiEmail) WHEN 0 THEN 0 WHEN ...
1
vote
1answer
855 views

Secure method beside phpMyAdmin for remote mysql

Recently, one of my centos linux servers was hacked and the cause of due to phpMyAdmin. It was said due to this problem PhpMyAdmin vulnerability CVE-2011-2505 ...
3
votes
3answers
6k views

innodb_buffer_pool_size not changed

I use MySQL 5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf default-storage-engine = innodb innodb_buffer_pool_size = 3G innodb_log_file_size = 256M innodb_thread_concurrency = 8 ...
5
votes
2answers
4k views

Frequently slow queries while in the 'Update' state

I have two tables that I update very frequently, one with a direct UPDATE .. and another INSERT .. ON DUPLICATE KEY UPDATE ... Normally these queries are instant but at times will take 0.1s to 1+ ...
0
votes
1answer
460 views

mysql picasa like photo tags store and query [closed]

I want to make a photo website, allow users to upload photos and write 1-10 customs photo tags, like picasa. I use mysql. Tags could be words or short sentence, but total length can not over 80 ...
2
votes
1answer
4k views

Very Large Log Files

I'm running Windows Server 2008 R2 and executed a large query by accident. Next, the sever ran out of hard drive space. I now have a file in my administrtor documents (log.log) that is over 18GB. I ...
4
votes
1answer
4k views

MySQL Innodb corruption

I recently inherited a Drupal site that has an Innodb corruption. MySQL will run for sometime--usually about 24-hours but will eventually need to be manually restarted(see log output below). I have ...
2
votes
1answer
560 views

How can I restore a MySQL database after formatting my hard drive

I recently formatted my PC which had MySQL running on it. As a result, I lost the databases on my hard drive. I tried to restore the databases by re-installing the MySQL Server and putting the ...
0
votes
1answer
108 views

Mysql dump restore doing some funny stuff

I am using linux platform. I also dump the data into a file and I restore into a server and I ran this command [root@localhost /]# mysqldump -u root -p fms < /device.sql The funny part it keep ...
1
vote
1answer
842 views

Changing collation type database-wide in MySQL

How do I retroactively update the collation type database wide without dropping and recreating it? I accidentally created a database without specifying UTF8 as the default collation type. I then went ...
3
votes
1answer
2k views

How to speed up a loading data into MySQL from a zipped archive

I dumped a large database to an archive with: mysqldump --compress --max_allowed_packet=500M -h myhost mydb | gzip > /tmp/mydb.sql.gz This took about 10 minutes to run, and generated a file ...
4
votes
1answer
1k views

mysql change password in session

I changed my password from old hash to a new hash using the following query SET SESSION old_passwords=FALSE; SET PASSWORD = PASSWORD('[your password]'); I found this query on Mysql password hashing ...
2
votes
2answers
691 views

Out of memory mysql was aborted

I am using windows 2003 server, with 8G of RAM and MySQL was installed on It. I am getting error as follows out of memory.[ERROR] mysqld: Out of memory (Needed 523712 bytes). The configuration ...
1
vote
1answer
175 views

How to prepare a date and double for a mysql replace statement?

I have a MySql table with three columns: CU VARCHAR(3) DA DATETIME VA DOUBLE I need to execute several REPLACE into mytable (CU, DA, VA) values("ABC", mydate, myval) statements from Java. mydate is ...
1
vote
1answer
204 views

In a simple table with no complex indexes, does a large number of existing rows slow down an INSERT?

I have this small, simple table: CREATE TABLE `ads_tracker` ( `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, `type` smallint unsigned NOT NULL, `reference` smallint unsigned NOT NULL, ...
7
votes
3answers
4k views

How costly is opening and closing of a DB connection?

How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL ... when the DB software is on localhost? ... when the DB software is on another machine?
2
votes
1answer
864 views

innodb_flush settings on Amazon RDS

Setting innodb_flush_log_at_trx_commit = 2 improves my application performance considerably on my local development environment. The response time and number of requests per second on a 40 concurrent ...
7
votes
2answers
1k views

Investigate peak in MySQL throughput

Recently one of our servers has ran out of memory and crashed. After reviewing the munin graphs, it appears that the only metric (other than memory usage) that peaked just before the crash was the ...
2
votes
1answer
686 views

MySQL: Show full processlist has empty Host name. Why?

I am executing SHOW FULL PROCESSLIST and from the response I get the "Host" field is empty. Quoting from the docs here Host The host name of the client issuing the statement (except for ...
1
vote
1answer
431 views

MySQL Character set and Collation Issue.?

I Have a table when i select from the table i got the invalid data as mysql> select budgetID,StartDate,modifiedBy from Table_name order by budgetID desc limit 10; ...
-1
votes
1answer
322 views

Important distinctions between DBMSs [closed]

DBA is a career I'm strongly considering after school, but my limited experience mostly involves some PL/SQL Oracle web programming, some basic PHP/MySQL web pages, and some queries in SQL Server. ...
5
votes
1answer
13k views

Cannot output MySQL data to file

I am trying to output the data from an MySQL table to a file but getting permission errors: $ pwd /home/dotancohen $ mkdir in $ chmod 777 in/ $ mysql -ugs -p mysql> USE someDatabase; mysql> ...
1
vote
1answer
247 views

MySQL not returning successful results on statement - query looks like it's hanging but is successful

So we have a script(s) that runs in succession. Randomly this task would halt/freeze and error out simply because it did not see a returned response from the MySQL server saying the statement ran ...
4
votes
2answers
635 views

Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?

In this query: select count(*) from largetable; a secondary index is chosen: mysql> explain select count(*) from largetable; ...
1
vote
1answer
2k views

Why do my ON UPDATE/ON DELETE rules disappear?

I'm trying to connect a couple of Mysql tables with foreign keys and ON UPDATE/DELETE rules. My rules are working on all tables except the last one (OrderContains). Why aren't my rules working? I am ...
0
votes
1answer
472 views

Mysql:Need to upload data to database, get duplicate key error - how can dup keys be ignored

I'm uploading data to a db (mysql 5.1.42) from a dump file using mysql command line and get duplicate key error. This is the format I use: /mnt/temp# mysql -u username -p -h url_of_db_master -D ...
1
vote
2answers
359 views

MySQL Cluster Node Connections

I am a brand new user to the MySQL cluster world (literally hours). I was following a tutorial just to get a simple cluster set up (one management node on one machine, a second machine with a SQL node ...
1
vote
1answer
718 views

when will Innodb support activation of triggers on cascade foreign key? [closed]

The Manual says: http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html Currently, cascaded foreign key actions do not activate triggers. Any Idea when will this be implemented. Is ...
2
votes
2answers
3k views

mysql 5.5 corrupt - missing temporary table

One of our mysql instances crashed and failed to start: 120422 10:04:13 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './databasename/#sql-29f6_45.ibd'! ...
1
vote
1answer
267 views

Mysql 5.1.X How to upload data from dump file without the partitioning commands?

I need to upload data from a dump file (large one) that includes partitioning info into a database that doesn't support partitioning. How can I tell the mysql command line to ignore the partitioning ...
3
votes
2answers
156 views

Selecting large data set on windows and linux

I need to parse a large data set, something like: mysql_query("SELECT * FROM largeTable") while($r = fetch()) { //> Do something } I just need 1 row to be in memory at time, because I need to ...
10
votes
3answers
56k 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 ...
6
votes
2answers
7k views

How can you have two auto-incremental columns in one table?

I have a MySQL table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to ...
4
votes
2answers
1k views

Data type for currency 'type' column, e.g. usd, cat, etc.

I am setting up a MySQL database for accounting software. One of the fields is to save the currency used for each transaction. What field type would you recommend? Details: No more than 10 different ...
11
votes
3answers
14k views

Why is 'LOAD DATA INFILE' faster than normal INSERT statements?

I've read an article that mentioned we can achieve 60,000 inserts per second by LOAD DATA IN FILE statement that read from csv files and insert it into database. Why should it differ from normal ...
2
votes
2answers
4k views

MySQL Table level Replication

I have 3 servers, say A,B &C. All have the equivalent data(same data). Now what I want is to make A as the master and B,C the slaves. But the scenario is that I only want to replicate some of the ...
4
votes
1answer
132 views

For MySQL, is there tool to dump a set of rows from one tables and all related (by foreign key) rows from all related tables?

I'm using MySQL db. I want to dump a set of rows from one table and if these rows have foriegn keys that point to rows on some other tables, I want to dump those too. Then I want to load these dumps ...
1
vote
1answer
119 views

create more logs in Mysql, pros and cons?

I just install XAMP in my mac and I'm trying to learn by my own, with books, tutorials and you guys. I'm working in the backup part and I saw I have only a LOG "mysql-bin.000001' : What happen when ...
2
votes
1answer
218 views

How to change the create and use statement in a large sql file without opening it

I need a way to change the schema name which appears in create statement and use statement of mysql dump file. The goal is having the database restored with a different database name on another ...
3
votes
1answer
255 views

What's the latest on MySQL Proxy?

Does somebody here know the latest state of the development of MySQL Proxy? Most materials I found on web are outdated.
2
votes
0answers
315 views

MYSQL & Access ODBC

I am trying to connect mysql with access, i am settings up the ODBC correctly from the data sources, however i get a driver error. I am using access 2010 and the driver version of the ODBC conector ...
1
vote
1answer
281 views

MySQL suddenly running very very slowly

I run a PHP/MySQL website on a fairly large server with 16GB RAM and 3 vCPUs. Everything was flying along until this morning when I noticed MySQL down. I tried to restart MySQL and first got the ...
1
vote
2answers
16k views

MySql - Clean ibdata1

I have a database that its entire size is ~44GB, out of it ibdata1 is ~35GB that doesnt make sense, the size of the data shouldnt be more then 10GB. i use the following query to get an esstimation of ...
2
votes
2answers
1k views

MySQL Server is Restarting automatically after some minutes

I am getting an Issue after everey 2-3 minutes mysql> show tables; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 57 I am really not able ...
2
votes
4answers
219 views

MySql Database solution for specific web app problem

Well, I need to create a comment system for posts that people add to my web app. This web app is just like facebook or myspace posts. Users add posts and people can comment on them. But the problem is ...
2
votes
1answer
250 views

MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?

I'm trying to estimate my db traffic not including backups (mysql 5.1.41). I use show global status; to get the parameters bytes_sent and bytes_received. Questions Do these numbers include ...
-3
votes
2answers
482 views

How can I generate Cassandra data model from a MySQL schema?

Is there a tool for generating a Cassandra model from a MySQL database?