from:"Josh"


Good afternoon,

I have recently converted a large table from MyISAM to InnoDB and am 
experiencing severe performance issues because of it.  HTTP response 
times have gone from avg .25 seconds to avg 2-3 seconds.  Details follow:


PHP/MySQL website, no memcached, 3 web nodes that interact with DB, one 
that serves images, one master DB that serves all reads/writes, backup 
DB that only serves for backup/failover at this time (app being changed 
to split reads/writes, not yet).


The one table that I converted is 130M rows, around 10GB data MyISAM to 
22GB InnoDB.  There are around 110 tables on the DB total.



My.cnf abbreviated settings:

[mysqld]
port  = 3306
socket  = /tmp/mysql.sock
skip-locking
key_buffer= 3G
sort_buffer_size  = 45M
max_allowed_packet  = 16M
table_cache = 2048

tmp_table_size= 512M
max_heap_table_size = 512M

myisam_sort_buffer_size = 512M
myisam_max_sort_file_size = 10G
myisam_repair_threads   = 1
thread_cache_size   = 300

query_cache_type  = 1
query_cache_limit = 1M
query_cache_size  = 600M

thread_concurrency  = 8
max_connections   = 2048
sync_binlog = 1

innodb_buffer_pool_size = 14G
innodb_log_file_size  = 20M
innodb_flush_log_at_trx_commit=1
innodb_flush_method = O_DIRECT
skip-innodb-doublewrite
innodb_support_xa = 1
innodb_autoextend_increment = 16
innodb_data_file_path   = ibdata1:40G:autoextend

We're seeing a significantly higher percentage of IO wait on the system, 
 averaging 20% now with the majority of that being user IO.  The system 
is not swapping at all.


Any ideas for what to check or modify to increase the performance here 
and let MyISAM and InnoDB play better together?  The plan is to convert 
all tables to InnoDB which does not seem like a great idea at this 
point, we're considering moving back to MyISAM.


Thanks!
Josh Miller, RHCE

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

Re: innodb/myisam performance issues


Tom Horstmann wrote:

Hello Josh,

why you moved your table to InnoDB? Your description doesn't sound like the
tables rows
are accessed concurrently and need to be locked? Are you sure you need
InnoDB for this table?

If you need InnoDB you probably need to redesign your queries and table
structure to get them
more convenient for InnoDB.



Hi Tom,

The rows in this table are accessed concurrently as any activity on the 
site is recorded/added/updated to this table.  We have several others 
which serve similar purposes, (sessions, totaltraffic, etc...).


I don't disagree, the application needs to be written to perform better 
and use MySQL more efficiently.  I need to find a way to make it work 
better in the interim :)


Thanks!
Josh Miller, RHCE

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

Re: innodb/myisam performance issues


Tom Horstmann wrote:

Addendum..
Please also try increasing your innodb_log_file_size to a much higher value
if you
have lots of writes/transactions. Maybe 250MB is a good first try. 
You need to delete/move the InnoDB logs before restart.


Not sure about this, but please also set innodb_log_buffer_size. Try
something
between 16-32MB if you have many transactions.


Ok, we've increased the innodb_log_file_size to 500M, and that has not 
changed the IO wait at all so far (after 1 hour).



Thanks!
Josh Miller, RHCE

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

Re: innodb/myisam performance issues


Perrin Harkins wrote:

What you really need to do is look at which queries are slow and run
EXPLAIN plans for them.  Most big performance problems like you're
describing are due to index issues, so that's where you should be
looking.  Server tuning comes lat



We definitely need to work on re-designing the queries and indexes.  We 
have a less than 50% index usage rate which is disastrous.


We'd like to prove InnoDB and move onto that storage engine for the 
transaction support, MVCC, etc.. but we're finding that performance is poor.


Thanks!
Josh Miller, RHCE

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

Configuration and improvement advice.

2008-07-18 Thread Josh Miller

I have recently become responsible for a LAMP site which has a decent 
MySQL install (v5.0.24a).  The database is around 40GB with a single 
master to single slave replication scheme, although all activity goes to 
the master at this time, with the exception of backups which are taken 
from the slave.


I have several tables which are fairly large, one has 120 million 
records, and I need to migrate these tables to InnoDB from MyISAM to 
reduce the number of table locks that occur on a daily basis which bring 
down the site's performance.


What is the best way to perform this migration?  Should I simply take an 
outage and alter table to set the engine type to InnoDB, or should I 
rename the table, and select into a new table?


What are the upper limits of MySQL performance in terms of data set size 
using MyISAM vs InnoDB?


TIA,
--
Josh Miller, RHCE

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

Re: Table Size

2007-11-02 Thread Josh

Thanks for all of your help/information.  One additional question...  do NULL 
values take up any space?

For example, if I have a column defined as:
repAccess char(1) default null

When a user should have access to run a particular report, repAccess will be 
set to 'T'.  If not, it is left null.

In this example, the rows with 'T' will occupy an additional 1 btye for storing 
the single character, however, for rows with null... does that take up space?  
I'm only asking to give me an idea of what sort of space NULL values take up.  
One of my tables has hundreds of thousands of rows and could potentially have 
many null values... I'm trying to get an idea of whether or not those null 
values are taking up much space.

Thanks.


- Original Message 
From: Baron Schwartz [EMAIL PROTECTED]
To: Dan Nelson [EMAIL PROTECTED]
Cc: Josh [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Sunday, October 28, 2007 9:25:11 AM
Subject: Re: Table Size

Dan Nelson wrote:
 In the last episode (Oct 27), Baron Schwartz said:
 InnoDB has the following extra things, plus some things I might forget:

 a) the primary key B-Tree
 b) row versioning information for every row
 c) 16k page size; each page might not be completely full

 Those are all counted towards the table size..  Actually, the primary
 key B-Tree might not be; I'd need to look that up.  But I think it
 is. H.  I just tested -- yes, the PK counts towards table size.
 
 In fact, in InnoDB, all indexes count towards table size, since there
 is a single .ibd file for the whole thing.  So you've got the space
 taken up by your `repid` index to consider as well..

It's true they're in the same file, but the secondary indexes show up in 
the 'Index_length' column in SHOW TABLE STATUS.  I was double-checking 
that the primary key contributes to the 'Data_length' column, not the 
'Index_length' column.

Table Size

2007-10-27 Thread Josh

Hello,

I have a database that is growing at a rate of 4-5 MB per day (that number is 
getting larger as well).  Not too bad but I'm trying to clean up the tables to 
minimize the amount of space they take up.

I have one particular table that has 2 columns:
rolID  int(10) unsigned
repID  int(10) unsigned

Both are foreign keys to other tables.

A show table status shows that this table has a Data_length=1081344 bytes.
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that 
integers take up 4 bytes of space.
There are currently 10472 rows in the table.

10472 x 4 x 2 = 83776

What am I calculating wrong?  Why is the Data_length value so much larger?

Thanks.

Re: Table Size

2007-10-27 Thread Josh

mysql show create table UserReports\G
*** 1. row ***
   Table: UserReports
Create Table: CREATE TABLE `UserReports` (
  `rolID` int(10) unsigned NOT NULL,
  `repID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`rolID`,`repID`),
  KEY `repID` (`repID`),
  CONSTRAINT `UserReports_ibfk_1` FOREIGN KEY (`rolID`) REFERENCES `UserRoles` 
(`rolID`) ON DELETE CASCADE,
  CONSTRAINT `UserReports_ibfk_2` FOREIGN KEY (`repID`) REFERENCES `Reports` 
(`repID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)



- Original Message 
From: Baron Schwartz [EMAIL PROTECTED]
To: Josh [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Saturday, October 27, 2007 10:17:32 AM
Subject: Re: Table Size

Josh wrote:
 Hello,
 
 I have a database that is growing at a rate of 4-5 MB per day (that number is 
 getting larger as well).  Not too bad but I'm trying to clean up the tables 
 to minimize the amount of space they take up.
 
 I have one particular table that has 2 columns:
 rolID  int(10) unsigned
 repID  int(10) unsigned
 
 Both are foreign keys to other tables.
 
 A show table status shows that this table has a Data_length=1081344 bytes.
 http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that 
 integers take up 4 bytes of space.
 There are currently 10472 rows in the table.
 
 10472 x 4 x 2 = 83776
 
 What am I calculating wrong?  Why is the Data_length value so much larger?

It depends on the storage engine, indexes, and possibly other things. 
What's the output of SHOW CREATE TABLE for this table?  If you can't 
tell us that, what's the storage engine?

(yes, I know Index_length is separate, but humor me).

Baron

Re: Table Size

2007-10-27 Thread Josh

Forgot to send the rest of 'show table status'

   Name: UserReports
 Engine: InnoDB
Version: 10
 Row_format: Compact
   Rows: 10388
 Avg_row_length: 104
Data_length: 1081344
Max_data_length: 0
   Index_length: 212992
  Data_free: 0
 Auto_increment: NULL
Create_time: 2007-05-19 21:17:58
Update_time: NULL
 Check_time: NULL
  Collation: latin1_swedish_ci
   Checksum: NULL



- Original Message 
From: Baron Schwartz [EMAIL PROTECTED]
To: Josh [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Saturday, October 27, 2007 10:17:32 AM
Subject: Re: Table Size

Josh wrote:
 Hello,
 
 I have a database that is growing at a rate of 4-5 MB per day (that number is 
 getting larger as well).  Not too bad but I'm trying to clean up the tables 
 to minimize the amount of space they take up.
 
 I have one particular table that has 2 columns:
 rolID  int(10) unsigned
 repID  int(10) unsigned
 
 Both are foreign keys to other tables.
 
 A show table status shows that this table has a Data_length=1081344 bytes.
 http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html shows that 
 integers take up 4 bytes of space.
 There are currently 10472 rows in the table.
 
 10472 x 4 x 2 = 83776
 
 What am I calculating wrong?  Why is the Data_length value so much larger?

It depends on the storage engine, indexes, and possibly other things. 
What's the output of SHOW CREATE TABLE for this table?  If you can't 
tell us that, what's the storage engine?

(yes, I know Index_length is separate, but humor me).

Baron

resources for tuning

2006-09-12 Thread Josh Trutwin

Hi,

We just upgraded our server's RAM from 1 GB to 5 GB - I'd like to
now make some adjustments in my.cnf to better tune my server to
this new memory amount.  I was looking in the MySQL 4.1.21 source
dir's support-files at the example my.cnf files provided with the
distribution, but these seem horribly out-dated.  (The my-large.cnf
assumes you have a whopping 512 mb of memory).

I was thinking of modelling my.cnf after the provided
my-innodb-heavy.cnf file, but I am worried these files might
be out-dated so I figured I'd ping the mailing list for other
resources for tuning? 

Thanks,

Josh

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

logging

2006-08-13 Thread Josh Milane

Hello,

I realize that this is a silly question, but I cannot figure it out. I
don't know why. Id really appreciate your help.

I cannot get mysql to do general logging. My my.ini reads like this:

--

#This File was made using the WinMySQLAdmin 1.4 Tool
#8/4/2006 9:31:16 PM

#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions

[mysqld]
log=C:/development/xampp/mysql/data/hostname.log
basedir=C:/development/xampp/mysql
#bind-address=192.168.1.222
datadir=C:/development/xampp/mysql/data
#language=C:/XAMPP/xampp/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
[WinMySQLadmin]
Server=C:/development/xampp/mysql/bin/mysqld-nt.exe
user=root
password=

--

I start the service, no hostname.log is created. Even if I don't run it
as a service, it does not work. 

Permissions are set... this is my machine at home and I am running as
Admin. Even if I create the file manually, it doesn't get updated.

For one second, unexplicably, it DID log one line. Then it stopped and
did no more. This is happening here at home and at work... and I need to
be able to see the sql being executed on my server.

The error log is working fine. 

Any ideas? I sure would appreciate it.



-Original Message-
From: Steffan A. Cline [mailto:[EMAIL PROTECTED] 
Sent: Sunday, August 13, 2006 5:34 AM
To: mysql@lists.mysql.com
Subject: Group by with an IF

I have the following query:

select *, 
if( season_week_date = 2006-08-16, on, off ) as stat,
sum(overall_points) as total_points
from rosters r 
left join celebs c 
on c.celeb_id = r.celeb_id
where 
season_id=5062
and user_id=1
group by r.celeb_id
order by overall_rank, ln, fn;

It almost works as expected however the problem I am concerned about is
that
I never get a stat saying On after it resolves. Removing the sum() and
adding group I get the total_points as expected but now the results are
always off.

Can anyone shed any light on this one?


Thanks

Steffan

---
T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net  USA
AIM : SteffanC  ICQ : 57234309
  Lasso Partner Alliance Member
---




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

silly trigger question

2006-08-13 Thread Josh Milane

Hi,

Would something like this make it so that every time there was a new row
inserted on TEST, a row with the new values was inserted on LOG_TABLE? I
am not sure if you can reference NEW.id and all the NEW values directly
and send them in a trigger... Thanks...

CREATE TRIGGER test.data_table_au
AFTER UPDATE ON test.data_table
FOR EACH ROW
BEGIN

INSERT INTO log_table
(id_data, old_d1, new_d1, old_d2, new_d2, kind_of_change, ts)
VALUES
(new.id, old.d1, new.d1, old.d2, new.d2, 'update', now());
END 

And could I go ahead and update two tables in the same trigger or am I
better off making the second trigger after update on the second table?



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

query logging is making me mental!

2006-08-08 Thread Josh Milane

Hello Everyone, 

 

I am hoping that someone can help me. Before I jump off the roof!

 

I installed MYSQL 5 along with PHP and apache 2 as part of the XAMPP
stack put out by ApacheFriends. I am putting together a few CMS sites
and think the product is great.

 

But I am having a simple problem and need help.

 

Obviously, I installed everything as admin on my local machine. MYSQL is
running as a service. I don't know what this means, but I know it is a
fact.

 

Here is my my.ini:

 

#This File was made using the WinMySQLAdmin 1.4 Tool

#8/8/2006 9:28:51 AM

 

#Uncomment or Add only the keys that you know how works.

#Read the MySQL Manual for instructions

 

[mysqld]

basedir=C:/apachefriends/xampp/mysql

#bind-address=192.168.1.75

datadir=C:/apachefriends/xampp/mysql/data

#language=C:/apachefriends/xampp/mysql/share/your language directory

#slow query log#=

#tmpdir#=

#port=3306

#set-variable=key_buffer=16M

log=hostname.log

[WinMySQLadmin]

Server=C:/apachefriends/xampp/mysql/bin/mysqld.exe

user=x

password=

 

But here is a portion of my Server Variables 

 

(I am getting these from the Report in WinMYSQLAdmin)

 

log OFF

log_bin  OFF

log_bin_trust_function_creatorsOFF

log_error .\HomeDesktop-24.err

 

Why is my logging OFF?

 

How do I turn it ON?

 

I thought that as long as I had the log=hostname.log in my my.ini that
it would record all sql statements executed?

 

Could this be a permissions thing? It happens even when I run everything
as admin.

 

Help?

 

If I was not bald I would be pulling my hair out.

 

Thank you very much...

RE: query logging is making me mental!

2006-08-08 Thread Josh Milane

I figured it out. Thanks for listening. I maybe just needed to vent. 

-Original Message-
From: Josh Milane [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 08, 2006 12:46 PM
To: mysql@lists.mysql.com
Subject: query logging is making me mental!

Hello Everyone, 

I am hoping that someone can help me. Before I jump off the roof!

I installed MYSQL 5 along with PHP and apache 2 as part of the XAMPP
stack put out by ApacheFriends. I am putting together a few CMS sites
and think the product is great.

But I am having a simple problem and need help.

Obviously, I installed everything as admin on my local machine. MYSQL is
running as a service. I don't know what this means, but I know it is a
fact.

Here is my my.ini:

#This File was made using the WinMySQLAdmin 1.4 Tool

#8/8/2006 9:28:51 AM

#Uncomment or Add only the keys that you know how works.

#Read the MySQL Manual for instructions

[mysqld]

basedir=C:/apachefriends/xampp/mysql

#bind-address=192.168.1.75

datadir=C:/apachefriends/xampp/mysql/data

#language=C:/apachefriends/xampp/mysql/share/your language directory

#slow query log#=

#tmpdir#=

#port=3306

#set-variable=key_buffer=16M

log=hostname.log

[WinMySQLadmin]

Server=C:/apachefriends/xampp/mysql/bin/mysqld.exe

user=x

password=

But here is a portion of my Server Variables 

(I am getting these from the Report in WinMYSQLAdmin)

log OFF

log_bin  OFF

log_bin_trust_function_creatorsOFF

log_error .\HomeDesktop-24.err

Why is my logging OFF?

How do I turn it ON?

I thought that as long as I had the log=hostname.log in my my.ini that
it would record all sql statements executed?

Could this be a permissions thing? It happens even when I run everything
as admin.

Help?

If I was not bald I would be pulling my hair out.

Thank you very much...

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

Re: SQL Plus (oracle) and SQL (mysql)

2006-07-31 Thread Josh Trutwin

On Mon, 31 Jul 2006 06:26:01 -0700 (PDT)
Phong Nguyen [EMAIL PROTECTED] wrote:

 My question is if we decide to switch server 1 to
 server 2 (oracle database to mysql) or server 2 to
 server1 (mysql to oracle). Then, what happen to
 sqlplus (oracle) and sql (mysql) using with
 application. You can say what are # between sqlplus
 (oracle) and sql (mysql)

Well, sqlplus is just an Oracle client program that lets you run
SQL queries against the Oracle db.  I think what you are really
after is what the differences between Oracle SQL and MySQL SQL are.

A couple things come to mind:

1.) For CREATE TABLE, Oracle uses VARCHAR2, MySQL has VARCHAR

2.) For MySQL you need to create your tables as InnoDB tables if
you are using transactions or foriegn keys (which I hope you are
for a production application)

3.) MySQL does not support Oracle's notion of Sequences - in MySQL
you do have AUTO_INCREMENT columns, but they are not as robust as
sequences.

4.) MySQL does not support CHECK constraints inside CREATE TABLE
clauses.

I'm sure there are others, but as long as your application uses
pretty generic SQL and you are not getting into Oracle-specific
stuff you should be ok.

Josh

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

Sum and Total Query

2006-07-16 Thread Josh

What's the proper way to query for a total for a value
and also the total for all values for a particular set
of parameters?

For example, I have a table of orders that customer
service reps make. The reps place multiple orders per
day for various products. I'm trying to pull a report
that displays the customer service ID (csID), total
number of a particular product sold, and the total of
all products sold... within a date range.

Sample Orders Table (heavily snipped):


ORDER_ID  CS_ID  PRO_ID  QTY_SOLD  DATE
  -  --    -
1 10 105 3 2008-07-06
2 12 105 4 2008-07-10
3 10 105 3 2008-07-10
3 10 120 2 2008-07-10
4 12 105 1 2008-07-11

When querying for the totals between July 8-July 11,
the query should return something like:


CS_ID  PRO_ID  PRO_TOTAL  ALL_TOTAL
-  --  -  -
10 105 6  8
10 120 2  8
12 105 5  5

I've tried various queries that work when I sum up
individually, but when I include 2 sums for a row I'm
getting duplicates and the sums are too high.

This seems like it would be fairly straightforward but
apparently I'm overlooking a key item.  Oracle has a
handy OVER() function that would work...

Any thoughts?

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

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

Re: Sum and Total Query

2006-07-16 Thread Josh

Sorry...  The dates were incorrect in my previous
post.  They are corrected below.

--- Josh [EMAIL PROTECTED] wrote:

 What's the proper way to query for a total for a
 value
 and also the total for all values for a particular
 set
 of parameters?
 
 For example, I have a table of orders that customer
 service reps make. The reps place multiple orders
 per
 day for various products. I'm trying to pull a
 report
 that displays the customer service ID (csID), total
 number of a particular product sold, and the total
 of
 all products sold... within a date range.
 
 Sample Orders Table (heavily snipped):
 
 
 ORDER_ID  CS_ID  PRO_ID  QTY_SOLD  DATE
   -  --    -
 1 10 105 3 2006-07-08
 2 12 105 4 2006-07-10
 3 10 105 3 2006-07-10
 3 10 120 2 2006-07-10
 4 12 105 1 2006-07-11
 
 When querying for the totals between July 8-July 11,
 the query should return something like:
 
 
 CS_ID  PRO_ID  PRO_TOTAL  ALL_TOTAL
 -  --  -  -
 10 105 6  8
 10 120 2  8
 12 105 5  5
 
 I've tried various queries that work when I sum up
 individually, but when I include 2 sums for a row
 I'm
 getting duplicates and the sums are too high.
 
 This seems like it would be fairly straightforward
 but
 apparently I'm overlooking a key item.  Oracle has a
 handy OVER() function that would work...
 
 Any thoughts?
 
 __
 Do You Yahoo!?
 Tired of spam?  Yahoo! Mail has the best spam
 protection around 
 http://mail.yahoo.com 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:   

http://lists.mysql.com/[EMAIL PROTECTED]
 
 


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

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

Re: Compare lists Query?

2006-03-13 Thread Josh

You could rotate the output... basically get 1 row
with 11 columns (CA01_count,CA02_count,...,CA12_count)
(leaving out CA10) joining all 12 tables together...

Or...  perhaps we can help with the timestamp issues
you are having and get you upgraded to later version
of mysql that supports nested SELECT statements.

--- Yesmin Patwary [EMAIL PROTECTED] wrote:

 Dear All,
   
 I had some issues in past with timestamp fields as a
 result I am unable to upgrade to mysql 4.1 version. 
 I am sure below the query recommended by Josh works
 with 4.1 or above. Would it be possible to rewrite
 this query for 3.23 version? 
   
 Again, thank you Josh and all others for your kind
 help and comments.
 
 Josh [EMAIL PROTECTED] wrote:
   Here's one method:
 
 SELECT cl1.list_name, count(*) as count
 FROM customerList cl1
 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2
 WHERE cl2.list_name='CA10')
 and cl1.list_name != 'CA10'
 GROUP BY cl1.list_name
 
 --- Yesmin Patwary wrote:
 
  Good morning all,
  
  We have 12 customer lists: CA01, CA02, .,CA12. 
  
  Table: customerList
  +---+--+
  | list_name | id |
  +---+--+
  | CA10 | 20BE |
  | CA07 | 20BE |
  | CA11 | 20BE |
  | CA03 | 20BE |
  | CA10 | NQCR |
  | CA04 | NQCR |
  | CA02 | MVYK |
  | CA10 | 0BEC |
  | AND SO ON. |
  +---+--+
  
  Each list has 25 to 350 customers. Same
  customer_id may exist in multiple lists. We need
 to
  compare CA10 list customer_ids with other 11
 lists
  to find matching id count by list_name. The query
  output should be something similar below:
  +--+---+
  | list_name |count |
  +--+---+
  | CA05 | 60 |
  | CA07 | 42 |
  | CA01 | 35 |
  | CA03 | 28 |
  | CA09 | 15 |
  | AND SO ON |
  +---+--+
  
  Can this be done with a SELECT statement without
  using perl or php? 
  
  Thanks in advance for any help.
 
   
 -
 Yahoo! Mail
 Bring photos to life! New PhotoMail  makes sharing a
 breeze. 


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

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

Re: Problems with timestamp field after upgrading MySQL Server.

2006-03-13 Thread Josh

Sure is...

SELECT DATE_FORMAT(dateField,'%Y%m%d') as dateField

Take a look at:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

-Josh

--- Yesmin Patwary [EMAIL PROTECTED] wrote:

 Dear All,

   First of all, I would like to thank to Josh and
 Peter Brawley for their kind help on previous issue.
 

   Here is another Issue:
   There are many tables that have timestamp field
 with 8 char (mmdd). I use this format all over
 our website and to create various reports.  Below is
 static php code that I used numerous places:
   ? 
$year=substr($date, 0, 4);
$month=substr($date, 4, 2);
$day=substr($date, 6, 2); 
   ?

   All timestamp dependent reports and web pages had
 problems, once I upgrade to MySQL  4.1.12.  At the
 end I had to downgrade to 3.23 to bring web site
 operation to normal. Is there anyway to preserve old
 timestamp format after upgrading?
   
 MySQL - 3.23
 +++--
 |  Field |   Type | Default (mmdd)
 +++--
 |log_date|timestamp(8)| 
   
 MySQL - 4.1.12
 ++---+
 |  Field |   Type| Default 
 ++---+
 |log_date| timestamp |-00-00 00:00:00

   
 Josh [EMAIL PROTECTED] wrote:
   You could rotate the output... basically get 1 row
 with 11 columns
 (CA01_count,CA02_count,...,CA12_count)
 (leaving out CA10) joining all 12 tables together...
 
 Or... perhaps we can help with the timestamp issues
 you are having and get you upgraded to later version
 of mysql that supports nested SELECT statements.
 
 --- Yesmin Patwary wrote:
 
  Dear All,
  
  I had some issues in past with timestamp fields as
 a
  result I am unable to upgrade to mysql 4.1
 version. 
  I am sure below the query recommended by Josh
 works
  with 4.1 or above. Would it be possible to rewrite
  this query for 3.23 version? 
  
  Again, thank you Josh and all others for your kind
  help and comments.
  
  Josh wrote:
  Here's one method:
  
  SELECT cl1.list_name, count(*) as count
  FROM customerList cl1
  WHERE cl1.id IN (SELECT cl2.id FROM customerList
 cl2
  WHERE cl2.list_name='CA10')
  and cl1.list_name != 'CA10'
  GROUP BY cl1.list_name
  
  --- Yesmin Patwary wrote:
  
   Good morning all,
   
   We have 12 customer lists: CA01, CA02, .,CA12. 
   
   Table: customerList
   +---+--+
   | list_name | id |
   +---+--+
   | CA10 | 20BE |
   | CA07 | 20BE |
   | CA11 | 20BE |
   | CA03 | 20BE |
   | CA10 | NQCR |
   | CA04 | NQCR |
   | CA02 | MVYK |
   | CA10 | 0BEC |
   | AND SO ON. |
   +---+--+
   
   Each list has 25 to 350 customers. Same
   customer_id may exist in multiple lists. We need
  to
   compare CA10 list customer_ids with other 11
  lists
   to find matching id count by list_name. The
 query
   output should be something similar below:
   +--+---+
   | list_name |count |
   +--+---+
   | CA05 | 60 |
   | CA07 | 42 |
   | CA01 | 35 |
   | CA03 | 28 |
   | CA09 | 15 |
   | AND SO ON |
   +---+--+
   
   Can this be done with a SELECT statement without
   using perl or php? 
   
   Thanks in advance for any help.
  
  
  -
  Yahoo! Mail
  Bring photos to life! New PhotoMail makes sharing
 a
  breeze. 
 
 
 __
 Do You Yahoo!?
 Tired of spam? Yahoo! Mail has the best spam
 protection around 
 http://mail.yahoo.com 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:

http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
   
 -
  Yahoo! Mail
  Use Photomail to share photos without annoying
attachments.


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

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

Re: Stored Procedure Problem

2006-03-10 Thread Josh

Peter, nice catch.  Changing the parameter names did
the trick.  Thanks.

--- Peter Brawley [EMAIL PROTECTED] wrote:

 Josh wrote:
  I posted this same item on the mysql forum but the
  only place that looked remotely appropriate was
 under
  the Newbie section...  I'm not sure if it will be
  answer there so I thought I might toss it out here
 to
  see if there were any takers.
 
  I'm baffled as to why this stored procedure is
 acting
  this way.  See the below sample table and
 examples.

 The query as provided doesn't parse--chgID doesn't
 exist.
 
 Did you try naming the sproc params differently from
 the corresponding 
 columns, eg pEMPID, pBDID?
 
 PB
 
 -
 
  mysql select * from Rates;
 

+--+--+---+-+---+
  | rtID | bdID | empID | rtStartDate |
 rtBillingRate |
 

+--+--+---+-+---+
  |1 | NULL |  NULL | -00-00  | 
 0.00 |
  |2 | NULL | 1 | 2004-01-01  | 
 2.00 |
  |3 | NULL | 1 | 2004-05-10  | 
 4.00 |
  |4 | NULL | 1 | 2005-01-10  | 
 6.00 |
  |5 | NULL | 1 | 2005-04-12  | 
 8.00 |
  |6 | NULL | 1 | 2006-01-02  |
 10.00 |
  |8 |   37 | 1 | 2005-10-01  |
 25.00 |
 

+--+--+---+-+---+
 
  DELIMITER $
  CREATE PROCEDURE test_rate (EMPID int, BDID int,
  CURRENTDATE date)
  BEGIN
  SELECT
 

COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate)
  AS rtBillingRate
FROM Rates rt1
  LEFT JOIN Rates rt2 ON (rt2.empID=EMPID
 and
  rt2.chgID IS NULL and rt2.bdID IS NULL and
  rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM
 Rates
  WHERE empID=EMPID and chgID IS NULL and bdID IS
 NULL
  and rtStartDate = CURRENTDATE))
  LEFT JOIN Rates rt3 ON (rt3.empID=EMPID
 and
  rt3.chgID IS NULL and rt3.bdID=BDID and
  rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM
 Rates
  WHERE empID=EMPID and chgID IS NULL and bdID=BDID
 and
  rtStartDate = CURRENTDATE))
   WHERE rt1.empID IS NULL
 and rt1.chgID IS NULL;
   
  END$
  DELIMITER ;
 
  mysql call test_rate (1,NULL,'2005-09-01');
  +---+
  | rtBillingRate |
  +---+
  |  8.00 |
  +---+
  CORRECT!
 
  mysql call test_rate (1,37,'2005-10-10');
  +---+
  | rtBillingRate |
  +---+
  | 25.00 |
  +---+
  CORRECT!
 
  mysql call test_rate (1,NULL,'2005-10-10');
  +---+
  | rtBillingRate |
  +---+
  |  0.00 |
  +---+
  1 row in set (0.01 sec)
  WRONG! This should have returned 8.00.
 
  When I run this query by itself (outside the
  procedure) I get the correct result: (notice I'm
  plugging in EMPID, BDID, and CURRENTDATE
 parameters)
 
  SELECT
 

COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate)
  AS rtBillingRate
FROM Rates rt1
  LEFT JOIN Rates rt2 ON (rt2.empID=1 and
  rt2.chgID IS NULL and rt2.bdID IS NULL and
  rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM
 Rates
  WHERE empID=1 and chgID IS NULL and bdID IS NULL
 and
  rtStartDate = '2005-10-10'))
  LEFT JOIN Rates rt3 ON (rt3.empID=1 and
  rt3.chgID IS NULL and rt3.bdID=NULL and
  rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM
 Rates
  WHERE empID=1 and chgID IS NULL and bdID=NULL and
  rtStartDate = '2005-10-10'))
   WHERE rt1.empID IS NULL
 and rt1.chgID IS NULL;
 
  +---+
  | rtBillingRate |
  +---+
  |  8.00 |
  +---+
  1 row in set (0.00 sec)
  CORRECT!
 
  What's going wrong in the stored procedure?
 
  __
  Do You Yahoo!?
  Tired of spam?  Yahoo! Mail has the best spam
 protection around 
  http://mail.yahoo.com 
 

 
 
 -- 
 No virus found in this outgoing message.
 Checked by AVG Free Edition.
 Version: 7.1.375 / Virus Database: 268.2.0/275 -
 Release Date: 3/6/2006
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:   

http://lists.mysql.com/[EMAIL PROTECTED]
 
 


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

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

Re: Compare lists Query?

2006-03-10 Thread Josh

Here's one method:

SELECT cl1.list_name, count(*) as count
  FROM customerList cl1
 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2
WHERE cl2.list_name='CA10')
  and cl1.list_name != 'CA10'
GROUP BY cl1.list_name

--- Yesmin Patwary [EMAIL PROTECTED] wrote:

 Good morning all,

   We have 12 customer lists: CA01, CA02, .,CA12. 

   Table: customerList
 +---+--+
 | list_name |  id  |
 +---+--+
 |CA10   | 20BE |
 |CA07   | 20BE |
 |CA11   | 20BE |
 |CA03   | 20BE |
 |CA10   | NQCR |
 |CA04   | NQCR |
 |CA02   | MVYK |
 |CA10   | 0BEC |
 |AND SO ON.   |
 +---+--+

   Each list has 25 to 350 customers.  Same
 customer_id may exist in multiple lists. We need to
 compare CA10 list customer_ids with other 11 lists
 to find matching id count by list_name.  The query
 output should be something similar below:
 +--+---+
 | list_name |count |
 +--+---+
 |CA05   |  60  |
 |CA07   |  42  |
 |CA01   |  35  |
 |CA03   |  28  |
 |CA09   |  15  |
 |AND SO ON   |
 +---+--+

   Can this be done with a SELECT statement without
 using perl or php?  

   Thanks in advance for any help.
 
   
 -
 Relax. Yahoo! Mail virus scanning helps detect nasty
viruses!


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

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

Stored Procedure Problem

2006-03-09 Thread Josh

I posted this same item on the mysql forum but the
only place that looked remotely appropriate was under
the Newbie section...  I'm not sure if it will be
answer there so I thought I might toss it out here to
see if there were any takers.

I'm baffled as to why this stored procedure is acting
this way.  See the below sample table and examples.

mysql select * from Rates;
+--+--+---+-+---+
| rtID | bdID | empID | rtStartDate | rtBillingRate |
+--+--+---+-+---+
|1 | NULL |  NULL | -00-00  |  0.00 |
|2 | NULL | 1 | 2004-01-01  |  2.00 |
|3 | NULL | 1 | 2004-05-10  |  4.00 |
|4 | NULL | 1 | 2005-01-10  |  6.00 |
|5 | NULL | 1 | 2005-04-12  |  8.00 |
|6 | NULL | 1 | 2006-01-02  | 10.00 |
|8 |   37 | 1 | 2005-10-01  | 25.00 |
+--+--+---+-+---+

DELIMITER $
CREATE PROCEDURE test_rate (EMPID int, BDID int,
CURRENTDATE date)
BEGIN
SELECT
COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate)
AS rtBillingRate
  FROM Rates rt1
LEFT JOIN Rates rt2 ON (rt2.empID=EMPID and
rt2.chgID IS NULL and rt2.bdID IS NULL and
rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=EMPID and chgID IS NULL and bdID IS NULL
and rtStartDate = CURRENTDATE))
LEFT JOIN Rates rt3 ON (rt3.empID=EMPID and
rt3.chgID IS NULL and rt3.bdID=BDID and
rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=EMPID and chgID IS NULL and bdID=BDID and
rtStartDate = CURRENTDATE))
 WHERE rt1.empID IS NULL
   and rt1.chgID IS NULL;
 
END$
DELIMITER ;

mysql call test_rate (1,NULL,'2005-09-01');
+---+
| rtBillingRate |
+---+
|  8.00 |
+---+
CORRECT!

mysql call test_rate (1,37,'2005-10-10');
+---+
| rtBillingRate |
+---+
| 25.00 |
+---+
CORRECT!

mysql call test_rate (1,NULL,'2005-10-10');
+---+
| rtBillingRate |
+---+
|  0.00 |
+---+
1 row in set (0.01 sec)
WRONG! This should have returned 8.00.

When I run this query by itself (outside the
procedure) I get the correct result: (notice I'm
plugging in EMPID, BDID, and CURRENTDATE parameters)

SELECT
COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate)
AS rtBillingRate
  FROM Rates rt1
LEFT JOIN Rates rt2 ON (rt2.empID=1 and
rt2.chgID IS NULL and rt2.bdID IS NULL and
rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=1 and chgID IS NULL and bdID IS NULL and
rtStartDate = '2005-10-10'))
LEFT JOIN Rates rt3 ON (rt3.empID=1 and
rt3.chgID IS NULL and rt3.bdID=NULL and
rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=1 and chgID IS NULL and bdID=NULL and
rtStartDate = '2005-10-10'))
 WHERE rt1.empID IS NULL
   and rt1.chgID IS NULL;

+---+
| rtBillingRate |
+---+
|  8.00 |
+---+
1 row in set (0.00 sec)
CORRECT!

What's going wrong in the stored procedure?

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

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

MySQL query question

2005-12-24 Thread Josh Mellicker


I have several tables, all with many-to-many joining tables.

users

users_teams

teams

teams_projects

projects


---

So, with a projects.id = 1, I want to get all the usernames of people  
on teams assigned to that project.


SELECT DISTINCT username
FROM users, users_teams, teams, projects_teams, projects
WHERE projects.id = '1'
AND projects_teams.project_id = projects.id
AND teams.id = projects_teams.team_id
AND users_teams.user_id = users.id

gives me ALL the users who are on any team... even teams not assigned  
to that project.


What gives? My brain hurts. Thanks for any help.

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

Status of OUTER JOIN bug 1591?

2005-12-21 Thread Josh Trutwin


This is referencing an old thread in the mailing list:

http://archives.neohapsis.com/archives/mysql/2004-q3/4484.html

I'm trying to write a justification for upgrading to MySQL 5.0 and I 
know it fixed some issues with LEFT/RIGHT joins.  In particular I 
thought it fixed bugs 1591, 1677 and 3765.  But bug 1591 is still 
listed as To be fixed later.  Is that correct?  If it is is there a 
different bug against how MySQL 4 handled OUTER JOINS which was fixed 
in MySQL 5 that I can reference in my justification letter?


Thanks,

Josh Trutwin


FWIW - here is an off-list conversation with another list member 
regarding this issue:



Josh Trutwin [EMAIL PROTECTED] wrote on 12/20/2005 05:45:04 PM:

  Josh Trutwin wrote:
   [EMAIL PROTECTED] wrote:
   Josh Trutwin [EMAIL PROTECTED] wrote on 09/20/2004
10:41:46 PM:
  
   On Mon, 20 Sep 2004 10:25:16 -0400
   [EMAIL PROTECTED] wrote:
  
   I think you missed my point. I think the 5.0.1 behavior was 
correct
   and the others are wrong. There is a known bug (or two) about 
mixing

   outer joins and inner joins and it looks like it may be fixed. IF
   you want to see all of the students THAT TABLE (students) 
needs to

   be on the LEFT side of a LEFT JOIN or the RIGHT side of a RIGHT
   JOIN. That's what the directions mean
   Interesting - do you have a link to more information on this bug?
  
  
   http://bugs.mysql.com/1677 and http://bugs.mysql.com/1591 and
   http://bugs.mysql.com/3765
  
   Shawn,
  
   - a while back you helped me debug some MySQL Join queries that
   didn't work after upgrading from 4.0 to 5.0 alpha.  The bugs are
   referenced in the links above.  Do you know why these bugs are still
   listed as To be fixed later in the bug database?  I am trying to
draft
   a document for persuading someone to support MySQL 5.0 in their
software
   and wanted to use this example as a major fix made in the database
server.
  
   Thread reference:
   http://archives.neohapsis.com/archives/mysql/2004-q3/4484.html
  
   Thanks,
  
   Josh
  
 


I don't know why they are not fixed. It may be that they are but nobody
has gone back to test the new algorithms against them. Mixing left joins
and right joins are problematic anyway. There also isn't a FULL OUTER
JOIN, yet either. I was trying to find a workaround for the lack of FULL
OUTER JOIN when I tried a query that included both LEFT and RIGHT joins
and noticed that it didn't quite mesh. That's how I found out about the
bugs I told you about. There have been 16 sets of changes since 5.0.1 so
any one of them may have fixed it but the regression tests just haven't
picked it up.

http://dev.mysql.com/doc/refman/5.0/en/news.html

99.9% of the queries out there do not mix LEFT and RIGHT joins and there
are other ways to work around the lack of a FULL OUTER JOIN so that
probably explains why the fixes are low on the priority list.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

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

Re: Reason for Auto-increment primary keys?

2005-12-21 Thread Josh Trutwin


Kenneth Wagner wrote:

Speed. Especially where related files are concerned. Foreign keys. Links 
on integer
fields are faster, smaller and more efficient. Keys remain smaller and 
faster.


This in my mind is one of the biggest reasons to use an AUTO_INCREMENT 
column as a primary key when other columns would work.  If you have a 
table that will act as a parent in a parent/child relationship and 
you've identified a composite (more than one column) PK as:


col1 VARCHAR(25)
col2 VARCHAR(30)

Then the child table would need to have a copy of both columns posted 
to setup a composite foriegn key:


CREATE TABLE child (
   child_id INT AUTO_INCREMENT,
   col1 VARCHAR(25) NOT NULL,
   col2 VARCHAR(30) NOT NULL,
   INDEX fk_ind (col1, col2),
   FOREIGN KEY (col1, col2) REFERENCES parent(col1,col2) ON DELETE...
   PRIMARY KEY (child_id)
)

So not only are you making a more complex index on the parent table by 
using two character columns you are also posting two columns into the 
child table(s) whenever you want to use this as a parent table.  And 
with MySQL you generally have to make another INDEX on the FK columns 
as well as shown above.  (I've never understood why this isn't automatic)


In this case you have to decide whether or not it's good to maintain 
the uniqueness constraint on the parent table columns if you add an 
AUTO_INCREMENT column by doing something like:


CREATE TABLE parent (
   parent_id INT AUTO_INCREMENT,
   col1 VARCHAR(25) NOT NULL,
   col2 VARCHAR(30) NOT NULL,
   some_other_col VARCHAR(200) NULL,
   UNIQUE (col1, col2),
   PRIMARY KEY (parent_id)
);

The UNIQUE constraint will still create an index on the text columns 
so you will still need to consider space/performance issues but at 
least your child tables only need to post a copy of the INT column 
parent_id.


In my mind it's always good to use UNIQUE in these cases so your real 
primary keys are in your table structure to prevent getting bad data.


My $0.02

Josh

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

Next alpha release?

2005-10-24 Thread Josh Trutwin

I don't think I remember a time when MySQL only had stable production
releases available for download (at least not off the website) since
v3.  Any idea if the next alpha is on the way?

Thanks, 5.0 looks great, I've been using it for over a year with very
few problems.

Josh

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

Re: Foreign key support in MyISAM

2005-09-30 Thread Josh Trutwin

On Fri, 30 Sep 2005 13:08:31 -0700
Jacek Becla [EMAIL PROTECTED] wrote:

 Hi,
 
 I had asked similar question few days ago, and then checked with
 the developers as no one was able to answer on this mailing list.
 I was told it is very likely we'll get it in 5.2.

Thanks - now that 5.0 is release candidate I wonder how soon it will
be before 5.1 alpha is released?

Josh

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

Re: Differences Between ORACLE SQL PLus and MYSQL SQL

2005-09-16 Thread Josh Trutwin

On Fri, 16 Sep 2005 10:32:23 -0400
[EMAIL PROTECTED] wrote:

You asked basically two questions:

a) what are the differences between ORACLE SQL plus and MYSQL?

snip

depends on how they are with MySQL already. One well-known point on
how
MySQL and Oracle differ is in how to define a JOIN in a SQL
statement.
MySQL works best if you use the explicit [INNER|LEFT|RIGHT] JOIN
... ON
... format. AFAIK, Oracle does not support this syntax.

All these questions are also dependant on which version of which
software you are using. In Oracle 8i the above is certainly true, you
have to use the doofy (+) syntax for outer joins, etc. In Oracle
10g though the more verbose syntax is supported:

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_10002.htm#sthref7225

You can do LEFT INNER, RIGHT INNER, LEFT OUTER, RIGHT OUTER, FULL
OUTER, etc. using the same syntax as MySQL.

I'm not sure but some versions of MySQL also had different results
from Oracle depending on how you defined the join condition in an
OUTER join. So what version of MySQL you choose also has consequences
on portability (e.g. sub-queries, views, etc)

There are enough little differences between all DBMS's that make
porting a real challenge. Bottom line, you're going to have to do a
LOT of research and testing.

I would seriously consider looking at a database abstraction layer.
In ColdFusion I have no idea what is available, but for PhP you'd want
to look at something like ADOBD (http://adodb.sourceforge.net) or
PDO/MDB/Metabase, etc.

Good luck,

Josh

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

Re: MySQL db size using show table status


Jaspreet Singh wrote:

Hi,

I am trying to compute the MySQL db size using show table status
command. It gives me the size of .MYD and .MIY files, but not .frm which
is typically 12k (using 4.1.9 version of MySQL)

Qus 1. is there any way to deterministically compute the value of .frm
file


using a command line tool should do this.

Usually, .frm table definition files are negligible to the size of
the actual database.


Qus 2. Is there any other way to compute the db size (other than disk
quota).



du -s mysql_data_directory

Regards,

Josh

--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: Locks acquired with get_lock() persisting, even after MySQL thread has exited


Adam Newby wrote:

Description:


We have distributed applications which make extensive use of the 
get_lock() function
to acquire a system-wide lock. Occasionally, all copies of a given 
application
block attempting to acquire a lock on the same lock string. Using 
is_used_lock() reports

that the lock is held by a particular thread ID.



This does not seem like expected behavior.  I would either file
this as a bug at http://bugs.mysql.com/ with a nice reproducible
test case, or file a ticket with MySQL Support at https://support.mysql.com/

Regards,

Josh

--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting


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

Re: MySQL Users


Jaspreet Singh wrote:


The problem is that MySQL users can only be 16 chars loong (bad !!) and
cannot have '@' or '#' in the name.



If you think 16 characters is not enough, you can file this as a bug/feature
request at http://bugs.mysql.com


Is there a way out ... Is there a way so that i can directly use system
users and not maintain this users table inside MySQL



We don't have external authentication hooks today, but understand that
this is a desirable feature.  Currently there is no commitment on
developing such a feature on our product roadmap, but that could change
in the future.

Regards,

Josh
--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: innodb thread concurrency size [mysql performance tuning]


Clyde Lewis wrote:

Hey guys,

I'm looking for any best practices or a formula that is commonly used in 
setting the value for thread concurrency in the configuration file.

I have 24 instances running on a sun 2900 server with 32GB or ram.
Here is a sample of my configuration file.



The best practice for setting innodb_thread_concurrency is
# of resources per instance * 2, so if you had 2 CPUs and 2 hard drives
you wanted to use for a MySQL instance, set it to 8,
which is the default.  Sometimes the *2 factor could be *1
to get the best system performance.  If you want to throttle
all of your instances so no one instance hogs the system,
you might set the innodb_thread_concurrency down to 2.  If you
wanted one to use all of your system resources, then set it
much higher.

If you ever need more involved assistance from the MySQL
Professional Services team, you might look at these packages:

  http://www.mysql.com/consulting/packaged/performance.html
  http://www.mysql.com/consulting/packaged/rapidresponse.html

Regards,

Josh

--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting



#use this line mfor mysql 4.1
old-passwords
server-id = 2216
net_buffer_length=65536
net_read_timeout=120
net_write_timeout=180
key_buffer=64M
max_allowed_packet=1M
table_cache=2048
sort_buffer=1M
record_buffer=1M
myisam_sort_buffer_size=16M
max_connections=2500
thread_cache=8
# Try number of CPU's*2
thread_concurrency=4
query_cache_size=256M
query_cache_limit=128K
#only availble in 4.1
innodb_file_per_table
innodb_buffer_pool_size=500M
innodb_additional_mem_pool_size=25M
innodb_log_archive=0
innodb_log_files_in_group=3
innodb_log_file_size=100M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_file_io_threads=4
innodb_lock_wait_timeout=30
#innodb_flush_method=fdatasync
#innodb_fast_shutdown=1
innodb_thread_concurrency=5

transaction-isolation = READ-UNCOMMITTED
[mysqld140]
bind-address=xxx.xxx.xxx.xxx
old-passwords
mysqld=/usr/local/mysql/bin/mysqld_safe
pid-file=/p01/abq/mysqladmin/abq_pid
basedir=/usr/local/mysql
datadir=/p01/abq/mysqldata
socket=/p01/abq/mysqladmin/mysql.sock
port=3306
local-infile=1
user=mysql
tmpdir = /tmp/abq/
log = /p01/abq/mysqllogs
log-bin = /p01/abq/mysqllogs/abq-bin
log-err = /p01/abq/mysqllogs/abq.err
log-slow-queries = /p01/abq/mysqllogs/abq_slow_query.log
innodb_file_per_table
set-variable = innodb_buffer_pool_size=500M
set-variable = innodb_additional_mem_pool_size=25M
innodb_data_home_dir = /p01/abq/mysqldata/innodb/
innodb_data_file_path = ibdata1_abq:100M:autoextend:max:4096M
#.._log_arch_dir must be the 
same

#as .._log_group_home_dir
innodb_log_group_home_dir = /p01/abq/mysqladmin/iblogs
innodb_log_arch_dir   = /p01/abq/mysqladmin/iblogs



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

Re: Too Many Queries Have Writing To Net Status [mysql performance tuning]


Kishore Jalleda wrote:

Hi All,
  We have a production Database, running mysql 4.0.17 , on a
Poweredge 2650 with 3 GB RAM, and dual Xeon 2.4 GHZ, the server
averages between 100 and 200 qps ( ,also and the CPU/MEM load is
pretty low and is extremely fast except for once in a while may be 1
in 1 queries take mote than 3 seconds to execute
(slow_query_time), and the status of the query is always writing to
net

Initially the NIC was an Intel 10/100, then the slow queries were a
lot more, then I started using the Broadcom Gigabit port that comes
with the 2650, and recently I also started load balancing the traffic
between the two Broadcomm NIC'S using the Basp Module, so that I
effectively have a 200 MBPS full duplex link( the switch connected has
only FE ports), with this setting the slow queries have decreased a
lot, but they do come up once in a while..

There is no lock time in any of them, I have tried using both InnoDB
and MyISAM, but they dont seem to go away, so I was wondering why
there are still few queries which still have writing to Net in the
status line and sometimes take 30-50 seconds to execute, note that if
the queries are run in any mysql client they run in less than 0.005
seconds, so I am pretty sure its the network thats the bottleneck, and
buying a Gigabit capable switch is not an option now, also sometimes
the traffic on the server goes upto 35MBPS.

So why is mysql not sending the results back to the client and
sometimes waiting soo long, also while mysql is waiting for a long
query( in the Writing to Net status)  hundreds of other queries are
executed extrelmely fast.

I am not very familiar with how mysql handles network packets, and why
its holding back ...



If the network is a bottleneck, certainly this could result in
long Writing to Net status.  One could imagine network congestion
causing repeated TCP/IP retries, etc.  It might just be that your
server is heavily loaded and certain threads are not getting enough
resources to finish processing.

Things like this are usually far to complex to diagnose simply,
and if you want expert help, I would recommend you engage with
our professional services team with either of these packaged
consulting solutions:

  http://www.mysql.com/consulting/packaged/performance.html
  http://www.mysql.com/consulting/packaged/rapidresponse.html

Regards,

Josh
--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: 45 minutes to check table to complete


Peter J. Schoenster wrote:

Hi,

I've got a database that is closed to 7G.

I'm using the Standard4.1.14 version.

The table was corrupt and I had to do a lot of moving of data to free up 
enough space on the partition as I discovered I needed at least twice 
the database size to do a recover. Well, using myisamchk -o worked. I 
then ran check table in the mysql client to see what it would say. It 
said all was well. I'm just suprised that it took 45 minutes to run.


Anyone run check table on large databases? What kind of times did you 
encounter? I must say this is on an OLD box ... maybe 512 RAM and right 
now I don't now the disk drives used.




45 minutes makes sense.  You need more memory for things to go faster,
as caching more of the 7GB database would help things a lot, otherwise
things become disk i/o bound, and that is likely what you are seeing
here.  Once you have the extra memory the next step is tuning MySQL
to take advantage of it depending on the storage engine you are using.

Regards,

Josh

--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: The Intel MySQL compiled Server


Dyego Souza Dantas Leal wrote:

Hello guys..

I want to use INTEL SHARED MySQL 4.0.25 - Pro with innodb tables on 
PRODUCTION SERVER DELL 2600


The Linux Kernel is 2.6.12 and the hardware is DUAL XEON 2.4 HT iwth 4 
GB of RAM

(i'm using raw partitions feature on innodb tables)

this is a secure option ? the Intel version of MySQL is really secure to 
use on production servers ?




These binaries tend to be faster, but also have some odd/rare bugs
that don't occur with other binaries.  So I would suggest if its
speed you want use them, and if they seem to have a problem on your
system, then revert back to the normal binaries.

Regards,

Josh

--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: Intelligent Converters product: MSSQL-to-MySQL


Ryan Stille wrote:

Has anyone ever used this MSSQL-to-MySQL converter?  It's pretty
reasonable at $40, and the demo output I got looked pretty good.  But I
wanted to see if there is anything I should be weary about.
http://www.convert-in.com/mss2sql.htm



I have done a test migration before with it and it seemed fine.

I would also stay tuned for our MySQL Migration Toolkit to support
MSSQL migrations.  You can find it here:

  http://www.mysql.com/products/tools/migration-toolkit/

Regards,

Josh
--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: MySQLHotCopy Error


Stephen P. Fracek, Jr. wrote:

We're having a problem with MySQLHotCopy.  It has worked flawlessly in the
past but now we're getting an error message similar to this:

Dumping database... DBD::mysql::db do failed: Can't find file:
'./file.frm' (errno: 24) at /usr/local/mysql/bin/mysqlhotcopy line 468.
Deleting previous 'old' hotcopy directory ('mydirectory')
Existing hotcopy directory renamed to '/mydirectory/db_name_old'
done.



$ perror 24
Error code  24:  Too many open files

Try increasing your open_files_limit?

This is documented here:
  http://dev.mysql.com/doc/mysql/en/not-enough-file-handles.html

If this does not help, and you need further support, you could work
this up with MySQL Support at:

  https://support.mysql.com

Regards,

Josh
--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: Behaviour of like expression


[EMAIL PROTECTED] wrote:

Hi all,

I've got a question. We were testing something on our mysql server (
4.0.21) with MyISAM tables. 

When we executed the query select * from people where name like ''; we 
expected the same results as select * from people where name=''; but it 
didn't.


The like function returned everything instead of only the people without a 
name. Is this known (and correct) behaviour? It does not sound logical to 
me. I couldn't find anything about it on the mysql website.




Usually, pattern matching done where a pattern is nothing matches everything.

Use LIKE for pattern matching, and = for equivalency.

I don't know what the ANSI SQL spec says here, and whether our behavior
is correct there, but this behavior makes sense to me at least.  I would
expect similar behavior from perl, for example:

$ perl -e 'my $a = qw(a); print $a =~ //;'
1

Regards,

Josh
--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: URGENT --- OSX 10.4 ODBC driver error


Andrew stolarz wrote:

Hello All,
 I am downloading and installing the Installer package (Mac OS X v10.3) 
from the mysql site.
I am installing on a OSX 10.4 machine, at the end of the install I get an 
error message saying
 The application MYODBCConfig quit unexpedically after it was reopened, max 
OSX and other applications are not effected etc. etc.

 its version : 3.51.11-1
 so this ODBC driver will not work on the 10.4 machine? as right next to the 
download it states OSX v10.3

 (installing on 10.3 works no problem)


Sorry, I can't help here, but noted this was an urgent request.
If you need urgent help, please contact MySQL Support at:

  https://support.mysql.com

Regards,

Josh
--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: MySQL vs Plone/Zope/Python

2005-08-05 Thread Josh Trutwin

On Thu, 4 Aug 2005 23:36:01 -0700 (PDT)
David Blomstrom [EMAIL PROTECTED] wrote:

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

I wouldn't compare Plone and MySQL.  I believe Zope (and hence
Plone's) underlying database technology is something called ZODB, you
might want to look into that.

FWIW, I got caught up in the Zope/Plone bandwagon a year or more ago
and it just didn't stick.  There are things I find intriguing as well
about Zope/Plone, but I've had much better success just installing a
Mambo/Drupal site to get a quick CMS.  I'm curious to see if Zope 3
makes Zope an attractive product again, but for now I'll just stick
with PhP.  

Not that I think Python is a bad language for web apps, I'm actually
starting to like Python more and more.

Josh

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

Re: ORACLE and Mysql: Create tables, constraints, swquences, storage. ..?

2005-07-26 Thread Josh Chamas


Nguyen, Phong wrote:

Good morning,

I have scripts to create constraints, sequences, storage..., tables from
ORACLE and I don't know if I can create them in MySQL?



You can try our Migration Toolkit which has pretty good support
for Oracle now...

  http://www.mysql.com/products/migration-toolkit/

One of the ways I like to use it is to reverse engineer the Oracle
schema, and then have the MySQL schema definition script created for
further modification.  Note we do not have support for sequences,
and its typical to just use AUTO_INCREMENT columns for these.

Regards,

Josh

--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: very slow inserts on InnoDB [InnoDB Performance Tuning]

2005-07-26 Thread Josh Chamas


Hi Catalin,

Here are some InnoDB performance tuning tips that may boost
your insert speed:

Catalin Trifu wrote:

...
innodb_buffer_pool_size = 256M


Higher is better, in fact pushing this up to 60%-80% on a
dedicated database would be good.  If there are other things
running like a web server, then you will have to take its memory
requirements into account, but 256M could likely be bigger.

Maybe this could be 512M ?  Then data sets up to this size
will be as fast as possible.


innodb_additional_mem_pool_size = 64M


Rarely does this need to be set over 8M.


# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 32M


Try setting this to 1/2 your buffer pool size, otherwise
you might get too much checkpointing during lots of inserts.


innodb_log_buffer_size = 8M


Looks good.


innodb_flush_log_at_trx_commit = 1


Try setting to trx_commit = 2 for faster insert performance,
however you then lose ACID transactions, where if you have a system
failure you could lose around 1 second worth committed data.

These suggestions will not necessarily fix your problem.  If you continue
to have issues and they go unresolved on this list, you might consider
getting help via our commercial offerings:

  http://www.mysql.com/network/
- OR -
  http://www.mysql.com/consulting/packaged/performance.html
  http://www.mysql.com/consulting/packaged/rapidresponse.html

Regards,

Josh

--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: query on a very big table [MySQL partitioning of large tables]

2005-07-26 Thread Josh Chamas


Christos Andronis wrote:

Hi all,
we are trying to run the following query on a table that contains over 600 million rows: 


'ALTER TABLE `typed_strengths` CHANGE `entity1_id` `entity1_id` int(10) 
UNSIGNED DEFAULT NULL FIRST'

The query takes ages to run (has been running for over 10 hours now). Is this 
normal?



Yes, this is normal, but not desirable of course!

The fundamental issue is that your table is likely too large, and
rebuilding indexes cannot fit into memory, and goes to disk.

You need to break the table up into smaller shards or partitions using
horizontal table partitioning methodologies.

Usually what one will do is have say 10M or 100M rows in a table say
for one months data or some such.  If this is MyISAM, then all of those
sub tables can be put into a MERGE, and queried normally:

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

If this is InnoDB, then you have to teach your application how to
query the partitions individually and aggregate the results on its own.

We will have a better partitioning implementation in MySQL 5.1, being
developed still, but you can get early information on this in the
Partitioning Forum here:

  http://forums.mysql.com/list.php?106

One nice side effect in data warehousing type applications is that
when you go to delete the data, you can just drop the old table,
with having to do large deletes and rebuilding the table to defragment.


As a side issue, is MySQL suited for such big tables? I've seen a couple of 
case studies with MySQL databases over 1.4 billion rows but it is not clear to 
me whether this size corresponds to the whole database or whether it is for a 
single table.



Yes, its fine.  You just need to partition your huge tables, this is true
in all database platforms.


The MySQL distribution we're using is 4.1.12. The database sits on a HP 
Proliant DL585 server with 2 dual-core Opterons and 12 GB of RAM, running Linux 
Fedora Core 3.



If this is InnoDB, then you might make sure that your InnoDB buffer pool
is set very large, say 10GB, and this will improve performance there.

If this is MyISAM, then you want to set myisam_sort_buffer_size and 
key_buffer_size
to 4GB or just under.  There is a 4GB limit currently on those settings.
myisam_sort_buffer_size is used for rebuilding an index and you need to make
sure your index will fit in that amount of memory when you partition your
tables.

If you continue to need help, then you might want to enlist our
on-site consulting for your project needs:

  http://www.mysql.com/consulting/packaged/performance.html

Regards,

Josh

--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!  www.mysql.com/consulting

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

Re: Seriously.. When are we going to get subqueries?!

2005-06-09 Thread Josh Trutwin

On Wed, 8 Jun 2005 21:57:25 -0600
George Sexton [EMAIL PROTECTED] wrote:

 I think MySQL has a little ways to go yet before I would
 subjectively call it best.

ok.

 I posted twice to the list with questions about porting my
 application that runs on (SQL Server, Oracle, PostgreSQL, Sybase SQL
 Anywhere, MS Access, and DB2) to MySQL. No one on the mysql list, or
 the internals list responded to my pretty basic issues:

 1)Why can't I declare a datetime field with DEFAULT NOW()

http://dev.mysql.com/doc/mysql/en/create-table.html

The DEFAULT clause specifies a default value for a column. With one
exception, the default value must be a constant; it cannot be a
function or an expression. This means, for example, that you cannot
set the default for a date column to be the value of a function such
as NOW() or CURRENT_DATE. The exception is that you can specify
CURRENT_TIMESTAMP as the default for a TIMESTAMP column as of MySQL
4.1.2. See Section 11.3.1.2, _TIMESTAMP Properties as of MySQL 4.1_. 

snip

For date and time types other than TIMESTAMP, the default is the
appropriate ``zero'' value for the type. For the first TIMESTAMP
column in a table, the default value is the current date and time. See
Section 11.3, _Date and Time Types_. 

Looks like a policy decision, not a missing feature?  Why does the
TIMESTAMP column not meet your needs?

 2)Since the SQL standard states that identifiers are not case
 sensitive, how can I use the DB without case sensitivity, when I
 don't have authority to change the system wide lowercase setting? I
 wouldn't have authority to change the setting in a hosted
 environment.

Only thing I would suggest is to work with your hosting admin to see
if they would be willing to change this system-wide setting since
there is no per-user control over this.  If this is something you
cannot live with then choose a different RDBMS. 

 I have to say, MySQL still looks like a tinker-toy to me.

ignoring troll bait

Good luck,

Josh

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

Re: Seriously.. When are we going to get subqueries?!

2005-06-09 Thread Josh Trutwin

On Thu, 09 Jun 2005 14:28:56 +0100
Gordan Bobic [EMAIL PROTECTED] wrote:

 My understanding was the timestamp fields were only set when the
 record  is created. They are not changed when the record is
 modified.

http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html

The first TIMESTAMP column in table row automatically is updated to
the current timestamp when the value of any other column in the row is
changed, unless the TIMESTAMP column explicitly is assigned a value
other than NULL.

Josh

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

Re: MySQL 5.0.6-beta has been released

2005-06-01 Thread Josh Trutwin

On Tue, 31 May 2005 18:01:48 -0500
Matt Wagner [EMAIL PROTECTED] wrote:

snip

 This is the third published Beta release in the 5.0 series. All
 attention will now be focused on fixing bugs and stabilizing 5.0 for
 later production release.

Just curious - was there a 5.0.5-beta release?  The last beta I had
was 5.0.4 unless I missed a release announcement...

Thanks,

Josh

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

Re: [Nevermind!] MySQL 5.0.6-beta has been released

2005-06-01 Thread Josh Trutwin

On Wed, 1 Jun 2005 10:40:10 -0500
Josh Trutwin [EMAIL PROTECTED] wrote:

 On Tue, 31 May 2005 18:01:48 -0500
 Matt Wagner [EMAIL PROTECTED] wrote:
 
 snip
 
  This is the third published Beta release in the 5.0 series. All
  attention will now be focused on fixing bugs and stabilizing 5.0
  for later production release.
 
 Just curious - was there a 5.0.5-beta release?  The last beta I had
 was 5.0.4 unless I missed a release announcement...

Just saw this in the release:

Changes in release 5.0.5 (not released):

Sorry for wasting bandwidth...

Josh

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

Re: Export from Access

2005-05-17 Thread Josh Trutwin

On Tue, 17 May 2005 17:17:31 +0100
S.D.Price [EMAIL PROTECTED] wrote:

 Hi,
 can anyone explain how I would export a database created in Access
 to MySQL using PHPMyAdmin - I can't seem to import the data as csv
 or txt.

Acess should allow exporting to CSV.  Otherwise you can skip
phpMyAdmin and just use ODBC - check out MyODBC on mysql.com.

Josh

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

Re: mysql_fix_privilege_tables error

2005-04-25 Thread Josh Trutwin

On Fri, 22 Apr 2005 22:44:44 +0300
Gleb Paharenko [EMAIL PROTECTED] wrote:

 Hello.
 
 
 
 Make a bug or feature report at http://bugs.mysql.com.

Already did - 10098 - it was recently closed, guess it was already
fixed in 5.0.5.

Josh

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

mysql_fix_privilege_tables error

2005-04-22 Thread Josh Trutwin

Would it be possible to add:

ENGINE=MyISAM 

To all the CREATE TABLE statements in the mysql_fix_privilege_tables
script?  The server (tested with 5.0.3 and 5.0.4) crashes when
creating/altering these tables if the following is in /etc/my.cnf: 

default-table-type=innodb

I had to drop all the new tables and added ENGINE=MyISAM to the CREATE
TABLE statements, re-ran the script and it worked fine.

Here is an example crash report:

050422  9:19:43InnoDB: Assertion failure in thread 245771 in file
../include/data0type.ic line 466
InnoDB: Failing assertion: type-len % type-mbmaxlen == 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this
binary
or one of the libraries it was linked against is corrupt, improperly
built,
or misconfigured. This error can also be caused by malfunctioning
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is
definitely wrong
and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size +
sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8ab7a70
Attempting backtrace. You can use the following information to find
out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbe5f3938, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8180bef
0xb7e48c85
0x8295abc
0x829492a
0x829286c
0x82af069
0x82ae30c
0x82c5f99
0x823754f
0x8233119
0x8227b3f
0x8210552
0x823cb76
0x823f164
0x8196522
0x819d604
0x8194278
0x8193d85
0x8193192
0xb7e4354e
0xb7d71b8a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html
and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please
do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x8ae2ae0 = ALTER TABLE time_zone
  MODIFY Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci
DEFAULT 'N' NOT NULL
thd-thread_id=6
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0


Josh




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

Re: MySQL 5.0.4-beta has been released

2005-04-18 Thread Josh Trutwin

On Mon, 18 Apr 2005 13:28:24 +0200
Joerg Bruehe [EMAIL PROTECTED] wrote:

 Hi,
 
 A new version of MySQL Community Edition 5.0.4-beta Open Source
 database management system has been released.  This version now
 includes support for Stored Procedures, Triggers, Views and many
 other features.  It is now available in source and binary form for a
 number of platforms from our download pages at
 http://dev.mysql.com/downloads/ and mirror sites.
 
 Note that not all mirror sites may be up-to-date at this point. If
 you cannot find this version on a particular mirror, please try
 again later or choose another download site.

snip

The mirrors seem to have links to the source tarball, but I've
downloaded 5 different files and each time I get:

# tar zxvf mysql-5.0.4-beta.tar.gz 

gzip: stdin: not in gzip format
tar: Child returned status 1
tar: Error exit delayed from previous errors

Something amiss or am I losing it?

Thanks,

Josh


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

Re: MySQL 5.0.4-beta has been released

2005-04-18 Thread Josh Trutwin

On Mon, 18 Apr 2005 18:04:46 +0200
Joerg Bruehe [EMAIL PROTECTED] wrote:

snip

 Are you sure it did not get damaged during transfer, or by your
 browser? All I can recommend is to try another mirror.

I'm using elinks text browser, which has worked great for this in the
past.  I tried about 4 different mirrors.  I'll keep at it and see if
I get a better download.

Thx,

Josh

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

Re: MySQL vs PostgreSQL

2005-03-15 Thread Josh Trutwin

On Tue, 15 Mar 2005 18:49:38 +0900
ninjajs [EMAIL PROTECTED] wrote:

 What do you think about MySQL vs PostgreSQL ?

Both are great products and have their ups and downs.  On a MySQL list
you will not get an un-biases answer to this question.  
If you really want to know what people on the MySQL list think of PG,
search the archives (as already recommended).  If you also want to
know what PG folks think of MySQL, they have list archives as well. 

Both are easy enough to install (with MySQL being slightly easier on
the newbie scale) that you can just install them both, and evaluate
for yourself.

Have fun,

Josh

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

Re: MySQL and PHP

2004-12-29 Thread Josh Trutwin

On Wed, 29 Dec 2004 10:42:45 -0500
GH [EMAIL PROTECTED] wrote:

 On Windows... Which do i install first? PHP or MySQL?

Apache.  :)

It doesn't really matter but I'd suggest MySQL first as I think there
is one file you need to copy from the MySQL installation to the
C:\Windows (or whatever systemroot directory you use) before the PhP
MySQL extension will work.

Josh

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

Re: [OT] MySQL and PHP

2004-12-29 Thread Josh Trutwin

On Wed, 29 Dec 2004 12:09:51 -0500
[EMAIL PROTECTED] wrote:

snip

 Of course, the WAMP alternative is always there, Apache is FREE and
 comes  with PHP. 

snip

FWIW, the download of apache from the Apache website
(http://httpd.apache.org/) does NOT include PhP, you still have to
download and install php as a module, but you don't have to do
anything special to Apache to use it with PhP/Perl/whatever beyond
editing the config file to load the correct module.

Here's another nice product that has all the WAMP software bundled
together. 
http://www.sokkit.net/pragmacms/index.php?layout=maincslot_1=2

Anyway, this is now OT for a MySQL list

Josh

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

Re: Mysql-4.1.8 library name bug

2004-12-23 Thread Josh Trutwin

Yes, this has been reported on this list a couple times already.  

Really baffling how this one made it out of QA.

Josh

On Thu, 23 Dec 2004 21:43:27 +0200 (EET)
Andrey Kotrekhov [EMAIL PROTECTED] wrote:

 SQL
 
 Hello, All!
 
 IMHO this the bug in 4.1.8 to create library shared libraries
 without .so suffix.
 After this any programs linked with static libraries not dynamic,
 because of convention lib*.so.[0-9] in shared libraries names.
 
 ldconfig doesn't see  new libraries at all too.
   This bug in 4.1.8
 4.1.7 compiled on the same PC at the same time with .so suffix in
 library names.
 
  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: libmysqlclient name library wrong

2004-12-17 Thread Josh Trutwin

On Fri, 17 Dec 2004 16:19:25 -0200 (BRST)
[EMAIL PROTECTED] wrote:

 Description:
   
   Mysql 4.1.8 installs libmysqlclient as libmysqlclient.14.0.0
   instead of libmysqlclient.so.14.0.0 which is the correct for
   Linux.

I wish you were about 1/2 hour faster, I just finished rebuilding
everything that links against mysql (php, perl dbd, courier imap, etc)
because of this.  I thought maybe the library version number just
bumped up one.  Erg, now I'll have to do that all again when 4.1.9
fixes this...

Josh

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

Re: FOREIGN_KEY CHECKS

2004-12-11 Thread Josh Trutwin

On Wed, 8 Dec 2004 08:21:54 +0200
Heikki Tuuri [EMAIL PROTECTED] wrote:

 dumps do contain that setting in 4.1.7. Below is a start of a dump
 file.
 
 [EMAIL PROTECTED]:~/mysql-4.1/client ./mysqldump test  dump
 [EMAIL PROTECTED]:~/mysql-4.1/client cat dump | more
 -- MySQL dump 10.9
 --
 -- Host: localhostDatabase: test
 -- --
 -- Server version   4.1.8-debug-log
 
 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 /*!40101 SET NAMES utf8 */;
 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, 
 FOREIGN_KEY_CHECKS=0
 */;
 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,
 SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;

Great, thanks for the info!

Josh

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

Re: FOREIGN_KEY CHECKS

2004-12-07 Thread Josh Trutwin

On Tue, 07 Dec 2004 16:51:10 -0700
Titus [EMAIL PROTECTED] wrote:

 
 On page 822 of the pdf copy of the documentation
 that I have, it says:
 
 To make it easier to reload dump files for tables
 that have foreign key relationships, mysql automatically
 includes a statement in the dump output to set FOREIGN_
 KEY_CHECKS to 0 as of MySQL 4.1.1.
 
 I'm using MySQL 4.1.7 and the dumps do not appear to
 include this line automatically.  I am having to add
 it manually.  What am I overlooking?

I THINK you have to add it manually to your dump file.  Personally I
wish MySQL would add this as an option to mysqldump as I can never
remember the syntax to this command when I do an import with InnoDB
tables.

Josh

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

RE: mysqldump

2004-11-02 Thread Josh Howe


Thanks Spenser, but I'd already tried something similar. What I get when
I try this is:

mysqldump: Can't get CREATE TABLE for table `z_*` (Table 'sinu_com.z_*'
doesn't exist)



-Original Message-
From: Spenser [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 01, 2004 6:30 PM
To: Josh Howe
Subject: Re: mysqldump

Take a look at this article: 
http://www.unixreview.com/documents/s=8989/ur0408d/

There's a section on mysqldump if you prefer using it.  You would do
something like the following to backup only certain tables based on
their names starting with z_.

mysqldump -u root -p -x -e db1 z_*  /tmp/backup/db1_table1.sql



On Mon, 2004-11-01 at 11:30, Josh Howe wrote:
  
 Hi,
 Can anybody help me with a linux newbie question. I want to use
 mysqldump to backup all of the tables in a database that start with
z_.
 Can I do this in linux with a single line? Thanks.
 




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

RE: mysqldump

2004-11-02 Thread Josh Howe

Thanks, I haven't tried this yet. Iwant to generate one big file, not
one file per table. I guess I can replace the code in the for loop to
just build the mysqldump command. Thanks for the help! 

-Original Message-
From: Steve Poirier [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 02, 2004 1:44 PM
To: 'Josh Howe'; [EMAIL PROTECTED]
Subject: RE: mysqldump

Did you try this one? I simplified it for you. You just need to put this
in
a file and chmod +x the file. Then you can execute it from a crontab or
with
the shell.

~~~ Copy/paste after this ~~~

#!/bin/bash

# Modify following variables

# script will dump the tables that contain the following in the name
expression_to_match=z_

# database name
database=database_name

# database host
database_host=localhost

# directory to dump tables (put ending /)
dump_path=/path/to/dump/

TABLES=`echo show tables | mysql -h$database_host $database | grep
$expression_to_match`

for table in $TABLES

do
echo $table
mysqldump $database $table $dump_path$table
done

# end script

~~ end copy/paste ~~

You may need to twea the TABLES= command if you're using a password.
Same
for mysqldump if you want to put more options such as --opt

_
Steve Poirier

 -Original Message-
 From: Josh Howe [mailto:[EMAIL PROTECTED] 
 Sent: November 2, 2004 11:47 AM
 To: Spenser; [EMAIL PROTECTED]
 Subject: RE: mysqldump

 Thanks Spenser, but I'd already tried something similar. What 
 I get when I try this is:

 mysqldump: Can't get CREATE TABLE for table `z_*` (Table 
 'sinu_com.z_*'
 doesn't exist)

 -Original Message-
 From: Spenser [mailto:[EMAIL PROTECTED]
 Sent: Monday, November 01, 2004 6:30 PM
 To: Josh Howe
 Subject: Re: mysqldump

 Take a look at this article: 
 http://www.unixreview.com/documents/s=8989/ur0408d/

 There's a section on mysqldump if you prefer using it.  You 
 would do something like the following to backup only certain 
 tables based on their names starting with z_.

 mysqldump -u root -p -x -e db1 z_*  /tmp/backup/db1_table1.sql

 On Mon, 2004-11-01 at 11:30, Josh Howe wrote:

  Hi,
  Can anybody help me with a linux newbie question. I want to use 
  mysqldump to backup all of the tables in a database that start with
 z_.
  Can I do this in linux with a single line? Thanks.

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

mysqldump

2004-11-01 Thread Josh Howe

 

Hi,

 

Can anybody help me with a linux newbie question. I want to use
mysqldump to backup all of the tables in a database that start with z_.
Can I do this in linux with a single line? Thanks.

RE: what is wrong woth this statement?

Sorry, I don't think I was very clear. I'm asking a more generic
question about control flow expressions. I want to run a sql statement
but only if a certain condition is met, namely if a particular record
exists in a table. I want to do it all in a single mysql statement, like
so:

If ([record exists]) then
Do some sql
End If

How can I do this? Thanks. 


-Original Message-
From: Leo [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 19, 2004 11:38 PM
To: Josh Howe
Cc: [EMAIL PROTECTED]
Subject: Re: what is wrong woth this statement?

i didnt fully catch you...
is this the kind of query statement you want?

INSERT INTO some_other_table
SELECT
some_field_list
FROM z_mail_systems
HAVING COUNT(any_field)0


On Tue, 19 Oct 2004 12:45:30 -0400, Josh Howe [EMAIL PROTECTED] wrote:
 if (select count(*) from z_mail_systems  0) then [insert statement]
 endif;
 
 How do I do this kind of conditional insert? Thanks.
 


-- 
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: what is wrong woth this statement?

Thanks, but I tried that and it doesn't seem to work any better than an
if statement. Have you tried it? Maybe I am doing something wrong. If
you could post some working code that would be awesome.

-Original Message-
From: sol beach [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 27, 2004 3:25 PM
To: Josh Howe
Subject: Re: what is wrong woth this statement?

 How can I do this? 
By using the CASE staement?

On Wed, 27 Oct 2004 15:15:11 -0400, Josh Howe [EMAIL PROTECTED] wrote:
 Sorry, I don't think I was very clear. I'm asking a more generic
 question about control flow expressions. I want to run a sql statement
 but only if a certain condition is met, namely if a particular record
 exists in a table. I want to do it all in a single mysql statement,
like
 so:

 If ([record exists]) then
 Do some sql
 End If

 How can I do this? Thanks.

 -Original Message-
 From: Leo [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, October 19, 2004 11:38 PM
 To: Josh Howe
 Cc: [EMAIL PROTECTED]
 Subject: Re: what is wrong woth this statement?

 i didnt fully catch you...
 is this the kind of query statement you want?

 INSERT INTO some_other_table
 SELECT
 some_field_list
 FROM z_mail_systems
 HAVING COUNT(any_field)0

 On Tue, 19 Oct 2004 12:45:30 -0400, Josh Howe [EMAIL PROTECTED] wrote:
  if (select count(*) from z_mail_systems  0) then [insert statement]
  endif;

  How do I do this kind of conditional insert? Thanks.

 --
 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: what is wrong woth this statement?

 

Thanks Shawn. Basically, our deployment mechanism involves applying sql
change scripts as part of the build process. I created this simple shell
script:

 

host=$1

user=$2

pwd=$3

 

mysql -u$user -p$pwd -h$host -f sinu_com _EOF_

 

#Put the scripts to execute here:

\. z_worklog_alter.sql

\. z_companies_alter.sql

 

quit

_EOF_

 

 

I'd like to be able to run this script multiple times against the same
db without corrupting the data or structure, and without generating
error messages that don't indicate a real problem with one of the
scripts (e.g. that column already exists). To this end, I want to put
code in all of the .sql files so that it only executes once. E.g. if
the sql is:

 

Insert into users values (myemail,mypassword)

 

I would want some thing like this:

 

If(not exists(select * from users where username=myemail), Insert into
users values (myemail,mypassword))

 

 

I think I can protect against bad data with the proper keys and unique
indexes. I'm not sure about this though. But even if I can  protect the
db from corruption, I will still get a bunch of primary key violations
and such, and this will make it harder to extract the actual errors
(e.g. syntax errors in the sql) when I run the script.

 

So that's why I want to do this. I hope that made sense. I guess that
rather than trying to execute each script directly I can execute each
via an intermediate perl script that updates the db to indicate which
change scripts have been applied. That's more complex though. Any ideas
would be greatly appreciated. Thanks! 

 

 

 

 

 



From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 27, 2004 3:30 PM
To: Josh Howe
Cc: Leo; [EMAIL PROTECTED]
Subject: RE: what is wrong woth this statement?

 


Nearly all of the T-SQL style procedural statements (IF... BEGIN... END,
WHILE...WEND, cursors, etc.)  are not currently available in MySQL.
Procedural scripts , like the one you propose, will be available in
Stored Procedures (new to MySQL 5.0+).  I haven't tested that version
yet, so I can't tell you if it will support a statement like yours
outside of a stored procedure (as a stand-alone statement). 

As of right now, you still need to make control-of-flow decisions in
your programming language not your SQL statements. Sorry. 

Depending on what you are trying to do, there may be valid MySQL SQL
statement or sequence of statements that will duplicate the behavior of
the decision you are trying to make. Can you be very specific about what
action(s) you want your statement to make? 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

Josh Howe [EMAIL PROTECTED] wrote on 10/27/2004 03:15:11 PM:

 Sorry, I don't think I was very clear. I'm asking a more generic
 question about control flow expressions. I want to run a sql statement
 but only if a certain condition is met, namely if a particular record
 exists in a table. I want to do it all in a single mysql statement,
like
 so:
 
 If ([record exists]) then
Do some sql
 End If
 
 How can I do this? Thanks. 
 
 
 -Original Message-
 From: Leo [mailto:[EMAIL PROTECTED] 
 Sent: Tuesday, October 19, 2004 11:38 PM
 To: Josh Howe
 Cc: [EMAIL PROTECTED]
 Subject: Re: what is wrong woth this statement?
 
 i didnt fully catch you...
 is this the kind of query statement you want?
 
 INSERT INTO some_other_table
 SELECT
 some_field_list
 FROM z_mail_systems
 HAVING COUNT(any_field)0
 
 
 On Tue, 19 Oct 2004 12:45:30 -0400, Josh Howe [EMAIL PROTECTED] wrote:
  if (select count(*) from z_mail_systems  0) then [insert statement]
  endif;
  
  How do I do this kind of conditional insert? Thanks.
  
 
 
 -- 
 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: what is wrong woth this statement?

Thanks for all of the advice everybody. I'm actually a lot more
comfortable with perl than ANT so I think I'll use that. ANT does seem
pretty cool though, I'll need to buckle down and learn it at some point.

-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 27, 2004 4:51 PM
To: Josh Howe; [EMAIL PROTECTED]
Cc: Leo; [EMAIL PROTECTED]
Subject: Re: what is wrong woth this statement?

Have you considered doing what you want to do in Ant? I haven't done
exactly
what you want to do but Ant supports properties and conditions. I could
imagine an Ant task that determines if the desired record exists, then
another task that does an insert is executed only if the record doesn't
exist.

That might be easier than using a programming language if you have a
major
reluctance to do programming.

Rhino
- Original Message - 
From: [EMAIL PROTECTED]
To: Josh Howe [EMAIL PROTECTED]
Cc: Leo [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, October 27, 2004 3:29 PM
Subject: RE: what is wrong woth this statement?

 Nearly all of the T-SQL style procedural statements (IF... BEGIN...
END,
 WHILE...WEND, cursors, etc.)  are not currently available in MySQL.
 Procedural scripts , like the one you propose, will be available in
Stored
 Procedures (new to MySQL 5.0+).  I haven't tested that version yet, so
I
 can't tell you if it will support a statement like yours outside of a
 stored procedure (as a stand-alone statement).

 As of right now, you still need to make control-of-flow decisions in
your
 programming language not your SQL statements. Sorry.

 Depending on what you are trying to do, there may be valid MySQL SQL
 statement or sequence of statements that will duplicate the behavior
of
 the decision you are trying to make. Can you be very specific about
what
 action(s) you want your statement to make?

 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine

 Josh Howe [EMAIL PROTECTED] wrote on 10/27/2004 03:15:11 PM:

  Sorry, I don't think I was very clear. I'm asking a more generic
  question about control flow expressions. I want to run a sql
statement
  but only if a certain condition is met, namely if a particular
record
  exists in a table. I want to do it all in a single mysql statement,
like
  so:

  If ([record exists]) then
 Do some sql
  End If

  How can I do this? Thanks.

  -Original Message-
  From: Leo [mailto:[EMAIL PROTECTED]
  Sent: Tuesday, October 19, 2004 11:38 PM
  To: Josh Howe
  Cc: [EMAIL PROTECTED]
  Subject: Re: what is wrong woth this statement?

  i didnt fully catch you...
  is this the kind of query statement you want?

  INSERT INTO some_other_table
  SELECT
  some_field_list
  FROM z_mail_systems
  HAVING COUNT(any_field)0

  On Tue, 19 Oct 2004 12:45:30 -0400, Josh Howe [EMAIL PROTECTED]
wrote:
   if (select count(*) from z_mail_systems  0) then [insert
statement]
   endif;

   How do I do this kind of conditional insert? Thanks.

  -- 
  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: user defined table constraint

2004-10-21 Thread Josh Howe

Thanks, but I don't think a unique index is what I want. I need to allow any
number of records with the same user_id and a value of 'N' in is_primary.

-Original Message-
From: Gary Richardson [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 13, 2004 11:18 PM
To: Josh Howe
Cc: [EMAIL PROTECTED]
Subject: Re: user defined table constraint

You need to use a UNIQUE index:

ALTER TABLE table ADD UNIQUE user_id_primary (user_id, is_primary);

I can't find a specific section about UNIQUE indexes in the mysql
docs, but I'm sure it's there and I'm pretty sure the syntax about is
correct..

It basically says that each combination of user_id and is_primary
needs to be unique. This won't work if you want to have multiple

user_id = 5 and is_primary = n, for example.

On Wed, 13 Oct 2004 15:45:56 -0400, Josh Howe [EMAIL PROTECTED] wrote:

 Hi all,

 I have a table with these fields:

 user_id

 dept_id

 is_primary ('Y' or 'N')

 I want to make sure that there are never two rows in this table with the
 same user_id and is_primary='Y'. For any user_id, there can only be one
 primary record. In MS SQL I would define a user constraint on the table.
 Does MySQL have anything similar, or do I need to check the data in
 every place I do an insert into this table? Thanks.

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

what is wrong woth this statement?

2004-10-19 Thread Josh Howe

if (select count(*) from z_mail_systems  0) then [insert statement]
endif;

 

How do I do this kind of conditional insert? Thanks.

user defined table constraint

2004-10-13 Thread Josh Howe

 

Hi all,

 

I have a table with these fields:

 

user_id

dept_id

is_primary ('Y' or 'N')

 

I want to make sure that there are never two rows in this table with the
same user_id and is_primary='Y'. For any user_id, there can only be one
primary record. In MS SQL I would define a user constraint on the table.
Does MySQL have anything similar, or do I need to check the data in
every place I do an insert into this table? Thanks.

compare schemas

2004-09-30 Thread Josh Howe

 

Does anybody know of any free tools to compare two mysql schemas? Thanks.

Re: OUTER JOIN bug in 5.0.1?

2004-09-21 Thread Josh Trutwin

On Tue, 21 Sep 2004 08:57:21 -0400
[EMAIL PROTECTED] wrote:

snip

 There are up to three layers of record filtering that happen during
 any query. First is the JOIN filtering.  That is where the ON
 conditions are used with the table declarations to build a virtual
 table that consists of all columns from each of the participating
 tables and each combination of rows that meets the ON conditions. 
 If table A has 5 rows and table B has 50 rows and the ON conditions
 force a match of at most 2 records from table B to each record in
 table A, the virtual table will have at most 10 rows (not the 250
 that would be generated without the ON conditions). Second to be
 applied is the rest of the WHERE clause that could not be applied
 during the ON determinations. This is especially true with queries
 that contain OUTER JOINS. If it didn't happen in this order, we
 couldn't do an outer join of two tables and look for a null result
 in the outer table to determine non-matching rows. The third set of
 filters to be applied comes from the HAVING clause. HAVING
 conditions are applied after every other portion of the query has
 been analyzed except for the LIMIT clause. That is why HAVING works
 on the results of GROUP BY aggregate functions and can use column
 aliases declared in the SELECT clause.

Yes, this much I grasp as far as the ordering of filtering.  Cartesian
Product, then JOIN, Then WHERE.

 When you put a condition into the ON clause of a JOIN, it is going
 to be applied to the formation of the virtual table which gets
 computed _before_ the entire WHERE clause is applied. Under many
 conditions, some WHERE conditions can also be applied to table JOINs
 along with the ON restrictions. Luckily, the query optimizer handles
 that for us.

Perhaps another example would help.  I've been trying to re-write
another join query that's designed to produce an attendance record for
each student detailing which classes they've attended and which
they've missed.  Each occurance of a student (PK tech_id) attending a
class (PK c_id) is recorded in an intersection table class_attneded
(tech_id, c_id PK).  The absense of a record in this table indicates
the student missed the class.  So if tech_id 123123 was at classes 1
and 3, there would be records in the class_attended table:

tech_id - class_id
1231231
1231233

The query that worked pre 5.0.1 is:

SELECT *
  FROM student s
 INNER JOIN enrollment e ON e.tech_id = s.tech_id
 INNER JOIN class_attended ca ON ca.tech_id = s.tech_id
 RIGHT JOIN class c ON c.c_id = ca.c_id
 WHERE s.tech_id = '253542'
   AND c.term_id = '4'
   AND c.class_date  NOW()
 ORDER BY week_no;

In my mind I see this as get all the class records (the table on the
right side of the RIGHT JOIN) and if you can match up the
class_attended, enrollment and student information do so, otherwise
set those values to NULL.  In the example above this yeilds 3 rows -
the for for class id 2 has NULLs in the other table data.

I cannot seem to figure out the 5.0.1 equivalent because I seem to
have learned this the wrong way.

I tried this, starting with the class table since those are the rows
that I want to have displayed regardless of matches.

SELECT *
  FROM class c
  LEFT JOIN class_attended ca ON c.c_id = ca.c_id
 INNER JOIN student s ON ca.tech_id = s.tech_id AND ca.tech_id =
'253542'
 INNER JOIN enrollment e ON e.tech_id = s.tech_id
 WHERE c.term_id = '4'
   AND c.class_date  NOW()
 ORDER BY week_no;

This acts like an INNER JOIN though, I can only get two rows.  I've
tried mucking around with it, but I just cannot get the outer row
for class id = 2 to show.

This was so much easier with the bug!  :)

Thanks for your help,

Josh


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

Re: OUTER JOIN bug in 5.0.1?

2004-09-21 Thread Josh Trutwin

On Tue, 21 Sep 2004 10:49:31 -0400
[EMAIL PROTECTED] wrote:


 Hmmm. you want to see a student, all of the classes they are
 enrolled in and how many times they attended? I understand the
 relationships between the student, class, and class_attended tables
 (that's all related to attendance and class scheduling) but I do not
 understand the relationship between student and class. Is that the
 enrollment table? Does enrollment have a class id field on it? Are
 there other tables I do not know about that can tell you if a
 student is _supposed_ to be in a class? If enrollment does relate a
 student to a class, I propose the following query

Not quite.  The class is probably causing confusion becuase I didn't
explain what this application was used for.  I teach a single course
named ICS311 and this is my gradebook for only this course.  I teach
the course in many terms (summer, fall, spring, etc).  Each class is
a single meeting for the course.  For example, class number 1 is on
9/24/04, class number 2 is on 10/5/04 etc.  Class is a poor choice of
words since I only care about this single course. 

If it helps, I have an ERD here:
http://trutwins.homeip.net/gradebook.png
For now ignore all the assignment and login stuff.  I don't list FK's
in ERD's, if you cannot derive them from the model they are:

Enrollment.tech_id references Student.tech_id
Enrollment.term_id references Term.term_id
Class_attended.tech_id references Student.tech_id
Class_attended.c_id references Class.c_id (week_no in ERD)
Class.term_id references Term.term_id

week_no in the ERD is the class Id since there is only one class per
week.  Week 1, week 2, etc.

Enrollment is used because it remembers which students are enrolled in
the current term for the attendance record I'm trying to create.  I
also use it in this query because the withdrawl date might come into
play.

So what I'm trying to do is display which classes (meetings/whatever)
a student has attended and which they have not been at for my ICS311
course. 

Here's what kind of results I'd like:

Student Id: 123123 Name: Josh

class_id   attended
1  1
2  NULL
3  1
4  1
5  NULL
6  1

So this particular student missed classes 2 and 5 because they did not
have a record in the class_attended table, which would have these
records:

class_id   tech_id
1  123123
3  123123
4  123123
6  123123

Hope that helps, let me know if more details would help.

 SELECT s.tech_id, s.name, c.c_id, c.name, count(ca.id) as
 days_present, count(c.c_id) as classes_held
 FROM student s
 INNER JOIN enrollment e
 on e.tech_id = s.tech_id
 INNER JOIN class c
 on c.c_id = e.c_id
 LEFT JOIN class_attended ca
 on ca.c_id = c.c_ID
 WHERE s.tech_ID = 253542
 AND c.term_id = 4
 AND c.class_date  NOW()
 GROUP BY 1,2,3,4

Based on the description above this isn't quite what I need.  I don't
need to GROUP at all, just get the right OUTER JOIN clause to do this.

 I think we are close. 

Agreed, many thanks for your persistance in helping with this!

Josh

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

Re: OUTER JOIN bug in 5.0.1?

2004-09-20 Thread Josh Trutwin

On Mon, 20 Sep 2004 09:33:56 -0400
[EMAIL PROTECTED] wrote:

 Sounds like your 4.0.20 may be the buggy installation... let me see
 if I can explain.

Except this is a 5.0.1 installation.  The query worked as is in 4.0.20
(and it also worked in 5.0.0), only after playing with 5.0.1 did the
results suddenly change.

 Let's analyze your FROM clause and imagine there is no WHERE clause,
 for the moment:
 
 FROM student s
 INNER JOIN enrollment e ON e.tech_id = s.tech_id
 INNER JOIN submitted_assignment sa ON sa.tech_id = s.tech_id 
 RIGHT JOIN assignment a ON a.a_id = sa.a_id
 
 The tables student, enrollment, and submitted_assignment are all
 INNER JOINed to each other. You won't get any records back from
 those tables UNLESS they satisfy 
 the ON statements that match records between them respectively. 
 That batch of records is RIGHT JOINed to assignment. That means that
 all of the records from assignment are returned and the other 3
 tables just became optional data. Their data will be added to the
 internal working table only if they meet the ON condition of the
 RIGHT JOIN.
 
 Here is a logically equivalent way of reformatting your original
 FROM clause
 
 FROM assignment a 
 LEFT JOIN submitted_assignment sa
 ON a.a_id = sa.a_id
 INNER JOIN student s
 on sa.tech_id = s.tech_id
 INNER JOIN enrollment e
 on e.tech_ID = sa.tech_ID

Yes, I tried re-arranging things like this, and as it is above I think
it's more readable, but I was unable to get any results that resembled
an outer join.  Unfortunately I cannot test this out at the moment due
to other issues.

Thanks for your help though,

Josh

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

Re: OUTER JOIN bug in 5.0.1?

2004-09-20 Thread Josh Trutwin

On Mon, 20 Sep 2004 10:25:16 -0400
[EMAIL PROTECTED] wrote:

 I think you missed my point. I think the 5.0.1 behavior was correct
 and the others are wrong. There is a known bug (or two) about mixing
 outer joins and inner joins and it looks like it may be fixed. IF
 you want to see all of the students THAT TABLE (students) needs to
 be on the LEFT side of a LEFT JOIN or the RIGHT side of a RIGHT
 JOIN. That's what the directions mean

Interesting - do you have a link to more information on this bug?

 SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted
 FROM student s
 INNER JOIN enrollment e 
 ON e.tech_id = s.tech_id
 AND e.term_id = '3'
 LEFT JOIN submitted_assignment sa 
 ON sa.tech_id = s.tech_id 
 LEFT JOIN assignment a 
 ON a.a_id = sa.a_id 
 AND a.a_id = '100'
 ORDER BY s.full_name;
 
 I also moved the clause AND a.a_id = '100' into the ON portion of
 the LEFT JOIN. That way you can see who did and didn't get that
 assignment.
 
 If you describe what you WANT to see, I can help you write the query
 to get it. What I think I wrote for you will be all students where
 term_ID=3 and what grades they got on assignment 100. But i think
 you may get some duplicate rows of blank scores. Does assignment
 relate to student, perhaps with a tech_id or student_id field?
 That fixes one dilemma by setting up the following query
 
 SELECT s.tech_id, s.full_name, if(isnull(a.a_ID),'no','yes') as 
 assigned_100, sa.points_awarded, sa.date_submitted
 FROM student s
 INNER JOIN enrollment e 
 ON e.tech_id = s.tech_id
 AND e.term_id = '3'
 LEFT JOIN assignment a 
 ON a.student_ID = s.student_ID
 AND a.a_id = '100'
 LEFT JOIN submitted_assignment sa 
 ON sa.tech_id = s.tech_id 
 AND a.a_id = sa.a_id 
 ORDER BY s.full_name;

This is what I eventually used:

SELECT s.tech_id, s.full_name, 
   sa.points_awarded, sa.date_submitted
FROM student s
INNER JOIN enrollment e 
ON e.tech_id = s.tech_id
LEFT JOIN submitted_assignment sa 
   ON sa.tech_id = s.tech_id AND sa.a_id = '$a_id'
LEFT JOIN assignment a 
   ON a.a_id = sa.a_id
WHERE e.term_id = '3'
ORDER BY s.full_name;

It didn't seem to work with the AND sa.a_id = '$a_id' in the assignment join 
condition - but this works.  I don't understand why that doesn't filter the right rows 
if it's in the WHERE clause, I usually define my JOIN condition solely on the 
column(s) being joined together and any additional filtering gets done in the WHERE 
clause.

Man, I thought I had a good handle on OUTER JOINs.  Erg.

Josh



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

OUTER JOIN bug in 5.0.1?

2004-09-17 Thread Josh Trutwin

Is there a known bug with outer joins in MySQL 5.0.1?

I tried the following query on 5.0.1:

SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted
FROM student s
INNER JOIN enrollment e ON e.tech_id = s.tech_id
INNER JOIN submitted_assignment sa ON sa.tech_id = s.tech_id 
RIGHT JOIN assignment a ON a.a_id = sa.a_id 
WHERE a.a_id = '100' AND e.term_id = '3'
ORDER BY s.full_name;

None of the outer results are present.  At first I thought the query
was wrong, but if I run the same exact query using the same tables
(from a mysqldump) on a 4.0.20 server I get the expected results
including student's who have not yet submitted the assignment.

Running on SuSE linux 5.0.1 compiled from source.

Any more details I can provide?  I have to imagine that something like
this has already been found.  Is the 5.0.1 snapshot on the products
download page updated frequently?  If so I guess I could try to
download and install again.

Josh

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

Re: html in a text field - good practice?

2004-08-18 Thread Josh Trutwin

On Wed, 18 Aug 2004 11:37:03 -0400
leegold [EMAIL PROTECTED] wrote:

 Question I have wondered about: Is it a good practice to put html in
 a text field, then (eg. via php) when the marked-up text renders in
 a user's browser it's good looking html. If not, then I'd just
 sandwitch field content in a p/p when it's rendered. Though,
 seems like it would mess-up fulltext searching in a marked-up text
 field(?). Thanks. Lee G.

I never cared for it, but if you HAVE to, my recommendation is to do something like 
this:

$clean_html = htmlentities($dirty_html, ENT_QUOTES);
mysql_query(INSERT INTO table (html_field) VALUES ('$clean_html'));

Then when you need to display the HTML, after pulling the data from the database use:

$html = mysql_entity_decode($html_from_db, ENT_QUOTES); //requires php  4.3.0

The htmlentities converts characters like quotes, , , etc. to nice text the database 
won't have any problems storing and prevents SQL injection attacks (it's a good idea 
to use htmlentities on ANY text field you take from an untrusted source and insert 
into a database)

I would also strip out script tags and research cross site scripting prevention 
which you are in danger of having problems with if you blindly store submitted HTML 
from the Internet such as on in a bulletin board app.

http://www.php.net/htmlentities
http://www.php.net/html_entity_decode

Josh

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

Re: Difference between PostgreSQL and MySQL

On Tue, 10 Aug 2004 23:40:39 +0200
Jochem van Dieten [EMAIL PROTECTED] wrote:

 On Tue, 10 Aug 2004 16:49:26 -0400, [EMAIL PROTECTED]
 [EMAIL PROTECTED] wrote:
  Well, since you admitted to being a newbie, I would suggest that
  you learn with MySQL. It supports several types of data storage
  (memory only, ISAM, full-relational) and both transacted and
  non-transacted execution models. That's just about everything you
  could want a database to do.
 
 No, not really: http://sql-info.de/mysql/gotchas.html

Good link - thanks.

Josh

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

Re: Difference between PostgreSQL and MySQL

On Tue, 10 Aug 2004 23:34:49 +0200
Jochem van Dieten [EMAIL PROTECTED] wrote:

 On Tue, 10 Aug 2004 16:00:12 -0500, Josh Trutwin wrote:
  
  One area where MySQL beat Postgres is in Windows installation. 
  Installing postgres on Windohs is like pulling your fingernails
  off slowly.
 
 It is more like following the manual. Not hard, you just have to
 take it one step at a time.

I should point out that I haven't used the compile-from-source method that's in the 
documentation, which like you said looks pretty simple.  I can get postgres running in 
a cygwin env pretty easilly, but getting it to automatically start/stop as a service 
is usually the painful part.  I've used this as a bonus exercise for students in a 
dbms course I teach and their conclusion is the same.  

I think this complaint will fall away soon though with PG 8.x.  FWIW, PG on *nix is 
just the opposite, very clean and simple install.

  MySQL's command line interface and programming API also are nicer
  for newer users.  Why in the world do I need to remember to type
  \d to show my tables?
 
 Why in the world do I need to remember SHOW TABLES? Why can't the
 standard information schema work?
 :-)

Ah, because SHOW TABLES and exit or quit makes sense and is easy to remember vs. 
\d \q \whatever.  I like the \d because once you know it it's dang quick to type, but 
it's nice to have the longer format as well.  Whatever, to each their own...

 Command line interface and programming API are pretty much
 irrelevant if you are using ColdFusion. It is all abstracted out
 behind a JDBC API (ColdFusion is still at JDC 2), which in turn is
 behind ColdFusion's API, so unless you go the way of the Java ninja
 you can't even reach it.

Ok.  Command line interface and programming API are never truely irrelevent IMHO.  :)

I said it before, I like em both, use em both and have rubs with both of em.  Is one 
better than the other?  Yes and No.

Thanks,

Josh


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

Re: [OT] PostgreSQL / MySQL Data Dictionary

On Wed, 11 Aug 2004 17:20:45 +0200
Jochem van Dieten [EMAIL PROTECTED] wrote:

 SHOW TABLES does not make sense. How are you going to join the
 output of SHOW TABLES against the output of SHOW COLUMNS and SHOW
 INDEXES?
 
 SELECT * FROM INFORMATION_SCHEMA.TABLES makes sense.
 
 
 And as for easy remembering: I prefer to remember just one standard,
 instead of the idiosyncracies of each product.

Yes, a queryable (sp?) set of dictionary tables/views would be nice for doing this.  
The MySQL set of SHOW commands is pretty painful for any serious development.

Does PostgreSQL have a set of information schema tables to query against like Oracle 
does (e.g. SELECT table_name FROM user_tables)?

I noticed this from a quick google search:

http://gborg.postgresql.org/project/orapgsqlviews/projdisplay.php

Is something similar planned for MySQL in the future?  Doesn't appear to be from: 
http://dev.mysql.com/doc/mysql/en/Roadmap.html

Josh

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

Re: MySQL Data Dictionary (INFORMATION_SCHEMA)