order by "version number"

[Stano Paska]

Hi,
in my table I have one varchar(20) column where I store version number.
Version looks like:
1.1.2
1.2.1
1.10.3
It is possible order this column in natural order (1.2 before 1.10)?
Stano.
--
Stanislav PaÅka
programÃtor, www skupina
KIOS s.r.o.
tel: 033 / 794 00 18

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

Re: Can't Access Via Webmin or PhpMyAdmin.

[Mark Sargent]

Mark Sargent wrote:
Hi All,
I can start mysql with the following cmd,
/etc/rc.d/init.d/mysql start
although, I had to change
bindir=./bin
to
bindir=/usr/local/mysql/bin
to get it to work. I can access the DB with,
mysql -h localhost -u root -p
successfully, but, when trying to access via phpmyadmin or webmin it 
doesn't work. I've set the config files to what I believe are correct.

phpmyadmin error below,
Error
*MySQL said: *Documentation 


| #2002 - Can't connect to local MySQL server through socket 
'/tmp/mysql.sock' (2)

||Now, I've got user as root and no password, as I haven't set 1 yet. 
Why am I getting this.? Anyone seen this before.?

With webmin, I get this displayed,
|*MySQL is not running on your system - database list could not be 
retrieved.

*Click this button to start the MySQL database server on your system 
with the command /etc/rc.d/init.d/mysql start. This Webmin module 
cannot administer the database until it is started.

even though the server is running, confirmed with the following,
[EMAIL PROTECTED] ~]# ps -uxwww | grep mysql
Warning: bad syntax, perhaps a bogus '-'? See 
/usr/share/doc/procps-3.2.3/FAQ
root  4640  0.0  0.2  4180 1116 ?S14:19   0:00 /bin/sh 
/usr/local/mysql/bin/mysqld_safe --datadir=/var/lib/mysql 
--pid-file=/var/lib/mysql/localhost.localdomain.pid
root  4771  0.0  0.0  1580  124 pts/2R+   14:36   0:00 grep mysql

Is anyone on here running webmin to manage mysql..? I'm curious how 
you got it to run..? If I stop the server via cli and then click the 
start mysql server button on the webmin page, it goes back to the same 
message, but, the server is started. I changed the config module page 
to reflect my installation. What have I missed, perhaps..?  Cheers.

Mark Sargent.
Hi All,
below is the config of webmin mysql module,
*Path to mysqlshow command* /usr/bin/mysqlshow
*Path to mysqladmin command*/usr/local/mysql/bin/mysqladmin
*Path to mysql command* /usr/local/mysql/bin/mysql
*Path to mysqldump command* /usr/local/mysql/bin/mysqldump
*Path to mysqlimport command*   /usr/local/mysql/bin/mysqlimport
*Command to start MySQL server* /etc/rc.d/init.d/mysql start
*Command to stop MySQL server*  /etc/rc.d/init.d/mysql stop
*Path to MySQL shared libraries directory*  
*Path to MySQL databases directory* /var/lib/mysql
I hope that helps. Cheers.
Mark Sargent.

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

Can't Access Via Webmin or PhpMyAdmin.

[Mark Sargent]

Hi All,
I can start mysql with the following cmd,
/etc/rc.d/init.d/mysql start
although, I had to change
bindir=./bin
to
bindir=/usr/local/mysql/bin
to get it to work. I can access the DB with,
mysql -h localhost -u root -p
successfully, but, when trying to access via phpmyadmin or webmin it 
doesn't work. I've set the config files to what I believe are correct.

phpmyadmin error below,
Error
*MySQL said: *Documentation 


| #2002 - Can't connect to local MySQL server through socket 
'/tmp/mysql.sock' (2)

||Now, I've got user as root and no password, as I haven't set 1 yet. 
Why am I getting this.? Anyone seen this before.?

With webmin, I get this displayed,
|*MySQL is not running on your system - database list could not be 
retrieved.

*Click this button to start the MySQL database server on your system 
with the command /etc/rc.d/init.d/mysql start. This Webmin module cannot 
administer the database until it is started.

even though the server is running, confirmed with the following,
[EMAIL PROTECTED] ~]# ps -uxwww | grep mysql
Warning: bad syntax, perhaps a bogus '-'? See 
/usr/share/doc/procps-3.2.3/FAQ
root  4640  0.0  0.2  4180 1116 ?S14:19   0:00 /bin/sh 
/usr/local/mysql/bin/mysqld_safe --datadir=/var/lib/mysql 
--pid-file=/var/lib/mysql/localhost.localdomain.pid
root  4771  0.0  0.0  1580  124 pts/2R+   14:36   0:00 grep mysql

Is anyone on here running webmin to manage mysql..? I'm curious how you 
got it to run..? If I stop the server via cli and then click the start 
mysql server button on the webmin page, it goes back to the same 
message, but, the server is started. I changed the config module page to 
reflect my installation. What have I missed, perhaps..?  Cheers.

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

InnoDB .frm files disappearing?

[Geoffrey R. Thompson]

We have been using MyISAM tables with MySQL merrily for about 18 months.
Recently we upgraded to MySQL 4.1, and even more recently, we converted some
of our MyISAM tables (which needed transactional support) to InnoDB.

 

After some configuration issues - the worst of which was the need to use a
symbolic link to redirect the data directories for MySQL to another disk
partition (our /usr/lib directory did not have sufficient space allocated to
support the data stores), we got things working, and they have been working
for about a month. Today, however, we lost all of the InnoDB tables in one
of our databases.

 

Upon examining the MySQL data directory, we discovered that the .frm files
for these three tables were gone (although the ibdata1 file was still
there).  These same tables were still working fine in another of our
databases (dev1 vs. dev2 - both of which share the same ibdata1 file), so we
copied the .frm files from that database's data directory over, and once we
did this, the tables re-appeared, complete with the correct data that had
been originally loaded into these tables prior to their disappearing.  So,
it appears that while the data file was fine, the .frm files were somehow
deleted.

 

Anyone seen this happen before?  Any insights would be very much
appreciated!

 

Regards,

 

Geoff Thompson

Avaion Support

[EMAIL PROTECTED]  

http://www.avaion.com  

 

re: using if in select statement

[James Black]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

My boss and I were playing with using select statements, and we can
actually execute subqueries as an option if the result is true or false.

Is this expected behavior, or is it something that may be fixed in a
revision, before I begin to depend on it being acceptable behavior.

Thanx.

- --
Corruptisima republica plurimae leges. [The more corrupt a republic, the
more laws.]
Tacitus from Annals III, 116AD
Blogs: http://jamesruminations.blogspot.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (MingW32)

iD8DBQFCcX2xJ/zyYkX46joRAgiVAJ9rw9BRPuT164/4wpYlHJbdj+x1agCcCbKG
fM7SPPMIo6QSWijniegUM9A=
=wK54
-END PGP SIGNATURE-

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

Re: No error / warning when data is truncated on insertion into mysql

[Paul DuBois]

At 19:39 -0400 4/28/05, Anoop kumar V wrote:
I mean:
Should I enable something like 'strict checking' / verbose so that
MYSQLcomplains and refrains from inserting truncated data in the
tables??
Yes, but you'll need MySQL 5.0 to do it.
http://dev.mysql.com/doc/mysql/en/server-sql-mode.html

Thanks,
Anoop
On 4/28/05, Anoop kumar V <[EMAIL PROTECTED]> wrote:
 Thank you,
 But the show warnings does not seem to work on my prompt. I am using
 mysql> SELECT VERSION();
 +---+
 | VERSION() |
 +---+
 | 4.0.23-nt |
 +---+
 And even if it did and does how will that reflect in my tomcat logs?? I
 mean I want a way where without user interaction any such warnings are
 recorded somewhere..
 Also why does it insert at all - I just checked with Sybase and previously
 MS Sql server - both display a very visible error message and DO NOT insert
 the data (although the bug filled MS SQL server said "data may have been
 truncated" when data was not even inserted!!)
 Should I enable somethink like strict checking so that MSSQL complains and
 refrains from inserting truncated data in the tables??
 Thanks and r,
 Anoop Kumar V.
 On 4/28/05, mathias fatene <[EMAIL PROTECTED]> wrote:
 >
 > Hi,
 > I think you shoul dcatch the "show warnings" command cause in mysql
 > client you see the number of warnings.
 > Data are even truncated according to the limit of the type (tinyint, int
 > ...).
 >
 > Example :
 > mysql> create table toto(a tinyint,b char(5));
 > Query OK, 0 rows affected (0.06 sec)
 >
 > mysql> insert into toto values (500,'Long text');
 > Query OK, 1 row affected, 2 warnings (0.02 sec)
 >
 > It's said here that i have 2 warnings.
 >
 > mysql> show warnings
 > -> ;
 > +-+--+--
 > +
 > | Level | Code | Message
 > |
 > +-+--+--
 >
 > +
 > | Warning | 1264 | Data truncated; out of range for column 'a' at row 1
 > |
 > | Warning | 1265 | Data truncated for column 'b' at row 1
 > |
 > +-+--+--
 >
 > +
 > 2 rows in set (0.00 sec)
 >
 > mysql> select * from toto;
 > +--+--+
 > | a | b |
 > +--+--+
 > | 127 | Long | < my 500 is also truncated
 > +--+--+
 > 1 row in set (0.00 sec)
 >
 > Best Regards
 > 
 > Mathias FATENE
 >
 > Hope that helps
 > *This not an official mysql support answer
 >
 >
 > -Original Message-
 > From: Anoop kumar V [mailto: [EMAIL PROTECTED]
 > Sent: vendredi 29 avril 2005 00:21
 > To: mysql@lists.mysql.com
 > Subject: No error / warning when data is truncated on insertion into
 > mysql
 >
 > I am using MySQL and SQL server with Tomcat.
 >
 > Our application writes into both databases (mysql and ms sql server) at
 > once
 > based on some data collected from an end user. Now if the end user
 > enters
 > more data (characters) than the column can hold, the data obviously gets
 >
 >
 > truncated.
 >
 > But the surprising thing is that although MS SQL server sends a warning
 > message to tomcat (seen on the tomcat console) that "data may have been
 > truncated" - MySQL does not show any warning message (I would have
 > expected
 > an error actually) as the data in the column is not what the data was
 > intended to be.
 > (Actually MS SQL shows the error and does not even insert the data...)
 >
 > Does MySQL not care or maybe I need to activate some option in MySQL
 > like
 > verbose or stict checking etc... It just truncated and inserted the data
 >
 > with no warning / error or any hassle!!
 >
 > how can i force mysql to check for such inconsistencies and report??
 > --
 > Thanks and best regards,
 > > Anoop
 >
 >
 --
 Thanks and best regards,
 Anoop

--
Thanks and best regards,
Anoop

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
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: No error / warning when data is truncated on insertion into mysql

[Anoop kumar V]

I mean:

Should I enable something like 'strict checking' / verbose so that
MYSQLcomplains and refrains from inserting truncated data in the
tables??

Thanks,
Anoop

On 4/28/05, Anoop kumar V <[EMAIL PROTECTED]> wrote:
> 
> Thank you,
> 
> But the show warnings does not seem to work on my prompt. I am using
> mysql> SELECT VERSION();
> +---+
> | VERSION() |
> +---+
> | 4.0.23-nt |
> +---+
> 
> And even if it did and does how will that reflect in my tomcat logs?? I 
> mean I want a way where without user interaction any such warnings are 
> recorded somewhere..
> 
> Also why does it insert at all - I just checked with Sybase and previously 
> MS Sql server - both display a very visible error message and DO NOT insert 
> the data (although the bug filled MS SQL server said "data may have been 
> truncated" when data was not even inserted!!)
> 
> Should I enable somethink like strict checking so that MSSQL complains and 
> refrains from inserting truncated data in the tables??
> 
> Thanks and r,
> Anoop Kumar V.
> 
> On 4/28/05, mathias fatene <[EMAIL PROTECTED]> wrote:
> > 
> > Hi,
> > I think you shoul dcatch the "show warnings" command cause in mysql
> > client you see the number of warnings.
> > Data are even truncated according to the limit of the type (tinyint, int
> > ...).
> > 
> > Example : 
> > mysql> create table toto(a tinyint,b char(5));
> > Query OK, 0 rows affected (0.06 sec)
> > 
> > mysql> insert into toto values (500,'Long text');
> > Query OK, 1 row affected, 2 warnings (0.02 sec)
> > 
> > It's said here that i have 2 warnings. 
> > 
> > mysql> show warnings
> > -> ;
> > +-+--+--
> > +
> > | Level | Code | Message
> > |
> > +-+--+-- 
> > 
> > +
> > | Warning | 1264 | Data truncated; out of range for column 'a' at row 1
> > |
> > | Warning | 1265 | Data truncated for column 'b' at row 1
> > |
> > +-+--+-- 
> > 
> > +
> > 2 rows in set (0.00 sec)
> > 
> > mysql> select * from toto;
> > +--+--+
> > | a | b |
> > +--+--+
> > | 127 | Long | < my 500 is also truncated
> > +--+--+
> > 1 row in set (0.00 sec)
> > 
> > Best Regards
> > 
> > Mathias FATENE
> > 
> > Hope that helps
> > *This not an official mysql support answer
> > 
> > 
> > -Original Message-
> > From: Anoop kumar V [mailto: [EMAIL PROTECTED]
> > Sent: vendredi 29 avril 2005 00:21
> > To: mysql@lists.mysql.com
> > Subject: No error / warning when data is truncated on insertion into
> > mysql
> > 
> > I am using MySQL and SQL server with Tomcat.
> > 
> > Our application writes into both databases (mysql and ms sql server) at
> > once
> > based on some data collected from an end user. Now if the end user
> > enters
> > more data (characters) than the column can hold, the data obviously gets 
> > 
> > 
> > truncated.
> > 
> > But the surprising thing is that although MS SQL server sends a warning
> > message to tomcat (seen on the tomcat console) that "data may have been
> > truncated" - MySQL does not show any warning message (I would have 
> > expected
> > an error actually) as the data in the column is not what the data was
> > intended to be.
> > (Actually MS SQL shows the error and does not even insert the data...)
> > 
> > Does MySQL not care or maybe I need to activate some option in MySQL 
> > like
> > verbose or stict checking etc... It just truncated and inserted the data
> > 
> > with no warning / error or any hassle!!
> > 
> > how can i force mysql to check for such inconsistencies and report??
> > --
> > Thanks and best regards, 
> > Anoop
> > 
> > 
> 
> 
> -- 
> Thanks and best regards,
> Anoop 
> 



-- 
Thanks and best regards,
Anoop

Re: No error / warning when data is truncated on insertion into mysql

[Anoop kumar V]

Thank you,

But the show warnings does not seem to work on my prompt. I am using
mysql> SELECT VERSION();
+---+
| VERSION() |
+---+
| 4.0.23-nt |
+---+

And even if it did and does how will that reflect in my tomcat logs?? I mean 
I want a way where without user interaction any such warnings are recorded 
somewhere..

Also why does it insert at all - I just checked with Sybase and previously 
MS Sql server - both display a very visible error message and DO NOT insert 
the data (although the bug filled MS SQL server said "data may have been 
truncated" when data was not even inserted!!)

Should I enable somethink like strict checking so that MSSQL complains and 
refrains from inserting truncated data in the tables??

Thanks and r,
Anoop Kumar V.

On 4/28/05, mathias fatene <[EMAIL PROTECTED]> wrote:
> 
> Hi,
> I think you shoul dcatch the "show warnings" command cause in mysql
> client you see the number of warnings.
> Data are even truncated according to the limit of the type (tinyint, int
> ...).
> 
> Example :
> mysql> create table toto(a tinyint,b char(5));
> Query OK, 0 rows affected (0.06 sec)
> 
> mysql> insert into toto values (500,'Long text');
> Query OK, 1 row affected, 2 warnings (0.02 sec)
> 
> It's said here that i have 2 warnings.
> 
> mysql> show warnings
> -> ;
> +-+--+--
> +
> | Level | Code | Message
> |
> +-+--+--
> +
> | Warning | 1264 | Data truncated; out of range for column 'a' at row 1
> |
> | Warning | 1265 | Data truncated for column 'b' at row 1
> |
> +-+--+--
> +
> 2 rows in set (0.00 sec)
> 
> mysql> select * from toto;
> +--+--+
> | a | b |
> +--+--+
> | 127 | Long | < my 500 is also truncated
> +--+--+
> 1 row in set (0.00 sec)
> 
> Best Regards
> 
> Mathias FATENE
> 
> Hope that helps
> *This not an official mysql support answer
> 
> 
> -Original Message-
> From: Anoop kumar V [mailto:[EMAIL PROTECTED]
> Sent: vendredi 29 avril 2005 00:21
> To: mysql@lists.mysql.com
> Subject: No error / warning when data is truncated on insertion into
> mysql
> 
> I am using MySQL and SQL server with Tomcat.
> 
> Our application writes into both databases (mysql and ms sql server) at
> once
> based on some data collected from an end user. Now if the end user
> enters
> more data (characters) than the column can hold, the data obviously gets
> 
> truncated.
> 
> But the surprising thing is that although MS SQL server sends a warning
> message to tomcat (seen on the tomcat console) that "data may have been
> truncated" - MySQL does not show any warning message (I would have
> expected
> an error actually) as the data in the column is not what the data was
> intended to be.
> (Actually MS SQL shows the error and does not even insert the data...)
> 
> Does MySQL not care or maybe I need to activate some option in MySQL
> like
> verbose or stict checking etc... It just truncated and inserted the data
> 
> with no warning / error or any hassle!!
> 
> how can i force mysql to check for such inconsistencies and report??
> --
> Thanks and best regards,
> Anoop
> 
> 


-- 
Thanks and best regards,
Anoop

Re: Find records that don't exist in MySQl 4.0

[Daniel Kasak]

Andrew Hargreaves wrote:

> In MySQL 4.0, is it possible to find all records that don't exist in one
>query. I managed to do it in Access by referencing a query that I had
>previously created, but I was wondering if it was possible to do this in one
>step.
>  
>
Not in one step, no. If you're still using Access you can of course set
up a query chain as in your example. But if you're using 'pure'
MySQL-4.0.x, you'll have to make a temporary table from the 1st query,
and then run your second query against this temp table.

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

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

RE: No error / warning when data is truncated on insertion into mysql

[mathias fatene]

Hi,
I think you shoul dcatch the "show warnings" command cause in mysql
client you see the number of warnings.
Data are even truncated according to the limit of the type (tinyint, int
...).

Example :
mysql> create table toto(a tinyint,b char(5));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into toto values (500,'Long text');
Query OK, 1 row affected, 2 warnings (0.02 sec)

It's said here that i have 2 warnings.

mysql> show warnings
-> ;
+-+--+--
+
| Level   | Code | Message
|
+-+--+--
+
| Warning | 1264 | Data truncated; out of range for column 'a' at row 1
|
| Warning | 1265 | Data truncated for column 'b' at row 1
|
+-+--+--
+
2 rows in set (0.00 sec)

mysql> select * from toto;
+--+--+
| a| b|
+--+--+
|  127 | Long |   < my 500 is also truncated
+--+--+
1 row in set (0.00 sec)



Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Anoop kumar V [mailto:[EMAIL PROTECTED] 
Sent: vendredi 29 avril 2005 00:21
To: mysql@lists.mysql.com
Subject: No error / warning when data is truncated on insertion into
mysql


I am using MySQL and SQL server with Tomcat. 

Our application writes into both databases (mysql and ms sql server) at
once 
based on some data collected from an end user. Now if the end user
enters 
more data (characters) than the column can hold, the data obviously gets

truncated.

But the surprising thing is that although MS SQL server sends a warning 
message to tomcat (seen on the tomcat console) that "data may have been 
truncated" - MySQL does not show any warning message (I would have
expected 
an error actually) as the data in the column is not what the data was 
intended to be.
(Actually MS SQL shows the error and does not even insert the data...)

Does MySQL not care or maybe I need to activate some option in MySQL
like 
verbose or stict checking etc... It just truncated and inserted the data

with no warning / error or any hassle!!

how can i force mysql to check for such inconsistencies and report??
-- 
Thanks and best regards,
Anoop


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

No error / warning when data is truncated on insertion into mysql

[Anoop kumar V]

I am using MySQL and SQL server with Tomcat. 

Our application writes into both databases (mysql and ms sql server) at once 
based on some data collected from an end user. Now if the end user enters 
more data (characters) than the column can hold, the data obviously gets 
truncated.

But the surprising thing is that although MS SQL server sends a warning 
message to tomcat (seen on the tomcat console) that "data may have been 
truncated" - MySQL does not show any warning message (I would have expected 
an error actually) as the data in the column is not what the data was 
intended to be.
(Actually MS SQL shows the error and does not even insert the data...)

Does MySQL not care or maybe I need to activate some option in MySQL like 
verbose or stict checking etc... It just truncated and inserted the data 
with no warning / error or any hassle!!

how can i force mysql to check for such inconsistencies and report??
-- 
Thanks and best regards,
Anoop

Find records that don't exist in MySQl 4.0

[Andrew Hargreaves]

 In MySQL 4.0, is it possible to find all records that don't exist in one
query. I managed to do it in Access by referencing a query that I had
previously created, but I was wondering if it was possible to do this in one
step.

SELECT C.CustomerID, ([JobStatus]="0" And [JobTypeID]=2) AS KountNA,
Count([KountNA]) AS KountComplete
FROM Workorders AS W INNER JOIN Customers AS C ON W.CustomerID =
C.CustomerID
GROUP BY C.CustomerID, ([JobStatus]="0" And [JobTypeID]=2)
HAVING [JobStatus]="0" And [JobTypeID]=2))=-1));

Would show all Customers that have JobTypeID=2 and JobStatus="0". I realise
that the KountComplete expression is not required.

I then used a RIGHT JOIN to filter out all the records from the Customers
table not in this query, as below:

SELECT C.CustomerID, QC.KountComplete, C.NameNumb, C.BillingAddress, C.City,
C.PostalCode
FROM qryCountCompleteServices AS QC RIGHT JOIN Customers AS C ON
QC.CustomerID = C.CustomerID
WHERE (QC.KountNA) Is Null);

So, can I do that without using a sub-query in MySQL 4.0? 

Thanks

Andrew 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 27/04/2005
 

Re: zip code search within x miles

[Jeremy Cole]

Hi,
known as "covering indexes".  The advantage to a covering index is that if 
your data is numeric and in the index, the engine can read the data 
All correct, except that the data does not have to be numeric.  It must, 
however, be completely indexed, not indexed by prefix.  That requirement 
usually excludes most BLOB/TEXT fields, so you wouldn't normally include 
a BLOB/TEXT in a covered index discussion.

Covered indexes work fine with CHAR/VARCHAR and are quite common.
Regards,
Jeremy
--
Jeremy Cole
Technical Yahoo - MySQL (Database) Geek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

memory error & innodb backup

[Baba Buehler]

We've got a customer whose system has been experiencing corruption in 
their InnoDB tables.  They have returned the system to us and after 
testing we've determined that the server has bad RAM.

This is the same system I had posted about in 
http://lists.mysql.com/mysql/180785

Memtest86+ v1.55 can run anywhere from 12-24 hours before it picks up 
any memory errors, so the problem with the memory (or memory subsystem) 
is very intermittent.

We have shipped this customer a replacement server and they have 
restored their database from a backup that was made with the InnoDB hot 
backup utility (ibbackup, 1.40).

They are now experiencing corruption in their database again on the new 
system.

My question is this:  Is it possible that the backup made from the 
system with the failing RAM is itself corrupted somehow?

If there was a corrupt page in the tablespace, would it generate an 
error during the ibbackup run?  Would the restore process be able to 
uncover an error within the backup?  Or is it possible that a corrupted 
page could survive through the backup/restore process and not be 
uncovered until it was accessed on the new system?

thanks,
baba
--
Baba Buehler - NetBotz, Inc. - <[EMAIL PROTECTED]>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Crystal Reports & MySQL

[Fredrick Bartlett]

Hmmm, they repeated several times...
 Under the agreement, Business Objects will embed MySQL database technology 
into BusinessObjects XI for use on the Linux and UNIX 

"Linux and UNIX" how can they determine the server is not running on Win32??? 
Strange. Crystal Reports is a Win32 app.

- Original Message - 
From: "Scott Pippin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, April 28, 2005 7:10 AM
Subject: Re: Crystal Reports & MySQL


I haven't tested it but I am using version 10 with no problems.  See the 
announcement made by business objects at the MySQL users conference:  
http://www.mysql.com/news-and-events/press-release/release_2005_10.html
 
I hope this helps.
 
Scott Pippin
[EMAIL PROTECTED]

>>> "Kerry Frater" <[EMAIL PROTECTED]> 04/28/05 6:09 AM >>>

I have MySQL & the downloaded ODBC drivers.

I am looking to use Crystal Reports to design & run my own reports on my
tables.

Can anyone tell me if the V11 Standard will happily work with MySQL or do I
need a different version?

Kerry

RE: mysql top 2 rows for each group

[mathias fatene]

For your query, just a where clause :

mysql> select * from seqs where id <3;
+---++
| seqno | id |
+---++
| 00122 |  1 |
| 00123 |  1 |
| 00123 |  2 |
| 00336 |  1 |
| 00346 |  1 |
| 00349 |  1 |
| 00427 |  1 |
| 00427 |  2 |
+---++
8 rows in set (0.00 sec)


Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: mathias fatene [mailto:[EMAIL PROTECTED] 
Sent: jeudi 28 avril 2005 22:52
To: 'Jay Blanchard'; 'Vivian Wang'; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group


Hi ,
The table must be myisam. Innodb refused my solution which is here :
 Beatifull
auto_increment
mysql> create table seqs(seqno varchar(10) NOT NULL , id int
auto_increment, primary key (seqno,id)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql>
mysql> insert into seqs(seqno) values('00122'),
->('00123'),
-> ('00123'),
->  ('00123'),
-> ('00336'),
-> ('00346'),
-> ('00349'),
-> ('00427'),
->  ('00427'),
->('00427');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from seqs;
+---++
| seqno | id |
+---++
| 00122 |  1 |
| 00123 |  1 |
| 00123 |  2 |
| 00123 |  3 |
| 00336 |  1 |
| 00346 |  1 |
| 00349 |  1 |
| 00427 |  1 |
| 00427 |  2 |
| 00427 |  3 |
+---++
10 rows in set (0.00 sec)
- I like this type
of auto_increment

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED] 
Sent: jeudi 28 avril 2005 22:24
To: Vivian Wang; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group


[snip]
I have question about how I can get top 2 rows for each group. like I
have table test
| seqno |
+---+
| 00122 |
| 00123 |
| 00123 |
| 00123 | 
| 00336 |
| 00346 |
| 00349 |
| 00427 |
| 00427 |
| 00427 |
+---+--+

I like have
+---+--+
| seqno | item |
+---+--+
| 00122 |  1 |
| 00123 |  1 |
| 00123 |   2 |
| 00123 |3 |
| 00336 |  1 |
| 00346 |  1 |
| 00349 |  1 |
| 00427 |  1 |
| 00427 |   2 |
| 00427 |3 |
+---+--+

Then I can have select * from test where item <3 to find all top 2 rows.
[/snip]

I think you want ...

SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2

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



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



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

RE: Crystal Reports & MySQL

[Scott Pippin]

Should be.  You should be able to download a trial and try it out.

>>> "Kerry Frater" <[EMAIL PROTECTED]> 04/28/05 3:26 PM >>>
Thanks for the reply Scott. I found the press release interesting.

I have V9 Dev version and need runtime version for another site. I can buy
V11 but wasn't sure if the STD version was enough to set a report and run it
with MySQL.

Kerry
  -Original Message-
  From: Scott Pippin [mailto:[EMAIL PROTECTED]
  Sent: 28 April 2005 15:11
  To: [EMAIL PROTECTED]
  Cc: mysql@lists.mysql.com
  Subject: Re: Crystal Reports & MySQL


  I haven't tested it but I am using version 10 with no problems.  See the
announcement made by business objects at the MySQL users conference:
http://www.mysql.com/news-and-events/press-release/release_2005_10.html

  I hope this helps.

  Scott Pippin
  [EMAIL PROTECTED]

  >>> "Kerry Frater" <[EMAIL PROTECTED]> 04/28/05 6:09 AM >>>

  I have MySQL & the downloaded ODBC drivers.

  I am looking to use Crystal Reports to design & run my own reports on my
  tables.

  Can anyone tell me if the V11 Standard will happily work with MySQL or do
I
  need a different version?

  Kerry

RE: Crystal Reports & MySQL

[Kerry Frater]

Thanks for the reply Scott. I found the press release interesting.

I have V9 Dev version and need runtime version for another site. I can buy
V11 but wasn't sure if the STD version was enough to set a report and run it
with MySQL.

Kerry
  -Original Message-
  From: Scott Pippin [mailto:[EMAIL PROTECTED]
  Sent: 28 April 2005 15:11
  To: [EMAIL PROTECTED]
  Cc: mysql@lists.mysql.com
  Subject: Re: Crystal Reports & MySQL


  I haven't tested it but I am using version 10 with no problems.  See the
announcement made by business objects at the MySQL users conference:
http://www.mysql.com/news-and-events/press-release/release_2005_10.html

  I hope this helps.

  Scott Pippin
  [EMAIL PROTECTED]

  >>> "Kerry Frater" <[EMAIL PROTECTED]> 04/28/05 6:09 AM >>>

  I have MySQL & the downloaded ODBC drivers.

  I am looking to use Crystal Reports to design & run my own reports on my
  tables.

  Can anyone tell me if the V11 Standard will happily work with MySQL or do
I
  need a different version?

  Kerry

RE: mysql top 2 rows for each group

[SGreen]

"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 04/28/2005 
04:24:23 PM:

> [snip]
> I have question about how I can get top 2 rows for each group.
> like I have table test
> | seqno |
> +---+
> | 00122 | 
> | 00123 |
> | 00123 | 
> | 00123 | 
> | 00336 |
> | 00346 |
> | 00349 |
> | 00427 |
> | 00427 |
> | 00427 |
> +---+--+
> 
> I like have
> +---+--+
> | seqno | item |
> +---+--+
> | 00122 |  1 |
> | 00123 |  1 |
> | 00123 |   2 |
> | 00123 |3 |
> | 00336 |  1 |
> | 00346 |  1 |
> | 00349 |  1 |
> | 00427 |  1 |
> | 00427 |   2 |
> | 00427 |3 |
> +---+--+
> 
> Then I can have select * from test where item <3 to find all top 2 rows.
> [/snip]
> 
> I think you want ...
> 
> SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2
> 

I think that will result in only two rows total, not two per group. 

Vivian? What is the PK for your table? What value or combination of values 
uniqely identifies each row of your source table? It can't be seqno as you 
already demonstrated that there are duplicate values in that column. I may 
have an idea but I need to know more about your data. Posting the results 
of SHOW CREATE TABLE xxx\G for your source table would be ideal.

Thanks,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: mysql top 2 rows for each group

[mathias fatene]

Hi ,
The table must be myisam. Innodb refused my solution which is here :
 Beatifull
auto_increment
mysql> create table seqs(seqno varchar(10) NOT NULL , id int
auto_increment, primary key (seqno,id)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql>
mysql> insert into seqs(seqno) values('00122'),
->('00123'),
-> ('00123'),
->  ('00123'),
-> ('00336'),
-> ('00346'),
-> ('00349'),
-> ('00427'),
->  ('00427'),
->('00427');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from seqs;
+---++
| seqno | id |
+---++
| 00122 |  1 |
| 00123 |  1 |
| 00123 |  2 |
| 00123 |  3 |
| 00336 |  1 |
| 00346 |  1 |
| 00349 |  1 |
| 00427 |  1 |
| 00427 |  2 |
| 00427 |  3 |
+---++
10 rows in set (0.00 sec)
- I like this type
of auto_increment

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED] 
Sent: jeudi 28 avril 2005 22:24
To: Vivian Wang; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group


[snip]
I have question about how I can get top 2 rows for each group. like I
have table test
| seqno |
+---+
| 00122 |
| 00123 |
| 00123 |  
| 00123 | 
| 00336 |
| 00346 |
| 00349 |
| 00427 |
| 00427 |
| 00427 |
+---+--+

I like have
+---+--+
| seqno | item |
+---+--+
| 00122 |  1 |
| 00123 |  1 |
| 00123 |   2 |
| 00123 |3 |
| 00336 |  1 |
| 00346 |  1 |
| 00349 |  1 |
| 00427 |  1 |
| 00427 |   2 |
| 00427 |3 |
+---+--+

Then I can have select * from test where item <3 to find all top 2 rows.
[/snip]

I think you want ...

SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2

-- 
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 top 2 rows for each group

[Jay Blanchard]

[snip]
I have question about how I can get top 2 rows for each group.
like I have table test
| seqno |
+---+
| 00122 | 
| 00123 |
| 00123 |  
| 00123 | 
| 00336 |
| 00346 |
| 00349 |
| 00427 |
| 00427 |
| 00427 |
+---+--+

I like have
+---+--+
| seqno | item |
+---+--+
| 00122 |  1 |
| 00123 |  1 |
| 00123 |   2 |
| 00123 |3 |
| 00336 |  1 |
| 00346 |  1 |
| 00349 |  1 |
| 00427 |  1 |
| 00427 |   2 |
| 00427 |3 |
+---+--+

Then I can have select * from test where item <3 to find all top 2 rows.
[/snip]

I think you want ...

SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2

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

mysql top 2 rows for each group

[Vivian Wang]

I have question about how I can get top 2 rows for each group.
like I have table test
| seqno |
+---+
| 00122 | 
| 00123 |
| 00123 |  
| 00123 | 
| 00336 |
| 00346 |
| 00349 |
| 00427 |
| 00427 |
| 00427 |
+---+--+

I like have
+---+--+
| seqno | item |
+---+--+
| 00122 |  1 |
| 00123 |  1 |
| 00123 |   2 |
| 00123 |3 |
| 00336 |  1 |
| 00346 |  1 |
| 00349 |  1 |
| 00427 |  1 |
| 00427 |   2 |
| 00427 |3 |
+---+--+
Then I can have select * from test where item <3 to find all top 2 rows.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Does anyone have experience?

[Berman, Mikhail]

I do believe that a problem is with SigmaPlot itself, because of all
research we have done. And, an additional fact that SigmaPlot ODBC
connection works properly with DSN created for MS-Access, but not with
DSN to MySQL.
 
Best
 
Mikhail Berman



From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 28, 2005 3:27 PM
To: mathias fatene
Cc: Berman, Mikhail; mysql@lists.mysql.com
Subject: RE: Does anyone have experience?



MySQL is not listed in the screenshot because that is a sample
screenshot provided by the **manufacturer**, not a screen shot from his
installation. I was trying to use it to get a sense of how many
connection-specific options he may have had control over from within
SigmaPlot. Basically, it appears that if he has already created (and
tested) a MySQL DSN in the ODBC manager, it should show up in the list.
If picking it from the list didn't work then I think the program is
probably broken. I think he shares that opinion as his last post said he
was taking the issue up with the manufacturer. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 


"mathias fatene" <[EMAIL PROTECTED]> wrote on 04/28/2005 03:22:55 PM:

> Hi,
> Had you read http://dev.mysql.com/doc/mysql/en/odbc-connector.html
> 
> One can't see the mysql ODBC driver in your snapshot.
> 
> Best Regards
> 
> Mathias FATENE
>  
> Hope that helps
> *This not an official mysql support answer
>  
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: jeudi 28 avril 2005 21:02
> To: Berman, Mikhail
> Cc: mysql@lists.mysql.com
> Subject: RE: Does anyone have experience?
> 
> 
> Thank you for being so patient with me. What options does SigmaPlot
give
> 
> you when selecting an ODBC data source? Have you verified that you are

> either using the default settings (as you defined when you set up the 
> connection) or that you are using the same credentials you used to
test 
> your ODBC connection?
> 
> Looking at this screen shot 
>
(http://www.systat.com/products/SigmaPlot/productinfo/pop_nf_odimp.html)
> I 
> think the ODBC datasource interface gives you the choices to use a DSN
> you 
> already created or to make another one from scratch.   If selecting an

> already-tested and working DSN from this list continues to fail, then
I 
> think this is an issue you need to take up with the manufacturer of 
> SigmaPlot. After further research I found out the using ODBC is *new*
to
> 
> v9.0. Some new features still have some bugs to work out and your
> problems 
> may be caused by one of those.
> 
> As a workaround, you may be able to use Access or Excel as crutches to
> get 
> at your MySQL data then get the data from one of them into SigmaPlot. 
> However, if you can use your MySQL server and you can use an ODBC 
> connection with another program to get at your MySQL data then it
seems 
> very likely to me that the problem is going to be in SigmaPlot. Have
you
> 
> tried their online forums or their "contact a technician" links? 
> (http://www.systat.com/products/SigmaPlot/resources/?sec=1019)
> 
> Again, thank you for your patience and I am very sorry I couldn't be
> more 
> helpful,
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 02:13:29
PM:
> 
> > See inserts below
> > 
> > 
> > 
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, April 28, 2005 12:36 PM
> > To: Berman, Mikhail
> > Cc: mysql@lists.mysql.com
> > Subject: RE: Does anyone have experience?
> > 
> > 
> > 
> > OK, I am still confused. Let's run down the list of what's working
and
> 
> > what isn't:
> > 
> > a) In the ODBC manager, create a System DSN and click on the TEST 
> > CONNECTION button. What happens?
> > 
> > ODBC manager returns - "Success. Connection was made"
> > 
> > b) In SigmaPlot, tell the software to use the connection you just 
> > created and tested. What happens?
> > 
> > SigmaPlot returns - "Cannot connect to data source"
> > 
> > If we can't get the ODBC manager to connect, nothing else using that

> > DSN can possibly connect. The fact that your MySQL database is in a 
> > different machine running a different OS is not important. What is 
> > important is that you are using a user account to make your
connection
> 
> > (a MySQL user account, NOT an OS user account) that has privileges
and
> 
> > that you can connect to the server and authenticate with that 
> > account's credentials.
> > 
> > If for some reason there is a firewall between your XP machine and 
> > your MySQL server, that can also cause a failure to connect. Can you

> > ping the server from your XP machine? Can you telnet from your XP 
> > machine to your MySQL server on port 3660?  (You cannot create a 
> > normal telnet session with a MySQL server. However, if you can see
the
> 
> > version of the server surrounded

Re: purge log fails

[Marten Lehmann]

MySQL could die during your query. What is in error log?
Oh my god, it's really dieing. I haven't looked in the error log before, 
because I though, it's just this connection that got lost. This is the 
error-log output:

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help 
diagnose
the problem, but since we have already crashed, something is definitely 
wrong
and this may fail.

key_buffer_size=402653184
read_buffer_size=2093056
max_used_connections=24
max_connections=1000
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections 
= 290904 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0xa3500490
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbe5fe748, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80b2589
0x82cb328
0x808f73b
0x808ce7e
0x80db3d5
0x80ca97b
0x80c45e1
0x80c4226
0x80c3a1d
0x82c6c21
0x82f916a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and 
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xa68e960 = purge master logs before (select 
adddate(current_timestamp(), interval -4 day))
thd->thread_id=106601
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
050428 21:44:17  mysqld restarted
050428 21:44:17  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...
050428 21:44:18  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 1226476.
InnoDB: Doing recovery: scanned up to log sequence number 0 1226476
InnoDB: Last MySQL binlog file position 0 79, file name ./vm23-bin.000102
050428 21:44:18  InnoDB: Flushing modified pages from the buffer pool...
050428 21:44:18  InnoDB: Started; log sequence number 0 1226476
/usr/mysql/mysql-4.1.11/libexec/mysqld: ready for connections.
Version: '4.1.11-log'  socket: '/tmp/mysql.sock'  port: 3306  Source 
distribution

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

RE: Does anyone have experience?

[SGreen]

MySQL is not listed in the screenshot because that is a sample screenshot 
provided by the **manufacturer**, not a screen shot from his installation. 
I was trying to use it to get a sense of how many connection-specific 
options he may have had control over from within SigmaPlot. Basically, it 
appears that if he has already created (and tested) a MySQL DSN in the 
ODBC manager, it should show up in the list. If picking it from the list 
didn't work then I think the program is probably broken. I think he shares 
that opinion as his last post said he was taking the issue up with the 
manufacturer.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"mathias fatene" <[EMAIL PROTECTED]> wrote on 04/28/2005 03:22:55 PM:

> Hi,
> Had you read http://dev.mysql.com/doc/mysql/en/odbc-connector.html
> 
> One can't see the mysql ODBC driver in your snapshot.
> 
> Best Regards
> 
> Mathias FATENE
> 
> Hope that helps
> *This not an official mysql support answer
> 
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: jeudi 28 avril 2005 21:02
> To: Berman, Mikhail
> Cc: mysql@lists.mysql.com
> Subject: RE: Does anyone have experience?
> 
> 
> Thank you for being so patient with me. What options does SigmaPlot give
> 
> you when selecting an ODBC data source? Have you verified that you are 
> either using the default settings (as you defined when you set up the 
> connection) or that you are using the same credentials you used to test 
> your ODBC connection?
> 
> Looking at this screen shot 
> (http://www.systat.com/products/SigmaPlot/productinfo/pop_nf_odimp.html)
> I 
> think the ODBC datasource interface gives you the choices to use a DSN
> you 
> already created or to make another one from scratch.   If selecting an 
> already-tested and working DSN from this list continues to fail, then I 
> think this is an issue you need to take up with the manufacturer of 
> SigmaPlot. After further research I found out the using ODBC is *new* to
> 
> v9.0. Some new features still have some bugs to work out and your
> problems 
> may be caused by one of those.
> 
> As a workaround, you may be able to use Access or Excel as crutches to
> get 
> at your MySQL data then get the data from one of them into SigmaPlot. 
> However, if you can use your MySQL server and you can use an ODBC 
> connection with another program to get at your MySQL data then it seems 
> very likely to me that the problem is going to be in SigmaPlot. Have you
> 
> tried their online forums or their "contact a technician" links? 
> (http://www.systat.com/products/SigmaPlot/resources/?sec=1019)
> 
> Again, thank you for your patience and I am very sorry I couldn't be
> more 
> helpful,
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 02:13:29 PM:
> 
> > See inserts below
> > 
> > 
> > 
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, April 28, 2005 12:36 PM
> > To: Berman, Mikhail
> > Cc: mysql@lists.mysql.com
> > Subject: RE: Does anyone have experience?
> > 
> > 
> > 
> > OK, I am still confused. Let's run down the list of what's working and
> 
> > what isn't:
> > 
> > a) In the ODBC manager, create a System DSN and click on the TEST 
> > CONNECTION button. What happens?
> > 
> > ODBC manager returns - "Success. Connection was made"
> > 
> > b) In SigmaPlot, tell the software to use the connection you just 
> > created and tested. What happens?
> > 
> > SigmaPlot returns - "Cannot connect to data source"
> > 
> > If we can't get the ODBC manager to connect, nothing else using that 
> > DSN can possibly connect. The fact that your MySQL database is in a 
> > different machine running a different OS is not important. What is 
> > important is that you are using a user account to make your connection
> 
> > (a MySQL user account, NOT an OS user account) that has privileges and
> 
> > that you can connect to the server and authenticate with that 
> > account's credentials.
> > 
> > If for some reason there is a firewall between your XP machine and 
> > your MySQL server, that can also cause a failure to connect. Can you 
> > ping the server from your XP machine? Can you telnet from your XP 
> > machine to your MySQL server on port 3660?  (You cannot create a 
> > normal telnet session with a MySQL server. However, if you can see the
> 
> > version of the server surrounded by several lines of gibberish, this 
> > telnet test was successful. )
> > 
> > I work freely with MySQL servers from XP machine as a part of my daily
> 
> > routine.
> > 
> > The reason I keep going back to ODBC is that I want to make absolutely
> 
> > certain that this is not the weak link. If all ODBC tests are good 
> > then we need to look at the connection between SigmaPlot and ODBC as 
> > the problem.
> > 
> > 
> > Shawn Green
> > Database Administrator
> > Un

RE: Does anyone have experience?

[mathias fatene]

Hi,
Had you read http://dev.mysql.com/doc/mysql/en/odbc-connector.html

One can't see the mysql ODBC driver in your snapshot.

Best Regards

Mathias FATENE
 
Hope that helps
*This not an official mysql support answer
 


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: jeudi 28 avril 2005 21:02
To: Berman, Mikhail
Cc: mysql@lists.mysql.com
Subject: RE: Does anyone have experience?


Thank you for being so patient with me. What options does SigmaPlot give

you when selecting an ODBC data source? Have you verified that you are 
either using the default settings (as you defined when you set up the 
connection) or that you are using the same credentials you used to test 
your ODBC connection?

Looking at this screen shot 
(http://www.systat.com/products/SigmaPlot/productinfo/pop_nf_odimp.html)
I 
think the ODBC datasource interface gives you the choices to use a DSN
you 
already created or to make another one from scratch.   If selecting an 
already-tested and working DSN from this list continues to fail, then I 
think this is an issue you need to take up with the manufacturer of 
SigmaPlot. After further research I found out the using ODBC is *new* to

v9.0. Some new features still have some bugs to work out and your
problems 
may be caused by one of those.

As a workaround, you may be able to use Access or Excel as crutches to
get 
at your MySQL data then get the data from one of them into SigmaPlot. 
However, if you can use your MySQL server and you can use an ODBC 
connection with another program to get at your MySQL data then it seems 
very likely to me that the problem is going to be in SigmaPlot. Have you

tried their online forums or their "contact a technician" links? 
(http://www.systat.com/products/SigmaPlot/resources/?sec=1019)

Again, thank you for your patience and I am very sorry I couldn't be
more 
helpful,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 02:13:29 PM:

> See inserts below
> 
> 
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 28, 2005 12:36 PM
> To: Berman, Mikhail
> Cc: mysql@lists.mysql.com
> Subject: RE: Does anyone have experience?
> 
> 
> 
> OK, I am still confused. Let's run down the list of what's working and

> what isn't:
> 
> a) In the ODBC manager, create a System DSN and click on the TEST 
> CONNECTION button. What happens?
> 
> ODBC manager returns - "Success. Connection was made"
> 
> b) In SigmaPlot, tell the software to use the connection you just 
> created and tested. What happens?
> 
> SigmaPlot returns - "Cannot connect to data source"
> 
> If we can't get the ODBC manager to connect, nothing else using that 
> DSN can possibly connect. The fact that your MySQL database is in a 
> different machine running a different OS is not important. What is 
> important is that you are using a user account to make your connection

> (a MySQL user account, NOT an OS user account) that has privileges and

> that you can connect to the server and authenticate with that 
> account's credentials.
> 
> If for some reason there is a firewall between your XP machine and 
> your MySQL server, that can also cause a failure to connect. Can you 
> ping the server from your XP machine? Can you telnet from your XP 
> machine to your MySQL server on port 3660?  (You cannot create a 
> normal telnet session with a MySQL server. However, if you can see the

> version of the server surrounded by several lines of gibberish, this 
> telnet test was successful. )
> 
> I work freely with MySQL servers from XP machine as a part of my daily

> routine.
> 
> The reason I keep going back to ODBC is that I want to make absolutely

> certain that this is not the weak link. If all ODBC tests are good 
> then we need to look at the connection between SigmaPlot and ODBC as 
> the problem.
> 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> 
> 
> "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 11:59:27 
> AM:
> 
> > Hi,
> > 
> > I do use Data Sources(ODBC) manager to create DSNs.
> > 
> > Either User or System DSN failed to connect from SysPlot to UNIX
> > databases, with the same error message "Cannot connect to data
source"
> 
> > 
> > 
> > Sorry I was not precise in description, I have mentioned MS-Access
> > vs. Excel only to raised a point that ODBC should work similarly 
> > with both tools, but it does not. 
> > I am aware of row limitation for Excel so my test are ran against 
> > the tables that are under Excel limitations 
> > 
> > Regards,
> > 
> > Mikhail Berman
> > 
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, April 28, 2005 11:41 AM
> > To: Berman, Mikhail
> > Cc: mysql@lists.mysql.com
> > Subject: RE: Does anyone have experience?
> 
> > 
> > 
> > "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:50:17
> AM:
> > 

RE: Does anyone have experience?

[Berman, Mikhail]

Shawn,
 
I would like to thank you for your great help, no apologies needed.  I
have gone through most of the steps describe by you myself in the search
for the answers.
 
I am going to take this up with Systat Software people, now.
 
Thank you again,
 
Mikhail Berman



From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 28, 2005 3:02 PM
To: Berman, Mikhail
Cc: mysql@lists.mysql.com
Subject: RE: Does anyone have experience?



Thank you for being so patient with me. What options does SigmaPlot give
you when selecting an ODBC data source? Have you verified that you are
either using the default settings (as you defined when you set up the
connection) or that you are using the same credentials you used to test
your ODBC connection? 

Looking at this screen shot
(http://www.systat.com/products/SigmaPlot/productinfo/pop_nf_odimp.html)
I think the ODBC datasource interface gives you the choices to use a DSN
you already created or to make another one from scratch.   If selecting
an already-tested and working DSN from this list continues to fail, then
I think this is an issue you need to take up with the manufacturer of
SigmaPlot. After further research I found out the using ODBC is *new* to
v9.0. Some new features still have some bugs to work out and your
problems may be caused by one of those. 

As a workaround, you may be able to use Access or Excel as crutches to
get at your MySQL data then get the data from one of them into
SigmaPlot. However, if you can use your MySQL server and you can use an
ODBC connection with another program to get at your MySQL data then it
seems very likely to me that the problem is going to be in SigmaPlot.
Have you tried their online forums or their "contact a technician"
links? (http://www.systat.com/products/SigmaPlot/resources/?sec=1019) 

Again, thank you for your patience and I am very sorry I couldn't be
more helpful, 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

"Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 02:13:29 PM:

> See inserts below 
> 
> 
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, April 28, 2005 12:36 PM
> To: Berman, Mikhail
> Cc: mysql@lists.mysql.com
> Subject: RE: Does anyone have experience?
> 
> 
> 
> OK, I am still confused. Let's run down the list of what's working and
> what isn't: 
> 
> a) In the ODBC manager, create a System DSN and click on the TEST
> CONNECTION button. What happens?  
>  
> ODBC manager returns - "Success. Connection was made" 
> 
> b) In SigmaPlot, tell the software to use the connection you just
> created and tested. What happens?  
>  
> SigmaPlot returns - "Cannot connect to data source" 
> 
> If we can't get the ODBC manager to connect, nothing else using that
DSN
> can possibly connect. The fact that your MySQL database is in a
> different machine running a different OS is not important. What is
> important is that you are using a user account to make your connection
> (a MySQL user account, NOT an OS user account) that has privileges and
> that you can connect to the server and authenticate with that
account's
> credentials. 
> 
> If for some reason there is a firewall between your XP machine and
your
> MySQL server, that can also cause a failure to connect. Can you ping
the
> server from your XP machine? Can you telnet from your XP machine to
your
> MySQL server on port 3660?  (You cannot create a normal telnet session
> with a MySQL server. However, if you can see the version of the server
> surrounded by several lines of gibberish, this telnet test was
> successful. )  
>  
> I work freely with MySQL servers from XP machine as a part of my daily
> routine. 
> 
> The reason I keep going back to ODBC is that I want to make absolutely
> certain that this is not the weak link. If all ODBC tests are good
then
> we need to look at the connection between SigmaPlot and ODBC as the
> problem. 
>  
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine 
> 
> 
> 
> "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 11:59:27
AM:
> 
> > Hi, 
> >   
> > I do use Data Sources(ODBC) manager to create DSNs. 
> >   
> > Either User or System DSN failed to connect from SysPlot to UNIX 
> > databases, with the same error message "Cannot connect to data
source"
> 
> >   
> >   
> > Sorry I was not precise in description, I have mentioned MS-Access 
> > vs. Excel only to raised a point that ODBC should work similarly 
> > with both tools, but it does not. 
> > I am aware of row limitation for Excel so my test are ran against 
> > the tables that are under Excel limitations 
> >   
> > Regards, 
> >   
> > Mikhail Berman 
> > 
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> > Sent: Thursday, April 28, 2005 11:41 AM
> > To: Berman, Mikhail
> > Cc: mysql@lists.mysql.com
> > Subject: RE: Does anyone have experience?
> 
> > 
> > 
> > "Berman, Mikhail" <[EMAIL PROTE

RE: Does anyone have experience?

[SGreen]

Thank you for being so patient with me. What options does SigmaPlot give 
you when selecting an ODBC data source? Have you verified that you are 
either using the default settings (as you defined when you set up the 
connection) or that you are using the same credentials you used to test 
your ODBC connection?

Looking at this screen shot 
(http://www.systat.com/products/SigmaPlot/productinfo/pop_nf_odimp.html) I 
think the ODBC datasource interface gives you the choices to use a DSN you 
already created or to make another one from scratch.   If selecting an 
already-tested and working DSN from this list continues to fail, then I 
think this is an issue you need to take up with the manufacturer of 
SigmaPlot. After further research I found out the using ODBC is *new* to 
v9.0. Some new features still have some bugs to work out and your problems 
may be caused by one of those.

As a workaround, you may be able to use Access or Excel as crutches to get 
at your MySQL data then get the data from one of them into SigmaPlot. 
However, if you can use your MySQL server and you can use an ODBC 
connection with another program to get at your MySQL data then it seems 
very likely to me that the problem is going to be in SigmaPlot. Have you 
tried their online forums or their "contact a technician" links? 
(http://www.systat.com/products/SigmaPlot/resources/?sec=1019)

Again, thank you for your patience and I am very sorry I couldn't be more 
helpful,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 02:13:29 PM:

> See inserts below 
> 
> 
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, April 28, 2005 12:36 PM
> To: Berman, Mikhail
> Cc: mysql@lists.mysql.com
> Subject: RE: Does anyone have experience?
> 
> 
> 
> OK, I am still confused. Let's run down the list of what's working and
> what isn't: 
> 
> a) In the ODBC manager, create a System DSN and click on the TEST
> CONNECTION button. What happens? 
> 
> ODBC manager returns - "Success. Connection was made" 
> 
> b) In SigmaPlot, tell the software to use the connection you just
> created and tested. What happens? 
> 
> SigmaPlot returns - "Cannot connect to data source" 
> 
> If we can't get the ODBC manager to connect, nothing else using that DSN
> can possibly connect. The fact that your MySQL database is in a
> different machine running a different OS is not important. What is
> important is that you are using a user account to make your connection
> (a MySQL user account, NOT an OS user account) that has privileges and
> that you can connect to the server and authenticate with that account's
> credentials. 
> 
> If for some reason there is a firewall between your XP machine and your
> MySQL server, that can also cause a failure to connect. Can you ping the
> server from your XP machine? Can you telnet from your XP machine to your
> MySQL server on port 3660?  (You cannot create a normal telnet session
> with a MySQL server. However, if you can see the version of the server
> surrounded by several lines of gibberish, this telnet test was
> successful. ) 
> 
> I work freely with MySQL servers from XP machine as a part of my daily
> routine. 
> 
> The reason I keep going back to ODBC is that I want to make absolutely
> certain that this is not the weak link. If all ODBC tests are good then
> we need to look at the connection between SigmaPlot and ODBC as the
> problem. 
> 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine 
> 
> 
> 
> "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 11:59:27 AM:
> 
> > Hi, 
> > 
> > I do use Data Sources(ODBC) manager to create DSNs. 
> > 
> > Either User or System DSN failed to connect from SysPlot to UNIX 
> > databases, with the same error message "Cannot connect to data source"
> 
> > 
> > 
> > Sorry I was not precise in description, I have mentioned MS-Access 
> > vs. Excel only to raised a point that ODBC should work similarly 
> > with both tools, but it does not. 
> > I am aware of row limitation for Excel so my test are ran against 
> > the tables that are under Excel limitations 
> > 
> > Regards, 
> > 
> > Mikhail Berman 
> > 
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> > Sent: Thursday, April 28, 2005 11:41 AM
> > To: Berman, Mikhail
> > Cc: mysql@lists.mysql.com
> > Subject: RE: Does anyone have experience?
> 
> > 
> > 
> > "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:50:17
> AM:
> > 
> > > Yes, 
> > > 
> > > I do test DSN connection itself. It returns "Success. Connection was
> > > made". I am creating User DSN.
> > > 
> > 
> > That's a good sign. 
> > 
> > > The only tool able to connect to my UNIX databases via ODBC driver
> is
> > > MS-Access, not Excel.
> > 
> > If you are on XP (as you say) there is an ODBC Manager separate from
> > all other programs. Look under Start -> Control Panel -> 
> > Administrative Tools

RE: Does anyone have experience?

[Berman, Mikhail]

See inserts below 



From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 28, 2005 12:36 PM
To: Berman, Mikhail
Cc: mysql@lists.mysql.com
Subject: RE: Does anyone have experience?



OK, I am still confused. Let's run down the list of what's working and
what isn't: 

a) In the ODBC manager, create a System DSN and click on the TEST
CONNECTION button. What happens?  
 
ODBC manager returns - "Success. Connection was made" 

b) In SigmaPlot, tell the software to use the connection you just
created and tested. What happens?  
 
SigmaPlot returns - "Cannot connect to data source" 

If we can't get the ODBC manager to connect, nothing else using that DSN
can possibly connect. The fact that your MySQL database is in a
different machine running a different OS is not important. What is
important is that you are using a user account to make your connection
(a MySQL user account, NOT an OS user account) that has privileges and
that you can connect to the server and authenticate with that account's
credentials. 

If for some reason there is a firewall between your XP machine and your
MySQL server, that can also cause a failure to connect. Can you ping the
server from your XP machine? Can you telnet from your XP machine to your
MySQL server on port 3660?  (You cannot create a normal telnet session
with a MySQL server. However, if you can see the version of the server
surrounded by several lines of gibberish, this telnet test was
successful. )  
 
I work freely with MySQL servers from XP machine as a part of my daily
routine. 

The reason I keep going back to ODBC is that I want to make absolutely
certain that this is not the weak link. If all ODBC tests are good then
we need to look at the connection between SigmaPlot and ODBC as the
problem. 
 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 



"Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 11:59:27 AM:

> Hi, 
>   
> I do use Data Sources(ODBC) manager to create DSNs. 
>   
> Either User or System DSN failed to connect from SysPlot to UNIX 
> databases, with the same error message "Cannot connect to data source"

>   
>   
> Sorry I was not precise in description, I have mentioned MS-Access 
> vs. Excel only to raised a point that ODBC should work similarly 
> with both tools, but it does not. 
> I am aware of row limitation for Excel so my test are ran against 
> the tables that are under Excel limitations 
>   
> Regards, 
>   
> Mikhail Berman 
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, April 28, 2005 11:41 AM
> To: Berman, Mikhail
> Cc: mysql@lists.mysql.com
> Subject: RE: Does anyone have experience?

> 
> 
> "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:50:17
AM:
> 
> > Yes, 
> >  
> > I do test DSN connection itself. It returns "Success. Connection was
> > made". I am creating User DSN.
> >   
> 
> That's a good sign. 
> 
> > The only tool able to connect to my UNIX databases via ODBC driver
is
> > MS-Access, not Excel.
> 
> If you are on XP (as you say) there is an ODBC Manager separate from
> all other programs. Look under Start -> Control Panel -> 
> Administrative Tools -> (this is where I get lost. I don't use XP at
> work.) -> Data Sources (ODBC). That's the tool I wanted you to use, 
> not Access. You don't need to use MS Access in order to work with 
> ODBC DSNs ( unless you really want to.) 
> 
> If you can create a sucessful "system" DSN connection (I don't 
> create "user" DSNs for applications, I always make them "system" 
> DSNs) then what problem are you having telling SigmaPlot to use the 
> DSN you created? 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine 
> 
> >  
> > Regards,
> >  
> > Mikhail Berman
> > 
> > 
> > 
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> > Sent: Thursday, April 28, 2005 10:39 AM
> > To: Berman, Mikhail
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Does anyone have experience?
> > 
> > 
> > 
> > 
> > "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:32:53
AM:
> > 
> > > Hi,
> > > 
> > > I am promoting this E-mail to larger list in hope to find people
who
> > can
> > > help me.
> > > 
> > > Regards,
> > > 
> > > Mikhail Berman
> > > 
> > > -Original Message-
> > > From: Berman, Mikhail [mailto:[EMAIL PROTECTED] 
> > > Sent: Wednesday, April 27, 2005 9:57 AM
> > > To: [EMAIL PROTECTED]
> > > Subject: Does anyone have experience?
> > > 
> > > Hi everyone,
> > >  
> > > Does anyone have experience connecting statistical tool SigmaPlot
9.01
> > > from Systat Software (http://www.systat.com/products/sigmaplot/)
to
> > > MySQL databases running on:
> > >  
> > > 
> > > 1.   mysql> status
> > >--
> > >mysql  Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc)
> > > 
> > > And
> > > 
> > > 1.   mysql> status
> > >--
> > >mysql  Ver 11.13 Distrib 3.23.36, for sun-solaris2

Re: determing number of Tuesdays within a date range

[Peter Brawley]

James,
>I need to figure out how many Tuesdays are contained
>within 1/1/2004 - 5/1/2004, and I need to come up with
>a result where I know how many of each day of the week
>is within that date range.
Supposing a table named tbl and datetime columns named d1 and d2, 
something like this (not optimised)...

SELECT
 d1,
 d2,
 @dow1 := DAYOFWEEK(d1) AS dow1,
 @dow2 := DAYOFWEEK(d2) AS dow2,
 @days := DATEDIFF(d2,d1) AS Days,
 @tuesdays := FLOOR( @days / 7 ) +
  IF( @[EMAIL PROTECTED],
  IF( @dow1=3, 1, 0 ),
  IF( @dow1<=3,
  IF( @dow2>=3,
  1,
  IF( @days>0, 1, 0 )
),
  IF( @dow2 >=3, 1, 0 )
)
)
  AS Tuesdays
FROM tbl
ORDER BY d1,d2;
PB
-
James Black wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I have a table with session information, with a start and stop time.
I can aggregate the information, grouping it by hour or weekday.
But, when I group it by hour and weekday, I need to also show the
average usage, so I need to figure out how many Tuesdays are contained
within 1/1/2004 - 5/1/2004, and I need to come up with a result where I
know how many of each day of the week is within that date range.
I am hoping someone may have a solution, as, once I know the number of
Tues then I can state the average number of sessions on a Tuesday at 4pm.
Thanx for any help.
- --
"Love is mutual self-giving that ends in self-recovery." Fulton Sheen
James Black[EMAIL PROTECTED]
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCcPaMikQgpVn8xrARAoSKAJ96AzRIgGXnjbn/Krlxehz7K/HTlgCfSXZe
VxslEjt9ERmUBiDJoBti3SE=
=bwx8
-END PGP SIGNATURE-
 


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 4/27/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to move a database to a new directory

[mfatene]

Hi,
If you want to move just one (or some) database and keep your datadir as defined
:
* stop the server
* move your database (say world) to a new directory (c:\newdir)
* in the datadir, create .sym and write in a line containing "c:\newdir"
* restart

on Unix, you have symbolic links.

Mathias



Selon Dixie <[EMAIL PROTECTED]>:

> Andy Ford ha scritto:
>
> >I have run out of disk space in the directory where I have a mySQL database.
> >How do I go about moving it and reconfiguring mySQL to see the new location.
> >
> >Thanks
> >
> >Andy
> >
> >
> Symple... move to a different directory the data and edit the my file at:
>
> ...
> #Path to the database root
> datadir="C:/Programmi/MySQL/MySQL Server 4.1/Data/"
>
> change the datadir.
>
> That's under Win ... if you use Linux (I hope :-) ) the file, normally,
> is under /etc (my.cnf).
>
> See you
>
> Paolo
>
> --
> 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: Does anyone have experience?

[SGreen]

OK, I am still confused. Let's run down the list of what's working and 
what isn't:

a) In the ODBC manager, create a System DSN and click on the TEST 
CONNECTION button. What happens?

b) In SigmaPlot, tell the software to use the connection you just created 
and tested. What happens?

If we can't get the ODBC manager to connect, nothing else using that DSN 
can possibly connect. The fact that your MySQL database is in a different 
machine running a different OS is not important. What is important is that 
you are using a user account to make your connection (a MySQL user 
account, NOT an OS user account) that has privileges and that you can 
connect to the server and authenticate with that account's credentials.

If for some reason there is a firewall between your XP machine and your 
MySQL server, that can also cause a failure to connect. Can you ping the 
server from your XP machine? Can you telnet from your XP machine to your 
MySQL server on port 3660?  (You cannot create a normal telnet session 
with a MySQL server. However, if you can see the version of the server 
surrounded by several lines of gibberish, this telnet test was successful. 
)

The reason I keep going back to ODBC is that I want to make absolutely 
certain that this is not the weak link. If all ODBC tests are good then we 
need to look at the connection between SigmaPlot and ODBC as the problem.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



"Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 11:59:27 AM:

> Hi,
> 
> I do use Data Sources(ODBC) manager to create DSNs.
> 
> Either User or System DSN failed to connect from SysPlot to UNIX 
> databases, with the same error message "Cannot connect to data source"
> 
> 
> Sorry I was not precise in description, I have mentioned MS-Access 
> vs. Excel only to raised a point that ODBC should work similarly 
> with both tools, but it does not.
> I am aware of row limitation for Excel so my test are ran against 
> the tables that are under Excel limitations
> 
> Regards,
> 
> Mikhail Berman
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, April 28, 2005 11:41 AM
> To: Berman, Mikhail
> Cc: mysql@lists.mysql.com
> Subject: RE: Does anyone have experience?

> 
> 
> "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:50:17 AM:
> 
> > Yes, 
> > 
> > I do test DSN connection itself. It returns "Success. Connection was
> > made". I am creating User DSN.
> > 
> 
> That's a good sign. 
> 
> > The only tool able to connect to my UNIX databases via ODBC driver is
> > MS-Access, not Excel.
> 
> If you are on XP (as you say) there is an ODBC Manager separate from
> all other programs. Look under Start -> Control Panel -> 
> Administrative Tools -> (this is where I get lost. I don't use XP at
> work.) -> Data Sources (ODBC). That's the tool I wanted you to use, 
> not Access. You don't need to use MS Access in order to work with 
> ODBC DSNs ( unless you really want to.) 
> 
> If you can create a sucessful "system" DSN connection (I don't 
> create "user" DSNs for applications, I always make them "system" 
> DSNs) then what problem are you having telling SigmaPlot to use the 
> DSN you created? 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine 
> 
> > 
> > Regards,
> > 
> > Mikhail Berman
> > 
> > 
> > 
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> > Sent: Thursday, April 28, 2005 10:39 AM
> > To: Berman, Mikhail
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Does anyone have experience?
> > 
> > 
> > 
> > 
> > "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:32:53 
AM:
> > 
> > > Hi,
> > > 
> > > I am promoting this E-mail to larger list in hope to find people who
> > can
> > > help me.
> > > 
> > > Regards,
> > > 
> > > Mikhail Berman
> > > 
> > > -Original Message-
> > > From: Berman, Mikhail [mailto:[EMAIL PROTECTED] 
> > > Sent: Wednesday, April 27, 2005 9:57 AM
> > > To: [EMAIL PROTECTED]
> > > Subject: Does anyone have experience?
> > > 
> > > Hi everyone,
> > > 
> > > Does anyone have experience connecting statistical tool SigmaPlot 
9.01
> > > from Systat Software (http://www.systat.com/products/sigmaplot/) to
> > > MySQL databases running on:
> > > 
> > > 
> > > 1.   mysql> status
> > >--
> > >mysql  Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc)
> > > 
> > > And
> > > 
> > > 1.   mysql> status
> > >--
> > >mysql  Ver 11.13 Distrib 3.23.36, for sun-solaris2.7 (sparc)
> > > 
> > > 
> > > Through MySQL ODBC driver v.3.51.10.00 installed on Windows XP Home
> > > Edition machine.
> > > 
> > > All my attempts to connect have failed. SigmaPlot returns " Cannot
> > > connect to data source."
> > > 
> > > Best,
> > > 
> > > Mikhail Berman
> > > 
> > 
> > Can you get a good "connection test" if you just use the ODBC manager?
> > While you attempt to create a DSN, there is a button on the interface 
to
> > "t

extra bytes added to large BLOB by mysqldump

[Zhe Wang]

Hi, there,
   I am using MySQL 4.1.10. I have a table which has a LONGBLOB field. 
Some length of the blob field of the records varies from a few  hundred 
bytes to more than 64 MB. I've set max_allowed _packet to 128M on the 
server side. I first created the table in MyISAM and the data was added 
to the table successfully. Then I decided to convert the table to 
InnoDB. Since the table is very large (data alone is 90 G), it would 
take days to do so by "alter table mytable type=InnoDB", I decided to 
dump the data out using mysqldump then imported into a newly created 
InnoDB table. Here is the mysqldump command I used:

mysqldump -u u -p -P 3306 -h host --add-locks --complete-insert 
--extended-insert --no-create-info --quick --max_allowed_packet=128M 
mydb mytable > mytable.sql

I imported the data into the InnoDB table by:
mysql -u u -p -P 3306 -h host mydb mytableInno < mytable.sql
I have a script which parse the content of the LONGBLOB field. This 
script was able to parse the data in the MyISAM table I first built but 
failed on some of the records in the InnoDB table. When I checked the 
length of the LONGBLOB field of the records which couldn't be parsed by 
my script, I found all of them of length above 15MB. Then I compared the 
lengths of such records in the MyISAM and InnoDB tables, I found that 
those in InnoDB were one or two bytes longer than those in MyISAM table.

Since the data in my InnoDB table was imported from a mysql data 
dump, I was wondering if these extra bytes were introduced by mysqldump, 
either at dumping out or importing back or both.

I know that I could avoid such problem by populating the InnoDB 
table from scratch. However, I want to find out if mysqldump does 
something weird to a large LONGBLOB.

I would greatly appreciate if someone can give me some hint. Thank 
you in advance!

Regards,
Zhe
  

show innodb status

[Mayuran Yogarajah]

Does anyone how to interpret the output of 'SHOW INNODB STATUS' ?
It prints quite a bit of stuff but I haven't been able to find any 
documentation
explaining what everything means. Specifically:

Total memory allocated 462835256; in additional pool allocated 1385472
Buffer pool size   24576
Free buffers   24512
Database pages 64
How does it get these numbers? I've set innodb_buffer_pool_size to 384M and
20M for additional_mem_pool_size.
thanks,
M
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Does anyone have experience?

[Berman, Mikhail]

Hi,
 
I do use Data Sources(ODBC) manager to create DSNs.
 
Either User or System DSN failed to connect from SysPlot to UNIX
databases, with the same error message "Cannot connect to data source"
 
 
Sorry I was not precise in description, I have mentioned MS-Access vs.
Excel only to raised a point that ODBC should work similarly with both
tools, but it does not.
I am aware of row limitation for Excel so my test are ran against the
tables that are under Excel limitations
 
Regards,
 
Mikhail Berman



From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 28, 2005 11:41 AM
To: Berman, Mikhail
Cc: mysql@lists.mysql.com
Subject: RE: Does anyone have experience?




"Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:50:17 AM:

> Yes, 
>  
> I do test DSN connection itself. It returns "Success. Connection was
> made". I am creating User DSN.
>   

That's a good sign. 

> The only tool able to connect to my UNIX databases via ODBC driver is
> MS-Access, not Excel.

If you are on XP (as you say) there is an ODBC Manager separate from all
other programs. Look under Start -> Control Panel -> Administrative
Tools -> (this is where I get lost. I don't use XP at work.) -> Data
Sources (ODBC). That's the tool I wanted you to use, not Access. You
don't need to use MS Access in order to work with ODBC DSNs ( unless you
really want to.) 

If you can create a sucessful "system" DSN connection (I don't create
"user" DSNs for applications, I always make them "system" DSNs) then
what problem are you having telling SigmaPlot to use the DSN you
created? 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

>  
> Regards,
>  
> Mikhail Berman
> 
> 
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, April 28, 2005 10:39 AM
> To: Berman, Mikhail
> Cc: mysql@lists.mysql.com
> Subject: Re: Does anyone have experience?
> 
> 
> 
> 
> "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:32:53
AM:
> 
> > Hi,
> > 
> > I am promoting this E-mail to larger list in hope to find people who
> can
> > help me.
> > 
> > Regards,
> > 
> > Mikhail Berman
> > 
> > -Original Message-
> > From: Berman, Mikhail [mailto:[EMAIL PROTECTED] 
> > Sent: Wednesday, April 27, 2005 9:57 AM
> > To: [EMAIL PROTECTED]
> > Subject: Does anyone have experience?
> > 
> > Hi everyone,
> >  
> > Does anyone have experience connecting statistical tool SigmaPlot
9.01
> > from Systat Software (http://www.systat.com/products/sigmaplot/) to
> > MySQL databases running on:
> >  
> > 
> > 1.   mysql> status
> >--
> >mysql  Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc)
> > 
> > And
> > 
> > 1.   mysql> status
> >--
> >mysql  Ver 11.13 Distrib 3.23.36, for sun-solaris2.7 (sparc)
> >
> > 
> > Through MySQL ODBC driver v.3.51.10.00 installed on Windows XP Home
> > Edition machine.
> >  
> > All my attempts to connect have failed. SigmaPlot returns " Cannot
> > connect to data source."
> >  
> > Best,
> >  
> > Mikhail Berman
> > 
> 
> Can you get a good "connection test" if you just use the ODBC manager?
> While you attempt to create a DSN, there is a button on the interface
to
> "test" the connection parameters you just entered. Please tell us the
> result of that test. 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine 
> 
> 

RE: Does anyone have experience?

[SGreen]

"Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:50:17 AM:

> Yes, 
> 
> I do test DSN connection itself. It returns "Success. Connection was
> made". I am creating User DSN.
> 

That's a good sign.

> The only tool able to connect to my UNIX databases via ODBC driver is
> MS-Access, not Excel.

If you are on XP (as you say) there is an ODBC Manager separate from all 
other programs. Look under Start -> Control Panel -> Administrative Tools 
-> (this is where I get lost. I don't use XP at work.) -> Data Sources 
(ODBC). That's the tool I wanted you to use, not Access. You don't need to 
use MS Access in order to work with ODBC DSNs ( unless you really want 
to.)

If you can create a sucessful "system" DSN connection (I don't create 
"user" DSNs for applications, I always make them "system" DSNs) then what 
problem are you having telling SigmaPlot to use the DSN you created?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

> 
> Regards,
> 
> Mikhail Berman
> 
> 
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, April 28, 2005 10:39 AM
> To: Berman, Mikhail
> Cc: mysql@lists.mysql.com
> Subject: Re: Does anyone have experience?
> 
> 
> 
> 
> "Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:32:53 AM:
> 
> > Hi,
> > 
> > I am promoting this E-mail to larger list in hope to find people who
> can
> > help me.
> > 
> > Regards,
> > 
> > Mikhail Berman
> > 
> > -Original Message-
> > From: Berman, Mikhail [mailto:[EMAIL PROTECTED] 
> > Sent: Wednesday, April 27, 2005 9:57 AM
> > To: [EMAIL PROTECTED]
> > Subject: Does anyone have experience?
> > 
> > Hi everyone,
> > 
> > Does anyone have experience connecting statistical tool SigmaPlot 9.01
> > from Systat Software (http://www.systat.com/products/sigmaplot/) to
> > MySQL databases running on:
> > 
> > 
> > 1.   mysql> status
> >--
> >mysql  Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc)
> > 
> > And
> > 
> > 1.   mysql> status
> >--
> >mysql  Ver 11.13 Distrib 3.23.36, for sun-solaris2.7 (sparc)
> > 
> > 
> > Through MySQL ODBC driver v.3.51.10.00 installed on Windows XP Home
> > Edition machine.
> > 
> > All my attempts to connect have failed. SigmaPlot returns " Cannot
> > connect to data source."
> > 
> > Best,
> > 
> > Mikhail Berman
> > 
> 
> Can you get a good "connection test" if you just use the ODBC manager?
> While you attempt to create a DSN, there is a button on the interface to
> "test" the connection parameters you just entered. Please tell us the
> result of that test. 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine 
> 
> 

Re: How to move a database to a new directory

[Dixie]

Andy Ford ha scritto:
I have run out of disk space in the directory where I have a mySQL database.
How do I go about moving it and reconfiguring mySQL to see the new location.
Thanks
Andy
 

Symple... move to a different directory the data and edit the my file at:
...
#Path to the database root
datadir="C:/Programmi/MySQL/MySQL Server 4.1/Data/"
change the datadir.
That's under Win ... if you use Linux (I hope :-) ) the file, normally, 
is under /etc (my.cnf).

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

How to move a database to a new directory

[Andy Ford]

I have run out of disk space in the directory where I have a mySQL database.
How do I go about moving it and reconfiguring mySQL to see the new location.

Thanks

Andy

This e-mail is private and may be confidential and is for the intended 
recipient only.  If misdirected, please notify us by telephone and confirm that 
it has been deleted from your system and any copies destroyed.  If you are not 
the intended recipient you are strictly prohibited from using, printing, 
copying, distributing or disseminating this e-mail or any information contained 
in it.  We use reasonable endeavours to virus scan all e-mails leaving the 
Company but no warranty is given that this e-mail and any attachments are virus 
free.  You should undertake your own virus checking.  The right to monitor 
e-mail communications through our network is reserved by us. 

Re: time zones

[Monty Harris]

> How does one cope with time zones?  For example, if I want to timestamp a
> record it will timestanp using the local time of my server.  I thought
that, > e.g if my server was in New York and my customer_location was the
UK, I
can > just add 5 hours to the time and it would be correct.
> While that might work for 51 weeks of the year, how do we cope with the
one > week where the clocks in the UK go forward one week earlier than they
do
in > the USA, as just happened a few weeks ago? > How can we reliably handle
multiple time zones, and daylight savings time, > with one server located in
one location?  Am I asking the impossible?

>Hello.

>The good support of timezones is on TODO list. See:

  >  http://dev.mysql.com/doc/mysql/en/todo-future.html

Many thanks for the info.  So in the light of this not being available yet
(and my RaQ4 being unable to handle the newer versions of MySQL), is there
anything I can do in the meantime to handle timezones?  How do others deal
with them?


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

Re: host info

[Eamon Daly]

I'm an idiot. I completely forgot about USER():
mysql> select user(), SUBSTRING_INDEX(user(), '@', -1);
+-+--+
| user()  | SUBSTRING_INDEX(user(), '@', -1) |
+-+--+
| [EMAIL PROTECTED] | localhost|
+-+--+
More details in the usual place:
http://dev.mysql.com/doc/mysql/en/information-functions.html

Eamon Daly

- Original Message - 
From: "Stanton, Brian" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, April 27, 2005 2:07 PM
Subject: RE: host info


The 'Connection' output from the 'status' command is actually what I was
looking for.  However, most likely it will be a jdbc connection to mysql,
not the mysql client, so I'll have to see if it works that way or not.
Thanks,
Brian Stanton
-Original Message-
From: Eamon Daly [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 27, 2005 2:04 PM
To: Stanton, Brian; mysql@lists.mysql.com
Subject: Re: host info

I don't know if it's possible in MySQL.
That said, in the mysql client, you can type '\s' for
'status'. Look for 'Current user' in the output.

Eamon Daly

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

RE: Does anyone have experience?

[Berman, Mikhail]

Yes, 
 
I do test DSN connection itself. It returns "Success. Connection was
made". I am creating User DSN.
 
The only tool able to connect to my UNIX databases via ODBC driver is
MS-Access, not Excel.
 
Regards,
 
Mikhail Berman



From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 28, 2005 10:39 AM
To: Berman, Mikhail
Cc: mysql@lists.mysql.com
Subject: Re: Does anyone have experience?




"Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:32:53 AM:

> Hi,
> 
> I am promoting this E-mail to larger list in hope to find people who
can
> help me.
> 
> Regards,
> 
> Mikhail Berman
> 
> -Original Message-
> From: Berman, Mikhail [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, April 27, 2005 9:57 AM
> To: [EMAIL PROTECTED]
> Subject: Does anyone have experience?
> 
> Hi everyone,
>  
> Does anyone have experience connecting statistical tool SigmaPlot 9.01
> from Systat Software (http://www.systat.com/products/sigmaplot/) to
> MySQL databases running on:
>  
> 
> 1.   mysql> status
>--
>mysql  Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc)
> 
> And
> 
> 1.   mysql> status
>--
>mysql  Ver 11.13 Distrib 3.23.36, for sun-solaris2.7 (sparc)
>
> 
> Through MySQL ODBC driver v.3.51.10.00 installed on Windows XP Home
> Edition machine.
>  
> All my attempts to connect have failed. SigmaPlot returns " Cannot
> connect to data source."
>  
> Best,
>  
> Mikhail Berman
> 

Can you get a good "connection test" if you just use the ODBC manager?
While you attempt to create a DSN, there is a button on the interface to
"test" the connection parameters you just entered. Please tell us the
result of that test. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

Re: Does anyone have experience?

[SGreen]

"Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 04/28/2005 10:32:53 AM:

> Hi,
> 
> I am promoting this E-mail to larger list in hope to find people who can
> help me.
> 
> Regards,
> 
> Mikhail Berman
> 
> -Original Message-
> From: Berman, Mikhail [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, April 27, 2005 9:57 AM
> To: [EMAIL PROTECTED]
> Subject: Does anyone have experience?
> 
> Hi everyone,
> 
> Does anyone have experience connecting statistical tool SigmaPlot 9.01
> from Systat Software (http://www.systat.com/products/sigmaplot/) to
> MySQL databases running on:
> 
> 
> 1.   mysql> status
>--
>mysql  Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc)
> 
> And
> 
> 1.   mysql> status
>--
>mysql  Ver 11.13 Distrib 3.23.36, for sun-solaris2.7 (sparc)
> 
> 
> Through MySQL ODBC driver v.3.51.10.00 installed on Windows XP Home
> Edition machine.
> 
> All my attempts to connect have failed. SigmaPlot returns " Cannot
> connect to data source."
> 
> Best,
> 
> Mikhail Berman
> 

Can you get a good "connection test" if you just use the ODBC manager? 
While you attempt to create a DSN, there is a button on the interface to 
"test" the connection parameters you just entered. Please tell us the 
result of that test. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

re: determing number of Tuesdays within a date range

[James Black]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

I have a table with session information, with a start and stop time.

I can aggregate the information, grouping it by hour or weekday.

But, when I group it by hour and weekday, I need to also show the
average usage, so I need to figure out how many Tuesdays are contained
within 1/1/2004 - 5/1/2004, and I need to come up with a result where I
know how many of each day of the week is within that date range.

I am hoping someone may have a solution, as, once I know the number of
Tues then I can state the average number of sessions on a Tuesday at 4pm.

Thanx for any help.

- --
"Love is mutual self-giving that ends in self-recovery." Fulton Sheen
James Black[EMAIL PROTECTED]
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCcPaMikQgpVn8xrARAoSKAJ96AzRIgGXnjbn/Krlxehz7K/HTlgCfSXZe
VxslEjt9ERmUBiDJoBti3SE=
=bwx8
-END PGP SIGNATURE-

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

Re: REPLACE function

[Eamon Daly]

Sadly, the REGEXP function is only for string comparison,
not substitution. You'll either have to handle the
replacement on the client side or string together an
unwieldy series of REPLACE functions, like so:
SET @phone = '(123) 456-7890';
SELECT
REPLACE(
REPLACE(
REPLACE(
REPLACE(@phone,
 ' ', ''),
 '(', ''),
 ')', ''),
 '-', '') AS phone;
++
| phone  |
++
| 1234567890 |
++
You'll probably need to add more REPLACE statements for
things like periods and various common abbreviations ('x',
'ext.', and 'EX', for instance). In the future, you should
probably try to clean up your data before it gets into the
database. It'll make your life a lot easier.

Eamon Daly

- Original Message - 
From: "Chris Ramsay" <[EMAIL PROTECTED]>
To: "Jerry Swanson" <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, April 28, 2005 6:37 AM
Subject: Re: REPLACE function


If I understand your problem, MySQL allows you to use regular
expressions - so you could use the REGEXP function to remove the
spaces. Check out the mysql site...
Chris

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

Re: Problems with character sets and Unicode in MySQL

[Gleb Paharenko]

Hello.



>But I've been told that the support for Unicode is not yet good in MySQL. Is 
>it possible to find whether this is true?  



I don't think so, as most bugs related to utf8 are reported about the fifth 
version

while utf8 support in fourth seems very stable. 





Raul Mauri <[EMAIL PROTECTED]> wrote:

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

> 

> I use the MyQSL Query Browser for a library database.

> I have come across a problem in MySQL: Using extended characters, as  , is 
> not a problem, as long as they are present in the standard 256 characters of 
> a font. Things become more difficult when I need other East Europe 
> characters. 

> Could anyone suggest any information about this matter? How to use all the 
> characters present in the Times New Roman font (which includes Hebrew, greek, 
> Arab, and all East Europe characters) ? Is it possible to include them in a 
> MySQL database ?

> In addition to this, I have another problem:

> I use the latin-1 code page for most of the text. I need to use some 
> additional characters. (I use Internet Explorer as interface). To display the 
> characters is not difficult: #1488; will display the hebew "Aleph". No 
> problem. The trouble is for writing a request and ordering the result list, 
> ("collation"). 

> 

> e.g.: 

> Standard collations of MySQL sends the special characters in the end of the 
> list. Suppose a request which sends 6 charterhouses, it will be ordered like 
> this :

> 

> Portes

> Séville

> Transfiguration

> Valsainte

> Witham

> Štipa

> 

> and I would like :

> 

> Portes

> Séville

> Štipa

> Transfiguration

> Valsainte

> Witham

> 

> The documentation at www.mysql.com indicates how things can be modified. But 
> I've been told that the support for Unicode is not yet good in MySQL. Is it 
> possible to find whether this is true?  

> I would also need that the user can type : Stipa without the accent, and find 
> the result. 

> As I said, we have found the trick for the standard 256 ANSI characters.  The 
> question is more difficult if Unicode is needed. The chapter 11 explains this 
> matter, but I am not sure that all this works well presently. I would just 
> like to know if MySQL is really ready for unicode use.

> Well, I thank you in advance.

> 

> 

> Raúl Mauri

> 

> 

> __

> Do You Yahoo!?

> Tired of spam?  Yahoo! Mail has the best spam protection around 

> http://mail.yahoo.com 



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




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

Does anyone have experience?

[Berman, Mikhail]

Hi,

I am promoting this E-mail to larger list in hope to find people who can
help me.

Regards,

Mikhail Berman

-Original Message-
From: Berman, Mikhail [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 27, 2005 9:57 AM
To: [EMAIL PROTECTED]
Subject: Does anyone have experience?

Hi everyone,
 
Does anyone have experience connecting statistical tool SigmaPlot 9.01
from Systat Software (http://www.systat.com/products/sigmaplot/) to
MySQL databases running on:
 

1.  mysql> status
--
mysql  Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc)

And

1.  mysql> status
--
mysql  Ver 11.13 Distrib 3.23.36, for sun-solaris2.7 (sparc)


Through MySQL ODBC driver v.3.51.10.00 installed on Windows XP Home
Edition machine.
 
All my attempts to connect have failed. SigmaPlot returns " Cannot
connect to data source."
 
Best,
 
Mikhail Berman


 
 

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

Re: Crystal Reports & MySQL

[Scott Pippin]

I haven't tested it but I am using version 10 with no problems.  See the 
announcement made by business objects at the MySQL users conference:  
http://www.mysql.com/news-and-events/press-release/release_2005_10.html
 
I hope this helps.
 
Scott Pippin
[EMAIL PROTECTED]

>>> "Kerry Frater" <[EMAIL PROTECTED]> 04/28/05 6:09 AM >>>

I have MySQL & the downloaded ODBC drivers.

I am looking to use Crystal Reports to design & run my own reports on my
tables.

Can anyone tell me if the V11 Standard will happily work with MySQL or do I
need a different version?

Kerry

Re: mysql install on Redhat Linux 2.1AS (32 bit)

[Gabriel PREDA]

You should be shure that the old client is not in the PATH... because seems
to me that when you're not int the mysql bin directory  the old client is
called... withc does not know about the new auth methods...

Or else... start the server with the "--old-password" param.

Gabriel

- Original Message - 
From: "V. Agarwal" <[EMAIL PROTECTED]>
Subject: mysql install on Redhat Linux 2.1AS (32 bit)
> However, it expects me to be in mysql bin directory to
> invoke 'mysql' to connect or else it gives me
> following error.
>
> mysql -uroot -p
> ERROR 1251: Client does not support authentication
> protocol requested by server; consider upgrading MySQL
> client.



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

Re: 4Knowledge

[SGreen]

"mdnazrul" <[EMAIL PROTECTED]> wrote on 04/28/2005 08:41:16 AM:

> Dear Sir/Madam,
> I'm new to mysql 
> Does any body know following program written in which language? is that 
C++
> or Java
> 
> Thank you for your support.
> 
> Sincerely
> Nazrul
> 

If you download MySQL you will also get a copy of it's source code. It is 
definitely C++.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: connecting with PHP from remote server - security breach?

[Brent Baisley]

Ideally you would want to encrypt the connection, if only to add 
another layer of security. But regardless, there are other steps you 
can and should take even before implementing a secure connection 
between PHP and MySQL. It's actually a smart idea to separate your 
database from the front end host.

Your external router, the one directly connected to the internet, 
should not allow any connections to or from the MySQL box from the 
"outside". Your PHP/Apache box would be in a DMZ zone, basically 
sitting between two routers/firewalls (between a rock and a hard 
place?). Your internal router/firewall would only allow connections 
from the PHP/Apache box to the MySQL box on the appropriate ports. 
That's the basics on setting up your network to restrict access to your 
machines. You should then also configure your machines to restrict 
access to themselves. So PHP can only talk to the MySQL machine and 
MySQL can only talk to the PHP machine on the appropriate ports.
With this setup, even if someone from the outside new the proper name 
and password for MySQL, they wouldn't be able to connect because the 
routers, firewalls, OS and MySQL wouldn't allow it. In theory, someone 
would have to be on the inside in order to sniff the unencrypted 
connection and connect to one of your machines. Or they would need to 
compromise one of your machines (router, firewall, pc, etc.).

These are some basic steps you should take whether or not you have an 
encrypted connection. Close all doors and make each path a one way 
street if possible. No system is secure, it's just sufficiently 
difficult to crack to make it not worthwhile.

On Apr 27, 2005, at 3:43 AM, Nikola Skoric wrote:
Hello,
I'm wondering whether there is a way to hack into my database if I'm 
connecting with PHP
from remote web server to my MySQL server with unencrypted connection? 
I'm using phpBB
forums (residing on one server and using the database on other server) 
which do not suport
SSH connections, so I'd like to know if I'm opening a security hole in 
my system this way?

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

4Knowledge

[mdnazrul]

Dear Sir/Madam,
I'm new to mysql 
Does any body know following program written in which language? is that C++
or Java

Thank you for your support.

Sincerely
Nazrul

Re: Maximize mysql ini for 4gig big query win 2003 box

[matt_lists]

Way ahead of you, that switch was turned on during initial load before 
we went live with the new servers.

Mark Leith wrote:
You mention that MySQL is "only using 1.5 gig".. 

Out of interest, are you aware that Windows by default when using
"large" amounts of memory, will split the memory allocation down the
middle - between "applications" and "system"? So given your 4Gb of
memory, applications with be getting 2Gb.. 

You can read more about this here:
http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx
Basically, add a \3GB switch to your boot.ini file. 

Then you can start look at tuning the memory on MySQL as well..
HTH
Mark
Mark Leith 
Cool-Tools UK Limited
http://www.cool-tools.co.uk

-Original Message-
From: matt_lists [mailto:[EMAIL PROTECTED] 
Sent: 28 April 2005 13:57
To: Gleb Paharenko
Cc: mysql@lists.mysql.com
Subject: Re: Maximize mysql ini for 4gig big query win 2003 box

I'm more concerned with mysql only using 1.5 gig, wouldnt it be faster 
to maximize memory usage?  The only thing these box's do is mysql

I do have slow queries, but they are big joins across all the big 
tables, and users doing querys on tables without using a key, the 
contract requires dynamic query on any fields, and the tables are too 
big to index every possible key combination they would use (30-40 
columns on some)

So far, I've got no major issues, aside from big queries and there's no 
way around that, unless mysql changes on how it combines indexes, ie it 
does not combine them currently.

Matt
Gleb Paharenko wrote:
 

Hello.
SHOW STATUS and a piece of SHOW PROCESSLIST
with slow queries could give additional information,
so the probability of the helpful answer grows.
matt_lists <[EMAIL PROTECTED]> wrote:
   

Anybody have any suggested settings for our my.ini ?
4 gig box, running windows 2003
peak memory usage for mysql is 1.4 gig currently
using myisam files, lots of little index querys, and some very large
queries at night
615 tables, 88.3 gb of myd,  45.2 gb of myi, 33 of the tables/indexes
break 1 gig, largest is 6 gig
if you discount the piddly little tables, there are only 125 tables 
with
size that matter, ie larger than 50mb

Here's what we have now, sugestions welcome
max_connections=100
query_cache_size=512M
query_cache_type=1
table_cache=1024
tmp_table_size=400M
thread_cache_size=8
myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=369M
key_buffer_size=318M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
open-files-limit=500
myisam-recover=BACKUP,FORCE
memlock

  

 


   


 


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

RE: Maximize mysql ini for 4gig big query win 2003 box

[Mark Leith]

You mention that MySQL is "only using 1.5 gig".. 

Out of interest, are you aware that Windows by default when using
"large" amounts of memory, will split the memory allocation down the
middle - between "applications" and "system"? So given your 4Gb of
memory, applications with be getting 2Gb.. 

You can read more about this here:

http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx

Basically, add a \3GB switch to your boot.ini file. 

Then you can start look at tuning the memory on MySQL as well..

HTH

Mark

Mark Leith 
Cool-Tools UK Limited
http://www.cool-tools.co.uk


-Original Message-
From: matt_lists [mailto:[EMAIL PROTECTED] 
Sent: 28 April 2005 13:57
To: Gleb Paharenko
Cc: mysql@lists.mysql.com
Subject: Re: Maximize mysql ini for 4gig big query win 2003 box


I'm more concerned with mysql only using 1.5 gig, wouldnt it be faster 
to maximize memory usage?  The only thing these box's do is mysql


I do have slow queries, but they are big joins across all the big 
tables, and users doing querys on tables without using a key, the 
contract requires dynamic query on any fields, and the tables are too 
big to index every possible key combination they would use (30-40 
columns on some)

So far, I've got no major issues, aside from big queries and there's no 
way around that, unless mysql changes on how it combines indexes, ie it 
does not combine them currently.

Matt


Gleb Paharenko wrote:

>Hello.
>
>SHOW STATUS and a piece of SHOW PROCESSLIST
>with slow queries could give additional information,
>so the probability of the helpful answer grows.
>
>matt_lists <[EMAIL PROTECTED]> wrote:
>  
>
>>Anybody have any suggested settings for our my.ini ?
>>
>>4 gig box, running windows 2003
>>
>>peak memory usage for mysql is 1.4 gig currently
>>
>>using myisam files, lots of little index querys, and some very large
>>queries at night
>>
>>615 tables, 88.3 gb of myd,  45.2 gb of myi, 33 of the tables/indexes
>>break 1 gig, largest is 6 gig
>>
>>if you discount the piddly little tables, there are only 125 tables 
>>with
>>size that matter, ie larger than 50mb
>>
>>
>>Here's what we have now, sugestions welcome
>>
>>max_connections=100
>>query_cache_size=512M
>>query_cache_type=1
>>table_cache=1024
>>tmp_table_size=400M
>>thread_cache_size=8
>>myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G
>>myisam_sort_buffer_size=369M
>>key_buffer_size=318M
>>read_buffer_size=64K
>>read_rnd_buffer_size=256K
>>sort_buffer_size=256K
>>open-files-limit=500
>>myisam-recover=BACKUP,FORCE
>>memlock
>>
>>
>>
>>
>>
>
>
>  
>



-- 
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 Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 27/04/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 27/04/2005
 


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

Re: Maximize mysql ini for 4gig big query win 2003 box

[matt_lists]

I'm more concerned with mysql only using 1.5 gig, wouldnt it be faster 
to maximize memory usage?  The only thing these box's do is mysql

I do have slow queries, but they are big joins across all the big 
tables, and users doing querys on tables without using a key, the 
contract requires dynamic query on any fields, and the tables are too 
big to index every possible key combination they would use (30-40 
columns on some)

So far, I've got no major issues, aside from big queries and there's no 
way around that, unless mysql changes on how it combines indexes, ie it 
does not combine them currently.

Matt
Gleb Paharenko wrote:
Hello.
SHOW STATUS and a piece of SHOW PROCESSLIST
with slow queries could give additional information,
so the probability of the helpful answer grows.
matt_lists <[EMAIL PROTECTED]> wrote:
 

Anybody have any suggested settings for our my.ini ?
4 gig box, running windows 2003
peak memory usage for mysql is 1.4 gig currently
using myisam files, lots of little index querys, and some very large 
queries at night

615 tables, 88.3 gb of myd,  45.2 gb of myi, 33 of the tables/indexes 
break 1 gig, largest is 6 gig

if you discount the piddly little tables, there are only 125 tables with 
size that matter, ie larger than 50mb

Here's what we have now, sugestions welcome
max_connections=100
query_cache_size=512M
query_cache_type=1
table_cache=1024
tmp_table_size=400M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=369M
key_buffer_size=318M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
open-files-limit=500
myisam-recover=BACKUP,FORCE
memlock

   


 


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

mysql install on Redhat Linux 2.1AS (32 bit)

[V. Agarwal]

There are several binaries for linux and I picked
following for my Redhat Linux 2.1AS (32 bit) from
MYSQL site.

mysql-standard-4.1.11-pc-linux-gnu-i686.tar.gz

I installed by unzipping/untarring and started the
daemon as per instructions.
I also added mysql bin directory to my PATH variable.

However, it expects me to be in mysql bin directory to
invoke 'mysql' to connect or else it gives me
following error.

mysql -uroot -p
ERROR 1251: Client does not support authentication
protocol requested by server; consider upgrading MySQL
client.

Any clues why ?
Q2. What is the default (small,medium,large,huge conf
?)



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

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

Crystal Reports & MySQL

[Kerry Frater]

I have MySQL & the downloaded ODBC drivers.

I am looking to use Crystal Reports to design & run my own reports on my
tables.

Can anyone tell me if the V11 Standard will happily work with MySQL or do I
need a different version?

Kerry

Re: REPLACE function

[Chris Ramsay]

If I understand your problem, MySQL allows you to use regular
expressions - so you could use the REGEXP function to remove the
spaces. Check out the mysql site...

Chris

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

REPLACE function

[Jerry Swanson]

I have field varchar(20) that stores phone numbers. Phone number can
be in different format. So I need only digits from the field. How I
can do such replacement?

TH

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

Re: Need help with stored procedures in MySQL

[Joerg Bruehe]

Hi!
madderla sreedhar wrote:
Hi, 
Iam looking to migrate an Oracle database to MySQL and
trying to write a simple stored procedure in MySQL 4.1
The manual states clearly:
"Stored procedures and functions are a new feature in MySQL version 5.0."
(At the proper place: "19 Stored Procedures and Functions")
HTH,
Jörg
--
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]

Need help with stored procedures in MySQL

[madderla sreedhar]

Hi, 
Iam looking to migrate an Oracle database to MySQL and
trying to write a simple stored procedure in MySQL 4.1
.I reffered few examples posted and used the
statements from them but I get errors while creating
procedures 

create procedure helloworld (out p_text VARCHAR(30))
BEGIN
set p_text = "HELLO WORLD" ;
END
//
After pressin enter key an error message is displayed
ERROR 1064(42000):
you have an erro in your SQL syntax; 
check the manual that corresponds to your mysql server
version for the right syntax t ouse near 'procedure
HElloWorld ( OUT p_text = 'HELLO World' ' at line 1


Any help would be appreciated .

Thanks, 

Sreedhar.







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

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

Need help with stored procedures in mysql

[madderla sreedhar]

 
 

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

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

Re: purge log fails

[Gleb Paharenko]

Hello.



That's not good:



> DBD::mysql::db do failed: Lost connection to MySQL server during query 

> at /vrmd/admin/cron/apps/purge_logs/purge_logs.pl line 15.



MySQL could die during your query. What is in error log?





Marten Lehmann <[EMAIL PROTECTED]> wrote:

> Hello,

> 

> the current 4.1.11 implementation of PURGE LOGS doesn't seem to work any 

> more. I created a script running once each day doing the following 

> statement:

> 

> purge master logs before (select adddate(current_timestamp(), interval 

> -4 day))

> 

> That way, all logs prior to today - 4 days will become deleted. This 

> worked fine with 4.1.9. But since I upgraded from 4.1.9 to 4.1.11 I 

> alway get this error:

> 

> DBD::mysql::db do failed: Lost connection to MySQL server during query 

> at /vrmd/admin/cron/apps/purge_logs/purge_logs.pl line 15.

> 

> This also happens when I'm doing the query manually logged in at the 

> mysql-prompt.

> 

> For testing, I simplified to statement e.g. to

> 

> purge master logs before '2005-04-24';

> 

> Now I didn't lost the connection, but on the other hand, the purging 

> wasn't done. All logfiles remained at their position. To clear a bit of 

> space, I was running "reset master". But so all logfiles have been 

> removed, but I want to keep the logfiles for at least the last 4 days to 

> be able to track statements in case of errors. Any idea what is going 

> wrong? As it worked in 4.1.9, I guess there's a problem in the mysql-server?

> 

> Regards

> Marten

> 



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




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

Re: Avoiding deadlock: beyond the basics

[Gleb Paharenko]

Hello.



The output of INNODB MONITOR is not clear for me (I hope only

for me, and someone more skilled could correct me). At:

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



it is said that, SELECT ... FROM is a consistent read, reading a snapshot 

of the database and setting no locks unless the transaction isolation level

is set to SERIALIZABLE. You don't have a SERIALIZABLE level, and your 



> CREATE TEMPORARY TABLE t_active_layers (PRIMARY KEY(tal_jid,tal_lid))  

> SELECT DISTINCT frame_jid as tal_jid, frame_lid as tal_lid FROM  

> frames WHERE frame_state="ready"



Should not put locks on table `frames`. May be the problem is in

the previous sequence of queries?







S$ren Ragsdale <[EMAIL PROTECTED]> wrote:

> I've been getting the following error in my Python program which  

> accesses InnoDB tables:

> 

> OperationalError: 1213 Deadlock found when trying to get lock; try  

> restarting transaction

> 

> I've already tried all the basic steps that the documentation seems  

> to recommend:

> 

> - Set my TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED for session  

> and globally

> - used "UPDATE LOW PRIORITY" to encourage waiting for locks to become  

> clear

> - COMMIT immediately before the UPDATE to drop all locks

> 

> What's puzzling is that this deadlock requires *three* active clients  

> to manifest.  Two of these clients can hit the server without  

> problems, but if that number is increased only two active clients  

> remain deadlock-free.  Any hints or suggestions for further reading  

> would be appreciated.

> 

> Here's what I get from SHOW INNODB STATUS after a deadlock.  What's  

> especially puzzling is that what I'm seeing seems to violate the very  

> definition of deadlock.  Transaction 1 is holding no locks (since it  

> just committed) and is attempting to acquire a lock on only one row  

> of one table for an update.  You need at least two locks to create  

> circularity.

> 

> --

> =

> 050427 12:31:50 INNODB MONITOR OUTPUT

> =

> Per second averages calculated from the last 9 seconds

> --

> SEMAPHORES

> --

> OS WAIT ARRAY INFO: reservation count 96, signal count 93

> Mutex spin waits 657, rounds 8130, OS waits 31

> RW-shared spins 83, OS waits 37; RW-excl spins 40, OS waits 28

> 

> LATEST DETECTED DEADLOCK

> 

> 050427 12:31:37

> *** (1) TRANSACTION:

> TRANSACTION 0 23662, ACTIVE 1 sec, OS thread id 25389056 updating or  

> deleting

> mysql tables in use 1, locked 1

> LOCK WAIT 5 lock struct(s), heap size 1024, undo log entries 1

> MySQL thread id 326, query id 13839 localhost sherman Updating

> UPDATE LOW_PRIORITY frames SET frame_state="run" WHERE frame_jid=9  

> AND frame_lid=0 AND frame_frame=2 AND frame_tile=6 AND  

> frame_state="ready"

> *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

> RECORD LOCKS space id 0 page no 393 n bits 648 index  

> `frame_state_key` of table `sherman/frames` trx id 0 23662 lock_mode  

> X locks gap before rec insert intention waiting

> Record lock, heap no 577 PHYSICAL RECORD: n_fields 5; 1-byte offs  

> TRUE; info bits 32

> 0: len 1; hex 04; asc  ;; 1: len 4; hex 002f; asc/;; 2: len  

> 1; hex 00; asc  ;; 3: len 2; hex 0010; asc   ;; 4: len 1; hex 00;  

> asc  ;;

> 

> *** (2) TRANSACTION:

> TRANSACTION 0 23665, ACTIVE 0 sec, OS thread id 25530880 fetching  

> rows, thread declared inside InnoDB 283

> mysql tables in use 1, locked 1

> 17 lock struct(s), heap size 2496

> MySQL thread id 328, query id 13853 localhost sherman Copying to tmp  

> table

> CREATE TEMPORARY TABLE t_active_layers (PRIMARY KEY(tal_jid,tal_lid))  

> SELECT DISTINCT frame_jid as tal_jid, frame_lid as tal_lid FROM  

> frames WHERE frame_state="ready"

> *** (2) HOLDS THE LOCK(S):

> RECORD LOCKS space id 0 page no 393 n bits 648 index  

> `frame_state_key` of table `sherman/frames` trx id 0 23665 lock mode S

> Record lock, heap no 577 PHYSICAL RECORD: n_fields 5; 1-byte offs  

> TRUE; info bits 32

> 0: len 1; hex 04; asc  ;; 1: len 4; hex 002f; asc/;; 2: len  

> 1; hex 00; asc  ;; 3: len 2; hex 0010; asc   ;; 4: len 1; hex 00;  

> asc  ;;

> 

> *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

> RECORD LOCKS space id 0 page no 1717 n bits 864 index  

> `frame_state_key` of table `sherman/frames` trx id 0 23665 lock mode  

> S waiting

> Record lock, heap no 78 PHYSICAL RECORD: n_fields 5; 1-byte offs  

> TRUE; info bits 32

> 0: len 1; hex 03; asc  ;; 1: len 4; hex 0009; asc ;; 2: len  

> 1; hex 00; asc  ;; 3: len 2; hex 0002; asc   ;; 4: len 1; hex 06;  

> asc  ;;

> 

> *** WE ROLL BACK TRANSACTION (2)

> 

> TRANSACTIONS

> 

> Trx id counter 0 23695

> Purge done for trx's n:o < 0 23695 undo n:o < 0 0

> History list length 31

Re: time zones

[Gleb Paharenko]

Hello.



The good support of timezones is on TODO list. See:



http://dev.mysql.com/doc/mysql/en/todo-future.html





"Monty Harris" <[EMAIL PROTECTED]> wrote:

> How does one cope with time zones?  For example, if I want to timestamp a

> record it will timestanp using the local time of my server.  I thought that,

> e.g if my server was in New York and my customer_location was the UK, I can

> just add 5 hours to the time and it would be correct.

> 

> While that might work for 51 weeks of the year, how do we cope with the one

> week where the clocks in the UK go forward one week earlier than they do in

> the USA, as just happened a few weeks ago?

> 

> How can we reliably handle multiple time zones, and daylight savings time,

> with one server located in one location?  Am I asking the impossible?

> 

> 



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




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

Re: Maximize mysql ini for 4gig big query win 2003 box

[Gleb Paharenko]

Hello.



SHOW STATUS and a piece of SHOW PROCESSLIST

with slow queries could give additional information,

so the probability of the helpful answer grows.



matt_lists <[EMAIL PROTECTED]> wrote:

> Anybody have any suggested settings for our my.ini ?

> 

> 4 gig box, running windows 2003

> 

> peak memory usage for mysql is 1.4 gig currently

> 

> using myisam files, lots of little index querys, and some very large 

> queries at night

> 

> 615 tables, 88.3 gb of myd,  45.2 gb of myi, 33 of the tables/indexes 

> break 1 gig, largest is 6 gig

> 

> if you discount the piddly little tables, there are only 125 tables with 

> size that matter, ie larger than 50mb

> 

> 

> Here's what we have now, sugestions welcome

> 

> max_connections=100

> query_cache_size=512M

> query_cache_type=1

> table_cache=1024

> tmp_table_size=400M

> thread_cache_size=8

> myisam_max_sort_file_size=100G

> myisam_max_extra_sort_file_size=100G

> myisam_sort_buffer_size=369M

> key_buffer_size=318M

> read_buffer_size=64K

> read_rnd_buffer_size=256K

> sort_buffer_size=256K

> open-files-limit=500

> myisam-recover=BACKUP,FORCE

> memlock

> 

> 

> 



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




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

Re: connecting with PHP from remote server - security breach?

[Gleb Paharenko]

Hello.



Somebody with the sniffer on the transit

router could get enough information about MySQL credentials. Try

to use SSL connection.





Nikola Skoric <[EMAIL PROTECTED]> wrote:

> Hello,

> 

> I'm wondering whether there is a way to hack into my database if I'm=

> connecting with PHP 

> from remote web server to my MySQL server with unencrypted connection? I'm=

> using phpBB 

> forums (residing on one server and using the database on other server) which=

> do not suport 

> SSH connections, so I'd like to know if I'm opening a security hole in my=

> system this way?

> 



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




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

Re: returning results in the order they were selected

[Scott Haneda]

on 4/28/05 1:41 AM, Johan Höök at [EMAIL PROTECTED] wrote:

> Hi Scott,
> you can do:
> SELECT a, b, c, from table where zip IN (94949, 94945, 94947)
> ORDER BY FIELD(zip,94949, 94945, 94947)

That is too handy, thanks for the tip.
-- 
-
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: Multi Table Delete in 3.23.47

[Philippe Poelvoorde]

Michael Stassen wrote:
zzapper wrote:
 > Hi,
 > Final whinge:  I wish the mysql website made it a little clearer 
which > features are recent additions, I suggested color coding.

Philippe Poelvoorde wrote:

I reckon, it's a bit hidden in the middle :
 "Starting with MySQL 4.0.4, you can also perform UPDATE  operations 
that cover multiple tables:"
in :
http://dev.mysql.com/doc/mysql/en/update.html

I don't think it's hidden at all.  Looking at the manual page detailing 
DELETE syntax, , the very 
first sentence describing multiple-table delete statements is, "From 
MySQL 4.0, you can specify multiple tables in the DELETE statement to 
delete rows from one or more tables depending on a particular condition 
in multiple tables."

Michael
Well, let's say it's a matter of view and subject to interpretation...
On top we have :
"Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
"
without any reference to 4.0.4
which is half-way in the middle. So when I quickly look at the syntax, I 
don't see it ;)

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

Re: returning results in the order they were selected

[Johan Höök]

Hi Scott,
you can do:
SELECT a, b, c, from table where zip IN (94949, 94945, 94947)
ORDER BY FIELD(zip,94949, 94945, 94947)
/Johan
Scott Haneda wrote:
mysql 4.0.18-standard
I am running this select:
SELECT a, b, c, from table where zip IN (94949, 94945, 94947)
How can I get back a result set in the order of the `IN` part of the
statement?

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

returning results in the order they were selected

[Scott Haneda]

mysql 4.0.18-standard

I am running this select:
SELECT a, b, c, from table where zip IN (94949, 94945, 94947)

How can I get back a result set in the order of the `IN` part of the
statement?
-- 
-
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: Get a Random Row on a HUGE db

[Jigal van Hemert]

From: "Scott Gifford"

>SELECT COUNT(*) FROM firebase_content;
>
> to get the count.  That's very fast; it comes from the table summary
> information, IIRC.  I use a similar solution for a similar problem,
> and have had great luck with it.
This is true for MyISAM tables, but e.g. InnoDB does *not* store the total
number of records per table seperately. A SELECT COUNT(*) on InnoDB will
result in a real row count...

Regards, Jigal.


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

Re: why NOT NULL in PRIMARY key??

[Jigal van Hemert]

From: "Frank Bax"

> At 11:07 AM 4/27/05, Jigal van Hemert wrote:
> >So, if we would define that the key entry "0-NULL-Whatever" equals
> >"0-NULL-Whatever" (which MySQL is capable of if you look at the
definition
> >of UNIQUE indexes with BDB tables) then allowing NULLs as part of a key
> >entry would not permit duplicate entries. At least not more than allowing
> >other values.
>
> You cannot "define" that those keys are equal, because SQL standard states
> that "0-NULL-Whatever" is *always* *not-equal* to "0-NULL-Whatever".

Allowing only a single NULL entry in a UNIQUE index of BDB tables would also
imply that in at least one case a new NULL "value" is treated as equal to
the NULL that is already present in the index. A "duplicate key" error would
IMHO mean that a value that one tried to insert is equal to a value that is
already present in the index...

But you are also not quite right stating that "0-NULL-Whatever" is not equal
to "0-NULL-Whatever". Comparing two NULLs will not result in equal or not
equal, but in unknown (represented by NULL) ;-P

Regards, Jigal.


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