MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

17
votes
3answers
7k 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 ...
2
votes
1answer
105 views

mysqladmin user account not secure?

I still playing with my own DB trying to learn and saw this: I could change the root password without any problem at all... If I'm in the server I can create an algorithm to start testing password ...
1
vote
1answer
211 views

MySql - How can I improve this query?

I have the following table: http://pastebin.com/XH6FvhZ8 and the following query: SELECT * FROM (SELECT * FROM `job_logs` ORDER BY id DESC) AS job_logs_tmp WHERE (job_logs_tmp.user_id = ...
1
vote
1answer
795 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 ...
0
votes
2answers
5k views

Diff in Transactional and Non-transactional tables

What is the difference between Transactional and Non-transactional tables? And How will the sql query go if I want to create them? Also when to use transactional or non-transactional tables?.
2
votes
1answer
2k views

Best way to compare strings using pre-built ngrams?

I'm struggling with an n-gram problem and have been hunting the Web for examples to help me out and have noticed n-grams being discussed on this forum. I'm working on a system where we've got scraped ...
2
votes
1answer
623 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
629 views

Is it possible to add index to mysql with multiple column to optimize order by performance?

# I have a simple table create table t1 (c1 int, c2 int); # I want to do the following sorting select * from t1 order by c2 desc, c1 desc; select * from t1 order by c2 asc, c1 desc; # add ...
6
votes
6answers
1k views

Alternative Database Tools for Web Admin [on hold]

I currently use phpmyadmin for most of my database administration. It works well enough, but I'm interested in other tools that might work more quickly/efficiently/etc... What are the best and/or ...
2
votes
1answer
289 views

MySQL performance on Mac vs Linux

I know that MySQL is tested more on Linux (Oracle has it's own linux distribution and you canot have LAMP wit MySQL (meaning MySQL has always focused more on Linux) My intuition says that MySQL has a ...
1
vote
1answer
524 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 ...
4
votes
1answer
247 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 ...
6
votes
2answers
2k views

Insert-heavy InnoDB table won't use all my CPU

I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
3
votes
5answers
3k views

Why is MySQL caching data on our dynamic PHP site?

We are having an annoyance with our PHP/MySQL site in that it seems to cache data from the database and our PHP pages are showing out of date records. In fact, there are pages that link to URLs that ...
2
votes
2answers
641 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 ...
2
votes
1answer
1k 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 ...
2
votes
1answer
3k 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 ...
0
votes
1answer
427 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 ...
4
votes
1answer
990 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 ...
0
votes
1answer
106 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
402 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; ...
0
votes
1answer
633 views

Look for software that can convert sql (mysql) to erd

I would like to have an ER Diagarm in phpmyadmin, however, its graph is not up to my expectation, the graph has no relationship indicator (1-1) (1-m) etc at all. So i am wondering whether there is a ...
1
vote
1answer
146 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
187 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, ...
1
vote
1answer
1k 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 ...
-1
votes
1answer
319 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. ...
4
votes
2answers
553 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
2answers
328 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 ...
0
votes
1answer
419 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
3k views

How to install MySQL 5.1 on Ubuntu 10.10?

I researched a lot but could not find any link which gives step by step procedure. Everywhere, there are vague answers. Can someone please give me steps to be followed to install Mysql 5.1 ? It gives ...
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 ...
4
votes
2answers
5k 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 ...
1
vote
1answer
2k views

mysql bin-log.index not found

I moved my bin-logs to a different folder: log-bin = /storage/mysql/bin-log log-bin-index = /storage/mysql/bin-log.index When I try to start mysqld I get this: /usr/libexec/mysqld: File ...
3
votes
1answer
1k views

Are many NULL columns harmful in mysql InnoDB?

As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance. Does the presence of excess ...
2
votes
4answers
211 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
2answers
3k 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
118 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 ...
2
votes
1answer
204 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 ...
2
votes
0answers
297 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 ...
4
votes
2answers
699 views

Difference between backup methods: Oracle and MySQL

I was reading that Oracle databases have cold, warm, full and redo backups. Questions How is it possible that MySQL doesn't have as many options? I have read that MySQL has cold backups (it puts ...
3
votes
2answers
299 views

Is index_type ignored on MySQL primary keys in create table?

I have seen a create table whose primary key had an identifier where it should have an index_type, something like that: create table a (foo INTEGER, PRIMARY KEY foo_id (foo)); The thing is that ...
2
votes
2answers
6k views

Is SELECT COUNT GROUP BY more efficient than counting a result set?

I have a table with a rank column which can be either 1, 2 or 3. I need to count the number of records for each rank, but I also need to display all the data ordered by rank. From my perspective, the ...
3
votes
1answer
134 views

Human relations between users

I'm involve in a development of a tiny Social Network where users must be able to establish relations between them and also have permissions over contents. For a example: I add one user as my friend ...
1
vote
1answer
561 views

Which Optimistic Concurrency Control method will work out best in the long run?

These are the options I've thought of: DotNet Dave: UPDATE mytable set f1=newval where id=666 and f1 := f1ValueFromSelect Charlie Checksum: UPDATE mytable set f1=newval where id=666 and ...
1
vote
1answer
272 views

GUID as reason for MySQL import slowness

We faced MySQL restores DB VERY slow: ~3 min for 15 MB dump. This is a new dedicated server with a lot of RAM. We were told the most possible reason is GUID is uded as primary key in many tables: ...
1
vote
2answers
1k views

Do I need SQL_NO_CACHE for write intensive query?

When I establish a connection to DB, I have to write about 30,000 records with an INSERT statement. I've read in a book that I should do it ! Questions Do I need to do SQL_NO_CACHE ? Is it gonna ...
1
vote
2answers
1k views

Client to use the slave when the master is down in MySQL replication

Is it possible for the client to use the slave when the master is down in mysql replication? I have already setup a simple mysql replication having a master and a slave. The client connects to the ...
2
votes
1answer
281 views

Replication and REPAIR TABLE

When running a repair table on a corrupted or damaged table, will this guarantee replication to break? Documentation says "in the event that a table on the master becomes damaged and you use REPAIR ...
3
votes
1answer
75 views

How to avoid listing FIELD/IN twice?

I have SELECT field(foo,'this','that','bar','baz') FROM t WHERE f1 IN ('this','that','bar','baz') How can I avoid listing ('this','that','bar','baz') twice?
0
votes
1answer
898 views

Problem with Error: InnoDB: page … log sequence … number is in the future!

i know this question was asked before, but i am at a point where i am really stuck and do not know how to continue with this problem and i would really like to know, what causes this error to come up. ...