I'd say MySQL 2nd edition by Paul Dubois. It has the first 200 pages
with stuff you already know, but the next 800 pages are mysql specific.
Very good reference book and best practices guide.
The only thing it's missing is what I think should be in every book (and
is in virtually none). 5 pages
We were affected by the power outage last week and I think people should
be aware - I'm not sure if it's fixed (I'm virtually certain it is not),
but the default rpm install of 4.0.12 has mysql starting up in runlevel
4 only. When our power was restored, RedHat went to runlevel 3 and
mysql never
I think I see the problem. Mysql really needs to be on it's own box.
It's designed to just use as much power as it can find. This is a good
thing for those with dedicated machines. I don't know if there's a
configuration setup that tell mysql that it's not the head honcho. Does
Windows have a
The point of the hash is that it doesn't matter who sees the data,
right?
username | passwordhash | ipaddress | permission
adam | AALKJA2344AFDS | 10.1.1.1 | rwxrwxr--
jake | 45324AFSDAF3423 | 10.1.1.1 | rwxrwxr--
tyrone | AALKJFF323FSDAF | 10.1.1.1 | rwxrwxr--
All you can ever do is compare
Also, one has to work out the cost of high availability. If you're
talking about a situation where you reduce downtime from 4 hours/yr to
.5 hours/yr and it costs you x dollars, you have to make sure that the
extra 3.5 hours of downtime would cost more than that much money.
The system you have
exactly, NULL is a good thing. It means that php isn't constantly
tearing down sessions and starting new ones. The connect process is
virtually always the longest step of the query (except the big queries,
but the goal is to keep them minimized in an application and use good
design, indexes,
I just did a major insert of new data and now all my selects have slowed
down. The table is innodb. Is there a way to reindex everything
without having to drop anything. Otherwise, I suppose I will have to
drop the indexes and remake them.
--
MySQL General Mailing List
For list archives:
I believe views in Oracle (SQL Server? Sybase?) can do this if you need
it.
-Original Message-
From: Andy Jackman [mailto:[EMAIL PROTECTED]
Sent: Monday, July 28, 2003 12:36 PM
To: MySQL
Subject: Re: dbase calculations
Kalle,
The usual way to do this is to create the table
There's also netapp (www.netapp.com). I don't have much experience in
some of these things, but I would stick with the same vendor that you
use now or whomever they have a partnership with. I can't imagine that
Sun,HP,EMC,Netapp,IBM have major differences in the quality of their
solutions or
With new HP ultrium tape drives, you can get 200GB/hr transfer rate. I
kind of hate tapes (just like everybody else), but tapes have really
improved in the past few years. These things are under $6k and could
back up 1-2 TB overnight without much problem. With a library
(MSL6060), you can have
I think the reference manual is basically what's on the web. The book
you ordered, MySQL, Second Edition is indispensible. Since Paul is on
the line, if there's ever a third edition, it would be nice to have the
book and the appendices broken into separate books (they'd be bundled
together at
Certainly datetime is the way to go. It takes up 8 bytes per row, as
opposed to 4 bytes for int. But, even if there are 10 million rows
(over 27 years of data), thatÂ’s only a 4 MB difference.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, July
The whole point of gator is that the web site owner has no control of
this. There is nothing mysql.com webmaster can do about this. I think
they could sue GatorWare, but to what end. In the future, it's
important to take a innocent until proven guilty approach on this list.
-Original
weird kernel incompatibility with a raid card. With 30
machines though, you can afford to lose one. For me, with 1 or 2, I
cannot and must get the best.
-Original Message-
From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 18, 2003 2:39 PM
To: 'Adam Nelson
We recently bought a kick $%#%% machine for ~10k
HP DL380
2x2.8GHz Xeon
1GB RAM
5 15k scsi drives (2 RAID 1 for OS and logs/3 RAID 5 for data)
RedHat Linux Enterprise Edition 2.1
This machine easily handles 200 queries/sec and never gets a load
average above 1.5. For your space requirements,
I suppose you're doing this on your workstation (Windows?)? Ideally,
Mysql should be on it's own machine - it (or any database) is designed
to suck up resources. Also, you definitely need to tweak the
configuration file. MySQL's default config is very anemic (annoying I
know) as opposed to
InnoDB is very fast. In theory, I guess MyISAM is considered faster
(that may not even be true these days). We're talking about fine hairs
here and I haven't heard anybody complain about InnoDB-specific speed
problems. Anyway, if you're using Windows, you're must not be too
concerned about
So I'm setting up a fancy new machine with Xeons doing hyperthreading.
What this means is that there are 2 physical processors, but as far as
linux is concerned, there are 4. Does anybody know whether
thread_concurrency should be 2*(Number of Physical Processors) or
2*(Number of Virtual
I would be wary of disabling version(). That's the kind of annoying
thing that sys admins do when they don't understand the life of a
developer. Some programs and modules require the version() function to
work. Security to that extreme is only useful if you understand that it
may cause more
This should work, but I would consider using the 4.0.12 instead of the
alpha release.
-Original Message-
From: dreq jkj [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 20, 2003 11:02 AM
To: [EMAIL PROTECTED]
Subject: Using two databases in a query?
Is it possible to use
Does anyone know how to view uncommited transactions for a session?
This is more of a problem when using MySQL Manager when it asks whether
or not to commit the transaction list. Is there a definitive way to
know what queries have yet to be committed/rolled back?
Are you using Windows? If so, this root/mysql user talk will be
meaningless. You can still make the directory secure and only touchable
by the user that mysql is running as. Is this what you need?
-Original Message-
From: Dyego Souza do Carmo [mailto:[EMAIL PROTECTED]
Sent:
I've gotten some weird responses to this type of question before, but
I'd like some input anyway. We are getting a new dedicated database
server. Currently our load average is hovering above 2 and although
things work fine, that snappiness is fading as the load becomes higher.
We may be able to
I believe you can do:
select avg(scantime) from percentile;
select std(scantime) from percentile;
std() is the same as stddev() which finds the standard deviation. If
the scantimes are gaussian (pretty good assumption if it's a large
dataset), then the 95th percentile will be avg() +
I would be scared out of my gourd to do the dual mysqld processes.
Just backup the machine and put linux on there. That was my solution to
the FreeBSD problem.
-Original Message-
From: Tommy F. Eriksen [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 10, 2003 3:14 AM
To: [EMAIL
If I'm getting copying to temp table often on some big queries, I
usually increase tmp_table_size, but for innodb, is that variable used
or is it innodb_buffer_pool_size?
-
Before posting, please check:
The real problem is the lack of a central knowledgebase. Is there one
that I'm not aware of? Even if there is, it should be very obvious off
the front page of the website.
-Original Message-
From: Csongor Fagyal [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 18, 2002 5:34 AM
That's the only thing wrong with Mysql is what it doesn't do.
Everything it does do it does fantastically.
-Original Message-
From: Michael T. Babcock [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 18, 2002 10:12 AM
To: Michael She
Cc: Qunfeng Dong; [EMAIL PROTECTED]; [EMAIL
I agree entirely. SETS and ENUMS should be avoided by any normal user
(frankly, I think they should be deprecated). They are not portable and
it's just horrific to be changing data with an ALTER statement.
Foreign Key relationships (even if they aren't real as in standard
MySQL) are the way to
I've posted my comments before but the important thing is that P4 is
largely unnecessary as it doesn't have instructions that apply to server
applications (mostly).
So, PIII (dual is quite helpful) the fastest you can get without paying
a premium
1 GB ram
dual scsi drives (raid 1)
This is the
The first thing I would do is toss the ultra ata drive and just use the
scsi drives running raid1, raid0 just isn't safe and hardware raid1 is
much faster than you would think. This may seem counter-intuitive, but
there are all sorts of bus issues that could be interfering. You may
very well
Oh, and what's up with the thread_concurrency being 6? That doesn't
make any sense unless you have a tri-processor setup.
-Original Message-
From: Ledet, Mike [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 04, 2002 11:01 AM
To: '[EMAIL PROTECTED]'
Subject: Slow performance
as many columns in less than 2 minutes.
-Original Message-
From: Adam Nelson [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 04, 2002 4:35 PM
To: 'Ledet, Mike'; [EMAIL PROTECTED]
Subject: RE: Slow performance using 3.23 on RH 8.0
The first thing I would do is toss the ultra
: Wednesday, December 04, 2002 5:23 PM
To: 'Adam Nelson'; [EMAIL PROTECTED]
Subject: RE: Slow performance using 3.23 on RH 8.0
Well, I've got an MSDN subscription so I have access to a
legal copy.. the
non-unix thing is a downside but there are number of scp command line
utilities for Windoze
You can use
insert group_map (user_id,group_id) select user_id,6 as any_label from
user
-Original Message-
From: Zabel, Ian [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 18, 2002 11:35 AM
To: [EMAIL PROTECTED]
Subject: Insert row in x for every row in y
I've having
This style of insert does not work in MySQL. The two table names must
be different.
-Original Message-
From: Alan McDonald [mailto:alan;meta.com.au]
Sent: Thursday, November 07, 2002 9:56 PM
To: Doug Coning; [EMAIL PROTECTED]
Subject: RE: Copy Records in a table...
insert into
I agree that whatever the app is, having the ability to unplug the
database (or for it to go down) and have there be a queue on the other
machine is ideal. This will mean that even if the db machine reboots
for whatever reason in the middle of the night, nobody will ever know
the difference.
These are good ideas. Just remember, if it ain't broke, don't fix it.
Unless I see performance degradation, I don't see the need to do
maintenance that could potentially create huge downtime (defrag doesn't
work, table files are permanently destroyed). The risks must be
weighed.
I used ext3
I think that's confusing, but right.
every null value is distinct, thus null != null.
weird, but null is not a value, it's the lack of a value, so nothing can
be shown about it.
so,
SELECT IF( NULL = NULL, 0 , 1 ) AS RESULT ;
is not the same as
SELECT IF( NULL is NULL, 0 , 1 ) AS RESULT ;
Something like this:
$site::dbName= 'DBI:mysql:test:10.10.2.20';
for a database called test on 10.10.2.20
Then add the user for the web machine (assuming it's 10.10.2.10) to the
authorized list:
Grant select on test.* to [EMAIL PROTECTED] identified by 'password';
Flush privileges;
The raid function is not useful (that's to get around the 2GB filesize
limit on certain OS). I think it's a misleading function (I think it
should be called RAIF - Redundant Array of Inexpensive Files).
I use RAID 1 (hardware) scsi and my load looks to be processor bound
(believe it or not)
I believe MySQL doesn't do a lot of the optimizations that MSSQL does.
However, you can do it manually (I think) by playing around with both
the order of the where clause and the order of the join clause
The where clauses go in order, so you want to use the first part of the
where clause to get
Seems to me like a better architecture might be:
N apache servers with mysql clients
1 Master Mysql Server
1 Slave Mysql Server/admin/backup server
If you have 5 slave servers (one on each apache server), that would
cause much more traffic on the internal network than each apache machine
just
).
Both ways will work fine since 100 queries/sec is not pushing any sort
of network envelopes or anything.
-Original Message-
From: Eric Anderson [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 30, 2002 11:22 AM
To: Adam Nelson
Cc: [EMAIL PROTECTED]
Subject: RE: MySQL hardware concerns
On Tue
we are considering moving to a 4-way system as the load average on our
2-way system is at 2.
Does anyone have recommendations? Is the larger 1MB cache on some xeon
chips worth the extra $$$?
I'm thinking of 2 raid1 arrays, one for the os and logs, the other for
the db. The data is relatively
I think any descent server would be fine:
Linux/FreeBSD
dual PIII
512MB ram (or better 1GB)
2 scsi drives with raid1
Frankly, you could even get cheaper than that with 1 processor.
-Original Message-
From: Javier Armendáriz [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 3:33
I believe slashdot uses one heavy duty database server (quad xeon) and a
redundant one. This isn't an actual cluster, but I would have to assume
that slashdot gets more that 30,000 clients at a time.
slashcode.org has information (somewhere deep in there, I couldn't find
it, but I remember
I don't know the answer to this in mysql, but in oracle, while you can
have big files, I've seen tables divided at, say, the 1 million record
mark. So there could be 5 files for a fast 5 million record table.
This is seamless to the user if there are views. Alas, mysql doesn't
have views :-(.
I'm finally getting around to working with InnoDB for real :-)
Anyway, is there any reason to still use MyISAM on any tables.
Concurrency is my biggest problem (Locked tables, etc.). My theory is
that the tables that wouldn't benefit from converting to InnoDB are so
small (5-50 rows?) that I
of course, the safe way is to always reset the password when such a
thing happens.
-Original Message-
From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 14, 2002 10:48 AM
To: [EMAIL PROTECTED]
Subject: Re: Encrypting with PASSWORD() function
Walter,
Tuesday, May
use InnoDB which does support row-locking (MySQL-Max)
-Original Message-
From: Sherzod B. Ruzmetov [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 14, 2002 9:23 AM
To: [EMAIL PROTECTED]; MySQL Maling list
Subject: Row Locking issue in 3.23.x
Hi. As far as I know, MySQL doesn't suport
/etc/my.cnf isn't installed by default. Try my-huge.cnf (I think)
Also, did you use mysql-max on the new one and mysql on the old (just
wondering)
This is what I use for a 1 Gig RAM single processor machine:
[mysqld]
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable =
I think he is saying varchar with support for unicode (that's how it is
labeled in sqlserver).
-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 12, 2002 1:07 AM
To: saraswathy saras
Cc: [EMAIL PROTECTED]
Subject: Re: nvarchar
On Thu, Apr 11, 2002
Firewall isn't good enough (who else is inside your firewall, likely the
entire hosting company or internal corporate network). The user table
has a host column that I use. Also, you can enable ipfw or some other
local firewall on the host itself if you are very serious.
-Original
I think you need to be more detailed. What is your goal? Let's work
from there.
-Original Message-
From: Daniel Jarrett [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 29, 2002 3:19 AM
To: [EMAIL PROTECTED]
Subject: [newbie post] linked lists..
is there any way of doing a linked
: Wednesday, March 27, 2002 1:05 AM
To: adam nelson
Cc: [EMAIL PROTECTED]
Subject: Re: binary expansion?
adam nelson queried:
iPersonType is a list in the form of 1,2,4,8,16,32,64
so, let's suppose that szPersonType for 8 is lawyer and szPerson type
for 2 is redhead
a value of iPersonType of 10
It's been a while since I've done this, does anybody remember:
If I have 2 tables:
tblPerson (
iPersonID int
szPersonDesc varchar
iPersonType int
)
tblPersonType (
iPersonType int
szPersonType varchar
)
iPersonType = iPersonType (foreign key between the 2 tables)
iPersonType is a list in
Correct me if I'm wrong, but a timestamp field is not meant to be human
updateable (ie. it's solely to record the last change to the record).
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 19, 2002 1:31 AM
To: [EMAIL PROTECTED]
Subject: Leap
I use a perl method for a full week's worth of backups, this could be
migrated to have a weekly, monthly, yearly snapshot as well.
#!/usr/bin/perl
my $szToday = `date +%a`;
chop ($szToday);
$dbDumpName = '/var/backup/dbDump.mysql';
$backupFile = '/var/backup/siteFiles';
$siteDir=
I just had the brilliant idea of using a password for mysql root with
semi-colons:
password is blahblah;;
this appears to have not worked in some way and now I'm stuck. I don't
want to restart since that isn't very graceful (on a production
machine). I've tried blahblah;;; and blahblah and
I don't see how to submit 'blahblah;;' using the quotes.
I've tried
mysql -u root
and then typed all the conceivable combinations for password
and
mysql -u root --password=blahblah;;
and
mysql -u root --password='blahblah;;'
What's worse is that I've looked at the raw table file and
I can access the client using other users, but none have mysql database
access (ie. everything is fine except that I can't add users). If
nobody knows the answer, I will restart with skip-grant-tables during
off-hours.
-
This is what I have on our dual PIII (1.1 Ghz) 1 GB ram
[mysqld]
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = table_cache=256
set-variable = key_buffer=128M
set-variable = tmp_table_size=16M
set-variable = interactive_timeout=7200
set-variable = wait_timeout=240
Is there any database only (currently I'm doing this in perl) solution
to update a record or insert it if criteria isn't met:
for example, I have a table:
mysql describe tblHits;
++--+--+-+-+---+
| Field | Type | Null |
Using foreign keys is a really, really good idea. Programs can't be
trusted (and what about running sql queries ad hoc). Even with a really
big database I wouldn't get rid of the keys, just time to move to a
bigger better machine/configuration.
-Original Message-
From: David Felio
I have to agree with the below. Clearly they want to get you into mssql
and keep you there. There's really no point in switching databases, so
(I know, this is too neutral) if you like these guys for some reason
(good haircuts or whatnot), then go with mssql. If you can find people
with good
These two queries were execute one after the other. I am the only one
who updates this table. I have seen this happen before, and people have
said that perhaps it's a problem with softupdate. I haven't been able
to address it.
I am on FreeBSD 4.2 FreeBSD 3.23.35
There is quite a bit of load
Perhaps, reconsider you're layout. Even with mssql, you are not talking
about an easy or cheap situation (you would need to get the advanced
version of mssql I believe, which is mucho dinero x 2).
Why can't both computers be at the same location (and even run off the
same machine, leaving the
Has anyone used a mysql gui that works securely over the internet
(through ssh?). Currently I use ssh to do command line mysql, but I
thought that a gui on my desktop might work well.
I can't afford a vpn, so that isn't an option.
-Adam
Currently, I am using the following query:
SELECT DISTINCT o.szZipCode FROM tblZips z,tblZips o WHERE
z.szZipCode=$szZip AND (3956 * (2 *
ASIN(SQRT(POWER(SIN(((z.dblLat-o.dblLat)*0.017453293)/2),2) +
COS(z.dblLat*0.017453293) * COS(o.dblLat*0.017453293) *
70 matches
Mail list logo
