mySQL Administrator 1.1.9 warning message

[Neil Tompkins]

I am running mySQL database version 3.23.58, but I'm using mySQL 
Administrator 1.1.9 for scheduled backups. When connecting to the database I 
get a message saying that this version of database is not supported and I 
might get unexpected behaviour.


Am I OK to use this version of mySQL Administrator to backup my database and 
what unexpected behaviour can I expect ?


Thanks
Neil



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

RE: mysqldump backup on filters

[Rithish Saralaya]

>Your table is missing. Try this:

?>mysqldump --single-transaction -u root clientdb 
--where="FLD_CLIENT_ID=1" > client1_dbbackup.sql

Yes. The tables are missing. That's because I want the backup of all the
tables in the db, and those tables that have the column FLD_CLIENT_ID, they
should be filtered by the where clause.

This is because, if we are to reproduce the system for a client, then all we
need to do is to run this backup on a new db.

Regards,
Rithish.


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

RE: create/restore database without binary logging

[Rithish Saralaya]

Thanks Sheeri.

Regards,
Rithish.

-Original Message-
From: sheeri kritzer [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 02, 2006 11:26 PM
To: Rithish Saralaya
Cc: MySQL general mailing list
Subject: Re: create/restore database without binary logging


Rithish,

It's possible to do this by turning off binary logging, restarting the
server, importing, turning on binary logging, and restarting.

-Sheeri

On 2/27/06, Rithish Saralaya <[EMAIL PROTECTED]> wrote:
> Hello.
>
> I was going to recreate a database of size 35 GB from sql dump file.
Wanted
> to know if it is possible to do it without mysql writing into the binary
> log. If yes, how?
>
> Regards,
> Rithish.
>
>


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

rpm mysql5.0 for fedora4

[unplug]

Hi,
  I found that there are 2 versions of mysql 5.0 rpm for download.

Linux x86 generic RPM (statically linked against glibc 2.2.5) downloads
Linux x86 generic RPM (dynamically linked) downloads

  What is the difference between them?  How to determine what version
should I use for fedora4?

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

Re: CONCAT() returns not correct character set

[Hirofumi Fujiwara]

Hi,

Is there any difference between _binary'Binary' and CONVERT('Binary' USING 
binary)


mysql> SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1   |
+--+
1 row in set (0.00 sec)
 
mysql> SELECT CHARSET(CONCAT(CONVERT('Bianry' USING binary),CONVERT('abc' USING 
latin1)));
+-+
| CHARSET(CONCAT(CONVERT('Bianry' USING binary),CONVERT('abc' USING latin1))) |
+-+
| binary  |
+-+
1 row in set (0.00 sec)

In Japan, we have to use many kinds of character set.
eucjpms,ujis for unix
cp932,sjis   for Windows
utf8 for Java, MySQL meta data
So, it's a very complicated world.

> Hi there,
> 
> I tried a few other queries:
> 
> first, confirm that what you think is a binary is indeed a binary:
> 
> mysql> SELECT CHARSET(_binary'Binary');
> +--+
> | CHARSET(_binary'Binary') |
> +--+
> | binary   |
> +--+
> 1 row in set (0.00 sec)
> 
> check the regular text (for completeness' sake)
> 
> mysql> select charset ('binary');
> ++
> | charset ('binary') |
> ++
> | latin1 |
> ++
> 1 row in set (0.00 sec)
> 
> OK, so concatenating the 2 should result in a binary according to the manual:
> 
> mysql> SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
> +--+
> | CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
> +--+
> | latin1   |
> +--+
> 1 row in set (0.00 sec)
> 
> Hrm, I got the same result you did.
> 
> However,
> 
> mysql> SELECT CHARSET(CONCAT(_binary'Binary','foo'));
> +--+
> | CHARSET(CONCAT((_binary'Binary'),'foo')) |
> +--+
> | binary   |
> +--+
> 1 row in set (0.00 sec)
> 
> works just fine.  Why are you converting the text to latin1?  It's
> already there.  That conversion seems to be messing things up.
> 
> Perhaps someone can explain why the conversion messes things up --
> seems like a bug to me.
> 
> -Sheeri
> 
> On 2/26/06, Hirofumi Fujiwara <[EMAIL PROTECTED]> wrote:
> > Dear  MySQL fans,
> >
> > I tested CONCAT() with binary strings and I got strange result.
> >
> > Manual says:
> > http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
> >
> > If the arguments include any binary strings, the result is a binary
> > string.
> >
> > But the following test says:
> >
> > bianry + latin1 > latin1 (not bianry)
> >
> > mysql> SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
> > +--+
> > | CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
> > +--+
> > | latin1   |
> > +--+
> > 1 row in set (0.00 sec)
> >
> > 
> > Hirofumi Fujiwara (Tokyo JAPAN)  enjoy JAVA and Puzzle World
> > [EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html
> > [EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/
> > 
> > My SUDOKU Probs  http://www.pro.or.jp/~fuji/sudoku/problems/
> > 
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> 

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

Building Client Program

[baynaa]

Hi all,

I am building client program on MySQL 4.0.16 with C API. But I always get
error like:

 

make  all-recursive

gcc -I. -I/usr/home/vpopmail/include  -I/usr/local/include/mysql -g -O2
-c user.c

user.c: In function `delalert':

user.c:357: syntax error before `*'

user.c:358: `conn' undeclared (first use in this function)

user.c:358: (Each undeclared identifier is reported only once

user.c:358: for each function it appears in.)

user.c:360: syntax error before `*'

user.c:365: `res_set' undeclared (first use in this function)

user.c:366: syntax error before `unsigned'

user.c:367: `numrows' undeclared (first use in this function)

user.c:372: `row' undeclared (first use in this function)

user.c:382: syntax error before `char'

user.c:384: `token' undeclared (first use in this function)

.

 

Here is my piece of code:

 

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

.

MYSQL *conn;

conn = mysql_init(NULL);

 
mysql_real_connect(conn,host,username,password,database,0,NULL,0);

MYSQL_RES *res_set;

MYSQL_ROW row;

unsigned int i;

sprintf(mysqlquery,"SELECT fromDomain,fromAddress FROM user
WHERE userName=\'[EMAIL PROTECTED]'",Username,Domain);

mysql_query(conn,mysqlquery);

res_set = mysql_store_result(conn);

.

 

 

 

Can anyone tell me, how can I compile this code.

 

BR, Baynaa.

Re: Can't select to outfile

[unplug]

You have to enable the file_permission for the user in order to create file.

On 3/3/06, Bruce Bales <[EMAIL PROTECTED]> wrote:
> I'm using mysql 3.23.58 on linux.  I recently upgraded from RedHat 9 to Fedora
> 3 and now I am unable to write to an outfile.
>
> If I have started mysql with the simple "mysql"  I get 'access denied' even if
> I am trying to put it into my home directory.
>
> mysql> select lname,fname,mname,addr,city,stzip from 1950class3 into outfile
> '/usr/class3-2';
> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
> mysql>
>
> If I have started with "mysql -u root -p"  and then a password, I get 'Can't
> create file.'
>
> mysql> select lname, fname, addr,city,stzip from 1950class2 into outfile
> '/usr/database3-2';
> ERROR 1: Can't create/write to file '/usr/database3-2' (Errcode: 13)
> mysql>
>
> bruce has all privileges on the table.
> How can I get my data out?
> bruce
>
> --
> 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]

Can't select to outfile

[Bruce Bales]

I'm using mysql 3.23.58 on linux.  I recently upgraded from RedHat 9 to Fedora 
3 and now I am unable to write to an outfile.

If I have started mysql with the simple "mysql"  I get 'access denied' even if 
I am trying to put it into my home directory.

mysql> select lname,fname,mname,addr,city,stzip from 1950class3 into outfile 
'/usr/class3-2';
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
mysql>
 
If I have started with "mysql -u root -p"  and then a password, I get 'Can't 
create file.'

mysql> select lname, fname, addr,city,stzip from 1950class2 into outfile 
'/usr/database3-2';
ERROR 1: Can't create/write to file '/usr/database3-2' (Errcode: 13) 
mysql>

bruce has all privileges on the table.
How can I get my data out?
bruce

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

Re: MySQL and Hyperthreading

[Hiro Yoshioka]

Hi Atle,

We are using Asianux 2.0 (RHEL 4 compatible, linux kernel 2.6.9)
MySQL is the latest one. 5.0.x

Thanks in advance,
  Hiro

From: Atle Veka <[EMAIL PROTECTED]>
Subject: Re: MySQL and Hyperthreading
Date: Thu, 2 Mar 2006 10:12:09 -0800 (PST)
Message-ID: <[EMAIL PROTECTED]>

> Hi Hiro, we have noticed severe performance loss with HT enabled under
> FreeBSD but not with MySQL. However, we could be having problems with
> MySQL as well without knowing as we haven't done any testing as it is
> operating fine. What operating system are you running?
> 
> We now disable HT on all servers wherever possible..
> 
> 
> Atle
> -
> Flying Crocodile Inc, Unix Systems Administrator
> 
> On Thu, 2 Mar 2006, Hiro Yoshioka wrote:
> 
> > Hi,
> >
> > We found a severe performance degradation when Hyperthreading is on
> > and thread_concurrency=20.
> >
> > We are using OSDL DBT-1 as the benchmark and got about 200 to 250
> > BT (bogotransactions per second) HT is OFF normal case but 30 to
> > 50 BT on HT is ON.
> >
> > innodb_thread_concurrency=20
> >
> > So we did profile (using oprofile tool) and got the following profiling
> > data. My impression is that mutex_spin_wait (and ut_delay) is
> > something wrong if HT is ON. (Spin-wait loop is too expensive if it is
> > hyperthreading.)
> >
> > I added the following code but it does not help it.
> >
> > $ diff -pu ut0ut.c.orig ut0ut.c
> > --- ut0ut.c.orig2005-10-17 10:27:43.0 +0900
> > +++ ut0ut.c 2006-02-28 11:59:16.777840496 +0900
> > @@ -290,6 +290,13 @@ ut_delay(
> > j = 0;
> >
> > for (i = 0; i < delay * 50; i++) {
> > +   /* When executing a spin-wait loop on the Hyper-Threading
> > +  processor, the processor can suffer a severe performance
> > +   penalty. The pause instruction provides a hint to the
> > +   processor. Please refer IA-32 Intel Architecture
> > +   Software Developers Manual, Vol 3.   */
> > +   __asm__ __volatile__(
> > +   "pause; \n");
> > j += i;
> > }
> >
> > What do you think? Is there any hints?
> >
> > HT is OFF
> > CPU: P4 / Xeon, speed 2793.26 MHz (estimated)
> > Counted GLOBAL_POWER_EVENTS events (time during which processor is not 
> > stopped) with a unit mask of 0x01 (mandatory)
> > count 10
> > samples  %image name   app name symbol 
> > name
> > 13159082  8.8445  libc-2.3.4.solibc-2.3.4.somemcpy
> > 12565549  8.4456  libpthread-2.3.4.so  libpthread-2.3.4.so  
> > pthread_mutex_trylock
> > 11387363  7.6537  mysqld   mysqld   
> > rec_get_offsets_func
> > 9631916   6.4738  libpthread-2.3.4.so  libpthread-2.3.4.so  
> > pthread_mutex_unlock
> > 8794484   5.9110  mysqld   mysqld   
> > btr_search_guess_on_hash
> > 4949248   3.3265  mysqld   mysqld   
> > row_search_for_mysql
> > 4022481   2.7036  mysqld   mysqld   ut_delay
> > 3754265   2.5233  mysqld   mysqld   
> > cmp_dtuple_rec_with_match
> > 2535190   1.7040  mysqld   mysqld   
> > row_sel_store_mysql_rec
> > 2520957   1.6944  mysqld   mysqld   
> > btr_cur_search_to_nth_level
> >
> > HT is ON
> > CPU: P4 / Xeon with 2 hyper-threads, speed 2793.26 MHz (estimated)
> > Counted GLOBAL_POWER_EVENTS events (time during which processor is not 
> > stopped) with a unit mask of 0x01 (mandatory)
> > count 10
> > samples  %image name   app name symbol 
> > name
> > 53221317 21.4225  libpthread-2.3.4.so  libpthread-2.3.4.so  
> > pthread_mutex_lock
> > 25743323 10.3621  mysqld   mysqld   ut_delay
> > 12345146  4.9691  vmlinux  vmlinux  do_futex
> > 12066038  4.8568  mysqld   mysqld   
> > mutex_spin_wait
> > 10395391  4.1843  vmlinux  vmlinux  
> > LKST_ETYPE_PROCESS_SCHED_ENTER_HEADER_hook
> > 9247281   3.7222  libpthread-2.3.4.so  libpthread-2.3.4.so  
> > pthread_mutex_unlock
> > 7407229   2.9815  vmlinux  vmlinux  
> > futex_requeue
> > 5921454   2.3835  libpthread-2.3.4.so  libpthread-2.3.4.so  
> > pthread_mutex_trylock
> > 5484279   2.2075  vmlinux  vmlinux  
> > LKST_ETYPE_PROCESS_WAKEUP_HEADER_hook
> > 4846067   1.9506  vmlinux  vmlinux  
> > __switch_to
> >
> > Regards,
> >   Hiro
> >
> >

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

Re: TimeStamp issue

[sheeri kritzer]

On 3/2/06, rtroiana <[EMAIL PROTECTED]> wrote:
> Thanks to all of you for replying. I'm using DATETIME instead of TIMESTAMP
> now. Although I still haven't find the answer for my second question.
>
>
>
> > > I used to use
> > > "CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" as default value for my
> > > TimeStamp column. Is there a way to assign default value to a DateTime
> > > column, since I couldn't find that in the documentation?
>

Sure.  You could run a trigger on an insert statement to update the
DATETIME field to the contents of SELECT NOW();

-Sheeri

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

RE: TimeStamp issue

[rtroiana]

Thanks to all of you for replying. I'm using DATETIME instead of TIMESTAMP
now. Although I still haven't find the answer for my second question.

 

> > I used to use
> > "CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" as default value for my
> > TimeStamp column. Is there a way to assign default value to a DateTime
> > column, since I couldn't find that in the documentation?



 

 

 

  _  

From: Rhino [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 02, 2006 3:50 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com; rtroiana
Subject: Re: TimeStamp issue

 

Thanks for keeping me honest! I'd forgotten that MySQL timestamps don't keep
the fractional parts of seconds either; I mostly use DB2 which keeps the
fractional parts (microseconds) and forgot about this quirk of MySQL.

 

--

Rhino

- Original Message - 

From: [EMAIL PROTECTED] 

To: Rhino   

Cc: mysql@lists.mysql.com ; rtroiana   

Sent: Thursday, March 02, 2006 1:42 PM

Subject: Re: TimeStamp issue

 


In fact, no time values in MySQL are fractional (yet). All times are stored
to the nearest second regardless of which date-time-like storage type you
use. They way Rhino phrased his answer, it sounded as though TIMSTAMP would
save fractional seconds. It doesn't. He is spot on about needing a separate
column to store any values that represent fractions of seconds. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

"Rhino" <[EMAIL PROTECTED]> wrote on 03/02/2006 01:25:36 PM:

> If you need a broader range of dates, you could use DATETIME instead of 
> TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through 
> '-12-31 23:59:59'. The only big difference is that DATETIME does not 
> store the fractional part of the seconds, e.g. 
> milliseconds/microseconds/nanonseconds. If you have to keep the fractional

> part of the seconds, you could store them in a second column defined as
some 
> kind of integer.
> 
> --
> Rhino
> 
> - Original Message - 
> From: "rtroiana" <[EMAIL PROTECTED]>
> To: 
> Sent: Thursday, March 02, 2006 10:10 AM
> Subject: TimeStamp issue
> 
> 
> > Hi All,
> >
> >
> >
> > I have recently noticed in the MySQL 5.0 documentation in section
11.3.1.
> > The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that
> >
> >
> >
> > "TIMESTAMP values cannot be earlier than 1970 or later than 2037. This 
> > means
> > that a date such as '1968-01-01', while legal as a DATETIME or DATE
value,
> > is not valid as a TIMESTAMP value and is converted to 0."
> >
> >
> >
> > Is that a correct range for TimeStamp? It's not big enough to be used in
a
> > real life application.
> >
> >
> >
> > I plan to use DATETIME instead of TIMESTAMP. I used to use
> > "CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" as default value for my
> > TimeStamp column. Is there a way to assign default value to a DateTime
> > column, since I couldn't find that in the documentation?
> >
> >
> >
> > Thanks,
> >
> > Reema
> >
> >
> >
> >
> 
> 
>


> 
> 
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006
> 
> 
> 
> -- 
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 


  _  


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006

Re: MyISAM Backup

[gerald_clark]

Jeff wrote:


Currently I backup my MyISAM tables every night by running a shell
script that does the following:

Run: 
Mysqlanalyze, mysqlrepair, mysqloptimize on all the tables

Then shutdown mysql
Then tar all the .MYI, .MYD and .frm files from the database's directory
to a backup director Start MySQL again.

Later on an outside backup device connects and backs up the tar file for
archiving.

Question:

Is it safe to do this without actually shutting down the mysql db?
Perhaps putting a write lock on all the tables first so that they can be
read but not written to during the tar.  Does it matter if I'm tar'ing a
file while mysql has it open?

Thanks,

Jeff



 


Look at mysqlhotcopy.

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

Re: TimeStamp issue

[Rhino]

Thanks for keeping me honest! I'd 
forgotten that MySQL timestamps don't keep the fractional parts of seconds 
either; I mostly use DB2 which keeps the fractional parts (microseconds) and 
forgot about this quirk of MySQL.
 
--
Rhino

  - Original Message - 
  From: 
  [EMAIL PROTECTED] 
  To: Rhino 
  Cc: mysql@lists.mysql.com ; rtroiana 
  
  Sent: Thursday, March 02, 2006 1:42 
  PM
  Subject: Re: TimeStamp issue
  In fact, no time values in 
  MySQL are fractional (yet). All times are stored to the nearest second 
  regardless of which date-time-like storage type you use. They way Rhino 
  phrased his answer, it sounded as though TIMSTAMP would save fractional 
  seconds. It doesn't. He is spot on about needing a separate column to store 
  any values that represent fractions of seconds. Shawn GreenDatabase AdministratorUnimin 
  Corporation - Spruce Pine "Rhino" <[EMAIL PROTECTED]> wrote on 
  03/02/2006 01:25:36 PM:> If you need a broader range of dates, you 
  could use DATETIME instead of > TIMESTAMP: DATETIME can handle the 
  range '1000-01-01 00:00:00' through > '-12-31 23:59:59'. The only 
  big difference is that DATETIME does not > store the fractional part of 
  the seconds, e.g. > milliseconds/microseconds/nanonseconds. If you have 
  to keep the fractional > part of the seconds, you could store them in a 
  second column defined as some > kind of integer.> > 
  --> Rhino> > - Original Message - > From: 
  "rtroiana" <[EMAIL PROTECTED]>> To: 
  > Sent: Thursday, March 02, 2006 10:10 
  AM> Subject: TimeStamp issue> > > > Hi 
  All,> >> >> >> > I have recently 
  noticed in the MySQL 5.0 documentation in section 11.3.1.> > The 
  DATETIME, DATE, and TIMESTAMP Types, it's mentioned that> >> 
  >> >> > "TIMESTAMP values cannot be earlier than 1970 
  or later than 2037. This > > means> > that a date such as 
  '1968-01-01', while legal as a DATETIME or DATE value,> > is not 
  valid as a TIMESTAMP value and is converted to 0."> >> 
  >> >> > Is that a correct range for TimeStamp? It's not 
  big enough to be used in a> > real life application.> 
  >> >> >> > I plan to use DATETIME instead of 
  TIMESTAMP. I used to use> > "CURRENT_TIMESTAMP on update 
  CURRENT_TIMESTAMP" as default value for my> > TimeStamp column. Is 
  there a way to assign default value to a DateTime> > column, since I 
  couldn't find that in the documentation?> >> >> 
  >> > Thanks,> >> > Reema> >> 
  >> >> >> > > 
  > 
  > > No virus found in this incoming message.> Checked by 
  AVG Free Edition.> Version: 7.1.375 / Virus Database: 268.1.1/272 - 
  Release Date: 01/03/2006> > > > -- > No 
  virus found in this outgoing message.> Checked by AVG Free 
  Edition.> Version: 7.1.375 / Virus Database: 268.1.1/272 - Release 
  Date: 01/03/2006> > > -- > MySQL General Mailing 
  List> For list archives: http://lists.mysql.com/mysql> To 
  unsubscribe:   
   http://lists.mysql.com/[EMAIL PROTECTED]> 
  
  
  

  No virus found in this incoming message.Checked by AVG Free 
  Edition.Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 
  01/03/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006


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

MyISAM Backup

[Jeff]

Currently I backup my MyISAM tables every night by running a shell
script that does the following:

Run: 
Mysqlanalyze, mysqlrepair, mysqloptimize on all the tables
Then shutdown mysql
Then tar all the .MYI, .MYD and .frm files from the database's directory
to a backup director Start MySQL again.

Later on an outside backup device connects and backs up the tar file for
archiving.

Question:

Is it safe to do this without actually shutting down the mysql db?
Perhaps putting a write lock on all the tables first so that they can be
read but not written to during the tar.  Does it matter if I'm tar'ing a
file while mysql has it open?

Thanks,

Jeff



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

RE: Replication from multiple masters?

[Jeff]

Sorry for the top post, just saying thanks, that's what I thought

Back to the drawing board...

Jeff

> -Original Message-
> From: David Griffiths [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, March 01, 2006 18:13
> To: mysql@lists.mysql.com
> Cc: [EMAIL PROTECTED]
> Subject: Re: Replication from multiple masters?
> 
> 
> Good point about the bin-logs. Yup - that would sink it. If 
> mysql used individual binary logs per master database, it would work. 
> Ya, if someone was silly enough to have two different 
> databases with the same name, it would be bad, even with 
> separate binary 
> logs for each database.
> 
> If you have two mysql instances on a single slave, you'll 
> need more memory, faster CPUs, more disk space, etc. But it 
> could be a 
> viable option if the machine is just being used to provide a 
> hot-standby.
> 
> 
> David
> 
> 
> 
> 
> 
> [EMAIL PROTECTED] wrote:
> > 
> > MySQL cannot handle more than one incoming binlog at a time. The
> > facilities are just not in the code.
> > 
> > You also run into a nightmare if a database exists on BOTH masters 
> > (same
> > name on both systems) and the PK values of any tables (also with 
> > matching names)  overlap. If  both masters update the 
> "same" row at appx 
> > the same time, we could run into deadlocking in the slave 
> that didn't 
> > happen on either master. It also means that the slave and 
> at least one 
> > of the masters will become out of sync (because the "other" 
> master's 
> > changes remain in the database) and replication is 
> considered "broken" 
> > at that point.  It's a serious can of worms to handle multi-master 
> > replication.
> > 
> > Your two instances on one matching replicating to two 
> separate masters
> > is not a multi-master replication (more than one master 
> replicating with 
> > a single slave) it's two single-master slave setups running 
> on the same 
> > machine. Close but not quite what the original post was 
> looking for (I 
> > don't think).
> > 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> > 
> > 
> > 
> > David Griffiths <[EMAIL PROTECTED]> wrote on 03/01/2006 04:34:26 
> > PM:
> > 
> >  > That's not entirely true.
> >  >
> >  > You can have two instances of mysql running on the 
> slave, and dbA  
> > > connects to one instance, and dbB connects to the other.  >
> >  >
> >  >
> >  > Jeff, when you say, "different databases", do you mean that each
> >  > master has a single mysql instance, and if you typed on M1,
> >  >
> >  > "show databases" you'd see (for example),  "dbA"
> >  >
> >  > and if you did the same on M2, you'd see, "dbB"?
> >  >
> >  > If so, I wonder if there is another way to get around it:
> >  >
> >  > - create a virtual IP address that represents both 
> masters. Use that
> >  > virtual master in the my.cnf on the slave; each master has to
> >  > have an identical replication account
> >  >
> >  > - put dbA and dbB on the slave
> >  >
> >  > - restrict replication from each master to their respective
> >  > databases - "dbA" and "dbB" - ie don't replicate changes to the
> >  > "mysql" database.
> >  >
> >  > The two masters appear as one (which overcomes the 
> single-IP-address
> >  > in the slave's my.cnf file), and each master has a different
> >  > database inside the mysql instance, they aren't stepping on each 
> > others toes.
> >  >
> >  > Just my 2 cents.
> >  >
> >  > David.
> >  >
> >  > Greg Donald wrote:
> >  > > On 3/1/06, Jeff <[EMAIL PROTECTED]> wrote:
> >  > >> Does anyone know if it's possible to replicate to a 
> single slave from
> >  > >> different databases on different masters?
> >  > >>
> >  > >> For instance:
> >  > >>
> >  > >> M1:dbAM2:dbB
> >  > >>   \ /
> >  > >>   rep rep
> >  > >> \ /
> >  > >>  Slave
> >  > >
> >  > >
> >  > > 
> http://dev.mysql.com/doc/refman/5.1/en/replication-features.ht
ml
>  > >
>  > > MySQL only supports one master and many slaves.
>  > >
>  > >
>  > >
>  > > --
>  > > Greg Donald
>  > > Zend Certified Engineer
>  > > MySQL Core Certification
>  > > http://destiney.com/
>  > >
>  >
>  > --
>  > MySQL General Mailing List
>  > For list archives: http://lists.mysql.com/mysql
>  > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>  >

-- 

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




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

Fw: TimeStamp issue

[Rhino]

I'm putting this back on the list where it belongs; that enables everyone to 
benefit from the discussion, both now and in the future via the archives.


--

Sorry, you're right, I didn't read your entire question thoroughly. You set 
the default value for a DATETIME column (or any other type for that matter) 
in the CREATE TABLE statement. For example:


create table if not exists dates03
(id smallint not null default 99,
my_datetime datetime not null default '2006-03-02 12:34:56',
primary key(id));

Please note that a datetime value can be expressed in several different 
formats. The different formats are explained here: 
http://dev.mysql.com/doc/refman/5.0/en/datetime.html.


--
Rhino


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

To: "'Rhino'" <[EMAIL PROTECTED]>
Sent: Thursday, March 02, 2006 1:29 PM
Subject: RE: TimeStamp issue


Thanks for the reply. I'm using DATETIME instead of TIMESTAMP now. 
Although

all I wanted to know was if there's any way I can set default values to
DATETIME column.

++Reema

-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 02, 2006 1:26 PM
To: rtroiana; mysql@lists.mysql.com
Subject: Re: TimeStamp issue

If you need a broader range of dates, you could use DATETIME instead of
TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through
'-12-31 23:59:59'. The only big difference is that DATETIME does not
store the fractional part of the seconds, e.g.
milliseconds/microseconds/nanonseconds. If you have to keep the fractional
part of the seconds, you could store them in a second column defined as 
some


kind of integer.

--
Rhino

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

To: 
Sent: Thursday, March 02, 2006 10:10 AM
Subject: TimeStamp issue



Hi All,



I have recently noticed in the MySQL 5.0 documentation in section 11.3.1.
The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that



"TIMESTAMP values cannot be earlier than 1970 or later than 2037. This
means
that a date such as '1968-01-01', while legal as a DATETIME or DATE 
value,

is not valid as a TIMESTAMP value and is converted to 0."



Is that a correct range for TimeStamp? It's not big enough to be used in 
a

real life application.



I plan to use DATETIME instead of TIMESTAMP. I used to use
"CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" as default value for my
TimeStamp column. Is there a way to assign default value to a DateTime
column, since I couldn't find that in the documentation?



Thanks,

Reema











No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006





--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006


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

RE: Want mysql to return tablename.fieldname format

[Ryan Stille]

The query has a number of joins and it was selecting * from all the
tables, so it was not apparent where each 'id' field was from.  It was
just a poorly written query all around. I've fixed the problem by
narrowing down the number of fields it selects and giving the ambiguous
fields specific names (ads.id as adid).

Thanks,
-Ryan

Rhino wrote:
> I don't understand what you want. If you have the original
> query, it should be apparent from it where each 'id' column
> originated. If you're not sure how to read the query, post it
> and we can help you figure out which table provided each 'id' column.
> 
> --
> Rhino
> 
> - Original Message -
> From: "Ryan Stille" <[EMAIL PROTECTED]>
> To: 
> Sent: Thursday, March 02, 2006 12:42 PM
> Subject: Want mysql to return tablename.fieldname format
> 
> 
> I am working with an existing compilcated query someone wrote
> years ago.
> When I dump the data from the query to try to figure out why
> I'm getting
> unexpected data, I have three fields named "id".  Is there anyway to
> tell mysql to name the fields with the table name when they are
> returned, so they show up as ads.id, track.id, etc?
> 
> -Ryan



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

Qyery help - pass string to stored procedure for IN clause

[Price, Randall]

I have a SELECT query that looks similar to the following:

SELECT
FirstName,
LastName
FROM
myTable
WHERE
LastName IN ('PRICE', 'SMITH');

What I want to do is create a stored procedure for this SELECT query
similar to the following:

CREATE PROCEDURE spGetNames (IN strNames VARCHAR(255))
BEGIN
SELECT
FirstName,
LastName
FROM
myTable
WHERE
LastName IN (strNames);
END

And then I would like to call it by passing in a list of names.  None of
the following calls work:

CALL spGetNames ('PRICE,SMITH');
CALL spGetNames (" 'PRICE', 'SMITH' ");
CALL spGetNames (" ''PRICE'', ''SMITH'' ");

My question is how to format the string parameter so this query works.

Thanks,

Randall Price
VT.SETI.IAD.MIG:Microsoft Implementation Group
http://vtmig.vt.edu
[EMAIL PROTECTED]

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

Re: TimeStamp issue

[SGreen]

In fact, no time values in MySQL are fractional (yet). All times are 
stored to the nearest second regardless of which date-time-like storage 
type you use. They way Rhino phrased his answer, it sounded as though 
TIMSTAMP would save fractional seconds. It doesn't. He is spot on about 
needing a separate column to store any values that represent fractions of 
seconds.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Rhino" <[EMAIL PROTECTED]> wrote on 03/02/2006 01:25:36 PM:

> If you need a broader range of dates, you could use DATETIME instead of 
> TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through 
> '-12-31 23:59:59'. The only big difference is that DATETIME does not 

> store the fractional part of the seconds, e.g. 
> milliseconds/microseconds/nanonseconds. If you have to keep the 
fractional 
> part of the seconds, you could store them in a second column defined as 
some 
> kind of integer.
> 
> --
> Rhino
> 
> - Original Message - 
> From: "rtroiana" <[EMAIL PROTECTED]>
> To: 
> Sent: Thursday, March 02, 2006 10:10 AM
> Subject: TimeStamp issue
> 
> 
> > Hi All,
> >
> >
> >
> > I have recently noticed in the MySQL 5.0 documentation in section 
11.3.1.
> > The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that
> >
> >
> >
> > "TIMESTAMP values cannot be earlier than 1970 or later than 2037. This 

> > means
> > that a date such as '1968-01-01', while legal as a DATETIME or DATE 
value,
> > is not valid as a TIMESTAMP value and is converted to 0."
> >
> >
> >
> > Is that a correct range for TimeStamp? It's not big enough to be used 
in a
> > real life application.
> >
> >
> >
> > I plan to use DATETIME instead of TIMESTAMP. I used to use
> > "CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" as default value for 
my
> > TimeStamp column. Is there a way to assign default value to a DateTime
> > column, since I couldn't find that in the documentation?
> >
> >
> >
> > Thanks,
> >
> > Reema
> >
> >
> >
> >
> 
> 
> 

> 
> 
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 
01/03/2006
> 
> 
> 
> -- 
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 
01/03/2006
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: TimeStamp issue

[Rhino]

If you need a broader range of dates, you could use DATETIME instead of 
TIMESTAMP: DATETIME can handle the range '1000-01-01 00:00:00' through 
'-12-31 23:59:59'. The only big difference is that DATETIME does not 
store the fractional part of the seconds, e.g. 
milliseconds/microseconds/nanonseconds. If you have to keep the fractional 
part of the seconds, you could store them in a second column defined as some 
kind of integer.


--
Rhino

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

To: 
Sent: Thursday, March 02, 2006 10:10 AM
Subject: TimeStamp issue



Hi All,



I have recently noticed in the MySQL 5.0 documentation in section 11.3.1.
The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that



"TIMESTAMP values cannot be earlier than 1970 or later than 2037. This 
means

that a date such as '1968-01-01', while legal as a DATETIME or DATE value,
is not valid as a TIMESTAMP value and is converted to 0."



Is that a correct range for TimeStamp? It's not big enough to be used in a
real life application.



I plan to use DATETIME instead of TIMESTAMP. I used to use
"CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" as default value for my
TimeStamp column. Is there a way to assign default value to a DateTime
column, since I couldn't find that in the documentation?



Thanks,

Reema










No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006


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

Re: Want mysql to return tablename.fieldname format

[Rhino]

I don't understand what you want. If you have the original query, it should 
be apparent from it where each 'id' column originated. If you're not sure 
how to read the query, post it and we can help you figure out which table 
provided each 'id' column.


--
Rhino

- Original Message - 
From: "Ryan Stille" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, March 02, 2006 12:42 PM
Subject: Want mysql to return tablename.fieldname format


I am working with an existing compilcated query someone wrote years ago.
When I dump the data from the query to try to figure out why I'm getting
unexpected data, I have three fields named "id".  Is there anyway to
tell mysql to name the fields with the table name when they are
returned, so they show up as ads.id, track.id, etc?

-Ryan


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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006


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

Re: MySQL and Hyperthreading

[Atle Veka]

Hi Hiro, we have noticed severe performance loss with HT enabled under
FreeBSD but not with MySQL. However, we could be having problems with
MySQL as well without knowing as we haven't done any testing as it is
operating fine. What operating system are you running?

We now disable HT on all servers wherever possible..


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Thu, 2 Mar 2006, Hiro Yoshioka wrote:

> Hi,
>
> We found a severe performance degradation when Hyperthreading is on
> and thread_concurrency=20.
>
> We are using OSDL DBT-1 as the benchmark and got about 200 to 250
> BT (bogotransactions per second) HT is OFF normal case but 30 to
> 50 BT on HT is ON.
>
> innodb_thread_concurrency=20
>
> So we did profile (using oprofile tool) and got the following profiling
> data. My impression is that mutex_spin_wait (and ut_delay) is
> something wrong if HT is ON. (Spin-wait loop is too expensive if it is
> hyperthreading.)
>
> I added the following code but it does not help it.
>
> $ diff -pu ut0ut.c.orig ut0ut.c
> --- ut0ut.c.orig2005-10-17 10:27:43.0 +0900
> +++ ut0ut.c 2006-02-28 11:59:16.777840496 +0900
> @@ -290,6 +290,13 @@ ut_delay(
> j = 0;
>
> for (i = 0; i < delay * 50; i++) {
> +   /* When executing a spin-wait loop on the Hyper-Threading
> +  processor, the processor can suffer a severe performance
> +   penalty. The pause instruction provides a hint to the
> +   processor. Please refer IA-32 Intel Architecture
> +   Software Developers Manual, Vol 3.   */
> +   __asm__ __volatile__(
> +   "pause; \n");
> j += i;
> }
>
> What do you think? Is there any hints?
>
> HT is OFF
> CPU: P4 / Xeon, speed 2793.26 MHz (estimated)
> Counted GLOBAL_POWER_EVENTS events (time during which processor is not 
> stopped) with a unit mask of 0x01 (mandatory)
> count 10
> samples  %image name   app name symbol 
> name
> 13159082  8.8445  libc-2.3.4.solibc-2.3.4.somemcpy
> 12565549  8.4456  libpthread-2.3.4.so  libpthread-2.3.4.so  
> pthread_mutex_trylock
> 11387363  7.6537  mysqld   mysqld   
> rec_get_offsets_func
> 9631916   6.4738  libpthread-2.3.4.so  libpthread-2.3.4.so  
> pthread_mutex_unlock
> 8794484   5.9110  mysqld   mysqld   
> btr_search_guess_on_hash
> 4949248   3.3265  mysqld   mysqld   
> row_search_for_mysql
> 4022481   2.7036  mysqld   mysqld   ut_delay
> 3754265   2.5233  mysqld   mysqld   
> cmp_dtuple_rec_with_match
> 2535190   1.7040  mysqld   mysqld   
> row_sel_store_mysql_rec
> 2520957   1.6944  mysqld   mysqld   
> btr_cur_search_to_nth_level
>
> HT is ON
> CPU: P4 / Xeon with 2 hyper-threads, speed 2793.26 MHz (estimated)
> Counted GLOBAL_POWER_EVENTS events (time during which processor is not 
> stopped) with a unit mask of 0x01 (mandatory)
> count 10
> samples  %image name   app name symbol 
> name
> 53221317 21.4225  libpthread-2.3.4.so  libpthread-2.3.4.so  
> pthread_mutex_lock
> 25743323 10.3621  mysqld   mysqld   ut_delay
> 12345146  4.9691  vmlinux  vmlinux  do_futex
> 12066038  4.8568  mysqld   mysqld   
> mutex_spin_wait
> 10395391  4.1843  vmlinux  vmlinux  
> LKST_ETYPE_PROCESS_SCHED_ENTER_HEADER_hook
> 9247281   3.7222  libpthread-2.3.4.so  libpthread-2.3.4.so  
> pthread_mutex_unlock
> 7407229   2.9815  vmlinux  vmlinux  
> futex_requeue
> 5921454   2.3835  libpthread-2.3.4.so  libpthread-2.3.4.so  
> pthread_mutex_trylock
> 5484279   2.2075  vmlinux  vmlinux  
> LKST_ETYPE_PROCESS_WAKEUP_HEADER_hook
> 4846067   1.9506  vmlinux  vmlinux  
> __switch_to
>
> Regards,
>   Hiro
>
>

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

Fwd: browsing in search-results

[sheeri kritzer]

What makes you think that temporary tables would have better
performance than LIMIT?  I'm not saying they do, or don't, just
wondering what makes you say that.

Each query, yes, would make a new temporary table.  However, once that
session disconnects, the temporary table goes away.  Something to
think about if you're using stateless connections (like you have to in
PHP4).

-Sheeri

On 2/26/06, Jochen Kaechelin <[EMAIL PROTECTED]> wrote:
> I make a fulltext-search and store the results in a temporary table
> so the user can browser the temporary table (5 results per page...).
>
> I think this is of better performance instead of setting
> "LIMIT $start,$elements".
>
> correct?
>
> what would happen if several visitors of the website make a search:
>
> each user a temorary table??
>
> Thanx.
>
> --
> fvgi242ss - Webmaster wlanhacking.de
> http://mail.wlanhacking.de/cgi-bin/mailman/listinfo
>
> --
> 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: CONCAT() returns not correct character set

[sheeri kritzer]

Hi there,

I tried a few other queries:

first, confirm that what you think is a binary is indeed a binary:

mysql> SELECT CHARSET(_binary'Binary');
+--+
| CHARSET(_binary'Binary') |
+--+
| binary   |
+--+
1 row in set (0.00 sec)

check the regular text (for completeness' sake)

mysql> select charset ('binary');
++
| charset ('binary') |
++
| latin1 |
++
1 row in set (0.00 sec)

OK, so concatenating the 2 should result in a binary according to the manual:

mysql> SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
+--+
| CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
+--+
| latin1   |
+--+
1 row in set (0.00 sec)

Hrm, I got the same result you did.

However,

mysql> SELECT CHARSET(CONCAT(_binary'Binary','foo'));
+--+
| CHARSET(CONCAT((_binary'Binary'),'foo')) |
+--+
| binary   |
+--+
1 row in set (0.00 sec)

works just fine.  Why are you converting the text to latin1?  It's
already there.  That conversion seems to be messing things up.

Perhaps someone can explain why the conversion messes things up --
seems like a bug to me.

-Sheeri

On 2/26/06, Hirofumi Fujiwara <[EMAIL PROTECTED]> wrote:
> Dear  MySQL fans,
>
> I tested CONCAT() with binary strings and I got strange result.
>
> Manual says:
> http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
>
> If the arguments include any binary strings, the result is a binary
> string.
>
> But the following test says:
>
> bianry + latin1 > latin1 (not bianry)
>
> mysql> SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1)));
> +--+
> | CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) |
> +--+
> | latin1   |
> +--+
> 1 row in set (0.00 sec)
>
> 
> Hirofumi Fujiwara (Tokyo JAPAN)  enjoy JAVA and Puzzle World
> [EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html
> [EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/
> 
> My SUDOKU Probs  http://www.pro.or.jp/~fuji/sudoku/problems/
> 
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

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

RE: create/restore database without binary logging

[Ryan Stille]

Put "SET SQL_LOG_BIN=0" at the top of your dump file.  That will turn
off logging just for your session.

-Ryan
 

> -Original Message-
> From: sheeri kritzer [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, March 02, 2006 11:56 AM
> To: Rithish Saralaya
> Cc: MySQL general mailing list
> Subject: Re: create/restore database without binary logging
> 
> Rithish,
> 
> It's possible to do this by turning off binary logging, 
> restarting the server, importing, turning on binary logging, 
> and restarting.
> 
> -Sheeri
> 
> On 2/27/06, Rithish Saralaya 
> <[EMAIL PROTECTED]> wrote:
> > Hello.
> >
> > I was going to recreate a database of size 35 GB from sql 
> dump file. 
> > Wanted to know if it is possible to do it without mysql 
> writing into 
> > the binary log. If yes, how?
> >
> > Regards,
> > Rithish.
> >
> >
> 
> --
> 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: problem with transfer databases from different Mysql versions

[sheeri kritzer]

It helps if you provide the error messages.

Sincerely,

-Sheeri

On 2/26/06, Xiaobo Chen <[EMAIL PROTECTED]> wrote:
> Hi, all
>
> I have installed a portal server and which has Mysql 4.0.15-nt with it.
> Before I installed this server, I have Mysql 4.1 in my PC. But thing is
> that they will conflict and I have to delete the old Mysql service. So I
> used "mysqldump" to export the databases from the 4.1 version to the sql
> files. But when I use "source" to retrieve those database in the 4.0.15-nt
> version, it reports errors.
>
> Can anyone tell me how I should tackle this problem? I am also concerned
> what the "-nt" here means?
>
> Thanks a lot.
>
> Xiaobo
>
>
> --
> 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: create/restore database without binary logging

[sheeri kritzer]

Rithish,

It's possible to do this by turning off binary logging, restarting the
server, importing, turning on binary logging, and restarting.

-Sheeri

On 2/27/06, Rithish Saralaya <[EMAIL PROTECTED]> wrote:
> Hello.
>
> I was going to recreate a database of size 35 GB from sql dump file. Wanted
> to know if it is possible to do it without mysql writing into the binary
> log. If yes, how?
>
> Regards,
> Rithish.
>
>

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

Re: Want mysql to return tablename.fieldname format

[SGreen]

"Ryan Stille" <[EMAIL PROTECTED]> wrote on 03/02/2006 12:42:01 PM:

> I am working with an existing compilcated query someone wrote years ago.
> When I dump the data from the query to try to figure out why I'm getting
> unexpected data, I have three fields named "id".  Is there anyway to
> tell mysql to name the fields with the table name when they are
> returned, so they show up as ads.id, track.id, etc?
> 
> -Ryan
> 
> 

I am not aware of that being an option unless you provide your own aliases 
in the SELECT clause of your query.

Select c.id as "customer.id", c.name as "customer.name", ct.name as 
"contact.name"
FROM customer c
INNER JOIN contact ct
  on ct.customer_id = c.id;

Sorry!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: Want mysql to return tablename.fieldname format

[Marciano [Intercol]]

Yes.

You can add on SELECT syntax the AS operation, like this:

SELECT
ads.id AS 'ads.id',
track.id AS 'track.id'

FROM .


-Original Message-
From: Ryan Stille [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 02, 2006 2:42 PM
To: mysql@lists.mysql.com
Subject: Want mysql to return tablename.fieldname format

I am working with an existing compilcated query someone wrote years ago.
When I dump the data from the query to try to figure out why I'm getting
unexpected data, I have three fields named "id".  Is there anyway to
tell mysql to name the fields with the table name when they are
returned, so they show up as ads.id, track.id, etc?

-Ryan


-- 
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: dumping results of a select

[SGreen]

2wsxdr5 <[EMAIL PROTECTED]> wrote on 03/02/2006 12:30:17 PM:

> Is there a way with mysqldump to instead of dumping the contents of a 
> table dump the contents of a select so if you import that sql back in 
> you will get a new table that looks like the select?
> 
> -- 
> Chris W
> KE5GIX
> 
> Gift Giving Made Easy
> Get the gifts you want & 
> give the gifts they want
> One stop wish list for any gift, 
> from anywhere, for any occasion!
> http://thewishzone.com
> 
> 

I don't think so but if all you wanted to do was to create a new table out 
of the results you could say

CREATE TABLE newtable
SELECT ...

I use it all the time to great effect. Documentation is near the bottom of 
this page:
http://dev.mysql.com/doc/refman/4.1/en/create-table.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Want mysql to return tablename.fieldname format

[Ryan Stille]

I am working with an existing compilcated query someone wrote years ago.
When I dump the data from the query to try to figure out why I'm getting
unexpected data, I have three fields named "id".  Is there anyway to
tell mysql to name the fields with the table name when they are
returned, so they show up as ads.id, track.id, etc?

-Ryan


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

RE: dumping results of a select

[George Law]

mysqldump has a -w option:

 -w, --where=  dump only selected records; QUOTES mandatory!

--
George 

> -Original Message-
> From: 2wsxdr5 [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, March 02, 2006 12:30 PM
> To: 'MySQL general mailing list'
> Subject: dumping results of a select
> 
> Is there a way with mysqldump to instead of dumping the 
> contents of a table dump the contents of a select so if you 
> import that sql back in you will get a new table that looks 
> like the select?
> 
> --
> Chris W
> KE5GIX
> 
> Gift Giving Made Easy
> Get the gifts you want &
> give the gifts they want
> One stop wish list for any gift,
> from anywhere, for any occasion!
> http://thewishzone.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: dumping results of a select

[jblanchard]

[snip]
Is there a way with mysqldump to instead of dumping the contents of a 
table dump the contents of a select so if you import that sql back in 
you will get a new table that looks like the select?
[/snip]

I don't think so, but you could use INTO OUTFILE


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

dumping results of a select

[2wsxdr5]

Is there a way with mysqldump to instead of dumping the contents of a 
table dump the contents of a select so if you import that sql back in 
you will get a new table that looks like the select?


--
Chris W
KE5GIX

Gift Giving Made Easy
Get the gifts you want & 
give the gifts they want
One stop wish list for any gift, 
from anywhere, for any occasion!

http://thewishzone.com


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

RE: mysqldump backup on filters

[Marciano [Intercol]]

Your table is missing. Try this:

?>mysqldump --single-transaction -u root clientdb 
--where="FLD_CLIENT_ID=1" > client1_dbbackup.sql

-Original Message-
From: Rithish Saralaya [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 02, 2006 1:23 PM
To: MySQL general mailing list
Subject: mysqldump backup on filters

Hello.

We have a database driven system that serves multiple clients. We have a
single database for this purpose. The data(rows) in some of the tables are
specific to individual clients. In all such tables, we have a field
FLD_CLIENT_ID whose value depicts the client to whom that row of information
corresponds to. All other tables in the system, are agnostic to client
information; but are required for the system to function as a whole.

I would want to take seperate backups for individual clients. I try to use
mysqldump to generate a dump file by filtering on the FLD_CLIENT_ID column.
However, since not all tables contain the FLD_CLIENT_ID column, mysqldump
fails.

I use mysqldump from the commandline as


---
?>mysqldump --single-transaction -u root clientdb --where="FLD_CLIENT_ID=1"
> client1_dbbackup.sql


---

The error that is thrown is


---
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM
`ACCOUNT` WHERE FLD_CLIENT_ID=1': Unknown column FLD_CLIENT_ID' in 'where
clause' (1054)


---
which means thats the dump can't be created as the table ACCOUNT does not
contain the column FLD_CLIENT_ID.

Is it possible to write the where clause such that it should apply the
FLD_CLIENT_ID filter only if the column exists in the table; and if not,
dump the data anyways.

I am on RHEL - MySQL 4.1.11 - Storage engine INNoDB

Regards,
Rithish.


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

Re: TimeStamp issue

[gerald_clark]

rtroiana wrote:


Hi All,



I have recently noticed in the MySQL 5.0 documentation in section 11.3.1.
The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that 




"TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means
that a date such as '1968-01-01', while legal as a DATETIME or DATE value,
is not valid as a TIMESTAMP value and is converted to 0."



Is that a correct range for TimeStamp? It's not big enough to be used in a
real life application.
 


Why not?
It is not 2037 yet.
Timestamp is designed to record when records are updated, not for 
storing arbitrary dates and times.





I plan to use DATETIME instead of TIMESTAMP. I used to use
"CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" as default value for my
TimeStamp column. Is there a way to assign default value to a DateTime
column, since I couldn't find that in the documentation?



Thanks,

Reema




 




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

RE: apostrophe char problem

[George Law]

Halid,

I think this is a php/phpmyadmin problem and not really a mysql problem.

PHP has an option called "magic quotes" that may be causing this.  
 http://us2.php.net/manual/en/function.get-magic-quotes-gpc.php

I have run into this before - I think what happens is that magic quotes
will change a single
quote '   to a double ''   so its mysql safe.   Then when it reads it
back in from the database
and renders the HTML, it also displays the doubles.  

If you look in the database iteself from the mysql command line client
do the double quotes show?

--
George Law



> -Original Message-
> From: Halid Faith [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, March 02, 2006 11:51 AM
> To: mysql@lists.mysql.com
> Subject: apostrophe char problem
> 
> Hello
> 
> I use mysql 4.1.16 and php4.3.10 and phpmyadmin2.7 and 
> apache-2.0.53  on
> FreeBSD5.3
> 
> I add any text as data with phpadmin on web. it works well. 
> But I have a problem.
> As example if I add a text to mysql table with phpadmin.
> My text contains as below;
> "Halid 's car is expensive"
> 
> I will see above the sentence as "Halid ''s car is expensive" 
> on web (internet explorer) That is, I see that double ' 
> apostrophe character out
> 
> What shall I do ?
> 
> Thanks
> 

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

Re: apostrophe char problem

[sprock]

Something is double escaping the strings.
Check if GPC magic string is enabled (apache's auto escaping).
Also check if your code manually escapes the strings.

Halid Faith wrote:

Hello

I use mysql 4.1.16 and php4.3.10 and phpmyadmin2.7 and apache-2.0.53  on
FreeBSD5.3

I add any text as data with phpadmin on web. it works well. But I have a
problem.
As example if I add a text to mysql table with phpadmin.
My text contains as below;
"Halid 's car is expensive"

I will see above the sentence as "Halid ''s car is expensive" on web
(internet explorer)
That is, I see that double ' apostrophe character out

What shall I do ?

Thanks

  



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

apostrophe char problem

[Halid Faith]

Hello

I use mysql 4.1.16 and php4.3.10 and phpmyadmin2.7 and apache-2.0.53  on
FreeBSD5.3

I add any text as data with phpadmin on web. it works well. But I have a
problem.
As example if I add a text to mysql table with phpadmin.
My text contains as below;
"Halid 's car is expensive"

I will see above the sentence as "Halid ''s car is expensive" on web
(internet explorer)
That is, I see that double ' apostrophe character out

What shall I do ?

Thanks

mysqldump backup on filters

[Rithish Saralaya]

Hello.

We have a database driven system that serves multiple clients. We have a
single database for this purpose. The data(rows) in some of the tables are
specific to individual clients. In all such tables, we have a field
FLD_CLIENT_ID whose value depicts the client to whom that row of information
corresponds to. All other tables in the system, are agnostic to client
information; but are required for the system to function as a whole.

I would want to take seperate backups for individual clients. I try to use
mysqldump to generate a dump file by filtering on the FLD_CLIENT_ID column.
However, since not all tables contain the FLD_CLIENT_ID column, mysqldump
fails.

I use mysqldump from the commandline as


---
?>mysqldump --single-transaction -u root clientdb --where="FLD_CLIENT_ID=1"
> client1_dbbackup.sql


---

The error that is thrown is


---
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM
`ACCOUNT` WHERE FLD_CLIENT_ID=1': Unknown column FLD_CLIENT_ID' in 'where
clause' (1054)


---
which means thats the dump can't be created as the table ACCOUNT does not
contain the column FLD_CLIENT_ID.

Is it possible to write the where clause such that it should apply the
FLD_CLIENT_ID filter only if the column exists in the table; and if not,
dump the data anyways.

I am on RHEL - MySQL 4.1.11 - Storage engine INNoDB

Regards,
Rithish.

Re: Getting every other value in a select

[Giuseppe Maxia]

[EMAIL PROTECTED] wrote:
> This might be a bit odd, but here we go..
>  
> I have some data in a table that has the following structure:
>  
[SNIP]

> The data is logged once a second. The StartTimeAndDate will be the same
> for the particular workpiece that I am interested in.
> I pull out the data with a select statement such as  "select
> `OutgoingPcntGgeDev` from gaugereportinglist where
> `StartTimeAndDate`="2006-03-02 09:36:09" This can give me, say, 3 to 4
> thousand rows. I am using BIRT (www.eclipse.org/birt) to do my
> reporting. These value are shown on a graph. However 3000 points on a
> small graph on a web page is a little over the top, and takes a long
> time to plot. Is there any way to select say, every 10th point without
> doing anything on the client side?
> 

A cheap solution, with a user variable:

select
 `OutgoingPcntGgeDev` from gaugereportinglist where
 `StartTimeAndDate`="2006-03-02 09:36:09"
  and (@count := coalesce( @count, 0) + 1 ) % 10 = 0 ;

ciao
gmax

-- 
 _  _ _  _
(_|| | |(_|><  The Data Charmer
 _|
http://datacharmer.blogspot.com/


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

TimeStamp issue

[rtroiana]

Hi All,

 

I have recently noticed in the MySQL 5.0 documentation in section 11.3.1.
The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that 

 

"TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means
that a date such as '1968-01-01', while legal as a DATETIME or DATE value,
is not valid as a TIMESTAMP value and is converted to 0."

 

Is that a correct range for TimeStamp? It's not big enough to be used in a
real life application.

 

I plan to use DATETIME instead of TIMESTAMP. I used to use
"CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" as default value for my
TimeStamp column. Is there a way to assign default value to a DateTime
column, since I couldn't find that in the documentation?

 

Thanks,

Reema

 

Re: Getting every other value in a select

[SGreen]

<[EMAIL PROTECTED]> wrote on 03/02/2006 06:55:14 AM:

> This might be a bit odd, but here we go..
> 
> I have some data in a table that has the following structure:
> 
> CREATE TABLE `gaugereportinglist` (
> 
>   `Updated` timestamp NOT NULL default
> CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 
>   `StartTimeAndDate` datetime NOT NULL,
> 
>   `Time_Offset` double default NULL,
> 
>   `OutgoingPcntGgeDev` float default NULL,
> 
>   `IncomingPcntGgeDev` float default NULL,
> 
>   `MillSpeed` float default NULL,
> 
>   `PassNumber` int(2) default NULL,
> 
>   KEY `STADIndex` (`StartTimeAndDate`)
> 
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 
> 
> The data is logged once a second. The StartTimeAndDate will be the same
> for the particular workpiece that I am interested in.
> I pull out the data with a select statement such as  "select
> `OutgoingPcntGgeDev` from gaugereportinglist where
> `StartTimeAndDate`="2006-03-02 09:36:09" This can give me, say, 3 to 4
> thousand rows. I am using BIRT (www.eclipse.org/birt) to do my
> reporting. These value are shown on a graph. However 3000 points on a
> small graph on a web page is a little over the top, and takes a long
> time to plot. Is there any way to select say, every 10th point without
> doing anything on the client side? 

You can do it if you store your current results into a temporary table 
with an auto_increment column in it. Then you can run a query on your 
temporary table looking for rows where MOD(auto_inc_column_name,10) =0

The MOD() operator returns the remainder that comes from dividing the 
first parameter by the second.
http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html


If you wanted to get every 20th term, just change the 10 to a 20. See the 
pattern?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: help regarding writing a query

[Rhino]

I'm not sure but the situation you are describing sounds like the so-called 
"Bill of Materials" Problem, sometimes called BOM for short.


I'm certain that some databases provide SQL to handle BOM problems; for 
example, DB2 which I know quite well, provides for BOMs. However, I just did 
a search in the MySQL 5.0 manual and could not find a single hit on "Bill of 
Materials" or "BOM" so I suspect that MySQL does not support this, although 
it might some day. Someone once told me about another database that supports 
BOMs; it might have been Oracle but I can't be sure. He and I discussed BOMs 
and I showed him the SQL used by DB2 to handle them; he said the SQL for 
BOMs in the other database was quite different but didn't show it to me.


In short, I suspect that solving your problem in MySQL will be difficult or 
maybe impossible. Solving it in DB2 or some other database should be 
possible if using another database is an option for you.
But don't give up yet! I may have misunderstood your requirement and it 
really isn't a BOM at all: after all, a BOM usually proceeds downwards from 
parents to children to grandchildren but you seem to want to go upwards; 
that may require a somewhat different approach.


I just did a search on "Bill of Materials" in the MySQL archives and found 
some useful information, particularly this article by Peter Brawley:


http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html#parts_explosion

It describes a way to do a BOM (also known as a "Parts Explosion") in MySQL 
and also gives many links to articles about BOMs which might be helpful to 
you.


Also, if you do a Google search on "Bill of Materials MySQL" in Google the 
way I just did, you'll find over 200,000 hits, some of which will point to 
tools or techniques that might help you with your problem.


--
Rhino

- Original Message - 
From: "VenuGopal Papasani" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, March 02, 2006 5:24 AM
Subject: help regarding writing a query


HI all,
  This is venugopal.Being a silent member of this group.I am having a doubt
regarding writing a query.I can explain it with an example

  Consider i have a table called GetParents

  The table consists of two fieds called num as Integer
 ParentNum as Integer

  The table consists of the following data
   num ParentNum
1 4
2 1
3 5
6 7
4 2

Now the output should be as follows if i have given input as 4
 then output should be as
   num ParentNum
1 4
2 1
4 2

Explaination of Output:
As the given input is 4.Input is compared with num and we get 4->
2.Now ParentNum is taken and checked in the table for 2 being the num and
compared and 2->1 is obtained and now again the comparision is taken and
1->2 is given as ouput

 NOw the problem.Can we get the result by writing only one query or we
have to write more no of queries.If it is possible to get this with one
query then can u please give me the query.

 Can u give me the query or tell me the source where i can get examples
of such queries.

  It will be very much helpful for me.

Thanks in advance,

Regards,
venu.






No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/272 - Release Date: 01/03/2006


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

Getting every other value in a select

[andy.lawton]

This might be a bit odd, but here we go..
 
I have some data in a table that has the following structure:
 
CREATE TABLE `gaugereportinglist` (

  `Updated` timestamp NOT NULL default
CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  
  `StartTimeAndDate` datetime NOT NULL,

  `Time_Offset` double default NULL,

  `OutgoingPcntGgeDev` float default NULL,

  `IncomingPcntGgeDev` float default NULL,

  `MillSpeed` float default NULL,

  `PassNumber` int(2) default NULL,

  KEY `STADIndex` (`StartTimeAndDate`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1  
 
The data is logged once a second. The StartTimeAndDate will be the same
for the particular workpiece that I am interested in.
I pull out the data with a select statement such as  "select
`OutgoingPcntGgeDev` from gaugereportinglist where
`StartTimeAndDate`="2006-03-02 09:36:09" This can give me, say, 3 to 4
thousand rows. I am using BIRT (www.eclipse.org/birt) to do my
reporting. These value are shown on a graph. However 3000 points on a
small graph on a web page is a little over the top, and takes a long
time to plot. Is there any way to select say, every 10th point without
doing anything on the client side?

Re: Elementary replication

[Marciano G. Bosi [Intercol]]

You can do with one line:

replicate-do-table = db.table1, db.table2, db.table3

- Original Message - 
From: "Rob Gormley" <[EMAIL PROTECTED]>

To: 
Sent: Monday, February 27, 2006 10:35 PM
Subject: Elementary replication


Hi,

Not having a lot of luck with replication.

Have 2 4.1x boxes

Slave is configured, via my.cnf, to only replicate three tables:

replicate-do-table = db.table1
replicate-do-table = db.table2
replicate-do-table = db.table3

SHOW SLAVE STATUS; (sanitised) output is as follows:

mysql> show slave status\G
*** 1. row ***
Slave_IO_State:
   Master_Host: db0.xxx
   Master_User: xxx
   Master_Port: 3306
 Connect_Retry: 60
   Master_Log_File: mysql-bin.001305
   Read_Master_Log_Pos: 10165261
Relay_Log_File: db2-relay-bin.02
 Relay_Log_Pos: 4
 Relay_Master_Log_File: mysql-bin.001305
  Slave_IO_Running: No
 Slave_SQL_Running: Yes
   Replicate_Do_DB:
   Replicate_Ignore_DB:
Replicate_Do_Table: db.table1,db.table2,db.table3
Replicate_Ignore_Table:
   Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
  Skip_Counter: 0
   Exec_Master_Log_Pos: 10165261
   Relay_Log_Space: 4
   Until_Condition: None
Until_Log_File:
 Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
   Master_SSL_Cert:
 Master_SSL_Cipher:
Master_SSL_Key:
 Seconds_Behind_Master: NULL
1 row in set (0.00 sec)


Master position was set with "CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.001305', MASTER_LOG_POS = 10165261"
to deal with restoring a mysqldump, and using a running server (I'd
really like to avoid shutting down the master)

Replication has never been run on this machine.

Where can I go from here to get it running?

Rob

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

help regarding writing a query

[VenuGopal Papasani]

HI all,
   This is venugopal.Being a silent member of this group.I am having a doubt
regarding writing a query.I can explain it with an example

   Consider i have a table called GetParents

   The table consists of two fieds called num as Integer
  ParentNum as Integer

   The table consists of the following data
num ParentNum
 1 4
 2 1
 3 5
 6 7
 4 2

 Now the output should be as follows if i have given input as 4
  then output should be as
num ParentNum
 1 4
 2 1
 4 2

 Explaination of Output:
 As the given input is 4.Input is compared with num and we get 4->
2.Now ParentNum is taken and checked in the table for 2 being the num and
compared and 2->1 is obtained and now again the comparision is taken and
1->2 is given as ouput

  NOw the problem.Can we get the result by writing only one query or we
have to write more no of queries.If it is possible to get this with one
query then can u please give me the query.

  Can u give me the query or tell me the source where i can get examples
of such queries.

   It will be very much helpful for me.

 Thanks in advance,

 Regards,
 venu.

Re: UPDATE from monthly to yearly rows

[C.R.Vegelin]

Thanks Peter,

I didn't know that MySQL has no UPDATE ... SELECT command.
I followed your advice and made the following query
UPDATE Data AS db INNER JOIN 
(SELECT myKey,Year,
 SUM(IF(Month= 1,Cell,Null)) AS `Jan`, 
 ...
 SUM(IF(Month=12,Cell,Null)) AS `Dec` 
 FROM Updates GROUP BY myKey, Year) AS sq
ON (db.myKey=sq.myKey AND db.Year=sq.Year)
SET db.Jan = sq.Jan,  ..., db.Dec = sq.Dec;

This works fine and fast. Thanks again !
Regards, Cor
  - Original Message - 
  From: Peter Brawley 
  To: C.R.Vegelin 
  Cc: mysql@lists.mysql.com 
  Sent: Tuesday, February 28, 2006 8:40 PM
  Subject: Re: UPDATE from monthly to yearly rows


  Cor,

  >I need to put all available monthly Values from Updates  
  >to 1 Data record where MyKey and Year are equal.

  IOW you want to save the results of the business end of a crosstab (pivot 
table) query. The 
  crosstab analysis will require a full query. MySQL has an INSERT ... SELECT 
command,
  but no UPDATE ... SELECT command, so this will be a two-step. If I understand 
your
  description correctly, you want to aggregate by month and report by mykey and 
year, so
  your crosstab would look something like this (not tested)...

CREATE TEMPORARY TABLE crosstab
SELECT 
  d.myKey,
  d.year,
  SUM(IF(u.month=1 ,u.value,0)) AS jan,
  SUM(IF(u.month=2 ,u.value,0)) AS feb,
  ... etc ...
  SUM(IF(u.month=12,u.value,0)) AS dec)
FROM data AS d 
INNER JOIN updates AS u USING (myKey)
GROUP BY mykey,year;

  aggregating updates to one row per mykey per year. Then update the data table
  with something like ...

  UPDATE Data AS d INNER JOIN crosstab AS c
  ON d.myKey = c.myKey AND d.year = c.year
  SET d.Jan = c.jan ... etc ...

  PB

  -

  C.R.Vegelin wrote: 
Hi List,

Please help me with the following problem in MySQL 5.0.15.
I have 2 MyISAM tables like:
- table Updates with fields myKey, Year, Month, Value
  where Month has the values 1 .. 12
- table Data with fields myKey, Year, Jan, Feb, ... Dec

I need to put all available monthly Values from Updates  
to 1 Data record where MyKey and Year are equal.
I tried the following query:

UPDATE Data AS db INNER JOIN Updates AS U
ON db.myKey = U.myKey
SET db.Jan = IF(U.Month = 1, U.Value, db.Jan), 
   db.Feb = IF(U.Month = 2, U.Value, db.Feb),
...
   db.Dec = IF(U.Month=12, U.Value,db.Dec);

But this query takes only the first available Month in Updates,
and ignores the other months per myKey / Year combination.
I would appreciate your help.

TIA, Cor
  
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
  

--


  No virus found in this outgoing message.
  Checked by AVG Free Edition.
  Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006