Re: Looking for a Mysql Guru/DBA

Kevin Hunter wrote: At 1:43p -0400 on Wed, 21 May 2008, bruce wrote: The basic goal of the project is to be able to track the sites that I'm visiting via a Firefox extension. I want to be able to implement something like the breadcrumbs extension, but I want to be able to go a lot further. If

Re: very simple but slow query

We use a sub select on a 8M+ row table because it takes better advantage of indexes. SELECT startip,endip FROM geodb a WHERE a.startip = (SELECT max(startip) FROM geodb WHERE b.startip = 3250648033) AND a.endip = 3250648033; startip and endip are INT(10) unsigned and unique keys. This

Re: Query execution time - MySQL

Neil Tompkins wrote: Thanks for your help. In the end I've decided to use GetTickCount() Neil Date: Wed, 14 May 2008 13:44:22 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query execution time - MySQL Hi Neil, If your using Linux then you

Re: History of changed rows

C K wrote: Hi all. How can we manage the history of changed rows in the database. I have some idea but not yet implemented. By using triggers to make a copy of the row being changed to the another table in the same db. Is there any way to only save the changed fields data and field name? Any

RE: Performance problem - MySQL at 99.9% CPU

-Original Message- From: Per Jessen [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 02, 2008 7:51 AM To: mysql@lists.mysql.com Subject: Re: Performance problem - MySQL at 99.9% CPU Gunnar R. wrote: I am thinking about buying a new dual core box (with IDE disks?), but I have to

Cross database joins

Hi, I found one thread on this that included some people's opinions, but I haven't been able to find anyone who has actually done some performance testing to see if there is a cost and what that cost is to doing cross database joins. I do tend to want to keep everything in one DB, but it

RE: Unusual sort

Hi, One thought, it might a good idea to make a trigger/procedure that inserts the seprate index field, so you can forget about it from here on. -Original Message- From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Saturday, November 24, 2007 11:18 AM To: Jim; mysql@lists.mysql.com

Re: Giant database vs unlimited databases

From: Eric Frazier [EMAIL PROTECTED] To: Mohammad wrk [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, November 19, 2007 7:42:13 AM Subject: Re: Giant database vs unlimited databases Mohammad wrk wrote: Hi, I'm working on a web 2.0 project that targeting small to medium size

Re: Giant database vs unlimited databases

Russell E Glaue wrote: No one probably wants to go through the trouble to code this solution but it is possible to use MySQL Proxy to filter the SQL statements and results. MySQL Proxy sits in between MySQL Server and the MySQL Client. It can read queries, modify them, send queries to the

Re: Giant database vs unlimited databases

Mohammad wrk wrote: Hi, I'm working on a web 2.0 project that targeting small to medium size companies for providing business services. Companies simply register to the site and then start their business by loading their data, sharing and discussing them with others. The

Re: Replication and AUTO_INCREMENT; is it safe?

On 10/24/07, Eric Frazier [EMAIL PROTECTED] wrote: js wrote: Hi list, Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB

Re: Saving space disk (innodb)

Dan Rogart wrote: OPTIMIZE TABLE should reclaim that space, but be aware that it could take a while to run (locking your table all the while) since it just maps to an ALTER TABLE statement which creates a new copy of the table. Depends on how big your tables are. Doc:

Re: Saving space disk (innodb)

Andrew Carlson wrote: If you do what Baron suggests, you may want to set Innodb to create a file-per-table - that way, in the future, you could save space when tables are dropped, or you could recreate innodb tables individually to save space, not have to dump all your innodb tables at one time.

Re: DB Schema Comparison Utility ?

Daevid Vincent wrote: This has been asked for many many times on this list, not sure why mySQL AB doesn't just release a command line tool like a 'mysql diff' and also a 'mysql lint'. The lint one should be totally trivial for them to do, as they already have a SQL parser! I can't tell you how

Re: Slave Bin Log Question

Boyd Hemphill wrote: I have executed a strategy for backup where I stop a slave and do a mysqldump with --master-data. Both master and slave are 4.1.20 My assumption was that the log coordinates in the dump file would provide me with the place to replay the log for a point in time recovery.

Re: Where to find advice on database structure/design?

It might also help to at least try to understand why you normalize. Don't just try to follow the rules, there is some art to it as well. My favorite book on this is Database Design for Mere Mortals Thanks, Eric At 09:11 PM 10/22/03 -0700, olinux wrote: Read up on database normalization. (do

Re: Replication from 2 Master

/03 -0700, Jeremy Zawodny wrote: On Sun, Aug 24, 2003 at 02:02:06PM -0400, Eric Frazier wrote: Sounds very biblical. :) Yeah, I have it etched on a pair of stone tablets around here somewhere... :-) Wouldn't there be a way to do this with two copies of mysql that share a common data dir

Re: Replication from 2 Master

Sounds very biblical. :) Wouldn't there be a way to do this with two copies of mysql that share a common data dir? I don't know if you could do that with InnoDB, but I wonder if you could with myisam? At least if you had a system where the two sets of tables came from a different master, and

Re: Mast-Master Replication

Hi, I wish I could use that more, but load data from master locks everything all at once, then you have to wait for the transfer, making it not a great idea to use on a busy live/big database. I can do a back up localy and then transfer the data with a lot less locked time. Still, the times I

Re: PHP or Perl?

The only and best careful way to use a global is not to use one at all!! At 11:38 AM 8/18/03 +0900, Joel Rees wrote: The only advantage of PHP is that it runs faster than Perl which may be important if a lot of people are accessing your web page. Using mod_perl vs. mod_php? or perl with

Re: Configure prob with FreeBSD/Linuxthreads

Hi, I don't know if this is all old news now, but I got the exact same error that Jesse did when I ran a copy and paste from your example. When I both switched to sh from csh, and made that big paragraph into a single line, then it worked. So I got though the configure. But on the make I got

Re: Configure prob with FreeBSD/Linuxthreads -- path to linuxthreads includes?

Hi, One other problem came up, the configure script looks for LinuxThreads in /usr/include where of course FreeBSD stores them in /usr/local/include/linuxthreads so the flag that gets set for linuxthreads doesn't, at least not in my version of mysql 4.09 Thanks, Eric (250) 655 - 9513 (PST

slave pukes with hostname change

Hi, Ok, I am begging now. Has no one ran into this problem? I can't believe it would not come up, it is the only thing that mysql does wrong with a hostname change. All of the log files for example just are rewritten with the new hostname. I know one way I could fix it, RESET MASTER on the

Re: slave pukes with hostname change

Hi, sorry, I didn't realize that. The slave's hostname. Thanks, Eric At 08:01 PM 12/15/02 -0800, Jeremy Zawodny wrote: On Sun, Dec 15, 2002 at 07:34:49PM -0500, Eric Frazier wrote: Hi, Ok, I am begging now. Has no one ran into this problem? I can't believe it would not come up

Re: Removal of Primary Key in Mysql

Hi, This is a why questionk, which may be somewhat pointless, but. Why is this under alter table instead of drop index? Thanks, Eric At 01:10 AM 11/5/02 -0600, Paul DuBois wrote: At 12:08 +0530 11/5/02, Uma Shankari T. wrote: Hello, I have set one of my field in the mysql table as

About the Changelog for 4.0.3

Big cleanup in replication code (less logging, better error messages, etc..) I have been having periodic problems with lost connections with my 4.0.2 slave and master. No errors, other than lost connection, retrying etc. But sometimes the connection doesn't come back even though show slave

Re: Are there ANY terminal-based frontends for Linux?

Hi, Lots of people will end up wanting to forget about the terminal part of your question and just say MS Access. I would not love to have to deal with a large number of people using Access, even with mySQL behind it. Maybe you can find something that uses the ncurser lib? That would be my

Re: A FAQ type question, but not in the FAQ - How to change my hostname, but not make replication puke

Hi, The mysql master wasn't restricting the slave by ip. But thanks, Eric At 10:28 AM 9/30/02 -0500, gerald_clark wrote: You need to grant privileges on the master to the new slave machine. http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. --

Re: A FAQ type question, but not in the FAQ - How to change my hostname, but not make replication puke

Hi, I didn't have a new mysql master, just the slave name was changed. Yes on the second question too. Thanks, Eric At 09:09 AM 9/30/02 -0500, gerald_clark wrote: Did you make the changes to master.onfo to point to the new master? Did mysql own master.info when you were through? Eric

Rephrasing a question - RESET SLAVE

Hi, I was having problems because I had to rename my slave sever. Now I think I understand the problem better. I looked in slave.cc and found my error message and can see that it only applies to the slave. So I guess that brings up two issues. 1. Why can't the relay-log deal with the name

A FAQ type question, but not in the FAQ - How to change my hostname, but not make replication puke

Hi, I changed my hostname(The DNS change was taken care of elseware) in FreeBSD 4.6 in the rc.conf file, rebooted. Mysql had no problems starting, but it failed to start the slave. In the new error log I saw: 020927 10:15:58 mysqld started 020927 10:15:59 InnoDB: Started 020927 10:15:59

Re: mysqlbinlog - doesn't work on some log files

Hi, I tried using the mysqlbinlog from 3.23.52 MAX with no difference in the output at all from 4.0.2 It seems that whatever happened truely caused the log files to become corrupted So my next question would be, is there a way to repair the log files? What are the formating rules for the log

mysqlbinlog - doesn't work on some log files

Hi, I deleted a db I didn't mean to. I am using mysql 4.0.1 Alpha and some innodb and some myisam tables. I have the log files I need to restore my data since I have been running since the last backup with binlog enabled in my.cnf. The problem is that mysqlbinlog shows some log files, but

Re: Replication with INNODB

Hi, I am confused. I just tried replication with 4.0.2 on master and slave, and it appeared to work with the InnoDB tables on the Master.. What is the expected issue, or error that happens that causes the manual to say that Replication doesn't work yet with InnoDB at least not when you use LOAD

DBI weird and unheard of issues with mySQL?

Hi, This is a problem of the sort that is starting to make me a little supersituous. I am using mySQL various versions, one of which is 4.01 max on FreeBSD 4.5 stable, DBI 1.30 and I believe the latest DBD::mysql module. Well the weird thing that is happening is that on this particular machine

RE: help me out here guys... you gotta have a primary key

Hi, Is it such a big deal to use more than one field for a primary key? Two field keys are only a little slower than single field Primary keys for selects. Often in linking tables you want to have duplicates of the two foreign keys right? I just wish people would stop making 100 field tables,

JDBC Date Fields, can't seem to INSERT with setDate or setTimestamp

Hi, I looked at the Date example that comes with the mm.mysql driver, but I still can't get this to work. When I run the code below, I have no errors, but I only end up with nulls in my table. This is getting weird.. Thanks, Eric == import java.sql.*; import

Re: AUTO_INCREMENT with Replication

*not* implemented such a system, but this could work, depending on your app. sean - Original Message - From: Eric Frazier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 03, 2002 2:32 PM Subject: Re: AUTO_INCREMENT with Replication Well. Good to know. So I guess the only alternative

BigINT inserts

You can always store an exact integer value in a BIGINT column by storing it as a string. In this case, MySQL will perform a string-to-number conversion that involves no intermediate double representation. I don't understand this, does this mean that the fastest way to insert bigint values

AUTO_INCREMENT with Replication

From the manual 4.10.4 Replication will be done correctly with AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values. I am somewhat fearful and curious about how this works. Say we have a master web database that gets replicated back to the office slave over the Internet. A person on the

Re: AUTO_INCREMENT with Replication

Well. Good to know. So I guess the only alternative would be to generate keys by date/time? I was hoping to avoid that. I am still worried about the timestamp type not having good enough resolution. Seconds are pretty broad. Thanks, Eric You're asking for trouble. :-) AUTO_INCREMENTS are

Re: Undo query in mysql

Hi, Kind of a quick answer huh? If he had binary logging enabld and the last inserts or updates are still in the log, he can get his data back. mysqlbinlog and some greping and seding. I dropped a database and while it wasn't super easy to do, I did get it back. Eric At 09:17 AM 6/16/02

Slave logs, and defaults

Hi, I had an odd thing happen yesterday. I had replication running, the master being on a server farm, the slave being in our office. I tested it early in the morning by creating a table on the master, it showed up on the slave right away. Of course as soon(a few hours later) as I went to

Re: mysqldump -A dump.txt

Hi, I didn't get that deeply into why exactly, but it seems that when I removed the mysql database from the file, that the rest went fine with mysql -u -p dumpfile I got an error about the column_prv field already existing I believe. Sorry, I should have been much more specific. I appreciate

mysqldump -A dump.txt

Hi, How do I deal with the import of this file? Every example I see involves a dump of a single database, or else uses a command line like my subject, but with no corresponding mysqlimport. If I use mysql dump.txt then I end up with errors that stop the process. With mysqlimport I can use

RE: A COMPANY RELEASES A NON-FINAL VERSION OF SOME PRODUCT NON-PREMIUM EDITION

Hi, This is complete crap. One of the tools I use is because of an email I saw on this list that might have been called spam. I say that this type of email is on topic. Maybe they could have eased up on the BS tone of the email, but I would still want to know about a new product. The only other

Re: MySQL vs. AS/400

You forgot to metion the great new feature becoming available. Secure telnet, it never existed before very recently for AS/400. Secure huh? Yeah. There are companies making web apps for the AS/400 that are advertising that you can use the web and it is more secure than the traditional 5240

Multi primary keys

Hi, I just discovered with mysql 3.22.32 that it is possible to make a table with more than one primary key. Shouldn't that be impossible? Thanks, Eric Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513

Compatable SQL for indexes

the two DBs Does that make the most sense? Thanks, Eric Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http

RE: XML support under mySQL

One thing I want. A Java way to save a data structure and recover it later. Easy in perl, not so easy in Java. But XML would be a great way to do it in Java. Eric At 10:42 AM 2/22/01 +1000, Opec Kemp \( Ozemail \) wrote: *big snip* I agree with Cal, the XML module should really be sperated

varchar and sql92

should look into other issues like this with lengths of fields? Thanks, Eric Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513 - Before posting, please check: http://www.mysql.com/manual.php

Re: MySQL vs Access; you'd *think* the choice is obvious...

Hi, One thing I have always wondered, how do you deal with table locking if you have a number of people using mySQL with ODBC? Does ODBC handle it? Does Access do it? Can mySQL do it with Berkeley? I am esp talking if you are using mySQL from many different locations and working on the same

Version distribution

to upgrade? I know my own upgrades tend to come when a get a chance, after a new version is considered stable. I would like to use Transactions with the mm.mysql driver, but it only supports that for 3.23. Thanks, Eric Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513

Sub selects working around

because I do what those packagesParts.pri =0 where there is a packagesParts.pri = 1 in the result set. Is there a way to cram this into my query with mysql as it stands? I saw some people here do some pretty fancy stuff, so I thought it was worth asking. Thanks, Eric Frazier Consulting