MySQL vs Plone/Zope/Python

[David Blomstrom]

I've been experimenting with databases, both Drupal
and Plone. Plone is a can of worms, but it's
intriguing, partly because it's so different. It
sounds like they replace LAMP with something
completely different, though it's a complete mystery
to me at present.

I wondered if anyone on this list has had experience
with Plone and could explain how their system compares
to PHP/MySQL. I'll be working with animal kingdom data
- child-parent relationships and recursive arrays.

I don't want to abandon PHP and MySQL for something
totally new after I've put so much time and effort
into them. But it might be worthwhile for this
particular site if Plone offers some major advantage.
It's also my understanding that PHP and MySQL can
still be used with Plone, though not be default.

So, do you know if Plone offers any significant
advantages over MySQL, or is it just comparing apples
and oranges?

Thanks.





Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ALTER TRIGGER in MySQL

[Karam Chand]

Hello,

I was reading the docs on Triggers at:

http://dev.mysql.com/doc/mysql/en/using-triggers.html

Looks like there is no support for Alter Trigger. So
if I want to change a trigger, I have to first drop
the existing one and recreate another?

Am I correct?

Karam




Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Creating new username & password

[Eugene Kosov]

David Blomstrom wrote:

What's the easiest way to create a username and
password for a new database? It's been so long since I
created my original database, I forgot how.


You can do it with a query like this:

GRANT USAGE ON database_name.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password';

Replace USAGE in query above with priveleges set you want for your new account.

See http://dev.mysql.com/doc/mysql/en/grant.html for more info.


Regards,
Eugene Kosov

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL 4.0.23a defunct

[Arek H]

Hi

Im running a stock install of Slackware 10.1 with the default setup of 
mysql. Whats happening is when I hit the reload button several times on 
a page that uses php and accesses mysql I get  defunct and 
zombies start showing when viewing top. I also get this when I run a web 
based apache bench on my server with the Requests set to 25 and 
Concurrency set to 5.


I checked my domains error logs and there is nothing there. I looked in 
the error log  located at /var/lib/mysql/ano.err and here are the last 
few lines of it. Im new to linux so this is the only error log I could 
find for mysql .


Memory status:
Non-mmapped space allocated from system: 5204056
Number of free chunks:   15
Number of fastbin blocks:0
Number of mmapped regions:   11
Space in mmapped regions:22401024
Maximum total allocated space:   0
Space available in freed fastbin blocks: 0
Total allocated space:   4901384
Total free space:302672
Top-most, releasable space:  131176
Estimated memory (with thread stack):27605080

050731 15:57:12  mysqld ended

050731 16:35:00  mysqld started
050731 16:35:02  InnoDB: Started
/usr/libexec/mysqld: ready for connections.
Version: '4.0.23a'  socket: '/var/run/mysql/mysql.sock'  port: 0  Source 
distribution

050731 16:40:59 /usr/libexec/mysqld: Normal shutdown

050731 16:40:59  InnoDB: Starting shutdown...
050731 16:41:00  InnoDB: Shutdown completed
050731 16:41:00 /usr/libexec/mysqld: Shutdown Complete

050731 16:41:00  mysqld ended

050731 16:42:17  mysqld started
050731 16:42:19  InnoDB: Started
/usr/libexec/mysqld: ready for connections.
Version: '4.0.23a'  socket: '/var/run/mysql/mysql.sock'  port: 0  Source 
distribution


Also here is what top shows

20797 root  12   0  1060 1060  824 R  1.3  0.2   0:18.60 top
20993 mysql 11   0 000 Z  1.0  0.0   0:00.03 mysqld 


20999 mysql 10   0 15612  15m 3020 S  0.6  3.1   0:00.02 mysqld
21007 mysql 11   0 000 Z  0.6  0.0   0:00.02 mysqld 


21017 mysql 13   0 15632  15m 3020 S  0.6  3.1   0:00.02 mysqld
1000 mysql 17   0 15592  15m 3020 R  0.3  3.1   0:01.89 mysqld
20991 mysql  9   0 000 Z  0.3  0.0   0:00.01 mysqld 


21013 mysql 10   0 15632  15m 3020 S  0.3  3.1   0:00.01 mysqld
21015 mysql 11   0 15632  15m 3020 S  0.3  3.1   0:00.01 mysqld
   1 root   8   0   228  228  200 S  0.0  0.0   0:04.86 init
   2 root   9   0 000 S  0.0  0.0   0:00.05 keventd
   3 root  19  19 000 S  0.0  0.0   0:00.00 ksoftirqd_CPU0
   4 root   9   0 000 S  0.0  0.0   0:00.00 kswapd
   5 root   9   0 000 S  0.0  0.0   0:00.00 bdflush
   6 root   9   0 000 S  0.0  0.0   0:00.02 kupdated
  10 root  -1 -20 000 S  0.0  0.0   0:00.00 mdrecoveryd
  11 root   9   0 000 S  0.0  0.0   0:00.06 kjournald
  44 root   9   0 000 S  0.0  0.0   0:00.16 kjournald
  45 root   9   0 000 S  0.0  0.0   0:00.26 kjournald

Thanks
Arek

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Creating new username & password

[David Blomstrom]

What's the easiest way to create a username and
password for a new database? It's been so long since I
created my original database, I forgot how.

When I created my new database with phpMyAdmin, I
scanned the page looking for anything mentioning
username or password but I couldn't find anything. I
remember encountering this problem before. It seems
only logical to prompt users for a username and
password after they've created a database. Or am I
missing something?

Thanks.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL in CentOS? try before?

[KH]

Hi all,
This amazing feedback from you guys, I feel much comfortable now start 
digging on this CentOS.

Thanks & Cheers
KH




Jason Pyeron wrote:

Yes, we use it for our Crisfield product, and we use it for our 
servers too.


On Thu, 4 Aug 2005, KH wrote:


Hi all,
I just came across Centos (Community Enterprise Operating System - 
http://www.centos.org/) It came with mysql as well. Hence my question 
here, does anyone guru here using this OS before for production ? 
Success story?






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: True randominess

[Scott Gifford]

Pat Adams <[EMAIL PROTECTED]> writes:

> On Thu, 2005-08-04 at 14:44 -0500, 2wsxdr5 wrote:
>> There are also several places that you can get a reasonably random 
>> number for the seed from your machine.  The amount of free disk space, 
>> unless that doesn't change much on your machine.  The amount of free 
>> RAM, (up time mod cpu usage).  Any number of things could be used that 
>> are not very predictable, if at all.
>
> But again, those aren't truely random. They're random-enough for the
> average web applications. The original poster, if memory serves, asked
> if it was possible to get true random numbers from MySQL. True random
> numbers can't be predicted even if I know everything about your system.
> Because computers are predictable beasts, the random number generators
> that they used are constrained by the hardware limits.

/dev/random is a source of some genuine entropy on many Unix-like
operating systems.  It uses variations in system timings that are
believed to be truly random.  It's not good for a large volume of
output, but it's a good seed.  You could probably incorporate access
to it or its friend /dev/urandom as a UDF:

http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/Adding_UDF.html

EGD (Entropy Gathering Daemon) is an option for other Unix-like
systems:

http://egd.sourceforge.net/

or you can use a Lava Lamp:

http://www.lavarnd.org/index.html

I'm sure Windows has some way to do this, too.

Many systems also have an onboard random number generator which you
should be able to access through an OS driver.

ScottG.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: How to delete log files? Was: RE: Where did my disk space go?

[Ron Thomas]

One thing to remember about *nix, is that disk space is not released until all 
processes that had a
file open have closed it.  For example, if a process has a 2Gb file open, and 
another process rm's
the file, an ls will show that file as gone but the disk space is not released 
until the first
process has closed the file.  Been bit by this a few times.  Could this be the 
case here?

Thanks,
Ron.

"The software said to install Windows 98 or better... So I installed linux."
"Friends don't let friends use IE"




 [EMAIL PROTECTED]  
   


 08/04/05 04:29 PM  
 To 
 [EMAIL PROTECTED], [EMAIL 
PROTECTED]   

 cc 
 mysql@lists.mysql.com  


Subject 
 RE: How to delete log files? 
Was: RE: Where did my 
 disk space go? 

















Hi Siegfried,

There is a section on each of the log files. I wouldn't go deleting the
log files without understanding exactly what you are trying to do. Have
a read of the section I mentioned. The logfiles that you are showing in
your directory are only 5Mb each and won't be taking anything if you
have > 1Gb free. The files you are showing are for the InnoDB engine and
I can't tell if you are using it. The only databases I can see are
mysql, test and hotjobs. All the other files are well within the usual
sizes that I would expect.

The my.cnf file enables you to customise your installation to ensure
best performance for your application. We use replication a lot here at
work and have custom requirements for the servers so end up doing many
mods from the defaults. I don't use replication at home and so don't
have to modify much at all.

The .cnf files you mentioned are example files. Mysql does not require a
my.cnf file as it makes a number of default assumptions if it is not
there.

The documentation at http://dev.mysql.com/doc/mysql/en/index.html goes
over all the different options that are available within the option
files. There are versions of the manual in other languages at
http://dev.mysql.com/doc/ HTH

Regards


David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Siegfried Heintze [mailto:[EMAIL PROTECTED]
Sent: Friday, 5 August 2005 8:46 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: How to delete log files? Was: RE: Where did my disk space go?

David (and anyone else):
Here is the directory of my data directory. I don't see hundreds of mega
bytes here, but deleting ib_logfile* and ibdata1 would sure help.

Which of these files I can delete without loosing any data from my
database?

drwxr-xr-x  1 Administrator mkpasswd0 Jul 30 23:07 hotjobs
-rw-r--r--  1 Administrator mkpasswd25,088 Mar  4 18:05
ib_arch_log_00
-rw-r--r--  1 Administrator mkpasswd  5,242,880 Aug  3 17:52 ib_logfile0
-rw-r--r--  1 Administrator mkpasswd  5,242,880 Mar  4 18:05 ib_logfile1
-rw-r--r--  1 Administrator mkpasswd 10,485,760 May  3 15:47 ibdata1
drwxr-xr-x  1 Administrator mkpasswd0 Mar  4 18:02 mysql
-rw-r--r--  1 Administrator mkpasswd53,127 Aug  3 17:52 sales.err
drwxr-xr-x  1 Administrator mkpasswd0 Mar  4 18:02 test

Now what about this my.cnf file? As I recall, this file lives in the top
level directory. I have no such file. I found these files in the mysql
directory:

my-huge.cnf
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf

I don't recall editing any of these. Should I be editing them?


Thanks,

Siegfried

-Original Message-
From: David Logan [mailto:[EMAIL PROTECTED]
Sent: Thurs

RE: How to delete log files? Was: RE: Where did my disk space go?

[Logan, David (SST - Adelaide)]

Hi Siegfried,

There is a section on each of the log files. I wouldn't go deleting the
log files without understanding exactly what you are trying to do. Have
a read of the section I mentioned. The logfiles that you are showing in
your directory are only 5Mb each and won't be taking anything if you
have > 1Gb free. The files you are showing are for the InnoDB engine and
I can't tell if you are using it. The only databases I can see are
mysql, test and hotjobs. All the other files are well within the usual
sizes that I would expect.

The my.cnf file enables you to customise your installation to ensure
best performance for your application. We use replication a lot here at
work and have custom requirements for the servers so end up doing many
mods from the defaults. I don't use replication at home and so don't
have to modify much at all.

The .cnf files you mentioned are example files. Mysql does not require a
my.cnf file as it makes a number of default assumptions if it is not
there. 

The documentation at http://dev.mysql.com/doc/mysql/en/index.html goes
over all the different options that are available within the option
files. There are versions of the manual in other languages at
http://dev.mysql.com/doc/ HTH

Regards


David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Siegfried Heintze [mailto:[EMAIL PROTECTED] 
Sent: Friday, 5 August 2005 8:46 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: How to delete log files? Was: RE: Where did my disk space go?

David (and anyone else):
Here is the directory of my data directory. I don't see hundreds of mega
bytes here, but deleting ib_logfile* and ibdata1 would sure help.

Which of these files I can delete without loosing any data from my
database?

drwxr-xr-x  1 Administrator mkpasswd0 Jul 30 23:07 hotjobs
-rw-r--r--  1 Administrator mkpasswd25,088 Mar  4 18:05
ib_arch_log_00
-rw-r--r--  1 Administrator mkpasswd  5,242,880 Aug  3 17:52 ib_logfile0
-rw-r--r--  1 Administrator mkpasswd  5,242,880 Mar  4 18:05 ib_logfile1
-rw-r--r--  1 Administrator mkpasswd 10,485,760 May  3 15:47 ibdata1
drwxr-xr-x  1 Administrator mkpasswd0 Mar  4 18:02 mysql
-rw-r--r--  1 Administrator mkpasswd53,127 Aug  3 17:52 sales.err
drwxr-xr-x  1 Administrator mkpasswd0 Mar  4 18:02 test

Now what about this my.cnf file? As I recall, this file lives in the top
level directory. I have no such file. I found these files in the mysql
directory:

my-huge.cnf 
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf

I don't recall editing any of these. Should I be editing them?


Thanks,

Siegfried

-Original Message-
From: David Logan [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 04, 2005 3:33 PM
To: Siegfried Heintze
Cc: mysql@lists.mysql.com
Subject: RE: Where did my disk space go?

Hi Siegfried,

In the mysql data directory,

-rw-rw1 mysqlmysql  358975 Mar 10 14:28 aaudbasa01.log
-rw-rw1 mysqlmysql   25088 Feb 15 08:08
ib_arch_log_00
-rw-rw1 mysqlmysql 5242880 Jun 22 11:20 ib_logfile0
-rw-rw1 mysqlmysql 5242880 Feb 15 08:08 ib_logfile1

The above files maybe in slightly different places, it depends on
whether
you are using the InnoDB engine or the MyISAM one. The InnoDB logging
files are the ib_logfiles. You could also check the my.cnf for any form
of
logging being switched on, in particular binary logging.

You could check for this as well. This will be in your my.cnf file as

# Replication Master Server (default)
# binary logging is required for replication
log_bin

If so, you could be filling up your disk quite easily

Regards

> Thank you David and Sebastion,
> I am not doing this in a transaction (at least, I did not do anything
> special to start a transaction) and I have no need for a transaction.
>
> How do I check the log files you two suggest?
>
> Thanks,
> Siegfried
>
> -Original Message-
> From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 03, 2005 8:10 PM
> To: Siegfried Heintze; mysql@lists.mysql.com
> Subject: RE: Where did my disk space go?
>
> Hi Siegfried,
>
> I would check your transaction logs. Are you doing this as one giant
> transaction? The system may be filling up the logs just in case you
need
> to rollback.
>
> Regards
>
> David Logan
> Database Administrator
> HP Managed Services
> 148 Frome Street,
> Adelaide 5000
> Australia
>
> +61 8 8408 4273 - Work
> +61 417 268 665 - Mobile
> +61 8 8408 4259 - Fax
>
>
> -Original Message-
> From: Siegfried Heintze [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 4 August 2005 10:00 AM
> To: mysql@lists.mysql.com
> Subject: Where did my disk space go?
>
> I've been using Perl 8.4+ (ActiveState) on WinXP. My program runs for
> many
> (> 20) hours issuing SQL UPDATE and DELETE commands.

How to delete log files? Was: RE: Where did my disk space go?

[Siegfried Heintze]

David (and anyone else):
Here is the directory of my data directory. I don't see hundreds of mega
bytes here, but deleting ib_logfile* and ibdata1 would sure help.

Which of these files I can delete without loosing any data from my database?

drwxr-xr-x  1 Administrator mkpasswd0 Jul 30 23:07 hotjobs
-rw-r--r--  1 Administrator mkpasswd25,088 Mar  4 18:05
ib_arch_log_00
-rw-r--r--  1 Administrator mkpasswd  5,242,880 Aug  3 17:52 ib_logfile0
-rw-r--r--  1 Administrator mkpasswd  5,242,880 Mar  4 18:05 ib_logfile1
-rw-r--r--  1 Administrator mkpasswd 10,485,760 May  3 15:47 ibdata1
drwxr-xr-x  1 Administrator mkpasswd0 Mar  4 18:02 mysql
-rw-r--r--  1 Administrator mkpasswd53,127 Aug  3 17:52 sales.err
drwxr-xr-x  1 Administrator mkpasswd0 Mar  4 18:02 test

Now what about this my.cnf file? As I recall, this file lives in the top
level directory. I have no such file. I found these files in the mysql
directory:

my-huge.cnf 
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf

I don't recall editing any of these. Should I be editing them?


Thanks,

Siegfried

-Original Message-
From: David Logan [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 04, 2005 3:33 PM
To: Siegfried Heintze
Cc: mysql@lists.mysql.com
Subject: RE: Where did my disk space go?

Hi Siegfried,

In the mysql data directory,

-rw-rw1 mysqlmysql  358975 Mar 10 14:28 aaudbasa01.log
-rw-rw1 mysqlmysql   25088 Feb 15 08:08
ib_arch_log_00
-rw-rw1 mysqlmysql 5242880 Jun 22 11:20 ib_logfile0
-rw-rw1 mysqlmysql 5242880 Feb 15 08:08 ib_logfile1

The above files maybe in slightly different places, it depends on whether
you are using the InnoDB engine or the MyISAM one. The InnoDB logging
files are the ib_logfiles. You could also check the my.cnf for any form of
logging being switched on, in particular binary logging.

You could check for this as well. This will be in your my.cnf file as

# Replication Master Server (default)
# binary logging is required for replication
log_bin

If so, you could be filling up your disk quite easily

Regards

> Thank you David and Sebastion,
> I am not doing this in a transaction (at least, I did not do anything
> special to start a transaction) and I have no need for a transaction.
>
> How do I check the log files you two suggest?
>
> Thanks,
> Siegfried
>
> -Original Message-
> From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 03, 2005 8:10 PM
> To: Siegfried Heintze; mysql@lists.mysql.com
> Subject: RE: Where did my disk space go?
>
> Hi Siegfried,
>
> I would check your transaction logs. Are you doing this as one giant
> transaction? The system may be filling up the logs just in case you need
> to rollback.
>
> Regards
>
> David Logan
> Database Administrator
> HP Managed Services
> 148 Frome Street,
> Adelaide 5000
> Australia
>
> +61 8 8408 4273 - Work
> +61 417 268 665 - Mobile
> +61 8 8408 4259 - Fax
>
>
> -Original Message-
> From: Siegfried Heintze [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 4 August 2005 10:00 AM
> To: mysql@lists.mysql.com
> Subject: Where did my disk space go?
>
> I've been using Perl 8.4+ (ActiveState) on WinXP. My program runs for
> many
> (> 20) hours issuing SQL UPDATE and DELETE commands. The update commands
> should not be increasing the storage requirements, I'm just updating
> integer
> values.
>
> I've noticed several times now that I run out of disk space. I started
> with
> a gigabyte free. Last time, I aborted the program, compressed my disk,
> retrieved much lost disk space and started again.
>
> Now I tried that again: no luck. I rebooted and recompressed again. I'm
> still out of disk space.
>
> It seems that MySQL just keeps using more and more disk space.
>
> How can I retrieve my lost disks pace?
>
> Thanks,
> Siegfried
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
David Logan
South Australia

when in trouble, or in doubt
run in circles, scream and shout


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

explain not explaining long running query?

[David Sparks]

I have a query that is taking days to complete (not good).  If I change
the query so that it selects less rows it runs fast.

I ran an explain on both queries and it didn't give any hints as to why
the one query is taking days to run.  In fact explain knows how many
rows each query will examine.

Please help explain this behavior to me.

Thanks,

ds



The output of running the queries:

mysql> select count(*) from msgs where message_id > 112000 and
message_id < 112001;
+--+
| count(*) |
+--+
|6 |
+--+
1 row in set (0.00 sec)


mysql> select count(*) from msgs where message_id > 112000 and
message_id < 112111;
(running for 2 days now)
->%-

The output of explain on both queries:

mysql> explain select count(*) from msgs where message_id > 112000
and message_id < 112111\G
*** 1. row ***
   id: 1
  select_type: SIMPLE
table: msgs
 type: range
possible_keys: PRIMARY
  key: PRIMARY
  key_len: 8
  ref: NULL
 rows: 580
Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> explain select count(*) from msgs where message_id > 112000
and message_id < 112001\G
*** 1. row ***
   id: 1
  select_type: SIMPLE
table: msgs
 type: range
possible_keys: PRIMARY
  key: PRIMARY
  key_len: 8
  ref: NULL
 rows: 5
Extra: Using where; Using index
1 row in set (0.00 sec)
->%


The table description:

mysql> describe messages\G
*** 1. row ***
  Field: message_id
   Type: double(15,5) unsigned
   Null:
Key: PRI
Default: 0.0
  Extra:
*** 2. row ***
  Field: abc1
   Type: int(10) unsigned
   Null:
Key:
Default: 0
  Extra:
*** 3. row ***
  Field: r_datetime
   Type: datetime
   Null: YES
Key:
Default: -00-00 00:00:00
  Extra:
*** 4. row ***
  Field: abc2
   Type: int(10) unsigned
   Null: YES
Key: MUL
Default: 0
  Extra:
*** 5. row ***
  Field: abc3
   Type: int(10) unsigned
   Null: YES
Key: MUL
Default: 0
  Extra:
*** 6. row ***
  Field: abc4
   Type: varchar(255)
   Null: YES
Key:
Default:
  Extra:
*** 7. row ***
  Field: abc5
   Type: float
   Null: YES
Key: MUL
Default: 0
  Extra:
*** 8. row ***
  Field: abc6
   Type: int(10) unsigned
   Null:
Key: MUL
Default: 0
  Extra:
*** 9. row ***
  Field: abc7
   Type: int(10) unsigned
   Null: YES
Key: MUL
Default: 0
  Extra:
*** 10. row ***
  Field: abc8
   Type: int(10) unsigned
   Null: YES
Key: MUL
Default: 0
  Extra:
*** 11. row ***
  Field: abc9
   Type: int(10) unsigned
   Null: YES
Key: MUL
Default: 0
  Extra:
*** 12. row ***
  Field: abc10
   Type: int(10) unsigned
   Null:
Key:
Default: 0
  Extra:
*** 13. row ***
  Field: abc11
   Type: int(10) unsigned
   Null:
Key:
Default: 0
  Extra:
13 rows in set (0.00 sec)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Where did my disk space go?

[David Logan]

Hi Siegfried,

In the mysql data directory,

-rw-rw1 mysqlmysql  358975 Mar 10 14:28 aaudbasa01.log
-rw-rw1 mysqlmysql   25088 Feb 15 08:08
ib_arch_log_00
-rw-rw1 mysqlmysql 5242880 Jun 22 11:20 ib_logfile0
-rw-rw1 mysqlmysql 5242880 Feb 15 08:08 ib_logfile1

The above files maybe in slightly different places, it depends on whether
you are using the InnoDB engine or the MyISAM one. The InnoDB logging
files are the ib_logfiles. You could also check the my.cnf for any form of
logging being switched on, in particular binary logging.

You could check for this as well. This will be in your my.cnf file as

# Replication Master Server (default)
# binary logging is required for replication
log_bin

If so, you could be filling up your disk quite easily

Regards

> Thank you David and Sebastion,
> I am not doing this in a transaction (at least, I did not do anything
> special to start a transaction) and I have no need for a transaction.
>
> How do I check the log files you two suggest?
>
> Thanks,
> Siegfried
>
> -Original Message-
> From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 03, 2005 8:10 PM
> To: Siegfried Heintze; mysql@lists.mysql.com
> Subject: RE: Where did my disk space go?
>
> Hi Siegfried,
>
> I would check your transaction logs. Are you doing this as one giant
> transaction? The system may be filling up the logs just in case you need
> to rollback.
>
> Regards
>
> David Logan
> Database Administrator
> HP Managed Services
> 148 Frome Street,
> Adelaide 5000
> Australia
>
> +61 8 8408 4273 - Work
> +61 417 268 665 - Mobile
> +61 8 8408 4259 - Fax
>
>
> -Original Message-
> From: Siegfried Heintze [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 4 August 2005 10:00 AM
> To: mysql@lists.mysql.com
> Subject: Where did my disk space go?
>
> I've been using Perl 8.4+ (ActiveState) on WinXP. My program runs for
> many
> (> 20) hours issuing SQL UPDATE and DELETE commands. The update commands
> should not be increasing the storage requirements, I'm just updating
> integer
> values.
>
> I've noticed several times now that I run out of disk space. I started
> with
> a gigabyte free. Last time, I aborted the program, compressed my disk,
> retrieved much lost disk space and started again.
>
> Now I tried that again: no luck. I rebooted and recompressed again. I'm
> still out of disk space.
>
> It seems that MySQL just keeps using more and more disk space.
>
> How can I retrieve my lost disks pace?
>
> Thanks,
> Siegfried
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
David Logan
South Australia

when in trouble, or in doubt
run in circles, scream and shout


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

how to determine right value for max_allowed_packet?

[Sid Lane]

all,

I just finshed hosing down a minor (that could have been FAR worse)
fire where replication failed with an:

"Error reading packet from server: Packet too large - increase
max_allowed_packet on this server"

in my error log.  I bumped it up from 1M to 4M, restarted mysql (as
well as dependant web servers) and replication went back on its merry
way and all appears to be well again.

my ? is:

how do I know how to size this parameter?  master & slave had been set
to the same value (1M).  we replicate a fair amount of data (~1GB of
binlog/2hr) and have not had any replication errors in the several
months since we implimented it so this is a little surprising.

FWIW, we're running 4.0.18 on SuSE 9.1.  before anyone says it:  yes,
we're planning on upgrading to 4.1.x but I have to finish an Oracle 10
upgrade 1st.

any help would be greatly appreciated...

thanks!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: True randominess

[Pat Adams]

On Thu, 2005-08-04 at 14:44 -0500, 2wsxdr5 wrote:
> There are also several places that you can get a reasonably random 
> number for the seed from your machine.  The amount of free disk space, 
> unless that doesn't change much on your machine.  The amount of free 
> RAM, (up time mod cpu usage).  Any number of things could be used that 
> are not very predictable, if at all.

But again, those aren't truely random. They're random-enough for the
average web applications. The original poster, if memory serves, asked
if it was possible to get true random numbers from MySQL. True random
numbers can't be predicted even if I know everything about your system.
Because computers are predictable beasts, the random number generators
that they used are constrained by the hardware limits.

But it's really just a semantic difference. Seeding with digits from the
least significant part of a UNIX timestamp would be sufficient to seed a
RNG randomly enough for average web applications, but it's not truely
random, since a web log will show what time the user hit the
application, and you can figure out what the RNG was seeded with at that
point. 
-- 
Pat Adams
Applications Programmer
SYSCO Food Services of Dallas


signature.asc
Description: This is a digitally signed message part

Re: True randominess

[2wsxdr5]

Pat Adams wrote:


However, in answer to your question, there is no way to get TRUE
randomness in a computer system. Even cryptographically secure random
number generators can be predicted under absolutely identical
circumstances.
 



While technically that is true, there is a method that will give what I 
think is a very random number and is extremely unlikely to produce the 
same sequence.  What you do is seed the random number generator with an 
integer based on the system time.  Unless the random number is generated 
at the same time every day, you will have very random out put.  If it is 
done at the same time every day you can use the date as part of your 
seed.  Depending on the frequency at which these random numbers need to 
be generated, you may wish to use fractions of a second or just seconds.


There are also several places that you can get a reasonably random 
number for the seed from your machine.  The amount of free disk space, 
unless that doesn't change much on your machine.  The amount of free 
RAM, (up time mod cpu usage).  Any number of things could be used that 
are not very predictable, if at all.



--
Chris W

Gift Giving Made Easy
Get the gifts you want & 
give the gifts they want

http://thewishzone.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL 4.1.13a binaries now available

[Lenz Grimmer]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello,

The MySQL 4.1.13 release included a fix to resolve a potential security
vulnerability in the zlib compression library (CAN-2005-2096) that is bundled
with the MySQL distribution. Very shortly afterwards, an additional potential
zlib security flaw was discovered and fixed - the issue is tracked by the Mitre
CVE ID CAN-2005-1849. However, this second fix appeared too late for inclusion
in the initial MySQL 4.1.13 release.

Even though zlib is included in the MySQL sources, most binary builds
distributed by MySQL actually are not statically linked against it by default.

On most platforms, MySQL is linked dynamically to the shared zlib library
provided by the operating system. This means that it's usually sufficient to
update the zlib library and restart MySQL to resolve this issue. Many OS
vendors have provided zlib updates by now.

For those platforms were the binaries are linked statically against zlib and
that were affected by this second vulnerabilty, we have now published updated
binaries on our download pages. The source archive was updated as well.

The MySQL binaries for the following platforms are affected and have been
updated to version 4.1.13a:

 - Microsoft Windows
 - HPUX 11.00/11.11 (PA/RISC)
 - HPUX 11.23 (IA64)
 - Mac OS X 10.4 (64bit only)
 - IBM AIX 5.2/4.3
 - Linux/s390

This is the same code base as the 4.1.13 release with just one additional patch
to resolve the security issue tracked by CAN-2005-1849. The source and binaries
are now available for Download from 
http://dev.mysql.com/downloads/mysql/4.1.html
and mirror sites. Even though we are currently not aware of any program that is
actually capable of exploiting this zlib vulnerability within MySQL, we would
like to encourage our users to make sure they update to this version as soon as
possible.

Bye,
LenZ
- -- 
 Lenz Grimmer <[EMAIL PROTECTED]>
 Senior Production Engineer
 MySQL GmbH, http://www.mysql.de/
 Hamburg, Germany
 Are you MySQL certified?  http://www.mysql.com/certification/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (GNU/Linux)
Comment: For info see http://quantumlab.net/pine_privacy_guard/

iD8DBQFC8m7lSVDhKrJykfIRAnQBAJ0VL9oULqKWsp7DW7MChM3gqAGOuACfQq+N
ePZqTR4/+IGMT6dOXqzLq2Y=
=SI7n
-END PGP SIGNATURE-

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: True randominess

[Pat Adams]

On Thu, 2005-08-04 at 15:00 -0400, Scott Hamm wrote:
> I've noticed that rand() do not change on each query request. Is there a way 
> I could get a TRUE randominess into MySQL?

http://dev.mysql.com/doc/mysql/en/mathematical-functions.html

Are you using RAND() or RAND(n)? Using RAND() makes MySQL choose its own
seed (the documentation doesn't specify what seed it will use). If you
choose to seed the random number generator (for example, RAND(3)) and
then start using RAND() it will produce a repeatable sequence.

mysql> SELECT rand(3), rand(), rand();
+--+--+--+
| rand(3)  | rand()   | rand()   |
+--+--+--+
| 0.18109050875631 | 0.75023213843837 | 0.20788919665654 |
+--+--+--+
1 row in set (0.00 sec)

mysql> SELECT rand(), rand(), rand();
+--+--+--+
| rand()   | rand()   | rand()   |
+--+--+--+
| 0.78874959870125 | 0.32008043427028 | 0.23415340598128 |
+--+--+--+
1 row in set (0.01 sec)

mysql> SELECT rand(3), rand(), rand();
+--+--+--+
| rand(3)  | rand()   | rand()   |
+--+--+--+
| 0.18109050875631 | 0.75023213843837 | 0.20788919665654 |
+--+--+--+
1 row in set (0.00 sec)

Notice that the numbers after calling RAND(3) are in the same sequence.

However, in answer to your question, there is no way to get TRUE
randomness in a computer system. Even cryptographically secure random
number generators can be predicted under absolutely identical
circumstances.

-- 
Pat Adams
Applications Programmer
SYSCO Food Services of Dallas


signature.asc
Description: This is a digitally signed message part

Re: indexes

[Sebastian]

Michael Stassen wrote:


Sebastian wrote:

I read the manual on indexes but i am a little confused how i should 
build the type of index i need.


i have this structure:

| id | item | type

|| 3 || 23  || news
|| 4 || 46  || faqs
|| 5 || 23  || news
|| 6 || 23  || news

query:

SELECT id, item, type ...
FROM comments
WHERE item = 23 AND type = 'news'

i want a mulitple column index in this case i assume?

INDEX name (item, type);

is this correct? anything else i should know?
i didn't pay attention to query speed until the site started to 
explode, so i need to speed up queries i created 2 years ago.


thanx.



The right index to add depends on your queries and the existing 
indexes.  Do you already have any indexes on your table?  Adding an 
index dramatically speeds up queries that use it, but at the cost of 
slower inserts (each index must be updated) and larger file size 
(indexes take space).  Thus, the ideal is to have the minimum set of 
indexes required for your typical queries.


Your sample query might be helped by a single-column index on item or 
type, depending on how restrictive the conditions "item = 23" and 
"type = 'news'" are.  The multiple-column index on (item, type) you 
describe would be best for this particular query (assuming there are 
more unique items than types), and others like it that restrict both 
item and type in the WHERE clause.  It would also function as a 
single-column index on item, so if you already have one of those, it 
could be droppped when you add the multi-column index.


Michael


i am merging about 3 tables (each about 10-15k rows) into one table, so 
that is why i asked about when i should create the index (before or 
after) since i will be moving things around.


since i have 3 tables which all are similar it is a pain because i have 
to repeat a lot of code in the app... this way i can just fetch the rows 
i need by specifying the item and type for the apps. so it's a new table 
with no data yet which i will dump into from other tables.




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.0/63 - Release Date: 8/3/2005


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

True randominess

[Scott Hamm]

I've noticed that rand() do not change on each query request. Is there a way 
I could get a TRUE randominess into MySQL?

-- 
Power to people, Linux is here.

Re: indexes

[Scott Noyes]

Some sections of the manual seem to indicate that it's better to
create indexes after data population, rather than before.  See section
7.2.14, "Speed of INSERT Statements".  The general procedure there is
to load the data using LOAD DATA INFILE, and then use myisamchk. "This
creates the index tree in memory before writing it to disk, which is
much faster because it avoids lots of disk seeks. The resulting index
tree is also perfectly balanced." (ibid)

On 8/4/05, Michael Stassen <[EMAIL PROTECTED]> wrote:
> Sebastian wrote:
> 
> > i forgot to add another question,
> >
> > is it ok to add index after the tables and data are already built, or is
> > it better to create the index before data gets inserted?
> 
> It's probably better to create the indexes up front (assuming you know in
> advance which ones will be needed), but I think that's a moot point if your
> table already exists and is full of data.  There's no sense starting from
> scratch when you can simply add the index with
> 
>ALTER TABLE yourtablename ADD INDEX name (item, type);
> 
> Be aware, however, that mysql alters a table by making a new, temporary table
> to match the new definition (old def + alterations), fills it with data from
> the original table, then replaces the old with the new.  The time to do this
> grows with the size of your table and number of indexes.  While it is in
> progress, statements which would change the data (e.g. INSERT, UPDATE) are
> locked out.  See the manual for details
> .
> 
> Michael
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

binary log long format

[Jerry Bonner]

The current documentation states :

 Log some extra information to the log files (update log, binary update 
log, and slow queries log, whatever log has been activated). For example, 
username and timestamp are logged for queries. Before MySQL 4.1, if you 
are using --log-slow-queries and --log-long-format, queries that are not 
using indexes also are logged to the slow query log. --log-long-format is 
deprecated as of MySQL version 4.1, when --log-short-format was 
introduced. (Long log format is the default setting since version 4.1.) 
Also note that starting with MySQL 4.1, the 
--log-queries-not-using-indexes option is available for the purpose of 
logging queries that do not use indexes to the slow query log.


I'm using 4.1.13, however, I don't see username or host information for 
binary logs, just slow query logs. Am I missing something?


Jerry Bonner
Systems Engineer
CP Telecom

[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: indexes

[Michael Stassen]

Sebastian wrote:


i forgot to add another question,

is it ok to add index after the tables and data are already built, or is 
it better to create the index before data gets inserted?


It's probably better to create the indexes up front (assuming you know in 
advance which ones will be needed), but I think that's a moot point if your 
table already exists and is full of data.  There's no sense starting from 
scratch when you can simply add the index with


  ALTER TABLE yourtablename ADD INDEX name (item, type);

Be aware, however, that mysql alters a table by making a new, temporary table 
to match the new definition (old def + alterations), fills it with data from 
the original table, then replaces the old with the new.  The time to do this 
grows with the size of your table and number of indexes.  While it is in 
progress, statements which would change the data (e.g. INSERT, UPDATE) are 
locked out.  See the manual for details 
.


Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: indexes

[SGreen]

Sebastian <[EMAIL PROTECTED]> wrote on 08/04/2005 01:31:13 PM:

> i forgot to add another question,
> 
> is it ok to add index after the tables and data are already built, or is 

> it better to create the index before data gets inserted?
> 
> Sebastian wrote:
> 
> > I read the manual on indexes but i am a little confused how i should 
> > build the type of index i need.
> >
> > i have this structure:
> >
> > | id | item | type
> > 
> > || 3 || 23  || news
> > || 4 || 46  || faqs
> > || 5 || 23  || news
> > || 6 || 23  || news
> >
> > query:
> >
> > SELECT id, item, type ...
> > FROM comments
> > WHERE item = 23 AND type = 'news'
> >
> > i want a mulitple column index in this case i assume?
> >
> > INDEX name (item, type);
> >
> > is this correct? anything else i should know?
> > i didn't pay attention to query speed until the site started to 
> > explode, so i need to speed up queries i created 2 years ago.
> >
> > thanx.
> >
> 

Yes, that two-column index will most likely speed up that type of query. 
If there is a 3rd column that frequently appears in the WHERE clauses of 
your queries along with `item` and `type`, you should probably consider 
making your index out of all 3 columns (with the 3rd column last) rather 
than just the two columns you already identified.

> is it ok to add index after the tables and data are already built, or is 

> it better to create the index before data gets inserted?

For bulk data loading and other INSERT/UPDATE operations that affect large 
numbers of rows (especially when building your table the first time), you 
should be able to setup your data much faster without the indexes in 
place. Also, since you are creating/modifying the indexes only once (and 
in bulk) it should take less TOTAL TIME (populate time + indexing time) 
with later indexing than if you had the indexes in place during the 
initial load.

I am sorry you waited so long to optimize your queries but better now than 
later.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: indexes

[Michael Stassen]

Sebastian wrote:

I read the manual on indexes but i am a little confused how i should 
build the type of index i need.


i have this structure:

| id | item | type

|| 3 || 23  || news
|| 4 || 46  || faqs
|| 5 || 23  || news
|| 6 || 23  || news

query:

SELECT id, item, type ...
FROM comments
WHERE item = 23 AND type = 'news'

i want a mulitple column index in this case i assume?

INDEX name (item, type);

is this correct? anything else i should know?
i didn't pay attention to query speed until the site started to explode, 
so i need to speed up queries i created 2 years ago.


thanx.


The right index to add depends on your queries and the existing indexes.  Do 
you already have any indexes on your table?  Adding an index dramatically 
speeds up queries that use it, but at the cost of slower inserts (each index 
must be updated) and larger file size (indexes take space).  Thus, the ideal 
is to have the minimum set of indexes required for your typical queries.


Your sample query might be helped by a single-column index on item or type, 
depending on how restrictive the conditions "item = 23" and "type = 'news'" 
are.  The multiple-column index on (item, type) you describe would be best for 
this particular query (assuming there are more unique items than types), and 
others like it that restrict both item and type in the WHERE clause.  It would 
also function as a single-column index on item, so if you already have one of 
those, it could be droppped when you add the multi-column index.


Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Question on V5.0.9 and SHOW command

[TheRefUmp]

Hello,
   I have a strange issue. When I issue the command SHOW COLUMNS from TABLE XYZ 
I get the usual output.The key field is blank because there is no key on the 
field. However, when I issue the command from a PERL script the the DBI::DBD it 
returns MUL in the key field. There is no index associated with this column 
either it's a straight VARCHAR(40) field. 
   Anyone have any ideas?

Regards,
George

__
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Count two kinds of related records?

[Brian Dunning]

I'm searching a table of people who own properties, and I want to  
also include the total count of related properties, and the count of  
related properties whose (status is 'Active' and approval is  
'Active'). I've got:


select accounts.name, count(properties.property_id) as totalcount  
from accounts, properties where  
accounts.account_id=properties.account_id group by accounts.account_id;


Works fine. Now I just need to figure out how to add that second  
count of property records meeting the two conditions. Anyone?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: indexes

[Sebastian]

i forgot to add another question,

is it ok to add index after the tables and data are already built, or is 
it better to create the index before data gets inserted?


Sebastian wrote:

I read the manual on indexes but i am a little confused how i should 
build the type of index i need.


i have this structure:

| id | item | type

|| 3 || 23  || news
|| 4 || 46  || faqs
|| 5 || 23  || news
|| 6 || 23  || news

query:

SELECT id, item, type ...
FROM comments
WHERE item = 23 AND type = 'news'

i want a mulitple column index in this case i assume?

INDEX name (item, type);

is this correct? anything else i should know?
i didn't pay attention to query speed until the site started to 
explode, so i need to speed up queries i created 2 years ago.


thanx.




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.0/63 - Release Date: 8/3/2005


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: 4.1 and unions

[Mike Johnson]

From: Scott Noyes [mailto:[EMAIL PROTECTED] 

> UNION is not broken; the syntax in the given comment is 
> incorrect. You can do 
> SELECT * FROM table UNION SELECT * FROM table
> or
> (SELECT * FROM table) UNION (SELECT * FROM table)
> but not
> SELECT * FROM table UNION (SELECT * FROM table)

Oh, interesting. My next question was going to be if the second syntax
is correct (all subqueries enclosed in parens), but looks like it's
good.

Thanks, Scott.

-- 
Mike Johnson Smarter Living, Inc.
Web Developerwww.smartertravel.com
[EMAIL PROTECTED]   (617) 886-5539

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

indexes

[Sebastian]

I read the manual on indexes but i am a little confused how i should 
build the type of index i need.


i have this structure:

| id | item | type

|| 3 || 23  || news
|| 4 || 46  || faqs
|| 5 || 23  || news
|| 6 || 23  || news

query:

SELECT id, item, type ...
FROM comments
WHERE item = 23 AND type = 'news'

i want a mulitple column index in this case i assume?

INDEX name (item, type);

is this correct? anything else i should know?
i didn't pay attention to query speed until the site started to explode, 
so i need to speed up queries i created 2 years ago.


thanx.


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.0/63 - Release Date: 8/3/2005


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 4.1 and unions

[Scott Noyes]

UNION is not broken; the syntax in the given comment is incorrect. You can 
do 
SELECT * FROM table UNION SELECT * FROM table
or
(SELECT * FROM table) UNION (SELECT * FROM table)
but not
SELECT * FROM table UNION (SELECT * FROM table)

On 8/4/05, Mike Johnson <[EMAIL PROTECTED]> wrote:
> 
> Hi all,
> 
> Is there anyone using version 4.1 that can verify or refute the claim
> posted at the bottom of the upgrade notes here?
> 
> http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html
> 
> The user claims that UNION statements with parentheses are broken in
> 4.1. That is, 'SELECT * FROM table1 UNION (SELECT * FROM table2)' or
> something similar.
> 
> We're looking to upgrade but find te claim a bit dubious as there's no
> follow-up, no other mention, and I can't recall seeing anything on the
> list about it. Can anyone perform a quick test or verify one way or the
> other? I'd be much obliged.
> 
> Thanks!
> 
> --
> Mike Johnson Smarter Living, Inc.
> Web Developer www.smartertravel.com 
> [EMAIL PROTECTED] (617) 886-5539
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

Re: 4.1 and unions

[Jason Pyeron]

mysql> select * from files where id < 10 union (select * from files where id >20 
and id <30);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near ')' 
at line 1
mysql> select * from files where id < 10 union select * from files where id >20 
and id <30;
++-+-+---+---+---+--+
| id | pathref | version | mdate | ddate | size  | md5  |
++-+-+---+---+---+--+
|  1 |  19 |   0 | 1094523040217 | NULL  | 9 | NULL |
|  2 |  21 |   0 | 1120090156726 | NULL  |45 | NULL |
|  3 |  22 |   0 | 1120089642527 | NULL  |14 | NULL |
|  4 |  23 |   0 | 1120089642527 | NULL  |28 | NULL |
|  5 |  25 |   0 | 1120781223174 | NULL  |  6171 | NULL |
|  6 |  26 |   0 | 1120781223204 | NULL  |  5511 | NULL |
|  7 |  31 |   0 | 1120781215964 | NULL  | 13854 | NULL |
|  8 |  33 |   0 | 1120781222173 | NULL  |  6236 | NULL |
|  9 |  35 |   0 | 1120781216184 | NULL  | 15036 | NULL |
| 21 |  48 |   0 | 1120781222753 | NULL  | 27663 | NULL |
| 22 |  49 |   0 | 1120781222723 | NULL  |  6477 | NULL |
| 23 |  50 |   0 | 112078103 | NULL  |  6397 | NULL |
| 24 |  51 |   0 | 1120781222663 | NULL  | 12968 | NULL |
| 25 |  52 |   0 | 1120781222713 | NULL  |  8904 | NULL |
| 26 |  53 |   0 | 1120781222693 | NULL  |  6397 | NULL |
| 27 |  54 |   0 | 1120781222673 | NULL  |  6397 | NULL |
| 28 |  55 |   0 | 1120781222653 | NULL  |  6337 | NULL |
| 29 |  56 |   0 | 1120781222623 | NULL  |  6447 | NULL |
++-+-+---+---+---+--+
18 rows in set (0.03 sec)


Server version: 4.1.13-nt

On Thu, 4 Aug 2005, Mike Johnson wrote:


Hi all,

Is there anyone using version 4.1 that can verify or refute the claim
posted at the bottom of the upgrade notes here?

http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html

The user claims that UNION statements with parentheses are broken in
4.1. That is, 'SELECT * FROM table1 UNION (SELECT * FROM table2)' or
something similar.

We're looking to upgrade but find te claim a bit dubious as there's no
follow-up, no other mention, and I can't recall seeing anything on the
list about it. Can anyone perform a quick test or verify one way or the
other? I'd be much obliged.

Thanks!




--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-   -
- Jason Pyeron  PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218   -
-   -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This message is for the designated recipient only and may contain 
privileged, proprietary, or otherwise private information. If you 
have received it in error, purge the message from your system and 
notify the sender immediately.  Any other use of the email by you 
is prohibited.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

4.1 and unions

[Mike Johnson]

Hi all,

Is there anyone using version 4.1 that can verify or refute the claim
posted at the bottom of the upgrade notes here?

http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html

The user claims that UNION statements with parentheses are broken in
4.1. That is, 'SELECT * FROM table1 UNION (SELECT * FROM table2)' or
something similar.

We're looking to upgrade but find te claim a bit dubious as there's no
follow-up, no other mention, and I can't recall seeing anything on the
list about it. Can anyone perform a quick test or verify one way or the
other? I'd be much obliged.

Thanks!

-- 
Mike Johnson Smarter Living, Inc.
Web Developerwww.smartertravel.com
[EMAIL PROTECTED]   (617) 886-5539

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to find Balance?

[Scott Noyes]

Is this what you had in mind? It assumes MySQL version 4.1 or later:

SELECT 
id, 
price - 
(SELECT 
SUM(IF(type = 'PAID', amount, 0 - amount)) 
FROM payment py 
WHERE py.id  = pr.id ) 
AS balance 
FROM 
price pr;

On 8/4/05, Yesmin Patwary <[EMAIL PROTECTED]> wrote:
> 
> 
> Dear All,
> 
> 
> 
> I am trying to calculate balance for all id's. Can it be done using only 
> one MySQL query (without any scripting, eg: php,perl,asp..)?
> 
> 
> 
> price table:
> 
> id|price
> 
> 1|150
> 
> 2|100
> 
> 3|300
> 
> 
> 
> payment table:
> 
> id|transaction_type|amount
> 
> 1|PAID|75
> 
> 1|PAID|25
> 
> 1|REFUND|60
> 
> 1|REFUND|30
> 
> 1|PAID|140
> 
> 2|PAID|35
> 
> 2|REFUND|15
> 
> 3|PAID|300
> 
> 
> 
> Thanks in advance for any help.
> 
> 
> __ 
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>

Re: How to find Balance?

[SGreen]

Yesmin Patwary <[EMAIL PROTECTED]> wrote on 08/04/2005 12:42:44 PM:

> 
> Dear All,
> 
> 
> 
> I am trying to calculate balance for all id's.  Can it be done using
> only one MySQL query (without any scripting, eg: php,perl,asp..)? 
> 
> 
> 
> price table:
> 
> id|price
> 
> 1|150
> 
> 2|100
> 
> 3|300
> 
> 
> 
> payment table:
> 
> id|transaction_type|amount
> 
> 1|PAID|75
> 
> 1|PAID|25
> 
> 1|REFUND|60
> 
> 1|REFUND|30
> 
> 1|PAID|140
> 
> 2|PAID|35
> 
> 2|REFUND|15
> 
> 3|PAID|300
> 
> 
> 
> Thanks in advance for any help.
> 

If by "balance" you mean (total paid) - (total refunded) for each account, 
then yes, it is possible to calculate that value in one query:

SELECT id
, sum(if(transaction_type="PAID"
,amount
,if(transaction_type="REFUND"
, -amount
,0
)
)
) as balance
FROM paymenttable
GROUP BY id;

You could also write this same logic with a CASE statement

SELECT ID
, SUM(
CASE transaction_type 
WHEN 'PAID' THEN amount 
WHEN 'REFUND' THEN -amount
ELSE 0
END
 ) as balance
FROM paymenttable
GROUP BY ID;

Personally, I think the case statement is easier to read for this type of 
query.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Database equivalent to NorthWind for M$ SQL

[Scott Hamm]

Lol

On 8/4/05, mos <[EMAIL PROTECTED]> wrote:
> 
> At 09:02 AM 8/4/2005, you wrote:
> >Is it possible if MySQL could create its own ficitional database to
> >standardize the way we train ourselves in MySQL and to be able to
> >communicate our ideas to others than having to expose our actual working
> >datas to others? Let M$ have their own NorthWind and we have ours.
> 
> >> Let M$ have their own NorthWind and we have ours.
> Yes but MyWind just does "cut it".
> 
> Mike
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Power to people, Linux is here.

How to find Balance?

[Yesmin Patwary]

Dear All,

 

I am trying to calculate balance for all id's.  Can it be done using only one 
MySQL query (without any scripting, eg: php,perl,asp..)?  

 

price table:

id|price

1|150

2|100

3|300

 

payment table:

id|transaction_type|amount

1|PAID|75

1|PAID|25

1|REFUND|60

1|REFUND|30

1|PAID|140

2|PAID|35

2|REFUND|15

3|PAID|300

 

Thanks in advance for any help.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: Database equivalent to NorthWind for M$ SQL

[mos]

At 09:02 AM 8/4/2005, you wrote:

Is it possible if MySQL could create its own ficitional database to
standardize the way we train ourselves in MySQL and to be able to
communicate our ideas to others than having to expose our actual working
datas to others? Let M$ have their own NorthWind and we have ours.


>> Let M$ have their own NorthWind and we have ours.
Yes but MyWind just does "cut it".

Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Where did my disk space go?

[Siegfried Heintze]

I did the following from the Cygwin bash prompt on WinXP Pro.

cd /c/mysql
find . -size -10M | xargs ls -l 

I only found one file greater than 10 megabytes. I'm looking for several
hundred megabytes.

Thanks,
Siegfried


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ibbackup question

[Xinhuan Zheng]

I've been using it for a while. you can find more info in 
http://www.innodb.com/manual.php. 

--xinhuan

On 8/4/05, Clyde Lewis <[EMAIL PROTECTED]> wrote:
> 
> I'm in the process of testing this backup utility and need to know if 
> anyone
> have any experience using it as their primary
> backup solution.
> 
> Any help would be greatly appreciated.
> 
> Thanks in advance.
> 
> ***
> Clyde Lewis
> DBA
> General Parts
> (919)227-5100
> [EMAIL PROTECTED]
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

Re: Persistent Corruption [UPDATE]

[Chris McKeever]

thanks for everyone help/suggestions --
I wound up swapping the media, and what was corrupt prior, seems to be
all nice and happy now --

so - as suggested, looks to be that the drive was starting to fault

On 7/31/05, Chris McKeever <[EMAIL PROTECTED]> wrote:
> Thanks for all the help - I tried the dump as suggested, and it still
> corrupted...waiting on the new drive to come and then I will try
> replacing that .. I hope that I dont have to move to a new kernel!!
> 
> 
> 
> On 7/29/05, Terence <[EMAIL PROTECTED]> wrote:
> > Also check your kernel version. We had the same problems running on the
> > old RH 7.3 kernel (2.4.17 was it?) After upgrading to RH9 it was fine.
> >
> > Chris McKeever wrote:
> > > MySQL 4.0.16
> > >
> > > I am having this annoying persistent corruption issue, and am
> > > wondering if anyone has any suggestions.  I have two tables that
> > > refuse to stay clean.  After a myisamchk (below) they show corruption.
> > >  I run a myisamchk -r, they get fixed, and the next day, they are once
> > > again corrupt.
> > >
> > > Finally, I did a mysqldump, dropped the tables, imported the data from
> > > the dump, and the next day - corrupt.
> > >
> > > I am at a loss, I thought the brute force method should clean it.
> > >
> > > I dont think it is the applicaiton itself, since there are an
> > > identical 24 tables (alphabet) that do not have this issue.
> > >
> > > Any help would be appreciated - Chris
> > >
> > > Checking MyISAM file: EmailMessage_c.MYI
> > > Data records:   79196   Deleted blocks:  22
> > > myisamchk: warning: 1 clients is using or hasn't closed the table properly
> > > - check file-size
> > > - check key delete-chain
> > > - check record delete-chain
> > > - check index reference
> > > - check data record references index: 1
> > > - check record links
> > > myisamchk: error: Record-count is not ok; is 79197Should be: 79196
> > > myisamchk: warning: Found 457872 deleted space.   Should be 459588
> > > myisamchk: warning: Found  79507 partsShould be: 
> > > 79506 parts
> > > MyISAM-table 'EmailMessage_c.MYI' is corrupted
> > > Fix it using switch "-r" or "-o"
> > >
> > >
> > >
> > > Checking MyISAM file: EmailMessage_j.MYI
> > > Data records:   39907   Deleted blocks:  91
> > > myisamchk: warning: 1 clients is using or hasn't closed the table properly
> > > - check file-size
> > > - check key delete-chain
> > > - check record delete-chain
> > > - check index reference
> > > - check data record references index: 1
> > > - check record links
> > > myisamchk: error: Record-count is not ok; is 39909Should be: 39907
> > > myisamchk: warning: Found 719032 deleted space.   Should be 742328
> > > myisamchk: warning: Found 89 deleted blocks   Should be: 91
> > > myisamchk: warning: Found  40195 partsShould be: 
> > > 40193 parts
> > > MyISAM-table 'EmailMessage_j.MYI' is corrupted
> > > Fix it using switch "-r" or "-o"
> > >
> > >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> 
> 
> --
> --
> please respond to the list .. if you need to contact me direct
> cgmckeever is the account
> prupref.com is the domain
> 
> http://www.prupref.com";>Simply Chicago Real Estate
> 


-- 
--
please respond to the list .. if you need to contact me direct
cgmckeever is the account
prupref.com is the domain

http://www.prupref.com";>Simply Chicago Real Estate

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL in CentOS? try before?

[Jason Pyeron]

Yes, we use it for our Crisfield product, and we use it for our servers 
too.


On Thu, 4 Aug 2005, KH wrote:


Hi all,
I just came across Centos (Community Enterprise Operating System - 
http://www.centos.org/) It came with mysql as well. Hence my question here, 
does anyone guru here using this OS before for production ? Success story?


--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-   -
- Jason Pyeron  PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218   -
-   -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This message is for the designated recipient only and may contain 
privileged, proprietary, or otherwise private information. If you 
have received it in error, purge the message from your system and 
notify the sender immediately.  Any other use of the email by you 
is prohibited.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL in CentOS? try before?

[Gary Richardson]

We've been using CentOS more and more. I recently built a small prototype 
datawarehouse with CentOS (but I used MySQL 5 RPM's instead of the stock).

I've had no problems that I didn't have with RHEL on CentOS. I also have a 
few similar RHEL systems, so when a problem occurs on a CentOS box, I 
reproduce it on a RHEL box and and get support that way.

On 8/4/05, KH <[EMAIL PROTECTED]> wrote:
> 
> Hi all,
> I just came across Centos (Community Enterprise Operating System -
> http://www.centos.org/) It came with mysql as well. Hence my question
> here, does anyone guru here using this OS before for production ?
> Success story?
> 
> Cheers
> KH
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

RE: Where did my disk space go?

[Siegfried Heintze]

Thank you David and Sebastion,
I am not doing this in a transaction (at least, I did not do anything
special to start a transaction) and I have no need for a transaction.

How do I check the log files you two suggest?

Thanks,
Siegfried

-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 03, 2005 8:10 PM
To: Siegfried Heintze; mysql@lists.mysql.com
Subject: RE: Where did my disk space go?

Hi Siegfried,

I would check your transaction logs. Are you doing this as one giant
transaction? The system may be filling up the logs just in case you need
to rollback.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Siegfried Heintze [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 4 August 2005 10:00 AM
To: mysql@lists.mysql.com
Subject: Where did my disk space go?

I've been using Perl 8.4+ (ActiveState) on WinXP. My program runs for
many
(> 20) hours issuing SQL UPDATE and DELETE commands. The update commands
should not be increasing the storage requirements, I'm just updating
integer
values.

I've noticed several times now that I run out of disk space. I started
with
a gigabyte free. Last time, I aborted the program, compressed my disk,
retrieved much lost disk space and started again.

Now I tried that again: no luck. I rebooted and recompressed again. I'm
still out of disk space.

It seems that MySQL just keeps using more and more disk space.

How can I retrieve my lost disks pace?

Thanks,
Siegfried


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Problem with datetime values and MyODBC driver

[Dušan Pavlica]

Hello,
I'm working in C++ Builder 6.0 and I'm trying to create application which could 
connect through ADO components to different databases using ODBC drivers.
If I create TADOQuery object which has datetime parametr and I fill this 
parametr with valid datetime value then I can see in MYSQL's query log that 
only date portion is used. But if I use same query object to connect to MS 
Access then data in MS Access are OK. 

Win XP, MyODBC 3.51.11, MySQL 4.1.9

Does anybody have any idea how to solve this problem?

Thanks in advance 

Dusan Pavlica

RE: is this correct

[Jay Blanchard]

[snip]
 Is this the general enquiry list foe mysql sorry to be a pain
[/snip]

Yes, and there was no pain at all.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Ibbackup question

[Clyde Lewis]

I'm in the process of testing this backup utility and need to know if anyone
have any experience using it as their primary
backup solution.

Any help would be greatly appreciated.

Thanks in advance.

***
Clyde Lewis
DBA
General Parts
(919)227-5100
[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL 4.1.12-debian + large HEAP table

[gaga moo]

Hello Gleb,

Thanks for your answer.

--- Gleb Paharenko <[EMAIL PROTECTED]> wrote:

> Hello.
> 
> 
> > 1. Why does the HEAP table take so much more
> physical
> > space than the MyISAM table?
> 
> There is a formula which allows to count the
> approximate memory usage
> for one row. Multiply an obtained value by number of
> rows and compare the
> results with the size of your table. See:
>  
>
http://dev.mysql.com/doc/mysql/en/memory-storage-engine.html

OK, meanwhile I saw this in the docs: 

MEMORY tables use a fixed record length format.

So I guess this is why my HEAP table is triple the
size of the disk table.  (Many varchars...)

> > 2. How can I trace the memory consumption from
> within
> > mysql to know what's causing the huge spikes in
> > consumption that eat my entire system memory?
>
>
> Check if memory frees after dropping your HEAP
> table. Sometimes
> I met the weird behavior with non-official binaries.
> So, if nothing
> helps, try the latest release from
> http://dev.mysql.com/downloads.

I did so, no memory at all was freed by mysql.

Does anybody on the list know how I can see exactly
how the memory allocated by mysql is utilized
internally?

I'm suspecting a leak.

Thanks



__ 
Yahoo! Mail 
Stay connected, organized, and protected. Take the tour: 
http://tour.mail.yahoo.com/mailtour.html 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

re: is this correct

[Ben]

hi,
 Is this the general enquiry list foe mysql
sorry to be a pain

yours
Ben (jmonkey)
:-)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Ibbackup and GNR

[Clyde Lewis]

I've been searching for a decent backup utility and is currently in the
process of testing ibbackup from www.innodb.com and is presently stuck.
I've tried running the script but it will only parse the first GNR in my
my.cnf file. The my.cnf file has five group numbers which sets up five
databases.  I'm still testing and have not yet purchased support, but
thought that I could get some feedback from this list.  Any help would be
greatly apprecciated in helping me move forward with this testing.

***
Clyde Lewis
DBA
General Parts
(919)227-5100
[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Database equivalent to NorthWind for M$ SQL

[Scott Hamm]

Is it possible if MySQL could create its own ficitional database to 
standardize the way we train ourselves in MySQL and to be able to 
communicate our ideas to others than having to expose our actual working 
datas to others? Let M$ have their own NorthWind and we have ours.

On 8/4/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> 
> It's only known in certain circles and it's only useful as an educational
> tool. What it is: Northwind is a small, populated, relational database for
> a fictional trading company that MS uses as an example database in several
> layers of training documentation. If you have never used any of the MS
> database products (MS Access, MS SQL Server, ...) then you would not have
> encountered this database. It is created during most installs, much like
> the `test` database in MySQL except `Northwind` already has data in it.
> 
> Since so many people need so much help to understand how to use the MS
> database products, there is a lot of "training" material both "in the
> wild" and on the various MS sites. Because it was beyond the scope of most
> of the lessons to also teach the users how to create tables and enter test
> data, much of that educational material references a common database that
> nearly all users of those products should have by default.
> 
> You are right, it is not particularly related to MySQL except as a
> possible training aid. Newbies can load the Northwind data and practice
> translating queries from T-SQL (MS SQL SERVER) or Access-SQL (MS Access)
> into standard SQL (MySQL and others) to make the examples work. Depending
> on what version of MySQL they are using, some of the elements in the
> Northwind database will not be supported (Views &Stored Procedures, to
> name two). But as a "well known" set of data, it still has some
> educational value as many of the query patterns they teach will translate
> directly to MySQL uses.
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> Enrique Sanchez Vela <[EMAIL PROTECTED]> wrote on 08/04/2005 08:58:48
> AM:
> 
> >
> > I ran a quick search on google regarding the NothWind
> > Database or schema, but I did not run across anything
> > that would explain what it is and what it is used for,
> > however, it seems to be a pretty useful and known one.
> >
> > I would appeciate insight on this.
> >
> > thanks
> > esv.
> >
> > ps. I think it would be more appropiate to reply
> > privately to me, since this does not seem to be
> > particulary related to MySQL.
> >
> > thanks again.
> >
> > --- "J. David Boyd" <[EMAIL PROTECTED]> wrote:
> >
> > > Peter Brawley <[EMAIL PROTECTED]> writes:
> > >
> > > > Scott,
> > > >
> > > >>Is there any database file similiar to M$'s
> > > Northwind that I can use to play
> > > >>with? It would be nice if there is one inside
> > > MySQL by default for newbies
> > > >>to start out with.
> > > >
> > > > For our chapters on Connector/ODBC and dotNet,
> > > Arthur & I wrote scripts to
> > > > create MySQL versions of the NW schema ...
> > > >
> > >
> > http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html
> > > (InnoDB)
> > > > and
> > >
> > http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html#11-2
> > > > (MyISAM). If you like, I'll email you the SQL
> > > script for populating the
> > > > tables.
> > > >
> > > > PB
> > > >
> > >
> > > I would like to see these scripts as well. Can you
> > > please post them
> > > somewhere, or mail them to me as well?
> > >
> > > Dave
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > >
> > http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> >
> >
> > Enrique Sanchez Vela
> > email: [EMAIL PROTECTED]
> >
> 
> -
> > It's often easier to fight for one's || We live in the
> > outer space
> > principles than to live up to them || Rev. Kay Greenleaf
> > Adlai Stevenson ||
> >
> >
> >
> > 
> > Start your day with Yahoo! - make it your home page
> > http://www.yahoo.com/r/hs
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
> 
> 


-- 
Power to people, Linux is here.

Re: Database equivalent to NorthWind for M$ SQL

[SGreen]

It's only known in certain circles and it's only useful as an educational 
tool. What it is: Northwind is a small, populated, relational database for 
a fictional trading company that MS uses as an example database in several 
layers of training documentation. If you have never used any of the MS 
database products (MS Access, MS SQL Server, ...) then you would not have 
encountered this database. It is created during most installs, much like 
the `test` database in MySQL except `Northwind` already has data in it.

Since so many people need so much help to understand how to use the MS 
database products, there is a lot of "training" material both "in the 
wild" and on the various MS sites. Because it was beyond the scope of most 
of the lessons to also teach the users how to create tables and enter test 
data, much of that educational material references a common database that 
nearly all users of those products should have by default.

You are right, it is not particularly related to MySQL except as a 
possible training aid. Newbies can load the Northwind data and practice 
translating queries from T-SQL (MS SQL SERVER) or Access-SQL (MS Access) 
into standard SQL (MySQL and others)  to make the examples work. Depending 
on what version of MySQL they are using, some of the elements in the 
Northwind database will not be supported (Views &Stored Procedures, to 
name two). But as a "well known" set of data, it still has some 
educational value as many of the query patterns they teach will translate 
directly to MySQL uses.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Enrique Sanchez Vela <[EMAIL PROTECTED]> wrote on 08/04/2005 08:58:48 
AM:

> 
> I ran a quick search on google regarding the NothWind
> Database or schema, but I did not run across anything
> that would explain what it is and what it is used for,
> however, it seems to be a pretty useful and known one.
> 
> I would appeciate insight on this. 
> 
> thanks
> esv.
> 
> ps. I think it would be more appropiate to reply
> privately to me, since this does not seem to be
> particulary related to MySQL.
> 
> thanks again.
> 
> --- "J. David Boyd" <[EMAIL PROTECTED]> wrote:
> 
> > Peter Brawley <[EMAIL PROTECTED]> writes:
> > 
> > > Scott,
> > >
> > >>Is there any database file similiar to M$'s
> > Northwind that I can use to play
> > >>with? It would be nice if there is one inside
> > MySQL by default for newbies
> > >>to start out with.
> > >
> > > For our chapters on Connector/ODBC and dotNet,
> > Arthur & I wrote scripts to
> > > create MySQL versions of the NW schema ...
> > >
> >
> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html
> > (InnoDB)
> > > and
> >
> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html#11-2
> > > (MyISAM).  If you like, I'll email you the SQL
> > script for populating the
> > > tables.
> > >
> > > PB
> > >
> > 
> > I would like to see these scripts as well.  Can you
> > please post them
> > somewhere, or mail them to me as well?
> > 
> > Dave
> > 
> > 
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: 
> >
> http://lists.mysql.com/[EMAIL PROTECTED]
> > 
> > 
> 
> 
> Enrique Sanchez Vela
> email: [EMAIL PROTECTED]
> 
-
> It's often easier to fight for one's||We live in the 
> outer space 
> principles than to live up to them||Rev. Kay Greenleaf
> Adlai Stevenson||
> 
> 
> 
> 
> Start your day with Yahoo! - make it your home page 
> http://www.yahoo.com/r/hs 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: Database equivalent to NorthWind for M$ SQL

[Scott Hamm]

Ok. Now that I got NorthWind into my MySQL 5.0.9 database and also am 
running M$ SQL database server at where I work, this might be a good 
opportunity for me to learn how to mirgate from M$ SQL database to MySQL 
using Northwind and to see the difference between them. Is there anything 
that I should pay close attention to, in order to make the most smooth 
mirgation? When I migrated several databases from M$ SQL to MySQL, some 
dates was flushed to 0 values. 

On 8/4/05, Enrique Sanchez Vela <[EMAIL PROTECTED]> wrote:
> 
> 
> I ran a quick search on google regarding the NothWind
> Database or schema, but I did not run across anything
> that would explain what it is and what it is used for,
> however, it seems to be a pretty useful and known one.
> 
> I would appeciate insight on this.
> 
> thanks
> esv.
> 
> ps. I think it would be more appropiate to reply
> privately to me, since this does not seem to be
> particulary related to MySQL.
> 
> thanks again.
> 
> --- "J. David Boyd" <[EMAIL PROTECTED]> wrote:
> 
> > Peter Brawley <[EMAIL PROTECTED]> writes:
> >
> > > Scott,
> > >
> > >>Is there any database file similiar to M$'s
> > Northwind that I can use to play
> > >>with? It would be nice if there is one inside
> > MySQL by default for newbies
> > >>to start out with.
> > >
> > > For our chapters on Connector/ODBC and dotNet,
> > Arthur & I wrote scripts to
> > > create MySQL versions of the NW schema ...
> > >
> >
> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html
> > (InnoDB)
> > > and
> >
> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html#11-2
> > > (MyISAM). If you like, I'll email you the SQL
> > script for populating the
> > > tables.
> > >
> > > PB
> > >
> >
> > I would like to see these scripts as well. Can you
> > please post them
> > somewhere, or mail them to me as well?
> >
> > Dave
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> >
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> 
> 
> Enrique Sanchez Vela
> email: [EMAIL PROTECTED]
> 
> -
> It's often easier to fight for one's || We live in the outer space
> principles than to live up to them || Rev. Kay Greenleaf
> Adlai Stevenson ||
> 
> 
> 
> 
> Start your day with Yahoo! - make it your home page
> http://www.yahoo.com/r/hs
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Power to people, Linux is here.

Re: Database equivalent to NorthWind for M$ SQL

[Enrique Sanchez Vela]

I ran a quick search on google regarding the NothWind
Database or schema, but I did not run across anything
that would explain what it is and what it is used for,
however, it seems to be a pretty useful and known one.

I would appeciate insight on this. 

thanks
esv.

ps. I think it would be more appropiate to reply
privately to me, since this does not seem to be
particulary related to MySQL.

thanks again.

--- "J. David Boyd" <[EMAIL PROTECTED]> wrote:

> Peter Brawley <[EMAIL PROTECTED]> writes:
> 
> > Scott,
> >
> >>Is there any database file similiar to M$'s
> Northwind that I can use to play
> >>with? It would be nice if there is one inside
> MySQL by default for newbies
> >>to start out with.
> >
> > For our chapters on Connector/ODBC and dotNet,
> Arthur & I wrote scripts to
> > create MySQL versions of the NW schema ...
> >
>
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html
> (InnoDB)
> > and
>
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html#11-2
> > (MyISAM).  If you like, I'll email you the SQL
> script for populating the
> > tables.
> >
> > PB
> >
> 
> I would like to see these scripts as well.  Can you
> please post them
> somewhere, or mail them to me as well?
> 
> Dave
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


Enrique Sanchez Vela
email: [EMAIL PROTECTED]
-
It's often easier to fight for one's||We live in the outer space
   
principles than to live up to them||Rev. Kay Greenleaf
Adlai Stevenson||




Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL in CentOS? try before?

[Marcus Bointon]

On 4 Aug 2005, at 08:03, KH wrote:

I just came across Centos (Community Enterprise Operating System -  
http://www.centos.org/) It came with mysql as well. Hence my  
question here, does anyone guru here using this OS before for  
production ? Success story?


Centos is a handy beast. It is a straight copy of RedHat Enterprise  
Linux with all references to RedHat removed. RH have not been very  
happy about it, but they have to live with it as Linux is GPL (though  
their own copyright materials are not, hence the need for their  
removal). If you don't want or need RH's support (e.g. RHN) for your  
server (e.g. for development rather than production servers), Centos  
is a cheaper route to get almost exactly the same thing. Centos  
provide what amounts to their own version of RHN, so you're not left  
entirely to your own devices.


Marcus
--
Marcus Bointon
Synchromedia Limited: Putting you in the picture
[EMAIL PROTECTED] | http://www.synchromedia.co.uk


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problems after upgrading form 4.1.10a to 4.1.13 on RHEL4 - solved

[Marcus Bointon]

On 3 Aug 2005, at 13:01, Gleb Paharenko wrote:


I've just successfully upgraded from 4.1.9 to 4.1.13 (all are
standard rpms for x86) on Fedora 3. Check that you able to start
MySQL with mysqld_safe (for example /usr/bin/mysqld_safe -- 
user=mysql&).

See:
  http://dev.mysql.com/doc/mysql/en/starting-server.html


It wasn't the startup script that was the problem, but your  
suggestion did reveal it; it turns out that the rpm (I can't think of  
anything else that had the opportunity and I certainly didn't do  
something so silly) had deleted the mysql user and group, so it  
couldn't start up as the mysql user. I manually recreated the user  
and group using the original IDs (both 27 in this case), and I was  
then able to launch the server successfully.


Marcus
--
Marcus Bointon
Synchromedia Limited: Putting you in the picture
[EMAIL PROTECTED] | http://www.synchromedia.co.uk


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: sudden mysqldump errors

[Gleb Paharenko]

Hello.



Does your problem still exist? Are you able to perform the dump

by hand with mysqldump? Is it possible that your table has grown

so the default value of max_allowed_packet is too small. What version

of MySQL do you have. See:

  http://dev.mysql.com/doc/mysql/en/communication-errors.html

http://dev.mysql.com/doc/mysql/en/gone-away.html

http://dev.mysql.com/doc/mysql/en/packet-too-large.html





Tom Cunningham <[EMAIL PROTECTED]> wrote:

> Hi list,

> 

> our overnight backup scripts have suddenly been getting a lot of

> mysqldump errors like:

> 

> mysqldump: Error 2013: Lost connection to MySQL server during query

> when dumping table `cs_email_things` at row: 512234

> 

> I've checked the obvious things, and no luck:

> * disk space is OK

> * nothing in the mysqld log

> * nothing in /var/log/messages/

> * CHECK TABLES doesn't complain about the tables

> 

> In fact, now that I think of it, it's happened on two different

> servers; though mysqldump is always running on the same machine.

> 

> Any ideas?

> 

> Thanks.

> 

> Tom.

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

sudden mysqldump errors

[Tom Cunningham]

Hi list,

our overnight backup scripts have suddenly been getting a lot of
mysqldump errors like:

mysqldump: Error 2013: Lost connection to MySQL server during query
when dumping table `cs_email_things` at row: 512234

I've checked the obvious things, and no luck:
 * disk space is OK
 * nothing in the mysqld log
 * nothing in /var/log/messages/
 * CHECK TABLES doesn't complain about the tables

In fact, now that I think of it, it's happened on two different
servers; though mysqldump is always running on the same machine.

Any ideas?

Thanks.

Tom.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Possible? Urgent please.

[xtcsuk]

Using OpenRowSet Transact SQL, I want to view/obtain data from MySql
using this function in "MS SQL Query Analyzer".  Can I do this at all?
 I have MyODBC driver and the relevant OLE DB provider to connect to
MySQL db, I can connect to MySQL db, but having problem with the last
parameter (table name) in the function.  Query Analyzer reports the
following:

"A four-part name was supplied, but the provider does not expose the
necessary interfaces to use a catalog and/or schema."

SELECT a.*
FROM OPENROWSET('MSDASQL',
   'DRIVER={MySQL ODBC 3.51 Driver};SERVER=LocalHhost;UID=root;PWD=test',
   testdb.table1) AS a

regards

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL 4.1.12-debian + large HEAP table

[Gleb Paharenko]

Hello.





> 1. Why does the HEAP table take so much more physical

> space than the MyISAM table?



There is a formula which allows to count the approximate memory usage

for one row. Multiply an obtained value by number of rows and compare the

results with the size of your table. See:

  http://dev.mysql.com/doc/mysql/en/memory-storage-engine.html





> 2. How can I trace the memory consumption from within

> mysql to know what's causing the huge spikes in

> consumption that eat my entire system memory?





Check if memory frees after dropping your HEAP table. Sometimes

I met the weird behavior with non-official binaries. So, if nothing

helps, try the latest release from http://dev.mysql.com/downloads.







gaga moo <[EMAIL PROTECTED]> wrote:

> Guys,

> 

> I have two questions that I hope someone would be able

> to answer.

> 

> First some backround, I'm running a standard debian

> package of mysql 4.1 (currently  the latest version)

> with no my.cnf tweaks except turning on query caching

> (using the default values from the manual) and

> allowing for large heap tables (up to 500MB).

> 

> I've been running the beast for a while now without

> any issues but recently mysqld started slowly eating

> up memory until it consumes the entire system memory. 

> In the past because I'm using heap tables I've always

> watched mysql's memory consumption very closely and it

> was increasing very slowly as new rows are added to

> the heap table.

> 

> My HEAP table is a mirror of a disk table Both are

> 150K rows in length while MySQL reports the HEAP table

> size is 160MB and MyISAM that is 50MB.

> 

> So basically the questions are.

> 

> 1. Why does the HEAP table take so much more physical

> space than the MyISAM table?

> 

> 2. How can I trace the memory consumption from within

> mysql to know what's causing the huge spikes in

> consumption that eat my entire system memory?

> 

> Usually my MySQL idles comfortably using 300MB

> resident and 300MB virtual memory though when all hell

> breaks loose memory usage surges to 1GB resident and a

> similar figure for virtual effectively eating my RAM

> and killing the system by swapping like mad.

> 

> Any input would be greatly appreciated!

> 

> 

> __

> Do You Yahoo!?

> Tired of spam?  Yahoo! Mail has the best spam protection around 

> http://mail.yahoo.com 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: default collation char

[Jigal van Hemert]

Philippe Poelvoorde wrote:

Enrique Sanchez Vela wrote:

I would like to have MySQL differentiate between 'abc'
and 'ABC' both the server and clients. so far anything
I've done has not worked.


Is altering the column type an option ? If yes, you would change any 
'text' for 'blob' and any 'varchar' for 'varchar binary', as a result 
'ABC' would next be different to 'abc' (but 'i' would also be different 
to  'e').


In many case using the BINARY operator in a query will suffice:
http://dev.mysql.com/doc/mysql/en/charset-binary-op.html

SELECT col1 FROM table1 WHERE BINARY col1 LIKE 'ABC%';
will only find rows with col1 starting with 'ABC', not with 'abc'.

If inserts, etc. need also differentiate between upper and lowercase 
(and differentiate between accented and not-accented characters) 
Philippes suggestion is probably the way to go.


Regards, Jigal.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: default collation char

[Philippe Poelvoorde]

Enrique Sanchez Vela wrote:

Hello Folks,

I would like to have MySQL differentiate between 'abc'
and 'ABC' both the server and clients. so far anything
I've done has not worked.


Is altering the column type an option ? If yes, you would change any 
'text' for 'blob' and any 'varchar' for 'varchar binary', as a result 
'ABC' would next be different to 'abc' (but 'i' would also be different 
to  'e').


--
Philippe Poelvoorde
COS Trading Ltd.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL in CentOS? try before?

[KH]

Hi all,
I just came across Centos (Community Enterprise Operating System - 
http://www.centos.org/) It came with mysql as well. Hence my question 
here, does anyone guru here using this OS before for production ? 
Success story?


Cheers
KH


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]