Re: help needed restoring crashed mysql

[Reindl Harald]

Am 30.11.2011 07:02, schrieb Hal?sz S?ndor:
> 2011/11/29 23:19 +0100, Reindl Harald 
> MY only luck is that i recognized this years ago after PLAYING
> with innodb and so i started with "innodb_file_per_table=1" from
> the begin with the first production database
> 
> And are then the table-files in the directories with "frm", or in the 
> directory where "ibdata1" is?
> 
> If the latter, one problem is exchanged for another.

they are in the db-folder but even if not it is a hughe differene
if "optimize table tablename" free space on disk or not

[root@mail:/mysql_data]$ ls
insgesamt 3,0G
drwx-- 2 mysql mysql 4,0K 2011-11-25 10:27 dbmail
drwx-- 2 mysql mysql 4,0K 2011-11-20 17:46 mysql
drwx-- 2 mysql mysql 4,0K 2011-11-20 17:46 performance_schema
drwx-- 2 mysql mysql 4,0K 2011-11-30 04:00 syslog
-rw-rw 1 mysql mysql 354M 2011-11-30 14:01 ibdata1
-rw-rw 1 mysql mysql 512M 2011-11-30 14:01 ib_logfile0
-rw-rw 1 mysql mysql 512M 2011-11-30 02:21 ib_logfile1
-rw-rw 1 mysql mysql6 2011-11-20 17:46 mysql_upgrade_info
-rw-rw 1 mysql mysql 1,1G 2011-11-29 15:21 bin.000137
-rw-rw 1 mysql mysql 617M 2011-11-30 14:01 bin.000138
-rw-rw 1 mysql mysql   72 2011-11-29 15:21 bin.index

[root@mail:/mysql_data]$ ls dbmail/
insgesamt 9,5G
-rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 cms1_config.frm
-rw-rw 1 mysql mysql 8,7K 2011-09-18 08:18 cms1_global_cache.frm
-rw-rw 1 mysql mysql 9,4K 2011-09-18 08:18 cms1_haupt.frm
-rw-rw 1 mysql mysql 8,6K 2011-09-18 08:18 cms1_locks.frm
-rw-rw 1 mysql mysql 9,8K 2011-09-18 08:18 cms1_meta.frm
-rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 cms1_snippets.frm
-rw-rw 1 mysql mysql  11K 2011-09-18 08:18 cms1_sub2.frm
-rw-rw 1 mysql mysql  11K 2011-09-18 08:18 cms1_sub.frm
-rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 cms1_user_group_permissions.frm
-rw-rw 1 mysql mysql 8,6K 2011-09-18 08:18 cms1_user_login.frm
-rw-rw 1 mysql mysql 9,6K 2011-09-18 08:18 cms1_user_modules.frm
-rw-rw 1 mysql mysql 8,5K 2011-09-14 09:32 cms1_user_online.frm
-rw-rw 1 mysql mysql 9,3K 2011-09-18 08:18 cms1_users.frm
-rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_aliases.frm
-rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_aliases_global.frm
-rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_allowed_hosts.frm
-rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_client_admins.frm
-rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_clients.frm
-rw-rw 1 mysql mysql 8,8K 2011-10-22 20:18 dbmail_acl.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_aliases.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_auto_notifications.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_auto_replies.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_ccfield.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_datefield.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:19 dbmail_envelope.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:19 dbmail_fromfield.frm
-rw-rw 1 mysql mysql 8,4K 2011-10-22 20:19 dbmail_headername.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:19 dbmail_headervalue.frm
-rw-rw 1 mysql mysql 8,9K 2011-10-22 20:20 dbmail_mailboxes.frm
-rw-rw 1 mysql mysql 8,6K 2011-07-24 12:17 dbmail_messageblks.frm
-rw-rw 1 mysql mysql 8,9K 2011-10-22 20:20 dbmail_messages.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_pbsp.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_physmessage.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_referencesfield.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_replycache.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_replytofield.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_sievescripts.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_subjectfield.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_subscription.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_tofield.frm
-rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_usermap.frm
-rw-rw 1 mysql mysql 8,9K 2011-10-22 20:21 dbmail_users.frm
-rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_mta.frm
-rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_recipient_relay.frm
-rw-rw 1 mysql mysql 1,8K 2011-11-25 10:27 dbma_recipients.frm
-rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_reply_groups.frm
-rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_rewrite_domains.frm
-rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_rewrite_senders.frm
-rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_sender_relay.frm
-rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_spamfilter.frm
-rw-rw 1 mysql mysql 1,7K 2011-07-24 11:49 dbma_stats.frm
-rw-rw 1 mysql mysql 8,5K 2011-08-27 22:39 dbma_transports_error.frm
-rw-rw 1 mysql mysql 1,5K 2011-07-24 11:49 dbma_transports.frm
-rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
-rw-rw 1 mysql mysql  64K 2011-09-18 0

Re: help needed restoring crashed mysql

[Hal�sz S�ndor]

 2011/11/29 23:19 +0100, Reindl Harald 
MY only luck is that i recognized this years ago after PLAYING
with innodb and so i started with "innodb_file_per_table=1" from
the begin with the first production database

And are then the table-files in the directories with "frm", or in the 
directory where "ibdata1" is?

If the latter, one problem is exchanged for another.


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

Re: help needed restoring crashed mysql

[Reindl Harald]

Am 30.11.2011 03:13, schrieb Karen Abgarian:
> The concept is not difficult to explain.  Most people do not expect a gas 
> tank 
> to shrink once the gas is consumed...right? 

yes, but the hard-disk is the gas tank and the data are the gas
and yes, normally everybody would expect after deleting data that
the space is available for other applications




signature.asc
Description: OpenPGP digital signature

Re: help needed restoring crashed mysql

[Karen Abgarian]

>>> 

Hi... and some more stuff inline. 


>> 
>> Well, I would not base my database design on luck and playing.   There 
>> should be good awareness 
>> of what the features do and what would be the plan to deal with file 
>> allocations should the database
>> grow, shrink or somerset
> 
> if you are working many years with mysql and myisam you
> normally do not expect this - and no my work depends never
> really on luck and that is why it look in ALL datadirs of
> all software i am using and missed the table files known
> from my isam
> 
> most peopole DO NOT care about this and not expect that
> allocated space will not be freed nor makes it any sense
> to have a whole database-server to dump/import because
> you get rid of big databases

I am not saying there is anything wrong with your experience.   I understand 
that people having worked with
MyISAM might have a different mindset.  But they at some point did make a 
decision to use InnoDB.   I think 
this is the time when I would ask myself a question: where is it that my data 
will be stored.   

The concept is not difficult to explain.  Most people do not expect a gas tank 
to shrink once the gas is 
consumed...right?  


> 
>> Another piece of logic is that it is not really typical for the databases
>> to lose 50% of its volume.
> 
> well, so install http://www.dbmail.org/ with replication and offsite-backups
> of the slave, get rid of your biggest mail-user and think about how useful
> it is to waste all this space as before multiple times in the backup-storages
> 
I didn't have a pleasure to use dbmail.   I presume it does something with mail 
users.
Thinking logically,  if I got rid of my biggest mail user, I might eventually 
get another user, 
even bigger one, which would consume the same space vacated by the deceased 
user.  
So why would I want to give up the space then?

> it did never happen to me - but i heard so many people start whining because
> the mysql-defaults and these are most pepole which do not have our knowledge
> to handle this before and in this case also not able to handle dump/import
> in a production environment
> 

If the people do not have the knowledge to do exports/imports, the brand new 
and cool
file management feature will not help them either.  Essentially, the 
instructions how to use
that feature are written in the same book as the instructions how to do 
export/imports.  If we
consider it as given that people would never read, it is a dead end. 



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

Re: help needed restoring crashed mysql

[Reindl Harald]

Am 30.11.2011 01:11, schrieb Karen Abgarian:
>> MY only luck is that i recognized this years ago after PLAYING
>> with innodb and so i started with "innodb_file_per_table=1" from
>> the begin with the first production database
> 
> Well, I would not base my database design on luck and playing.   There should 
> be good awareness 
> of what the features do and what would be the plan to deal with file 
> allocations should the database
> grow, shrink or somerset

if you are working many years with mysql and myisam you
normally do not expect this - and no my work depends never
really on luck and that is why it look in ALL datadirs of
all software i am using and missed the table files known
from my isam

most peopole DO NOT care about this and not expect that
allocated space will not be freed nor makes it any sense
to have a whole database-server to dump/import because
you get rid of big databases

> Another piece of logic is that it is not really typical for the databases
> to lose 50% of its volume.

well, so install http://www.dbmail.org/ with replication and offsite-backups
of the slave, get rid of your biggest mail-user and think about how useful
it is to waste all this space as before multiple times in the backup-storages

it did never happen to me - but i heard so many people start whining because
the mysql-defaults and these are most pepole which do not have our knowledge
to handle this before and in this case also not able to handle dump/import
in a production environment



signature.asc
Description: OpenPGP digital signature

Re: help needed restoring crashed mysql

[Karen Abgarian]

On Nov 29, 2011, at 11:50 AM, Claudio Nanni wrote:

> 
> This is not to say that MySQL could not  have more of the file management
> features.  For example, the ability to add or remove datafiles on the fly and 
> the
> ability to detach tablespaces as collections of tables.
> 
> That's where MySQL(read InnoDB) got stuck actually, it never introduced a 
> powerful datafiles management system,
> and that is where Oracle excels (as far as being almost a O.S.) with multiple 
> level of abstractions, just think of ASM. 
> It is actually the part of Oracle I like most as well as the really 
> "oraclish" way to get stats out of it!
>  The 'problem' with MySQL is that it is so easy to start with it that people 
> do not realize that is also a real RDBMS.
> --

Yes, Oracle has features of the known kind.   But, if we consider the wishlist 
for InnoDB, then on top there would be an
ability to add files without stopping database (that is without innodb 
tablespaces), followed by the ability to control 
which files are part of the database (information schema of that).  

It looks like at some point they have decided that the file per table thing 
will cover all needs.   Well, it doesn't.   I personally 
do not like dealing with 50G files should individual table grow to this size.   
It will be much better managed with a larger collection
of smaller files, without innodb tablespaces.   It is after I have those I 
would want the features like removing files or compacting
the database.  

Re: help needed restoring crashed mysql

[Karen Abgarian]

Hi... there is stuff inline there.  

>> The logic behind this is probably that without innodb_file_per_table=1
>> and with several large ibdata files, the space IS freed up when one does
>> optimize table or drop table.  The space is freed up inside the database
>> files and can be reused.  
> 
> well, and if you have this day 2 TB mysql-data and a year later
> get rid of 1 TB of it they allocated space can be REUSED for
> innodb but never for any other application

I did not say it is the right thing to not have an option to shrink the 
database or do file management. 
I tried to explain the logic that is probably put into this product.   Another 
piece of logic is that it is 
not really typical for the databases to lose 50% of its volume.   The databases 
usually either grow, 
or can grow, or are destroyed.  In that regard the product with this feature 
lacking probably still covers 
the needs of most.  By comparison, Oracle did not provide ability to drop the 
datafiles until, eh, version 8, 
I believe, and it was not made easy until version 10.  

> 
>> If we think about it, the database product should only resolve problems of
>> the database space management, not of the OS space management. 
> 
> the database producht with default settings is the part starting
> the troubles of os-space-managment and this is idiotic, no other
> words for this!

I would say inconvenient.   As I explained above, the OS space allocation 
problems that way could 
be considered a corner case and thus be considered unimportant by MySQL 
development.   Considering 
the problem of reclaiming 1 terabyte out of 2-terabyte database, one could 
resolve it with creating a brand
new instance followed by export/import of data.   It is not that there is no 
solution, it is inconvenient to use. 

> MY only luck is that i recognized this years ago after PLAYING
> with innodb and so i started with "innodb_file_per_table=1" from
> the begin with the first production database

Well, I would not base my database design on luck and playing.   There should 
be good awareness 
of what the features do and what would be the plan to deal with file 
allocations should the database
grow, shrink or somerset.  

> but the cases where ibdata1 is growing becasue ONCE bigger
> data was stored and never release the allocated space is a
> design-problem
> 

Not exactly.  A design problem is to build a server in such a way as that 
adding a feature to remove datafiles 
would be impossible (without major rebuild).   I think this one can be added.  
I didn't bother to check, but I 
would be surprised if there isn't already an enhancement request for this

Re: help needed restoring crashed mysql

[Reindl Harald]

Am 29.11.2011 20:25, schrieb Karen Abgarian:
> 
> On 29.11.2011, at 5:21, Reindl Harald wrote:
>> why is this dumb "innodb_file_per_table=0" default since MOST PEOPLE
>> have only troubles with it because they can not free space with
>> "optimize table" with no real benefits?
>
> The logic behind this is probably that without innodb_file_per_table=1
> and with several large ibdata files, the space IS freed up when one does
> optimize table or drop table.  The space is freed up inside the database
> files and can be reused.  

well, and if you have this day 2 TB mysql-data and a year later
get rid of 1 TB of it they allocated space can be REUSED for
innodb but never for any other application

> If we think about it, the database product should only resolve problems of
> the database space management, not of the OS space management. 

the database producht with default settings is the part starting
the troubles of os-space-managment and this is idiotic, no other
words for this!

MY only luck is that i recognized this years ago after PLAYING
with innodb and so i started with "innodb_file_per_table=1" from
the begin with the first production database

> the user essentially asked InnoDB to keep allocating arbitrary amount of space
> as needed, ignoring that the OS disk is actually of the limited size.  To be
> correct about it, the user should have stated that the ibdata file should 
> have a
> firm limit and not autoextend beyond that. 

yes this case is a user-problem

but the cases where ibdata1 is growing becasue ONCE bigger
data was stored and never release the allocated space is a
design-problem



signature.asc
Description: OpenPGP digital signature

Re: help needed restoring crashed mysql

[Claudio Nanni]

>
>
> This is not to say that MySQL could not  have more of the file management
> features.  For example, the ability to add or remove datafiles on the fly
> and the
> ability to detach tablespaces as collections of tables.


That's where MySQL(read InnoDB) got stuck actually, it never introduced a
powerful datafiles management system,
and that is where Oracle excels (as far as being almost a O.S.) with
multiple level of abstractions, just think of ASM.
It is actually the part of Oracle I like most as well as the really
"oraclish" way to get stats out of it!
 The 'problem' with MySQL is that it is so easy to start with it that
people do not realize that is also a real RDBMS.
--
Claudio

Making innodb tablespaces default... well, it still would not liberate the
> users
> from thinking whether they want to run with them enabled or not.   For
> example,
> if I have 1 tables of 100 bytes each, I probably do not want
> tablespaces.
> If I have 1% of tables consuming 99% of the space, I would also not want
> the
> tablespaces.
>
> As for the OP's problem, unless he changed his mind about the need to
> import,
> the same amount of space would anyway be consumed.   The solution would
> probably be to find some bigger O Sdisk and copy that ibdata file there.
> Right?
>
>
> Cheers
> Karen
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
Claudio

Re: help needed restoring crashed mysql

[Karen Abgarian]

On 29.11.2011, at 5:21, Reindl Harald wrote:

> 
> ibdata1 does NEVER get smaller, this is normal and a hughe problem
> in your case, only if you are using "innodb_file_per_table" which
> is NOT default would retire the space after drop tables
> 
> why is this dumb "innodb_file_per_table=0" default since MOST PEOPLE
> have only troubles with it because they can not free space with
> "optimize table" with no real benefits?
> 

Hi...

The logic behind this is probably that without innodb_file_per_table=1
and with several large ibdata files, the space IS freed up when one does
optimize table or drop table.  The space is freed up inside the database
files and can be reused.  

If we think about it, the database product should only resolve problems of
the database space management, not of the OS space management.   Then, 
the user essentially asked InnoDB to keep allocating arbitrary amount of space
as needed, ignoring that the OS disk is actually of the limited size.  To be
correct about it, the user should have stated that the ibdata file should have a
firm limit and not autoextend beyond that. 

This is not to say that MySQL could not  have more of the file management 
features.  For example, the ability to add or remove datafiles on the fly and 
the
ability to detach tablespaces as collections of tables.   

Making innodb tablespaces default... well, it still would not liberate the users
from thinking whether they want to run with them enabled or not.   For example, 
if I have 1 tables of 100 bytes each, I probably do not want tablespaces. 
If I have 1% of tables consuming 99% of the space, I would also not want the
tablespaces. 

As for the OP's problem, unless he changed his mind about the need to import, 
the same amount of space would anyway be consumed.   The solution would 
probably be to find some bigger O Sdisk and copy that ibdata file there.   
Right?


Cheers
Karen 


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

Re: help needed restoring crashed mysql

[Reindl Harald]

Am 29.11.2011 14:08, schrieb Luis Pugoy:
> Hello. I have the following problem.
> 
> I was importing a large database to mysql using mysqldump. Unfortunately this 
> filled up the whole disk, and
> mysqldump exited with an error that the table it is currently writing to is 
> full. Checking df -h it shows that the
> disk usage is at 100%. I decided to drop the database I was importing, but 
> rechecking df -h shows that it is still
> at 100%. I then looked for the cause of this and found a very large file 
> under /var/lib/mysql. I think it is
> ibdata1.

ibdata1 does NEVER get smaller, this is normal and a hughe problem
in your case, only if you are using "innodb_file_per_table" which
is NOT default would retire the space after drop tables

why is this dumb "innodb_file_per_table=0" default since MOST PEOPLE
have only troubles with it because they can not free space with
"optimize table" with no real benefits?



signature.asc
Description: OpenPGP digital signature

Re: Help needed with what appears to be a corrupted innodb db

[Ananda Kumar]

Pito,

can u show us the innodb parameters in the my.cnf file.

regards
anandkl

On Sat, Jan 8, 2011 at 10:31 PM, Pito Salas  wrote:

> I am very new to trying to solve a problem like this and have searched
> and searched the web for a useful troubleshooting guide but I am
> honestly stuck. I wonder if anyone out there could take a look at this
> bit of mysqld log. Any help at all would be greatly appreciated!!!
>
> Pito
>
> 110107 15:07:15  mysqld started
> 110107 15:07:15  InnoDB: Database was not shut down normally!
> InnoDB: Starting crash recovery.
> InnoDB: Reading tablespace information from the .ibd files...
> InnoDB: Restoring possible half-written data pages from the doublewrite
> InnoDB: buffer...
> 110107 15:07:15  InnoDB: Starting log scan based on checkpoint at
> InnoDB: log sequence number 35 515914826.
> InnoDB: Doing recovery: scanned up to log sequence number 35 515915839
> InnoDB: 1 transaction(s) which must be rolled back or cleaned up
> InnoDB: in total 1 row operations to undo
> InnoDB: Trx id counter is 0 1697553664
> 110107 15:07:15  InnoDB: Starting an apply batch of log records to the
> database...
> InnoDB: Progress in percents: 26 27 28 29 30 31 32 33 34 35 36 37 38
> 39 40 41 42 43 44
> 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
> 68 69 70 71 72 73
> 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
> 99
> InnoDB: Apply batch completed
> InnoDB: Starting rollback of uncommitted transactions
> InnoDB: Rolling back trx with id 0 1697553198, 1 rows to undoInnoDB:
> Error: trying to
> access page number 3522914176 in space 0,
> InnoDB: space name ./ibdata1,
> InnoDB: which is outside the tablespace bounds.
> InnoDB: Byte offset 0, len 16384, i/o type 10
> 110107 15:07:15InnoDB: Assertion failure in thread 3086403264 in file
> fil0fil.c line
> 3922
> 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=0
> read_buffer_size=131072
> max_used_connections=0
> max_connections=100
> threads_connected=0
> It is possible that mysqld could use up to
> key_buffer_size + (read_buffer_size +
> sort_buffer_size)*max_connections = 217599 K
> bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
>
> thd=(nil)
> 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=0xbffc55ac, backtrace may not be correct.
> Stack range sanity check OK, backtrace follows:
> 0x8139eec
> 0x83721d5
> 0x833d897
> 0x833db71
> 0x832aa38
> 0x835f025
> 0x835f7a3
> 0x830a77e
> 0x8326b57
> 0x831c825
> 0x8317b8d
> 0x82a9e66
> 0x8315732
> 0x834fc9a
> 0x828d7c3
> 0x81c29dd
> 0x81b5620
> 0x813d9fe
> 0x40fdf3
> 0x80d5ff1
> 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 The manual page at 
> http://www.mysql.com/doc/en/Crashing.htmlcontains
> information that should help you find out what is causing the crash.
> 110107 15:07:15  mysqld ended
>
> --
> Check out http://www.salas.com and http://www.blogbridge.com/look
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
>
>

RE: Help needed on query on multiple tables

[Steven Staples]

I am glad that I was able to help someone finally :)

There may be other ways to do this, but that was what first came to mind.
I would maybe run an explain on that query to ensure that it is using
indexes.

Steven Staples


> -Original Message-
> From: Michael Stroh [mailto:st...@astroh.org]
> Sent: June 3, 2010 11:55 AM
> To: Steven Staples
> Cc: 'MySql'
> Subject: Re: Help needed on query on multiple tables
> 
> Thanks! That did it perfectly!
> 
> Michael
> 
> 
> On Jun 3, 2010, at 11:45 AM, Steven Staples wrote:
> 
> > How about this?
> >
> > SELECT
> >`first_table`.`names`
> >, `first_table`.`version`
> >, (SELECT
> >   COUNT(`other_table`.`names`)
> >   FROM `other_table`
> >   WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count'
> > FROM `first_table`
> > WHERE `first_table`.`progress` > 0;
> >
> >
> > Granted, you have not provided structure or names of the tables so this
> is
> > just my interpretation, but maybe something like this could give you a
> > starting point?
> >
> > Steven Staples
> >
> >
> >> -Original Message-
> >> From: Michael Stroh [mailto:st...@astroh.org]
> >> Sent: June 3, 2010 11:24 AM
> >> To: MySql
> >> Subject: Help needed on query on multiple tables
> >>
> >> Hi everyone. I'm trying to create a certain MySQL query but I'm not
sure
> >> how to do it. Here is a stripped down version of the result I'm aiming
> > for.
> >> I'm pretty new to queries that act on multiple tables, so apologize if
> > this
> >> is a very stupid question.
> >>
> >> I have one table (data) that has two columns (names and progress). I
> have
> > a
> >> second table (items) that has two columns (names and version). I'd like
> to
> >> do a query that produces the name of every record in data that has
> > progress
> >> set to 0 and the number of records in the items table that have the
same
> >> value in each table.names field.
> >>
> >> I can perform this by using two sets of queries, one that queries the
> data
> >> table and then loop through the names to do a count(names) query, but
> I'm
> >> not sure if I can somehow do it in one query.
> >>
> >> Thanks in advance!
> >> Michael
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
> >>
> >> No virus found in this incoming message.
> >> Checked by AVG - www.avg.com
> >> Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date:
06/03/10
> >> 02:25:00
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org
> >
> 
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10
> 02:25:00


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Help needed on query on multiple tables

[Michael Stroh]

Thanks! That did it perfectly!

Michael


On Jun 3, 2010, at 11:45 AM, Steven Staples wrote:

> How about this?
> 
> SELECT
>`first_table`.`names`
>, `first_table`.`version`
>, (SELECT
>   COUNT(`other_table`.`names`)
>   FROM `other_table`
>   WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count'
> FROM `first_table`
> WHERE `first_table`.`progress` > 0;
> 
> 
> Granted, you have not provided structure or names of the tables so this is
> just my interpretation, but maybe something like this could give you a
> starting point?
> 
> Steven Staples
> 
> 
>> -Original Message-
>> From: Michael Stroh [mailto:st...@astroh.org]
>> Sent: June 3, 2010 11:24 AM
>> To: MySql
>> Subject: Help needed on query on multiple tables
>> 
>> Hi everyone. I'm trying to create a certain MySQL query but I'm not sure
>> how to do it. Here is a stripped down version of the result I'm aiming
> for.
>> I'm pretty new to queries that act on multiple tables, so apologize if
> this
>> is a very stupid question.
>> 
>> I have one table (data) that has two columns (names and progress). I have
> a
>> second table (items) that has two columns (names and version). I'd like to
>> do a query that produces the name of every record in data that has
> progress
>> set to 0 and the number of records in the items table that have the same
>> value in each table.names field.
>> 
>> I can perform this by using two sets of queries, one that queries the data
>> table and then loop through the names to do a count(names) query, but I'm
>> not sure if I can somehow do it in one query.
>> 
>> Thanks in advance!
>> Michael
>> 
>> 
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
>> 
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10
>> 02:25:00
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Help needed on query on multiple tables

[Steven Staples]

How about this?

SELECT
`first_table`.`names`
, `first_table`.`version`
, (SELECT
   COUNT(`other_table`.`names`)
   FROM `other_table`
   WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count'
FROM `first_table`
WHERE `first_table`.`progress` > 0;


Granted, you have not provided structure or names of the tables so this is
just my interpretation, but maybe something like this could give you a
starting point?

Steven Staples


> -Original Message-
> From: Michael Stroh [mailto:st...@astroh.org]
> Sent: June 3, 2010 11:24 AM
> To: MySql
> Subject: Help needed on query on multiple tables
> 
> Hi everyone. I'm trying to create a certain MySQL query but I'm not sure
> how to do it. Here is a stripped down version of the result I'm aiming
for.
> I'm pretty new to queries that act on multiple tables, so apologize if
this
> is a very stupid question.
> 
> I have one table (data) that has two columns (names and progress). I have
a
> second table (items) that has two columns (names and version). I'd like to
> do a query that produces the name of every record in data that has
progress
> set to 0 and the number of records in the items table that have the same
> value in each table.names field.
> 
> I can perform this by using two sets of queries, one that queries the data
> table and then loop through the names to do a count(names) query, but I'm
> not sure if I can somehow do it in one query.
> 
> Thanks in advance!
> Michael
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net
> 
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10
> 02:25:00


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Help needed

[Jerry Schwartz]

I suggest you check out http://en.wikipedia.org/wiki/UUID. It seems to have
a pretty current, but not overwhelming, explanation. Originally the starting
point for a UUID was the MAC (hardware) address of the network interface.
Theoretically, there should only be one device with any particular MAC
address in the whole world. There are exceptions, but they would be
mistakes.

For your purposes, you can assume that it is a unique string of 32
hexadecimal digits.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com

>-Original Message-
>From: Velen [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, May 13, 2008 11:28 AM
>To: Jerry Schwartz
>Cc: mysql@lists.mysql.com
>Subject: Re: Help needed
>
>Hi Jerry,
>
>Thanks for the tip.
>
>What is UUID look for?  is it an ID associated with the motherboard,
>CPU,
>harddisk?
>
>Thanks.
>
>Regards,
>
>Velen
>
>
>- Original Message -
>From: "Jerry Schwartz" <[EMAIL PROTECTED]>
>To: "'Garris, Nicole'" <[EMAIL PROTECTED]>;
>
>Sent: Monday, May 12, 2008 11:43 PM
>Subject: RE: Help needed
>
>
>> Your best bet would be to use a UUID:
>>
>> SELECT UUID();
>> +--+
>> | uuid()   |
>> +--+
>> | c712dc72-718d-102b-b3c8-97395a1766b9 |
>> +--+
>>
>> There are equivalents in various programming languages, although not
>in
>PHP.
>>
>> Regards,
>>
>> Jerry Schwartz
>> The Infoshop by Global Information Incorporated
>> 195 Farmington Ave.
>> Farmington, CT 06032
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>> www.the-infoshop.com
>> www.giiexpress.com
>> www.etudes-marche.com
>>
>> >-Original Message-
>> >From: Garris, Nicole [mailto:[EMAIL PROTECTED]
>> >Sent: Monday, May 12, 2008 11:59 AM
>> >To: mysql@lists.mysql.com
>> >Subject: RE: Help needed
>> >
>> > Sorry, you can't prove uniqueness by running it against a hundred
>> >thousand, million, or even a billion computers. (The billionth-and-
>first
>> >computer could be the one with the non-unique ID.) You need a
>> >mathematical proof to prove uniqueness.
>> >
>> >-Original Message-
>> >From: Velen [mailto:[EMAIL PROTECTED]
>> >Sent: Monday, May 12, 2008 8:32 AM
>> >To: Arthur Fuller
>> >Cc: mysql@lists.mysql.com
>> >Subject: Re: Help needed
>> >
>> >Hi Arthur,
>> >
>> >Could you please tell this guy that it was not a virus and you tested
>it
>> >without any problem?
>> >
>> >Thanks.
>> >
>> >Velen
>> >
>> >
>> >On Sat, 2008-05-10 at 20:59 +0400, Velen wrote:
>> >
>> >> Hi,
>> >>
>> >> I'm testing a program and I need you assistance.
>> >>
>> >> Please unzip the file at http://www.biz-mu.com/PCID.zip and run the
>> >program.
>> >
>> >Please supply a Linux version of your virus so I can test.
>> >
>> >--
>> >Daniel Kasak
>> >IT Developer
>> >NUS Consulting Group
>> >Level 5, 77 Pacific Highway
>> >North Sydney, NSW, Australia 2060
>> >T: (+61) 2 9922-7676 / F: (+61) 2
>> >9922 7989
>> >email: [EMAIL PROTECTED]
>> >website:
>> >http://www.nusconsulting.com.au
>> >
>> >
>> >
>> >  - Original Message -
>> >  From: Arthur Fuller
>> >  To: Velen
>> >  Sent: Saturday, May 10, 2008 10:44 PM
>> >  Subject: Re: Help needed
>> >
>> >
>> >  On my main machine the PCID is 135184-45-4-10-1513-1.
>> >
>> >  Hope it helps.
>> >  Arthur
>> >
>> >
>> >  On Sat, May 10, 2008 at 12:59 PM, Velen <[EMAIL PROTECTED]> wrote:
>> >
>> >Hi,
>> >
>> >I'm testing a program and I need you assistance.
>> >
>> >Please unzip the file at http://www.biz-mu.com/PCID.zip and run
>the
>> >program.  It will display an ID, please mail me back the ID.
>> >
>> >If you can use it on several computers, it will be even better
>for
>> >me, I need to have as much results as possible.
>> >
>> >The program i'm testing is supposed to create a unique ID for
>each
>> >PC, this is why I need to test if it is really unique.
>> >
>> >This file is virus free and cannot do any harm to your PC.  It is
>> >however a voluntary testing.
>> >
>> >Thanks for your help in advance.
>> >
>> >
>> >
>> >Velen
>> >
>> >
>> >--
>> >MySQL General Mailing List
>> >For list archives: http://lists.mysql.com/mysql
>> >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>> >infoshop.com
>>
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>>
>>





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

Re: Help needed

[Velen]

Hi Jerry,

Thanks for the tip.

What is UUID look for?  is it an ID associated with the motherboard, CPU,
harddisk?

Thanks.

Regards,

Velen


- Original Message -
From: "Jerry Schwartz" <[EMAIL PROTECTED]>
To: "'Garris, Nicole'" <[EMAIL PROTECTED]>; 
Sent: Monday, May 12, 2008 11:43 PM
Subject: RE: Help needed


> Your best bet would be to use a UUID:
>
> SELECT UUID();
> +--+
> | uuid()   |
> +--+
> | c712dc72-718d-102b-b3c8-97395a1766b9 |
> +--+
>
> There are equivalents in various programming languages, although not in
PHP.
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
> >-Original Message-
> >From: Garris, Nicole [mailto:[EMAIL PROTECTED]
> >Sent: Monday, May 12, 2008 11:59 AM
> >To: mysql@lists.mysql.com
> >Subject: RE: Help needed
> >
> > Sorry, you can't prove uniqueness by running it against a hundred
> >thousand, million, or even a billion computers. (The billionth-and-first
> >computer could be the one with the non-unique ID.) You need a
> >mathematical proof to prove uniqueness.
> >
> >-Original Message-
> >From: Velen [mailto:[EMAIL PROTECTED]
> >Sent: Monday, May 12, 2008 8:32 AM
> >To: Arthur Fuller
> >Cc: mysql@lists.mysql.com
> >Subject: Re: Help needed
> >
> >Hi Arthur,
> >
> >Could you please tell this guy that it was not a virus and you tested it
> >without any problem?
> >
> >Thanks.
> >
> >Velen
> >
> >
> >On Sat, 2008-05-10 at 20:59 +0400, Velen wrote:
> >
> >> Hi,
> >>
> >> I'm testing a program and I need you assistance.
> >>
> >> Please unzip the file at http://www.biz-mu.com/PCID.zip and run the
> >program.
> >
> >Please supply a Linux version of your virus so I can test.
> >
> >--
> >Daniel Kasak
> >IT Developer
> >NUS Consulting Group
> >Level 5, 77 Pacific Highway
> >North Sydney, NSW, Australia 2060
> >T: (+61) 2 9922-7676 / F: (+61) 2
> >9922 7989
> >email: [EMAIL PROTECTED]
> >website:
> >http://www.nusconsulting.com.au
> >
> >
> >
> >  - Original Message -
> >  From: Arthur Fuller
> >  To: Velen
> >  Sent: Saturday, May 10, 2008 10:44 PM
> >  Subject: Re: Help needed
> >
> >
> >  On my main machine the PCID is 135184-45-4-10-1513-1.
> >
> >  Hope it helps.
> >  Arthur
> >
> >
> >  On Sat, May 10, 2008 at 12:59 PM, Velen <[EMAIL PROTECTED]> wrote:
> >
> >Hi,
> >
> >I'm testing a program and I need you assistance.
> >
> >Please unzip the file at http://www.biz-mu.com/PCID.zip and run the
> >program.  It will display an ID, please mail me back the ID.
> >
> >If you can use it on several computers, it will be even better for
> >me, I need to have as much results as possible.
> >
> >The program i'm testing is supposed to create a unique ID for each
> >PC, this is why I need to test if it is really unique.
> >
> >This file is virus free and cannot do any harm to your PC.  It is
> >however a voluntary testing.
> >
> >Thanks for your help in advance.
> >
> >
> >
> >Velen
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >infoshop.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


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

Re: Help needed

[omni]

On Mon, May 12, 2008 at 8:31 AM, Velen <[EMAIL PROTECTED]> wrote:

> Hi Arthur,
>
> Could you please tell this guy that it was not a virus and you tested it
> without any problem?
>

One guy that is brave/crazy/stupid enough to run a binary on his machine
that didn't have anything bad happen to him does not make it safe or a good
idea for the rest of us. And for all we know, you could have two email
accounts just to trick the rest of us into running your
trojan/keylogger/virus/botnet/whatever.

Not a chance.

Re: Help needed

[Arthur Fuller]

I tested the program before running it and it is virus-clean. I ran it
through AVG first and it's clean. I ran it in Windows. I don't know whether
there is a Linux version.

Arthur

On Mon, May 12, 2008 at 11:31 AM, Velen <[EMAIL PROTECTED]> wrote:

>  Hi Arthur,
>
> Could you please tell this guy that it was not a virus and you tested it
> without any problem?
>
> Thanks.
>
> Velen
>

RE: Help needed

[Jerry Schwartz]

Your best bet would be to use a UUID:

SELECT UUID();
+--+
| uuid()   |
+--+
| c712dc72-718d-102b-b3c8-97395a1766b9 |
+--+

There are equivalents in various programming languages, although not in PHP.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com

>-Original Message-
>From: Garris, Nicole [mailto:[EMAIL PROTECTED]
>Sent: Monday, May 12, 2008 11:59 AM
>To: mysql@lists.mysql.com
>Subject: RE: Help needed
>
> Sorry, you can't prove uniqueness by running it against a hundred
>thousand, million, or even a billion computers. (The billionth-and-first
>computer could be the one with the non-unique ID.) You need a
>mathematical proof to prove uniqueness.
>
>-Original Message-
>From: Velen [mailto:[EMAIL PROTECTED]
>Sent: Monday, May 12, 2008 8:32 AM
>To: Arthur Fuller
>Cc: mysql@lists.mysql.com
>Subject: Re: Help needed
>
>Hi Arthur,
>
>Could you please tell this guy that it was not a virus and you tested it
>without any problem?
>
>Thanks.
>
>Velen
>
>
>On Sat, 2008-05-10 at 20:59 +0400, Velen wrote:
>
>> Hi,
>>
>> I'm testing a program and I need you assistance.
>>
>> Please unzip the file at http://www.biz-mu.com/PCID.zip and run the
>program.
>
>Please supply a Linux version of your virus so I can test.
>
>--
>Daniel Kasak
>IT Developer
>NUS Consulting Group
>Level 5, 77 Pacific Highway
>North Sydney, NSW, Australia 2060
>T: (+61) 2 9922-7676 / F: (+61) 2
>9922 7989
>email: [EMAIL PROTECTED]
>website:
>http://www.nusconsulting.com.au
>
>
>
>  - Original Message -
>  From: Arthur Fuller
>  To: Velen
>  Sent: Saturday, May 10, 2008 10:44 PM
>  Subject: Re: Help needed
>
>
>  On my main machine the PCID is 135184-45-4-10-1513-1.
>
>  Hope it helps.
>  Arthur
>
>
>  On Sat, May 10, 2008 at 12:59 PM, Velen <[EMAIL PROTECTED]> wrote:
>
>Hi,
>
>I'm testing a program and I need you assistance.
>
>Please unzip the file at http://www.biz-mu.com/PCID.zip and run the
>program.  It will display an ID, please mail me back the ID.
>
>If you can use it on several computers, it will be even better for
>me, I need to have as much results as possible.
>
>The program i'm testing is supposed to create a unique ID for each
>PC, this is why I need to test if it is really unique.
>
>This file is virus free and cannot do any harm to your PC.  It is
>however a voluntary testing.
>
>Thanks for your help in advance.
>
>
>
>Velen
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>infoshop.com





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

RE: Help needed

[Garris, Nicole]

 Sorry, you can't prove uniqueness by running it against a hundred
thousand, million, or even a billion computers. (The billionth-and-first
computer could be the one with the non-unique ID.) You need a
mathematical proof to prove uniqueness.

-Original Message-
From: Velen [mailto:[EMAIL PROTECTED] 
Sent: Monday, May 12, 2008 8:32 AM
To: Arthur Fuller
Cc: mysql@lists.mysql.com
Subject: Re: Help needed

Hi Arthur,

Could you please tell this guy that it was not a virus and you tested it
without any problem?

Thanks.

Velen


On Sat, 2008-05-10 at 20:59 +0400, Velen wrote:

> Hi,
> 
> I'm testing a program and I need you assistance.  
> 
> Please unzip the file at http://www.biz-mu.com/PCID.zip and run the
program.

Please supply a Linux version of your virus so I can test.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2
9922 7989
email: [EMAIL PROTECTED]
website:
http://www.nusconsulting.com.au



  - Original Message -
  From: Arthur Fuller
  To: Velen
  Sent: Saturday, May 10, 2008 10:44 PM
  Subject: Re: Help needed


  On my main machine the PCID is 135184-45-4-10-1513-1.

  Hope it helps.
  Arthur


  On Sat, May 10, 2008 at 12:59 PM, Velen <[EMAIL PROTECTED]> wrote:

Hi,

I'm testing a program and I need you assistance.

Please unzip the file at http://www.biz-mu.com/PCID.zip and run the
program.  It will display an ID, please mail me back the ID.

If you can use it on several computers, it will be even better for
me, I need to have as much results as possible.

The program i'm testing is supposed to create a unique ID for each
PC, this is why I need to test if it is really unique.

This file is virus free and cannot do any harm to your PC.  It is
however a voluntary testing.

Thanks for your help in advance.



Velen


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

Re: Help needed

[Velen]

Hi Arthur,

Could you please tell this guy that it was not a virus and you tested it 
without any problem?

Thanks.

Velen


On Sat, 2008-05-10 at 20:59 +0400, Velen wrote:

> Hi,
> 
> I'm testing a program and I need you assistance.  
> 
> Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program.

Please supply a Linux version of your virus so I can test.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2
9922 7989
email: [EMAIL PROTECTED]
website:
http://www.nusconsulting.com.au



  - Original Message - 
  From: Arthur Fuller 
  To: Velen 
  Sent: Saturday, May 10, 2008 10:44 PM
  Subject: Re: Help needed


  On my main machine the PCID is 135184-45-4-10-1513-1.

  Hope it helps.
  Arthur


  On Sat, May 10, 2008 at 12:59 PM, Velen <[EMAIL PROTECTED]> wrote:

Hi,

I'm testing a program and I need you assistance.

Please unzip the file at http://www.biz-mu.com/PCID.zip and run the 
program.  It will display an ID, please mail me back the ID.

If you can use it on several computers, it will be even better for me, I 
need to have as much results as possible.

The program i'm testing is supposed to create a unique ID for each PC, this 
is why I need to test if it is really unique.

This file is virus free and cannot do any harm to your PC.  It is however a 
voluntary testing.

Thanks for your help in advance.



Velen

Re: Help needed

[Daniel Kasak]

On Sat, 2008-05-10 at 20:59 +0400, Velen wrote:

> Hi,
> 
> I'm testing a program and I need you assistance.  
> 
> Please unzip the file at http://www.biz-mu.com/PCID.zip and run the program.

Please supply a Linux version of your virus so I can test.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2
9922 7989
email: [EMAIL PROTECTED]
website:
http://www.nusconsulting.com.au



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

Re: Help needed to tune Innodb on ZFS (on Solaris)

[JOUANNET, Rodolphe]

Hi all,

You can read this article, written by a SUN benchmarking guru (hi
Dimitri :) ).

Best regards.

Web link : http://dimitrik.free.fr/db_STRESS_BMK_Part2_ZFS.html

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

Re: Help needed to tune Innodb on ZFS (on Solaris)

[Nico Sabbi]

Il Wednesday 09 April 2008 19:40:32 Rob Wultsch ha scritto:
> On Wed, Apr 9, 2008 at 5:51 AM, Nico Sabbi <[EMAIL PROTECTED]> wrote:
> > The problem I have is that importing the same 7 MB sql dump
> > takes 9 seconds if engine=Myisam and 98 when engine is Innodb.
> >
> 
> Is autocommit turned off?
> 
> http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
> 
> " When importing data into InnoDB, make sure that MySQL does not have
> autocommit mode enabled because that requires a log flush to disk for
> every insert. To disable autocommit during your import operation,
> surround it with SET AUTOCOMMIT and COMMIT statements: SET
> AUTOCOMMIT=0;
> ... SQL import statements ...
> COMMIT;
> "

yes, autocommit is off

> 
> There is also a note in there about forcedirectio and certain solaris setups.

I read it, but directio seems to be unavailable on ZFS (as fas as I can tell)



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

Re: Help needed to tune Innodb on ZFS (on Solaris)

[Rob Wultsch]

On Wed, Apr 9, 2008 at 5:51 AM, Nico Sabbi <[EMAIL PROTECTED]> wrote:
> The problem I have is that importing the same 7 MB sql dump
> takes 9 seconds if engine=Myisam and 98 when engine is Innodb.
>

Is autocommit turned off?

http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html

" When importing data into InnoDB, make sure that MySQL does not have
autocommit mode enabled because that requires a log flush to disk for
every insert. To disable autocommit during your import operation,
surround it with SET AUTOCOMMIT and COMMIT statements: SET
AUTOCOMMIT=0;
... SQL import statements ...
COMMIT;
"

There is also a note in there about forcedirectio and certain solaris setups.

-- 
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)

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

Re: HELP needed for speeding up a query!

[Chris]

[ always cc the list so others can share the fix or make appropriate 
comments ]


Nicholas Wyatt wrote:

hi chris,

thanks for answering! however, i do already have indexes on those
columns. all my tables use the myisam storage engine. what are the
differences you mentioned between these engines when using MIN()?


myisam is a non-transactional table type, so only 1 client can 
update/delete/insert into it at once (other clients attempting to do 
this will be locked until the insert/update/delete finishes). That means 
the table itself can keep information about the largest value, the 
smallest value and the number of rows the table has.


innodb is a transactional table type, which means many clients can 
update/delete/insert into it at once. That means the table cannot keep 
information about values and the number of rows because at any time it 
could change in another client.


http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html


i did find a way to speed up the query, by the way. i joined the tables
the other way around. the query now takes about 0,5 seconds.

SELECT site.site_id, site.site_title, site_url, site.site_testtype, 
site.project_id, test.test_kt_points,

MIN(test_item.ti_evaluation) as completed,
IF (site.site_date < site.site_enddate, site.site_enddate, 
site.site_date) AS sort_date

FROM test_item
LEFT JOIN test ON (test.test_id = test_item.test_id)
LEFT JOIN site ON (site.site_id = test.site_id)
WHERE site.site_testtype IN (-1, 0, 1, 2)
GROUP BY site.site_id


Interesting.

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

Re: HELP needed for speeding up a query!

[Chris]

Nicholas Wyatt wrote:

Hello to all!

has anybody got any ideas how i can speed up the following query?
it's so awfully slow (about 1 second).

the "test_item" table is the main problem. currently, it has
about 108.000 entries.

is it normal that it takes so long? and if it isn't, how can i
optimize the query? i've got indices on all relevant columns.


SELECT site.site_id, site.site_title, site.site_testtype, 
test.test_kt_points,

MIN(test_item.ti_evaluation) as completed,
IF (site.site_date < site.site_enddate, site.site_enddate, 
site.site_date) AS sort_date

FROM site
LEFT JOIN test ON (site.site_id = test.site_id)
LEFT JOIN test_item ON (test.test_id = test_item.test_id)
WHERE site.site_testtype IN (-1, 0, 1, 2)
GROUP BY site.site_id


Do you have indexes on these tables/columns:

site.site_id, test.site_id, test.test_id, test_item.test_id ?

What sort of tables are they (innodb or myisam)? That will make a 
difference mainly because of the MIN() call.


Try creating an index on test_item.ti_evaluation:

create index ti_evaluation on test_item(ti_evaluation)

or even

create index ti_evaluation on test_item(test_id, ti_evaluation)

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

Re: help needed to mount data from /var/lib/mysql

[balaraju mandala]

if i search for any process running i am getting following thing.

[EMAIL PROTECTED] mysql]# ps -ef | grep mysqld
root 18389 1  0 13:09 pts/300:00:00 /bin/sh /usr/bin/mysqld_safe
--defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid
mysql18422 18389  0 13:09 pts/300:00:00 /usr/libexec/mysqld
--defaults-file=/etc/my.cnf --basedir=/usr --datadir=/home/mysql
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking
--socket=/home/mysql/mysql.sock
root 18725 15850  0 13:47 pts/300:00:00 grep mysqld

i am new to this concept please help me.

Re: help needed to mount data from /var/lib/mysql

[balaraju mandala]

I am unable to start server after shifting to new location.

i tried to start 'mysqld' but it was failed. A blank mysql.sock file is
creating. Entries of log files are also not reporting any problem.

Re: help needed to mount data from /var/lib/mysql

[balaraju mandala]

Hi All,

Thank you for u r reply. But i am unable to find my.cnf, is i need to create
this file.

Re: help needed to mount data from /var/lib/mysql

[ViSolve DB Team]

Hello,

You can change the MySQL data path in /etc/my.cnf  by editing the 
configuration parameter "datadir" with new data path. Once you done the 
changes in my.cnf, reboot the MySQL server.


Thanks,
ViSolve DB Team


- Original Message - 
From: "balaraju mandala" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, August 19, 2006 12:28 PM
Subject: help needed to mount data from /var/lib/mysql



Hi Everybody,

I need small help from you. In my Linux box i have limitation of Size in
partition. I have only 5 GB space for /var. MySQL is installed in this
partition only. I want to give another path like /home, where i have 120GB
of space. Somebody told me that we need to change the data path of MySQL,
but he is not sure of that. Can anybody knows abt this?







No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.3/423 - Release Date: 8/18/2006


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

Re: help needed to mount data from /var/lib/mysql

[chris smith]

On 8/19/06, balaraju mandala <[EMAIL PROTECTED]> wrote:

Hi Everybody,

I need small help from you. In my Linux box i have limitation of Size in
partition. I have only 5 GB space for /var. MySQL is installed in this
partition only. I want to give another path like /home, where i have 120GB
of space. Somebody told me that we need to change the data path of MySQL,
but he is not sure of that. Can anybody knows abt this?


Edit the my.cnf and change the data path.

Then read this page:

http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html

about how to set up your new environment.

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

Re: Help Needed

[Gabriel PREDA]

If you do an INSERT and you generate an new number in an AUTO_INCREMENT
field the new value generated can be retrieved using:
*SELECT LAST_INSERT_ID();*

You are not required to retrieve into the application and then use it back
in another SQL statement... you can use user variables:
*SELECT @lastGenerated:=LAST_INSERT_ID();*

And then use: the variable in another query:
*INSERT INTO someTbl (id, someCol, somCol2) VALUES (NULL, @lastGenerated,
'OutsideValue');
*
Be warned that LAST_INSERT_ID() is functional only if you did not specified
the AUTO_INCREMENT field's value... only if you did not specified the
AUTO_INCREMENT field at all... or in the insert statement you used NULL or 0
as a value... those values are considered "magic" by MySQL in case of an
AUTO_INCREMENT field.

Now part 2... if you didn't created an AUTO_INCREMENT field... and only want
to use the last value from that table:
Either you use a MAX() request:
*SELECT @lastGenerated:=MAX(columnName) FROM tableName*
Or:
*SHOW TABLE STATUS LIKE 'tableName'*
and somewhere in the result is the AUTO_INCREMENT value.

Good luck !

--
Gabriel PREDA
Senior Web Developer

Re: Help needed for SQL statement

[Alvaro Cobo]

Is this what you are looking for:

SELECT clone_ids, COUNT(DISTINCT(gene_ids)) as count_genes
FROM table_name
GROUP BY clone_ids

or

SELECT clone_ids, COUNT(gene_ids) as count_genes
FROM table_name
GROUP BY clone_ids

Hope this helps,

Alvaro
- Original Message -
From: "Xiaobo Chen" <[EMAIL PROTECTED]>
To: 
Sent: Monday, October 24, 2005 11:43 PM
Subject: Help needed for SQL statement


> Hi,
>
> I have such a situation:
>
> There is a table with gene_ids and clone_ids. Each gene only resides on a
> single clone and each clone may contain multiple genes. How do would I
> find how many genes are on each and every clone?
>
> Thanks in advance.
>
> X.Chen
>
>
> --
> 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: Help needed regarding MySQL-C Libraries on Linux....

[Gleb Paharenko]

Hello.



This is an output of diff user.c user.cpp. Which successfully compiles on

my FC3 system using the following command:

  g++ user.cpp -I/usr/include/mysql  -lmysqlclient 



I've changed the extension to 'cpp', so g++ could work with this file.

Also #include was commented, and main now returns int.



[EMAIL PROTECTED] pgm]$ diff  user.c user.cpp

2c2

< #include 

---

> //#include 

17c17

< void main()

---

> int main()

52a53

>







Ashok Kumar <[EMAIL PROTECTED]> wrote:

> [-- text/plain, encoding 8bit, charset: iso-8859-1, 29 lines --]

> 

> Dear Friends,

> I'm Ashok here. first i'll tell my sys configuration.

> 1.Redhat-Linux 9

> 2.Intel Pentium chipset

> 3.MySQL 4.1 with Development Libraries

> 

> For this i've downloaded the MySQL4.1 under the

> specification "Linux x86 RPM downloads" from the site.

> 

> In my system, the MySQL Server is working well. I

> tried to run the C program that will make the

> connection to the MySQL database and create the user

> database and useracc table.

> It's finely working with Windows-VC++. But when i'm

> trying to run with Linux gcc, I'm getting lots of

> errors. I don't know what's the problem. I've attached

> the C-Pgm and the Error info.

> 

> Pls, help me regarding this.

> 

> Thanks and Regards,

> Ashok.

> 

> 

>

>  

> Yahoo! Sports 

> Rekindle the Rivalries. Sign up for Fantasy Football 

> http://football.fantasysports.yahoo.com

> [-- application/zip, encoding base64, 97 lines, name: Pgm_Err.zip --]

> [-- Description: 3707653052-Pgm_Err.zip --]

> 

> [-- text/plain, encoding 7bit, charset: us-ascii, 5 lines --]

> 

> 



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




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

Re: Help needed regarding MySQL-C Libraries on Linux....

[Mir Islam]

The same source that you compiled under VC++ may not work at all under
linux. Especially since the header files will be different. Make sure
you have the proper header files for linux installed. Also I did not
see any linking flags during compilation.

On 7/3/05, Ashok Kumar <[EMAIL PROTECTED]> wrote:
> Dear Friends,
>  I'm Ashok here. first i'll tell my sys configuration.
> 1.Redhat-Linux 9
> 2.Intel Pentium chipset
> 3.MySQL 4.1 with Development Libraries
> 
>  For this i've downloaded the MySQL4.1 under the
> specification "Linux x86 RPM downloads" from the site.
> 
>  In my system, the MySQL Server is working well. I
> tried to run the C program that will make the
> connection to the MySQL database and create the user
> database and useracc table.
>  It's finely working with Windows-VC++. But when i'm
> trying to run with Linux gcc, I'm getting lots of
> errors. I don't know what's the problem. I've attached
> the C-Pgm and the Error info.
> 
>  Pls, help me regarding this.
> 
> Thanks and Regards,
>  Ashok.
> 
> 
> 
> 
> Yahoo! Sports
> Rekindle the Rivalries. Sign up for Fantasy Football
> http://football.fantasysports.yahoo.com
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
>

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

Re: Help needed regarding MySQL-C Libraries on Linux....

[Philippe Poelvoorde]

Ashok Kumar wrote:

Dear Friends,
 I'm Ashok here. first i'll tell my sys configuration.
1.Redhat-Linux 9
2.Intel Pentium chipset
3.MySQL 4.1 with Development Libraries

 For this i've downloaded the MySQL4.1 under the
specification "Linux x86 RPM downloads" from the site.

 In my system, the MySQL Server is working well. I
tried to run the C program that will make the
connection to the MySQL database and create the user
database and useracc table.
 It's finely working with Windows-VC++. But when i'm
trying to run with Linux gcc, I'm getting lots of
errors. I don't know what's the problem. I've attached
the C-Pgm and the Error info.




[EMAIL PROTECTED] CGI]# gcc -c -I/usr/lib/bcc/include -I/usr/include/mysql 
user.c



is BCC the 8086 compiler ? then I'm not sure gcc can deal with those 
includes.
To get the proper flags, I'll advise you to use the mysql_config program 
like :

/usr/local/mysql/bin/mysql_config --cflags

(you didn't attach your program, so I can't see what is going on)

--
Philippe Poelvoorde
COS Trading Ltd.

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

RE: Help needed regarding MySQL-C Libraries on Linux....

[Ashok Kumar]

Hi Prashanth,
 I'm happy to help u. i attached one 'C' code in the
previous mail. just use that code for in vc++. Follow
the procedure,
 1.Create the new "win32 Console App." as a empty
project and add the attached 'C' file to the project.
 2.goto "Tools->options->Directories(tab)" and in that
add the corresponding MySQL Include path and as well
as the lib->debug path.
 3.goto "Project->settings->Link(tab)->General
Category->add the name 'libmysql.lib' in lib modules"
 4.Save the project.
 5.Add the MySQL lib->debug path to the environment
path variable, which is found in
ctrl-panel->system->Advanced->env.var->path (for the
dynamic linking of libmysql.dll file).
 6.Restart the system (for effect of env path var).
 7.Run the project.

 This c pgm will create one database called "user" and
table named "useracc" under the db "user".

Note:
 Don't forget to change the username and password vars
in the pgm if u set anything like that for ur MySQL.

 For further info on C-MySQL read the following page,
some sample chapters are there, that will be useful.
 
http://www.quepublishing.com/articles/article.asp?p=30494&seqNum=2&rl=1

 If u have any doubts means, send that to the group,
rather than sending to the seperate person and so that
reply will be useful for others also.

Thanks and Regards,
 Ashok.



--- "Prashanth H. Baragur"
<[EMAIL PROTECTED]> wrote:

> Ashok,
> 
> Can u do me a favor.
> Am trying to update the mysql server in windows
> using VC++ and C code. 
> Am very new to this domain. Can u please send the
> code u are using in
> VC++ to me?
> 
> Your help would be greatly appreciate.
> Thanks
> prashanth
> 
> -Original Message-
> From: Ashok Kumar [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, June 30, 2005 3:34 PM
> To: mysql mysql
> Subject: Help needed regarding MySQL-C Libraries on
> Linux
> 
> Dear Friends,
>  I'm Ashok here. first i'll tell my sys
> configuration.
> 1.Redhat-Linux 9
> 2.Intel Pentium chipset
> 3.MySQL 4.1 with Development Libraries
> 
>  For this i've downloaded the MySQL4.1 under the
> specification "Linux x86 RPM downloads" from the
> site.
> 
>  In my system, the MySQL Server is working well. I
> tried to run the C program that will make the
> connection to the MySQL database and create the user
> database and useracc table.
>  It's finely working with Windows-VC++. But when i'm
> trying to run with Linux gcc, I'm getting lots of
> errors. I don't know what's the problem. I've
> attached
> the C-Pgm and the Error info.
> 
>  Pls, help me regarding this.
> 
> Thanks and Regards,
>  Ashok.
> 
> 
>   
> 
> 
> Yahoo! Sports 
> Rekindle the Rivalries. Sign up for Fantasy Football
> 
> http://football.fantasysports.yahoo.com
> 




 
Yahoo! Sports 
Rekindle the Rivalries. Sign up for Fantasy Football 
http://football.fantasysports.yahoo.com

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

Re: Help needed with complex Query

[Philip Lawatsch]

[EMAIL PROTECTED] wrote:

>>I pretty much have no idea how I can do this without nested queries (and
>>to be frank not even how to do it with them) so I'd really appreciate
>>any help!
> 
> 
>>kind regards Philip
> 
> 
> Try this as a starting point:



> I think you were having two mental problems: 1) how to GROUP BY across 
> tables (creating a JOIN). and 2) How to use a HAVING clause. If you Refer 
> To the Fine Manual (RTFM) you can get examples and more explanations of 
> both processes.

Thanks a lot, this did the trick!

kind regards Philip

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

Re: Help needed with complex Query

[Brent Baisley]

Unless you are keeping track of whether a widget in complete or not, 
there is no hope but to scan the entire table to determine if a widget 
is complete. That's something you don't want to do. You should mark a 
widget as complete when it is completed. This would mean checking if a 
particular widget is completed when a modification occurs.
Really what you are going to be doing is running the "loop" you spoke 
of in bits and pieces of time and only for widgets that require 
checking/updating. Then you only need to query on the completed field, 
which will be very fast.


To do it in a single query, you going to need to use count and group by 
and then check for completion using HAVING (as opposed to where).


On Jun 6, 2005, at 1:37 PM, Philip Lawatsch wrote:


Hi,

I'm trying hard to figure out how to perform a special query in mysql 
4.0.


I have one table "widgets" which has a column "widget-id" (int) and one
column "number_of_parts" (int).

And then I have another table "part_mapping" which has one column
"widget-id" (int) and one column "part_id" (int).

part_id is unique throughout the "part_mapping" table.

The idea is that every widget consists of several unique parts.

Now I want to select all widgets which are complete, this means where

SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ...  equals the
number_of_parts of "widget-id" in table widgets.

What I could do is simply "loop" over table "widgets" and execute a
select count for every wiget. This would result in a huge number if
queries needed form my client which is something I'd like to avoid.

I pretty much have no idea how I can do this without nested queries 
(and

to be frank not even how to do it with them) so I'd really appreciate
any help!

kind regards Philip

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




--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


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

Re: Help needed with complex Query

[gerald_clark]

Philip Lawatsch wrote:


Hi,

I'm trying hard to figure out how to perform a special query in mysql 4.0.

I have one table "widgets" which has a column "widget-id" (int) and one
column "number_of_parts" (int).

And then I have another table "part_mapping" which has one column
"widget-id" (int) and one column "part_id" (int).

part_id is unique throughout the "part_mapping" table.

The idea is that every widget consists of several unique parts.

Now I want to select all widgets which are complete, this means where

SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ...  equals the
number_of_parts of "widget-id" in table widgets.

What I could do is simply "loop" over table "widgets" and execute a
select count for every wiget. This would result in a huge number if
queries needed form my client which is something I'd like to avoid.

I pretty much have no idea how I can do this without nested queries (and
to be frank not even how to do it with them) so I'd really appreciate
any help!

kind regards Philip

 


Try this:
SELECT widgets.widget-id, number_of_parts, count(partid) AS cnt
FROM widgets INNER JOIN part_mapping
ON widgets.widget-id = part_mapping.widget-id
GROUP BY widgets.widget-id,  number_of_parts
HAVING cnt = number_of_parts;



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

Re: Help needed with complex Query

[SGreen]

Philip Lawatsch <[EMAIL PROTECTED]> wrote on 06/06/2005 01:37:37 PM:

> Hi,

> I'm trying hard to figure out how to perform a special query in mysql 
4.0.

> I have one table "widgets" which has a column "widget-id" (int) and one
> column "number_of_parts" (int).

> And then I have another table "part_mapping" which has one column
> "widget-id" (int) and one column "part_id" (int).

> part_id is unique throughout the "part_mapping" table.

> The idea is that every widget consists of several unique parts.

> Now I want to select all widgets which are complete, this means where

> SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ...  equals the
> number_of_parts of "widget-id" in table widgets.

> What I could do is simply "loop" over table "widgets" and execute a
> select count for every wiget. This would result in a huge number if
> queries needed form my client which is something I'd like to avoid.

> I pretty much have no idea how I can do this without nested queries (and
> to be frank not even how to do it with them) so I'd really appreciate
> any help!

> kind regards Philip

Try this as a starting point:

SELECT w.`widget-id`
, w.`number_of_parts`
, count(pm.`part_id`) as part_count
FROM widgets w
LEFT JOIN part_mapping pm
on w.`widget-id` = pm.`widget-id`
GROUP BY 1,2;

Wherever the column `number_of_parts` equals the computed value of 
part_count, you have a complete widget. Here is a query that returns only 
completed widgets:

SELECT w.`widget-id`
, w.`number_of_parts`
, count(pm.`part_id`) as part_count
FROM widgets w
LEFT JOIN part_mapping pm
on w.`widget-id` = pm.`widget-id`
GROUP BY 1,2
HAVING `number_of_parts` = `part_count`;

Here is one that returns incomplete widgets:

SELECT w.`widget-id`
, w.`number_of_parts`
, count(pm.`part_id`) as part_count
FROM widgets w
LEFT JOIN part_mapping pm
on w.`widget-id` = pm.`widget-id`
GROUP BY 1,2
HAVING `number_of_parts` > `part_count`;

Here is the query that tell you that construction on these widgets hasn't 
even started:

SELECT w.`widget-id`
, w.`number_of_parts`
, count(pm.`part_id`) as part_count
FROM widgets w
LEFT JOIN part_mapping pm
on w.`widget-id` = pm.`widget-id`
GROUP BY 1,2
HAVING `part_count` = 0;

I think you were having two mental problems: 1) how to GROUP BY across 
tables (creating a JOIN). and 2) How to use a HAVING clause. If you Refer 
To the Fine Manual (RTFM) you can get examples and more explanations of 
both processes.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: help needed to create index

[Peter Normann]

Asha <> wrote:

> Is there a physical
> limitation in the InnoDb table structure as to why it can't
> support  FullText indexes?

http://dev.mysql.com/doc/mysql/en/fulltext-restrictions.html

http://dev.mysql.com/doc/mysql/en/innodb-restrictions.html


Peter Normann


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

Re: Help needed in creating primary key ,foreign key on a varchar datatype colum

[Rhino]

I just checked the definition of CREATE TABLE in the MySQL manual
 and I didn't see
anything that suggests that primary keys based on VARCHAR columns need to be
treated differently than other column types in MySQL. However, when I tried
to create a table based on a VARCHAR(500) column, I got a message that said:

ERROR 1074: Too big column length for column 'account_number' (max = 255).
Use BLOB instead

Therefore, if your column is more than 255 characters long, you will have to
use BLOB instead of VARCHAR. If your column is 255 or fewer characters long,
change your column definition to CHAR from VARCHAR.

Also, I believe you will have to include the phrase 'Type=INNODB' in your
table definition; I think only the INNODB engine actually enforces FOREIGN
KEY constraints.

Here is an example of creating two tables, a parent and a child, that use
the INNODB engine:

drop table if exists dept;
create table dept(
deptno char(3) not null,
deptname varchar(36) not null,
mgrno char(6),
primary key(deptno)
) Type=InnoDB;

drop table if exists emp;
create table emp(
empno char(6) not null,
firstnme char(12) not null,
midinit char(1),
lastname char(15) not null,
workdept char(3) not null,
salary dec(9,2) not null,
primary key(empno),
index(workdept),
foreign key(workdept) references dept(deptno) on delete restrict
) Type=InnoDB;

Table 'dept' is the parent table and contains one row for each department of
an imaginary company. Table 'emp' is the child table and contains one row
for each employee of the imaginary company. Each row in the 'emp' table has
a department number in its 'workdept' column; 'workdept' is the foreign key
and points to the 'deptno' column of the 'dept' table.

Rhino


- Original Message - 
From: "Dayakar" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, November 21, 2004 12:39 AM
Subject: Help needed in creating primary key ,foreign key on a varchar
datatype colum


Hello,

I am converting my database from oracle to mysql4.1 and i want the same
structure as it is oracle like primary key and foreign key references etc..

In oracle i have created my primary key and foreign key references on a
varchar datatype column, so can any one help me in doing the same in mysql.
I have tried doing the same by seeing the examples given in mysql manual but
not successfull.

If any one can help me by giving a example then it would be great help for
me.

regards
dayakar


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

Re: Help needed in creating primary key ,foreign key on a varchar datatype colum

[Heikki Tuuri]

Dayakar,
- Original Message - 
From: ""Dayakar"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Sunday, November 21, 2004 7:39 AM
Subject: Help needed in creating primary key ,foreign key on a varchar 
datatype colum


--=_NextPart_000_000B_01C4CFBA.91C9BA80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello,
I am converting my database from oracle to mysql4.1 and i want the same =
structure as it is oracle like primary key and foreign key references =
etc..=20
In oracle i have created my primary key and foreign key references on a =
varchar datatype column, so can any one help me in doing the same in =
mysql. I have tried doing the same by seeing the examples given in mysql =
manual but not successfull.
If any one can help me by giving a example then it would be great help =
for me.
[EMAIL PROTECTED]:~/mysql-4.1/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.8-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE t(a VARCHAR(100) NOT NULL PRIMARY KEY, b INT) TYPE = 
INNODB;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE t1(a1 INT NOT NULL PRIMARY KEY, b1 VARCHAR(50), FOREIGN 
KEY
(b1) REFERENCES t(a)) TYPE=INNODB;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
regards
dayakar
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help needed with MySQL C API-based client (segfault)

[Aftab Jahan Subedar]

have you tried ?
http://www.geocities.com/jahan.geo/mysql_c_by_example.html
Ruben Safir Secretary NYLXS wrote:
On Fri, Sep 19, 2003 at 09:18:22AM +0500, Vikram Vaswani wrote:
Hello,
I need to write a simple C client for a project. I am using the MySQL C
API. Attached is the code. It occassionally segfaults with no visible
pattern. Could someone help me figure out why? Or any other comments on the
code to help me make it better?

You know, I used to read this mailing list religiously before it became
flooded with W32 questions and PHP users.  And I've stay susbscribed 
but I haven't posted to it in many many months.  iIn fact, I didn't notice
I wasn't any longer subscribed.   So I'm writing this application in C 
and GTK and I was thinking, it's finally time to learn to write some MYSQL 
C API stuff.

I've written a lot of Oracle C programs in years past.  And now I'm looking 
at the C API stuff and wow, it is not readly understandable.  I open up
the mysql mail file with mutt, and bang, this is on the top!

I was going to ask the list if anyone has an exmaple of the basic needs
for a MYSQL program which makes a connection, sends a querry.  Checks the
potention errors, and maps the most basic column types to C types.
The docs say to look at examples in the source directory, but those 
aren't yet clear to me to understand.

Ruben

/* client.c */
#include 
#include 
int main()
{
   /* declare
structures and variables */
char query[255];
int i, j, count;
MYSQL mysql;
   MYSQL_RES *result;
   MYSQL_ROW row;
MYSQL_FIELD
*field;
   /* initialize MYSQL structure */
mysql_init(&mysql);
   /* connect to database */
   if
(!(mysql_real_connect(&mysql, NULL, "root", "", "db1", 0, NULL, 0)))
{
   fprintf(stderr, "Error in connection: %s\n",
mysql_error(&mysql));
   }
   for( ;; )
{
printf("query? ");
   	gets(query);
   	if (strcmp(query,"exit")
== 0) 
   	{
   	break;
   	}

/* execute query
*/
/* if error, display error message */
/* else check the type of
query and handle appropriately */
if (mysql_query(&mysql, query) != 0)
{
fprintf(stderr, "Error in query: %s\n", mysql_error(&mysql));
}
else
{
if (result = mysql_store_result(&mysql))
{
/* SELECT
query */
/* retrieve result set */
int numRecords =
mysql_num_rows(result);
int numFields = mysql_num_fields(result);
for (i = 0; i < numRecords; i++)
{
row =
mysql_fetch_row(result);
for (j = 0; j < numFields; j++)
{
//field= mysql_fetch_field(result);
fprintf(stdout, "%s", row[j]);
j != (numFields-1) ? printf(", ") : printf("\n");
}
}
fprintf(stdout, "** Query successful, %d rows retrieved **\n",
numRecords);
}
else
{
if (mysql_field_count(&mysql) == 0)
{
/* non-SELECT query */
fprintf(stdout, "** Query successful, %d
rows affected **\n", mysql_affected_rows(&mysql));
}
else
{
fprintf(stderr, "Error in reading result set: %s\n",
mysql_error(&mysql));
}
}
}
/* clean up */
mysql_free_result(result);
}
   mysql_close(&mysql);
}
--
I wouldn't recommend sex, drugs, and insanity for everyone, but it works
for me.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

--
Aftab Jahan Subedar
CEO/Software Engineer
Subedar Technologies
Subedar Baag
Bibir Bagicha #1
North Jatrabari
Dhaka 1204
Bangladesh
http://www.SubedarTechnologies.com
http://www.DhakaStockExchangeGame.com/
http://www.CEOBangladesh.com/
http://www.NYSEGame.com
tel://+88027519050
EMail://[EMAIL PROTECTED] - Directly to my notebook
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Help needed with MySQL C API-based client (segfault)

[Ruben Safir Secretary NYLXS]

On Fri, Sep 19, 2003 at 09:18:22AM +0500, Vikram Vaswani wrote:
> Hello,
> 
> I need to write a simple C client for a project. I am using the MySQL C
> API. Attached is the code. It occassionally segfaults with no visible
> pattern. Could someone help me figure out why? Or any other comments on the
> code to help me make it better?

You know, I used to read this mailing list religiously before it became
flooded with W32 questions and PHP users.  And I've stay susbscribed 
but I haven't posted to it in many many months.  iIn fact, I didn't notice
I wasn't any longer subscribed.   So I'm writing this application in C 
and GTK and I was thinking, it's finally time to learn to write some MYSQL 
C API stuff.

I've written a lot of Oracle C programs in years past.  And now I'm looking 
at the C API stuff and wow, it is not readly understandable.  I open up
the mysql mail file with mutt, and bang, this is on the top!

I was going to ask the list if anyone has an exmaple of the basic needs
for a MYSQL program which makes a connection, sends a querry.  Checks the
potention errors, and maps the most basic column types to C types.

The docs say to look at examples in the source directory, but those 
aren't yet clear to me to understand.


Ruben

> 
> /* client.c */
> 
> #include 
> #include 
> 
> int main()
> {
> /* declare
> structures and variables */
>   char query[255];
>   int i, j, count;
> 
> MYSQL mysql;
> MYSQL_RES *result;
> MYSQL_ROW row;
>   MYSQL_FIELD
> *field;
> 
> /* initialize MYSQL structure */
> 
> mysql_init(&mysql);
> 
> /* connect to database */
> if
> (!(mysql_real_connect(&mysql, NULL, "root", "", "db1", 0, NULL, 0)))
> 
>  {
> fprintf(stderr, "Error in connection: %s\n",
> mysql_error(&mysql));
> }
> 
> for( ;; )
>   {
> 
> printf("query? ");
>   gets(query);
>   if (strcmp(query,"exit")
> == 0) 
>   {
>   break;
>   }
> 
>   /* execute query
> */
>   /* if error, display error message */
>   /* else check the type of
> query and handle appropriately */
>   if (mysql_query(&mysql, query) != 0)
> 
> {
>   fprintf(stderr, "Error in query: %s\n", mysql_error(&mysql));
>   }
> 
> else
>   {
>   if (result = mysql_store_result(&mysql))
>   {
>   /* SELECT
> query */
>   /* retrieve result set */
>   int numRecords =
> mysql_num_rows(result);
>   int numFields = mysql_num_fields(result);
> 
> for (i = 0; i < numRecords; i++)
>   {
>   row =
> mysql_fetch_row(result);
> 
>   for (j = 0; j < numFields; j++)
>   {
> 
> //field= mysql_fetch_field(result);
>   fprintf(stdout, "%s", row[j]);
> 
>   j != (numFields-1) ? printf(", ") : printf("\n");
>   }
>   }
> 
> fprintf(stdout, "** Query successful, %d rows retrieved **\n",
> numRecords);
>   }
>   else
>   {
>   if (mysql_field_count(&mysql) == 0)
> 
> {
>   /* non-SELECT query */
>   fprintf(stdout, "** Query successful, %d
> rows affected **\n", mysql_affected_rows(&mysql));
>   }
>   else
>   {
> 
>   fprintf(stderr, "Error in reading result set: %s\n",
> mysql_error(&mysql));
>   }
>   }
>   }
> 
>   /* clean up */
> 
> mysql_free_result(result);
>   }
> mysql_close(&mysql);
> }
> 
> --
> I wouldn't recommend sex, drugs, and insanity for everyone, but it works
> for me.
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
__
Brooklyn Linux Solutions

So many immigrant groups have swept through our town 
that Brooklyn, like Atlantis, reaches mythological 
proportions in the mind of the world  - RI Safir 1998

DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002
http://fairuse.nylxs.com

http://www.mrbrklyn.com - Consulting
http://www.inns.net <-- Happy Clients
http://www.nylxs.com - Leadership Development in Free Software
http://www2.mrbrklyn.com/resources - Unpublished Archive or stories and articles from 
around the net
http://www2.mrbrklyn.com/downtown.html - See the New Downtown Brooklyn

1-718-382-0585

-- 
MySQL General Maili

Re: Help needed with a select statement

[SGreen]

What you want to do is "pivot your data into a crosstab report". This has 
been covered several times so you should be able to find more information 
if you search on either "pivot table", "crosstab" or "cross tab". 

Here's how to do it for a week's worth of dates, I will let you extend it 
to fit your needs. And, before you ask, there is no generic solution to 
this issue in MySQL. It is a fairly simple pattern which makes dynamic 
solutions fairly easy to script.

SELECT error
, SUM(if(date='2004-07-08',1,0)) as  '2004-07-08'
, SUM(if(date='2004-07-09',1,0)) as '2004-07-09'
, SUM(if(date='2004-07-10',1,0)) as '2004-07-10' 
, SUM(if(date='2004-07-11',1,0)) as '2004-07-11'
, SUM(if(date='2004-07-12',1,0)) as '2004-07-12'
, SUM(if(date='2004-07-13',1,0)) as '2004-07-13'
, SUM(if(date='2004-07-14',1,0)) as '2004-07-14'
FROM tablename
GROUP BY error;

I know it seems clumsy to need to define every column but it actually 
processes rather quickly.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Jonas Fischer <[EMAIL PROTECTED]> wrote on 08/03/2004 03:39:39 PM:

> Hello
> 
> right now I have a table which looks like this
> (including duplicate entries)
> 
> ErrorDate
> A0012004-07-08 
> A001   2004-07-08
> A222   2004-07-08
> A001   2004-07-09
> 
> 
> Now I query for the quantity one error occurs during a
> day (select Date, Error, 
> 
> Count(*) FROM table1 GROUP BY Date and Error). The
> result looks like this:
> 
> Date  Error   Quantity
> 2004-07-08   A001   2
> 2004-07-08   A222   1
> 2004-07-09   A001   1
> 
> 
> What I would like to have right now is a result which
> looks like this:
> 
> Date   2004-07-08   2004-07-08
> Error
> 
> A001   1  1
> A222   2  0
> 
> As a header I would like to have the dates and than
> display the error on the left side and the quantity of
> how often the error occures beneath under each date.
> 
> My problem is, that I do not always have an entry for
> every error per day. 
> 
> Anybody knows how to do that?
> 
> Thank you very much for your help.
> 
> Jonas
> 
> 
> 
> 
> 
> 
> ___
> Gesendet von Yahoo! Mail - Jetzt mit 100MB Speicher kostenlos - Hier
> anmelden: http://mail.yahoo.de
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: help needed!

[Martijn Tonies]

Hi Dominic,


> I think everyone is misunderstanding my problem as perhaps I didnt phrase
it very well.
>
> I need to add a new autonumber field to my db, but I need to make sure
when the numbers go into the field they are in the order of one of my other
fields that is a date field.
>
> i.e. I have a field which shows the date someone became a member - but my
current ID field doesnt match in order.
>
> So I need to add a new ID field so that numbers 1 - say 100 are say 1st
Jan 00 then numbers 101 - say 201 are say 2nd Jan 00 etc. etc. So that the
ID fields order matches the dates order.
>
> Now I can write SELECT * FROM table ORDER BY STARTDATE which orders the db
correctly, but then how do I add an autonumber field to it and keep the
records in that order?
>

Right - now things _are_ more clear :-)

Nevertheless, I have to ask you: why is this a requirement? What is the
auto-inc column supposed to be: if it's "only" a unique identifier, what
does the order matter?

As for a solution, I think the best thing you can do is rename this table,
create a new table with the same strucutre, but WITH an auto-inc column and
then do a INSERT INTO... SELECT ... FROM oldtable ORDER BY datecolumn.

With regards,

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


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

Re: help needed!

[Martijn Tonies]

Hi,

(please reply to the list only)

> Yes thanks but how do I then add a new auto number field and still keep
the order?

What order? What has an auto-number have to do with what order?

Please explain your problem in detail - it might be completely clear to you,
but we're outsiders :-)

With regards,

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


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

Re: help needed!

[Martijn Tonies]

Hi,

> I need to add a new auto field to my db, but I need to make sure the
records are listed in order of one of the other fields first which is a date
field.
>
> Any ideas how I do this would be greatly appreciated!

"listing records" is in no particular order, unless you ask for an order.

SELECT ... column names ...
FROM ... table name(s) ...
WHERE ... where clause ...
ORDER BY MyDateColumn

With regards,

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


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

Re: Help needed

[Roger Baklund]

* Unnar
> I have a problem with this query
>
> Version MySQL 4.0.15-nt
>
> The query gets all articles written by author but excludes those articles
> which are related to products
>
> SELECT DISTINCT
> post.post_id,
> post.title
> FROM
> c_posts post,
> c_post_product prod,
> authors_relation rel,
> c_post_properties prop
> WHERE
> rel.post_id != '$nPostId' // This is the post_id of the post which
> is currently being viewed ( works )
> AND
> rel.author_id = '$nAuthorId' // The author_id of the Author
> AND
> post.post_id = rel.post_id // To connect the two tables
> AND
> post.post_id = prop.post_id // The properties table
> AND
> post.post_id <> prod.post_id // This is where the problem is. I've
> also tried != . Details below**
> AND
> prop.state != 3
>
> ** I don't get any errors but the query just doesn't Exclude
> those post_id's
> which are in the c_post_product table which should be the case.

Try a LEFT JOIN, and check if prod.post_id is NULL:

SELECT post.post_id, post.title
  FROM c_posts post
authors_relation rel,
c_post_properties prop
  LEFT JOIN c_post_product prod ON
post.post_id = prod.post_id
WHERE
rel.post_id != '$nPostId' // This is the post_id of the post which
is currently being viewed ( works )
AND
rel.author_id = '$nAuthorId' // The author_id of the Author
AND
post.post_id = rel.post_id // To connect the two tables
AND
post.post_id = prop.post_id // The properties table
AND
prod.post_id IS NULL
AND
prop.state != 3

http://www.mysql.com/doc/en/JOIN.html >

> ** I've also tried the NOT IN "function", but I get errors when
> trying that. Seems like it's not supported by my version ??

Well... the IN and NOT IN operators are available, but the operand is a
list, you can't use sub-queries in 4.0:

http://www.mysql.com/doc/en/Comparison_Operators.html#IDX1177 >
http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html >

--
Roger


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

Re: Help needed with MySQL C API-based client (segfault)

[Ganbold]

Here is the corrected version:)

Take a look at Paul DuBois book. Don't use gets. Instead use fgets. That's 
safe. I didn't change your gets call:)

hth,

Ganbold

#include 
#include 
#include 
#if defined(_WIN32) || defined(_WIN64)
#include 
#endif
#ifdef WIN32

#pragma  comment(lib,"ws2_32")
#include "d:\mysql\include\mysql.h"
#else

#include "/usr/local/include/mysql/mysql.h"

#endif

int main(void)
{
char query[255];
int i, j, count;
MYSQL *mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD *field;
/* initialize MYSQL structure */

mysql = mysql_init(NULL);

if(mysql==NULL){
fprintf(stderr, "mysql_init failed! Error %u 
(%s)\n",mysql_errno(mysql),mysql_error(mysql));
exit(1);
}
if(mysql_real_connect(mysql,"localhost","root","test","test",3306,NULL,0)==NULL){

fprintf(stderr, "mysql_real_connect failed! Error %u 
(%s)\n",mysql_errno(mysql),mysql_error(mysql));
exit(1);
}

for( ;; )
{
printf("query? ");
gets(query);
if (strcmp(query,"exit")== 0)
{
break;
}
/* execute query */
if (mysql_query(mysql, query) != 0)
{
fprintf(stderr, "Error in query: %s\n", mysql_error(mysql));
}
else
{
if (result = mysql_store_result(mysql))
{
/* SELECT query */
/* retrieve result set */
int numRecords = (int)mysql_num_rows(result);
int numFields = mysql_num_fields(result);
for (i = 0; i < numRecords; i++)
{
row = mysql_fetch_row(result);
for (j = 0; j < numFields; j++)
{
fprintf(stdout, "%s", row[j]);
j != (numFields-1) ? printf(", ") : printf("\n");
}
}
fprintf(stdout, "** Query successful, %d 
rows retrieved **\n",numRecords);
}
else
{
if (mysql_field_count(mysql) == 0)
{
/* non-SELECT query */
fprintf(stdout, "** Query successful, %d rows affected 
**\n", mysql_affected_rows(mysql));
}
else
{
fprintf(stderr, "Error in reading result 
set: %s\n",mysql_error(mysql));
}
}
}
/* clean up */
mysql_free_result(result);
}
mysql_close(mysql);

return 0;
}


At 09:18 AM 9/19/2003 +0500, you wrote:
Hello,

I need to write a simple C client for a project. I am using the MySQL C
API. Attached is the code. It occassionally segfaults with no visible
pattern. Could someone help me figure out why? Or any other comments on the
code to help me make it better?
/* client.c */

#include 
#include 
int main()
{
/* declare
structures and variables */
char query[255];
int i, j, count;
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD
*field;
/* initialize MYSQL structure */

mysql_init(&mysql);

/* connect to database */
if
(!(mysql_real_connect(&mysql, NULL, "root", "", "db1", 0, NULL, 0)))
 {
fprintf(stderr, "Error in connection: %s\n",
mysql_error(&mysql));
}
for( ;; )
{
printf("query? ");
gets(query);
if (strcmp(query,"exit")
== 0)
{
break;
}
/* execute query
*/
/* if error, display error message */
/* else check the type of
query and handle appropriately */
if (mysql_query(&mysql, query) != 0)
{
fprintf(stderr, "Error in query: %s\n", 
mysql_error(&mysql));
}

else
{
if (result = mysql_store_result(&mysql))
{
/* SELECT
query */
/* retrieve result set */
int numRecords =
mysql_num_rows(result);
int numFields = mysql_num_fields(result);
for (i = 0; i < numRecords; i++)
{
row =
mysql_fetch_row(result);
for (j = 0; j < numFields; j++)
{
//field= mysql_fetch_field(result);
fprintf(stdout, "%s", 
row[j]);

j != (numFields-1) ? printf(", ") : printf("\n");
}
}
fprin

Re: Help needed with SQL...

[Tore Bostrup]

It is usually called a self-join, and it is a very useful technique.  You
have to do this when you need to obtain or access two separate subsets from
a single table and somehow use those in a join.  In this case, you want the
groupids a specific member belongs to (set one) as well as all members
belonging to the same group (set two).  And the two "instances" of the table
have to be identified through use of table aliases.

Regards,
Tore.

- Original Message -
From: "Sheryl Canter" <[EMAIL PROTECTED]>
To: "Don Read" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, February 26, 2003 10:56 AM
Subject: Re: Help needed with SQL...


> I've never seen opening a table twice and then doing a join back with
> itself. Am I wording this correctly? Is that how to describe what you're
> doing?
>
> Thanks for posting this to the full list.
>
> - Sheryl
>
>
> - Original Message -
> From: "Don Read" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Wednesday, February 26, 2003 5:11 AM
> Subject: RE: Help needed with SQL...
>
>
>
> On 26-Feb-2003 [EMAIL PROTECTED] wrote:
> > Yes , I had use this query statement in my MySQL server,
> > But I have a query about it.
> > Why are you use "WHERE a.groupname=b.groupname AND members.id=b.memberid
> > AND a.memberid=1"?
> > Can you give me a explain or give me a advise!
> >
>
> Sure.
>
> mysql> SELECT DISTINCT members.* FROM members, groups as a, groups as b
> -> WHERE a.groupname=b.groupname AND members.id=b.memberid
> -> AND a.memberid=1;
>
>
> The 'a.memberid=1' clause looks up the groups that member 1 belongs to in
> the
> groups table (as a), giving 'group1' & 'group2'.
>
> Then it joins back against the groups table (as b) with the
> 'a.groupname=b.groupname' clause to get the folks that belong to these
> group(s).
>
> Finally the 'members.id=b.memberid' bit selects the records out of the
> members
> table, with the DISTINCT function suppressing any duplicates.
>
> Clear as mud?
>
> (I could've be a little more obvious if I'd put the 'a.memberid=1' clause
> first. Sorry ...)
>
> Regards,
> --
> Don Read   [EMAIL PROTECTED]
> -- It's always darkest before the dawn. So if you are going to
>steal the neighbor's newspaper, that's the time to do it.
> (53kr33t w0rdz: sql table query)
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed with SQL...

[Sheryl Canter]

I've never seen opening a table twice and then doing a join back with
itself. Am I wording this correctly? Is that how to describe what you're
doing?

Thanks for posting this to the full list.

- Sheryl


- Original Message -
From: "Don Read" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, February 26, 2003 5:11 AM
Subject: RE: Help needed with SQL...



On 26-Feb-2003 [EMAIL PROTECTED] wrote:
> Yes , I had use this query statement in my MySQL server,
> But I have a query about it.
> Why are you use "WHERE a.groupname=b.groupname AND members.id=b.memberid
> AND a.memberid=1"?
> Can you give me a explain or give me a advise!
>

Sure.

mysql> SELECT DISTINCT members.* FROM members, groups as a, groups as b
-> WHERE a.groupname=b.groupname AND members.id=b.memberid
-> AND a.memberid=1;


The 'a.memberid=1' clause looks up the groups that member 1 belongs to in
the
groups table (as a), giving 'group1' & 'group2'.

Then it joins back against the groups table (as b) with the
'a.groupname=b.groupname' clause to get the folks that belong to these
group(s).

Finally the 'members.id=b.memberid' bit selects the records out of the
members
table, with the DISTINCT function suppressing any duplicates.

Clear as mud?

(I could've be a little more obvious if I'd put the 'a.memberid=1' clause
first. Sorry ...)

Regards,
--
Don Read   [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
   steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Help needed with SQL...

[Don Read]

On 26-Feb-2003 [EMAIL PROTECTED] wrote:
> Yes , I had use this query statement in my MySQL server,
> But I have a query about it.
> Why are you use "WHERE a.groupname=b.groupname AND members.id=b.memberid 
> AND a.memberid=1"?
> Can you give me a explain or give me a advise!
> 

Sure.

mysql> SELECT DISTINCT members.* FROM members, groups as a, groups as b
-> WHERE a.groupname=b.groupname AND members.id=b.memberid 
-> AND a.memberid=1;


The 'a.memberid=1' clause looks up the groups that member 1 belongs to in the 
groups table (as a), giving 'group1' & 'group2'.

Then it joins back against the groups table (as b) with the
'a.groupname=b.groupname' clause to get the folks that belong to these
group(s).

Finally the 'members.id=b.memberid' bit selects the records out of the members
table, with the DISTINCT function suppressing any duplicates.

Clear as mud?

(I could've be a little more obvious if I'd put the 'a.memberid=1' clause
first. Sorry ...)

Regards,
-- 
Don Read   [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Help needed with SQL...

[Jun.Han]

Yes , I had use this query statement in my MySQL server,
But I have a query about it.
Why are you use "WHERE a.groupname=b.groupname AND members.id=b.memberid  AND 
a.memberid=1"?
Can you give me a explain or give me a advise!

Thanks in advantage for  help!

-Original Message-
From: Don Read [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 26, 2003 4:05 PM
To: M Wells
Cc: [EMAIL PROTECTED]
Subject: RE: Help needed with SQL...



On 26-Feb-2003 M Wells wrote:
> Hi All,
> 
> I'm having problems creating a query to return a recordset from the
> following situation:
> 
> I have two tables, one which contains member details, and the other
> which contains details re: the groups to which the member belongs.
> 
> When a member logs into a particular part of my site, I want to show
> them the details of the other people who belong to the same groups they
> do.

 

> 
> So, if I passed the query the memberid value of '1', I'd like to return
> a a unique records recordset of:
> 
> 1, john doe, [EMAIL PROTECTED]
> 2, jane doe, [EMAIL PROTECTED]
> 4, inigo montoya, [EMAIL PROTECTED]
> 
> Can anyone help me work out how to achieve this?
> 

mysql> select * from members;
++---+--+
| id | name  | email|
++---+--+
|  1 | john doe  |  [EMAIL PROTECTED] |
|  2 | jane doe  |  [EMAIL PROTECTED]   |
|  3 | joe bloggs|  [EMAIL PROTECTED] |
|  4 | inigo montoya | [EMAIL PROTECTED]  |
++---+--+

mysql> select * from groups; 
+---+--+
| groupname | memberid |
+---+--+
| group1|1 |
| group1|2 |
| group2|1 |
| group2|4 |
| group3|3 |
+---+--+
5 rows in set (0.00 sec)

mysql> SELECT DISTINCT members.* FROM members, groups as a, groups as b
-> WHERE a.groupname=b.groupname AND members.id=b.memberid 
-> AND a.memberid=1;
++---+-+
| id | name  | email   |
++---+-+
|  1 | john doe  |  [EMAIL PROTECTED]|
|  2 | jane doe  |  [EMAIL PROTECTED]  |
|  4 | inigo montoya | [EMAIL PROTECTED] |
++---+-+
3 rows in set (0.01 sec)


Regards,
-- 
Don Read   [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Help needed with SQL...

[Don Read]

On 26-Feb-2003 M Wells wrote:
> Hi All,
> 
> I'm having problems creating a query to return a recordset from the
> following situation:
> 
> I have two tables, one which contains member details, and the other
> which contains details re: the groups to which the member belongs.
> 
> When a member logs into a particular part of my site, I want to show
> them the details of the other people who belong to the same groups they
> do.

 

> 
> So, if I passed the query the memberid value of '1', I'd like to return
> a a unique records recordset of:
> 
> 1, john doe, [EMAIL PROTECTED]
> 2, jane doe, [EMAIL PROTECTED]
> 4, inigo montoya, [EMAIL PROTECTED]
> 
> Can anyone help me work out how to achieve this?
> 

mysql> select * from members;
++---+--+
| id | name  | email|
++---+--+
|  1 | john doe  |  [EMAIL PROTECTED] |
|  2 | jane doe  |  [EMAIL PROTECTED]   |
|  3 | joe bloggs|  [EMAIL PROTECTED] |
|  4 | inigo montoya | [EMAIL PROTECTED]  |
++---+--+

mysql> select * from groups; 
+---+--+
| groupname | memberid |
+---+--+
| group1|1 |
| group1|2 |
| group2|1 |
| group2|4 |
| group3|3 |
+---+--+
5 rows in set (0.00 sec)

mysql> SELECT DISTINCT members.* FROM members, groups as a, groups as b
-> WHERE a.groupname=b.groupname AND members.id=b.memberid 
-> AND a.memberid=1;
++---+-+
| id | name  | email   |
++---+-+
|  1 | john doe  |  [EMAIL PROTECTED]|
|  2 | jane doe  |  [EMAIL PROTECTED]  |
|  4 | inigo montoya | [EMAIL PROTECTED] |
++---+-+
3 rows in set (0.01 sec)


Regards,
-- 
Don Read   [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed in mysql

[Egor Egorov]

Hello Inamullah,
Thursday, October 24, 2002, 12:50:27 PM, you wrote:

First of all don't send me your questions, send them to the mailing
list. I don't privide private support.

IK>Well, I have just installed mysql on both wndows n
IK> Linux on the same machine and i wanted to ask how i
IK> can access a database in mysql on windows to be able
IK> to import it to mysql in Linux and also how i can
IK> import a database from SQL server or Access into
IK> mysql. 

Make a dump file using mysqldump program, copy dump file to the Linux
box, and then restore tables.

or you can do

mysqldump -hyour_windows_box -uuser_name -ppassword database_name |
mysql -hyour_linux_box -uuser_name -ppassword database_name

For more info about mysqldump look at:
http://www.mysql.com/doc/en/mysqldump.html



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




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: help needed

[Daniel Kiss]

Hi Sheela,

At 01:49 2002.10.11. -0700, you wrote:
>hi,
>I am using mysql
>while creating a table I want to give column
>descriptioneither by using  a query or GUI
>ex
>create table t1(
>a bigint not null ' here i wamt to tell what for the a
>is
>)
>
>pl help me
>
>thanks
>sheela

You can add comments to your sql statements like this: /* comment here */

For example:

create table t1
  (a bigint not null, /*comment1*/
  b int, /*comment1*/
  c char /*comment1*/
  )

Good luck,
 Daniel


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help Needed

[Kelly Firkins]

Pushpinder,

Looks like a file permission problem. Which user do
you have that runs the MySQL daemon (server)?  Does
this user have the ability to write to the directory
you've specified as the data directory for MySQL?

Kelly

--- Pushpinder Garcha <[EMAIL PROTECTED]>
wrote:
> Hi
> I have set up the MySQL database to run locally on
> my Mac. I have set 
> the privilege for the root user. However, when I log
> in as the root user 
> I am still unable  to create a new database
> 
> mysql> CREATE DATABASE master;
> ERROR 1006: Can't create database 'master'. (errno:
> 13)
> 
> 
> Please tell why this keeps on happening. I am
> running the following 
> version of MySQl for MAC
> 
> mysql> select version();
> ++
> | version()  |
> ++
> | 3.23.51-entropy.ch |
> ++
> 1 row in set (0.00 sec)
> 
> This is the privilages that I have granted uses on
> the system
> mysql> select * from user;
>
+---+--+--+-+-+-+
>
-+-+---+-+---+--+
>
---++-+++
> | Host  | User | Password |
> Select_priv | Insert_priv | 
> Update_priv | Delete_priv | Create_priv | Drop_priv
> | Reload_priv | 
> Shutdown_priv | Process_priv | File_priv |
> Grant_priv | 
> References_priv | Index_priv | Alter_priv |
>
+---+--+--+-+-+-+
>
-+-+---+-+---+--+
>
---++-+++
> | localhost | root | 04bd6ac2298e4ebd | Y   
>| Y   | 
> Y   | Y   | Y   | Y
> | Y   | 
> Y | Y| Y | Y
>  | 
> Y   | Y  | Y  |
> | localhost |  |  | N   
>| N   | 
> N   | N   | N   | N
> | N   | 
> N | N| N | N
>  | 
> N   | N  | N  |
> | % | pgarcha  |  | N   
>| N   | 
> N   | N   | N   | N
> | N   | 
> N | N| N | N
>  | 
> N   | N  | N  |
> | localhost | psgarcha | 6a309f0f0ad4b60d | Y   
>| Y   | 
> Y   | Y   | Y   | Y
> | Y   | 
> Y | Y| Y | Y
>  | 
> Y   | Y  | Y  |
> | % | psgarcha | 6a309f0f0ad4b60d | Y   
>| Y   | 
> Y   | Y   | Y   | Y
> | Y   | 
> Y | Y| Y | Y
>  | 
> Y   | Y  | Y  |
> | localhost | admin|  | N   
>| N   | 
> N   | N   | N   | N
> | Y   | 
> N | Y| N | N
>  | 
> N   | N  | N  |
> | localhost | dummy| dummy| N   
>| N   | 
> N   | N   | N   | N
> | N   | 
> N | N| N | N
>  | 
> N   | N  | N  |
>
+---+--+--+-+-+-+
>
-+-+---+-+---+--+
>
---++-+++
> 7 rows in set (0.00 sec)
> 
> 
> 
> Thanks in advance
> Pushpinder Garcha
> 
> 
>
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list
> archive)
> 
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
>
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> 



=
--
Kelly Firkins
Computer Junkie
Information Systems guy
email: [EMAIL PROTECTED]

__
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed with fixed size table database design!

[Brent Baisley]

One mistake that I see happen a lot in database design is that people 
get stuck in visualizing the data in physical terms along with the 
labels we attach to them. You should try to think more generically. 
Freezer, box, building, tube, etc. are all just labels assigned to 
locations. In your case, everything appears to fall together quite 
nicely.
Here is how I would create your main "Inventory" table:
Inventory

InvSeqID
ParentID
TypeID
Quantity
LocVertical
LocHorizontal
MiscAttribute (i.e. Temp)

Types
---
TypeSeqID
Label
Capacity
Description

That should take you most of the way to designing a system that can 
expand easier without changing table structures or adding tables when 
you get a new location "Type", like floor, building, complex, etc. Your 
"Types" table will hold a list of the various "Types" of "locations". 
This would have an entry for each size tube you use, each size box, 
freezer, etc. Very easy to had new box sizes, freezer types, etc.
The Inventory table will have many self relating (ParentID->InvSeqID) 
records that you can drill down (or up) to build a view of your 
inventory. This table structure allows an unlimited amount of "levels" 
without the need for you need change or add tables. Programming this may 
seem daunting at first, but most of your programming will just be loops 
on the ParentID, continuing until there is no more parent ID.

I know may not have gone into enough detail for you, but as you said, 
this has been done 650,000 some odd times. Although usually to create a 
message board that can have unlimited replies to replies. The attributes 
you are tracking are just different than a message boards, the concept 
is the same. And there are a bunch of message board tutorials out there.

On Thursday, September 19, 2002, at 06:30 PM, Csaba Kiss wrote:

>   this is my first mail to the mailing last, since I am new to the 
> mysql
> world. I would like to create a "cell-bank" mysql database. We freeze
> down cells in small tubes. These tubes are stored in boxes and the
> boxes are in big freezers. Sometimes we freeze 10-20 new tubes with
> new samples (i.e. 5 of each).
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed with fixed size table database design!

[Arjen Lentz]

Hi,

On Fri, 2002-09-20 at 08:30, Csaba Kiss wrote:
>   this is my first mail to the mailing last, since I am new to the mysql
> world. I would like to create a "cell-bank" mysql database. We freeze
> down cells in small tubes. These tubes are stored in boxes and the
> boxes are in big freezers. Sometimes we freeze 10-20 new tubes with
> new samples (i.e. 5 of each). When we pick them up we do it usually
> one by one. Since the size of the boxes and the freezers are constant,
> the tables that I should use should also be constant (right?). I am
> not sure how I should design my database. That's why I would like to
> ask for help.
>  I thought of using the following tables:
> Samples (contains the data about the samples)
> 
> Boxes   (I am not sure about this) It should contain how many rows and
> columns are in the box?
> 
> Freezers (Freezers have racks that holds the boxes and these racks are
> arranged in rows and columns again)I guess this table should contain
> the number of racks, rows, and columns, location, temperature)
> 
> I am stuck somewhere here. I am not too sure how I should proceed.
> Should I create a "freezing" table that contains Sample_id, Box_id,
> Freezer_id...?

Rule #1: a database is not a spreadsheet.
Particularly with a RELATIONAL database.
Also, you want to prevent duplicating information (in relational terms:
normalise).

You could create a freezer table, a box table and a sample table.

Each box (=row in box table) has a reference to the freezer_id, and each
sample has a reference to its box_id. So you build relations between the
tables.

If you need to store the location of a box within a freezer, you can add
its row/col coordinates as extra columns in the box table. That way each
box has its own coordinates with it as well as its freezer_id. Easy!

Temperature... per freezer or per indivual box? Put in the appropriate
table (i.e. freezer if per freezer. That way you only have to
store/update it in one place).


> The database should automatically put the tubes in the boxes until
> they are full and then move to another box by itself.

That is something for your application to do.
You can find out how many samples each indivual box has, so where to add
new samples, and you can also decide when to start a new box.


> If is thaw one samples it should remove those samples from the box
> and mark it empty.

In the above schema, you would simply delete the sample from the sample
table. In the end, a box would have no samples referring to it, which
implicitly makes it empty.


So, start thinking in relational terms. Your project actually makes this
quite easy because it itself already has clear relations (samples in a
box, boxes in a freezer, etc).
Good luck!

You may also wish to consider MySQL training, to quickly get to grips
with these concepts. See http://www.mysql.com/training/ for details on
currently scheduled training courses.


Regards,
Arjen.


-- 
MySQL Training, Support, Licenses @ https://order.mysql.com/?marl
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Technical Writer, Trainer
/_/  /_/\_, /___/\___\_\___/   Brisbane, QLD Australia
   <___/   www.mysql.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed with JOIN on 3 tables

[Andy Sy]

Strange, there shouldn't be any difference between MySQL
query behaviour under Win2K or Linux or BSD or Win98. It
could be more to do with the version of MySQL you are
using.


- Original Message -
From: "bob" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 14, 2002 3:18 AM
Subject: RE: Help needed with JOIN on 3 tables


> This is what I found I will stop using Microsoft for development of
> mysql. I was running the queries locally on my Win2k machine using the
win32
> version of mysql. After screwing with it for several days I finally gave
up
> and posted to the list.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Help needed with JOIN on 3 tables

[Peter Lovatt]

Hi

SELECT b.* //all fields from builder
FROM
builder b   //define tables and aliases
, builder_category_link l
, category c
WHERE
(b.builderID = l.builderID) //linking builder to link table
AND (l.categoryID = c.categoryID)   //linking category to link table
which in
//turn builds the link to the
builder table

AND (l.category_description = 'plumber'); //where category is plumber

should return all builders who do plumbing

I think the id field in the link table is redundant, unless you have another
use for it.

Hope I understood what you were aiming to do :)

Peter



---
Excellence in internet and open source software
---
Sunmaia
www.sunmaia.net
[EMAIL PROTECTED]
tel. 0121-242-1473
---

> -Original Message-
> From: bob [mailto:[EMAIL PROTECTED]]
> Sent: 13 June 2002 17:09
> To: [EMAIL PROTECTED]
> Subject: Help needed with JOIN on 3 tables
>
>
> Hi,
>
> I'm very new to mysql and I'm having a problem getting my joins to work. I
> have 3 tables:
>
> builder
> category
> builder_category_link
>
> The builder table has a an id field and then several other fields (name,
> address etc)
> The category table has an id field and then one other field (type of
> business... electrician, plumber etc)
> The builder_category_link table has 3 fields, an id and then a
> builderid and
> a categoryid (links the other two tables together)
>
> So, I can have a builder that can be a plumber, an electrician etc, etc.
>
> I've tried every way I can think to get the join to work and I've read and
> re-read the mysql docs and
> I just can't figure out how to make it go. I want to be able to search on
> both builder name and category
> (and get the builders that are in that category).
>
> Thanks for your help.
>
> Rob.
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Help needed with JOIN on 3 tables

[bob]

Thanks for all the input:

My code was correct when I compared it to the responses I received. I'm
pretty new to mysql, so I was totally blaming my coding for the errors I was
getting:

This is what I found I will stop using Microsoft for development of
mysql. I was running the queries locally on my Win2k machine using the win32
version of mysql. After screwing with it for several days I finally gave up
and posted to the list.

My code works perfectly on my live Linux and FreeBSD servers. Now after
several wasted days, I have another reason to hate Bill Gates! :-)

Thanks again.


-Original Message-
From: Ralf Narozny [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 13, 2002 12:28 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Help needed with JOIN on 3 tables


Hiho hiho!

bob wrote:

>Hi,
>
>I'm very new to mysql and I'm having a problem getting my joins to work. I
>have 3 tables:
>
>builder
>category
>builder_category_link
>
>The builder table has a an id field and then several other fields (name,
>address etc)
>The category table has an id field and then one other field (type of
>business... electrician, plumber etc)
>The builder_category_link table has 3 fields, an id and then a builderid
and
>a categoryid (links the other two tables together)
>
>So, I can have a builder that can be a plumber, an electrician etc, etc.
>
>I've tried every way I can think to get the join to work and I've read and
>re-read the mysql docs and
>I just can't figure out how to make it go. I want to be able to search on
>both builder name and category
>(and get the builders that are in that category).
>
>

I'm assuming the ID in builder table is named builderID in
builder_category. Furthermore the ID in category is categoryID in
builder_category_table.

Then you should join like this:

SELECT
b.name,
c.business
FROM
builder b
LEFT JOIN builder_category bc ON ( b.id = bc.builderid)
LEFT JOIN category c ON ( c.id = bc.builderid )
;

That should give out all names from builder which have any business given.

Greetings
 Ralf


--
Ralf Narozny
Splendid Internet GmbH
Skandinaviendamm 212, 24109 Kiel, Germany
fon: +49 431 660 97 0, fax: +49 431 660 97 20
mailto:[EMAIL PROTECTED], http://www.splendid.de





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed with JOIN on 3 tables

[Ralf Narozny]

Hiho hiho!

bob wrote:

>Hi,
>
>I'm very new to mysql and I'm having a problem getting my joins to work. I
>have 3 tables:
>
>builder
>category
>builder_category_link
>
>The builder table has a an id field and then several other fields (name,
>address etc)
>The category table has an id field and then one other field (type of
>business... electrician, plumber etc)
>The builder_category_link table has 3 fields, an id and then a builderid and
>a categoryid (links the other two tables together)
>
>So, I can have a builder that can be a plumber, an electrician etc, etc.
>
>I've tried every way I can think to get the join to work and I've read and
>re-read the mysql docs and
>I just can't figure out how to make it go. I want to be able to search on
>both builder name and category
>(and get the builders that are in that category).
>  
>

I'm assuming the ID in builder table is named builderID in 
builder_category. Furthermore the ID in category is categoryID in 
builder_category_table.

Then you should join like this:

SELECT
b.name,
c.business
FROM
builder b
LEFT JOIN builder_category bc ON ( b.id = bc.builderid)
LEFT JOIN category c ON ( c.id = bc.builderid )
;

That should give out all names from builder which have any business given.

Greetings
 Ralf
   

-- 
Ralf Narozny
Splendid Internet GmbH
Skandinaviendamm 212, 24109 Kiel, Germany
fon: +49 431 660 97 0, fax: +49 431 660 97 20
mailto:[EMAIL PROTECTED], http://www.splendid.de





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed on query

[Chris Johnson]

You should be able to just use something like this:

Select uid, username From tablename Where '$externalstringvar' like query;

That's PHP syntax.  You'll have to use whatever makes sense for your
scripting or programming language in place of the $externalstringvar
variable.  The single-quotes are required.

Note this will always do a full table scan, so if your table is big, it will
be slow.

..chris

- Original Message -
From: "thor" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, April 23, 2002 4:22 AM
Subject: Help needed on query


I have a table like this one:

+-+---+---+
| uid | username  | query |
+-+---+---+
|   1 | someuser  | %qu1% |
|   2 | anotheru  | bla%  |
+-+---+---+

I get a string from external source, let's say the string is 'blahblah'.

Now I need a query which will take the values from column 'query', treat
them
as patterns for matching and return row(s) which match the given string
'blahblah'. In this example, the matched data is obviously in second row.
(bla% matches blahblah)

Do I make any sense? Is this possible?
Any help will be greatly appreciated.





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed on query

[Aleksandar Bradaric]

Hi,

> Now  I need a query which will take the values from column
> 'query',  treat  them  as patterns for matching and return
> row(s)  which  match  the given string 'blahblah'. In this
> example,  the  matched  data  is  obviously in second row.
> (bla% matches blahblah)

> Do I make any sense? Is this possible?
> Any help will be greatly appreciated.

To  be  honest I didn't belive it would work, but... here it
is :)

mysql> select * from blah;
+--+--+---+
| uid  | username | query |
+--+--+---+
|1 | someuser | %qu1% |
|2 | anotheru | bla%  |
+--+--+---+
2 rows in set (0.00 sec)

mysql> select * from blah where 'blahblah' like query;
+--+--+---+
| uid  | username | query |
+--+--+---+
|2 | anotheru | bla%  |
+--+--+---+
1 row in set (0.00 sec)


Regards,
Sasa

ťmysql, select, databaseŤ



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed with C program + Mysql

[Benjamin Pflugmann]

Hi.

The char* to the field value will be NULL (in the C meaning), if the
value is NULL (in the SQL meaning). The reason for your core dump is
probably a missing check before you treat the NULL pointer as string.

Bye,

Benjamin.


On Thu, Feb 28, 2002 at 03:23:01PM -0300, [EMAIL PROTECTED] wrote:
>   I have defined a Mysql table with some integer variable, I can put
> NULL values in these elementes, but when I user mysql_fetch_row, if there's
> some integer field with NULL value, the program fails with core dump.
>   Where's the problem?
>   Thanks in advance.

-- 
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Help needed !!!

[Chetan Lavti]

hi,
Yes, I was really thinking about the InnoDB Tables,
  
Thank you very much for such a systematic and accurate reply,

Thanks and regards,
Chetan

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 25, 2002 1:10 PM
To: Chetan Lavti
Cc: [EMAIL PROTECTED]
Subject: Re: Help needed !!!


On Fri, Jan 25, 2002 at 01:05:12PM +0530, Chetan Lavti wrote:
> 
> I am going to use the MySQL version 3.23.47 for as our database.
> The issue is which table type to use.
>
> I want that the database should be (memory-resident). I have tried
> with the MyISAM tables which doesn't solves my purpose as in this
> case the data are stored in the files.

Right.

> I have also tried with the HEAP tables that uses a hashed index and
> are stored in memory. This makes them very fast, but if MySQL
> crashes I am loosing all data stored in that. As, I have created one
> table as a heap type but when I make my server down and start it
> again all rows created nowhere exists.

Correct.

It sounds like you might want to look at InnoDB tables.  They are disk
based, but the InnoDB table handler can use a significant amount of
RAM (if you allow it to) to cache index *and* record data.  MyISAM
only caches index data in memory.  The InnoDB buffer pool is where
this cached data is stored.

> So, if anybody can suggest me any process by which I can populated
> HEAP table(es) from a duplicate table(es) (which is on secondary
> storage) at the time of startup. Also, any runtime modifications in
> the tables will need to be updated in both the tables (one in memory
> and another one on disk).  If any other method by which I can
> achieve the same.

There was talk last year of implementing a hybrid HEAP/MyISAM table
type (originally motived by the DBA at Slashdot).  Upon startup, the
MyISAM table would be loaded into a RAM-based HEAP-table.  Any changes
to the HEAP table would get written to the underlying MyISAM table as
well.  Read-only queries, of course, would be run against the
RAM-based HEAP table.

I suspect that will never be implemented, since InnoDB and it's buffer
pool go a long way toward solving the same problem.

Hope that helps,

Jeremy
-- 
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936

MySQL 3.23.41-max: up 22 days, processed 513,100,530 queries (265/sec.
avg)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed !!!

[Jeremy Zawodny]

On Fri, Jan 25, 2002 at 01:05:12PM +0530, Chetan Lavti wrote:
> 
> I am going to use the MySQL version 3.23.47 for as our database.
> The issue is which table type to use.
>
> I want that the database should be (memory-resident). I have tried
> with the MyISAM tables which doesn't solves my purpose as in this
> case the data are stored in the files.

Right.

> I have also tried with the HEAP tables that uses a hashed index and
> are stored in memory. This makes them very fast, but if MySQL
> crashes I am loosing all data stored in that. As, I have created one
> table as a heap type but when I make my server down and start it
> again all rows created nowhere exists.

Correct.

It sounds like you might want to look at InnoDB tables.  They are disk
based, but the InnoDB table handler can use a significant amount of
RAM (if you allow it to) to cache index *and* record data.  MyISAM
only caches index data in memory.  The InnoDB buffer pool is where
this cached data is stored.

> So, if anybody can suggest me any process by which I can populated
> HEAP table(es) from a duplicate table(es) (which is on secondary
> storage) at the time of startup. Also, any runtime modifications in
> the tables will need to be updated in both the tables (one in memory
> and another one on disk).  If any other method by which I can
> achieve the same.

There was talk last year of implementing a hybrid HEAP/MyISAM table
type (originally motived by the DBA at Slashdot).  Upon startup, the
MyISAM table would be loaded into a RAM-based HEAP-table.  Any changes
to the HEAP table would get written to the underlying MyISAM table as
well.  Read-only queries, of course, would be run against the
RAM-based HEAP table.

I suspect that will never be implemented, since InnoDB and it's buffer
pool go a long way toward solving the same problem.

Hope that helps,

Jeremy
-- 
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936

MySQL 3.23.41-max: up 22 days, processed 513,100,530 queries (265/sec. avg)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: help needed on datetime functions

[Jason Wong]

On Wednesday 05 December 2001 16:40, you wrote:
> hi everybody
> i want to take diff of datetime format columns.
> Is it possible using direct '-' arithmatic..
> It gives result as:
>
> 2001-11-03 15:43:47 - 2001-11-03 15:42:21 = 126
> 2001-11-03 16:11:50 - 2001-11-03 15:48:00 = 6350
>
> First row is clear but i'm not getting second row's
> diff.. **((63)50)** how it comes.
>
> Is there any diff way to find diff of datetime
> columns. (any mysql func.)
>   OR
> It is not possible by using any standard mysql
> funcions and one has to write his own program to do
> this.

You can use something like this:

SEC_TO_TIME(UNIX_TIMESTAMP("2001-11-03 16:11:50") - 
UNIX_TIMESTAMP("2001-11-03 15:48:00"))

which would return

 00:23:50

hth
-- 
Jason Wong -> Gremlins Associates -> www.gremlins.com.hk

/*
Universe, n.:
The problem.
*/

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Help needed getting table headings into an outfile

[Andrew Murphy]

Sorry, I should have given you more information about the problem.

We have a Java program, with a J2EE backend which is connected to a MySQL
database.
The database is running on a windows 2000 machine, and we want to output the
contents of the tables in text format(CSV).   I can get the formatting ok,
but it just needs the table headings.

Ideally we want to use one or more queries to do this, rather than using
mysqldump.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 10 September 2001 11:38 am
To: Andrew Murphy
Subject: Re: Help needed getting table headings into an outfile


I do this in perl to get the headers.  Perhaps you can adjust to fit into
your output script.

 my $statement = "SELECT * FROM $table";
 my $sth = $dbh->prepare( $statement ) ;
 $sth->execute();
 my $fields = $sth->{NUM_OF_FIELDS};

 for ( my $i = 0 ; $i < $fields ; $i++ ) {

$name = $sth->{NAME}->[$i] ;
if ($i == 0) {
  $fldnames = $name ;
} else {
  $fldnames = $fldnames . "|" . $name ;
}
 }  # end of for loop
 $sth->finish();

Dirk Van Wie



Andrew Murphy <[EMAIL PROTECTED]> on 09/10/2001 12:33:50 PM
To:   'mysql Mailing List' <[EMAIL PROTECTED]>
cc:
Subject:  Help needed getting table headings into an outfile


Hi,

I keep sending this to the list, but im not sure if anyone else is getting
it.

I hope you can help me with this.
I am using the INTO OUTFILE 'filename' function, to create .CSV output
files
from my queries.
Whenever I do this, the table headings are removed from the output.  Is
there a way to get the table headings displayed in the output file?

Thanks in advance
Andrew

database, sql, query, table
database, sql, query, table
database, sql, query, table



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Help Needed Please

[Don Read]

On 26-Aug-2001 [EMAIL PROTECTED] wrote:
> Hi
> 
> I need to set the max_allowed_packet to 16M. below is my
> 01mysql-server.sh file. I use DBI and I know that I have to start this
> before the DBI program and I did but if I use ./mysqld --help it shows
> that my max_allowed_packet is still 1M. Any help would be really great
> since I've been working on this all day :(
> 
> 
> case "$1" in
> start)
> if [ -x /usr/local/bin/safe_mysqld ]; then
> /usr/local/bin/safe_mysqld --user=mysql
> --set-variable=max_allowed_packet=16M  > /dev/null & && echo -n '
> mysqld'
> 
> 

try it in your /etc/my.cnf:

[mysqld]
 socket=/tmp/mysql.sock
 set-variable = max_allowed_packet=16M

Regards,
-- 
Don Read   [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to 
   steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help Needed Please

[Martin Mokrejs]

Hi,
  use "mysqladmin variables" command to see current settings. I guess
your mysqld does not read the config file you have edited. ;-) It
happened to me me also few days ago. Remember mysqld looks for
/etc/my.cnf and $DATADIR/var/my.cnf if I remember well. Maybe put the
path to config file just on the command line into safe_mysqld script.

Martin

> Hi
>
>I need to set the max_allowed_packet to 16M. below is my
>01mysql-server.sh file. I use DBI and I know that I have to start this
>before the DBI program and I did but if I use ./mysqld --help it shows
>that my max_allowed_packet is still 1M. Any help would be really great
>since I've been working on this all day :(

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: help needed for replication : EUREKA

[BALU Frédéric]

The solution is ...
 FLUSH PRIVILEGES;
on the slaves.
As it's not an UPDATE, this command is not sent to slaves with the binlog.


> -Original Message-
> From: BALU Frédéric [mailto:[EMAIL PROTECTED]]
> Sent: Monday, July 23, 2001 8:47 AM
> To: 'Stefan Hinz'; [EMAIL PROTECTED]
> Subject: RE: help needed for replication
> 
> 
> Already tried ... Not successfully.
> Another idea ?
> 
> > -Original Message-
> > From: Stefan Hinz [mailto:[EMAIL PROTECTED]]
> > Sent: Friday, July 20, 2001 7:51 PM
> > To: BALU Frédéric; [EMAIL PROTECTED]
> > Subject: Re: help needed for replication
> > 
> > 
> > Dear Frederic,
> > 
> > >localhost | jkasas | 1896f443280395b3
> > >   And what I do and get :
> > >mysql> GRANT SELECT ON MEMBERS.users TO jkasas@'localhost';
> > >ERROR 1133: Can't find any matching row in the user table
> > 
> > Try GRANT SELECT ON MEMBERS.users TO jkasas@localhost (w/out 
> > quotes)! :)
> > 
> > Regards,
> > 
> > --
> >   Stefan Hinz
> >   Geschäftsführer / CEO iConnect e-commerce solutions GmbH
> >   #  www.js-webShop.com www.iConnect.de
> >   #  Gustav-Meyer-Allee 25, 13355 Berlin
> >   #  Tel: +49-30-46307-382  Fax: +49-30-46307-388
> > 
> > - Original Message -
> > From: "BALU Frédéric" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Friday, July 20, 2001 5:54 PM
> > Subject: help needed for replication
> > 
> > 
> > > Hi everybody,
> > >
> > >  I use Replication on a windows NT4 Server.
> > >  There are 1 master and 2 slaves, all on the same machine.
> > >  One slave is started with the option skip-name-resolve, not the
> > other.
> > >  For the 2 slaves : master-host=localhost
> > >  When adding a user (MYSQL.User), the replication is OK but,
> > >  when I use GRANT, I get a 1133 error.
> > >  Let's have a look on a slave :
> > >
> > >   MYSQL.User table :
> > >localhost | jkasas | 1896f443280395b3
> > >   And what I do and get :
> > >mysql> GRANT SELECT ON MEMBERS.users TO jkasas@'localhost';
> > >ERROR 1133: Can't find any matching row in the user table
> > >
> > >  Personnally, I don't understand why.
> > >  Does anyone understand ?
> > >
> > >  Thx for the answers.
> > >
> > > --
> > > Frederic BALU
> > >
> > > 
> > 
> -
> > > Before posting, please check:
> > >http://www.mysql.com/manual.php   (the manual)
> > >http://lists.mysql.com/   (the list archive)
> > >
> > > To request this thread, e-mail <[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try: 
> > http://lists.mysql.com/php/unsubscribe.php
> > >
> > >
> > 
> > 
> > 
> -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> > 
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail 
> > <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: 
http://lists.mysql.com/php/unsubscribe.php
> 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: help needed for replication

[BALU Frédéric]

Already tried ... Not successfully.
Another idea ?

> -Original Message-
> From: Stefan Hinz [mailto:[EMAIL PROTECTED]]
> Sent: Friday, July 20, 2001 7:51 PM
> To: BALU Frédéric; [EMAIL PROTECTED]
> Subject: Re: help needed for replication
> 
> 
> Dear Frederic,
> 
> >localhost | jkasas | 1896f443280395b3
> >   And what I do and get :
> >mysql> GRANT SELECT ON MEMBERS.users TO jkasas@'localhost';
> >ERROR 1133: Can't find any matching row in the user table
> 
> Try GRANT SELECT ON MEMBERS.users TO jkasas@localhost (w/out 
> quotes)! :)
> 
> Regards,
> 
> --
>   Stefan Hinz
>   Geschäftsführer / CEO iConnect e-commerce solutions GmbH
>   #  www.js-webShop.com www.iConnect.de
>   #  Gustav-Meyer-Allee 25, 13355 Berlin
>   #  Tel: +49-30-46307-382  Fax: +49-30-46307-388
> 
> - Original Message -
> From: "BALU Frédéric" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, July 20, 2001 5:54 PM
> Subject: help needed for replication
> 
> 
> > Hi everybody,
> >
> >  I use Replication on a windows NT4 Server.
> >  There are 1 master and 2 slaves, all on the same machine.
> >  One slave is started with the option skip-name-resolve, not the
> other.
> >  For the 2 slaves : master-host=localhost
> >  When adding a user (MYSQL.User), the replication is OK but,
> >  when I use GRANT, I get a 1133 error.
> >  Let's have a look on a slave :
> >
> >   MYSQL.User table :
> >localhost | jkasas | 1896f443280395b3
> >   And what I do and get :
> >mysql> GRANT SELECT ON MEMBERS.users TO jkasas@'localhost';
> >ERROR 1133: Can't find any matching row in the user table
> >
> >  Personnally, I don't understand why.
> >  Does anyone understand ?
> >
> >  Thx for the answers.
> >
> > --
> > Frederic BALU
> >
> > 
> -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: 
> http://lists.mysql.com/php/unsubscribe.php
> >
> >
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: help needed for replication

[Stefan Hinz]

Dear Frederic,

>localhost | jkasas | 1896f443280395b3
>   And what I do and get :
>mysql> GRANT SELECT ON MEMBERS.users TO jkasas@'localhost';
>ERROR 1133: Can't find any matching row in the user table

Try GRANT SELECT ON MEMBERS.users TO jkasas@localhost (w/out quotes)! :)

Regards,

--
  Stefan Hinz
  Geschäftsführer / CEO iConnect e-commerce solutions GmbH
  #  www.js-webShop.com www.iConnect.de
  #  Gustav-Meyer-Allee 25, 13355 Berlin
  #  Tel: +49-30-46307-382  Fax: +49-30-46307-388

- Original Message -
From: "BALU Frédéric" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, July 20, 2001 5:54 PM
Subject: help needed for replication


> Hi everybody,
>
>  I use Replication on a windows NT4 Server.
>  There are 1 master and 2 slaves, all on the same machine.
>  One slave is started with the option skip-name-resolve, not the
other.
>  For the 2 slaves : master-host=localhost
>  When adding a user (MYSQL.User), the replication is OK but,
>  when I use GRANT, I get a 1133 error.
>  Let's have a look on a slave :
>
>   MYSQL.User table :
>localhost | jkasas | 1896f443280395b3
>   And what I do and get :
>mysql> GRANT SELECT ON MEMBERS.users TO jkasas@'localhost';
>ERROR 1133: Can't find any matching row in the user table
>
>  Personnally, I don't understand why.
>  Does anyone understand ?
>
>  Thx for the answers.
>
> --
> Frederic BALU
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed

[Tonu Samuel]

Kim White wrote:


> Could anybody tell me how to get rid of the following error on a php3
> request. "Warning: MySQL Connection Failed: Host 'hostname.co.za' is not
> allowed to connect to this MySQL server in /var/local/irm/irm.inc on line
> 83".
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)

Go to the manual on http://www.mysql.com and search for GRANT command 
syntax.

-- 
For technical support contracts, goto https://order.mysql.com/
__  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Tonu Samuel <[EMAIL PROTECTED]>
  / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Security Administrator
/_/  /_/\_, /___/\___\_\___/   Hong Kong, China
<___/   www.mysql.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed

[Gerald Clark]

You need to GRANT PRIVILEGES to the host and user that PHP connects from.

Kim White wrote:

> Hi
> 
> Could anybody tell me how to get rid of the following error on a php3
> request. "Warning: MySQL Connection Failed: Host 'hostname.co.za' is not
> allowed to connect to this MySQL server in /var/local/irm/irm.inc on line
> 83".
> 
> Thanks
> 
> 
> Kim White
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
><[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Gerald L. Clark
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: HELP NEEDED: Problems with SELECT in combination with HAVING

[Hannes Niedner]

Thomas

You might want to consider to post a sample select from the table, some
things become apparent just then.

Hannes

On 6/27/01 3:35 AM, "Thomas Michael Koch" <[EMAIL PROTECTED]> wrote:

> Hey Hans - it didn't work (damn) but thank you for your reply and sorry
> about the accidental mail you got.
> 
> The result that was produced had the correct time but it was not attatched
> to the correct row. It was instead attached to the first row that MySql
> found in the RobotRun table.
> 
> I have found another way around it, which goes like this:
> 
> SELECT max(runId), robotId
>> FROM RobotRun
> WHERE endTime IS NOT NULL
> GROUP BY RobotId
> 
> This builds on the assumption that runId's are assigned in a strictly
> ascending order, so it only works for my domain ...
> 
> It also seems that I have made a small typo in my initial request (sorry).
> The purpose of the SQL is to find the runId of the newest run for each
> robotId. Thus I reformed your SQL to: SELECT runId, max(startTime)
> 
> I can solve my problem by using GROUP BY and HAVING when running against
> SyBase like this:
> 
> SELECT *
>> FROM RobotRun
> WHERE endTime IS NOT NULL
> GROUP BY robotId
> HAVING startTime = max(startTime)
> 
> Alas, this does not work with MySql.
> 
> Is there a general understanding within the MySql community that GROUP BY
> and HAVING doesn't conform to the standard ?
> 
> Regards
> Thomas
> 
> 
> -Original Message-
> From: Hannes Niedner [mailto:[EMAIL PROTECTED]]
> Sent: 26. juni 2001 17:42
> To: [EMAIL PROTECTED]
> Subject: Re: HELP NEEDED: Problems with SELECT in combination with
> HAVING
> 
> 
> Try
> 
> SELECT robotId, max(startTime) as crit
>> FROM RobotRun
> WHERE startTime >= '2001-06-26 00:00:00'
> AND endTime IS NOT NULL
> GROUP BY robotId
> 
> This should return the robotId and the most recent startTime labeled 'crit'
> for all records specified in the where clause. And have a look in the manual
> for the group by statement - since this statement works like a enhanced
> 'select distinct' query it will produce ill results applied to non unique
> combinations in the select and group by statement. I suppose that¹s the case
> for your  'robotId, startTime' pair.
> 
> If I got this wrong forgive me it was first thing in the morning.
> 
> Hannes
> 
> On 6/26/01 4:05 AM, "Thomas Michael Koch" <[EMAIL PROTECTED]> wrote:
> 
>> Hello there - I have this weird problem using MySql (version 3.23.39).
>> 
>> It seems that MySql interpretation of a SELECT statment using HAVING is
>> completely random.
>> 
>> I have a table called RobotRun which stores information about when a robot
>> has been running (start, stop and the id of the robot). This translates to
>> columns: "runId" (primary key), "robotId" (the id of the robot),
> "startTime"
>> and "endTime".
>> 
>> A robot can run several times during its life time, thus several records
>> will appear in RobotRun for the same robotId, however, each row will
> always
>> have a unique runId.
>> 
>> The purpose of the SQL is to produce one RobotRun row for each robot, with
>> the added restriction that it is only the row with the newest "startTime"
>> value that gets selected.
>> 
>> Now if I do the following SQL things start to get strange:
>> 
>> SELECT robotId, startTime, max(startTime) as crit
>>> FROM RobotRun
>> WHERE startTime >= '2001-06-26 00:00:00'
>> AND endTime IS NOT NULL
>> GROUP BY robotId
>> HAVING startTime = crit
>> 
>> This query produces 321 rows.
>> 
>> Setting the startTime to '2001-06-25 00:00:00' produces 707 rows, which is
>> correct and expected since the input set is larger.
>> 
>> NOW: Setting the startTime to '2001-06-24 00:00:00' produces 21 rows
>> What is going on. I cannot see how this is possible. The input set is only
>> getting larger.
>> 
>> The number of rows involved in the table is in the vicinity of 5. The
>> theoretical maximum number of rows produced from the SQL is in the
>> neighbourhood of 5000 rows. That shouldn't be a problem.
>> 
>> 
>> I have also tried the following statement with the same result (problem):
>> 
>> SELECT robotId, startTime
>>> FROM RobotRun
>> WHERE startTime >= '2001-06-26 00:00:00'
>> AND endTime IS NOT NULL
>> GROUP BY robotId
>> HAVING startTime = max(startTime)
>> 
>> 
>>

RE: HELP NEEDED: Problems with SELECT in combination with HAVING 2

[Thomas Michael Koch]

Hey Hannes - it didn't work (damn) but thank you for your reply and sorry
about the accidental mail you got.

The result that was produced had the correct time but it was not attatched
to the correct row. It was instead attached to the first row that MySql
found in the RobotRun table.

I have found another way around it, which goes like this:

SELECT max(runId), robotId
FROM RobotRun
WHERE endTime IS NOT NULL
GROUP BY RobotId

This builds on the assumption that runId's are assigned in a strictly
ascending order, so it only works for my domain ...

It also seems that I have made a small typo in my initial request (sorry).
The purpose of the SQL is to find the runId of the newest run for each
robotId. Thus I reformed your SQL to: SELECT runId, max(startTime)

I can solve my problem by using GROUP BY and HAVING when running against
SyBase like this:

SELECT *
FROM RobotRun
WHERE endTime IS NOT NULL
GROUP BY robotId
HAVING startTime = max(startTime)

Alas, this does not work with MySql.

Is there a general understanding within the MySql community that GROUP BY
and HAVING doesn't conform to the standard ?

Regards
Thomas


-Original Message-
From: Hannes Niedner [mailto:[EMAIL PROTECTED]]
Sent: 26. juni 2001 17:42
To: [EMAIL PROTECTED]
Subject: Re: HELP NEEDED: Problems with SELECT in combination with
HAVING


Try

SELECT robotId, max(startTime) as crit
FROM RobotRun
WHERE startTime >= '2001-06-26 00:00:00'
AND endTime IS NOT NULL
GROUP BY robotId

This should return the robotId and the most recent startTime labeled 'crit'
for all records specified in the where clause. And have a look in the manual
for the group by statement - since this statement works like a enhanced
'select distinct' query it will produce ill results applied to non unique
combinations in the select and group by statement. I suppose that¹s the case
for your  'robotId, startTime' pair.

If I got this wrong forgive me it was first thing in the morning.

Hannes

On 6/26/01 4:05 AM, "Thomas Michael Koch" <[EMAIL PROTECTED]> wrote:

> Hello there - I have this weird problem using MySql (version 3.23.39).
>
> It seems that MySql interpretation of a SELECT statment using HAVING is
> completely random.
>
> I have a table called RobotRun which stores information about when a robot
> has been running (start, stop and the id of the robot). This translates to
> columns: "runId" (primary key), "robotId" (the id of the robot),
"startTime"
> and "endTime".
>
> A robot can run several times during its life time, thus several records
> will appear in RobotRun for the same robotId, however, each row will
always
> have a unique runId.
>
> The purpose of the SQL is to produce one RobotRun row for each robot, with
> the added restriction that it is only the row with the newest "startTime"
> value that gets selected.
>
> Now if I do the following SQL things start to get strange:
>
> SELECT robotId, startTime, max(startTime) as crit
>> FROM RobotRun
> WHERE startTime >= '2001-06-26 00:00:00'
> AND endTime IS NOT NULL
> GROUP BY robotId
> HAVING startTime = crit
>
> This query produces 321 rows.
>
> Setting the startTime to '2001-06-25 00:00:00' produces 707 rows, which is
> correct and expected since the input set is larger.
>
> NOW: Setting the startTime to '2001-06-24 00:00:00' produces 21 rows
> What is going on. I cannot see how this is possible. The input set is only
> getting larger.
>
> The number of rows involved in the table is in the vicinity of 5. The
> theoretical maximum number of rows produced from the SQL is in the
> neighbourhood of 5000 rows. That shouldn't be a problem.
>
>
> I have also tried the following statement with the same result (problem):
>
> SELECT robotId, startTime
>> FROM RobotRun
> WHERE startTime >= '2001-06-26 00:00:00'
> AND endTime IS NOT NULL
> GROUP BY robotId
> HAVING startTime = max(startTime)
>
>
> Any help would be appreciated.
>
> Regards
> Thomas Koch
>
>
> -
> Before posting, please check:
>  http://www.mysql.com/manual.php   (the manual)
>  http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe,

RE: HELP NEEDED: Problems with SELECT in combination with HAVING

[Thomas Michael Koch]

Hey Hans - it didn't work (damn) but thank you for your reply and sorry
about the accidental mail you got.

The result that was produced had the correct time but it was not attatched
to the correct row. It was instead attached to the first row that MySql
found in the RobotRun table.

I have found another way around it, which goes like this:

SELECT max(runId), robotId
FROM RobotRun
WHERE endTime IS NOT NULL
GROUP BY RobotId

This builds on the assumption that runId's are assigned in a strictly
ascending order, so it only works for my domain ...

It also seems that I have made a small typo in my initial request (sorry).
The purpose of the SQL is to find the runId of the newest run for each
robotId. Thus I reformed your SQL to: SELECT runId, max(startTime)

I can solve my problem by using GROUP BY and HAVING when running against
SyBase like this:

SELECT *
FROM RobotRun
WHERE endTime IS NOT NULL
GROUP BY robotId
HAVING startTime = max(startTime)

Alas, this does not work with MySql.

Is there a general understanding within the MySql community that GROUP BY
and HAVING doesn't conform to the standard ?

Regards
Thomas


-Original Message-
From: Hannes Niedner [mailto:[EMAIL PROTECTED]]
Sent: 26. juni 2001 17:42
To: [EMAIL PROTECTED]
Subject: Re: HELP NEEDED: Problems with SELECT in combination with
HAVING


Try

SELECT robotId, max(startTime) as crit
FROM RobotRun
WHERE startTime >= '2001-06-26 00:00:00'
AND endTime IS NOT NULL
GROUP BY robotId

This should return the robotId and the most recent startTime labeled 'crit'
for all records specified in the where clause. And have a look in the manual
for the group by statement - since this statement works like a enhanced
'select distinct' query it will produce ill results applied to non unique
combinations in the select and group by statement. I suppose that¹s the case
for your  'robotId, startTime' pair.

If I got this wrong forgive me it was first thing in the morning.

Hannes

On 6/26/01 4:05 AM, "Thomas Michael Koch" <[EMAIL PROTECTED]> wrote:

> Hello there - I have this weird problem using MySql (version 3.23.39).
>
> It seems that MySql interpretation of a SELECT statment using HAVING is
> completely random.
>
> I have a table called RobotRun which stores information about when a robot
> has been running (start, stop and the id of the robot). This translates to
> columns: "runId" (primary key), "robotId" (the id of the robot),
"startTime"
> and "endTime".
>
> A robot can run several times during its life time, thus several records
> will appear in RobotRun for the same robotId, however, each row will
always
> have a unique runId.
>
> The purpose of the SQL is to produce one RobotRun row for each robot, with
> the added restriction that it is only the row with the newest "startTime"
> value that gets selected.
>
> Now if I do the following SQL things start to get strange:
>
> SELECT robotId, startTime, max(startTime) as crit
>> FROM RobotRun
> WHERE startTime >= '2001-06-26 00:00:00'
> AND endTime IS NOT NULL
> GROUP BY robotId
> HAVING startTime = crit
>
> This query produces 321 rows.
>
> Setting the startTime to '2001-06-25 00:00:00' produces 707 rows, which is
> correct and expected since the input set is larger.
>
> NOW: Setting the startTime to '2001-06-24 00:00:00' produces 21 rows
> What is going on. I cannot see how this is possible. The input set is only
> getting larger.
>
> The number of rows involved in the table is in the vicinity of 5. The
> theoretical maximum number of rows produced from the SQL is in the
> neighbourhood of 5000 rows. That shouldn't be a problem.
>
>
> I have also tried the following statement with the same result (problem):
>
> SELECT robotId, startTime
>> FROM RobotRun
> WHERE startTime >= '2001-06-26 00:00:00'
> AND endTime IS NOT NULL
> GROUP BY robotId
> HAVING startTime = max(startTime)
>
>
> Any help would be appreciated.
>
> Regards
> Thomas Koch
>
>
> -
> Before posting, please check:
>  http://www.mysql.com/manual.php   (the manual)
>  http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe,

Re: HELP NEEDED: Problems with SELECT in combination with HAVING

[Hannes Niedner]

Try 

SELECT robotId, max(startTime) as crit
FROM RobotRun
WHERE startTime >= '2001-06-26 00:00:00'
AND endTime IS NOT NULL
GROUP BY robotId

This should return the robotId and the most recent startTime labeled 'crit'
for all records specified in the where clause. And have a look in the manual
for the group by statement - since this statement works like a enhanced
'select distinct' query it will produce ill results applied to non unique
combinations in the select and group by statement. I suppose that¹s the case
for your  'robotId, startTime' pair.

If I got this wrong forgive me it was first thing in the morning.

Hannes

On 6/26/01 4:05 AM, "Thomas Michael Koch" <[EMAIL PROTECTED]> wrote:

> Hello there - I have this weird problem using MySql (version 3.23.39).
> 
> It seems that MySql interpretation of a SELECT statment using HAVING is
> completely random.
> 
> I have a table called RobotRun which stores information about when a robot
> has been running (start, stop and the id of the robot). This translates to
> columns: "runId" (primary key), "robotId" (the id of the robot), "startTime"
> and "endTime".
> 
> A robot can run several times during its life time, thus several records
> will appear in RobotRun for the same robotId, however, each row will always
> have a unique runId.
> 
> The purpose of the SQL is to produce one RobotRun row for each robot, with
> the added restriction that it is only the row with the newest "startTime"
> value that gets selected.
> 
> Now if I do the following SQL things start to get strange:
> 
> SELECT robotId, startTime, max(startTime) as crit
>> FROM RobotRun
> WHERE startTime >= '2001-06-26 00:00:00'
> AND endTime IS NOT NULL
> GROUP BY robotId
> HAVING startTime = crit
> 
> This query produces 321 rows.
> 
> Setting the startTime to '2001-06-25 00:00:00' produces 707 rows, which is
> correct and expected since the input set is larger.
> 
> NOW: Setting the startTime to '2001-06-24 00:00:00' produces 21 rows
> What is going on. I cannot see how this is possible. The input set is only
> getting larger.
> 
> The number of rows involved in the table is in the vicinity of 5. The
> theoretical maximum number of rows produced from the SQL is in the
> neighbourhood of 5000 rows. That shouldn't be a problem.
> 
> 
> I have also tried the following statement with the same result (problem):
> 
> SELECT robotId, startTime
>> FROM RobotRun
> WHERE startTime >= '2001-06-26 00:00:00'
> AND endTime IS NOT NULL
> GROUP BY robotId
> HAVING startTime = max(startTime)
> 
> 
> Any help would be appreciated.
> 
> Regards
> Thomas Koch
> 
> 
> -
> Before posting, please check:
>  http://www.mysql.com/manual.php   (the manual)
>  http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed with Query UPDATE INFO

[Eric Fitzgerald]

Ok, a few things I see...first off, slap some keys into those tables on the
join portions.  Secondly, upon examining your query a second time, your
never joining subsnp and locuslink.  I see your trying to use a full join,
but you didn't put the criteria for it in the where clause.  That would
cause some VERY weird join results, and may be your problem.  Try adding in
the join clause in the where clause.

- Original Message -
From: "Bryan Coon" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, April 27, 2001 2:52 PM
Subject: RE: Help needed with Query UPDATE INFO


> Okay, here is the full monty from the database regarding the query in
> question, any suggestions on how to improve any of it are greatly
> appreciated!
>
> Also, if any more info is needed, just let me know.
>
> Thanks!
> Bryan
>
>
> QUERY:
> mysql> select distinct subsnp_pk as sqnmid, c.chrom, chrompos, source as
> panel,  null as first_pcrp, null as second_pcrp, null as prb_seq, null as
> term,  null as validation, null as freq, null as population  from
locuslink
> l, subsnp left outer join chromosome_position c  on subsnp_pk = c.snp_fk
> left outer join locus_annotation a  on subsnp_pk = a.snp_fk where
a.locusid
> = substring(l.locusid, 4) and description like '%GABA%' order by
subsnp_pk;
>
> EXPLAIN:
>
++--+---+--+-+--+---+---
> --+
> | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra
> |
>
++--+---+--+-+--+---+---
> --+
> | subsnp | ALL  | NULL  | NULL |NULL | NULL |  1201 | Using
> temporary; Using filesort |
> | l  | ALL  | NULL  | NULL |NULL | NULL | 21294 | where
used
> |
> | c  | ALL  | NULL  | NULL |NULL | NULL |   579 |
> |
> | a  | ALL  | NULL  | NULL |NULL | NULL |   704 | where
> used; Distinct|
>
++--+---+--+-+--+---+---
> --+
> 4 rows in set (0.00 sec)
>
> DESCRIBE:
> mysql> describe locuslink;
> ++--+--+-+-+---+
> | Field  | Type | Null | Key | Default | Extra |
> ++--+--+-+-+---+
> | LOCUSID| varchar(12)  |  | | |   |
> | SYMBOL | varchar(15)  | YES  | | NULL|   |
> | INTERIM_SYMBOL | varchar(15)  | YES  | | NULL|   |
> | MIM_NUMBER | varchar(15)  | YES  | | NULL|   |
> | CHROM  | varchar(6)   | YES  | | NULL|   |
> | BAND   | varchar(20)  | YES  | | NULL|   |
> | DESCRIPTION| varchar(150) | YES  | | NULL|   |
> | SPECIES| varchar(10)  | YES  | | NULL|   |
> | SOURCESEQ  | varchar(15)  | YES  | | NULL|   |
> ++--+--+-+-+---+
> 9 rows in set (0.01 sec)
>
> mysql> describe subsnp;
> ++-+--+-+-+---+
> | Field  | Type| Null | Key | Default | Extra |
> ++-+--+-+-+---+
> | SUBSNP_PK  | int(11) |  | PRI | 0   |   |
> | SOURCE | varchar(40) |  | | |   |
> | SOURCEID   | varchar(20) |  | | |   |
> | SNPPOSITION| int(11) |  | | 0   |   |
> | TOTALSEQLENGTH | int(11) |  | | 0   |   |
> | VARIATION  | varchar(20) | YES  | | NULL|   |
> | NUM_ALLELES| int(11) | YES  | | NULL|   |
> ++-+--+-+-+---+
> 7 rows in set (0.00 sec)
>
> mysql> describe chromosome_position;
> +--++--+-+-+---+
> | Field| Type   | Null | Key | Default | Extra |
> +--++--+-+-+---+
> | SNP_FK   | int(11)|  | | 0   |   |
> | CHROM| varchar(5) | YES  | | NULL|   |
> | CHROMPOS | int(11)| YES  | | NULL|   |
> +--++--+-+-+---+
> 3 rows in set (0.00 sec)
>
> mysql> describe locus_annotation;
> +-+-+--+-+-+---+
> | Field   | Type| Null | Key | Default | Extra |
> +-+-+--+-+-+---+
> | SNP_FK  | int(11) |  | | 0   |   |
> | LOCUS  

RE: Help needed with Query UPDATE INFO

[Bryan Coon]

Okay, here is the full monty from the database regarding the query in
question, any suggestions on how to improve any of it are greatly
appreciated!

Also, if any more info is needed, just let me know.

Thanks!
Bryan


QUERY:
mysql> select distinct subsnp_pk as sqnmid, c.chrom, chrompos, source as
panel,  null as first_pcrp, null as second_pcrp, null as prb_seq, null as
term,  null as validation, null as freq, null as population  from locuslink
l, subsnp left outer join chromosome_position c  on subsnp_pk = c.snp_fk
left outer join locus_annotation a  on subsnp_pk = a.snp_fk where a.locusid
= substring(l.locusid, 4) and description like '%GABA%' order by subsnp_pk;

EXPLAIN:
++--+---+--+-+--+---+---
--+
| table  | type | possible_keys | key  | key_len | ref  | rows  | Extra
|
++--+---+--+-+--+---+---
--+
| subsnp | ALL  | NULL  | NULL |NULL | NULL |  1201 | Using
temporary; Using filesort |
| l  | ALL  | NULL  | NULL |NULL | NULL | 21294 | where used
|
| c  | ALL  | NULL  | NULL |NULL | NULL |   579 |
|
| a  | ALL  | NULL  | NULL |NULL | NULL |   704 | where
used; Distinct|
++--+---+--+-+--+---+---
--+
4 rows in set (0.00 sec)

DESCRIBE:
mysql> describe locuslink;
++--+--+-+-+---+
| Field  | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| LOCUSID| varchar(12)  |  | | |   |
| SYMBOL | varchar(15)  | YES  | | NULL|   |
| INTERIM_SYMBOL | varchar(15)  | YES  | | NULL|   |
| MIM_NUMBER | varchar(15)  | YES  | | NULL|   |
| CHROM  | varchar(6)   | YES  | | NULL|   |
| BAND   | varchar(20)  | YES  | | NULL|   |
| DESCRIPTION| varchar(150) | YES  | | NULL|   |
| SPECIES| varchar(10)  | YES  | | NULL|   |
| SOURCESEQ  | varchar(15)  | YES  | | NULL|   |
++--+--+-+-+---+
9 rows in set (0.01 sec)

mysql> describe subsnp;
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| SUBSNP_PK  | int(11) |  | PRI | 0   |   |
| SOURCE | varchar(40) |  | | |   |
| SOURCEID   | varchar(20) |  | | |   |
| SNPPOSITION| int(11) |  | | 0   |   |
| TOTALSEQLENGTH | int(11) |  | | 0   |   |
| VARIATION  | varchar(20) | YES  | | NULL|   |
| NUM_ALLELES| int(11) | YES  | | NULL|   |
++-+--+-+-+---+
7 rows in set (0.00 sec)

mysql> describe chromosome_position;
+--++--+-+-+---+
| Field| Type   | Null | Key | Default | Extra |
+--++--+-+-+---+
| SNP_FK   | int(11)|  | | 0   |   |
| CHROM| varchar(5) | YES  | | NULL|   |
| CHROMPOS | int(11)| YES  | | NULL|   |
+--++--+-+-+---+
3 rows in set (0.00 sec)

mysql> describe locus_annotation;
+-+-+--+-+-+---+
| Field   | Type| Null | Key | Default | Extra |
+-+-+--+-+-+---+
| SNP_FK  | int(11) |  | | 0   |   |
| LOCUS   | varchar(20) | YES  | | NULL|   |
| LOCUSID | int(11) | YES  | | NULL|   |
+-+-+--+-+-+---+
3 rows in set (0.00 sec)

mysql> 

KEYS:
mysql> show keys from locuslink;
Empty set (0.00 sec)

mysql> show keys from subsnp;
+++--+--+-+---+-
+--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+++--+--+-+---+-
+--++-+
| subsnp |  0 | PRIMARY  |1 | SUBSNP_PK   | A |
1201 | NULL | NULL   | |
+++--+--+-+---+-
+--++-+
1 row in set (0.00 sec)

mysql> show keys from chromosome_position;
Empty set (0.00 sec)

mysql> show keys from locus_annotation;
Empty set (0.00 sec)

mysql> 


-
Before posting, please check:
   http://www.mysql.com

Re: Help needed with Query

[Eric Fitzgerald]

Please do a "SHOW FIELDS FROM" and "SHOW KEYS FROM" on all tables involved
so we can see what's going on here.  Also, a warning, if desacription is
indexed, by doing LIKE '%GABA%' with wildcard at beginning, it won't use
index's.

Would also help if you did an EXPLAIN on that query and sent that.

- Original Message -
From: "Bryan Coon" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, April 27, 2001 1:20 PM
Subject: Help needed with Query


> I am working on a mysql database with many large tables (1.5 million rows
on
> some) and came across a beeg problem.
>
> I have need for a query like this:
> mysql> select distinct subsnp_pk as sqnmid, c.chrom, chrompos, source as
> panel,  null as first_pcrp, null as second_pcrp, null as prb_seq, null as
> term,  null as validation, null as freq, null as population  from
locuslink
> l, subsnp left outer join chromosome_position c  on subsnp_pk = c.snp_fk
> left outer join locus_annotation a  on subsnp_pk = a.snp_fk where
a.locusid
> = substring(l.locusid, 4) and description like '%GABA%' order by
subsnp_pk;
>
> Which seems insane.  This 4 table join in another situation would become a
6
> table join.  As it is, it takes 1min 10.60 seconds for this query to
return
> one result.  Clearly I am not a MySQL expert, and even though this query
> works, there must be a better way to do this.
>
> Is it a matter of indexing?  A matter of poor schema design?  Poor query
> design?  Is it unavoidable?
>
> As far as I know, none of the tables are indexed (I did not set up the
> schema).
>
> Please let me know if more info is required to help with this.
>
> Thanks,
> Bryan
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Help needed with Query

[Ravi Raman]

hi.

by skimming that query, the first thing that will take a long time is the
'description like "%GABA%"' part...that's a pretty inefficient method of
searching due to the wildcard at the beginning of the string.

if there are no indexes on the tables, you should probably add some.
"show index from tablename" will tell you for sure.

check out the mysql manual section re: indexes for more information...also
use the keyword EXPLAIN before the select query to find out where/if indexes
are being used and where they would likely be the most helpful.

hth.
-ravi.

-Original Message-
From: Bryan Coon [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 27, 2001 4:20 PM
To: '[EMAIL PROTECTED]'
Subject: Help needed with Query


I am working on a mysql database with many large tables (1.5 million rows on
some) and came across a beeg problem.

I have need for a query like this:
mysql> select distinct subsnp_pk as sqnmid, c.chrom, chrompos, source as
panel,  null as first_pcrp, null as second_pcrp, null as prb_seq, null as
term,  null as validation, null as freq, null as population  from locuslink
l, subsnp left outer join chromosome_position c  on subsnp_pk = c.snp_fk
left outer join locus_annotation a  on subsnp_pk = a.snp_fk where a.locusid
= substring(l.locusid, 4) and description like '%GABA%' order by subsnp_pk;

Which seems insane.  This 4 table join in another situation would become a 6
table join.  As it is, it takes 1min 10.60 seconds for this query to return
one result.  Clearly I am not a MySQL expert, and even though this query
works, there must be a better way to do this.

Is it a matter of indexing?  A matter of poor schema design?  Poor query
design?  Is it unavoidable?

As far as I know, none of the tables are indexed (I did not set up the
schema).

Please let me know if more info is required to help with this.

Thanks,
Bryan


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help needed on mysql!!

[Gerald Clark]

What do you mean by "better" ?

Next time it hangs, check your disk freespace.
When MySQL runs out of tmp workspace, it waits until some becomes
available.



[EMAIL PROTECTED] wrote:
> 
> Help!
> I have set up a web server with PHP4, RH7 and MySQL 3.23.32.
> After surfing for sometime, the browser will be in a forever loading
> mode.
> I use mysqladmin processlist to take a look and I saw processes
> with the command "sleep" (it could be just 1 "sleep" process).
> 
> Everytime I encounter this, I have to restart httpd server.
> 
> Qn: How can I optimize apache, or php or mysql so that it won't "sleep"?
>Where should I modify to have no such problem again?
> 
> With a better machine config with same os/softwareconfiguration,
> it seems to have no such problem...does that mean it is due to hardware?
> 
> Please help.
> TIA!!
> 
> Regards
> 
>

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help Needed

[training88]

Hello Pat

Well, I really feel stupid! Under the gun with this any I really don't
have a clue. I also need to install some type of DBI foe windows2000
advanced server too and I'm now downloading a perl file off of
mysql.com. I really have no idea working with windows. My main OS that I
have been working with was FreeBSD server.

Again thanks for the time that you took out to help me out!
Joe



Pat Sherrill wrote:
> 
> Expected behaviour, look in your Tray on the Taskbar for a stoplight icon.
> Pat...
> 
> - Original Message -
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Saturday, March 17, 2001 6:12 PM
> Subject: Help Needed
> 
> > Hi
> >
> > I just downloaded MYsql for windows NT. I'm running it on Windows2000
> > Advanced Server. Everything loads fine and it is running ok but, when I
> > try to run Winmysqladmin the program loads up for only about 2 seconds
> > and then closes. Any ideas on what I need to do?
> >
> > Thanks
> > Joe
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help Needed

[Pat Sherrill]

Expected behaviour, look in your Tray on the Taskbar for a stoplight icon.
Pat...

- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, March 17, 2001 6:12 PM
Subject: Help Needed


> Hi
>
> I just downloaded MYsql for windows NT. I'm running it on Windows2000
> Advanced Server. Everything loads fine and it is running ok but, when I
> try to run Winmysqladmin the program loads up for only about 2 seconds
> and then closes. Any ideas on what I need to do?
>
> Thanks
> Joe
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: (+) Help needed

[Scott Gerhardt]

Slight correction, for some reason the query below does NOT work on my
sample DB unless I quote the date values.

This should work now:

 SELECT IP, Size, Date FROM your_table
WHERE Date BETWEEN '2001-02-01' AND '2001-05-01';



>
> so and i have Mysql table with
> IP   | Size | Date|
> --
> 213.32.44.11 | 3|2001-01-11-12|
> --
> 213.32.44.11 | 3|2001-01-11-13|
> --
> 213.32.44.12 | 3|2001-01-12-12|
> --
> 213.32.44.14 | 3|2001-01-13-17|
> --
> 213.32.44.16 | 3|2001-01-14-11|
> --
> 213.32.44.111 | 3|2001-02-15-10|
> --
> 213.32.44.121 | 3|2001-01-13-10|
> --
> 213.32.44.131 | 3|2001-01-15-10|
> --
>
>
> So i need SELECT from table only record between values
> $dat1 and $dat2
> how to do this ?!
> Thx
> _
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: (+) Help needed

[Artem Koutchine]

Don't you think this has nothing specific to MySQL? Maybe you
should read some books on SQL and the manual for MySQL?

SELECT * FROM
tablename
WHERE
datefield>'2001-01-11' AND
datefied<'2001-02-12'

Don;t ask how to correctly call the query in perl, read
perldoc DBI
perldoc DBD::mysql

Does ANYBODY read  books manuals  these days?

Bye
Artem


- Original Message -
From: "Alexei Sh." <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, January 27, 2001 4:04 PM
Subject: (+) Help needed


> Ok I need help with this..
> I have to values like
> $dat1="2001-01-11";
> $dat2="2001-02-12";
>
> so and i have Mysql table with
> IP   | Size | Date|
> --
> 213.32.44.11 | 3|2001-01-11-12|
> --
> 213.32.44.11 | 3|2001-01-11-13|
> --
> 213.32.44.12 | 3|2001-01-12-12|
> --
> 213.32.44.14 | 3|2001-01-13-17|
> --
> 213.32.44.16 | 3|2001-01-14-11|
> --
> 213.32.44.111 | 3|2001-02-15-10|
> --
> 213.32.44.121 | 3|2001-01-13-10|
> --
> 213.32.44.131 | 3|2001-01-15-10|
> --
>
>
> So i need SELECT from table only record between values
> $dat1 and $dat2
> how to do this ?!
> Thx
>
__
___
> Get Your Private, Free E-mail from MSN Hotmail at
http://www.hotmail.com.
>
>
> 
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php