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]

Microfinance database

[Alvaro Cobo]

Hi guys. I don't want my job done by anybody else, but I have found no solution 
for several days to this problem. 

I am developing a micro-finance system to farmer communities. The problem I 
have got is in the database description. 

I have three principal tables: (see SQL at the end). 

1. Loan. # Where I register the loan. 
2. Resources/goods given. # Where I register the goods give in the credit (and 
I sum all the resources to know how much money the credit has). 
3. Payments. # Where I register the payments. 

I've got all the formulas to calculate the interest and amortization tables. 
The problem is with the penalty/default interest. 

Example: 

Paul has got a loan: he's got a cow and money (total 500). 

He has to pay 80 dollars each month. 75 correspond to capital and 5 to 
interest. 

If he fails in paying one of the installment, I have to calculate the penalty 
(which in this cases is 10% per year) in daily basis. Two days later he has 
paid part of the installment (30) so I have to calculate the penalization 
interest for the two days, then discount the interest, and the discount the 
rest from the Principal (capital). Then he has got 39 as unpaid capital. 

Three days later he has paid 20 dollars. Again, I have to calculate the 
penalization interest, and substract it from the capital in debt. And like this 
until it gets the payment finished. 

I know all the formulas and stuff, but my problem is in the database 
definition, and specifically, where to store the payments, the failed 
installment and the penalization interest. 

The questions are: 

1. Should I store the failed installment in a separate table, or in the same 
payments table?. 
2. The calculations (interest, failed capital, penalization interest) must be 
stored in a table or retrieved using a query?. 
3. The most important question: Does anybody could help me with a link or 
information about how might be the structure of a financial database?. 

Thank you very much guys, and again, as you can see I am very confused and I 
don't my job done by anybody else. I just need your wise advice. 

Best regards, 

Alvaro. 

TABLES: 

/*Table for the credits. */

CREATE TABLE `tbl04_Credit` (
`PK_Credit` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`FK_ProjectHolderId` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`date_credit` date NOT NULL default '-00-00',
`interest` double NOT NULL default '0',
`tiempo_credito_days` double NOT NULL default '0',
`periodicity_credit` int(11) NOT NULL default '0',
`nom_responsable` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`comment_credit` varchar(255) collate latin1_spanish_ci default NULL,
`last_change` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,
PRIMARY KEY (`PK_Credit`),
KEY `FK_ProjectHolderId` (`FK_ProjectHolderId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

INSERT INTO `tbl04_Credit` VALUES ('1', 'Community One', '2005-10-25', 12, 360, 
30, 'Paul Simon', 'No comments', '2005-10-25 01:35:24');
INSERT INTO `tbl04_Credit` VALUES ('2', 'Community Two', '2005-10-27', 12, 360, 
30, 'Art Garfunkel', 'No comments', '2005-10-25 01:35:05');

/*  Resource table   */

CREATE TABLE `tbl04_CreditResource` (
`PK_Cred_Recurso` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`FK_Credito` varchar(255) collate latin1_spanish_ci NOT NULL default '',
`resource` varchar(60) collate latin1_spanish_ci default NULL,
`UnitValue` float NOT NULL default '0',
`Quantity` float default NULL,
`penalty_interest` float NOT NULL default '0',
`credit_time` int(11) NOT NULL default '0',
PRIMARY KEY (`PK_Cred_Recurso`),
KEY `FK_Credito` (`FK_Credito`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci ;

INSERT INTO `tbl04_CreditResource` VALUES ('Resource1', '1', 'Duck', 20, 8, 12, 
360);
INSERT INTO `tbl04_CreditResource` VALUES ('Resource2', '1', 'Chicken', 5, 8, 
12, 360);
INSERT INTO `tbl04_CreditResource` VALUES ('Resource3', '2', 'Cow', 250, 1, 12, 
360);
INSERT INTO `tbl04_CreditResource` VALUES ('Resource4', '2', 'Chicken', 5, 10, 
12, 360);


/*Payments*/

CREATE TABLE `tbl05_Payment` (
`PK_Pago` int(11) NOT NULL auto_increment,
`FK_Cred_Recurso` varchar(255) collate latin1_spanish_ci default NULL,
`installment_number` int(11) NOT NULL default '0',
`end_installment_date` date NOT NULL default '-00-00',
`payment_date` date NOT NULL default '-00-00',
`payment_capital` float default '0',
`payment_interest` float default '0',
`unpaid_capital` float default '0',
`interest_unpaid_capital` float default NULL,
`comentario_pago` varchar(255) collate latin1_spanish_ci default NULL,
`ultimo_cambio` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,
`Estado` enum('1','0') collate latin1_spanish_ci NOT NULL default '0',
PRIMARY KEY (`PK_Pago`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;




-
"If you have an apple a

Re: Date storage format

[Martijn Tonies]

>
>Can anyone tell me what advantages there are in keeping dates and times in
a MySQL DateTime field, as opposed to storing its >string equivalent in a
Varchar field ?


Decent sorting, validity checking, being able to use the DATE and TIME
functions etc etc...

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Date storage format

[Sinang, Danny]

Hello,
 
Can anyone tell me what advantages there are in keeping dates and times in a 
MySQL DateTime field, as opposed to storing its string equivalent in a Varchar 
field ?
 
Regards,
Danny
 
 

Re: ERROR 1005 (HY000): Can't create table '.\testDataBase\#sql-ec4_c.frm' (errno: 139)

[Heikki Tuuri]

Sunil,

in InnoDB, the maximum indexed column length is 767 bytes.

Osku is improving the error message:
http://bugs.mysql.com/bug.php?id=13315

Regards,

Heikki
Oracle/Innobase

- Original Message - 
From: ""Sunil Vishwas"" <[EMAIL PROTECTED]>

Newsgroups: mailing.database.myodbc
Sent: Tuesday, October 25, 2005 5:33 AM
Subject: ERROR 1005 (HY000): Can't create table 
'.\testDataBase\#sql-ec4_c.frm' (errno: 139)




--_=_NextPart_001_01C5D90C.62DB5CF5
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

(I am using mysql-5.0.15-win32)
Why I am getting this error and is there any way I can fix it, or is it
a bug?
Between I don't get this error if I change the field size to 767 or
below:
=20
drop table Address;

CREATE TABLE `Address`=20
(
`RecId` CHAR(32) NOT NULL,=20
`WebAddress` VARCHAR(1000),=20
CONSTRAINT PKAddress PRIMARY KEY(RecId)
);
=20
CREATE INDEX AddressWebAddressWebAddress ON Address (WebAddress);
=20

--_=_NextPart_001_01C5D90C.62DB5CF5-- 



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

Help needed for SQL statement

[Xiaobo Chen]

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]

Re: Map of MySQL Users

[Jeffrey Goldberg]

[posted only]

On Oct 24, 2005, at 8:14 PM, Claire McLister wrote:

if you go to the web page, http://www.zeesource.net/maps/help.do,  
then in the paragraph under the heading "Automatic Maps" the last  
sentence should give you the location of your IP.


Hmm.  That located me perfectly, but on the map, I was off by 2000km.

However, I had just updated my location (deleted my entry and then  
created a new one with my correct address), so if that fed back into  
the location system, that might explain it.


And this is great.

-j


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

ERROR 1005 (HY000): Can't create table '.\testDataBase\#sql-ec4_c.frm' (errno: 139)

[Sunil Vishwas]

(I am using mysql-5.0.15-win32)
Why I am getting this error and is there any way I can fix it, or is it
a bug?
Between I don't get this error if I change the field size to 767 or
below:
 
drop table Address;

CREATE TABLE `Address` 
(
 `RecId` CHAR(32) NOT NULL, 
 `WebAddress` VARCHAR(1000), 
 CONSTRAINT PKAddress PRIMARY KEY(RecId)
);
 
CREATE INDEX AddressWebAddressWebAddress ON Address (WebAddress);
 

demon quits immediately...

[tom wible]

Description:


[EMAIL PROTECTED] mysql-standard-5.0.15-linux-i686]# Starting mysqld
daemon with databases from /usr/local/mysql-standard-5.0.15-linux-i686/data
STOPPING server from pid file
/usr/local/mysql-standard-5.0.15-linux-i686/data/tomsEmachine.pid
051024 19:52:09  mysqld ended

[EMAIL PROTECTED] mysql-standard-5.0.15-linux-i686]# cat 
/var/lib/mysql/tomsEmachine.err

051024 18:38:27  mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
051024 18:38:28  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
051024 18:38:28  InnoDB: Log file ./ib_logfile0 did not exist: new to be 
created

InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
051024 18:38:29  InnoDB: Log file ./ib_logfile1 did not exist: new to be 
created

InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
051024 18:38:29  InnoDB: Started; log sequence number 0 0
051024 18:38:29 [ERROR] Fatal error: Can't open and lock privilege 
tables: Table 'mysql.host' doesn't exist

051024 18:38:30  mysqld ended

051024 19:54:03  mysqld started
051024 19:54:03  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...
051024 19:54:03  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 36808.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
051024 19:54:04  InnoDB: Starting an apply batch of log records to the 
database...
InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 
19 20 21 22 23 24 25 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
051024 19:54:04  InnoDB: Started; log sequence number 0 43655
051024 19:54:04 [ERROR] Fatal error: Can't open and lock privilege 
tables: Table 'mysql.host' doesn't exist

051024 19:54:04  mysqld ended



Submitter-Id:airdrummer
Originator:tom wible
Organization:
MySQL support: none
Synopsis:demon quits immediately
Severity:   critical
Priority:<[ low | medium | high ] (one line)>
Category:mysql
Class:sw-bug
Release:mysql-5.0.15-standard (MySQL Community Edition - Standard 

(GPL))


C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
C++ compiler:  gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
Environment:

   
System: Linux tomsEmachine 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT
2005 i686 i686 i386 GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Using built-in specs.
Target: i386-redhat-linux
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix
--enable-checking=release --with-system-zlib --enable-__cxa_atexit
--disable-libunwind-exceptions --enable-libgcj-multifile
--enable-languages=c,c++,objc,java,f95,ada --enable-java-awt=gtk
--with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre
--host=i386-redhat-linux
Thread model: posix
gcc version 4.0.0 20050519 (Red Hat 4.0.0-8)
Compilation info: CC='gcc'  CFLAGS=''  CXX='gcc'  CXXFLAGS=''
LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx  1 root root 13 Jun 19 21:47 /lib/libc.so.6 -> libc-2.3.5.so
-rwxr-xr-x  1 root root 1489572 May 30 08:05 /lib/libc-2.3.5.so
-rw-r--r--  1 root root 2523698 May 30 08:44 /usr/lib/libc.a
-rw-r--r--  1 root root 238 May 30 07:10 /usr/lib/libc.so
lrwxrwxrwx  1 root root 10 Jun 19 23:31 /usr/lib/libc-client.a -> c-client.a
lrwxrwxrwx  1 root root 16 Jun 19 23:31 /usr/lib/libc-client.so ->
libc-client.so.0
-rwxr-xr-x  1 root root 773464 Mar  2  2005 /usr/lib/libc-client.so.0
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data'
'--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community
Edition - Standard (GPL)' '--with-extra-charsets=complex'
'--with-server-suffix=-standard' '--enable-thread-safe-client'
'--enable-local-infile' '--enable-assembler' '--disable-shared'
'--with-big-tables' '--with-readline' '--with-archive-storage-engine'
'--with-innodb' 'CC=gcc' 'CXX=gcc'




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

Next alpha release?

[Josh Trutwin]

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

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

Josh

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

demon quits immediately...

[tom wible]

Description:


[EMAIL PROTECTED] mysql-standard-5.0.15-linux-i686]# Starting mysqld 
daemon with databases from /usr/local/mysql-standard-5.0.15-linux-i686/data
STOPPING server from pid file 
/usr/local/mysql-standard-5.0.15-linux-i686/data/tomsEmachine.pid

051024 19:52:09  mysqld ended



Submitter-Id:airdrummer
Originator:tom wible
Organization:
MySQL support: none
Synopsis:demon quits immediately
Severity:   critical
Priority:<[ low | medium | high ] (one line)>
Category:mysql
Class:sw-bug
Release:mysql-5.0.15-standard (MySQL Community Edition - Standard 

(GPL))


C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
C++ compiler:  gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
Environment:

   
System: Linux tomsEmachine 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT
2005 i686 i686 i386 GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Using built-in specs.
Target: i386-redhat-linux
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix
--enable-checking=release --with-system-zlib --enable-__cxa_atexit
--disable-libunwind-exceptions --enable-libgcj-multifile
--enable-languages=c,c++,objc,java,f95,ada --enable-java-awt=gtk
--with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre
--host=i386-redhat-linux
Thread model: posix
gcc version 4.0.0 20050519 (Red Hat 4.0.0-8)
Compilation info: CC='gcc'  CFLAGS=''  CXX='gcc'  CXXFLAGS=''
LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx  1 root root 13 Jun 19 21:47 /lib/libc.so.6 -> libc-2.3.5.so
-rwxr-xr-x  1 root root 1489572 May 30 08:05 /lib/libc-2.3.5.so
-rw-r--r--  1 root root 2523698 May 30 08:44 /usr/lib/libc.a
-rw-r--r--  1 root root 238 May 30 07:10 /usr/lib/libc.so
lrwxrwxrwx  1 root root 10 Jun 19 23:31 /usr/lib/libc-client.a -> c-client.a
lrwxrwxrwx  1 root root 16 Jun 19 23:31 /usr/lib/libc-client.so ->
libc-client.so.0
-rwxr-xr-x  1 root root 773464 Mar  2  2005 /usr/lib/libc-client.so.0
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data'
'--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community
Edition - Standard (GPL)' '--with-extra-charsets=complex'
'--with-server-suffix=-standard' '--enable-thread-safe-client'
'--enable-local-infile' '--enable-assembler' '--disable-shared'
'--with-big-tables' '--with-readline' '--with-archive-storage-engine'
'--with-innodb' 'CC=gcc' 'CXX=gcc'



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

Re: demon quits immediately...

[Peter J Milanese]

Logs?

-
Sent from my NYPL BlackBerry Handheld.


- Original Message -
From: tom wible [EMAIL PROTECTED]
Sent: 10/24/2005 06:58 PM
To: mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Subject: demon quits immediately...

>Description:
[EMAIL PROTECTED] mysql-standard-5.0.15-linux-i686-glibc23]# Starting
mysqld daemon with databases from
/usr/local/mysql-standard-5.0.15-linux-i686-glibc23/data
STOPPING server from pid file
/usr/local/mysql-standard-5.0.15-linux-i686-glibc23/data/tomsEmachine.pid
051024 18:36:32  mysqld ended

>Submitter-Id:airdrummer
>Originator:tom wible
>Organization:
>MySQL support: none
>Synopsis:demon quits immediately
>Severity:   critical
>Priority:<[ low | medium | high ] (one line)>
>Category:mysql
>Class:sw-bug
>Release:mysql-5.0.15-standard (MySQL Community Edition - Standard
(GPL))

>C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
>C++ compiler:  gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
>Environment:
   
System: Linux tomsEmachine 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT
2005 i686 i686 i386 GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Using built-in specs.
Target: i386-redhat-linux
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix
--enable-checking=release --with-system-zlib --enable-__cxa_atexit
--disable-libunwind-exceptions --enable-libgcj-multifile
--enable-languages=c,c++,objc,java,f95,ada --enable-java-awt=gtk
--with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre
--host=i386-redhat-linux
Thread model: posix
gcc version 4.0.0 20050519 (Red Hat 4.0.0-8)
Compilation info: CC='gcc'  CFLAGS=''  CXX='gcc'  CXXFLAGS=''
LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx  1 root root 13 Jun 19 21:47 /lib/libc.so.6 -> libc-2.3.5.so
-rwxr-xr-x  1 root root 1489572 May 30 08:05 /lib/libc-2.3.5.so
-rw-r--r--  1 root root 2523698 May 30 08:44 /usr/lib/libc.a
-rw-r--r--  1 root root 238 May 30 07:10 /usr/lib/libc.so
lrwxrwxrwx  1 root root 10 Jun 19 23:31 /usr/lib/libc-client.a -> c-client.a
lrwxrwxrwx  1 root root 16 Jun 19 23:31 /usr/lib/libc-client.so ->
libc-client.so.0
-rwxr-xr-x  1 root root 773464 Mar  2  2005 /usr/lib/libc-client.so.0
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data'
'--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community
Edition - Standard (GPL)' '--with-extra-charsets=complex'
'--with-server-suffix=-standard' '--enable-thread-safe-client'
'--enable-local-infile' '--enable-assembler' '--disable-shared'
'--with-big-tables' '--with-readline' '--with-archive-storage-engine'
'--with-innodb' 'CC=gcc' 'CXX=gcc'


--
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: Map of MySQL Users

[Robert L Cochran]

This shows me as being in Washington, DC. My ISP has a point-of-presence 
somewhere in the city. My physical location is Greenbelt, Maryland, USA.


Bob Cochran

Claire McLister wrote:


Peter,

 We try to take care of the situation where people are coming from an 
ISP account.


 Let me know if you think there is a specific case that is not right 
on our map. I searched for AOL in the map, and did not find a single 
entry from AOL.


 We do not use whois databases. As you correctly point out, those can 
be very misleading. We utilize a commercial service for mapping IP to 
location, and so far we've had very good success with them. For 
example, if you go to the web page, 
http://www.zeesource.net/maps/help.do, then in the paragraph under the 
heading "Automatic Maps" the last sentence should give you the 
location of your IP.


Claire

On Oct 24, 2005, at 5:12 PM, Peter J Milanese wrote:

This is particularly troublesome when it come to isp accounts. I.E., 
all aol users showing Reston as their base.


I tried something like this using whois databases and had the same 
issues. I canned the idea for now.


P

-
Sent from my NYPL BlackBerry Handheld.


- Original Message -
From: "J.R. Bullington" [EMAIL PROTECTED]
Sent: 10/24/2005 07:58 PM
To: "'Claire McLister'" <[EMAIL PROTECTED]>
Cc: 
Subject: RE: Map of MySQL Users

Thank you for your email.

It seems as though you are getting rave reviews from the list. I am sure
that if you want to make this really neat, try creating a simple web 
login

that allows for you to create an account using the email address, then
update your location so that it can be a little more accurate.

As stated in some of the posts, a lot of times your lat/long service 
may not

be entirely right as it is basing the lat/long off the mail carrier, not
necessarily the location of the user.

J.R.

PS -- Listers, sorry I didn't CC the list on my first correspondence. 
Here

it is with her answers.

GREAT JOB CLAIRE & COMPANY!

-Original Message-
From: Claire McLister [mailto:[EMAIL PROTECTED]
Sent: Monday, October 24, 2005 6:24 PM
To: [EMAIL PROTECTED]
Subject: Re: Map of MySQL Users

We use the origin IP and then convert it to a latitude/longitude using a
commercial service. There are several services available these days 
to do

this conversion.

We use any email posted to the list for which we can get  a decent IP 
value.
It could be from a registered MySQL user or not. Even a single post 
should
get you on the map. The only thing is a special email address needs 
to be
added to the list so that we can get location information from each 
email.


Thanks for your feedback. Yes, I think adding a geographical 
dimension to

the email lists brings up interesting possibilities.

Let me know if you want to see maps of other email lists.

Claire


On Oct 24, 2005, at 3:05 PM, J.R. Bullington wrote:


Couple of questions for you --

What are you using to keep pinpoint the origin of the submitted emails?
PING, TRACERT, WHOIS off the domain names?

Are you only doing original posts, or original & replies?
Do they need to be registered users of mySQL or just post to the list?
Do you have to post / reply a minimum number of times to be
"noticed"/added onto the list?

-- Now back to the original topic --

The popup JS is great (love the filter / search feature).
It's a great system and, if used, I think it will bring MySQL users
together and help in creating site/regional specific MySQL
lists/conferences.


Thanks for listening!

J.R.

-Original Message-
From: Claire McLister [mailto:[EMAIL PROTECTED]
Sent: Monday, October 24, 2005 5:19 PM
To: mysql@lists.mysql.com
Subject: Map of MySQL Users

Hi,

  We've developed an automatic email mapping capability from Google
Maps API.

  To try it out, we mapped origins of emails to this group from
October
3 through October 14th.

The result of this map is at:
http://www.zeesource.net/maps/map.do?group=460

  Would like to hear what you think of it.

  Best wishes

Claire

  --
  Claire McLister[EMAIL PROTECTED]
  1684 Nightingale Avenue Suite 201
  Sunnyvale, CA 94087408-733-2737(fax)

http://www.zeemaps.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]

demon quits immediately...

[tom wible]

>Description:
[EMAIL PROTECTED] mysql-standard-5.0.15-linux-i686-glibc23]# Starting 
mysqld daemon with databases from 
/usr/local/mysql-standard-5.0.15-linux-i686-glibc23/data
STOPPING server from pid file 
/usr/local/mysql-standard-5.0.15-linux-i686-glibc23/data/tomsEmachine.pid

051024 18:36:32  mysqld ended

>Submitter-Id:airdrummer
>Originator:tom wible
>Organization:
>MySQL support: none
>Synopsis:demon quits immediately
>Severity:   critical
>Priority:<[ low | medium | high ] (one line)>
>Category:mysql
>Class:sw-bug
>Release:mysql-5.0.15-standard (MySQL Community Edition - Standard 
(GPL))


>C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
>C++ compiler:  gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52)
>Environment:
   
System: Linux tomsEmachine 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT 
2005 i686 i686 i386 GNU/Linux

Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc 
/usr/bin/cc

GCC: Using built-in specs.
Target: i386-redhat-linux
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man 
--infodir=/usr/share/info --enable-shared --enable-threads=posix 
--enable-checking=release --with-system-zlib --enable-__cxa_atexit 
--disable-libunwind-exceptions --enable-libgcj-multifile 
--enable-languages=c,c++,objc,java,f95,ada --enable-java-awt=gtk 
--with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre 
--host=i386-redhat-linux

Thread model: posix
gcc version 4.0.0 20050519 (Red Hat 4.0.0-8)
Compilation info: CC='gcc'  CFLAGS=''  CXX='gcc'  CXXFLAGS=''  
LDFLAGS=''  ASFLAGS=''

LIBC:
lrwxrwxrwx  1 root root 13 Jun 19 21:47 /lib/libc.so.6 -> libc-2.3.5.so
-rwxr-xr-x  1 root root 1489572 May 30 08:05 /lib/libc-2.3.5.so
-rw-r--r--  1 root root 2523698 May 30 08:44 /usr/lib/libc.a
-rw-r--r--  1 root root 238 May 30 07:10 /usr/lib/libc.so
lrwxrwxrwx  1 root root 10 Jun 19 23:31 /usr/lib/libc-client.a -> c-client.a
lrwxrwxrwx  1 root root 16 Jun 19 23:31 /usr/lib/libc-client.so -> 
libc-client.so.0

-rwxr-xr-x  1 root root 773464 Mar  2  2005 /usr/lib/libc-client.so.0
Configure command: ./configure '--prefix=/usr/local/mysql' 
'--localstatedir=/usr/local/mysql/data' 
'--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community 
Edition - Standard (GPL)' '--with-extra-charsets=complex' 
'--with-server-suffix=-standard' '--enable-thread-safe-client' 
'--enable-local-infile' '--enable-assembler' '--disable-shared' 
'--with-big-tables' '--with-readline' '--with-archive-storage-engine' 
'--with-innodb' 'CC=gcc' 'CXX=gcc'



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

Re: Map of MySQL Users

[Claire McLister]

Peter,

 We try to take care of the situation where people are coming from an 
ISP account.


 Let me know if you think there is a specific case that is not right on 
our map. I searched for AOL in the map, and did not find a single entry 
from AOL.


 We do not use whois databases. As you correctly point out, those can 
be very misleading. We utilize a commercial service for mapping IP to 
location, and so far we've had very good success with them. For 
example, if you go to the web page, 
http://www.zeesource.net/maps/help.do, then in the paragraph under the 
heading "Automatic Maps" the last sentence should give you the location 
of your IP.


Claire

On Oct 24, 2005, at 5:12 PM, Peter J Milanese wrote:

This is particularly troublesome when it come to isp accounts. I.E., 
all aol users showing Reston as their base.


I tried something like this using whois databases and had the same 
issues. I canned the idea for now.


P

-
Sent from my NYPL BlackBerry Handheld.


- Original Message -
From: "J.R. Bullington" [EMAIL PROTECTED]
Sent: 10/24/2005 07:58 PM
To: "'Claire McLister'" <[EMAIL PROTECTED]>
Cc: 
Subject: RE: Map of MySQL Users

Thank you for your email.

It seems as though you are getting rave reviews from the list. I am 
sure
that if you want to make this really neat, try creating a simple web 
login

that allows for you to create an account using the email address, then
update your location so that it can be a little more accurate.

As stated in some of the posts, a lot of times your lat/long service 
may not
be entirely right as it is basing the lat/long off the mail carrier, 
not

necessarily the location of the user.

J.R.

PS -- Listers, sorry I didn't CC the list on my first correspondence. 
Here

it is with her answers.

GREAT JOB CLAIRE & COMPANY!

-Original Message-
From: Claire McLister [mailto:[EMAIL PROTECTED]
Sent: Monday, October 24, 2005 6:24 PM
To: [EMAIL PROTECTED]
Subject: Re: Map of MySQL Users

We use the origin IP and then convert it to a latitude/longitude using 
a
commercial service. There are several services available these days to 
do

this conversion.

We use any email posted to the list for which we can get  a decent IP 
value.
It could be from a registered MySQL user or not. Even a single post 
should
get you on the map. The only thing is a special email address needs to 
be
added to the list so that we can get location information from each 
email.


Thanks for your feedback. Yes, I think adding a geographical dimension 
to

the email lists brings up interesting possibilities.

Let me know if you want to see maps of other email lists.

Claire


On Oct 24, 2005, at 3:05 PM, J.R. Bullington wrote:


Couple of questions for you --

What are you using to keep pinpoint the origin of the submitted 
emails?

PING, TRACERT, WHOIS off the domain names?

Are you only doing original posts, or original & replies?
Do they need to be registered users of mySQL or just post to the list?
Do you have to post / reply a minimum number of times to be
"noticed"/added onto the list?

-- Now back to the original topic --

The popup JS is great (love the filter / search feature).
It's a great system and, if used, I think it will bring MySQL users
together and help in creating site/regional specific MySQL
lists/conferences.


Thanks for listening!

J.R.

-Original Message-
From: Claire McLister [mailto:[EMAIL PROTECTED]
Sent: Monday, October 24, 2005 5:19 PM
To: mysql@lists.mysql.com
Subject: Map of MySQL Users

Hi,

  We've developed an automatic email mapping capability from Google
Maps API.

  To try it out, we mapped origins of emails to this group from
October
3 through October 14th.

The result of this map is at:
http://www.zeesource.net/maps/map.do?group=460

  Would like to hear what you think of it.

  Best wishes

Claire

  --
  Claire McLister                        [EMAIL PROTECTED]
  1684 Nightingale Avenue     Suite 201
  Sunnyvale, CA 94087        408-733-2737(fax)

http://www.zeemaps.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: Map of MySQL Users

[Claire McLister]

We do allow manual modifications to maps, through separate passwords 
for group moderators, members, and viewers.


In our case, I have set the group password to 'mysqlmap'. Please feel 
free to add your entries to the map. This password will only allow you 
to add entries, or to modify existing entries. Currently we do not 
support modifying entry locations, so if you really want to change an 
entry, you will have to delete the existing entry.


I've added a legend of 'red' => automatic, and 'blue' => manual. So, if 
you add your entry manually, please make its color blue.


Let me know if this level of password management is not sufficient.

Thanks for your encouragement.

Claire


On Oct 24, 2005, at 4:58 PM, J.R. Bullington wrote:


Thank you for your email.

It seems as though you are getting rave reviews from the list. I am 
sure
that if you want to make this really neat, try creating a simple web 
login

that allows for you to create an account using the email address, then
update your location so that it can be a little more accurate.

As stated in some of the posts, a lot of times your lat/long service 
may not
be entirely right as it is basing the lat/long off the mail carrier, 
not

necessarily the location of the user.

J.R.

PS -- Listers, sorry I didn't CC the list on my first correspondence. 
Here

it is with her answers.

GREAT JOB CLAIRE & COMPANY!

-Original Message-
From: Claire McLister [mailto:[EMAIL PROTECTED]
Sent: Monday, October 24, 2005 6:24 PM
To: [EMAIL PROTECTED]
Subject: Re: Map of MySQL Users

We use the origin IP and then convert it to a latitude/longitude using 
a
commercial service. There are several services available these days to 
do

this conversion.

We use any email posted to the list for which we can get  a decent IP 
value.
It could be from a registered MySQL user or not. Even a single post 
should
get you on the map. The only thing is a special email address needs to 
be
added to the list so that we can get location information from each 
email.


Thanks for your feedback. Yes, I think adding a geographical dimension 
to

the email lists brings up interesting possibilities.

Let me know if you want to see maps of other email lists.

Claire


On Oct 24, 2005, at 3:05 PM, J.R. Bullington wrote:


Couple of questions for you --

What are you using to keep pinpoint the origin of the submitted 
emails?

PING, TRACERT, WHOIS off the domain names?

Are you only doing original posts, or original & replies?
Do they need to be registered users of mySQL or just post to the list?
Do you have to post / reply a minimum number of times to be
"noticed"/added onto the list?

-- Now back to the original topic --

The popup JS is great (love the filter / search feature).
It's a great system and, if used, I think it will bring MySQL users
together and help in creating site/regional specific MySQL
lists/conferences.


Thanks for listening!

J.R.

-Original Message-
From: Claire McLister [mailto:[EMAIL PROTECTED]
Sent: Monday, October 24, 2005 5:19 PM
To: mysql@lists.mysql.com
Subject: Map of MySQL Users

Hi,

  We've developed an automatic email mapping capability from Google
Maps API.

  To try it out, we mapped origins of emails to this group from
October
3 through October 14th.

The result of this map is at:
http://www.zeesource.net/maps/map.do?group=460

  Would like to hear what you think of it.

  Best wishes

Claire

  --
  Claire McLister                        [EMAIL PROTECTED]
  1684 Nightingale Avenue     Suite 201
  Sunnyvale, CA 94087        408-733-2737(fax)

http://www.zeemaps.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: Map of MySQL Users

[Peter J Milanese]

This is particularly troublesome when it come to isp accounts. I.E., all aol 
users showing Reston as their base.

I tried something like this using whois databases and had the same issues. I 
canned the idea for now.

P

-
Sent from my NYPL BlackBerry Handheld.


- Original Message -
From: "J.R. Bullington" [EMAIL PROTECTED]
Sent: 10/24/2005 07:58 PM
To: "'Claire McLister'" <[EMAIL PROTECTED]>
Cc: 
Subject: RE: Map of MySQL Users

Thank you for your email.

It seems as though you are getting rave reviews from the list. I am sure
that if you want to make this really neat, try creating a simple web login
that allows for you to create an account using the email address, then
update your location so that it can be a little more accurate.

As stated in some of the posts, a lot of times your lat/long service may not
be entirely right as it is basing the lat/long off the mail carrier, not
necessarily the location of the user.

J.R.

PS -- Listers, sorry I didn't CC the list on my first correspondence. Here
it is with her answers.

GREAT JOB CLAIRE & COMPANY!

-Original Message-
From: Claire McLister [mailto:[EMAIL PROTECTED]
Sent: Monday, October 24, 2005 6:24 PM
To: [EMAIL PROTECTED]
Subject: Re: Map of MySQL Users

We use the origin IP and then convert it to a latitude/longitude using a
commercial service. There are several services available these days to do
this conversion.

We use any email posted to the list for which we can get  a decent IP value.
It could be from a registered MySQL user or not. Even a single post should
get you on the map. The only thing is a special email address needs to be
added to the list so that we can get location information from each email.

Thanks for your feedback. Yes, I think adding a geographical dimension to
the email lists brings up interesting possibilities.

Let me know if you want to see maps of other email lists.

Claire


On Oct 24, 2005, at 3:05 PM, J.R. Bullington wrote:

> Couple of questions for you --
>
> What are you using to keep pinpoint the origin of the submitted emails?
> PING, TRACERT, WHOIS off the domain names?
>
> Are you only doing original posts, or original & replies?
> Do they need to be registered users of mySQL or just post to the list?
> Do you have to post / reply a minimum number of times to be
> "noticed"/added onto the list?
>
> -- Now back to the original topic --
>
> The popup JS is great (love the filter / search feature).
> It's a great system and, if used, I think it will bring MySQL users
> together and help in creating site/regional specific MySQL
> lists/conferences.
>
>
> Thanks for listening!
>
> J.R.
>
> -Original Message-
> From: Claire McLister [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 24, 2005 5:19 PM
> To: mysql@lists.mysql.com
> Subject: Map of MySQL Users
>
> Hi,
>
>   We've developed an automatic email mapping capability from Google
> Maps API.
>
>   To try it out, we mapped origins of emails to this group from
> October
> 3 through October 14th.
>
> The result of this map is at:
> http://www.zeesource.net/maps/map.do?group=460
>
>   Would like to hear what you think of it.
>
>   Best wishes
>
> Claire
>
>   --
>   Claire McLister                        [EMAIL PROTECTED]
>   1684 Nightingale Avenue     Suite 201
>   Sunnyvale, CA 94087        408-733-2737(fax)
>
>   http://www.zeemaps.com
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>

RE: Map of MySQL Users

[J.R. Bullington]

Thank you for your email.

It seems as though you are getting rave reviews from the list. I am sure
that if you want to make this really neat, try creating a simple web login
that allows for you to create an account using the email address, then
update your location so that it can be a little more accurate.

As stated in some of the posts, a lot of times your lat/long service may not
be entirely right as it is basing the lat/long off the mail carrier, not
necessarily the location of the user.

J.R.

PS -- Listers, sorry I didn't CC the list on my first correspondence. Here
it is with her answers.

GREAT JOB CLAIRE & COMPANY!

-Original Message-
From: Claire McLister [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 24, 2005 6:24 PM
To: [EMAIL PROTECTED]
Subject: Re: Map of MySQL Users

We use the origin IP and then convert it to a latitude/longitude using a
commercial service. There are several services available these days to do
this conversion.

We use any email posted to the list for which we can get  a decent IP value.
It could be from a registered MySQL user or not. Even a single post should
get you on the map. The only thing is a special email address needs to be
added to the list so that we can get location information from each email.

Thanks for your feedback. Yes, I think adding a geographical dimension to
the email lists brings up interesting possibilities.

Let me know if you want to see maps of other email lists.

Claire


On Oct 24, 2005, at 3:05 PM, J.R. Bullington wrote:

> Couple of questions for you --
>
> What are you using to keep pinpoint the origin of the submitted emails?
> PING, TRACERT, WHOIS off the domain names?
>
> Are you only doing original posts, or original & replies?
> Do they need to be registered users of mySQL or just post to the list?
> Do you have to post / reply a minimum number of times to be 
> "noticed"/added onto the list?
>
> -- Now back to the original topic --
>
> The popup JS is great (love the filter / search feature).
> It's a great system and, if used, I think it will bring MySQL users 
> together and help in creating site/regional specific MySQL 
> lists/conferences.
>
>
> Thanks for listening!
>
> J.R.
>
> -Original Message-
> From: Claire McLister [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 24, 2005 5:19 PM
> To: mysql@lists.mysql.com
> Subject: Map of MySQL Users
>
> Hi,
>
>   We've developed an automatic email mapping capability from Google 
> Maps API.
>
>   To try it out, we mapped origins of emails to this group from 
> October
> 3 through October 14th.
>
> The result of this map is at:
> http://www.zeesource.net/maps/map.do?group=460
>
>   Would like to hear what you think of it.
>
>   Best wishes
>
> Claire
>
>   --
>   Claire McLister                        [EMAIL PROTECTED]
>   1684 Nightingale Avenue     Suite 201
>   Sunnyvale, CA 94087        408-733-2737(fax)
>
>   http://www.zeemaps.com
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>



smime.p7s
Description: S/MIME cryptographic signature

Re: Map of MySQL Users

[Jasper Bryant-Greene]

On Mon, 2005-10-24 at 14:19 -0700, Claire McLister wrote:
>   We've developed an automatic email mapping capability from Google Maps 
> API.
> 
>   To try it out, we mapped origins of emails to this group from October 
> 3 through October 14th.
> 
> The result of this map is at:  
> http://www.zeesource.net/maps/map.do?group=460
> 
>   Would like to hear what you think of it.

Very cool. You got me on the wrong island (I'm in Christchurch, South
Island, New Zealand; not Wellington, North Island, New Zealand) but
still very interesting.

How exactly are you finding the geographical location of email senders?

-- 
Jasper Bryant-Greene
General Manager
Album Limited

e: [EMAIL PROTECTED]
w: http://www.album.co.nz/
p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303
a: PO Box 579, Christchurch 8015, New Zealand


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

Re: mysql command line error

[Dustin Krysak]

I got it figured out.. of all things it was a line return. DOH!

Dustin


On 24-Oct-05, at 12:14 PM, Dustin Krysak wrote:

Sorry - but to add to this, the command is actually in a bash  
script. I suspect it is the single quotes and or the "()"  
characters


Dustin


On 24-Oct-05, at 11:57 AM, Dustin Krysak wrote:


Hi there - I am trying to issue the following command in a  
terminal window... I know my syntax is slightly off, however I can  
not see what... it seems to be due to the OLD_PASSWORD function...  
any pointers?




mysql -u${DB_NAME} -p${DB_PASSWORD} -e"GRANT select, update,  
insert, delete ON $DBNAME.* TO [EMAIL PROTECTED] IDENTIFIED BY  
\"$DBPASS\";SET PASSWORD FOR [EMAIL PROTECTED] = OLD_PASSWORD 
('$DBPASS');flush privileges;"


Dustin



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










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

Mysql Processes

[Edward David]

I just joined this list so I am hoping that this question is relevant to 
this group.

I am running Linux AS4 Enterprise Server.
I am running Mysql  Ver 14.7 Distrib 4.1.12, for pc-linux-gnu (i686) 
using readline 4.3

With Client version 3.23.49.
When I go and do a ps -ax I see 18 mysqld processes running.
When I look at the pid file I only see one Process ID Number.

When I look at another server that is running mysql that we do not have 
access to the cnf file

I only see two processes running.
Can anyone explain why this is happening and if it is normal.

---
Edward David
Sr. Systems Analyst
University of Calgary
Information Resources
Information Technology Service
---


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

RE: Map of MySQL Users

[Logan, David (SST - Adelaide)]

Hi Claire,

I thought Daniel Kasak was in Sydney? It is about 2000kms from Alice Springs 
8-) Apart from that, very good.

Regards

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

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


-Original Message-
From: Claire McLister [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 25 October 2005 6:49 AM
To: mysql@lists.mysql.com
Subject: Map of MySQL Users

Hi,

  We've developed an automatic email mapping capability from Google Maps 
API.

  To try it out, we mapped origins of emails to this group from October 
3 through October 14th.

The result of this map is at:  
http://www.zeesource.net/maps/map.do?group=460

  Would like to hear what you think of it.

  Best wishes

Claire

  --
  Claire McLister                        [EMAIL PROTECTED]
  1684 Nightingale Avenue     Suite 201
  Sunnyvale, CA 94087        408-733-2737(fax)

http://www.zeemaps.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: Map of MySQL Users

[Brian Dunning]

That's awesome! I love it. Even though it didn't include me...  :(

How are you doing the geotargeting?

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

LASSO TIPS for MYSQL: 3.3 NEAR MAGICAL INLINES

[m i l e s]

   - Hi and Welcome to -


  LASSO TIPS FOR NEWBIES: 3.3


I'm your host, M i l e s.

First and foremost, a good place for you to start with Lasso is the  
following 5 things:


The FIRST LASSO TIPS FOR NEWBIES
-> http://www.listsearch.com/lassotalk.lasso?id=143312
The LAST LASSO TIPS FOR NEWBIES
-> http://www.listsearch.com/lassotalk.lasso?id=154555
10 LASSO RESOURCES
-> http://www.listsearch.com/lassotalk.lasso?id=143018
THE LASSO RESOURCES ADDENDUM
-> http://www.listsearch.com/lassotalk.lasso?id=143417
OMNIPILOT RESOURCE LIST
-> http://www.omnipilot.com/Resources+for+Beginners.2225.lasso
   http://www.omnipilot.com/Tip+of+the+Week.1768.lasso
   http://www.omnipilot.com/Hosting+Providers.1744.lasso
   http://www.omnipilot.com/Frequently+Asked+Questions.1791.lasso


   TODAYS TIP:
THE INLINE THAT CHANGED MY LIFE


I'll never forget the afternoon that I web enabled my very first  
FileMakerPro db.  The Lasso install took less than 2 minutes.  Once  
installed it was just open the database, restart WebStar, then write  
my page and run it.  I was skeptical at first because these other  
languages and scripts (tango, webfm, coldfusion) had to do x, y and z  
and what not...s complicated.  But I pressed on and wrote 5 lines  
of code.  An Inline, A records statement.  A field tag.  And then a  
closing records and inline tag.  Then saved the document, and pulled  
the url & hit enter.  I wasn't expecting anything after having tried  
and failing with Tango & WebFM I was to say the least, appathetic.   
The browser immediately returned my page to me.  It was like the  
clouds parted.  Magic!  Just FIVE lines of code.  5 lines!!!  There  
was my data!  I was in awe.  I sat there stunned for several minutes  
giddy at the thought of what I had just done.  I then ran it over  
again...and kept looking at the code and was just stunned at what I  
had just done.  It was just 5 lines, but its 5 lines that would  
change my life.  The dreams of data avarice quickly began seeping  
into my lil Mileslike brain and I saw bigger and better things to do  
with my new found toy...errr 'skill'.  I wanted to see what else it  
and I could doAnd my Life has never been the same since.


PART ONE: THE BASIC INLINE.

  For those of you that have been living under a rock for the  
last 8 years, the real power of Lasso aside from its simplicity and  
ease of use, and its ace in the whole that sets it apart from other  
scripting languages is the INLINE:


Your 'basic' inline consists of three parts.  The tag (1), name  
value pairs (2), and commands (3):


EX: [inline (1): -database='dbname', -table='tbname', -op='eq',  
'fieldname'='somevalue' (2), -search (3)]


We all use this basic structure on a daily basis in some form or  
another.  In the 8 years that Ive been using Lasso this basic  
structure hasn't changed all that much.  A quick review of an inline  
reveals that with it, you can invoke a query against a named  
datasource and its table/layout, add records to said datasource/ 
tables, display said values from said query, or update values already  
in said datasource/tables, and/or delete them.  We all know this to  
be true.  And this is Lasso's true power I believe, what you can do  
in a single inline, will take you 10 to 50 lines in any other  
languagebeat that with a stick PHP/.NET/COLDFUSION!


 ++
   TO SEE THE REST OF THIS LASSO TIP
 ++

 THE CURRENT LASSO TIP for MYSQL:

 http://www.listsearch.com/lassotalk.lasso?id=154859

 THE LAST 5 LASSO TIPS for MYSQL:

 LTƒM 3.2: If I Only Had A ->> http://www.listsearch.com/ 
lassotalk.lasso?id=154176
 LTƒM 3.1: Three Lil Toys ->> http://www.listsearch.com/ 
lassotalk.lasso?id=154555
 LTƒM 3.0: The Lasso App ->> http://www.listsearch.com/ 
lassotalk.lasso?id=153881
 LTƒM 2.9: The Login Routine ->> http://www.listsearch.com/ 
lassotalk.lasso?id=153613
 LTƒM 2.8: SQL and Lasso (pt2) ->>  http://www.listsearch.com/ 
lassotalk.lasso?id=149158


M i l e s

  $LASSO->MYSQL CONSULTANT & EVANGELIST FOR HIRE
  -don't know what 'LASSO' is ? - http://www.omnipilot.com/

M i l e s  [EMAIL PROTECTED]
Featuring: ToolboxEngine  - A Custom CMS(415) 686 - 6164
http://www.lassoconsultant.com/   AIM/Yahoo/MSN:  magikmiles

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

Re: Map of MySQL Users

[Robert L Cochran]

This is awesome! You missed me by 3 days. It looks like you can see the 
exact street and house that an email originated from. I like the mapping 
capability very much.


Bob Cochran


Claire McLister wrote:


Hi,

 We've developed an automatic email mapping capability from Google 
Maps API.


 To try it out, we mapped origins of emails to this group from October 
3 through October 14th.


The result of this map is at:  
http://www.zeesource.net/maps/map.do?group=460


 Would like to hear what you think of it.

 Best wishes

Claire

 --
 Claire McLister[EMAIL PROTECTED]
 1684 Nightingale Avenue Suite 201
 Sunnyvale, CA 94087408-733-2737(fax)

http://www.zeemaps.com






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

Help on nested categories query

[pedro mpa]

Hi!

I need help on implementing a query that performs a search for items under
nested categories. An example is on ebay search where you can restrict your
search by selecting a subcategory and you only get items under that
category/subcategories.
Do I have to first get a list of all subcategories under the selected
category and then loop OR's or is there a better way?

Some example tables:

| id | pid | category |
  1 0cat1
  2 0cat2
  3 1cat3
  4 1cat4
  5 2cat5

| id | id_category | item |
  13 item1
  24 item2
  35 item3


(Running Mysql 4.1.9 and PHP 5.0.4).
Thanks in advance.
Apologies for my bad English.
Pedro.




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

Re: How Can I upgrade TPC-C performance test result for mysql

[Heikki Tuuri]

George,

can you please post a few typical

SHOW INNODB STATUS\G

outputs during the stress test.

Best regards,

Heikki
Oracle/Innobase

- Original Message - 
From: "yang george" <[EMAIL PROTECTED]>

Newsgroups: mailing.database.myodbc
Sent: Monday, October 24, 2005 1:50 PM
Subject: How Can I upgrade TPC-C performance test result for mysql



dear sir:
we develop a TPC-C test progrom,this program use mysql ODBC3.51 to
connect mysql server, and use microsoft sqlserver odbc to connect
sqlserver2000.
we suppose you are know well the TPC-C.

we use  InnoDB as mysql's store engine.
follow is the os and hardware information:
=09os : Windows advance server 2000
=09CPU: 2X3.0Ghz
=09memory:2G
=09disk: only one 40G IDE disk

table struct:
=09for one warehouse,we stat all table list:
=09table name=09|  record numbers |=09type record size(bytes)=09|  type 
tab=

le
size(K bytes)
=09  Warehouse=09  |   1=09  |89=09  | 
0.0=

89
   District=09  |   10=09  |95=09  | 
0.95=

0
   Customer=09  |   30k=09|655=09  | 
1965=

0
   History=09|   30k=09|46=09  |  1380
   Order=09  |   30k=09|24=09  |  720
   New-Order=09  |   9k=09  |8=09|  72
   Order-Line=09|   300k=09|54=09  | 
1620=

0
   Stock=09  |   100k=09|306=09  | 
30=

600
   Item=09  |   100k=09|82=09  |  8200

=09SQL sentence:
=09create  table warehouse ( w_id int not null, w_name varchar(10) null,
w_street_1 varchar(20) null, w_street_2 varchar(20) null, w_city
varchar(20) null, w_state char(2) null, w_zip char(9) null, w_tax
float null, w_ytd float null, primary key(w_id) );
=09create  table district ( d_id int not null, d_w_id int not null,
d_name varchar(10) null, d_street_1 varchar(20) null, d_street_2
varchar(20) null, d_city varchar(20) null, d_state char(2) null, d_zip
char(9) null, d_tax float null, d_ytd float null, d_next_o_id int
null, primary key(d_w_id, d_id),foreign key(d_w_id)references
warehouse(w_id) );
=09create  table customer ( c_id int not null, c_d_id int not null,
c_w_id int not null, c_first varchar(16) null, c_middle char(2) null,
c_last varchar(16) null, c_street_1 varchar(20) null, c_street_2
varchar(20) null, c_city varchar(20) null, c_state char(2) null, c_zip
char(9) null, c_phone char(16) null, c_since timestamp null, c_credit
char(2) null, c_credit_lim float null, c_discount float null,
c_balance float null, c_ytd_payment float null, c_payment_cnt int
null, c_delivery_cnt int null, c_data varchar(500) null, primary
key(c_w_id, c_d_id, c_id),foreign key(c_w_id,c_d_id) references
district(d_w_id,d_id) );
=09create  table history ( h_c_id int null, h_c_d_id int null, h_c_w_id
int null, h_d_id int null, h_w_id int null, h_date timestamp null,
h_amount float null, h_data varchar(24) null,foreign
key(h_c_w_id,h_c_d_id,h_c_id) references
customer(c_w_id,c_d_id,c_id),foreign key(h_w_id,h_d_id)references
district(d_w_id,d_id) );
=09create  table orders ( o_id int not null, o_d_id int not null, o_w_id
int not null, o_c_id int null, o_entry_d timestamp null, o_carrier_id
int null, o_ol_cnt int null, o_all_local int null, primary key(o_w_id,
o_d_id, o_id),foreign key(o_w_id,o_d_id,o_c_id)references
customer(c_w_id,c_d_id,c_id) );
=09create  table new_order ( no_o_id int not null, no_d_id int not null,
no_w_id int not null, primary key(no_w_id, no_d_id, no_o_id),foreign
key(no_w_id,no_d_id,no_o_id)references orders(o_w_id,o_d_id,o_id) );
=09create  table item ( i_id int not null, i_im_id int null, i_name
varchar(24) null, i_price float null, i_data varchar(50) null, primary
key(i_id) );
=09create  table stock ( s_i_id int not null, s_w_id int not null,
s_quantity int null, s_dist_01 varchar(24) null, s_dist_02 varchar(24)
null, s_dist_03 varchar(24) null, s_dist_04 varchar(24) null,
s_dist_05 varchar(24) null, s_dist_06 varchar(24) null, s_dist_07
varchar(24) null, s_dist_08 varchar(24) null, s_dist_09 varchar(24)
null, s_dist_10 varchar(24) null, s_ytd int null, s_order_cnt int
null, s_remote_cnt int null, s_data varchar(50) null, primary
key(s_w_id, s_i_id),foreign key(s_w_id)references
warehouse(w_id),foreign key(s_i_id)references item(i_id) );
=09create  table order_line ( ol_o_id  int not null, ol_d_id int not
null, ol_w_id  int not null, ol_number int not null, ol_i_id int null,
ol_supply_w_id int null, ol_delivery_d timestamp null, ol_quantity int
null, ol_amount float null, ol_dist_info char(24) null, primary
key(ol_w_id, ol_d_id, ol_o_id, ol_number),foreign
key(ol_w_id,ol_d_id,ol_o_id) references
orders(o_w_id,o_d_id,o_id),foreign
key(ol_supply_w_id,ol_i_id)references stock(s_w_id,s_i_id) );
=09
=09index:
=09create index i_orders on orders (o_w_id, o_d_id, o_c_id, o_id);
=09create index i_customer on customer (c_w_id, c_d_id, c_last, c_first, 
c

Can conditions be mixed with DISTINCT()

[Scott Haneda]

tSql = "SELECT DISTINCT(p.ship_status)
FROM products AS p
INNER JOIN  cart AS i
ON i.product_id = p.id
WHERE i.session_id = " & prepSQL(tConn, tSessionID);

p.ship_status is either a "1" or a "0", which is just how the database was
set up ages, ago.  I am moving these to enum() types as I go, but to change
this one, would break too much stuff.

I would like to toss in a condition to the select so it returns
"shipping_now" for "1" and "shipping_soon" for "2".  When I do this, I get
zero results returned.
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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

Re: Optimal index for date range query with order by using index for sort???

[sheeri kritzer]

Is the DATE field a timestamp column?

What's your schema?  What's your primary key?

I ask this because the real question is, "is it safe to assume that
new entries are for the current day it is inserted?"

If the answer to that question is yes, you can use an id field (or an
existing one) to find the FIRST and LAST record of the day, and get
all the id's in between.

Of course, that's based on a generic idea I have of what you're trying
to do.  If you find the first entry of the day you're looking for
(SELECT * FROM PRODUCT WHERE DATE LIKE '2005-10-24%' LIMIT 1;) and the
first entry for the next day (SELECT * FROM PRODUCT WHERE DATE LIKE
'2005-10-25%' LIMIT 1;) and find all the entries in between (this is
helpful if you use an ID index).

Also, I'm guessing here that the "help" you need is that your query
takes too long. . .you do not actually state why you need help, so I'm
just putting all my assumptions out there.

-Sheeri

On 10/24/05, Kevin Burton <[EMAIL PROTECTED]> wrote:
>
> OK.
>
> I need help with the following query:
>
> SELECT * FROM PRODUCT WHERE DATE > ? ORDER BY PRICE;
>
> Basically find products created since a given date and order by prices.
>
> I could put an index of DATE, PRICE but it will have to resort to a
> filesort since DATE isn't a constant value.
>
> I was thinking of using a DAY column so that I can just find values
> in the last day.
>
> Then I could rewrite it as:
>
> SELECT * FROM PRODUCT WHERE DAY = ? ORDER BY PRICE;
>
> and place an index on DAY, PRICE at which point I'd be able to order
> by the index.
>
> Which would work really well.
>
> The problem is that at midnight there would be no results since DAY
> isn't really a floating window.
>
> I could use DAY IN { ? ? } and then ORDER BY PRICE but it would have
> to use a filesort again.
>
> Is there any interesting way people have solved this problem in the
> past?
>
> Kevin
>
>
> Kevin A. Burton, Location - San Francisco, CA
>AIM/YIM - sfburtonator,  Web - http://www.feedblog.org/
> GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04
>
>
>
> --
> 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]

Optimal index for date range query with order by using index for sort???

[Kevin Burton]

OK.

I need help with the following query:

SELECT * FROM PRODUCT WHERE DATE > ? ORDER BY PRICE;

Basically find products created since a given date and order by prices.

I could put an index of DATE, PRICE but it will have to resort to a  
filesort since DATE isn't a constant value.


I was thinking of using a DAY column so that I can just find values  
in the last day.


Then I could rewrite it as:

SELECT * FROM PRODUCT WHERE DAY = ? ORDER BY PRICE;

and place an index on DAY, PRICE at which point I'd be able to order  
by the index.


Which would work really well.

The problem is that at midnight there would be no results since DAY  
isn't really a floating window.


I could use DAY IN { ? ? } and then ORDER BY PRICE but it would have  
to use a filesort again.


Is there any interesting way people have solved this problem in the  
past?


Kevin


Kevin A. Burton, Location - San Francisco, CA
  AIM/YIM - sfburtonator,  Web - http://www.feedblog.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04



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

Map of MySQL Users

[Claire McLister]

Hi,

 We've developed an automatic email mapping capability from Google Maps 
API.


 To try it out, we mapped origins of emails to this group from October 
3 through October 14th.


The result of this map is at:  
http://www.zeesource.net/maps/map.do?group=460


 Would like to hear what you think of it.

 Best wishes

Claire

 --
 Claire McLister                        [EMAIL PROTECTED]
 1684 Nightingale Avenue     Suite 201
 Sunnyvale, CA 94087        408-733-2737(fax)

http://www.zeemaps.com



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

RE: mysql command line error

[Logan, David (SST - Adelaide)]

Hi Dustin,

If this is the actual syntax as you currently have it, the single quotes
around the second occurrence of $DBPASS (in the function OLD_PASSWORD)
will be causing lots of grief to bash. You are passing literally
'$DBPASS' to the function rather than the value of the variable. Try
using double quotes.

Regards

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

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


-Original Message-
From: Dustin Krysak [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 25 October 2005 4:27 AM
To: mysql@lists.mysql.com
Subject: mysql command line error

Hi there - I am trying to issue the following command in a terminal  
window... I know my syntax is slightly off, however I can not see  
what... it seems to be due to the OLD_PASSWORD function... any pointers?



mysql -u${DB_NAME} -p${DB_PASSWORD} -e"GRANT select, update, insert,  
delete ON $DBNAME.* TO [EMAIL PROTECTED] IDENTIFIED BY \"$DBPASS 
\";SET PASSWORD FOR [EMAIL PROTECTED] = OLD_PASSWORD 
('$DBPASS');flush privileges;"

Dustin



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


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

Re: mysql command line error

[SGreen]

Dustin Krysak <[EMAIL PROTECTED]> wrote on 10/24/2005 
03:14:02 PM:

> Sorry - but to add to this, the command is actually in a bash script. 
> I suspect it is the single quotes and or the "()" characters
> 
> Dustin
> 
> 
> On 24-Oct-05, at 11:57 AM, Dustin Krysak wrote:
> 
> > Hi there - I am trying to issue the following command in a terminal 
> > window... I know my syntax is slightly off, however I can not see 
> > what... it seems to be due to the OLD_PASSWORD function... any 
> > pointers?
> >
> >
> >
> > mysql -u${DB_NAME} -p${DB_PASSWORD} -e"GRANT select, update, 
> > insert, delete ON $DBNAME.* TO [EMAIL PROTECTED] IDENTIFIED BY 
> > \"$DBPASS\";SET PASSWORD FOR [EMAIL PROTECTED] = OLD_PASSWORD 
> > ('$DBPASS');flush privileges;"
> >
> > Dustin
> >

What's the actual error you are getting? Can you somehow view the string 
you are sending to the server? I don't know BASH at all or I could suggest 
something to let you see it. You may need to add more single quotes around 
[EMAIL PROTECTED] 

As I said, without knowing the precise error message or knowing what your 
final string would look like, I am just shooting in the dark.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: mysql command line error

[Dustin Krysak]

Sorry - but to add to this, the command is actually in a bash script.  
I suspect it is the single quotes and or the "()" characters


Dustin


On 24-Oct-05, at 11:57 AM, Dustin Krysak wrote:

Hi there - I am trying to issue the following command in a terminal  
window... I know my syntax is slightly off, however I can not see  
what... it seems to be due to the OLD_PASSWORD function... any  
pointers?




mysql -u${DB_NAME} -p${DB_PASSWORD} -e"GRANT select, update,  
insert, delete ON $DBNAME.* TO [EMAIL PROTECTED] IDENTIFIED BY  
\"$DBPASS\";SET PASSWORD FOR [EMAIL PROTECTED] = OLD_PASSWORD 
('$DBPASS');flush privileges;"


Dustin



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






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

mysql command line error

[Dustin Krysak]

Hi there - I am trying to issue the following command in a terminal  
window... I know my syntax is slightly off, however I can not see  
what... it seems to be due to the OLD_PASSWORD function... any pointers?




mysql -u${DB_NAME} -p${DB_PASSWORD} -e"GRANT select, update, insert,  
delete ON $DBNAME.* TO [EMAIL PROTECTED] IDENTIFIED BY \"$DBPASS 
\";SET PASSWORD FOR [EMAIL PROTECTED] = OLD_PASSWORD 
('$DBPASS');flush privileges;"


Dustin



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

Re: Temp table doesn't seem to work

[SGreen]

Brian Dunning <[EMAIL PROTECTED]> wrote on 10/24/2005 01:32:43 PM:

> I got it to work - turns out it was a stupid typo on my part (hate it 
> when that happens - and hate wasting the list's time even more!). How 
> long does this temporary table persist for - just the execution of 
> the one page, or will it live on the server (using resources) until I 
> explicitly make it go away?
> 

Temp tables and user variables are connection specific. They will be there 
until you explicitly get rid of them OR your connection closes. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Temp table doesn't seem to work

[Brian Dunning]

I got it to work - turns out it was a stupid typo on my part (hate it  
when that happens - and hate wasting the list's time even more!). How  
long does this temporary table persist for - just the execution of  
the one page, or will it live on the server (using resources) until I  
explicitly make it go away?


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

Re: multiple commands to mysql from regular command line

[sheeri kritzer]

> If you just need to select a database, you can do it while connecting to 
> mysql:
>
> mysql -u${DB_USER} -p${DB_PASSWORD} ${DATABASE} -e 'update foo set bar=0'
>
> Although the other suggestions, of using semicolons or scripts, work
> as well.  If it's just a one-liner, though, the above will work.
>
> -Sheeri
>
> On 10/21/05, Dustin Krysak <[EMAIL PROTECTED]> wrote:
> > Hi there, I was wondering if it was possible to issue multiple
> > commands to mysql from a regular command line? For example i know you
> > can issue a single by the following:
> >
> >
> > mysql -u${DB_USER} -p${DB_PASSWORD} -e 'show databases'
> >
> > Now what if for example i needed to select a database, then run an
> > update against it? With a single command it exits back out to a
> > shell, so there is no chance to issue a 2nd command.
> >
> > Thanks in advance!
> >
> > Dustin
> >
> > --
> > 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: sum of time?

[jabbott]

On Mon, 24 Oct 2005 [EMAIL PROTECTED] wrote:

> "Martijn Tonies" <[EMAIL PROTECTED]> wrote on 10/24/2005 10:16:21 AM:
> 
> > 
> > 
> > > Michael Stassen wrote:
> > > 
> > > > Second, no, it won't overflow:
> > > >
> > > >   mysql> SELECT SEC_TO_TIME(60*60*24*5);
> > > >   +-+
> > > >   | SEC_TO_TIME(60*60*24*5) |
> > > >   +-+
> > > >   | 120:00:00   |
> > > >   +-+
> > > >   1 row in set (0.00 sec)
> > > >
> > > >   mysql> SELECT SEC_TO_TIME(60*60*24*50);
> > > >   +--+
> > > >   | SEC_TO_TIME(60*60*24*50) |
> > > >   +--+
> > > >   | 1200:00:00   |
> > > >   +--+
> > > >   1 row in set (0.00 sec)
> > > >
> > > > SEC_TO_TIME() is not limited to 24 hours.
> > > >
> > >
> > > I should have added that the limits of a TIME column are documented in 
> the
> > > manual :
> > >
> > >TIME values may range from '-838:59:59' to '838:59:59'. The reason 
> for
> > >which the hours part may be so large is that the TIME type may be 
> used
> > >not only to represent a time of day (which must be less than 24 
> hours),
> > >but elapsed time or a time interval between two events as well. 
> (Note
> > >that this interval may be much greater than 24 hours, or even
> > negative.)
> > 
> > That's actually a very weird definition for a TIME datatype :-)
> > 
> > It should have an "interval" datatype for such operations.
> > 
> > > So some care may be needed if you will be storing the result, because
> > > SEC_TO_TIME() can return a time outside of a TIME column's allowable
> > range.
> > 
> > With regards,
> > 
> > Martijn Tonies
> > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS 
> SQL
> > Server
> > Upscene Productions
> > http://www.upscene.com
> > Database development questions? Check the forum!
> > http://www.databasedevelopmentforum.com
> > 
> 
> The "interval" datatype? I don't see "interval" as an option for MySQL. 
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html. Yes, 
> "interval" fields are defined as part of SQL2003 but MySQL doesn't have 
> them (yet) which is probably why the TIME datatype has such a wide range.
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine

Hey I have another question.  If I was running MySQL 5, would this be a great 
thing to create as a view?  That way I could just send the userID as a select 
for the hours and get them back?  Or would this be a waste as it is easy to get 
with a query anyway?  If so, when should I use a view?

--ja
-- 


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

Re: sum of time?

[SGreen]

"Martijn Tonies" <[EMAIL PROTECTED]> wrote on 10/24/2005 10:16:21 AM:

> 
> 
> > Michael Stassen wrote:
> > 
> > > Second, no, it won't overflow:
> > >
> > >   mysql> SELECT SEC_TO_TIME(60*60*24*5);
> > >   +-+
> > >   | SEC_TO_TIME(60*60*24*5) |
> > >   +-+
> > >   | 120:00:00   |
> > >   +-+
> > >   1 row in set (0.00 sec)
> > >
> > >   mysql> SELECT SEC_TO_TIME(60*60*24*50);
> > >   +--+
> > >   | SEC_TO_TIME(60*60*24*50) |
> > >   +--+
> > >   | 1200:00:00   |
> > >   +--+
> > >   1 row in set (0.00 sec)
> > >
> > > SEC_TO_TIME() is not limited to 24 hours.
> > >
> >
> > I should have added that the limits of a TIME column are documented in 
the
> > manual :
> >
> >TIME values may range from '-838:59:59' to '838:59:59'. The reason 
for
> >which the hours part may be so large is that the TIME type may be 
used
> >not only to represent a time of day (which must be less than 24 
hours),
> >but elapsed time or a time interval between two events as well. 
(Note
> >that this interval may be much greater than 24 hours, or even
> negative.)
> 
> That's actually a very weird definition for a TIME datatype :-)
> 
> It should have an "interval" datatype for such operations.
> 
> > So some care may be needed if you will be storing the result, because
> > SEC_TO_TIME() can return a time outside of a TIME column's allowable
> range.
> 
> With regards,
> 
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS 
SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
> 

The "interval" datatype? I don't see "interval" as an option for MySQL. 
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html. Yes, 
"interval" fields are defined as part of SQL2003 but MySQL doesn't have 
them (yet) which is probably why the TIME datatype has such a wide range.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

[ANN] DBManager Professional 3.1.2 Released

[DBTools Software]

We are pleased to announce the release of the new DBTools Manager
Professional. This version is a bug fix with small new features, see below:

- Fixed: Dialog Box Preferences for Data Options
- Fixed: 'Invalid DateTime' on datetime columns with NULL values in
Microsoft Access
- Fixed: SetFieldNull In MSAccess (Generated an Exception)
- Fixed: Find Files Button to Attach Databases didn't work for SQL Server
- Fixed: Parser error in query editor for MS Access
- Fixed: Entering Integer Size (Adding new Columns) Show an error message in
loop
- Fixed: GO Not working on Datasheet to separate multiple queries
- Added FROM to Send Emails in the Task Builder
- Implemented Picture Viewer to Query Editor
- Implemented Picture Viewer to Form Builder
- Implemented double click to insert tables in Query Editor
- Implemented Export/Import Diagram Data in Diagram Designer

The Enterprise Edition 3.1.2 and Freeware Edition 3.1.1 are both available
for downloading in the Downloads Center at
http://www.dbtools.com.br/EN/downloads.

What Is DBTools Manager?

DBTools Manager is a Windows application for database management. It
supports MySQL (3, 4 and 5), PostgreSQL (6, 7, 8), Interbase, Firebird,
SQLite, Xbase, MSAccess, MSSQL Server/MSDE, Oracle, Sybase and ODBC
Datasources. The main features are:

- Management of all database objects including: database, tables, index,
foreign keys, check constraints, triggers, domains, stored procedures, user
defined functions, etc
- Lots of wizards to import and export data, including: MSAccess, ODBC,
MSExcel, Text files formatted and CSV, HTML, XML, Paradox, Clipper, Dbase,
FoxPro (*)
- Wizard to create php script (*)
- Database Comparerto syncronize database structure (*)
- Query Editor with support for debugging, planning, multiple results sets,
image viewer and query builder to visually create your queries
- Diagram Designer (*)
- Report and Form builder (*)
- Objects Browser to view objects dependencies (*)
- Task Builder to automate process of moving, importing and exporting data
and structure (*)
- Server, Database and Table Monitor shows graphics with the status of the
selected server and database (*)
- And much more

(*) Enterprise Edition only

The full list of features can be found at
http://www.dbtools.com.br/EN/dbmanagerpro/features.php

SPECIAL OFFER

In October and November/2005 we are offering a special price for the
Enterprise Edition for a single database engine. The price list starting at
US$ 50.00 makes it affordable by everyone. To see the full price list check
http://www.dbtools.com.br/EN/prices.php. You can download a TRIAL with full
features enabled and try it for 20 days.


Best Regards,

DBTools Software
http://www.dbtools.com.br


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

Re: sum of time?

[Martijn Tonies]

> Michael Stassen wrote:
> 
> > Second, no, it won't overflow:
> >
> >   mysql> SELECT SEC_TO_TIME(60*60*24*5);
> >   +-+
> >   | SEC_TO_TIME(60*60*24*5) |
> >   +-+
> >   | 120:00:00   |
> >   +-+
> >   1 row in set (0.00 sec)
> >
> >   mysql> SELECT SEC_TO_TIME(60*60*24*50);
> >   +--+
> >   | SEC_TO_TIME(60*60*24*50) |
> >   +--+
> >   | 1200:00:00   |
> >   +--+
> >   1 row in set (0.00 sec)
> >
> > SEC_TO_TIME() is not limited to 24 hours.
> >
>
> I should have added that the limits of a TIME column are documented in the
> manual :
>
>TIME values may range from '-838:59:59' to '838:59:59'. The reason for
>which the hours part may be so large is that the TIME type may be used
>not only to represent a time of day (which must be less than 24 hours),
>but elapsed time or a time interval between two events as well. (Note
>that this interval may be much greater than 24 hours, or even
negative.)

That's actually a very weird definition for a TIME datatype :-)

It should have an "interval" datatype for such operations.

> So some care may be needed if you will be storing the result, because
> SEC_TO_TIME() can return a time outside of a TIME column's allowable
range.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Re: sum of time?

[Martijn Tonies]

> >>This translated to SQL as:
> >>
> >>select SEC_to_time(SUM(unix_timestamp(TTendTime)) -
> >>SUM(unix_timestamp(TTstartTime)))
> >>as endtime
> >>FROM TimeTracking
> >>WHERE TTperson = 1
> >>and date(TTstartTime) = '2005-10-19'
> >
> > Won't this fail if it overflows 24 hours?
> >
> > Given that the result of SEC_TO_TIME seems to be a valid "time value",
> > which "duration" is not?
> >
> > eg: a duration can be 25 hours long, while a TIME value cannot.
>
> First, it is hard to imagine how that would happen given the OP's
situation.
>
> Second, no, it won't overflow:
>
>mysql> SELECT SEC_TO_TIME(60*60*24*5);
>+-+
>| SEC_TO_TIME(60*60*24*5) |
>+-+
>| 120:00:00   |
>+-+
>1 row in set (0.00 sec)
>
>mysql> SELECT SEC_TO_TIME(60*60*24*50);
>+--+
>| SEC_TO_TIME(60*60*24*50) |
>+--+
>| 1200:00:00   |
>+--+
>1 row in set (0.00 sec)
>
> SEC_TO_TIME() is not limited to 24 hours.

Aha, right - thanks for that.

It seems to be a display error on my side :-)

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Re: sum of time?

[Michael Stassen]

Michael Stassen wrote:


Second, no, it won't overflow:

  mysql> SELECT SEC_TO_TIME(60*60*24*5);
  +-+
  | SEC_TO_TIME(60*60*24*5) |
  +-+
  | 120:00:00   |
  +-+
  1 row in set (0.00 sec)

  mysql> SELECT SEC_TO_TIME(60*60*24*50);
  +--+
  | SEC_TO_TIME(60*60*24*50) |
  +--+
  | 1200:00:00   |
  +--+
  1 row in set (0.00 sec)

SEC_TO_TIME() is not limited to 24 hours.



I should have added that the limits of a TIME column are documented in the 
manual :


  TIME values may range from '-838:59:59' to '838:59:59'. The reason for
  which the hours part may be so large is that the TIME type may be used
  not only to represent a time of day (which must be less than 24 hours),
  but elapsed time or a time interval between two events as well. (Note
  that this interval may be much greater than 24 hours, or even negative.)

So some care may be needed if you will be storing the result, because 
SEC_TO_TIME() can return a time outside of a TIME column's allowable range.


Michael

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

Fw: column type problem

[Szmutku Zoltán]

Hi ,

More column type problem ...The MySql  Text fields arrived as General (not 
readable) field in VFP via ODBC... 

By: Zoltan

- Original Message - 
From: Szmutku Zoltán 
To: mysql@lists.mysql.com 
Sent: Monday, October 24, 2005 10:36 AM
Subject: column type problem 


Hi , 

I using MySql from Visual Foxpro cross over ODBC 3.51 . 
When I run a SELECT statement then the DECIMAL 15,0  MySql field arrived to 
Foxpro as Currency type with 4 digits decimals! 
The data not lost (15 digits long) but ,the decimals set to 4 and column type 
is Currency . 

I must always use the MTON() Foxpro function for convert data to numeric data 
type. 
That is my problem... 

By: Zoltan

Re: Selecting more than one property (req help)

[Jigal van Hemert]

mem bob wrote:

| id | model | service_id |
||---||
| 1 | 500 | 1 |
| 2 | 500 | 3 |
| 3 | 500 | 10 |
| 4 | 600 | 1 |


From this table i want to extract all distinct models which have

service_id=1 *AND* service_id=3


The JOIN-construction is something like:

SELECT `model` FROM `i` AS t1 JOIN `i` AS t2 ON t1.`model` = t2.`model` 
AND t1.`id` != t2.`id` WHERE t1.`service_id` = 1 AND t2.`service_id` = 3 
GROUP BY t1.`model`;


Experiment to see whether it makes a difference of not if you move the 
expressions in the WHERE clause to the ON clause of the JOIN. It seems 
to depend on the version of MySQL that you are using how good the 
optimizer is at finding out the fastest execution path.


Make sure that you have an index on at least `model` and perhaps a 
combined index on `model`, `id` and/or `service_id`. Experiment with 
indexes and see which combination is the fastest.


Look at the output from EXPLAIN  to see the type of table joins 
MySQL will use and how many records it estimates it needs to evaluate in 
each step.


Regards, Jigal.

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

Re: Selecting more than one property (req help)

[SGreen]

mem bob <[EMAIL PROTECTED]> wrote on 10/24/2005 09:45:30 AM:

> Hi all,
> 
> Can anyone throw some suggestions at me for this problem?
> 
> | id | model | service_id |
> ||---||
> | 1 | 500 | 1 |
> | 2 | 500 | 3 |
> | 3 | 500 | 10 |
> | 4 | 600 | 1 |
> 
> 
> From this table i want to extract all distinct models which have
> service_id=1 *AND* service_id=3
> 
> What's the best possible (speed) solution for this - I have a lot of 
records
> to juggle about!
> 
> I have tried inner joining it within it's self but this method could get
> rather complicated.
> I have also tried making a count of service_id's captured and HAVING 
count
> == 2 but this query seems slow.
> 
> Any suggestions or pointers would be great!
> 
> Thanks
> 
> Membob


You have already mentioned the two most popular techniques. The self-join 
is more complicated to write and does not scale well to more than 2 or 3 
search terms but it can be quite fast (depending on your index coverage). 
The COUNT() ... HAVING  techinique is more flexible (scales better, too) 
but can take more time to compute (also based on your index coverage).

How may different sets of terms do you need to test? If you are going to 
be running a lot of different lists of service_ids through a query, you 
probably ought to stick with the COUNT()...HAVING techniqe but this time 
add an index of (model, service_id) so that you no longer need to query 
the actual table.

ALTER TABLE yourtablename ADD KEY(model, service_id);

You can drop the index after your ad-hoc analysis (or not, choice is 
always yours) however it will make both techniques faster.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: sum of time?

[Michael Stassen]

Martijn Tonies wrote:

I have a table doing time tracking.  I don't use timestamps, I use
datetime fields to record punch in times and punch out times.  I
have this query that computes the amount of time between a punch in
and punch out:

select SEC_to_time(unix_timestamp(TTendTime) -


unix_timestamp(TTstartTime))


as endtime
FROM TimeTracking
WHERE TTperson = 1
   and date(TTstartTime) = '2005-10-19'

And this works great except for when people punch in and out several
times in one day.  Is there any way I can total a number of records
into one total time?  In this example case, I am TTperson #1 and I
punched in and out five times this day.

I know I can do it in the code, but if I can do it in sql, life
would be better for me.

--ja

--


Let's do some basic algebra:

et = end time
st = start time

Total time = (et1 - st1) + (et2 - st2) + ... + (etN - stN)
Where N = how many clock-ins and clock outs they had.

We can rewrite this as

TT = et1 - st1 + et2 - st2 + ... etN - stN
   = et1 + et2 + ... etN - st1 - st2 - ... - stN
   = et1 + et2 + ... etN - (st1 + st2 + ... + stN)
   = SUM(et(1..N))-SUM(st(1..n))

This translated to SQL as:

select SEC_to_time(SUM(unix_timestamp(TTendTime)) -
SUM(unix_timestamp(TTstartTime)))
as endtime
FROM TimeTracking
WHERE TTperson = 1
   and date(TTstartTime) = '2005-10-19'


Won't this fail if it overflows 24 hours?

Given that the result of SEC_TO_TIME seems to be a valid "time value",
which "duration" is not?

eg: a duration can be 25 hours long, while a TIME value cannot.


First, it is hard to imagine how that would happen given the OP's situation.

Second, no, it won't overflow:

  mysql> SELECT SEC_TO_TIME(60*60*24*5);
  +-+
  | SEC_TO_TIME(60*60*24*5) |
  +-+
  | 120:00:00   |
  +-+
  1 row in set (0.00 sec)

  mysql> SELECT SEC_TO_TIME(60*60*24*50);
  +--+
  | SEC_TO_TIME(60*60*24*50) |
  +--+
  | 1200:00:00   |
  +--+
  1 row in set (0.00 sec)

SEC_TO_TIME() is not limited to 24 hours.

Michael

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

MySQL 5.0.15 has been released

[Joerg Bruehe]

Hi,


MySQL 5.0.15, a new version of the popular Open Source Database
Management System, has been released. The Community Edition is now
available in source and binary form for a number of platforms from our
download pages at
http://dev.mysql.com/downloads/ and mirror sites.


Note that not all mirror sites may be up to date at this point in time -
if you can't find this version on some mirror, please try again later or
choose another download site.


This is the first production release of the 5.0 release family, which 
offers substantial new features. For further information, please see:

* the separate email to [EMAIL PROTECTED] with the header
  "Announcing MySQL 5.0"
* the web page with the major new features:
  http://dev.mysql.com/doc/mysql/en/MySQL_5.0_Nutshell.html


Please pay special attention to the manual, section 2.10.2, "Upgrading
from Version 4.1 to 5.0.", and to the additional notes at the end of
this announcement.


MySQL has worked very hard to ensure a high level of quality, but as it
is starting a new release family, we recommend that you protect your 
data by making a backup before migrating to it.



Please refer to our bug database at http://bugs.mysql.com/ for more
details about the individual bugs fixed in this version.



Functionality added or changed:

* Warning: Incompatible change. The CHAR() function now returns a binary
   string rather than a string in the connection character set. An
   optional USING charset clause may be used to produce a result in a
   specific character set instead. Also, arguments larger than 256
   produce multiple characters. They are no longer interpreted modulo
   256 to produce a single character each. These changes may cause some
   incompatibilities, as noted in Section 2.10.2, "Upgrading from
   Version 4.1 to 5.0."
* NDBCluster: The perror utility included with the MySQL-Server RPM now
   provides support for the --ndb option, and so can be used to obtain
   error  message  text  for  MySQL  Cluster error codes. (Bug #13740
   (http://bugs.mysql.com/13740))
* When executing single-table UPDATE or DELETE queries containing an
   ORDER BY ... LIMIT N clause, but not having any WHERE clause, MySQL
   can now take advantage of an index to read the first N rows in the
   ordering specified in the query. If an index is used, only the first
   N records will be read, as opposed to scanning the entire table. (Bug
   #12915 (http://bugs.mysql.com/12915))
* The MySQL-server RPM now explicitly assigns the mysql system user to
   the mysql user group during the postinstallation process. This
   corrects an issue with upgrading the server on some Linux
   distributions whereby a previously existing mysql user was not
   changed to the mysql group, resulting in wrong groups for files
   created following the installation. (Bug #12823
   (http://bugs.mysql.com/12823))
* Added  the  --tz-utc  option  to  mysqldump.  This option adds SET
   TIME_ZONE='+00:00' to the dump file so that TIMESTAMP columns can be
   dumped  and  reloaded  between servers in different time zones and
   protected  from  changes  due to daylight saving time. (Bug #13052
   (http://bugs.mysql.com/13052))
* When declaring a local variable (or parameter) named password or name,
   and setting it with SET, e.g. SET password = '', the new error
   message: ERROR 42000: Variable 'nnn' must be quoted with `...`, or
   renamed is returned (where 'nnn' is 'password' or 'names'). This
   means there is a syntax conflict with special sentences like SET
   PASSWORD = PASSWORD(...) (for setting a user's password) and set
   names default (for setting charset and collation).
   This  must  be  resolved  either by quoting the variable name: SET
   `password` = ..., which will set the local variable `password`, or by
   renaming the variable to something else (if setting the user's
   password is the desired effect).
* The following statements now cause an implicit COMMIT:
  + CREATE VIEW
  + ALTER VIEW
  + DROP VIEW
  + CREATE TRIGGER
  + DROP TRIGGER
  + CREATE USER
  + RENAME USER
  + DROP USER
* NDBCluster:  A  number of new or improved error messages have been
   implemented in this release in order to provide better and more
   accurate diagnostic information regarding cluster configuration
   issues and problems.  (Bug  #11739  (http://bugs.mysql.com/11739),
   Bug #11749 (http://bugs.mysql.com/11749),
   Bug #12044 (http://bugs.mysql.com/12044),
   Bug #12786 (http://bugs.mysql.com/12786),
   Bug #13197 (http://bugs.mysql.com/13197))
* NDBCluster: A new "smart" node allocation algorithm means that it is
   no longer necessary to use sequential IDs for cluster nodes, and that
   nodes not explicitly assigned IDs should now have IDs allocated
   automatically in most cases. In practical terms, this means that it
   is now possible to assign a set of node IDs such as 1, 2, 4, 5
   without an error being generated due to the missing 3.  (Bug #13009
   (http://bu

Selecting more than one property (req help)

[mem bob]

Hi all,

Can anyone throw some suggestions at me for this problem?

| id | model | service_id |
||---||
| 1 | 500 | 1 |
| 2 | 500 | 3 |
| 3 | 500 | 10 |
| 4 | 600 | 1 |


>From this table i want to extract all distinct models which have
service_id=1 *AND* service_id=3

What's the best possible (speed) solution for this - I have a lot of records
to juggle about!

I have tried inner joining it within it's self but this method could get
rather complicated.
I have also tried making a count of service_id's captured and HAVING count
== 2 but this query seems slow.

Any suggestions or pointers would be great!

Thanks

Membob

Re: sum of time?

[Martijn Tonies]

> > I have a table doing time tracking.  I don't use timestamps, I use
> > datetime fields to record punch in times and punch out times.  I
> > have this query that computes the amount of time between a punch in
> > and punch out:
> >
> > select SEC_to_time(unix_timestamp(TTendTime) -
> unix_timestamp(TTstartTime))
> > as endtime
> > FROM TimeTracking
> > WHERE TTperson = 1
> > and date(TTstartTime) = '2005-10-19'
> >
> > And this works great except for when people punch in and out several
> > times in one day.  Is there any way I can total a number of records
> > into one total time?  In this example case, I am TTperson #1 and I
> > punched in and out five times this day.
> >
> > I know I can do it in the code, but if I can do it in sql, life
> > would be better for me.
> >
> > --ja
> >
> > -- 
>
> Let's do some basic algebra:
>
> et = end time
> st = start time
>
> Total time = (et1 - st1) + (et2 - st2) + ... + (etN - stN)
> Where N = how many clock-ins and clock outs they had.
>
> We can rewrite this as
>
> TT = et1 - st1 + et2 - st2 + ... etN - stN
> = et1 + et2 + ... etN - st1 - st2 - ... - stN
> = et1 + et2 + ... etN - (st1 + st2 + ... + stN)
> = SUM(et(1..N))-SUM(st(1..n))
>
> This translated to SQL as:
>
> select SEC_to_time(SUM(unix_timestamp(TTendTime)) -
> SUM(unix_timestamp(TTstartTime)))
> as endtime
> FROM TimeTracking
> WHERE TTperson = 1
> and date(TTstartTime) = '2005-10-19'
>

Won't this fail if it overflows 24 hours?

Given that the result of SEC_TO_TIME seems to be a valid "time value",
which "duration" is not?

eg: a duration can be 25 hours long, while a TIME value cannot.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Re: sum of time?

[SGreen]

<[EMAIL PROTECTED]> wrote on 10/24/2005 12:48:32 AM:

> 
> I have a table doing time tracking.  I don't use timestamps, I use 
> datetime fields to record punch in times and punch out times.  I 
> have this query that computes the amount of time between a punch in 
> and punch out:
> 
> select SEC_to_time(unix_timestamp(TTendTime) - 
unix_timestamp(TTstartTime))
> as endtime
> FROM TimeTracking
> WHERE TTperson = 1
> and date(TTstartTime) = '2005-10-19'
> 
> And this works great except for when people punch in and out several
> times in one day.  Is there any way I can total a number of records 
> into one total time?  In this example case, I am TTperson #1 and I 
> punched in and out five times this day.
> 
> I know I can do it in the code, but if I can do it in sql, life 
> would be better for me.
> 
> --ja
> 
> -- 

Let's do some basic algebra:

et = end time
st = start time

Total time = (et1 - st1) + (et2 - st2) + ... + (etN - stN)
Where N = how many clock-ins and clock outs they had.

We can rewrite this as

TT = et1 - st1 + et2 - st2 + ... etN - stN 
= et1 + et2 + ... etN - st1 - st2 - ... - stN
= et1 + et2 + ... etN - (st1 + st2 + ... + stN)
= SUM(et(1..N))-SUM(st(1..n))

This translated to SQL as:

select SEC_to_time(SUM(unix_timestamp(TTendTime)) - 
SUM(unix_timestamp(TTstartTime)))
as endtime
FROM TimeTracking
WHERE TTperson = 1
and date(TTstartTime) = '2005-10-19'


The only issue will be if there is a mismatch between # of clock-ins and # 
of clock-outs (person is currently on the clock or they checked out on the 
following day, perhaps). That's something you are going to need to build a 
little logic to handle but this should help you get started.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: sum of time?

[Dobromir Velev]

I think there should be no problem to use the SUM() function - did you tried 
it like this

select SEC_to_time(SUM(unix_timestamp(TTendTime) - 
unix_timestamp(TTstartTime))) as endtime
FROM TimeTracking
WHERE TTperson = 1  and date(TTstartTime) = '2005-10-19'

HTH

-- 
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/

On Monday 24 October 2005 07:48, [EMAIL PROTECTED] wrote:
> I have a table doing time tracking.  I don't use timestamps, I use datetime
> fields to record punch in times and punch out times.  I have this query
> that computes the amount of time between a punch in and punch out:
>
> select SEC_to_time(unix_timestamp(TTendTime) - unix_timestamp(TTstartTime))
> as endtime
> FROM TimeTracking
> WHERE TTperson = 1
> and date(TTstartTime) = '2005-10-19'
>
> And this works great except for when people punch in and out several times
> in one day.  Is there any way I can total a number of records into one
> total time?  In this example case, I am TTperson #1 and I punched in and
> out five times this day.
>
> I know I can do it in the code, but if I can do it in sql, life would be
> better for me.
>
> --ja
>
> --

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

Re: Not operator in Select statements

[Dotan Cohen]

On 10/24/05, Jasper Bryant-Greene <[EMAIL PROTECTED]> wrote:
> On Mon, 2005-10-24 at 06:09 +0200, Dotan Cohen wrote:
> > How do I use a not operator in the WHERE clause? The obvious != and
> > NOT didn't work for me. Something along the lines of:
> > $query = "SELECT album, year FROM albums WHERE year!=1990 ORDER BY year 
> > ASC";
>
> The above query is syntactically correct. If it didn't work for you,
> then your problem lies elsewhere. Perhaps post your error message?
>
> By the way, your question would have been answered by reading the
> manual...
>
> Jasper Bryant-Greene

Thank you Jasper. I remember from the manual that this _should_be
correct. But when it doesn't work I am inclinded to ask. I will go try
to track down the cause of my disappointment with the query. Thank
you.

Dotan Cohen
http://lyricslist.com/lyrics/artist_albums/332/mccartney_paul.php
McCartney, Paul Song Lyrics

Re: String insertion

[Andreas Steichardt]

On Monday 24 October 2005 12:00, Bruce Martin wrote:
> Can you force it to keep the space by escaping the space?
> Something like:
>
> mysql> insert into test_strings set foo_1=' test\ ',foo_2=' test\
> ',foo_3=' test\
> ',foo_4=' test\ ';

no...doesn't work...guess i will have to use text :|


Kind regards,

 Andreas Streichardt

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

How Can I upgrade TPC-C performance test result for mysql

[yang george]

dear sir:
we develop a TPC-C test progrom,this program use mysql ODBC3.51 to
connect mysql server, and use microsoft sqlserver odbc to connect
sqlserver2000.
we suppose you are know well the TPC-C.

we use  InnoDB as mysql's store engine.
follow is the os and hardware information:
os : Windows advance server 2000
CPU: 2X3.0Ghz
memory:2G
disk: only one 40G IDE disk

table struct:
for one warehouse,we stat all table list:
table name  |  record numbers | type record size(bytes) |  type 
table
size(K bytes)
  Warehouse   |   1   |89 | 
 0.089
District  |   10  |95 | 
 0.950
Customer  |   30k   |655  | 
 19650
History |   30k |46   |  1380
Order |   30k   |24   |  720
New-Order |   9k  |8|   
   72
Order-Line  |   300k|54   |  16200
Stock |   100k  |306  | 
 30600
Item  |   100k  |82   |  8200

SQL sentence:
create  table warehouse ( w_id int not null, w_name varchar(10) null,
w_street_1 varchar(20) null, w_street_2 varchar(20) null, w_city
varchar(20) null, w_state char(2) null, w_zip char(9) null, w_tax
float null, w_ytd float null, primary key(w_id) );
create  table district ( d_id int not null, d_w_id int not null,
d_name varchar(10) null, d_street_1 varchar(20) null, d_street_2
varchar(20) null, d_city varchar(20) null, d_state char(2) null, d_zip
char(9) null, d_tax float null, d_ytd float null, d_next_o_id int
null, primary key(d_w_id, d_id),foreign key(d_w_id)references
warehouse(w_id) );
create  table customer ( c_id int not null, c_d_id int not null,
c_w_id int not null, c_first varchar(16) null, c_middle char(2) null,
c_last varchar(16) null, c_street_1 varchar(20) null, c_street_2
varchar(20) null, c_city varchar(20) null, c_state char(2) null, c_zip
char(9) null, c_phone char(16) null, c_since timestamp null, c_credit
char(2) null, c_credit_lim float null, c_discount float null,
c_balance float null, c_ytd_payment float null, c_payment_cnt int
null, c_delivery_cnt int null, c_data varchar(500) null, primary
key(c_w_id, c_d_id, c_id),foreign key(c_w_id,c_d_id) references
district(d_w_id,d_id) );
create  table history ( h_c_id int null, h_c_d_id int null, h_c_w_id
int null, h_d_id int null, h_w_id int null, h_date timestamp null,
h_amount float null, h_data varchar(24) null,foreign
key(h_c_w_id,h_c_d_id,h_c_id) references
customer(c_w_id,c_d_id,c_id),foreign key(h_w_id,h_d_id)references
district(d_w_id,d_id) );
create  table orders ( o_id int not null, o_d_id int not null, o_w_id
int not null, o_c_id int null, o_entry_d timestamp null, o_carrier_id
int null, o_ol_cnt int null, o_all_local int null, primary key(o_w_id,
o_d_id, o_id),foreign key(o_w_id,o_d_id,o_c_id)references
customer(c_w_id,c_d_id,c_id) );
create  table new_order ( no_o_id int not null, no_d_id int not null,
no_w_id int not null, primary key(no_w_id, no_d_id, no_o_id),foreign
key(no_w_id,no_d_id,no_o_id)references orders(o_w_id,o_d_id,o_id) );
create  table item ( i_id int not null, i_im_id int null, i_name
varchar(24) null, i_price float null, i_data varchar(50) null, primary
key(i_id) );
create  table stock ( s_i_id int not null, s_w_id int not null,
s_quantity int null, s_dist_01 varchar(24) null, s_dist_02 varchar(24)
null, s_dist_03 varchar(24) null, s_dist_04 varchar(24) null,
s_dist_05 varchar(24) null, s_dist_06 varchar(24) null, s_dist_07
varchar(24) null, s_dist_08 varchar(24) null, s_dist_09 varchar(24)
null, s_dist_10 varchar(24) null, s_ytd int null, s_order_cnt int
null, s_remote_cnt int null, s_data varchar(50) null, primary
key(s_w_id, s_i_id),foreign key(s_w_id)references
warehouse(w_id),foreign key(s_i_id)references item(i_id) );
create  table order_line ( ol_o_id  int not null, ol_d_id int not
null, ol_w_id  int not null, ol_number int not null, ol_i_id int null,
ol_supply_w_id int null, ol_delivery_d timestamp null, ol_quantity int
null, ol_amount float null, ol_dist_info char(24) null, primary
key(ol_w_id, ol_d_id, ol_o_id, ol_number),foreign
key(ol_w_id,ol_d_id,ol_o_id) references
orders(o_w_id,o_d_id,o_id),foreign
key(ol_supply_w_id,ol_i_id)references stock(s_w_id,s_i_id) );

index:
create index i_orders on orders (o_w_id, o_d_id, o_c_id, o_id);
create index i_customer on customer (c_w_id, c_d_id, c_last, c_first, 
c_id);


transaction information:
  transaction type  |  min percent(%) |   min keyboard time(sec)  |  
90% response time(sec)  |min think time(sec)
  

Re: source rpm for mysql 4.1.15

[Joerg Bruehe]

Hi Stever, all!


Stever wrote:

Is there any reason why there isn't a source rpm for mysql 4.1.15?


A tool problem we have not yet fixed - no intention.
I am not sure whether I can look into this today.

 I
need to compile my own version since (annoyingly), there doesn't seem to 
be a binary rpm version that works with glibc 2.2.5.


Just for curiosity: Which platform / distribution / ... is this? I 
thought our glibc2.2 packages should work?


Joerg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

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

Re: String insertion

[Bruce Martin]

Can you force it to keep the space by escaping the space?
Something like:

mysql> insert into test_strings set foo_1=' test\ ',foo_2=' test\ 
',foo_3=' test\

',foo_4=' test\ ';


On Oct 24, 2005, at 5:34 AM, Andreas Steichardt wrote:


Hi!

I just hit something really strange which is either a bug in MySQL or a
dumbness bug of me.

I am trying to insert a string ending with a simple space and i really 
want

this space at the end of my string ;). Unfortunately MySQL kills this
whitespace when inserting into normal (var)char columns:

mysql> create table test_strings (foo_1 varchar(255),foo_2 
char(255),foo_3

text,foo_4 blob);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_strings set foo_1=' test ',foo_2=' test 
',foo_3=' test

',foo_4=' test ';
Query OK, 1 row affected (0.00 sec)

mysql> select length(foo_1),length(foo_2),length(foo_3),length(foo_4) 
from

test_strings;
+---+---+---+---+
| length(foo_1) | length(foo_2) | length(foo_3) | length(foo_4) |
+---+---+---+---+
| 5 | 5 | 6 | 6 |
+---+---+---+---+
1 row in set (0.00 sec)

Is this a feature or am i missing something. text would do it for me 
but it is

a total waste of space.

Any ideas?

Kind regards,

 Andreas Streichardt

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



Bruce Martin
The Martin Solution
PO Box 644
Delaware Water Gap, PA
(570) 421-0670
[EMAIL PROTECTED]


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

Re: String insertion

[Alec . Cawley]

Andreas Steichardt <[EMAIL PROTECTED]> wrote on 24/10/2005 10:34:08:

> Hi!
> 
> I just hit something really strange which is either a bug in MySQL or a 
> dumbness bug of me.
> 
> I am trying to insert a string ending with a simple space and i really 
want 
> this space at the end of my string ;). Unfortunately MySQL kills this 
> whitespace when inserting into normal (var)char columns:
> 
> mysql> create table test_strings (foo_1 varchar(255),foo_2 
char(255),foo_3 
> text,foo_4 blob);
> Query OK, 0 rows affected (0.00 sec)
> 
> mysql> insert into test_strings set foo_1=' test ',foo_2=' test ',
> foo_3=' test 
> ',foo_4=' test ';
> Query OK, 1 row affected (0.00 sec)
> 
> mysql> select length(foo_1),length(foo_2),length(foo_3),length(foo_4) 
from 
> test_strings;
> +---+---+---+---+
> | length(foo_1) | length(foo_2) | length(foo_3) | length(foo_4) |
> +---+---+---+---+
> | 5 | 5 | 6 | 6 |
> +---+---+---+---+
> 1 row in set (0.00 sec)
> 
> Is this a feature or am i missing something. text would do it for mebut 
it is 
> a total waste of space.
> 
> Any ideas?

This is a "feature" of VARCHAR in MySQL V4 and before. It is fixed in 
5.0.3. The Manual ( http://dev.mysql.com/doc/refman/5.0/en/char.html ) 
suggests using BLOB or TEXT instead of VARCHAR to avoid this behaviour in 
earlier versions.

Alec




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

RE: failure notice

[Mevershosting.nl]

unscubcribe



-
Scanned for virus and spam


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

String insertion

[Andreas Steichardt]

Hi!

I just hit something really strange which is either a bug in MySQL or a 
dumbness bug of me.

I am trying to insert a string ending with a simple space and i really want 
this space at the end of my string ;). Unfortunately MySQL kills this 
whitespace when inserting into normal (var)char columns:

mysql> create table test_strings (foo_1 varchar(255),foo_2 char(255),foo_3 
text,foo_4 blob);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_strings set foo_1=' test ',foo_2=' test ',foo_3=' test 
',foo_4=' test ';
Query OK, 1 row affected (0.00 sec)

mysql> select length(foo_1),length(foo_2),length(foo_3),length(foo_4) from 
test_strings;
+---+---+---+---+
| length(foo_1) | length(foo_2) | length(foo_3) | length(foo_4) |
+---+---+---+---+
| 5 | 5 | 6 | 6 |
+---+---+---+---+
1 row in set (0.00 sec)

Is this a feature or am i missing something. text would do it for me but it is 
a total waste of space.

Any ideas?

Kind regards,

 Andreas Streichardt

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

column type problem

[Szmutku Zoltán]

Hi , 

I using MySql from Visual Foxpro cross over ODBC 3.51 . 
When I run a SELECT statement then the DECIMAL 15,0  MySql field arrived to 
Foxpro as Currency type with 4 digits decimals! 
The data not lost (15 digits long) but ,the decimals set to 4 and column type 
is Currency . 

I must always use the MTON() Foxpro function for convert data to numeric data 
type. 
That is my problem... 

By: Zoltan