MySQL benchmarks on the G5: Jaguar vs. Panther

[Jan Pieter Kunst]

Hardware: 1.8 GHz G5 with 1GB RAM

Panther (Mac OS X 10.3) doesn't have the sudden slowness in the 
'insert' test that Jaguar (Mac OS X 10.2) has. Total time for Panther 
is faster than total time for Jaguar (5561.00 secs vs. 6770.00 secs). 
'Insert' is faster on Panther than on Jaguar (3316 secs vs. 5215 
secs), but all other benchmarks are somewhat slower on Panther. I'm a 
bit worried about the 'select', which is about twice as  slow on 
Panther than on Jaguar (1428 secs vs. 743 secs).

Complete benchmark results below, sorry for the possible bandwidth 
waste, but I'm not sure what I could snip, if anything.

JP

Panther benchmarks:
=
Benchmark DBD suite: 2.14
Date of test:2003-11-07  0:12:11
Running tests on:Darwin 7.0.0 Power Macintosh
Arguments:
Comments:
Limits from: mysql,pg,solid
Server version:  MySQL 4.0.15 standard log
Optimization:None
Hardware:
alter-table: Total time: 141 wallclock secs ( 0.22 usr  0.11 sys + 
0.00 cusr  0.00 csys =  0.33 CPU)
ATIS: Total time: 59 wallclock secs (10.59 usr  6.30 sys +  0.00 cusr 
0.00 csys = 16.89 CPU)
big-tables: Total time: 57 wallclock secs ( 7.53 usr 13.50 sys + 
0.00 cusr  0.00 csys = 21.03 CPU)
connect: Total time: 252 wallclock secs (44.31 usr 38.81 sys +  0.00 
cusr  0.00 csys = 83.12 CPU)
create: Total time: 298 wallclock secs ( 8.76 usr  3.06 sys +  0.00 
cusr  0.00 csys = 11.82 CPU)
insert: Total time: 3316 wallclock secs (594.82 usr 261.66 sys + 
0.00 cusr  0.00 csys = 856.48 CPU)
select: Total time: 1428 wallclock secs (67.50 usr 25.18 sys +  0.00 
cusr  0.00 csys = 92.68 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 27 wallclock secs ( 4.19 usr  2.58 sys +  0.00 
cusr  0.00 csys =  6.77 CPU)

All 9 test executed successfully

Totals per operation:
Operation seconds usr sys cpu   tests
alter_table_add  134.000.200.090.29 992
connect   16.006.711.308.01   1
connect+select_1_row  23.007.652.219.86   1
count 38.000.040.020.06 100
count_distinct27.000.390.090.481000
count_distinct_big   124.009.596.03   15.62 120
count_distinct_group 184.001.590.802.391000
count_distinct_group_on_key   31.000.490.100.591000
count_distinct_group_on_key_parts182.001.360.752.111000
count_distinct_key_prefix 21.000.460.090.551000
count_group_on_key_parts  38.001.260.671.931000
count_on_key 383.00   15.733.78   19.51   50100
create+drop  111.003.390.984.37   1
create_MANY_tables46.001.150.361.515000
create_index   3.000.000.000.00   8
create_key+drop  121.003.161.164.32   1
create_table   0.000.010.000.01  31
delete_all_many_keys  59.000.020.020.04   1
delete_big 0.000.000.000.00   1
delete_big_many_keys  59.000.020.020.04 128
delete_key 5.000.470.310.78   1
delete_range  11.000.000.000.00  12
drop_index 3.000.000.000.00   8
drop_table 1.000.010.000.01  28
drop_table_when_MANY_tables   12.000.440.140.585000
insert   188.00   18.93   10.96   29.89  350768
insert_duplicates 33.004.583.027.60  10
insert_key   168.00   12.504.27   16.77  10
insert_many_fields10.000.410.160.572000
insert_select_1_key5.000.000.000.00   1
insert_select_2_keys   6.000.000.000.00   1
min_max   20.000.050.000.05  60
min_max_on_key   169.00   22.065.41   27.47   85000
order_by_big 128.00   24.08   17.32   41.40  10
order_by_big_key  89.00   27.07   19.61   46.68  10
order_by_big_key2 86.00   24.30   18.88   43.18  10
order_by_big_key_desc 94.00   27.52   20.67   48.19  10
order_by_big_key_diff116.00   25.02   16.13   41.15  10
order_by_big_key_prefix   87.00   24.71   18.71   43.42  10
order_by_key2_diff10.002.211.42

Need ur help ..........

[Renuka Prasad]

Hi,

I downloaded Linux semi static binary of 
mySQL,mysqlgui-linux-semi-static-1.7.5.tar.gz. Could you please help me,how to install 
in my system.My OS is Redhat Linux9.0.   

Warm regards,

Renuka Prasad.N

How to generate data sequences simple way?

[Petr]

Hello,

I hope someone can help me with the following problem: I have a table 
containing integer counters associated with particular datetime like this:

CREATE TABLE `counters` (
  ...
  `when` datetime NOT NULL default '-00-00 00:00:00',
  `counter` smallint(5) unsigned NOT NULL default '0',
  ...
);
For some purposes I need to initialize frequently the table for time
sequences with constant time step e.g. since 2003-11-06 8:00 to
2003-11-06 12:00 with time step 15 minutes. It means I need to do
following INSERT commands:
INSERT INTO couters (..., '2003-11-06 8:00', 0, ...);
INSERT INTO couters (..., '2003-11-06 8:15', 0, ...);
INSERT INTO couters (..., '2003-11-06 8:30', 0, ...);
...
INSERT INTO couters (..., '2003-11-06 12:00', 0, ...);
Of course my C code is able to generate such command strings in one
simple loop and send them to the MySQL server. But the code should be
as fast as possible and I prefer to do this task by one INSERT-SELECT
command like this:
INSERT INTO counters SELECT ...

Unfortunately I do not have any idea how to build such SELECT command
that will generate the datetime sequence for me. Does anybody know how
to do it? I would appreciate any hints very much.
Thanks, Petr

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

RE: Need ur help ..........

[Andy Eastham]

Renuka,

Put the file into a location such as /usr/local
Gunzip it:
gunzip mysqlgui-linux-semi-static-1.7.5.tar.gz
Unpack it:
tar -xvf mysqlgui-linux-semi-static-1.7.5.tar
create a symbolic link from mysql to mysqlgui-linux-semi-static-1.7.5
under /usr/local/
ln -s /usr/local/mysqlgui-linux-semi-static-1.7.5 /usr/local/mysql

read the installation instructions in the INSTALL-BINARY file in
/usr/local/mysql

Hope this helps,

Andy

 -Original Message-
 From: Renuka Prasad [mailto:[EMAIL PROTECTED]
 Sent: 07 November 2003 08:00
 To: [EMAIL PROTECTED]
 Subject: Need ur help ..


 Hi,

 I downloaded Linux semi static binary of
 mySQL,mysqlgui-linux-semi-static-1.7.5.tar.gz. Could you please
 help me,how to install in my system.My OS is Redhat Linux9.0.

 Warm regards,

 Renuka Prasad.N



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

Dillema: LIKE vs MATCH - AGAINST

[Ciprian Trofin]

I'm building a financial-like web site (PHP - MySQL). I was requested to
build a search engine for IT.

Everything works OK, w/ the exception of a certain component. The owner of
the web site will write, each day, a report for the customers. I use TEXT
fields to record each section of the report (there are 4 sections).

Now the search part:
1. I can use LIKE %keyword% but when the DB will grow, I guess I will get
into trouble.

2. I can use MATCH - AGAINST, but the report it's quite stereotypical: most
of the words a user will look for are very common (the 50% rule).

Any suggestion ?


-- 
 Cip


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

Re: Dillema: LIKE vs MATCH - AGAINST

[daniel]

Man, if you need a full boolean search, ie more than one word, use
fulltext !, it crunches cpu so you need a beasty machine, use like if its
just one string you are searching for in a varchar field, use fulltext if
you are searching in wads of text.

I highly recommend match against though, its the best thing that has come
our way as yet

dan
(waiting for 4.1)
 I'm building a financial-like web site (PHP - MySQL). I was requested
 to build a search engine for IT.

 Everything works OK, w/ the exception of a certain component. The owner
 of the web site will write, each day, a report for the customers. I use
 TEXT fields to record each section of the report (there are 4
 sections).

 Now the search part:
 1. I can use LIKE %keyword% but when the DB will grow, I guess I will
 get into trouble.

 2. I can use MATCH - AGAINST, but the report it's quite stereotypical:
 most of the words a user will look for are very common (the 50% rule).

 Any suggestion ?


 --
 Cip


 --
 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]

Cronjob / rights problem.

[Anders Norrbring]

I've set up a new MySQL server on a box with multiple IP addresses, and the
SQL server only binds to ONE of these addresses.

Also, I've been changing some user rights in the SQL setup, and now I get a
cronjob error, related to user rights...  My big problem is that I don't
even know where I should start looking for it.

The system and MySQL setup is in all other aspects the distributed versions
from the SuSE Linux Standard Server 8 distribution, based on United Linux
1.0.

The cronjob error mailed to me is the following:


SCRIPT: clean_catman, OK.
SCRIPT: clean_core, OK.
SCRIPT: do_mandb, OK.
SCRIPT: logrotate exited with RETURNCODE = 1.
SCRIPT: ouput (stdout  stderr) follows

 /usr/bin/mysqladmin: refresh failed; error: 'Access denied for user:
'@localhost' (Using password: NO)'
error running postrotate script
Reload syslog service..done
SCRIPT: logrotate
--- END OF OUTPUT


SCRIPT: slots, OK.
SCRIPT: ouput (stdout  stderr) follows

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket /tmp/.s.PGSQL.5432?
SCRIPT: slots
--- END OF OUTPUT


Can somebody please help me out here?  If the /usr/bin/mysqladmin tries to
use a user from localhost (any) then it fails, because localhost doesn't
have access to the SQL server...  What user should be granted access (and
from where) to make this job work correctly?

Anders Norrbring

Norrbring Consulting
Halmvägen 42
SE-691 48  Karlskoga
SWEDEN


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

Re: How to generate data sequences simple way?

[Martijn Tonies]

Hi Petr,

 I hope someone can help me with the following problem: I have a table
 containing integer counters associated with particular datetime like this:

 CREATE TABLE `counters` (
...
`when` datetime NOT NULL default '-00-00 00:00:00',
`counter` smallint(5) unsigned NOT NULL default '0',
...
 );

 For some purposes I need to initialize frequently the table for time
 sequences with constant time step e.g. since 2003-11-06 8:00 to
 2003-11-06 12:00 with time step 15 minutes. It means I need to do
 following INSERT commands:

 INSERT INTO couters (..., '2003-11-06 8:00', 0, ...);
 INSERT INTO couters (..., '2003-11-06 8:15', 0, ...);
 INSERT INTO couters (..., '2003-11-06 8:30', 0, ...);
 ...
 INSERT INTO couters (..., '2003-11-06 12:00', 0, ...);

 Of course my C code is able to generate such command strings in one
 simple loop and send them to the MySQL server. But the code should be
 as fast as possible and I prefer to do this task by one INSERT-SELECT
 command like this:

 INSERT INTO counters SELECT ...

As fast as possible? How many rows do you need to create then? Is
this a process that needs to be done several times?

'Cause I can do this kind of stuff very easily with my Test Data Generator
tool that comes with Database Workbench.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird  MS SQL Server.
Upscene Productions
http://www.upscene.com


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

How can I become an Expert MySql User?

[zzapper]

HiY'll

I've been using MYSQL for a couple of years now. And can write some
pretty complex WHERE statements. I've become aware that's really only
tip of the iceberg stuff.

Can readers suggest a gentle path to moving onto JOINS and a more
fundamental understanding of DBs

No rude answers please g

zzapper
--

vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?

http://www.vim.org/tips/tip.php?tip_id=305  Best of Vim Tips


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

Can't set myisam_repair_threads

[Eric Jain]

From http://www.mysql.com/doc/en/News-4.0.13.html: Added multi-threaded
MyISAM repair optimisation and myisam_repair_threads variable to enable
it.

The variable is also documented in
http://www.mysql.com/doc/en/SHOW_VARIABLES.html: If this value is
greater than one, MyISAM table indexes during Repair by sorting process
will be created in parallel - each index in its own thread.

Unfortunately this variable is neither listed by SHOW VARIABLES, nor can
I set it:

  mysql set myisam_repair_threads=2;
  ERROR 1193: Unknown system variable 'myisam_repair_threads'

I'm using 4.1.0-alpha-standard.

Am I doing something wrong, or was this variable dropped? If so, perhaps
the documentation should be updated...

--
Eric Jain


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

myisam_max_extra_sort_file_size

[Eric Jain]

From the documentation: If the temporary file used for fast index
creation would be bigger than using the key cache by the amount
specified here, then prefer the key cache method.

Did I understand correctly that if I always want fast index creation
whenever myisam_max_sort_file_size is set large enough, this variable
must be set to 0?

--
Eric Jain


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

myisam_max_sort_file_size not set

[Eric Jain]

In my.cnf:

[mysqld]
key_buffer_size=64M
myisam_sort_buffer_size=512MB
myisam_max_sort_file_size=100
myisam_max_extra_sort_file_size=0
bulk_insert_buffer_size=64MB
...

All variables are set as expected, except myisam_max_sort_file_size,
which is set to 4GB. Only if I choose a value smaller than 4GB, or
manually execute

  set global myisam_max_sort_file_size=100;

does the variable get updated. Is there any reasoning behind this
behavior, or is this simply a bug?

--
Eric Jain


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

RE: Issues with Mysql 4.0 and PHP in a production environment

[Luis Lebron]

Anything that breaks a script (unsupported functions, changes, etc.) or that
causes performance problems.

thanks,

Luis

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 06, 2003 4:09 PM
To: Luis Lebron
Cc: Mysql (E-mail); Php-General (E-mail)
Subject: Re: Issues with Mysql 4.0 and PHP in a production environment


On Thu, Nov 06, 2003 at 02:10:27PM -0600, Luis Lebron wrote:
 Are there any issues with running PHP 4.3.X and Mysql 4.0 in a production
 environment?

What sort of issues are you looking for?

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,012,977,736 queries
(432/sec. avg)

Re: Benchmark differences: Mac OS X - Linux

[David Steinbrunner]

The Mac was HFS+ journaled. Disk: the stock Apple-supplied one. The
Linux machine was a default SuSE 8.0 installation. ext2 as the
filesystem? No idea about journaling. No SCSI or RAID, just an
internal IDE disk. Both machines are really consumer-level machines,
no heavy-duty server hardware. That's about all I can tell you, I
really don't know that much about the details of the hardware. Sorry,
not much help here, I know.
Hmm.  The Mac had journaling but the Linux box did not (ext2 is not
journaled).  That could have influenced things too.  I know virtually
nothing about HFS+ journaling.
You can turn off journaling in 10.3 in Disk Utility by going to File 
and selecting  Disable Journaling.  This page has some visuals:
http://diveintoosx.org/panther/disk_management.html

There is a Terminal command that does the same thing that I don't know 
off the top of my head.

--
David Steinbrunner
MFM Communication Software, Inc.
http://www.mfm.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: remote connection

[John Almberg]

Yes I was already pretty sure it was a rights issue. What I'm trying
to do is modify the rights for a particular user so I can log in
remotely. The question is what I should use for the host fields in the
various tables so that I can log in remotely *and* the web server can
log in through localhost.

Problem is, everything I try blocks access from everywhere, even when I
use '%'. I did some more reading last night and figured out that it has
something to do with the sorting done in the first connection phase. I
think just changing the host field to '%' or '%.optonline.net' actually
lowers that user account below one of the default accounts (one with a
blank in the User field), so that I'm logging in on the other account. 

I'm sure there's a logic behind this set up, so I'm not changing it
haphazardly, but the logic escapes me at the moment!

-- John


Identry, LLC
Northport, NY 11768

Ph: 631.754.8440
Fx: 631.980.4262
Em: [EMAIL PROTECTED]

Member: ASDA, APS, ANA, Ephemera Society
of America, Long Island Web Developers Guild


   Visit us at: www.identry.com
   Building You A Better Online Business




 

-Original Message-
From: Curtis Maurand [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 07, 2003 1:44 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: remote connection


Its a permissions thing on the server end.  You have to have set up a
user
account on the server.  Very important are hostnames.

Curtis

John Almberg said:
 I'm trying to set up a remote connection to a mysql database using
Perl
 DBI. I have done this without problem on another database. I suspect
the
 rights are set up differently on this one. I have root access.

 When I try to connect, I get the following error (hostname, username,
 password have been edited for the purpose of this email):

 DBI connect('database:host','user','password') failed: Host
 'ool-182f1a5f.dyn.optonline.net' is not allowed to connect to this
MySQL
 server at D:\My Documents\Identry Shared\Operations\Stamp Center --
 website\database\update_inv.pl line 7

 After reading section 4.2.8 in the manual (Connecting to the MySQL
 Server), I am pretty sure this is a rights problem. The user I am
trying
 to connect as has 'localhost' set in the Host field of both the 'user'
 and 'db' tables. There are no entries whatsoever in the 'host' table.

 What I want to do is to add the ability for my Perl program to connect
 as a certain user (call him 'bob') from a certain IP address.
 Unfortunately, all my attempts result block access from every host,
even
 localhost. Thus, even the webserver can't access the database.

 In desperation, I've even tried using '%' in the Host fields of both
 user  db tables. This doesn't work, either, so I'm obviously missing
 something.

 Any help much appreciated!

 Brgds: John

 
 Identry, LLC
 Northport, NY 11768

 Ph: 631.754.8440
 Fx: 631.980.4262
 Em: [EMAIL PROTECTED]

 Member: ASDA, APS, ANA, Ephemera Society
 of America, Long Island Web Developers Guild

 
Visit us at: www.identry.com
Building You A Better Online Business
 








 --
 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]

Re: MySQL benchmarks on the G5: Jaguar vs. Panther

[Brent Baisley]

Thanks for posting the comparison, it's something I've been meaning to 
do myself.

I was wondering if you had journaling turned on in Panther and not in 
Jaguar? I'm not sure if it would have any effect on selects, I would 
think it would slow down inserts not selects. You notice the CPU 
breakdown is different.

On Friday, November 7, 2003, at 03:06 AM, Jan Pieter Kunst wrote:

Hardware: 1.8 GHz G5 with 1GB RAM

Panther (Mac OS X 10.3) doesn't have the sudden slowness in the 
'insert' test that Jaguar (Mac OS X 10.2) has. Total time for Panther 
is faster than total time for Jaguar (5561.00 secs vs. 6770.00 secs). 
'Insert' is faster on Panther than on Jaguar (3316 secs vs. 5215 
secs), but all other benchmarks are somewhat slower on Panther. I'm a 
bit worried about the 'select', which is about twice as  slow on 
Panther than on Jaguar (1428 secs vs. 743 secs).

Complete benchmark results below, sorry for the possible bandwidth 
waste, but I'm not sure what I could snip, if anything.

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Max connection

[Kittiphum Worachat]

Hi.

When MySQL reach max connection can root use mysql client program to login 
and look for some process that can be kill to release connection.

I had ever seen from some web said that some user with PROCESS_ACL 
privileges can do (can login MySQL reserved 1 connection for user who has 
PROCESS_ACL privileges) if yes is root have PROCESS_ACL privileges and how 
to use to login with mysql client program or if root has not this 
privileges be default how to grant this for root or how to make a login ?

Thanks
Kittiphum Worachat,MT.
 

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

Secure way of storing passwords in the database

[Luis Lebron]

Any suggestions of a secure way of storing membership passwords (for a
website) in a mysql database? Should I use sha, aes, des???

thanks,



Luis R. Lebron
Sigmatech, Inc

Re: MySQL benchmarks on the G5: Jaguar vs. Panther

[Jan Pieter Kunst]

I was wondering if you had journaling turned on in Panther and not 
in Jaguar? I'm not sure if it would have any effect on selects, I 
would think it would slow down inserts not selects. You notice the 
CPU breakdown is different.
Journaling was turned on in both cases.

JP

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

SQL [Q] how to migrate 4.0 - 4.1

[Andrey Kotrekhov]

SQL
mysql

HI, All!
I have a question.
I migrate from 4.0.14 to 4.1.0 but:
the 4.0.14 show me:
mysql desc statname;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| statname | char(16) binary |  | PRI | unknown |   |
| passwd   | char(64) binary |  | | *   |   |
| env  | char(10)|  | | |   |
| area | int(11) |  | | 0   |   |
| manager  | char(16) binary |  | | |   |
| d_create | datetime|  | | -00-00 00:00:00 |   |
| d_change | datetime|  | | -00-00 00:00:00 |   |
| wizard   | int(11) |  | | 0   |   |
+--+-+--+-+-+---+
 And 4.1.0 show me on the sane table:
mysql mysql desc statname;
+--+--+--+--+-+-+---+
| Field| Type | Collation| Null | Key
| Default | Extra |
+--+--+--+--+-+-+---+
| statname | char(16) character set koi8r | koi8r_general_ci |  | PRI
| unknown |   |
| passwd   | char(64) character set koi8r | koi8r_general_ci |  |
| *   |   |
| env  | char(10) character set koi8r | koi8r_general_ci |  |
| |   |
| area | int(11)  | binary   |  |
| 0   |   |
| manager  | char(16) character set koi8r | koi8r_general_ci |  |
| |   |
| d_create | datetime | koi8r_general_ci |  |
| -00-00 00:00:00 |   |
| d_change | datetime | koi8r_general_ci |  |
| -00-00 00:00:00 |   |
| wizard   | int(11)  | binary   |  |
| 0   |   |
+--+--+--+--+-+-+---+

But where is my binary flags?
Select use fields as binary.
But I don't want binary on each field.
I need old types on fields.

How can I resolv this?

Best regards.
___
Andrey Kotrekhov [EMAIL PROTECTED]
ISP Alkar Teleport
. +380 562 34-00-44

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

Re: Secure way of storing passwords in the database

[Henning Heil]

 Luis Lebron wrote on 07.11.2003 15:22 

Any suggestions of a secure way of storing membership passwords (for a
website) in a mysql database? Should I use sha, aes, des???
thanks,
 

Hi Luis,

try md5 encryption for passwords in your application and write 
md5-encrypted values to simple varchar/text fields in your db. you can 
find md5() in the php manual, there are also some examples in the user 
notes which can also be applied with other encryption methods (as I 
don't know whether you are using php).

Cherio,

Henning

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

Can't force Repair by sort

[Eric Jain]

Is there any way to force a Repair by sort rather than a (way to slow)
Repair with keycache when doing an ALTER TABLE t ENABLE KEYS?

The table in question is 6GB large and contains 100M rows. I have set

  myisam_sort_buffer_size=512MB
  myisam_max_sort_file_size=1MB
  myisam_max_extra_sort_file_size=0
  bulk_insert_buffer_size=64MB

but this doesn't help.

I am running somewhat low on diskspace with only a few gigabytes free,
could this have any influence here? Any other ideas?

--
Eric Jain


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

RE: remote connection

[Curtis Maurand]

For some reason, MySQL treats localhost as a special case (at least that
seems to be the behavior.  If you run grant list of rights on
database.* to user@'%' identified by 'password';  will allow login from
anywhere but the localhost.  That account must be created seperately.

Curtis

John Almberg said:
 Yes I was already pretty sure it was a rights issue. What I'm trying
 to do is modify the rights for a particular user so I can log in
 remotely. The question is what I should use for the host fields in the
 various tables so that I can log in remotely *and* the web server can
 log in through localhost.

 Problem is, everything I try blocks access from everywhere, even when I
 use '%'. I did some more reading last night and figured out that it has
 something to do with the sorting done in the first connection phase. I
 think just changing the host field to '%' or '%.optonline.net' actually
 lowers that user account below one of the default accounts (one with a
 blank in the User field), so that I'm logging in on the other account.

 I'm sure there's a logic behind this set up, so I'm not changing it
 haphazardly, but the logic escapes me at the moment!

 -- John

 
 Identry, LLC
 Northport, NY 11768

 Ph: 631.754.8440
 Fx: 631.980.4262
 Em: [EMAIL PROTECTED]

 Member: ASDA, APS, ANA, Ephemera Society
 of America, Long Island Web Developers Guild

 
Visit us at: www.identry.com
Building You A Better Online Business
 





 -Original Message-
 From: Curtis Maurand [mailto:[EMAIL PROTECTED]
 Sent: Friday, November 07, 2003 1:44 AM
 To: [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED]
 Subject: Re: remote connection


 Its a permissions thing on the server end.  You have to have set up a
 user
 account on the server.  Very important are hostnames.

 Curtis

 John Almberg said:
 I'm trying to set up a remote connection to a mysql database using
 Perl
 DBI. I have done this without problem on another database. I suspect
 the
 rights are set up differently on this one. I have root access.

 When I try to connect, I get the following error (hostname, username,
 password have been edited for the purpose of this email):

 DBI connect('database:host','user','password') failed: Host
 'ool-182f1a5f.dyn.optonline.net' is not allowed to connect to this
 MySQL
 server at D:\My Documents\Identry Shared\Operations\Stamp Center --
 website\database\update_inv.pl line 7

 After reading section 4.2.8 in the manual (Connecting to the MySQL
 Server), I am pretty sure this is a rights problem. The user I am
 trying
 to connect as has 'localhost' set in the Host field of both the 'user'
 and 'db' tables. There are no entries whatsoever in the 'host' table.

 What I want to do is to add the ability for my Perl program to connect
 as a certain user (call him 'bob') from a certain IP address.
 Unfortunately, all my attempts result block access from every host,
 even
 localhost. Thus, even the webserver can't access the database.

 In desperation, I've even tried using '%' in the Host fields of both
 user  db tables. This doesn't work, either, so I'm obviously missing
 something.

 Any help much appreciated!

 Brgds: John

 
 Identry, LLC
 Northport, NY 11768

 Ph: 631.754.8440
 Fx: 631.980.4262
 Em: [EMAIL PROTECTED]

 Member: ASDA, APS, ANA, Ephemera Society
 of America, Long Island Web Developers Guild

 
Visit us at: www.identry.com
Building You A Better Online Business
 








 --
 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]




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

Difference between 'mysql_real_query' and 'mysql_send_query'

[Pascal Francq]

Hi all,

I'm using mysql 4.0.11a and I meet a probleme with mysql_real_query().
It retruns a commands out of sync , but my queries are well-ordered.
I found another query command called mysql_send_query().
If I use it, the queries success. But I can't find the difference between 
mysql_send_query and mysql_real_query in MySQL documentation.

Does anyone know something about it?

thanks for help !
-- 


Prof. Pascal Francq
Université Libre de Bruxelles
CAD/CAM Department
Avenue F.D. Roosevelt, 50
CP 165/14
B-1050 Brussels
BELGIUM
Tel. +32-2-650 47 65
Fax +32-2-650 47 24


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

Loosing é characters in DB insertion

[Yves Arsenault]

Hello,

I just took over a site from a client, he used an Access DB, the Database
contains mostly french text...

I've created a dump file to transfer the DB to MySQL, but when I execute the
code, the é characters are lost.

They're being created as VARCHAR..

I'm not a very experienced MySQL user. Is there anything I should look for
in the dump file?


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

Re: How can I become an Expert MySql User?

[mos]

At 06:29 AM 11/7/2003, you wrote:
HiY'll

I've been using MYSQL for a couple of years now. And can write some
pretty complex WHERE statements. I've become aware that's really only
tip of the iceberg stuff.
Can readers suggest a gentle path to moving onto JOINS and a more
fundamental understanding of DBs
No rude answers please g

zzapper
--
Zzapper,
Buy, beg, borrow, or steal Paul Dubois books MySql 2nd Edition 
and MySQL Cookbook. You will learn a lot from them. Plus I'd recommend 
reading the MySQL manual from cover to cover, and try your own examples in 
each section.

Mike

In another thread I had posted these SQL related links:

Try one of these MySQL tutorials:
http://www.mysql.com/doc/en/Tutorial.html
http://www.analysisandsolutions.com/code/mybasic.htm
http://www.devshed.com/Server_Side/MySQL
http://www.sqlcourse.com/
http://www.w3schools.com/sql/default.asp
http://www.juicystudio.com/tutorial/mysql/
http://www.justphukit.com/mysql/mysql-tutorials-1.php
http://sqlzoo.net/
http://www.troobloo.com/tech/mysql.shtml
http://perl.about.com/cs/mysql/index.htm
http://www.anu.edu.au/web/authors/mysql/manual_Tutorial.html
Reference:
http://www.mysql.com/doc/en/  (the MySQL manual is quite good too)
http://tiger.la.asu.edu/Quick_Ref/MySQL_QuickRef.pdf  (MySQL Quick 
Reference Card)

Related links
http://dmoz.org/Computers/Software/Databases/MySQL/Tutorials/
Books:
MySQL 2nd Edition by Paul Dubois
MySQL Cookbook by Paul Dubois


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

Need help comparing MySQL to MS SQL Server

[KEVIN ZEMBOWER]

I'm a system administrator for a small (200 people) branch of a large 
university/medical school. I've worked with MySQL and use it as my database of choice 
for web-based dynamic content. I would not consider myself an experienced, 
professionally-trained, knowledgeable database administrator, more of a database user 
who's had to administer his own database systems because no one else's around.

My organization is trying to decide on an SQL engine for general purpose database work 
within our organization. The one professional database administrator we have works 
mainly in MS Access, but is looking forward to building on her beginner-level 
understanding of SQL and becoming an SQL administrator. Right now, the largest 
database in our organization is a flat-file structure with less than 500,000 records 
in it, which could conceivably grow ten-fold in the next five years. The organization 
hired an outside consultant to evaluate which SQL engine to go with. This is what he 
sent to us:
===
MySQL is an open-source database management system (DBMS). It
uses client/server architecture and is a multi-threaded,
multi-user database server. MySQL was designed for speed;
therefore, it does not provide many of the features provided
by relational database systems, such as sub-queries, foreign
keys, referential integrity, stored procedures, triggers, and
views. In addition, it contains a locking mechanism that is
not adequate for tables containing many write actions
occurring simultaneously from different users. It is also
lacking in reference to support for software applications and
tools.

SQL Server 2000 is a complete Relational Database Management
System (RDBMS) that also includes integrated analysis
functionality for OLAP and data mining. SQL Server 2000 meets
the data and analysis storage requirements of the largest
data processing systems and commercial Web sites, yet at the
same time can provide easy-to-use data storage services to an
individual or small business.

The architecture of Microsoft SQL Server supports advanced
server features, such as row-level locking, advanced query
optimization, data replication, distributed database
management, and Analysis Services. Transact-SQL (T-SQL) is
the SQL dialect supported by SQL Server 2000.
===
I don't know whether the consultant wrote this himself, or if it came from somewhere. 
It could be Microsoft advertizement, for all I know. Most of the terms aren't familiar 
to me, like sub-queries or referential integrity. I feel out of my depth 
evaluating this comparison.

My questions are:
1. Is this a fair comparison of MySQL and MS SQL Server 2000?
2. Is this up to date with the current status of MySQL?
3. Would the deficiencies pointed out in MySQL, if true, apply to the type of work we 
envision? Granted, I haven't given you all much information about what we hope to do 
with an SQL engine, but I don't think it will be very sophisticated.

Thank you for all your thoughts and comments.

-Kevin Zembower

-
E. Kevin Zembower
Unix Administrator
Johns Hopkins University/Center for Communications Programs
111 Market Place, Suite 310
Baltimore, MD  21202
410-659-6139


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

Re: aes encryption bug

[Herb Rubin]

Paul,

TINYBLOB does seem to hold the value properly.

I can't use a TINYBLOB in a primary key. Is this not allowed?
The ALTER statement complains that I am using a column without a length
specified.

Herb


On Thu, 2003-11-06 at 19:24, Paul DuBois wrote:
 At 15:29 -0800 11/6/03, Herb Rubin wrote:
 Paul,
 
 I did try VARCHAR(16) BINARY and it still failed to INSERT in a NOT NULL
 column.
 
 The encrypted string seems to be equivalent to the NULL value even though
 it visually looks like some kind of data.
 
 Herb
 
 Okay, I investigated this further and I believe I know the cause of the
 problem.  The solution is to use a TINYBLOB NOT NULL rather than
 a VARCHAR(16) NOT NULL.
 
 Here's what's going on:
 
 - The particular encrypted value you calculate ends with 0x20.  That is,
the same value as a space character.
 - Trailing spaces are trimmed from values stored in VARCHAR columns.
 - That means when you retrieve the value, it's 15 bytes long, and is no
longer a legal encrypted value.
 - AES_DECRYPT() returns NULL for illegal encrypted values.
 
 Can you instead use CHAR(16) instead of VARCHAR(16)? No, because
 the trailing space would still be trimmed *when the value is retrieved*,
 and you'd still get NULL from AES_DECRYPT().
 
 Use a TINYBLOB instead.  Trailing spaces won't be trimmed when the value
 is stored, or when it is retrieved.  AES_DECRYPT() will work.
 
 In general, you shouldn't try to use CHAR/VARCHAR for encrypted values
 or other forms of binary data. Use BLOB columns instead.
 
 
 
   Please reply to the list, not to me directly, so that others can
   follow this discussion.  Thanks.
 
   At 14:54 -0800 11/6/03, Herb Rubin wrote:
 Paul,
 
 Yes, I get the same, now try and decrypt it, it will turn out to be
 NULL. So, you cannot insert this into a NOT NULL column. It will reject
 it.
 
   Your message (below) appears to be reporting a problem with
   AES_ENCRYPT(). It states that you get a NULL return value from that
   function.
 
   You now appear to be saying something else.
 
   I don't understand what problem you're trying to report.
 
 
   My result from decryption:
 
   mysql select AES_ENCRYPT('551850040',
   '0bf251c9aaf007deaf1143ca1492b561');
   +--+
   | AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561') |
   +--+
   | k\Vx  |
   +--+
   1 row in set (0.00 sec)
 
   mysql select
   AES_DECRYPT(AES_ENCRYPT('551850040',
   '0bf251c9aaf007deaf1143ca1492b561'),
- '0bf251c9aaf007deaf1143ca1492b561');
   +-
 --+
   | AES_DECRYPT(AES_ENCRYPT('551850040',
   '0bf251c9aaf007deaf1143ca1492b561'),
   '0bf251c9aaf007deaf1143ca1492b561') |
   +-
 --+
   | 551850040
   |
   +-
 --+
   1 row in set (0.07 sec)
 
   Are you reporting that you get NULL only in the context of inserting
   and retrieving the value from the id column?
 
   If so, please try using TINYBLOB rather than VARCHAR and see what
   happens.
 
 
 
 we are on 4.0.14
 Herb
 
 
At 14:03 -0800 11/6/03, Herb Rubin wrote:
 Hi,
 
 I am trying to use aes_encrypt and I get a NULL value with a specific
 string:
 
 INSERT INTO test SET
 `id` = AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561');
 
 my field 'id' is VARCHAR(16) NOT NULL
 
 If I change the value or the encryption string it works. But this
 combination turns out to be null and it refuses to insert.
 
With MySQL 4.0.14, 4.0.16, and 4.1.1, I get:
 
mysql select AES_ENCRYPT('551850040',
'0bf251c9aaf007deaf1143ca1492b561');
+--+ |
AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561') |
+--+ |
 k\Vx  |
+--+
 
What version are you using?
 
 
 
 Help!
 
 Herb
 
 
 -- 
 Paul DuBois, Senior Technical Writer
 Madison, Wisconsin, USA
 MySQL AB, www.mysql.com
 
 Are you MySQL certified?  http://www.mysql.com/certification/
 
-- 
Herb Rubin Pathfinders Software
[EMAIL PROTECTED] http://www.pfinders.com
phone: 650-692-9220fax:   650-692-9250


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

MySQL query question

[Chris A. Mattingly]

I've searched around on the lists archives and even did some googling, but I'm 
having trouble finding the answer to this question.

Given that I have a table with 2 columns (say col1, col2) I want to be able to 
search for the value of col1 in the value of col2.  Let's say that in one 
instance col1 = foo and col2 = foobar, I want to know, but if col1 
= temp and col2 = foobar, I do not want anything returned.

A query something like:  SELECT col2 FROM table WHERE col2 LIKE '%col1%';

Any help would be greatly appreciated.

-Chris


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

HELP is missing

[Andy Thiel]

Hi

I'm attempting to use your mysql control center v. 0.9.3 beta. I want to
load information into a database from a file, but am having problems.  Why
don't you have any HELP contents?

Thanks,
Andy


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

Very strange problem

[Eric Anderson]

A little explaination:

We've got two servers, e450 (3.23.47) and 1U (4.0.14).  These two
machines have a private connection between them (192.168.0.1 and
192.168.0.2).

The e450 is the master server, and the 1U has a copy (no replication)
of some basic data, so it can run server-intensive pages.  However, it
needs to connect back to the e450 to insert some data.

Everything runs fine until the e450 does something that blocks other
threads (including the threads that are connecting from the 1U) on the
table 'vip_t'.

Let's say for example that I run a lengthy query on table 'vip_t' which
causes all the other threads to be blocked while that query runs.

The threads connecting from the 1U (192.168.0.2) all start to block up
as expected, but when the length query on the e450 stops running, the
threads from the 1U stay blocked, and all NEW threads end up in a
limbo state:

| 726136 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726135 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726134 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726138 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726140 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726142 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726143 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726148 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726149 | nobody   | localhost   | bm   | Sleep  | 11   | 
 | NULL  |
| 726150 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726153 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726158 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726161 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726164 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726170 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726172 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726173 | nobody   | localhost   | bm   | Sleep  | 4| 
 | NULL  |
| 726181 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726185 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726191 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |
| 726201 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | 
login| NULL  |

No connections from 1U (192.168.0.2) are possible - they just hang, even
the interactive client just hangs.

In order to clear things, I have to shutdown both httpd and mysql on
both machines and restart them.  And on the e450, mysql will not shut
down completely.  'mysqladmin shutdown' just hangs (ostensibly waiting
for the above threads to clear).  I end up flushing everything and
killing both mysql and the starting script.

I'm sure the solution is to upgrade the e450's version of mysql first
because that seems to be where the problem is, but I hate to do it at
gunpoint.  I'm wondering if anyone else has heard/seen any of these
problems?

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

Re: Need help comparing MySQL to MS SQL Server

[Martijn Tonies]

Hi Kevin,


 I'm a system administrator for a small (200 people) branch of a large
university/medical school. I've worked with MySQL and use it as my database
of choice for web-based dynamic content. I would not consider myself an
experienced, professionally-trained, knowledgeable database administrator,
more of a database user who's had to administer his own database systems
because no one else's around.

 My organization is trying to decide on an SQL engine for general purpose
database work within our organization. The one professional database
administrator we have works mainly in MS Access, but is looking forward to
building on her beginner-level understanding of SQL and becoming an SQL
administrator. Right now, the largest database in our organization is a
flat-file structure with less than 500,000 records in it, which could
conceivably grow ten-fold in the next five years. The organization hired an
outside consultant to evaluate which SQL engine to go with. This is what he
sent to us:
 ===
 MySQL is an open-source database management system (DBMS). It
 uses client/server architecture and is a multi-threaded,
 multi-user database server. MySQL was designed for speed;
 therefore, it does not provide many of the features provided
 by relational database systems, such as sub-queries, foreign
 keys, referential integrity, stored procedures, triggers, and
 views. In addition, it contains a locking mechanism that is
 not adequate for tables containing many write actions
 occurring simultaneously from different users. It is also
 lacking in reference to support for software applications and
 tools.

 SQL Server 2000 is a complete Relational Database Management
 System (RDBMS) that also includes integrated analysis
 functionality for OLAP and data mining. SQL Server 2000 meets
 the data and analysis storage requirements of the largest
 data processing systems and commercial Web sites, yet at the
 same time can provide easy-to-use data storage services to an
 individual or small business.

 The architecture of Microsoft SQL Server supports advanced
 server features, such as row-level locking, advanced query
 optimization, data replication, distributed database
 management, and Analysis Services. Transact-SQL (T-SQL) is
 the SQL dialect supported by SQL Server 2000.
 ===
 I don't know whether the consultant wrote this himself, or if it came from
somewhere. It could be Microsoft advertizement, for all I know. Most of the
terms aren't familiar to me, like sub-queries or referential integrity.
I feel out of my depth evaluating this comparison.


Referential integrity is supported for InnoDB type tables - with MySQL,
each table can
have a different type, each table handler (in the MySQL system) can handle
different
features. With InnoDB, there's Referential integrity, transaction support
and and also
a different locking  mechanism - which is more suited for lots of readers
and concurrent
writers.

 My questions are:
 1. Is this a fair comparison of MySQL and MS SQL Server 2000?

Fairly fair :-)

 2. Is this up to date with the current status of MySQL?

Triggers, views and Stored Procedures are expected to be included in MySQL
5.

The next version of MSSQL will include a new locktype/transaction isolation,
one which works pretty much the same as InnoDB - versioned locking.

 3. Would the deficiencies pointed out in MySQL, if true, apply to the type
of work we envision? Granted, I haven't given you all much information about
what we hope to do with an SQL engine, but I don't think it will be very
sophisticated.


If it's not very sophisticated, MySQL will do just fine. In my opinion.
There are
other free and open source DBMSes as well, that do have procedures,
triggers,
views, subqueries and the like.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird  MS SQL Server.
Upscene Productions
http://www.upscene.com


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

RE: Loosing é characters in DB insertion

[Victor Pendleton]

Are the é being replaced by another character?

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 10:05 AM
To: 1Mysql
Subject: Loosing é characters in DB insertion



Hello,

I just took over a site from a client, he used an Access DB, the Database
contains mostly french text...

I've created a dump file to transfer the DB to MySQL, but when I execute the
code, the é characters are lost.

They're being created as VARCHAR..

I'm not a very experienced MySQL user. Is there anything I should look for
in the dump file?


-- 
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]

Re: Need help comparing MySQL to MS SQL Server

[KEVIN ZEMBOWER]

Martijn, thank you very much for your analysis. I hope others will continue to join in.

With regard to your point quoted below, are you referring to PostgreSQL, and would 
that be a
stronger competitor to MS SQL Server 2000 than either the current version of MySQL or 
MySQL 5?

Thanks, again, for your thoughts.

-Kevin

 Martijn Tonies [EMAIL PROTECTED] 11/07/03 12:12PM 
 3. Would the deficiencies pointed out in MySQL, if true, apply to the type
of work we envision? Granted, I haven't given you all much information about
what we hope to do with an SQL engine, but I don't think it will be very
sophisticated.


If it's not very sophisticated, MySQL will do just fine. In my opinion.
There are
other free and open source DBMSes as well, that do have procedures,
triggers,
views, subqueries and the like.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird  MS SQL Server.
Upscene Productions
http://www.upscene.com 


-- 
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]

RE: Loosing é characters in DB insertion

[Yves Arsenault]

The è caracter is being changed to a square...

Yves

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 7 novembre 2003 13:19
To: 'Yves Arsenault'; 1Mysql
Subject: RE: Loosing é characters in DB insertion


Are the é being replaced by another character?

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 10:05 AM
To: 1Mysql
Subject: Loosing é characters in DB insertion



Hello,

I just took over a site from a client, he used an Access DB, the Database
contains mostly french text...

I've created a dump file to transfer the DB to MySQL, but when I execute the
code, the é characters are lost.

They're being created as VARCHAR..

I'm not a very experienced MySQL user. Is there anything I should look for
in the dump file?


--
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]

RE: Very strange problem

[Victor Pendleton]

Is anything being logged in the error log?

-Original Message-
From: Eric Anderson [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 11:16 AM
To: [EMAIL PROTECTED]
Subject: Very strange problem



A little explaination:

We've got two servers, e450 (3.23.47) and 1U (4.0.14).  These two
machines have a private connection between them (192.168.0.1 and
192.168.0.2).

The e450 is the master server, and the 1U has a copy (no replication)
of some basic data, so it can run server-intensive pages.  However, it
needs to connect back to the e450 to insert some data.

Everything runs fine until the e450 does something that blocks other
threads (including the threads that are connecting from the 1U) on the
table 'vip_t'.

Let's say for example that I run a lengthy query on table 'vip_t' which
causes all the other threads to be blocked while that query runs.

The threads connecting from the 1U (192.168.0.2) all start to block up
as expected, but when the length query on the e450 stops running, the
threads from the 1U stay blocked, and all NEW threads end up in a
limbo state:

| 726136 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726135 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726134 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726138 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726140 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726142 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726143 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726148 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726149 | nobody   | localhost   | bm   | Sleep
| 11   |  | NULL  |
| 726150 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726153 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726158 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726161 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726164 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726170 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726172 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726173 | nobody   | localhost   | bm   | Sleep
| 4|  | NULL  |
| 726181 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726185 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726191 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |
| 726201 | unauthenticated user | 192.168.0.2 | NULL | Connect
| NULL | login| NULL  |

No connections from 1U (192.168.0.2) are possible - they just hang, even
the interactive client just hangs.

In order to clear things, I have to shutdown both httpd and mysql on
both machines and restart them.  And on the e450, mysql will not shut
down completely.  'mysqladmin shutdown' just hangs (ostensibly waiting
for the above threads to clear).  I end up flushing everything and
killing both mysql and the starting script.

I'm sure the solution is to upgrade the e450's version of mysql first
because that seems to be where the problem is, but I hate to do it at
gunpoint.  I'm wondering if anyone else has heard/seen any of these
problems?

-- 
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:

RE: Loosing é characters in DB insertion

[Victor Pendleton]

What operating system does the MySQL server reside? The ASCII representation
for é is 233. Is this the same value for the host machine?

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 11:27 AM
To: Victor Pendleton; 1Mysql
Subject: RE: Loosing é characters in DB insertion



The è caracter is being changed to a square...

Yves

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 7 novembre 2003 13:19
To: 'Yves Arsenault'; 1Mysql
Subject: RE: Loosing é characters in DB insertion


Are the é being replaced by another character?

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 10:05 AM
To: 1Mysql
Subject: Loosing é characters in DB insertion



Hello,

I just took over a site from a client, he used an Access DB, the Database
contains mostly french text...

I've created a dump file to transfer the DB to MySQL, but when I execute the
code, the é characters are lost.

They're being created as VARCHAR..

I'm not a very experienced MySQL user. Is there anything I should look for
in the dump file?


--
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]

RE: MySQL query question

[Andy Eastham]

Chris,

You're almost there!
select * from temp where col2 like concat('%',col1, '%');

Andy

 -Original Message-
 From: Chris A. Mattingly [mailto:[EMAIL PROTECTED]
 Sent: 07 November 2003 17:01
 To: [EMAIL PROTECTED]
 Subject: MySQL query question


 I've searched around on the lists archives and even did some
 googling, but I'm
 having trouble finding the answer to this question.

 Given that I have a table with 2 columns (say col1, col2) I want
 to be able to
 search for the value of col1 in the value of col2.  Let's say that in one
 instance col1 = foo and col2 = foobar, I want to know, but if col1
 = temp and col2 = foobar, I do not want anything returned.

 A query something like:  SELECT col2 FROM table WHERE col2 LIKE '%col1%';

 Any help would be greatly appreciated.

 -Chris


 --
 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]

RE: Very strange problem

[Eric Anderson]

 Is anything being logged in the error log?

Nope.

My 'wait_timeout' was set to 120 seconds, I reduced that down to 60
seconds but I don't think that will help because once it gets stuck in
this blocked thread thing, it never recovers.. any subsequent
connections never actually process, until the max_connections are hit.
It's very strange.. :-/

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

Stupid permission question

[Steven Lefevre]

Hey folks -

I have an installation of mysql 3.23 from debian stable, installed with apt-get. 

I'm trying to create users but I'm running into problems. I'm using phpmyadmin, and 
myabe that's the problem, I don't know. Here's something that confuses me:

$ mysql -uanna -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 143 to server version: 3.23.49-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql use dummy_database
ERROR 1044: Access denied for user: '@localhost' to database 'dummy_database'
mysql

Why doesn't it show the user as '[EMAIL PROTECTED]' instead of '@localhost'?


Steve Lefevre
Network Administrator 
IMI International, Inc.
614.839.2500

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

RE: Loosing é characters in DB insertion

[Yves Arsenault]

Red Hat 9.
Looking at the data through Webmin, it appears as though the text is correct
in the DB in certain fields, in some others, there are strange characters...
I think the clients db may have had slight errors...

In any effect, the web interface that is run off this DB doesn't display the
'é' characters..

Yves
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 7 novembre 2003 13:29
To: 'Yves Arsenault'; Victor Pendleton; 1Mysql
Subject: RE: Loosing é characters in DB insertion


What operating system does the MySQL server reside? The ASCII representation
for é is 233. Is this the same value for the host machine?

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 11:27 AM
To: Victor Pendleton; 1Mysql
Subject: RE: Loosing é characters in DB insertion



The è caracter is being changed to a square...

Yves

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 7 novembre 2003 13:19
To: 'Yves Arsenault'; 1Mysql
Subject: RE: Loosing é characters in DB insertion


Are the é being replaced by another character?

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 10:05 AM
To: 1Mysql
Subject: Loosing é characters in DB insertion



Hello,

I just took over a site from a client, he used an Access DB, the Database
contains mostly french text...

I've created a dump file to transfer the DB to MySQL, but when I execute the
code, the é characters are lost.

They're being created as VARCHAR..

I'm not a very experienced MySQL user. Is there anything I should look for
in the dump file?


--
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]

Re: Need help comparing MySQL to MS SQL Server

[Martijn Tonies]

Hi Kevin,


 Martijn, thank you very much for your analysis.
I hope others will continue to join in.

So do I :-)

 With regard to your point quoted below, are you referring to PostgreSQL,
and would that be a
 stronger competitor to MS SQL Server 2000 than either the current version
of MySQL or
 MySQL 5?

I have no experience with PostgreSQL - although, from what I've heard and
read,
it's quite capable - but not easy to get going on Windows.

One other open source RDBMS would be Firebird - see www.firebirdsql.org
Especially the newer release (1.5). Don't get fooled by that version
number -
it's a fork of the Borland InterBase code, which has been around for about
20
years now.

I'm looking forward to MySQL5 to see what's new and how it's implemented.

As for what engine would be the best for you - it all depends on what you're
going to do. For example, I frequently use triggers and check constraints in
my database applications, with MySQL, I can't do this.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird  MS SQL Server.
Upscene Productions
http://www.upscene.com


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

RE: Loosing é characters in DB insertion

[Victor Pendleton]

The Red Hat client does not display the 233 the same as the MS client. The
underlying data should be the same. Try to dump the decimal values of the
data in question and see if 233 is present where it is supposed to be.

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 11:44 AM
To: Victor Pendleton; 1Mysql
Subject: RE: Loosing é characters in DB insertion



Red Hat 9.
Looking at the data through Webmin, it appears as though the text is correct
in the DB in certain fields, in some others, there are strange characters...
I think the clients db may have had slight errors...

In any effect, the web interface that is run off this DB doesn't display the
'é' characters..

Yves
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 7 novembre 2003 13:29
To: 'Yves Arsenault'; Victor Pendleton; 1Mysql
Subject: RE: Loosing é characters in DB insertion


What operating system does the MySQL server reside? The ASCII representation
for é is 233. Is this the same value for the host machine?

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 11:27 AM
To: Victor Pendleton; 1Mysql
Subject: RE: Loosing é characters in DB insertion



The è caracter is being changed to a square...

Yves

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 7 novembre 2003 13:19
To: 'Yves Arsenault'; 1Mysql
Subject: RE: Loosing é characters in DB insertion


Are the é being replaced by another character?

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 10:05 AM
To: 1Mysql
Subject: Loosing é characters in DB insertion



Hello,

I just took over a site from a client, he used an Access DB, the Database
contains mostly french text...

I've created a dump file to transfer the DB to MySQL, but when I execute the
code, the é characters are lost.

They're being created as VARCHAR..

I'm not a very experienced MySQL user. Is there anything I should look for
in the dump file?


--
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]

Re: Benchmark differences: Mac OS X - Linux

[Gabriel Ricard]

On Friday, November 7, 2003, at 09:05  AM, David Steinbrunner wrote:


The Mac was HFS+ journaled. Disk: the stock Apple-supplied one. The
Linux machine was a default SuSE 8.0 installation. ext2 as the
filesystem? No idea about journaling. No SCSI or RAID, just an
internal IDE disk. Both machines are really consumer-level machines,
no heavy-duty server hardware. That's about all I can tell you, I
really don't know that much about the details of the hardware. Sorry,
not much help here, I know.
Hmm.  The Mac had journaling but the Linux box did not (ext2 is not
journaled).  That could have influenced things too.  I know virtually
nothing about HFS+ journaling.
You can turn off journaling in 10.3 in Disk Utility by going to File 
and selecting  Disable Journaling.  This page has some visuals:
http://diveintoosx.org/panther/disk_management.html

There is a Terminal command that does the same thing that I don't know 
off the top of my head.
diskutil disableJournal disk0s1

- Gabriel

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

RE: Stupid permission question

[Victor Pendleton]

query your use table and see if the anonymous user is still present. You
will need to delete this account and flush privileges. 

-Original Message-
From: Steven Lefevre [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 11:33 AM
To: [EMAIL PROTECTED]
Subject: Stupid permission question


Hey folks -

I have an installation of mysql 3.23 from debian stable, installed with
apt-get. 

I'm trying to create users but I'm running into problems. I'm using
phpmyadmin, and myabe that's the problem, I don't know. Here's something
that confuses me:

$ mysql -uanna -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 143 to server version: 3.23.49-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql use dummy_database
ERROR 1044: Access denied for user: '@localhost' to database
'dummy_database'
mysql

Why doesn't it show the user as '[EMAIL PROTECTED]' instead of '@localhost'?


Steve Lefevre
Network Administrator 
IMI International, Inc.
614.839.2500

-- 
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 processes

[Hugh Beaumont]

Hi List,

I've searched the archives and the web many times in the past for answers to the 
following
questions but never really got a complete understanding of what is going on. Hopefully 
someone on
the list will be able to take the time to set me straight.

I have a typical php + mysql + linux setup. Today I noticed a high load, and checked 
things out
with:

mysql% show processlist;

it showed 6 processes running:

[ I'll skip all the ascii output since it usually ends up garbled]

6 rows in set (0.00 sec)


however if I do:

 ps axuw | grep mysql | wc -l

I get:

 28

What is going on here? 

Do I have 28 mysqld's waiting around for requests and just 6 actually processes 
running?

Is there any way to regulate this - something similiar to apache's max_clients, min 
spare server,
max spare servers, etc?

We often have mysql just totally melt down a server - someone will run a bit query and 
then all
the others start to back-up. We then end up with a ton of queries trying run at once, 
the load
goes to 50 or even 100(!).

How can I stop this? Any tips at all on regulating the use of mysql - this is an ISP 
server so it
is very hard to make sure that all usrs are running sensible and well-constructed 
database
schema's.

I have tried to contact mysql for consulting but their beginning prices are too high 
for our
modest budget. Does anyone else know of ways to get some help if no one on the list is 
able to
advise?

Thanks!


__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

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

Re: MySQL benchmarks on the G5: Jaguar vs. Panther

[Gabriel Ricard]

On Friday, November 7, 2003, at 03:06  AM, Jan Pieter Kunst wrote:

Hardware: 1.8 GHz G5 with 1GB RAM

Panther (Mac OS X 10.3) doesn't have the sudden slowness in the  
'insert' test that Jaguar (Mac OS X 10.2) has. Total time for Panther  
is faster than total time for Jaguar (5561.00 secs vs. 6770.00 secs).  
'Insert' is faster on Panther than on Jaguar (3316 secs vs. 5215  
secs), but all other benchmarks are somewhat slower on Panther. I'm a  
bit worried about the 'select', which is about twice as  slow on  
Panther than on Jaguar (1428 secs vs. 743 secs).

Complete benchmark results below, sorry for the possible bandwidth  
waste, but I'm not sure what I could snip, if anything.

JP

Panther benchmarks:
=== 
==
Benchmark DBD suite: 2.14
Date of test:2003-11-07  0:12:11
Running tests on:Darwin 7.0.0 Power Macintosh
Arguments:
Comments:
Limits from: mysql,pg,solid
Server version:  MySQL 4.0.15 standard log
Optimization:None
Hardware:

alter-table: Total time: 141 wallclock secs ( 0.22 usr  0.11 sys +  
0.00 cusr  0.00 csys =  0.33 CPU)
ATIS: Total time: 59 wallclock secs (10.59 usr  6.30 sys +  0.00 cusr  
0.00 csys = 16.89 CPU)
big-tables: Total time: 57 wallclock secs ( 7.53 usr 13.50 sys + 0.00  
cusr  0.00 csys = 21.03 CPU)
connect: Total time: 252 wallclock secs (44.31 usr 38.81 sys +  0.00  
cusr  0.00 csys = 83.12 CPU)
create: Total time: 298 wallclock secs ( 8.76 usr  3.06 sys +  0.00  
cusr  0.00 csys = 11.82 CPU)
insert: Total time: 3316 wallclock secs (594.82 usr 261.66 sys + 0.00  
cusr  0.00 csys = 856.48 CPU)
select: Total time: 1428 wallclock secs (67.50 usr 25.18 sys +  0.00  
cusr  0.00 csys = 92.68 CPU)
transactions: Test skipped because the database doesn't support  
transactions
wisconsin: Total time: 27 wallclock secs ( 4.19 usr  2.58 sys +  0.00  
cusr  0.00 csys =  6.77 CPU)


I'm not sure how useful it is for comparison, but our dual 2GHz G5 w/  
4GB of RAM had these results (for MyISAM tables):

alter-table: Total time: 11 wallclock secs ( 0.02 usr  0.04 sys +  0.00  
cusr  0.00 csys =  0.06 CPU)
ATIS: Total time: 21 wallclock secs ( 9.41 usr  9.44 sys +  0.00 cusr   
0.00 csys = 18.85 CPU)
big-tables: Total time: 34 wallclock secs ( 7.90 usr 20.86 sys +  0.00  
cusr  0.00 csys = 28.76 CPU)
connect: Total time: 180 wallclock secs (45.56 usr 51.51 sys +  0.00  
cusr  0.00 csys = 97.07 CPU)
create: Total time: 118 wallclock secs ( 3.87 usr  2.55 sys +  0.00  
cusr  0.00 csys =  6.42 CPU)
insert: Total time: 1437 wallclock secs (432.84 usr 358.12 sys +  0.00  
cusr  0.00 csys = 790.96 CPU)
select: Total time: 146 wallclock secs (42.52 usr 33.74 sys +  0.00  
cusr  0.00 csys = 76.26 CPU)
transactions: Test skipped because the database doesn't support  
transactions
wisconsin: Total time: 12 wallclock secs ( 2.91 usr  2.76 sys +  0.00  
cusr  0.00 csys =  5.67 CPU)

How the select portion of the test could be 10x slower, I'm not sure.  
Have you tuned MySQL at all? key_buffer, etc?

- Gabriel

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

multitable sum problem

[David Katz]

I have the following two tables:

salesinvoiceslysalesinvoices
  salesrepidsalesrepid  
  invamt  lyinvamt

I am trying to get a current year and last year sum for each sales rep.  The problem 
is it looks like it is resuming each file for each record.  
select salesrepid, sum(invamt) as curramt, sum(lyinvamt) as lyamt from 
salesinvoices,lysalesinvoices group by salesrepid order by curramt;

++-+--+
| salesrepid | curramt| lyamt  |
+---+---+-+
| 101   | 3434464.00  | 4286744.00 |
| 102   | 3507312.00  | 4286744.00 |
+---+---+--+

I've attached the individual file records below.

Any help would be appreciated.

Thanks
David.


mysql select salesrepid, invamt from salesinvoices;
++--+
| salesrepid | invamt   |
++--+
| 101| 25230.00 |
| 101| 34332.00 |
| 101| 24564.00 |
| 101| 15155.00 |
| 101| 43243.00 |
| 101| 34352.00 |
| 101| 24352.00 |
| 101| 13426.00 |
| 102| 26330.00 |
| 102| 36433.00 |
| 102| 25436.00 |
| 102| 16335.00 |
| 102| 43543.00 |
| 102| 34456.00 |
| 102| 23422.00 |
| 102| 13252.00 |
++--+

mysql select lysalesrepid, lyinvamt from lysalesinvoices;
+--+--+
| lysalesrepid | lyinvamt |
+--+--+
| 101  | 25230.00 |
| 101  | 34332.00 |
| 101  | 24564.00 |
| 101  | 15155.00 |
| 101  | 32650.00 |
| 101  | 43224.00 |
| 101  | 43623.00 |
| 101  | 45633.00 |
| 102  | 23330.00 |
| 102  | 34653.00 |
| 102  | 23264.00 |
| 102  | 26435.00 |
| 102  | 43550.00 |
| 102  | 32524.00 |
| 102  | 32353.00 |
| 102  | 55323.00 |
+--+--+

Re: Need help comparing MySQL to MS SQL Server

[Nestor Florez]

I have not work with it but postgres is supposed to work great in
/BSD/Linux/Unix/solaris environment
Which platform are you using?

:-) 

Nestor A. Florez


 Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM 
Hi Kevin,


 Martijn, thank you very much for your analysis.
I hope others will continue to join in.

So do I :-)

 With regard to your point quoted below, are you referring to
PostgreSQL,
and would that be a
 stronger competitor to MS SQL Server 2000 than either the current
version
of MySQL or
 MySQL 5?

I have no experience with PostgreSQL - although, from what I've heard
and
read,
it's quite capable - but not easy to get going on Windows.

One other open source RDBMS would be Firebird - see www.firebirdsql.org

Especially the newer release (1.5). Don't get fooled by that version
number -
it's a fork of the Borland InterBase code, which has been around for
about
20
years now.

I'm looking forward to MySQL5 to see what's new and how it's
implemented.

As for what engine would be the best for you - it all depends on what
you're
going to do. For example, I frequently use triggers and check
constraints in
my database applications, with MySQL, I can't do this.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird  MS SQL
Server.
Upscene Productions
http://www.upscene.com 


-- 
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]

Re: Issues with Mysql 4.0 and PHP in a production environment

[Gabriel Ricard]

We've been using PHP 4.3.x and MySQL 4.0.x for a few months now on our 
production systems and had encountered only one problem unrelated to 
PHP (an issue where LOAD DATA FROM MASTER would wipe out all of the 
tables in the database, even if it was just using wildcard replication 
for a few tables) which was fixed in 4.0.16.

- Gabriel

On Friday, November 7, 2003, at 08:59  AM, Luis Lebron wrote:

Anything that breaks a script (unsupported functions, changes, etc.) 
or that
causes performance problems.

thanks,

Luis

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 06, 2003 4:09 PM
To: Luis Lebron
Cc: Mysql (E-mail); Php-General (E-mail)
Subject: Re: Issues with Mysql 4.0 and PHP in a production environment
On Thu, Nov 06, 2003 at 02:10:27PM -0600, Luis Lebron wrote:
Are there any issues with running PHP 4.3.X and Mysql 4.0 in a 
production
environment?
What sort of issues are you looking for?

Jeremy
--
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/
MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,012,977,736 queries
(432/sec. avg)


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

Re: aes encryption bug

[William R. Mussatto]

Paul DuBois said:
 At 15:29 -0800 11/6/03, Herb Rubin wrote:
Paul,

I did try VARCHAR(16) BINARY and it still failed to INSERT in a NOT
 NULL column.

The encrypted string seems to be equivalent to the NULL value even
 though it visually looks like some kind of data.

Herb

 Okay, I investigated this further and I believe I know the cause of the
 problem.  The solution is to use a TINYBLOB NOT NULL rather than
 a VARCHAR(16) NOT NULL.

 Here's what's going on:

 - The particular encrypted value you calculate ends with 0x20.  That is,
the same value as a space character.
 - Trailing spaces are trimmed from values stored in VARCHAR columns. -
 That means when you retrieve the value, it's 15 bytes long, and is no
longer a legal encrypted value.
 - AES_DECRYPT() returns NULL for illegal encrypted values.

 Can you instead use CHAR(16) instead of VARCHAR(16)? No, because
 the trailing space would still be trimmed *when the value is retrieved*,
 and you'd still get NULL from AES_DECRYPT().

 Use a TINYBLOB instead.  Trailing spaces won't be trimmed when the value
 is stored, or when it is retrieved.  AES_DECRYPT() will work.

 In general, you shouldn't try to use CHAR/VARCHAR for encrypted values
 or other forms of binary data. Use BLOB columns instead.



  Please reply to the list, not to me directly, so that others can
 follow this discussion.  Thanks.

  At 14:54 -0800 11/6/03, Herb Rubin wrote:
Paul,

Yes, I get the same, now try and decrypt it, it will turn out to be
 NULL. So, you cannot insert this into a NOT NULL column. It will
 reject it.

  Your message (below) appears to be reporting a problem with
  AES_ENCRYPT(). It states that you get a NULL return value from that
 function.

  You now appear to be saying something else.

  I don't understand what problem you're trying to report.


  My result from decryption:

  mysql select AES_ENCRYPT('551850040',
  '0bf251c9aaf007deaf1143ca1492b561');
  +--+ |
 AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561') |
 +--+ |
 k\ÎúVÀàÿxû÷Ò  |
  +--+ 1
 row in set (0.00 sec)

  mysql select
  AES_DECRYPT(AES_ENCRYPT('551850040',
  '0bf251c9aaf007deaf1143ca1492b561'),
   - '0bf251c9aaf007deaf1143ca1492b561');
  +-
--+
  | AES_DECRYPT(AES_ENCRYPT('551850040',
  '0bf251c9aaf007deaf1143ca1492b561'),
  '0bf251c9aaf007deaf1143ca1492b561') |
  +-
--+
  | 551850040
  |
  +-
--+
  1 row in set (0.07 sec)

  Are you reporting that you get NULL only in the context of inserting
 and retrieving the value from the id column?

  If so, please try using TINYBLOB rather than VARCHAR and see what
 happens.



we are on 4.0.14
Herb


   At 14:03 -0800 11/6/03, Herb Rubin wrote:
Hi,

I am trying to use aes_encrypt and I get a NULL value with a
 specific string:

INSERT INTO test SET
`id` = AES_ENCRYPT('551850040',
 '0bf251c9aaf007deaf1143ca1492b561');

my field 'id' is VARCHAR(16) NOT NULL

If I change the value or the encryption string it works. But this
 combination turns out to be null and it refuses to insert.

   With MySQL 4.0.14, 4.0.16, and 4.1.1, I get:

   mysql select AES_ENCRYPT('551850040',
   '0bf251c9aaf007deaf1143ca1492b561');
   +--+
 | AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561') |
 +--+
 |
k\ÎúVÀàÿxû÷Ò  |
   +--+

   What version are you using?



Help!

Herb


 --
 Paul DuBois, Senior Technical Writer
 Madison, Wisconsin, USA
 MySQL AB, www.mysql.com

 Are you MySQL certified?  http://www.mysql.com/certification/
So a char(16) binary would work fine as well?

William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061



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

Re: SQL [Q] how to migrate 4.0 - 4.1

[Andrey Kotrekhov]

SQL
mysql

Hi, All.
Other question is:
Can I create case sensitive field with right ordering of national
characters.



 HI, All!
 I have a question.
 I migrate from 4.0.14 to 4.1.0 but:
 the 4.0.14 show me:
 mysql desc statname;
 +--+-+--+-+-+---+
 | Field| Type| Null | Key | Default | Extra |
 +--+-+--+-+-+---+
 | statname | char(16) binary |  | PRI | unknown |   |
 | passwd   | char(64) binary |  | | *   |   |
 | env  | char(10)|  | | |   |
 | area | int(11) |  | | 0   |   |
 | manager  | char(16) binary |  | | |   |
 | d_create | datetime|  | | -00-00 00:00:00 |   |
 | d_change | datetime|  | | -00-00 00:00:00 |   |
 | wizard   | int(11) |  | | 0   |   |
 +--+-+--+-+-+---+
  And 4.1.0 show me on the sane table:
 mysql mysql desc statname;
 +--+--+--+--+-+-+---+
 | Field| Type | Collation| Null | Key
 | Default | Extra |
 +--+--+--+--+-+-+---+
 | statname | char(16) character set koi8r | koi8r_general_ci |  | PRI
 | unknown |   |
 | passwd   | char(64) character set koi8r | koi8r_general_ci |  |
 | *   |   |
 | env  | char(10) character set koi8r | koi8r_general_ci |  |
 | |   |
 | area | int(11)  | binary   |  |
 | 0   |   |
 | manager  | char(16) character set koi8r | koi8r_general_ci |  |
 | |   |
 | d_create | datetime | koi8r_general_ci |  |
 | -00-00 00:00:00 |   |
 | d_change | datetime | koi8r_general_ci |  |
 | -00-00 00:00:00 |   |
 | wizard   | int(11)  | binary   |  |
 | 0   |   |
 +--+--+--+--+-+-+---+

 But where is my binary flags?
 Select use fields as binary.
 But I don't want binary on each field.
 I need old types on fields.

 How can I resolv this?

 Best regards.
 ___
   Andrey Kotrekhov [EMAIL PROTECTED]
   ISP Alkar Teleport
   . +380 562 34-00-44

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



Best regards.
___
Andrey Kotrekhov [EMAIL PROTECTED]
ISP Alkar Teleport
. +380 562 34-00-44

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

Re: Need help comparing MySQL to MS SQL Server

[Brent Baisley]

It sounds like a copy and paste from Microsoft, but that is just my 
guess. An objective recommendation with show pluses and minuses of 
both. It most definitely does not sound like this consultant is 
qualified to suggest a database product. What about PostgresSQL, 
Oracle, Sybase, DB2? They all at least match the features of SQL 
Server, except maybe Postgres, and they run on more platforms and are 
more scalable.
MySQL does have transaction support in the form of InnoDB tables. 
Sub-queries are now supported in v4, although not the fastest 
implementation. The other areas MySQL is lacking.
When I first started using MySQL, subqueries were not supported and I 
found it frustrating. Since then I have truly acquired in depth 
knowledge of left and right joins and other techniques that I really 
didn't have before. I think I now write better, faster queries because 
I was forced to learn a new technique. I now have better knowledge of 
SQL. Not sure if that was a good or bad point.

If your needs are simple, you can get by without stored procedures and 
triggers. Referential integrity can be enforced in your front-end 
code.
Here is a question: how much would it cost to give everyone a copy of 
the database to play with? On their laptop, home computer? Nothing for 
MySQL or Postgres.
What front-end will be used? Your options for SQL Server are kind of 
limited.

On Friday, November 7, 2003, at 11:39 AM, KEVIN ZEMBOWER wrote:

I don't know whether the consultant wrote this himself, or if it came 
from somewhere. It could be Microsoft advertizement, for all I know. 
Most of the terms aren't familiar to me, like sub-queries or 
referential integrity. I feel out of my depth evaluating this 
comparison.

My questions are:
1. Is this a fair comparison of MySQL and MS SQL Server 2000?
2. Is this up to date with the current status of MySQL?
3. Would the deficiencies pointed out in MySQL, if true, apply to the 
type of work we envision? Granted, I haven't given you all much 
information about what we hope to do with an SQL engine, but I don't 
think it will be very sophisticated.

Thank you for all your thoughts and comments.

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Need help comparing MySQL to MS SQL Server

[KEVIN ZEMBOWER]

Nestor, thanks for your question.

The platform will actually be dictated by the SQL engine, not the 
other way around, which is more typically the case. If we go with 
MS SQL Server, we'll build a separate host, NT I would guess, to 
host it. I'm only responsible for Unix and Linux boxes here, so it'll be the 
responsibility of another group. If we go with MySQL or PostgreSQL 
(the only databases I have any familiarity with), I'll probably be 
responsible for setting up and configuring a new Linux (Debian) host, 
and maintaining it. The in-house database administer would be the 
administrator, and I would just offer any help that I could, which might 
not be much.

Thanks, again, for writing.

-Kevin

 Nestor Florez [EMAIL PROTECTED] 11/07/03 01:18PM 
I have not work with it but postgres is supposed to work great in
/BSD/Linux/Unix/solaris environment
Which platform are you using?

:-) 

Nestor A. Florez


 Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM 
Hi Kevin,


 Martijn, thank you very much for your analysis.
I hope others will continue to join in.

So do I :-)

 With regard to your point quoted below, are you referring to
PostgreSQL,
and would that be a
 stronger competitor to MS SQL Server 2000 than either the current
version
of MySQL or
 MySQL 5?

I have no experience with PostgreSQL - although, from what I've heard
and
read,
it's quite capable - but not easy to get going on Windows.

One other open source RDBMS would be Firebird - see www.firebirdsql.org 

Especially the newer release (1.5). Don't get fooled by that version
number -
it's a fork of the Borland InterBase code, which has been around for
about
20
years now.

I'm looking forward to MySQL5 to see what's new and how it's
implemented.

As for what engine would be the best for you - it all depends on what
you're
going to do. For example, I frequently use triggers and check
constraints in
my database applications, with MySQL, I can't do this.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird  MS SQL
Server.
Upscene Productions
http://www.upscene.com 


-- 
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]

RE: Need help comparing MySQL to MS SQL Server

[John Griffin]

What about MySQL-max/SAPDB? I believe that it was completely omitted in the 
consultants report but has many of the features you need.

I would also like to ask a question; do you need stored procedures, triggers or views 
for your application? There are a number of high volume, high quality sites that do 
very nicely without them. Why are you different?

John Griffin

-Original Message-
From: KEVIN ZEMBOWER [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 1:53 PM
To: [EMAIL PROTECTED]
Subject: Re: Need help comparing MySQL to MS SQL Server


Nestor, thanks for your question.

The platform will actually be dictated by the SQL engine, not the 
other way around, which is more typically the case. If we go with 
MS SQL Server, we'll build a separate host, NT I would guess, to 
host it. I'm only responsible for Unix and Linux boxes here, so it'll be the 
responsibility of another group. If we go with MySQL or PostgreSQL 
(the only databases I have any familiarity with), I'll probably be 
responsible for setting up and configuring a new Linux (Debian) host, 
and maintaining it. The in-house database administer would be the 
administrator, and I would just offer any help that I could, which might 
not be much.

Thanks, again, for writing.

-Kevin

 Nestor Florez [EMAIL PROTECTED] 11/07/03 01:18PM 
I have not work with it but postgres is supposed to work great in
/BSD/Linux/Unix/solaris environment
Which platform are you using?

:-) 

Nestor A. Florez


 Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM 
Hi Kevin,


 Martijn, thank you very much for your analysis.
I hope others will continue to join in.

So do I :-)

 With regard to your point quoted below, are you referring to
PostgreSQL,
and would that be a
 stronger competitor to MS SQL Server 2000 than either the current
version
of MySQL or
 MySQL 5?

I have no experience with PostgreSQL - although, from what I've heard
and
read,
it's quite capable - but not easy to get going on Windows.

One other open source RDBMS would be Firebird - see www.firebirdsql.org 

Especially the newer release (1.5). Don't get fooled by that version
number -
it's a fork of the Borland InterBase code, which has been around for
about
20
years now.

I'm looking forward to MySQL5 to see what's new and how it's
implemented.

As for what engine would be the best for you - it all depends on what
you're
going to do. For example, I frequently use triggers and check
constraints in
my database applications, with MySQL, I can't do this.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird  MS SQL
Server.
Upscene Productions
http://www.upscene.com 


-- 
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]


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

RE: Loosing é characters in DB insertion

[Yves Arsenault]

The only language I installed on this box is English... would that be why?

Yves

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 7 novembre 2003 14:16
To: 'Yves Arsenault'; Victor Pendleton; 1Mysql
Subject: RE: Loosing é characters in DB insertion


The Red Hat client does not display the 233 the same as the MS client. The
underlying data should be the same. Try to dump the decimal values of the
data in question and see if 233 is present where it is supposed to be.

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 11:44 AM
To: Victor Pendleton; 1Mysql
Subject: RE: Loosing é characters in DB insertion



Red Hat 9.
Looking at the data through Webmin, it appears as though the text is correct
in the DB in certain fields, in some others, there are strange characters...
I think the clients db may have had slight errors...

In any effect, the web interface that is run off this DB doesn't display the
'é' characters..

Yves
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 7 novembre 2003 13:29
To: 'Yves Arsenault'; Victor Pendleton; 1Mysql
Subject: RE: Loosing é characters in DB insertion


What operating system does the MySQL server reside? The ASCII representation
for é is 233. Is this the same value for the host machine?

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 11:27 AM
To: Victor Pendleton; 1Mysql
Subject: RE: Loosing é characters in DB insertion



The è caracter is being changed to a square...

Yves

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 7 novembre 2003 13:19
To: 'Yves Arsenault'; 1Mysql
Subject: RE: Loosing é characters in DB insertion


Are the é being replaced by another character?

-Original Message-
From: Yves Arsenault [mailto:[EMAIL PROTECTED]
Sent: Friday, November 07, 2003 10:05 AM
To: 1Mysql
Subject: Loosing é characters in DB insertion



Hello,

I just took over a site from a client, he used an Access DB, the Database
contains mostly french text...

I've created a dump file to transfer the DB to MySQL, but when I execute the
code, the é characters are lost.

They're being created as VARCHAR..

I'm not a very experienced MySQL user. Is there anything I should look for
in the dump file?


--
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]

Re: Need help comparing MySQL to MS SQL Server

[Jeff Mathis]

kevin,

i tend to think the consultant really just read something that microsoft
sent him. it doesn't sound like he's qualified to suggest one database
or another.

We've been usinf mysql for a year now. We use InnoDB tables, which give
us primary key/foreign key constraints and transactions. We've gotten
around the lack of stored procedures by putting the necessary logic and
checks into the application that inserts/updates the database. We have
several tables with  8 million rows, and growing every day. updating
rows on the big tables still shows approximately constant time
performance. In general, we are extremely satisfied with the product,
and have purchased a license (about $400 -- mainly so we can contribute
to the cause). When 4.1 becomes stable, we will upgrade in order to get
the sub-select capability. 

I came from an Oracle/Sybase background. Those products have features
that mysql does not have, in particular DBA-specific tables, views, and
triggers, but you may not need these features. 

happy to give you more information if you need it.

jeff

KEVIN ZEMBOWER wrote:
 
 Nestor, thanks for your question.
 
 The platform will actually be dictated by the SQL engine, not the
 other way around, which is more typically the case. If we go with
 MS SQL Server, we'll build a separate host, NT I would guess, to
 host it. I'm only responsible for Unix and Linux boxes here, so it'll be the
 responsibility of another group. If we go with MySQL or PostgreSQL
 (the only databases I have any familiarity with), I'll probably be
 responsible for setting up and configuring a new Linux (Debian) host,
 and maintaining it. The in-house database administer would be the
 administrator, and I would just offer any help that I could, which might
 not be much.
 
 Thanks, again, for writing.
 
 -Kevin
 
  Nestor Florez [EMAIL PROTECTED] 11/07/03 01:18PM 
 I have not work with it but postgres is supposed to work great in
 /BSD/Linux/Unix/solaris environment
 Which platform are you using?
 
 :-)
 
 Nestor A. Florez
 
  Martijn Tonies [EMAIL PROTECTED] 11/7/2003 10:08:53 AM 
 Hi Kevin,
 
  Martijn, thank you very much for your analysis.
 I hope others will continue to join in.
 
 So do I :-)
 
  With regard to your point quoted below, are you referring to
 PostgreSQL,
 and would that be a
  stronger competitor to MS SQL Server 2000 than either the current
 version
 of MySQL or
  MySQL 5?
 
 I have no experience with PostgreSQL - although, from what I've heard
 and
 read,
 it's quite capable - but not easy to get going on Windows.
 
 One other open source RDBMS would be Firebird - see www.firebirdsql.org
 
 Especially the newer release (1.5). Don't get fooled by that version
 number -
 it's a fork of the Borland InterBase code, which has been around for
 about
 20
 years now.
 
 I'm looking forward to MySQL5 to see what's new and how it's
 implemented.
 
 As for what engine would be the best for you - it all depends on what
 you're
 going to do. For example, I frequently use triggers and check
 constraints in
 my database applications, with MySQL, I can't do this.
 
 With regards,
 
 Martijn Tonies
 Database Workbench - developer tool for InterBase, Firebird  MS SQL
 Server.
 Upscene Productions
 http://www.upscene.com
 
 --
 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]

-- 
Jeff Mathis, Ph.D.  505-955-1434
The Prediction Company  [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505

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

How do I distinguish between a TEXT and BLOB field

[David Ritter]

Hello,

I am having trouble finding a way to tell if a result set field I am looking
at is from a TEXT column or BLOB column since the C API lumps both types
together under FIELD_TYPE_BLOB.

Does anyone have any advice on a method to distinguish between these two
types?

Thanks!

Dave

__

David Ritter
Software Developer, SourcePro DB
Rogue Wave Software http://www.roguewave.com

SourcePro DB
http://www.roguewave.com/products/sourcepro/db/



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

Cronjob / rights problem - 2:nd try.

[Anders Norrbring]

[Second try, the first one rendered a few hints that didn't help]


I've set up a new MySQL server on a box with multiple IP addresses, and the
SQL server only binds to ONE of these addresses.

Also, I've been changing some user rights in the SQL setup, and now I get a
cronjob error, related to user rights...  My big problem is that I don't
even know where I should start looking for it.

The system and MySQL setup is in all other aspects the distributed versions
from the SuSE Linux Standard Server 8 distribution, based on United Linux
1.0.

The cronjob error mailed to me is the following:


SCRIPT: clean_catman, OK.
SCRIPT: clean_core, OK.
SCRIPT: do_mandb, OK.
SCRIPT: logrotate exited with RETURNCODE = 1.
SCRIPT: ouput (stdout  stderr) follows

 /usr/bin/mysqladmin: refresh failed; error: 'Access denied for user:
'@localhost' (Using password: NO)'
error running postrotate script
Reload syslog service..done
SCRIPT: logrotate
--- END OF OUTPUT


SCRIPT: slots, OK.
SCRIPT: ouput (stdout  stderr) follows

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket /tmp/.s.PGSQL.5432?
SCRIPT: slots
--- END OF OUTPUT


Can somebody please help me out here?  If the /usr/bin/mysqladmin tries to
use a user from localhost (any) then it fails, because localhost doesn't
have access to the SQL server...  What user should be granted access (and
from where) to make this job work correctly?

Anders Norrbring

Norrbring Consulting
Halmvägen 42
SE-691 48  Karlskoga
SWEDEN


-- 
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]

Re: mysql processes

[Gabriel Ricard]

On Friday, November 7, 2003, at 01:21  PM, Hugh Beaumont wrote:

Hi List,

I've searched the archives and the web many times in the past for 
answers to the following
questions but never really got a complete understanding of what is 
going on. Hopefully someone on
the list will be able to take the time to set me straight.

I have a typical php + mysql + linux setup. Today I noticed a high 
load, and checked things out
with:

mysql% show processlist;

it showed 6 processes running:

[ I'll skip all the ascii output since it usually ends up garbled]

6 rows in set (0.00 sec)

however if I do:

 ps axuw | grep mysql | wc -l

I get:

 28

What is going on here?
Looks like your OS has threads in user space as processes rather than 
in kernel space.


Do I have 28 mysqld's waiting around for requests and just 6 actually 
processes running?
You have one mysqld with 28 threads waiting for requests, only 6 are 
actually processing requests.

Is there any way to regulate this - something similiar to apache's 
max_clients, min spare server,
max spare servers, etc?
I don't recall any way to limit the number of threads created, but 
there is a way to limit the number of threads that are handling delayed 
inserts - max_delayed_threads and thread_cache_size will tell mysql how 
many threads to keep around waiting for something to do. in your case, 
since the threads are in user space, they tend to be more expensive to 
create, so if your mysql server is busy, you may want to leave a bunch 
waiting around to process requests.

Be aware that the following variables are per-thread, and if they are 
set high, will consume a lot of RAM:

read_buffer (was record_buffer)
record_rnd_buffer
sort_buffer

We often have mysql just totally melt down a server - someone will run 
a bit query and then all
the others start to back-up. We then end up with a ton of queries 
trying run at once, the load
goes to 50 or even 100(!).
How many users  databases are there on this server? Is the server 
dedicated to just MySQL? What kinda hardware is it running on?

- Gabriel

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

Re: Cronjob / rights problem - 2:nd try.

[Bernard Clement]

Hello Anders,

NOTE: I am actually using SuSE 8.2 Pro Edition.  However, those CRON jobs seem 
to be at the same place irrelevant of the distro (RedHat or SuSE).  
Therefore, I doubt that SuSE Linux Standard Server 8 has changed that.

If the problem occurs once a day then it is within the script 
/etc/cron.daily/logrotate

That script is used to rotate the various log files in order that they do 
not fill up the hard disk.

The script logrotate will activate all the scripts in /etc/logrotate.d and one 
of them is called mysql.  You will find within that script the activation of 
mysqladmin that generate the error.

I cannot really tell what you have to do to have mysqladmin to work correctly.  
Probably you will have to specify a user with some kind of root permission.

I hope that I have been helpful.

Regards,

Bernard

On Friday 07 November 2003 14:47, Anders Norrbring wrote:
 [Second try, the first one rendered a few hints that didn't help]


 I've set up a new MySQL server on a box with multiple IP addresses, and the
 SQL server only binds to ONE of these addresses.

 Also, I've been changing some user rights in the SQL setup, and now I get a
 cronjob error, related to user rights...  My big problem is that I don't
 even know where I should start looking for it.

 The system and MySQL setup is in all other aspects the distributed versions
 from the SuSE Linux Standard Server 8 distribution, based on United Linux
 1.0.

 The cronjob error mailed to me is the following:


 SCRIPT: clean_catman, OK.
 SCRIPT: clean_core, OK.
 SCRIPT: do_mandb, OK.
 SCRIPT: logrotate exited with RETURNCODE = 1.
 SCRIPT: ouput (stdout  stderr) follows

  /usr/bin/mysqladmin: refresh failed; error: 'Access denied for user:
 '@localhost' (Using password: NO)'
 error running postrotate script
 Reload syslog service..done
 SCRIPT: logrotate
 --- END OF OUTPUT


 SCRIPT: slots, OK.
 SCRIPT: ouput (stdout  stderr) follows

 psql: could not connect to server: No such file or directory
   Is the server running locally and accepting
   connections on Unix domain socket /tmp/.s.PGSQL.5432?
 SCRIPT: slots
 --- END OF OUTPUT


 Can somebody please help me out here?  If the /usr/bin/mysqladmin tries to
 use a user from localhost (any) then it fails, because localhost doesn't
 have access to the SQL server...  What user should be granted access (and
 from where) to make this job work correctly?

 Anders Norrbring

 Norrbring Consulting
 Halmvägen 42
 SE-691 48  Karlskoga
 SWEDEN


 --
 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 Control Center

[Dennis Strickland]

Are there any help files for MySQL Control Center at this time?

 

Dennis H. Strickland

SatisFacts Research, LLC

2360 W. Joppa Road., Joppa Concourse Building

Suite 322

Lutherville, MD 21093

Toll Free: 866.655.1490 ext. 103

Fax: 866.655.1491

Local: 410.583.9080

 

 

Re: Cronjob / rights problem - 2:nd try.

[Bernard Clement]

Hello Anders,

Sorry, I did not go through the end of your message.

You also have a problem with psql which is, I think, the equivalent of mysql 
for PostgreSQL.

Do you need PostgreSQL?  If not, you should deactivate it.

For your problem with mysqladmin, most probably specifying the switch --host 
with the hostname associated with your binding will fix the problem.

Regards,

Bernard

On Friday 07 November 2003 15:15, Bernard Clement wrote:
 Hello Anders,

 NOTE: I am actually using SuSE 8.2 Pro Edition.  However, those CRON jobs
 seem to be at the same place irrelevant of the distro (RedHat or SuSE).
 Therefore, I doubt that SuSE Linux Standard Server 8 has changed that.

 If the problem occurs once a day then it is within the script
 /etc/cron.daily/logrotate

 That script is used to rotate the various log files in order that they do
 not fill up the hard disk.

 The script logrotate will activate all the scripts in /etc/logrotate.d and
 one of them is called mysql.  You will find within that script the
 activation of mysqladmin that generate the error.

 I cannot really tell what you have to do to have mysqladmin to work
 correctly. Probably you will have to specify a user with some kind of
 root permission.

 I hope that I have been helpful.

 Regards,

 Bernard

 On Friday 07 November 2003 14:47, Anders Norrbring wrote:
  [Second try, the first one rendered a few hints that didn't help]
 
 
  I've set up a new MySQL server on a box with multiple IP addresses, and
  the SQL server only binds to ONE of these addresses.
 
  Also, I've been changing some user rights in the SQL setup, and now I get
  a cronjob error, related to user rights...  My big problem is that I
  don't even know where I should start looking for it.
 
  The system and MySQL setup is in all other aspects the distributed
  versions from the SuSE Linux Standard Server 8 distribution, based on
  United Linux 1.0.
 
  The cronjob error mailed to me is the following:
 
 
  SCRIPT: clean_catman, OK.
  SCRIPT: clean_core, OK.
  SCRIPT: do_mandb, OK.
  SCRIPT: logrotate exited with RETURNCODE = 1.
  SCRIPT: ouput (stdout  stderr) follows
 
   /usr/bin/mysqladmin: refresh failed; error: 'Access denied for user:
  '@localhost' (Using password: NO)'
  error running postrotate script
  Reload syslog service..done
  SCRIPT: logrotate
  --- END OF OUTPUT
 
 
  SCRIPT: slots, OK.
  SCRIPT: ouput (stdout  stderr) follows
 
  psql: could not connect to server: No such file or directory
  Is the server running locally and accepting
  connections on Unix domain socket /tmp/.s.PGSQL.5432?
  SCRIPT: slots
  --- END OF OUTPUT
 
 
  Can somebody please help me out here?  If the /usr/bin/mysqladmin tries
  to use a user from localhost (any) then it fails, because localhost
  doesn't have access to the SQL server...  What user should be granted
  access (and from where) to make this job work correctly?
 
  Anders Norrbring
 
  Norrbring Consulting
  Halmvägen 42
  SE-691 48  Karlskoga
  SWEDEN
 
 
  --
  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]

64bit gcc build on Red Hat Enterprise Linux 3 (RHEL3) for Linux on POWER

[Matthew Davis]

Hello, World.

I successfully built a 64bit MySQL for SuSE Enterprise Linux Server 8 
(SLES8) for Linux on POWER.

The SLES8 gcc implememtation has two separate toolchains for 32 and 64 
bit.  However, RHEL3 has a biarch gcc implementation, where the -m64 flag 
is used to tell the compiler/linker to use 64bit.

When I ran the configure script on RHEL3, with $CFLAGS and $LDFLAGS set to 
-m64 the resulting Makefile has the -m64 flag in CFLAGS, but not the 
various LDFLAGS.  So, when I tried to make the build on RHEL3, the linker 
tried to make 32bit binaries from the 64bit object files.

So, my conclusion is that the configure script isn't applying the 
environment variables to the makefiles properly, which I think is a bug.

Thanks,

Matt

Re: aes encryption bug

[Herb Rubin]

Paul,

Is there a limitation on having a TINYBLOB as a primary key?

Herb


On Thu, 2003-11-06 at 19:24, Paul DuBois wrote:
 At 15:29 -0800 11/6/03, Herb Rubin wrote:
 Paul,
 
 I did try VARCHAR(16) BINARY and it still failed to INSERT in a NOT NULL
 column.
 
 The encrypted string seems to be equivalent to the NULL value even though
 it visually looks like some kind of data.
 
 Herb
 
 Okay, I investigated this further and I believe I know the cause of the
 problem.  The solution is to use a TINYBLOB NOT NULL rather than
 a VARCHAR(16) NOT NULL.
 
 Here's what's going on:
 
 - The particular encrypted value you calculate ends with 0x20.  That is,
the same value as a space character.
 - Trailing spaces are trimmed from values stored in VARCHAR columns.
 - That means when you retrieve the value, it's 15 bytes long, and is no
longer a legal encrypted value.
 - AES_DECRYPT() returns NULL for illegal encrypted values.
 
 Can you instead use CHAR(16) instead of VARCHAR(16)? No, because
 the trailing space would still be trimmed *when the value is retrieved*,
 and you'd still get NULL from AES_DECRYPT().
 
 Use a TINYBLOB instead.  Trailing spaces won't be trimmed when the value
 is stored, or when it is retrieved.  AES_DECRYPT() will work.
 
 In general, you shouldn't try to use CHAR/VARCHAR for encrypted values
 or other forms of binary data. Use BLOB columns instead.
 
 
 
   Please reply to the list, not to me directly, so that others can
   follow this discussion.  Thanks.
 
   At 14:54 -0800 11/6/03, Herb Rubin wrote:
 Paul,
 
 Yes, I get the same, now try and decrypt it, it will turn out to be
 NULL. So, you cannot insert this into a NOT NULL column. It will reject
 it.
 
   Your message (below) appears to be reporting a problem with
   AES_ENCRYPT(). It states that you get a NULL return value from that
   function.
 
   You now appear to be saying something else.
 
   I don't understand what problem you're trying to report.
 
 
   My result from decryption:
 
   mysql select AES_ENCRYPT('551850040',
   '0bf251c9aaf007deaf1143ca1492b561');
   +--+
   | AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561') |
   +--+
   | k\Vx  |
   +--+
   1 row in set (0.00 sec)
 
   mysql select
   AES_DECRYPT(AES_ENCRYPT('551850040',
   '0bf251c9aaf007deaf1143ca1492b561'),
- '0bf251c9aaf007deaf1143ca1492b561');
   +-
 --+
   | AES_DECRYPT(AES_ENCRYPT('551850040',
   '0bf251c9aaf007deaf1143ca1492b561'),
   '0bf251c9aaf007deaf1143ca1492b561') |
   +-
 --+
   | 551850040
   |
   +-
 --+
   1 row in set (0.07 sec)
 
   Are you reporting that you get NULL only in the context of inserting
   and retrieving the value from the id column?
 
   If so, please try using TINYBLOB rather than VARCHAR and see what
   happens.
 
 
 
 we are on 4.0.14
 Herb
 
 
At 14:03 -0800 11/6/03, Herb Rubin wrote:
 Hi,
 
 I am trying to use aes_encrypt and I get a NULL value with a specific
 string:
 
 INSERT INTO test SET
 `id` = AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561');
 
 my field 'id' is VARCHAR(16) NOT NULL
 
 If I change the value or the encryption string it works. But this
 combination turns out to be null and it refuses to insert.
 
With MySQL 4.0.14, 4.0.16, and 4.1.1, I get:
 
mysql select AES_ENCRYPT('551850040',
'0bf251c9aaf007deaf1143ca1492b561');
+--+ |
AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561') |
+--+ |
 k\Vx  |
+--+
 
What version are you using?
 
 
 
 Help!
 
 Herb
 
 
 -- 
 Paul DuBois, Senior Technical Writer
 Madison, Wisconsin, USA
 MySQL AB, www.mysql.com
 
 Are you MySQL certified?  http://www.mysql.com/certification/
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
-- 
Herb Rubin Pathfinders Software
[EMAIL PROTECTED] http://www.pfinders.com
phone: 650-692-9220fax:   650-692-9250


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

sql question

[Chris Edwards]

Hi

Using mysql 3.23.54

I'm trying to join three tables.
categories, topics, posts.

I just want the categories to print out, with the number of topics in each
category, and the number of posts in each topic.

ex output:
Category  |  Topics  |  Posts

Cat One   | 3   |15



I have this:
SELECT forum_categories.id AS  `id` , forum_categories.name AS  `name` ,
forum_categories.createdby AS  `createdby` ,
forum_categories.order AS  `order` ,DATE_FORMAT( forum_categories.created,
%m/%d/%y %l:%i %p  )  AS  `created` ,
COUNT( forum_topics.id )  AS  `topics`, SUM(forum_posts.id) AS `posts`
FROM forum_categories
LEFT JOIN forum_topics ON forum_categories.id = forum_topics.category
LEFT JOIN forum_posts ON forum_topics.id = forum_posts.topic
WHERE forum_categories.domain = 01
GROUP BY forum_categories.id
ORDER BY forum_categories.order ASC

Pulling from:
2 Categories.  There are 3 topics under Category 1, and there are two Posts
for Topic 1, 1 for Topic 2, and 0 for Topic 3.
1 topic under Category 2, and one Post under that Topic.

Gives me:
++-+---+---+---+
+---+
| id  | name| createdby| order   |
created  | topics| posts   |
++-+---+---+---+
+---+
| 29 | Test Category One   | Chris Edwards | 0 | 11/06/03 11:00
|  4   | 6|
| 31 | Test Category Three | Chris Edwards | 1 | 11/06/03 11:00
|  1   | 0|
++-+---+---+---+
+---+

My problem is getting the grouping to work or something.  The query ends up
multiplying the topics and posts to create the post count.  I cannot figure
out how to prevent this.  I need the posts to say 3 and 0, not 6 and 0.

I hope this is enough information.

Thanks.

-- 
Chris Edwards
Web Application Developer
Outer Banks Internet, Inc.
252-441-6698
[EMAIL PROTECTED]
http://www.OuterBanksInternet.com



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

Re: single quotes in database

[Peter Burden]

[EMAIL PROTECTED] wrote:

You can use addslashes and stripslashes when inserting and selecting
respectively.  addslashes will turn your name into O\'connel.  and
stripslashes will bring it back to the displayable format.
check out
php.net/addslashes
php.net/stripslashes
 

An alternative is to use mysql_escape(). If your PHP server has 
magicquotes turned
on you won't need to take any action. I did some notes for students on 
this topic once,
they're at http://www.scit.wlv.ac.uk/~jphb/sst/basics/quoting.html

You can somtimes get quite surprising results by typing a query such as 
O'Malley into
a web based query facility.

hth
Jeff
   
 Steve Buehler 
 [EMAIL PROTECTED]To:   PHP [EMAIL PROTECTED], mysql [EMAIL PROTECTED] 
  cc:  
 11/07/2003 11:30 Subject:  single quotes in database  
 AM
   
   



I am using PHP/MySQL and am having a problem.  We have some names and
addresses in the database that have single quotes in them.  For
instance.  There is a town around here called Lee's Summit.  Also names
like O'connel.  When I pull from the database it skips these because of
the quotes.  I know there is something that I have seen before about this,
but can't find it now.  Can anybody help me?  I really hope this makes
since because I am sick today and am having a hard time thinking
straight.  Is it something that I will have to fix when putting things into
the database?  I am hoping on being able to fix this when going in and when
coming out of the database so that I don't have to go back and redo all the
ones that are already in the database.
Thanks
Steve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




 



--
From Peter Burden, [EMAIL PROTECTED]
http://www.scit.wlv.ac.uk/~jphb



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

Re: performance while creating indexes

[William Baker]

Sorry for the slow reply.  I was battling SCSI controller bugs as well 
as database issues.  I have given up on the software raid for now 
because it is unstable.

Back to the subject at hand: performance.

You are right, the load is meaningless outside the context of a 
specific machine...and often even inside the context of a specific 
machine.  top showed that the system was disk (iowait) bound.  Changing 
the innodb_log_buffer_size (64MB) and innodb_log_file_size (32MB) was 
the trick to increasing performance significantly.  I was able to cut 
index build time in half.

There is still way too much disk activity during the index build.  Since 
the whole file fits in cache at several levels, it makes no sense that 
the CPU still reflects an average of 20% in iowait.  At least user 
space is now around 70% cpu usage, which is up from under 50%.  Other 
than digging into souce code and using strace, I'm clueless as to how to 
improve this situation.  I think it's still way too high.  I've tried 
variations of all the system variables that appear to be relevant.

bbaker

On Mon, Nov 03, 2003 at 01:35:26PM -0600, William Baker wrote:
 

It's hard to tell.  The CPU is under a reasonable load (uptime shows 1.0 
- 2.0), no swapping, and the hard drive is churning away continually.
   

The load average is relatively meaningless.  What's the actualy CPU
utilization as shown by top, vmstat, or similar tools?
 

Is atime being updated continuously?  I'll mount with noatime and try it 
again, though I suspect it is something else.  I forgot to mention file 
system is ext2.
   

That could be, but it should affect things much either.

Jeremy
 



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

Urgent Pl: problem with rename the field name: IN

[karthikeyan]

how to rename a field name IN to something else..
I could not use

alter table tbl_name change IN NEW mediumtext;

Pl. suggest how to rename particular field name for all the tables in
a database

thanks
M.Karthikeyan


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

Re: MySQL benchmarks on the G5: Jaguar vs. Panther

[Jan Pieter Kunst]

At 13:21 -0500 07-11-2003, Gabriel Ricard wrote:

[1.8 GHz G5:]

select: Total time: 1428 wallclock secs (67.50 usr 25.18 sys + 
0.00 cusr  0.00 csys = 92.68 CPU)
[dual 2.0 GHz G5:]

select: Total time: 146 wallclock secs (42.52 usr 33.74 sys +  0.00 
cusr  0.00 csys = 76.26 CPU)

How the select portion of the test could be 10x slower, I'm not 
sure. Have you tuned MySQL at all? key_buffer, etc?


Well, I'm looking for advice on how I should tune MySQL. These are 
the relevant parts of the my.cnf I used:

[mysqld]
skip-locking
set-variable   = key_buffer=64M
set-variable   = max_allowed_packet=1M
set-variable   = table_cache=256
set-variable   = sort_buffer=4M
set-variable   = net_buffer_length=8K
set-variable   = record_buffer=1M
set-variable   = thread_cache=8
set-variable   = myisam_sort_buffer_size=8M
log-bin
server-id   = 1
[mysqldump]
quick
set-variable   = max_allowed_packet=16M
[mysql]
no-auto-rehash
[isamchk]
set-variable   = key_buffer=20M
set-variable   = sort_buffer=20M
set-variable   = read_buffer=2M
set-variable   = write_buffer=2M
[myisamchk]
set-variable   = key_buffer=20M
set-variable   = sort_buffer=20M
set-variable   = read_buffer=2M
set-variable   = write_buffer=2M
If you have any advice on what I could change here to get faster 
selects, I'm all ears.

Thanks in advance,
Jan Pieter Kunst
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: performance while creating indexes

[Jeremy Zawodny]

On Fri, Nov 07, 2003 at 05:03:43PM -0600, William Baker wrote:
 Sorry for the slow reply.  I was battling SCSI controller bugs as well 
 as database issues.  I have given up on the software raid for now 
 because it is unstable.

Really?  I've run Linux software RAID quite happily on several systems
(both RAID-5 and RAID-1) for years.

 Back to the subject at hand: performance.
 
 You are right, the load is meaningless outside the context of a 
 specific machine...and often even inside the context of a specific 
 machine.  top showed that the system was disk (iowait) bound.  Changing 
 the innodb_log_buffer_size (64MB) and innodb_log_file_size (32MB) was 
 the trick to increasing performance significantly.  I was able to cut 
 index build time in half.

Ah, good.

 There is still way too much disk activity during the index build.  Since 
 the whole file fits in cache at several levels, it makes no sense that 
 the CPU still reflects an average of 20% in iowait.  At least user 
 space is now around 70% cpu usage, which is up from under 50%.  Other 
 than digging into souce code and using strace, I'm clueless as to how to 
 improve this situation.  I think it's still way too high.  I've tried 
 variations of all the system variables that appear to be relevant.

Did we already talk about the log flush method you're using with
InnoDB?  I don't recall...

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 55 days, processed 2,061,950,033 queries (433/sec. avg)

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

Re: Very strange problem

[Jeremy Zawodny]

On Fri, Nov 07, 2003 at 09:16:03AM -0800, Eric Anderson wrote:
 
 A little explaination:

[snip]

 The threads connecting from the 1U (192.168.0.2) all start to block up
 as expected, but when the length query on the e450 stops running, the
 threads from the 1U stay blocked, and all NEW threads end up in a
 limbo state:
 
 | 726136 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726135 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726134 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726138 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726140 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726142 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726143 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726148 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726149 | nobody   | localhost   | bm   | Sleep  | 11   
 |  | NULL  |
 | 726150 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726153 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726158 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726161 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726164 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726170 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726172 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726173 | nobody   | localhost   | bm   | Sleep  | 4
 |  | NULL  |
 | 726181 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726185 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726191 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 | 726201 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL 
 | login| NULL  |
 
 No connections from 1U (192.168.0.2) are possible - they just hang, even
 the interactive client just hangs.

That looks a lot like a DNS problem I've seen on FreeBSD.

Do you happen to have skip-name-resovle in the E450's my.cnf?

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 55 days, processed 2,062,011,744 queries (433/sec. avg)

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

Re: Can't set myisam_repair_threads

[Jeremy Zawodny]

On Fri, Nov 07, 2003 at 02:00:39PM +0100, Eric Jain wrote:
 From http://www.mysql.com/doc/en/News-4.0.13.html: Added multi-threaded
 MyISAM repair optimisation and myisam_repair_threads variable to enable
 it.
 
 The variable is also documented in
 http://www.mysql.com/doc/en/SHOW_VARIABLES.html: If this value is
 greater than one, MyISAM table indexes during Repair by sorting process
 will be created in parallel - each index in its own thread.
 
 Unfortunately this variable is neither listed by SHOW VARIABLES, nor can
 I set it:
 
   mysql set myisam_repair_threads=2;
   ERROR 1193: Unknown system variable 'myisam_repair_threads'
 
 I'm using 4.1.0-alpha-standard.
 
 Am I doing something wrong, or was this variable dropped? If so, perhaps
 the documentation should be updated...

It probably wasn't included in 4.1.0 but will be merged into 4.1.1.
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 55 days, processed 2,062,115,445 queries (433/sec. avg)

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

MySQL Success Story

[Aftab Jahan Subedar]

Dear Lists  MySQL,

I have just released the site www.DhakaStockExhcnageGame.com which uses 
MySQL database and CGICC heavily.

I Like MySQL, cuz, I can twist,turn,roll and whatever I want with its 
MySQL C API.

Thank you MySQL, you all are really good. Dont become sooo commercial.

Aftab Jahan Subedar
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
North Jatrabari
Dhaka 1204
Bangladesh
sms://+447765341890
tel://+88027519050
[EMAIL PROTECTED]


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

Re: ROWID

[Jeremy Zawodny]

On Thu, Nov 06, 2003 at 01:02:31PM -0800, Mike Brando wrote:
 
  -Original Message-
  On Thu, Nov 06, 2003 at 09:59:41AM -0800, Wan, Wenhua wrote:
   Hi there,
  
   Both Oracle and Informix use ROWID to uniquely represent the location of
   each row of data in a table.  ROWID is basically a hidden column or
   pseudocolumn for each table, and it is the fastest way to retrive a row
  from
   a table.  Does MySql have similar field?  If is, what's the name and how
  to
   access it?
  
   Thank you very much in advance for your advice.
  
  http://www.mysql.com/doc/search.php?q=rowid
  
 
 
 Ok, so that search produces this:
 
  If the PRIMARY or UNIQUE key consists of only one column and this is of type
 integer, you can also refer to it as _rowid (new in Version 3.23.11).

Yes.

 But that's not what a ROWID is compared to what I think the
 original poster was looking for.

One hopes he figured that out after reading the description.

 In Oracle for example, a ROWID is the unique address of a row in the
 database. Every row, unique key or not has a unique address. Is
 there such a thing in MySQL?

No.  If there was it'd be documented.  But at lesat with MyISAM
tables, MySQL cannot guarantee the address (or row number) of a
row.  An ALTER/REPAIR/OPTIMIZE may reorder the rows.

 ROWIDs are extremely useful for guaranteeing that you are
 manipulating the exact row that you think you are.

So I've been told.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 55 days, processed 2,063,471,142 queries (433/sec. avg)

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

Re: Issues with Mysql 4.0 and PHP in a production environment

[Jeremy Zawodny]

On Fri, Nov 07, 2003 at 07:59:36AM -0600, Luis Lebron wrote:
 Anything that breaks a script (unsupported functions, changes, etc.) or that
 causes performance problems.

I'm still not sure what you're looking for.  A list of known bugs,
maybe?

It's trivial to do something in MySQL that breaks a PHP script--a
poorly written one, but you didn't specify.  As for unuspported
functions, all documented functions in MySQL are supported in the
sense that any bugs in them will be fixed.

 -Original Message-
 From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
 Sent: Thursday, November 06, 2003 4:09 PM
 To: Luis Lebron
 Cc: Mysql (E-mail); Php-General (E-mail)
 Subject: Re: Issues with Mysql 4.0 and PHP in a production environment
 
 
 On Thu, Nov 06, 2003 at 02:10:27PM -0600, Luis Lebron wrote:
  Are there any issues with running PHP 4.3.X and Mysql 4.0 in a production
  environment?
 
 What sort of issues are you looking for?
 
 Jeremy
 -- 
 Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
 [EMAIL PROTECTED]  |  http://jeremy.zawodny.com/
 
 MySQL 4.0.15-Yahoo-SMP: up 53 days, processed 2,012,977,736 queries
 (432/sec. avg)

-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 55 days, processed 2,063,492,319 queries (433/sec. avg)

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

Re: Can't set myisam_repair_threads

[Matt W]

Hi,

Yes, that's what I was gonna say. It's not in 4.1.0 because it was
released *before* 4.0.13.


Matt


- Original Message -
From: Jeremy Zawodny
Sent: Friday, November 07, 2003 6:45 PM
Subject: Re: Can't set myisam_repair_threads


 On Fri, Nov 07, 2003 at 02:00:39PM +0100, Eric Jain wrote:
  From http://www.mysql.com/doc/en/News-4.0.13.html: Added
multi-threaded
  MyISAM repair optimisation and myisam_repair_threads variable to
enable
  it.
 
  The variable is also documented in
  http://www.mysql.com/doc/en/SHOW_VARIABLES.html: If this value is
  greater than one, MyISAM table indexes during Repair by sorting
process
  will be created in parallel - each index in its own thread.
 
  Unfortunately this variable is neither listed by SHOW VARIABLES, nor
can
  I set it:
 
mysql set myisam_repair_threads=2;
ERROR 1193: Unknown system variable 'myisam_repair_threads'
 
  I'm using 4.1.0-alpha-standard.
 
  Am I doing something wrong, or was this variable dropped? If so,
perhaps
  the documentation should be updated...

 It probably wasn't included in 4.1.0 but will be merged into 4.1.1.


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

AMD64 Performance

[Wendell Dingus]

I've recently gotten an AMD Opteron 64-bit machine for MySQL testing and
eventual deployment. I have a web server with MySQL and 48 databases of roughly
36GB total, stored as INNODB. The current server is a dual Xeon 2.8Ghz machine
with 4GB RAM. Since it's 32-bit about 1.2GB is the highest I can get the
innodb_buffer_pool_size set to. On the Opteron I have 8GB RAM total and have the
innodb_buffer_pool_size set to 6GB successfully. The problem is that it's slower
on the machine with more memory and more of the database(s) cached. Hmm... In
both cases I downloaded MySQL 4.0.16 source RPM and built with --target i686 and
x86_64 respectively. Are there build options or parameters which will help or is
this sufficient? The Xeon box runs RedHat 9 and the Opteron has RedHat
Enterprise 3 WS.

Xeon:
1 row in set (55.04 sec)
1 row in set (4.38 sec)
1 row in set (0.17 sec)
1 row in set (12.33 sec)
1 row in set (3.02 sec)

Opteron:
1 row in set (2 min 34.02 sec)
1 row in set (17.66 sec)
1 row in set (0.39 sec)
1 row in set (38.02 sec)
1 row in set (1.06 sec)

Same queries with same data on each. The first 3 were against the same database
and the 4th was after changing to a different database. On the 5th I changed
back to the database the first 3 queries were performed against, I think the 8GB
and larger cache was paying off there. The queries themselves were fairly
complex with a left join and like 'abc%' and other such things against tables
in the one database of ~5M and ~1M records in size.

I'm just wondering if there's some reason why performance on AMD64 is to be
expected to be like this or did I compile MySQL without a correct option or
something like that. Maybe I've got something in my.cnf set _way_ off for this
type of machine?

Here's the my.cnf used on each with only the innodb buffer pool value being
1200M on the 32-bit box:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
default-table-type=innodb
innodb_data_file_path =
ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G;ibdata5:2G;ibdata6:2G;ibdata7:2G;ibdata8:2G;ibdata9
:2G;ibdata10:2G;ibdata11:2G;ibdata12:2G;ibdata13:2G;ibdata14:2G;ibdata15:2G;ibdata16:2G;ibdata17:2G;ibdata18:2G
innodb_data_home_dir = /var/lib/innodb/
set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = /var/lib/iblogs
set-variable = innodb_log_files_in_group=5
set-variable = innodb_log_file_size=100M
set-variable = innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=0
innodb_log_arch_dir = /var/lib/iblogs
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=6200M
set-variable = innodb_additional_mem_pool_size=40M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
set-variable = max_connections=300
set-variable = query_cache_size=16777216
set-variable = key_buffer_size=16777216
tmpdir = /data/tmp
basedir=/var/lib

Thanks in advance! 

PS. After only loading 1 of the databases (albeit a large one ~5GB) on the
Opteron machine it was consistently faster than the other machine. It should
have been even faster than it was in my mind, 5GB of database data should all
have been cached in RAM and been _very_ fast. It was faster, but not as much as
I was expecting.

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

Maybe easy, maybe hard SELECT puzzle :)

[Jonathan Terhorst]

I can't figure out if this is actually challenging or if it's a stupid
question. Table1 is a normal old relation that describes a bunch of objects:
Table1 (id INT PRIMARY KEY NOT NULL, Name varchar(255), Size int) etc.

Associated with each Table1 record is a variable-length of 3-byte
alphanumeric feature codes e.g. A20. I've put these into table called
Features, that is,
Features (id INT PRIMARY KEY NOT NULL REFERENCES Table1, FeatureCode char(3)
NOT NULL);

So a typical record from Table1 might look like:
id: 123
Name: Foo
Size: 42

And the corresponding Features entries:
id: 123
FeatureCode: A01

id: 123
FeatureCode: Z99

id: 123
FeatureCode: X42

My question is how to pull all records from Table1 that have certain
FeatureCodes associate with them. If it's just one FeatureCode (say, 'T56')
I'm looking for then it's easy:
SELECT * from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='T56';

But it I want all records from Table1 that have features 'A01' _and_ 'B02',
clearly
SELECT * from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='A01' AND Features.FeatureCode='B02' doesn't work.

One way I have found to implement this is
SELECT Table1.id from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='A01' OR Features.FeatureCode='B02' GROUP BY Table1.id
HAVING COUNT(*)=2;

e.g. counting the duplications of id and selecting those equal to the number
of FeatureCodes I'm searching for. But this seems somehow inelegant, and I'm
justing wondering if there's a better way that's staring me in the face.
Thanks



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

Strategies for optimizing a read-only table

[Jonathan Terhorst]

I could have sworn I posted this once before, but apparently it got lost somewhere. 
Apologies if you're seeing this twice:

I'm wondering what I can do with MySQL to optimize reads (SELECTs) on a read-only 
table where data will never be INSERTed or UPDATEd. Okay, that's not entirely 
correct--the database will be rebuilt every night but it's small (~20,000 rows) and 
all the writing will take place at once, when the DB is offline to users. In contrast 
we anticipate read activity on the DB to be high, making it worth putting some thought 
into this. So far my only thoughts have been a) myisampack and b) to index every 
single column that our application searches on, since the calculations needed to build 
said indices can be performed once and forgotten. (Disk space isn't really an issue 
but myisampack is said to speed up individual row retrieval.)
Any other ideas? I've searched for a way to manually mark MySQL tables read-only, but 
to no avail. Thanks,

Jonathan
jterhorst [EMAIL PROTECTED] appliedtns {D0T} com

Re: Secure way of storing passwords in the database

[David T-G]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Luis, et al --

...and then Luis Lebron said...
% 
% Any suggestions of a secure way of storing membership passwords (for a
% website) in a mysql database? Should I use sha, aes, des???

Do you really need to be able to decrypt and get the plaintext password?
Why not instead save the encrypted password and then when checking always
encrypt what you're given and compare it?  Not only is it more secure,
it's easier :-)


% 
% thanks,

Sure thing.


% 
% Luis R. Lebron
% Sigmatech, Inc


HTH  HAND

:-D
- -- 
David T-G  * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, Science and Health
http://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE/rHlvGb7uCXufRwARApWPAKC+UEfw8KCw9nlEpEyr+CL4jye+aACfXUn7
wFFJqHnQRb3ejCoheF3mNuU=
=Gefq
-END PGP SIGNATURE-

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

Re: Maybe easy, maybe hard SELECT puzzle :)

[Leo]

But it I want all records from Table1 that have features 'A01' _and_ 'B02',
clearly
SELECT * from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='A01' AND Features.FeatureCode='B02' doesn't work.


okay maybe this one is a more stupid and complex solution :)
but i think it should work (sorry i havent test it yet :p )

select table1.id
from
table1
left join features f1 on table1.id=f1.id
and f1.featurecode='A01'
left join features f2 on table1.id=f2.id
and f1.featurecode='B02'

.
.
. (as many as you like)

where
not isnull(f1.id)
and not isnull(f2.id)
.
.
. (as many as you like)

you can generate the repeatance by some script
hope this help

-Leo-

RE:sql question

[Leo]

SELECT forum_categories.id AS  `id` , forum_categories.name AS  `name` ,
forum_categories.createdby AS  `createdby` ,
forum_categories.order AS  `order` ,DATE_FORMAT( forum_categories.created,%m/%d/%y 
%l:%i %p  )  AS  `created` ,
COUNT( forum_topics.id )  AS  `topics`, SUM(forum_posts.id) AS `posts`
FROM forum_categories
LEFT JOIN forum_topics ON forum_categories.id = forum_topics.category
LEFT JOIN forum_posts ON forum_topics.id = forum_posts.topic
WHERE forum_categories.domain = 01
GROUP BY forum_categories.id
ORDER BY forum_categories.order ASC

imho, you should have a reference field in table topics to table categories
so you wont end up with cross table query (multiplying the result)

LEFT JOIN forum_posts ON forum_topics.id = forum_posts.topic
left join forum_categories on forum_categories.id=forum_posts.id

-Leo-

Does MySQL support hindi language

[Nitin]

Hi all,

I have a project in hand, where I need to accept and show data in Hindi language, 
storage doesn't matter. Is that possible with MySQL, if yes, what do I need to 
configure? How do I do it, any help or ideas?

Regards
Nitin

Re: multitable sum problem

[Leo]

i think you should have a sales person table
that hold unique id for each sales force
such as

salesrep (id,etc..)
| 101 | ... |
| 102 | ... |

so you can alter the query into 
select 
salesrep.id,
sum(ifnull(salesinvoices.invamt,0)) as curramt,
sum(ifnull(lysalesinvoices.lyinvamt,0)) as lyamt
from 
salesrep
left join salesinvoices on salesrep.id=salesinvoices.salesrepid
left join lysalesinvoices on salesrep.id=lysalesinvoices.salesrepid
group by salesrep.id

hope this help..

-Leo-

RE: Secure way of storing passwords in the database

[Erik Osterman]

Right, you should really use a 1-way hashing algorithm like SHA1. If the
user forget's their password, require them to change it.. which is good
practice anyways, since someone else might have found it for them!

The general idea with 1-way hashing algorithms is that you compare the
result of hashing 2 strings. If the result is the same, there is a
ridiculously small chance that the 2 strings are different. 

MySQL provides built-in support for SHA1. 

SHA1(string)

http://www.mysql.com/doc/en/Miscellaneous_functions.html

Example: SELECT * FROM members WHERE id = 123 AND password
=SHA1($password);

$password is the user's input password.


Regards,

Erik Osterman
http://osterman.com/


-Original Message-
From: David T-G [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 07, 2003 9:05 PM
To: mysql users
Cc: Luis Lebron
Subject: Re: Secure way of storing passwords in the database

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Luis, et al --

...and then Luis Lebron said...
% 
% Any suggestions of a secure way of storing membership passwords (for a
% website) in a mysql database? Should I use sha, aes, des???

Do you really need to be able to decrypt and get the plaintext password?
Why not instead save the encrypted password and then when checking always
encrypt what you're given and compare it?  Not only is it more secure,
it's easier :-)


% 
% thanks,

Sure thing.


% 
% Luis R. Lebron
% Sigmatech, Inc


HTH  HAND

:-D
- -- 
David T-G  * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, Science and Health
http://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE/rHlvGb7uCXufRwARApWPAKC+UEfw8KCw9nlEpEyr+CL4jye+aACfXUn7
wFFJqHnQRb3ejCoheF3mNuU=
=Gefq
-END PGP SIGNATURE-

-- 
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]

single quotes in database

[Steve Buehler]

I am using PHP/MySQL and am having a problem.  We have some names and 
addresses in the database that have single quotes in them.  For 
instance.  There is a town around here called Lee's Summit.  Also names 
like O'connel.  When I pull from the database it skips these because of 
the quotes.  I know there is something that I have seen before about this, 
but can't find it now.  Can anybody help me?  I really hope this makes 
since because I am sick today and am having a hard time thinking 
straight.  Is it something that I will have to fix when putting things into 
the database?  I am hoping on being able to fix this when going in and when 
coming out of the database so that I don't have to go back and redo all the 
ones that are already in the database.

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

Re: single quotes in database

[jeffrey_n_Dyke]

 You can use addslashes and stripslashes when inserting and selecting
respectively.  addslashes will turn your name into O\'connel.  and
stripslashes will bring it back to the displayable format.

check out
php.net/addslashes
php.net/stripslashes

hth
Jeff


   
 
  Steve Buehler
 
  [EMAIL PROTECTED]To:   PHP [EMAIL PROTECTED], 
mysql [EMAIL PROTECTED] 
   cc: 
 
  11/07/2003 11:30 Subject:  single quotes in database 
 
  AM   
 
   
 
   
 




I am using PHP/MySQL and am having a problem.  We have some names and
addresses in the database that have single quotes in them.  For
instance.  There is a town around here called Lee's Summit.  Also names
like O'connel.  When I pull from the database it skips these because of
the quotes.  I know there is something that I have seen before about this,
but can't find it now.  Can anybody help me?  I really hope this makes
since because I am sick today and am having a hard time thinking
straight.  Is it something that I will have to fix when putting things into
the database?  I am hoping on being able to fix this when going in and when
coming out of the database so that I don't have to go back and redo all the
ones that are already in the database.

Thanks
Steve


--
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]