Re: no /tmp/mysql.sock

[Mark Sargent]

Simon Garner wrote:
On 6/05/2005 4:18 p.m., Mark Sargent wrote:
Ok, I just took a punt, and changed the setting in my.cnf to reflect 
the config settings, to /tmp/mysql.sock, and all is well. What I 
don't un, is, why the default settings were like this, I certainly 
made no changes to either the configs or the my.cnf file. Weird. Cheers.

Mark Sargent.
Hi Mark,
The default location for the socket file in modern distributions is in 
the MySQL data directory now (usually /var/lib/mysql), as /tmp is not 
always 100% safe, and in /tmp it is liable to be e.g. cleaned up by 
tmpwatch. And I expect if you were to talk in terms of filesystem 
standards, /tmp would not be the 'correct' place for a socket file.

-Simon
Hi All,
thanx, Simon, but, my real query, now, is, why the configs/my.cnf were 
different, along with the coding being wrong in the mysql_install_db 
script, which I had to move to /usr/local/mysql from the scripts dir to 
get it to run without errors. All quite weird. This happened on both 
home machines. Work machine never had these problems(same 
version/install). Cheers.

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

Re: no /tmp/mysql.sock

[Simon Garner]

On 6/05/2005 4:18 p.m., Mark Sargent wrote:
Ok, I just took a punt, and changed the setting in my.cnf to reflect the 
config settings, to /tmp/mysql.sock, and all is well. What I don't un, 
is, why the default settings were like this, I certainly made no changes 
to either the configs or the my.cnf file. Weird. Cheers.

Mark Sargent.
Hi Mark,
The default location for the socket file in modern distributions is in 
the MySQL data directory now (usually /var/lib/mysql), as /tmp is not 
always 100% safe, and in /tmp it is liable to be e.g. cleaned up by 
tmpwatch. And I expect if you were to talk in terms of filesystem 
standards, /tmp would not be the 'correct' place for a socket file.

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

Re: no /tmp/mysql.sock

[Mark Sargent]

Mark Sargent wrote:
Mark Sargent wrote:
Simon Garner wrote:
Mark Sargent wrote:
Hi All,
mysql is running, but, there seems to be no /tmp/mysql.sock file. 
How is this file generated..? I can't connect, keep getting errors. 
What about via a port..? What is the argument for that..? Cheers.

Mark Sargent.
It's probably not in /tmp any more. Try /var/lib/mysql/mysql.sock, 
or look at `mysqladmin variables` and check the 'socket' value.

-Simon
Hi All,
Simon, if it is somewhere else, how do I let other apps etc know..? 
If the client, which is the same PC as the server, is of the same 
version/install, why doesn't it know where it is..? Cheers.

Mark Sargent.
Hi All,
[EMAIL PROTECTED] ~]# mysql_config --socket
/tmp/mysql.sock
Is that what you were referring to, Simon..? Looks to me like it 
should be where mysql(and phpmyadmin also) is trying to access it 
from. Any thoughts on this further..? Cheers.

Mark Sargent.
Hi All,
[EMAIL PROTECTED] ~]# mysql_config
Usage: /usr/local/mysql/bin/mysql_config [OPTIONS]
Options:
   --cflags [-I/usr/local/mysql/include -mcpu=pentiumpro]
   --include[-I/usr/local/mysql/include]
   --libs   [-L/usr/local/mysql/lib -lmysqlclient -lz 
-lcrypt -lnsl -lm -lc -lnss_files -lnss_dns -lresolv -lc -lnss_files 
-lnss_dns -lresolv]--libs_r [-L/usr/local/mysql/lib 
-lmysqlclient_r -lz -lpthread -lcrypt -lnsl -lm -lpthread -lc 
-lnss_files -lnss_dns -lresolv -lc -lnss_files -lnss_dns -lresolv]
   --socket [/tmp/mysql.sock]
   --port   [3306]
   --version[4.1.11]
   --libmysqld-libs [-L/usr/local/mysql/lib -lmysqld -lpthread 
-lcrypt -lnsl -lm -lpthread -lc -lnss_files -lnss_dns -lresolv -lc 
-lnss_files -lnss_dns -lresolv -lrt]

Below is my my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
~
~
~
~
~
~
~
~
~
~
~
~
"/etc/my.cnf" 11L, 193C

Ok, I just took a punt, and changed the setting in my.cnf to reflect the 
config settings, to /tmp/mysql.sock, and all is well. What I don't un, 
is, why the default settings were like this, I certainly made no changes 
to either the configs or the my.cnf file. Weird. Cheers.

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

Re: How to export a table to a CSV file?

[Karam Chand]

If you are windows then I prefer to use SQLyog at
http://www.webyog.com to do the job.

ITS FREE!

Karam
--- "Homam S.A." <[EMAIL PROTECTED]> wrote:
> NOTE: I sent this message yesterday, but for some
> reason it didn't show up in the list. If you've
> already received it, I apologize for the
> inconvenience.
> 
> So here it goes again:
> 
> 
> 
> 
> I searched the online manual and all I could find
> was
> a reference to a statement of the form:
> 
> SELECT INTO OUTFILE...
> 
>
http://dev.mysql.com/doc/mysql/en/ansi-diff-select-into-table.html
> 
> I searched for this statement on the web and I found
> several references to a statement that should look
> like this:
> 
> select * into outfile 'C:/MyOutfile.txt'
> fields terminated by ','
> lines terimated by '|'
> from myTable
> 
> This statement generates a syntax error on MySQL
> 4.1.9. The following variation also generates a
> syntax
> error:
> 
> select * from table into outfile 'C:/MyOutfile.txt'
> fields terminated by ','
> lines terimated by '|'
> from myTable
> 
> Why is this statement rejected? Why is it not
> documented anywhere in the manual except for a
> passing
> reference?
> 
> And if there's no such a statement, how come there
> are
> many references to it on the web that date back to
> MySQL versions older than 4.1.9?
> 
> Finally, if this statement has been
> depricated/removed
> from 4.1.9, is there any way to export a table to a
> CSV file?
> 
> Thanks,
> 
> Homam
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 




__ 
Do you Yahoo!? 
Yahoo! Mail - You care about security. So do we. 
http://promotions.yahoo.com/new_mail

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

client_IP

[nngau]

Forgive me if this question has been answer in the past.
 
How can you record IP address on your first page index.html?
 
I am using my index.html to be index.lasso, and client_ip tag does not
seem to work.
It is picking up my server IP, and yes I have turned off the setting in
Lasso. 
 
Now it seems to work if I actually write out
http://websitename.com/index.lasso, 
 
why doesn't it work with just http://websitename.com/ ?
 
I do not want the user to be having to type out index.lasso to access
the first page.
 
Thank You
 
 

Re: ACCESS ODBC Interface whit 5.0.4

[Daniel Kasak]

Gordon wrote:

>Here is the table structure.
>In this case I tried to change the 4 characters in prod_ID when I get the
>message, but I get the same message when I try to make any changes.
>
>Doing the exact same activity works fine on all of the 3.23/4.0/4.1 versions
>I have installed previouly. Tables with unsigned integer fields work just
>fine through this interface on previous versions.
>
unsigned int doesn't work for me. Anyway, you don't seem to have an
unsigned in in the table def below so it doesn't matter.

> The only anomaly up to
>this point is that I can't change timestamp fields through the odbc
>connection. They display as dates in access and I can change other fields in
>the table correctly, just can't change the timestamp fields.
>  
>
Timestamp fields aren't for you to change. MySQL changes them when you
edit the record. If you want to edit a timestamp field, what you really
need is another field, of type datetime ... you can edit those to your
heart's content.

>mysql> show create table product_order_choice;
>---+
>| Table| Create Table
> 
>|
>---+
>| product_order_choice | CREATE TABLE `product_order_choice` (
>  `cpny_ID` varchar(4) NOT NULL default '',
>  `prod_ID` varchar(4) NOT NULL default '',
>  `porc_Look_Up_Type` varchar(25) NOT NULL default '',
>  `prft_Sub_Month` char(2) NOT NULL default '00',
>  `prft_Sub_Item` char(2) NOT NULL default '0',
>  `poch_Name` varchar(100) NOT NULL default '',
>  `prct_ID` varchar(4) default NULL,
>  `poch_Value` text,
>  `poch_Image_Path` varchar(255) default NULL,
>  `poch_Link` varchar(255) default NULL,
>  `poch_Link_2` varchar(255) default NULL,
>  `poch_Active` enum('Yes','No','Hidden') NOT NULL default 'Yes',
>  `poch_Timestamp` timestamp(14) NOT NULL,
>  `poch_Create` datetime NOT NULL default '-00-00 00:00:00',
>  PRIMARY KEY
>(`cpny_ID`,`prod_ID`,`porc_Look_Up_Type`,`prft_Sub_Month`,`prft_Sub_Item`,`p
>och_Name`)
>) TYPE=InnoDB |
>---+
>1 row in set (0.00 sec)
>
>  
>
I would strongly recommend against using a composite primary key. This
could be the cause of your error. If you want you can still put a unique
key across all 4 fields, which has the same effect of making sure you
don't get duplicates across them. I would use an unsigned mediumint
auto_increment field as the primary key, and then put your unique index
across the 4 fields that are now your primary key. You will of course
have to re-link your tables in Access after this. You shouldn't need to
rewrite any code immediately after this - you can still link to this
table via the composite index, but you will probably get better
performance by rewriting any joins to use the auto_increment primary key.

-- 
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: Slave stuck at "registering"

[Keith Ivey]

Victor Pendleton wrote:
From your previous posts I know you are very competent so no disrespect
intended. Was the slave data reloaded from the master, and the master.info
reset? What does the master.info log say?
I copied over all the data from the master to the slave with 
rsync, did "LOCK TABLES WITH READ LOCK" and "RESET MASTER" on 
the master and rsynced again (all this while mysqld was not 
running on the slave).  I checked the ownership and permissions, 
deleted the logs, and started MySQL on the slave.  So the 
master.info is as created by the slave.  It looks like this:

   [blank line]
   4
   [master hostname]
   [user]
   [password]
   3306
   60
--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Slave stuck at "registering"

[Victor Pendleton]

>From your previous posts I know you are very competent so no disrespect
intended. Was the slave data reloaded from the master, and the master.info
reset? What does the master.info log say?

-Original Message-
From: Keith Ivey [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 05, 2005 3:16 PM
To: mysql@lists.mysql.com
Subject: Re: Slave stuck at "registering"

Victor Pendleton wrote:
> Can you see the slave thread on the master when you do a show processlist
> from the master?

Yes.  It shows up with the command as "Sleep", nothing in the 
"State" column, and NULL in the "Info" column.  It hangs around 
for a while until "Time" reaches a little over 500, and then 
disappears.

-- 
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC

-- 
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: Slave stuck at "registering"

[Keith Ivey]

Victor Pendleton wrote:
Can you see the slave thread on the master when you do a show processlist
from the master?
Yes.  It shows up with the command as "Sleep", nothing in the 
"State" column, and NULL in the "Info" column.  It hangs around 
for a while until "Time" reaches a little over 500, and then 
disappears.

--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Slave stuck at "registering"

[Victor Pendleton]

Can you see the slave thread on the master when you do a show processlist
from the master?

-Original Message-
From: Keith Ivey [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 05, 2005 1:48 PM
To: mysql@lists.mysql.com
Subject: Slave stuck at "registering"

I'm trying to set up replication over the Internet -- something 
I've done successfully many times before.  But this time I'm 
having problems I haven't run into before, and I'm wondering if 
they're related to firewall settings or network problems or just 
something I'm overlooking.

For the slave, I'm using the same settings that I used 
successfully for another slave of the same master at a different 
location.  The relevant section of my.cnf looks like this:

master-host = [the master hostname]
master-user = [username]
master-password = [password]
server-id   = 47
report-host = [the slave hostname]
slave_compressed_protocol = 1
read-only

When I start the slave I get the proper "connected to master ... 
replication started in log 'FIRST' at position 4" message in the 
slave error log.  In SHOW SLAVE STATUS I see "Connecting to 
master" briefly and then "Registering slave on master".  It 
stays in the "Registering" state for about 500 seconds, after 
which the I/O thread stops and I get "Error on 
COM_REGISTER_SLAVE: 2013 'Lost connection to MySQL server during 
query'" in the error log.

In the error log on the master I get "Aborted connection 114025 
to db: 'unconnected' user: '[username]' host: `[slave IP]' (Got 
an error reading communication packets)".

If I use the mysql command-line client on the slave, I can 
connect fine to the master, and vice versa.  Also, PHP and Perl 
programs on the slave use databases on the master with no 
problems.  What would replication require that the normal 
client-server communication doesn't?

The master is running 4.0.22 on FreeBSD and the slave is running 
4.0.24 on Linux.

Any suggestions?

-- 
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC

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

Slave stuck at "registering"

[Keith Ivey]

I'm trying to set up replication over the Internet -- something 
I've done successfully many times before.  But this time I'm 
having problems I haven't run into before, and I'm wondering if 
they're related to firewall settings or network problems or just 
something I'm overlooking.

For the slave, I'm using the same settings that I used 
successfully for another slave of the same master at a different 
location.  The relevant section of my.cnf looks like this:

   master-host = [the master hostname]
   master-user = [username]
   master-password = [password]
   server-id   = 47
   report-host = [the slave hostname]
   slave_compressed_protocol = 1
   read-only
When I start the slave I get the proper "connected to master ... 
replication started in log 'FIRST' at position 4" message in the 
slave error log.  In SHOW SLAVE STATUS I see "Connecting to 
master" briefly and then "Registering slave on master".  It 
stays in the "Registering" state for about 500 seconds, after 
which the I/O thread stops and I get "Error on 
COM_REGISTER_SLAVE: 2013 'Lost connection to MySQL server during 
query'" in the error log.

In the error log on the master I get "Aborted connection 114025 
to db: 'unconnected' user: '[username]' host: `[slave IP]' (Got 
an error reading communication packets)".

If I use the mysql command-line client on the slave, I can 
connect fine to the master, and vice versa.  Also, PHP and Perl 
programs on the slave use databases on the master with no 
problems.  What would replication require that the normal 
client-server communication doesn't?

The master is running 4.0.22 on FreeBSD and the slave is running 
4.0.24 on Linux.

Any suggestions?
--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to export a table to a CSV file?

[Chris]

Well... it's part of the SELECT statement, so you could try looking at 
the SELECT documentation...

http://dev.mysql.com/doc/mysql/en/select.html
That has a good 9 paragraphs on the 'INTO OUTFILE' part of the SELECT 
statement. It also refers you to...

http://dev.mysql.com/doc/mysql/en/load-data.html
Which has some more info you might find useful.
Chris
Homam S.A. wrote:
NOTE: I sent this message yesterday, but for some
reason it didn't show up in the list. If you've
already received it, I apologize for the
inconvenience.
So here it goes again:

I searched the online manual and all I could find was
a reference to a statement of the form:
SELECT INTO OUTFILE...
http://dev.mysql.com/doc/mysql/en/ansi-diff-select-into-table.html
I searched for this statement on the web and I found
several references to a statement that should look
like this:
select * into outfile 'C:/MyOutfile.txt'
fields terminated by ','
lines terimated by '|'
from myTable
This statement generates a syntax error on MySQL
4.1.9. The following variation also generates a syntax
error:
select * from table into outfile 'C:/MyOutfile.txt'
fields terminated by ','
lines terimated by '|'
from myTable
Why is this statement rejected? Why is it not
documented anywhere in the manual except for a passing
reference?
And if there's no such a statement, how come there are
many references to it on the web that date back to
MySQL versions older than 4.1.9?
Finally, if this statement has been depricated/removed
from 4.1.9, is there any way to export a table to a
CSV file?
Thanks,
Homam
 


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

Re: [Fwd: Re: Collation problems or messed joins?]

[Andrés Villanueva]

Actually, I did a full backup, uninstalled everything, installed a clean 
4.1.11 and restored the backup
The data seems to be fine. The funny thing is that if i change one of 
the inners for a left everything works fine! I think it's a bug, and an 
ugly one...

Andrés Villanueva
Gleb Paharenko wrote:
Is it possible your data was changed during upgrade? How
have you performed it?



Andr$s Villanueva <[EMAIL PROTECTED]> wrote:
 

Hi! thanks for your response.
   

 

 

This are the values of the variables you asked for:
   

 

 

collation_connection: utf8_general_ci
   

 

collation_database: utf8_general_ci
   

 

collation_server: utf8_general_ci
   

 

character_set_client: utf8
   

 

character_set_connection: utf8
   

 

character_set_database: utf8
   

 

character_set_results: NULL
   

 

character_set_server: utf8
   

 

character_set_system: utf8
   

 

 

These were taken from a query inside the app. From the query browser the 
   

 

only difference is:
   

 

character_set_results: utf8
   

 

 

Anyway, the same query has the same results in the app and the query 
   

 

browser. The app is using the .net connector
   

 

 

Thanks
   

 

Andr$s Villanueva
   

 

 

 

 

Gleb Paharenko wrote:
   

 

 

Hello.
 

 

 

 

 

Please, send the output of the following statements:
 

 

 

 

 

show variables like '%colla%';
 

 

 

show variables like '%char%';
 

 

 

 

 

 

 

 

 

Andr$s Villanueva <[EMAIL PROTECTED]> wrote:
 

 

 

 

 

 

Anyone??
   

 

  
   

 

 

 

 

 

 

 

 

 

 

 Original Message 
   

 

  
   

 

 

 

 

 

 

 

 

 

 

The tables are now entirely in utf8, and that is also the instance's 
   

 

  
   

 

 

 

 

 

 

default.
   

 

  
   

 

 

 

 

 

 

I'm using the .net dll to connect and the connection string has charset 
   

 

  
   

 

 

 

 

 

 

= utf8.
   

 

  
   

 

 

 

 

 

 

I'm testing everything with the query browser, and i get the exact same 
   

 

  
   

 

 

 

 

 

 

behaviour.
   

 

  
   

 

 

 

 

 

 

Again, if anyone wants a script to recreate the tables with some data, I 
   

 

  
   

 

 

 

 

 

 

can send it to you instantly.
   

 

  
   

 

 

 

 

 

 

 

 

 

 

Thanks
   

 

  
   

 

 

 

 

 

 

Andr$s Villanueva
   

 

  
   

 

 

 

 

 

 

 

 

 

 

Kevin Cowley wrote:
   

 

  
   

 

 

 

 

 

 

 

 

 

 

Have you check that the collation for the text columns match the
 

 


 

 

 

 

 

 

 

collation for the table and that you've set UTF8 for the query  (set
 

 


 

 

 

 

 

 

 

char set utf8).
 

 


 

 

 

 

 

 

 

 

 

 

 

Kevin Cowley
 

 


 

 

 

 

 

 

 

Product Development
 

 


 

 

 

 

 

 

 

Alchemetrics Ltd
 

 


 

 

 

 

 

 

 

SMARTER DATA , FASTER
 

 


 

 

 

 

 

 

 

Tel: 0118 902 9000 (swithcboard)
 

 


 

 

 

 

 

 

 

Tel: 0118 902 9099 (direct)
 

 


 

 

 

 

 

 

 

Web: www.alchemetrics.co.uk
 

 


 

 

 

 

 

 

 

Email: [EMAIL PROTECTED]
 

 


 

 

 

 

 

 

 

 

 

 

 

 


 

 

 

 

 

 

 

 

 

 


 


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

How to export a table to a CSV file?

[Homam S.A.]

NOTE: I sent this message yesterday, but for some
reason it didn't show up in the list. If you've
already received it, I apologize for the
inconvenience.

So here it goes again:




I searched the online manual and all I could find was
a reference to a statement of the form:

SELECT INTO OUTFILE...

http://dev.mysql.com/doc/mysql/en/ansi-diff-select-into-table.html

I searched for this statement on the web and I found
several references to a statement that should look
like this:

select * into outfile 'C:/MyOutfile.txt'
fields terminated by ','
lines terimated by '|'
from myTable

This statement generates a syntax error on MySQL
4.1.9. The following variation also generates a syntax
error:

select * from table into outfile 'C:/MyOutfile.txt'
fields terminated by ','
lines terimated by '|'
from myTable

Why is this statement rejected? Why is it not
documented anywhere in the manual except for a passing
reference?

And if there's no such a statement, how come there are
many references to it on the web that date back to
MySQL versions older than 4.1.9?

Finally, if this statement has been depricated/removed
from 4.1.9, is there any way to export a table to a
CSV file?

Thanks,

Homam


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

Re: How to backup and restore database

[Karam Chand]

I prefder to use a FREE GUI like SQLyog found at
http://www.webyog.com

Karam
--- Gary Richardson <[EMAIL PROTECTED]> wrote:
> try mysqldump -keq SDN > SDN.sql
> 
> Then you can import it on another instance using
> mysql  -u root
> new_instance < SDN.sql
> 
> On 5/5/05, zlf <[EMAIL PROTECTED]> wrote:
> > Hi all,
> > I have installed a MySQL5.0 instance on
> Windows. And then created a
> > database( named 'SDN' ). Now I want to move this
> database to another
> > MySQL5.0 envirnment on Linux.
> > How can I make it. Thx
> > 
> > zlf
> > 
> > --
> > 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]
> 
> 



Discover Yahoo! 
Get on-the-go sports scores, stock quotes, news and more. Check it out! 
http://discover.yahoo.com/mobile.html

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

Re: Work / Home DB synchronization

[Karam Chand]

You require SQLyog's Data Sync Tool. Download it from
http://www.webyog.com

You can find an article on it at:
http://www.sitepoint.com/article/mysql-data-sqlyog-job-agent

Karam
--- Scott Purcell <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I am developing a web application in which I have
> been working between home and an away office.
> Anyway, up to lately, most of my efforts have been
> writing the queries, etc for the backend. So I have
> a text file that dumps the database and repopulates
> them. And up until now, it has been effective in
> getting some "basic" data into the datase to code
> against.
> 
> But now, that portion is done, and I am starting to
> port more data into the database. Data I need to
> display purposes, etc.
> 
> Is there a simple, easy, effective way to transfer
> the data between home and away? What I do for the
> code, is create a .war file and copy that to a ram
> stick and transfer it that way. But when it comes to
> the database, I am not sure how to keep them
> synchronized?
> 
> Less than 10,000 total records at launch time.
> 
> Thanks,
> 
> Scott K Purcell 
>  
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

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

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

Re: radius account management using sql

[Rhino]

If testuser signed in the day before the command was executed - i.e.
yesterday - he/she is well within the past 6 months and therefore
*shouldn't* be disabled.

Rhino

- Original Message - 
From: "Allan P. Magmanlac" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, May 05, 2005 12:53 PM
Subject: radius account management using sql


> Hello,
> We have  radius server and using mysql. I would like to determine
> who are the users
> who have not login to our dialup server for over six months and then
> disable the account
> In the example below, I'm just using > 10 days
>
> mysql> SELECT distinct
> username,framedipaddress,acctstarttime,acctstoptime from radacct where
> DATEDIFF(CURDATE(),acctstoptime) > 10 order by username;
>
++-+-+-+
> | username   | framedipaddress | acctstarttime   | acctstoptime
|
>
++-+-+-+
> | testuser   | 191.168.2.37  | 2005-04-11 14:56:40 | 2005-04-11 15:02:45 |
> | testuser   | 191.168.2.47  | 2005-04-24 15:23:18 | 2005-04-24 15:35:58 |
> | testuser   | 191.168.2.42  | 2005-04-19 18:38:51 | 2005-04-19 19:06:11 |
> | testuser  | 191.168.2.51  | 2005-04-14 16:57:14 | 2005-04-14 18:16:20 |
> | testuser   | 191.168.2.41  | 2005-04-25 16:22:30 | 2005-04-25 16:32:44 |
>
> The problem here is that  "testuser"  may have logged in the day before
> the command was executed. So my question is how to get the  users last
> login to the server and
> if it's more than 6 months disable the account.
>
> Thanks
>
>
> -- 
> 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.11.5 - Release Date: 04/05/2005
>
>



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


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

radius account management using sql

[Allan P. Magmanlac]

Hello,
   We have  radius server and using mysql. I would like to determine 
who are the users
who have not login to our dialup server for over six months and then 
disable the account
In the example below, I'm just using > 10 days

mysql> SELECT distinct 
username,framedipaddress,acctstarttime,acctstoptime from radacct where 
DATEDIFF(CURDATE(),acctstoptime) > 10 order by username;
++-+-+-+
| username   | framedipaddress | acctstarttime   | acctstoptime|
++-+-+-+
| testuser   | 191.168.2.37  | 2005-04-11 14:56:40 | 2005-04-11 15:02:45 |
| testuser   | 191.168.2.47  | 2005-04-24 15:23:18 | 2005-04-24 15:35:58 |
| testuser   | 191.168.2.42  | 2005-04-19 18:38:51 | 2005-04-19 19:06:11 |
| testuser  | 191.168.2.51  | 2005-04-14 16:57:14 | 2005-04-14 18:16:20 |
| testuser   | 191.168.2.41  | 2005-04-25 16:22:30 | 2005-04-25 16:32:44 |

The problem here is that  "testuser"  may have logged in the day before 
the command was executed. So my question is how to get the  users last 
login to the server and
if it's more than 6 months disable the account.

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

Work / Home DB synchronization

[Scott Purcell]

Hello,

I am developing a web application in which I have been working between home and 
an away office. Anyway, up to lately, most of my efforts have been writing the 
queries, etc for the backend. So I have a text file that dumps the database and 
repopulates them. And up until now, it has been effective in getting some 
"basic" data into the datase to code against.

But now, that portion is done, and I am starting to port more data into the 
database. Data I need to display purposes, etc.

Is there a simple, easy, effective way to transfer the data between home and 
away? What I do for the code, is create a .war file and copy that to a ram 
stick and transfer it that way. But when it comes to the database, I am not 
sure how to keep them synchronized?

Less than 10,000 total records at launch time.

Thanks,

Scott K Purcell 
 

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

Re: How to backup and restore database

[Gary Richardson]

try mysqldump -keq SDN > SDN.sql

Then you can import it on another instance using mysql  -u root
new_instance < SDN.sql

On 5/5/05, zlf <[EMAIL PROTECTED]> wrote:
> Hi all,
> I have installed a MySQL5.0 instance on Windows. And then created a
> database( named 'SDN' ). Now I want to move this database to another
> MySQL5.0 envirnment on Linux.
> How can I make it. Thx
> 
> zlf
> 
> --
> 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]

THCC Upcoming Events

[Tennessee Hispanic Chamber of Commerce]

Title: The Official Tennessee Hispanic Chamber of Commerce



  
		
		  
			
			  
			  
		
			


(Mailing List Information, including unsubscription instructions, 
is located at the end of this message.)

Upcoming THCC Networking Mixer

May 19 
Mixer in collaboration with CABLE 
Jalapeño's Restaurant
219 Largo Drive 
Nashville
5:30 pm - 8:00 pm. 
Admission: $15 
 
Admission will include: one beer and a buffet, featuring 

nachos with cochinita (marinated roast pork), 
Jalapeños rellenos, 
quesadillas supreme, and 
flautas with chicken and avocado.


Please RSVP by May 16th.


Sponsorship opportunities available. 
Mixer sponsorship starts at $200 and includes:
Website advertisement, newsletters advertisement, and a display table at the mixer.
If you are interested in becoming a mixer sponsor, please visit our event sponsor page (http://www.tnhispanic.com/mixer_sponsors.html)
If you are sponsoring in response to this email, two months of free website advertising will be added to your sponsorship package.

Our April Sponsor was: 
Cricket Communications
 


Other Events around town

May 1
El Dia del Nino
The THCC participated in this event.

May 2-6
The Fifth Annual Southeast Regional Civil Rights Training Conference
Click the link below to read more about the event. 
http://www.tnhispanic.com/events.html.
Representatives of the THCC Board of Directors are participating in this event.

May 5
Cinco de Mayo
Location: New Hacienda Restaurant, Nolensville Road (by the zoo)
The THCC is participating in this event.


News
April 13
Clarksville's Austin Peay State University became the first institute of higher education in Tennessee to recognize the change and growth in its Hispanic student enrollment with its dedication of a Cultural Arts Center for Latino heritage.
To read the article, click http://www.tnhispanic.com/events_news.html.


Coming Soon 


 Members and Businesses Database

The THCC is working on a database system where our members and affiliated businesses can be listed and searched for. Detailed listing will be available for businesses wanting to put pictures, online brochures, advertising, and articles in the database.

 Discount Services & Discount Cards
The THCC is working on bringing our members and the community a set of discount services provided by our members such as Life Insurance, Health Insurance, Mortgages, Real Estate, Investments, Financial Planning, Website Solutions, and Accounting. If you are interested in offering your products or services under our discount services program, please contact  [EMAIL PROTECTED].

 Online Articles Publications
The THCC is currently accepting educational articles suitable for posting on the THCC resources page on our corporate website. If you have written an article which you feel will benefit the community, please email us by  clicking here.


 Educational Seminar Series
The THCC is working on Educational Seminar Series. If you are interested in becoming a speaker or getting involved, please contact us by  clicking here.



Please check our website regularly for more information.
http://www.tnhispanic.com






--

The following information is a reminder of your current mailing
list subscription: 

You are subscribed to the following list: 

Tennessee Hispanic Chamber of Commerce

	
Using the following email:

mysql@lists.mysql.com

You may automatically unsubscribe from this list at any time by 
visiting the following URL:


 
  http://www.tnhispanic.info/cgi-bin/dada2/mail.cgi?f=u&l=THCC&[EMAIL PROTECTED]&p=13124954
 


If the above URL is inoperable, make sure that you have copied the 
entire address. Some mail readers will wrap a long URL and thus break
this automatic unsubscribe mechanism. 

You may also change your subscription by visiting this list's main screen:


 
  http://www.tnhispanic.info/cgi-bin/dada2/mail.cgi?f=list&l=THCC
 


If you're still having trouble, please contact the list owner at: 

	
	 
	 [EMAIL PROTECTED]
	 
	

The following physical address is associated with this mailing list: 


 814 Church Street, 5th Floor
Nashville TN 37203
 

 

Powered by Dada Mail 2.8.15Copyright © 1999-2004, Simoni Creative.

			
  
	
  

	
	
	The Tennessee Hispanic Chamber of Commerce
	P.O. Box 69 - Hermitage, TN 37076
	Phone: 615-251-3585 - Fax: 615-251-3599
	Reply to: [EMAIL PROTECTED]
	







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

RE: ACCESS ODBC Interface whit 5.0.4

[Gordon]

Thanks, that looks like my problem. I 'll wait until the fix percolates into
the 5. stream.

-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 04, 2005 10:00 AM
To: mysql@lists.mysql.com
Subject: Re: ACCESS ODBC Interface whit 5.0.4

Hello.

Have you been here?

  http://bugs.mysql.com/bug.php?id=9211



>I have been using ACCESS to do simpe data editing on our MySQL tables
>for 3
>years.
> I recently installed 5.0.4 on my machine to evaluate it. I linked the
> tables
> into ACCESS through my old ODBC driver and  got 
>  ODBC-update on a linked table 'product_order_choice' failed
>  [Microsoft][ODBC Driver Manager] SQL data type out of rance (#0)
>   I then downloaded and installed the current ODBC connector
>   [3.51.11]
>   thinking maybe it was my old ODBC copy, but get the same result.
>Has anyone else seen this or have any ideas?
>
"Gordon" <[EMAIL PROTECTED]> wrote:


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



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

RE: ACCESS ODBC Interface whit 5.0.4

[Gordon]

Here is the table structure.
In this case I tried to change the 4 characters in prod_ID when I get the
message, but I get the same message when I try to make any changes.

Doing the exact same activity works fine on all of the 3.23/4.0/4.1 versions
I have installed previouly. Tables with unsigned integer fields work just
fine through this interface on previous versions. The only anomaly up to
this point is that I can't change timestamp fields through the odbc
connection. They display as dates in access and I can change other fields in
the table correctly, just can't change the timestamp fields.

mysql> show create table product_order_choice;
---+
| Table| Create Table
 
|
---+
| product_order_choice | CREATE TABLE `product_order_choice` (
  `cpny_ID` varchar(4) NOT NULL default '',
  `prod_ID` varchar(4) NOT NULL default '',
  `porc_Look_Up_Type` varchar(25) NOT NULL default '',
  `prft_Sub_Month` char(2) NOT NULL default '00',
  `prft_Sub_Item` char(2) NOT NULL default '0',
  `poch_Name` varchar(100) NOT NULL default '',
  `prct_ID` varchar(4) default NULL,
  `poch_Value` text,
  `poch_Image_Path` varchar(255) default NULL,
  `poch_Link` varchar(255) default NULL,
  `poch_Link_2` varchar(255) default NULL,
  `poch_Active` enum('Yes','No','Hidden') NOT NULL default 'Yes',
  `poch_Timestamp` timestamp(14) NOT NULL,
  `poch_Create` datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY
(`cpny_ID`,`prod_ID`,`porc_Look_Up_Type`,`prft_Sub_Month`,`prft_Sub_Item`,`p
och_Name`)
) TYPE=InnoDB |
---+
1 row in set (0.00 sec)

mysql>

-Original Message-
From: Daniel Kasak [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 04, 2005 5:16 PM
To: Gordon; 'MySQL'
Subject: Re: ACCESS ODBC Interface whit 5.0.4

Gordon wrote:

>I have been using ACCESS to do simpe data editing on our MySQL tables for 3
>years.
>
> 
>
>I recently installed 5.0.4 on my machine to evaluate it. I linked the
tables
>into ACCESS through my old ODBC driver and  got 
>
> 
>
>ODBC-update on a linked table 'product_order_choice' failed
>
>[Microsoft][ODBC Driver Manager] SQL data type out of rance (#0)
>
> 
>
>I then downloaded and installed the current ODBC connector [3.51.11]
>thinking maybe it was my old ODBC copy, but get the same result.
>
> 
>
>Has anyone else seen this or have any ideas?
>
>
>  
>
It would help if you posted details of the table / data you're working with.
'Data type out of range' usually means you've tried to put a numerical
value in a field which is too small. For example, you may be trying to
put an int value in a mediumint field. Or it could be that you're using
a field type not supported by MS Access, such as an unsigned int or a
bigint.

-- 
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 /tmp/mysql.sock

[Mark Sargent]

Mark Sargent wrote:
Simon Garner wrote:
Mark Sargent wrote:
Hi All,
mysql is running, but, there seems to be no /tmp/mysql.sock file. 
How is this file generated..? I can't connect, keep getting errors. 
What about via a port..? What is the argument for that..? Cheers.

Mark Sargent.
It's probably not in /tmp any more. Try /var/lib/mysql/mysql.sock, or 
look at `mysqladmin variables` and check the 'socket' value.

-Simon
Hi All,
Simon, if it is somewhere else, how do I let other apps etc know..? If 
the client, which is the same PC as the server, is of the same 
version/install, why doesn't it know where it is..? Cheers.

Mark Sargent.
Hi All,
[EMAIL PROTECTED] ~]# mysql_config --socket
/tmp/mysql.sock
Is that what you were referring to, Simon..? Looks to me like it should 
be where mysql(and phpmyadmin also) is trying to access it from. Any 
thoughts on this further..? Cheers.

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

Re: where and dates

[Chris Knipe]

Found my problem...
Thanks anyways :)
--
Chris.
I love deadlines. I especially love the whooshing sound they make as they 
fly by..." - Douglas Adams, 'Hitchhiker's Guide to the Galaxy'

- Original Message - 
From: "Chris Knipe" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, May 05, 2005 4:09 PM
Subject: where and dates


Hi,
I dont think it's needed to go into to much details here but the 
query:

 SELECT CONCAT(Airports.IATA, ' - ', Airports.Name) AS ArrivingFrom,
ADDTIME(FlightData.TimeStamp, 
SUBTIME(FlightData.Enroute,DATE_FORMAT(SUBTIME(FlightData.TimeStamp,FlightData.ActDepTime),'%H:%i:%s'))) 
AS ArrvTime,
CONCAT(FlightData.Network, ' - ', FlightData.PilotCallSign) AS 
FlightNum
   FROM FlightData
   LEFT JOIN Airports ON FlightData.AirportDep=Airports.ICAO
  WHERE FlightData.AirportDes='KJFK'
AND DATE_SUB(CURDATE(),INTERVAL 2 HOUR) <= 
ADDTIME(FlightData.TimeStamp, 
SUBTIME(FlightData.Enroute,DATE_FORMAT(SUBTIME(FlightData.TimeStamp,FlightData.ActDepTime),'%H:%i:%s')))
GROUP BY FlightData.PilotCallSign
ORDER BY ArrvTime;

Result:
+---+-+---+
| ArrivingFrom  | ArrvTime| FlightNum 
|
+---+-+---+
| YYZ - Toronto/Pearson Intl| 2005-05-04 22:22:00 | V - ACA114 
|
| LHR - Heathrow| 2005-05-04 22:42:00 | V - BAW115 
|
|  - Norman Manley Intl | 2005-05-04 22:45:00 | V - AAL645 
|
| BOS - Logan Intl  | 2005-05-04 22:50:00 | V - AE729 
|
| NULL  | 2005-05-05 00:00:00 | V - N6340D 
|
| JFK - Kennedy Intl| 2005-05-05 00:00:00 | V - AFA5290 
|
| MCO - Orlando Intl| 2005-05-05 00:05:00 | V - FEDEX1472 
|
| MCO - Orlando Intl| 2005-05-05 00:25:00 | V - FEDEX1563 
|
| BOS - Logan Intl  | 2005-05-05 01:23:00 | V - AAL328 
|
| YYZ - Toronto/Pearson Intl| 2005-05-05 01:40:00 | V - ACA221 
|
| TPA - Tampa Intl  | 2005-05-05 02:10:00 | V - N171E 
|
| BOS - Logan Intl  | 2005-05-05 02:10:00 | V - N37TJ 
|
| MIA - Miami Intl  | 2005-05-05 02:40:00 | V - JAL5837 
|
|  - Eduardo Gomes Intl | 2005-05-05 02:42:00 | V - PST9864 
|
| CVG - Cincinnati/Northern Ky Intl | 2005-05-05 03:15:00 | V - MTN219 
|
| YYZ - Toronto/Pearson Intl| 2005-05-05 03:45:00 | V - UAL392 
|
| BOS - Logan Intl  | 2005-05-05 03:50:00 | V - DVA2314 
|
| JFK - Kennedy Intl| 2005-05-05 05:30:00 | V - WSM-9950 
|
| YWG - Winnipeg Intl   | 2005-05-05 05:40:00 | V - ML853 
|
| ZRH - Zurich  | 2005-05-05 05:50:00 | V - AAL213 
|
| ATL - The Hartsfield Atlanta Intl | 2005-05-05 06:05:00 | V - AAL209 
|
| SJU - Luis Munoz Marin Intl   | 2005-05-05 06:17:00 | V - AAL117 
|
| LHR - Heathrow| 2005-05-05 08:40:00 | V - BAW01 
|
| ZRH - Zurich  | 2005-05-05 13:00:00 | V - LH3733 
|
| PRG - Ruzyne  | 2005-05-05 13:10:00 | V - CSA255 
|
| MAN - Manchester  | 2005-05-05 15:30:00 | V - BAW1503 
|
| VIE - Schwechat   | 2005-05-05 17:20:00 | V - AUA85Y 
|
+---+-+---+

Problem... Well, the date clause on the WHERE part of the query is not 
doing what I am hoping it would.  I need to get all records where ArrvTime 
is bigger than (i.e. in the future) of NOW() + 2Hrs.  All my dates in the 
database are stored in GMT, so I only need to use NOW()-2H (which is the 
same as DATE_SUB(CURDATE(),INTERVAL 2 HOUR)).

Can anyone help perhaps?
--
Chris.
I love deadlines. I especially love the whooshing sound they make as they 
fly by..." - Douglas Adams, 'Hitchhiker's Guide to the Galaxy'

--
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: Tables lost in new location of database

[Anoop kumar V]

No - mysqld-nt-max is not running - I can see in the task manager that only 
mysqld-nt is running!!
I also confirmed this by checking in my services - I only have mysqld-nt 
present as a service for mysql!

Should I run mysqld-nt-max for this functionality to work??

Also - I need to run only myisam and nothing else - we do not use innodb or 
bdb or anything else but myisam.

-Anoop

On 5/4/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote:
> 
> Hello.
> 
> mysql.exe is a client utility, I've meant the server process. Check that
> 
> you're running mysqld-max-nt.exe.
> 
> 
> >I am not sure about max - but I am running mysql.exe (I think it is the
> 
> >same as mysqld-nt).
> 
> >
> 
> >No - when I do a select * from on a table in the moved database - I get
> 
> >the error that the table does not exist.
> 
> >
> 
> >well - how can i tell if i have disabled symlinks???
> 
> >
> 
> >I have not checked using 4.1.11.
> 
> Anoop kumar V <[EMAIL PROTECTED]> wrote:
> 
> --
> 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]
> 
> 


-- 
Thanks and best regards,
Anoop

Re: no /tmp/mysql.sock

[Mark Sargent]

Simon Garner wrote:
Mark Sargent wrote:
Hi All,
mysql is running, but, there seems to be no /tmp/mysql.sock file. How 
is this file generated..? I can't connect, keep getting errors. What 
about via a port..? What is the argument for that..? Cheers.

Mark Sargent.
It's probably not in /tmp any more. Try /var/lib/mysql/mysql.sock, or 
look at `mysqladmin variables` and check the 'socket' value.

-Simon
Hi All,
Simon, if it is somewhere else, how do I let other apps etc know..? If 
the client, which is the same PC as the server, is of the same 
version/install, why doesn't it know where it is..? Cheers.

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

where and dates

[Chris Knipe]

Hi,
I dont think it's needed to go into to much details here but the query:
 SELECT CONCAT(Airports.IATA, ' - ', Airports.Name) AS ArrivingFrom,
ADDTIME(FlightData.TimeStamp, 
SUBTIME(FlightData.Enroute,DATE_FORMAT(SUBTIME(FlightData.TimeStamp,FlightData.ActDepTime),'%H:%i:%s'))) 
AS ArrvTime,
CONCAT(FlightData.Network, ' - ', FlightData.PilotCallSign) AS 
FlightNum
   FROM FlightData
   LEFT JOIN Airports ON FlightData.AirportDep=Airports.ICAO
  WHERE FlightData.AirportDes='KJFK'
AND DATE_SUB(CURDATE(),INTERVAL 2 HOUR) <= 
ADDTIME(FlightData.TimeStamp, 
SUBTIME(FlightData.Enroute,DATE_FORMAT(SUBTIME(FlightData.TimeStamp,FlightData.ActDepTime),'%H:%i:%s')))
GROUP BY FlightData.PilotCallSign
ORDER BY ArrvTime;

Result:
+---+-+---+
| ArrivingFrom  | ArrvTime| FlightNum |
+---+-+---+
| YYZ - Toronto/Pearson Intl| 2005-05-04 22:22:00 | V - ACA114|
| LHR - Heathrow| 2005-05-04 22:42:00 | V - BAW115|
|  - Norman Manley Intl | 2005-05-04 22:45:00 | V - AAL645|
| BOS - Logan Intl  | 2005-05-04 22:50:00 | V - AE729 |
| NULL  | 2005-05-05 00:00:00 | V - N6340D|
| JFK - Kennedy Intl| 2005-05-05 00:00:00 | V - AFA5290   |
| MCO - Orlando Intl| 2005-05-05 00:05:00 | V - FEDEX1472 |
| MCO - Orlando Intl| 2005-05-05 00:25:00 | V - FEDEX1563 |
| BOS - Logan Intl  | 2005-05-05 01:23:00 | V - AAL328|
| YYZ - Toronto/Pearson Intl| 2005-05-05 01:40:00 | V - ACA221|
| TPA - Tampa Intl  | 2005-05-05 02:10:00 | V - N171E |
| BOS - Logan Intl  | 2005-05-05 02:10:00 | V - N37TJ |
| MIA - Miami Intl  | 2005-05-05 02:40:00 | V - JAL5837   |
|  - Eduardo Gomes Intl | 2005-05-05 02:42:00 | V - PST9864   |
| CVG - Cincinnati/Northern Ky Intl | 2005-05-05 03:15:00 | V - MTN219|
| YYZ - Toronto/Pearson Intl| 2005-05-05 03:45:00 | V - UAL392|
| BOS - Logan Intl  | 2005-05-05 03:50:00 | V - DVA2314   |
| JFK - Kennedy Intl| 2005-05-05 05:30:00 | V - WSM-9950  |
| YWG - Winnipeg Intl   | 2005-05-05 05:40:00 | V - ML853 |
| ZRH - Zurich  | 2005-05-05 05:50:00 | V - AAL213|
| ATL - The Hartsfield Atlanta Intl | 2005-05-05 06:05:00 | V - AAL209|
| SJU - Luis Munoz Marin Intl   | 2005-05-05 06:17:00 | V - AAL117|
| LHR - Heathrow| 2005-05-05 08:40:00 | V - BAW01 |
| ZRH - Zurich  | 2005-05-05 13:00:00 | V - LH3733|
| PRG - Ruzyne  | 2005-05-05 13:10:00 | V - CSA255|
| MAN - Manchester  | 2005-05-05 15:30:00 | V - BAW1503   |
| VIE - Schwechat   | 2005-05-05 17:20:00 | V - AUA85Y|
+---+-+---+
Problem... Well, the date clause on the WHERE part of the query is not doing 
what I am hoping it would.  I need to get all records where ArrvTime is 
bigger than (i.e. in the future) of NOW() + 2Hrs.  All my dates in the 
database are stored in GMT, so I only need to use NOW()-2H (which is the 
same as DATE_SUB(CURDATE(),INTERVAL 2 HOUR)).

Can anyone help perhaps?
--
Chris.
I love deadlines. I especially love the whooshing sound they make as they 
fly by..." - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' 

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

FW: Slow queries, why?

[lakshmi.narasimharao]

Hi,

  Thank you. I have a doubt, you mentioned one equation as

Memory Used By MySQL = Innodb_buffer_pool_size + key_buffer_size +
max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) +
max_connections*2MB


Is it against mysqld or for each mysql prompt?.

Could you please confirm it?


Thanks,
Narasimha

From: David Griffiths [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 04, 2005 10:19 PM
To: Gleb Paharenko
Cc: mysql@lists.mysql.com
Subject: Re: Slow queries, why?

Yes, indexes slow down inserts (or updates that change the value of a
column that is indexed).

Also, remember that MySQL only uses one index per per table in a query.
So if there are some columns in your table that are indexed, but,

1) Have poor cardinality (number of distinct values - low cardinality
means there aren't many distinct values)
2) Are only used in a where clause with another column that has good
cardinality

then they are an excellent candidate for removal.

While "EXPLAIN" is great for queries, it won't help much with an insert;

it might be useful for figuring out what indexes are used, and which
ones aren't.

Use "show innodb status" to get an idea of what's going on (Gleb
suggested it in the link to the innodb monitor).

You should also post the relevant parts of your my.cnf file; have you
seen this equation before:

Memory Used By MySQL = Innodb_buffer_pool_size + key_buffer_size +
max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) +
max_connections*2MB

Use it to calculate how much memory you are using.

Finally, read up on phantom reads:
http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/InnoDB_Next-key_
locking.html

This might be what's happening.

David


Gleb Paharenko wrote:

>Hello.
>
>
>
> 
>
>>We're running MySQL 4.11 on a machine with 2GB memory, the table is
>>   
>>
>
> 
>
>>InnoDB with a compound primary key, and additional indexes on all rows
>>   
>>
>
> 
>
>>with searchable options in the API. Any generic advice or admin tools
>>   
>>
>
> 
>
>>would be great.
>>   
>>
>
>
>
>Use EXPLAIN to determine how efficient your indexes are. Using a lot of
>
>keys could slow down the INSERT operations but fasten the SELECTs.
>
>InnoDB monitors might be helpful in your case as well. See:
>
>  http://dev.mysql.com/doc/mysql/en/explain.html
>
>  http://dev.mysql.com/doc/mysql/en/innodb-monitor.html
>
>
>
>
>
>
>
>Joseph Cochran <[EMAIL PROTECTED]> wrote:
>
> 
>
>>So here's my situation: we have a database that has a table of about 5
>>   
>>
>
> 
>
>>million rows. To put a new row into the table, I do an INSERT ...
>>   
>>
>
> 
>
>>SELECT, pulling data from one row in the table to seed the data for
>>   
>>
>
> 
>
>>the new row. When there are no active connections to the DB other than
>>   
>>
>
> 
>
>>the one making the INSERT, it runs like a charm. But during normal
>>   
>>
>
> 
>
>>daytime operation, when we run around 50 connections (most sleeping at
>>   
>>
>
> 
>
>>any one time), it takes up to two minutes to do, and ends up locking
>>   
>>
>
> 
>
>>any other inserts or updates against that table for the entire time.
>>   
>>
>
> 
>
>
> 
>
>>I'll get into more specifics if they're required, but I wanted to ask
>>   
>>
>
> 
>
>>in general if MySQL has tools to diagnose this, or if anyone has had
>>   
>>
>
> 
>
>>general situations like this. In SQL Server (which is where I have
>>   
>>
>
> 
>
>>most of my experience) I could use the trace tool and the Query
>>   
>>
>
> 
>
>>Analyzer to tell what the execution plan for the query was and thus
>>   
>>
>
> 
>
>>what's stalling it (an index gone bad, a weird locking situation,
>>   
>>
>
> 
>
>>etc).
>>   
>>
>
> 
>
>
> 
>
>>We're running MySQL 4.11 on a machine with 2GB memory, the table is
>>   
>>
>
> 
>
>>InnoDB with a compound primary key, and additional indexes on all rows
>>   
>>
>
> 
>
>>with searchable options in the API. Any generic advice or admin tools
>>   
>>
>
> 
>
>>would be great.
>>   
>>
>
> 
>
>
> 
>
>>-- Joe
>>   
>>
>
> 
>
>
>
>
> 
>


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




Confidentiality Notice

The information contained in this electronic message and any attachments to 
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or 
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

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

Re: The age old delete duplicates

[Rhino]

- Original Message - 
From: "Scott Haneda" <[EMAIL PROTECTED]>
To: "MySql" 
Sent: Thursday, May 05, 2005 3:39 AM
Subject: The age old delete duplicates


> I have been researching on how to deal with duplicate data.  While I have
a
> case where there can be duplicate data, and I want to get rid of it, the
> general ideas I seem to find are as follows, plus my own, which I would
like
> opinions on since I have not seen it mentioned.
>
> I am not in a situation where I can test for duplicates on insert, so the
on
> duplicate key update deal does me no good here.
>
Okay, stop right there. If you design your tables with appropriate primary
keys, there is no reason you should be getting duplicates in the first
place. Furthermore, there is no reason that you have to test for those
duplicates either at the command line or in your programs.

For example, if I create an Employee table with one row per employee in my
company, and if I give that table an appropriate primary key, say employee
number or social security number or possibly the full name plus the date of
birth, MySQL itself will prohibit me from inserting any rows that duplicate
an existing row. For instance, if employee number is my primary key and I've
already got an employee whose number is 123, there is no way that MySQL will
allow me to insert a second record whose key is 123. It's that simple.


> Most data points to selecting distinct records to a temp table, deleting
the
> source records, and selecting back into the original, three steps.  I
> imagine there is a bit of overhead to the create temp table, it afterall,
is
> writing a small file to disk.
>
> saying I want to favor newer records over old, and I have a pk of id, is
> there any flaw in:
> SELECT distinct(id) from table where my_param = 'foo' order by id;
> that gives me the newest unique record id's
> // little logic to convert result set inot a mysql list
> DELETE from table where my_param = 'foo' and id NOT IN (the_result)
>
> So I have two steps, I save the temp table creation, AND I don't bother
> doing anything at all in the case where there are only uniqe records to
> begin with, ie: nothing to delete.
>
> Is there something wrong with this, is seems a wee bit more efficient that
> the temp table deal for my case?
>
This sort of messing around should only be necessary if you neglect to put a
primary key on the table in the first place. And I have yet to hear a
convincing case for ever not having a primary key in a relational database,
although I suppose it is always possible that someone is going to come up
with one.

It's not always obvious what the primary key should be though so if you're
having trouble thinking of a good primary key for your table(s), describe
the data and we'll try to help you choose the primary key(s) you need.

Seriously, I think you have to decide to keep the barn door closed (create
primary keys on all of your tables) before your horses have left rather than
worry about the best way to close it once the horses are gone.

Rhino



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


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

Re: no /tmp/mysql.sock

[Simon Garner]

Mark Sargent wrote:
Hi All,
mysql is running, but, there seems to be no /tmp/mysql.sock file. How is 
this file generated..? I can't connect, keep getting errors. What about 
via a port..? What is the argument for that..? Cheers.

Mark Sargent.
It's probably not in /tmp any more. Try /var/lib/mysql/mysql.sock, or 
look at `mysqladmin variables` and check the 'socket' value.

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

no /tmp/mysql.sock

[Mark Sargent]

Hi All,
mysql is running, but, there seems to be no /tmp/mysql.sock file. How is 
this file generated..? I can't connect, keep getting errors. What about 
via a port..? What is the argument for that..? Cheers.

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

How to backup and restore database

[zlf]

Hi all,
I have installed a MySQL5.0 instance on Windows. And then created a 
database( named 'SDN' ). Now I want to move this database to another 
MySQL5.0 envirnment on Linux.
How can I make it. Thx

zlf




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

Re: [Fwd: Re: Collation problems or messed joins?]

[Gleb Paharenko]

Is it possible your data was changed during upgrade? How

have you performed it?







Andr$s Villanueva <[EMAIL PROTECTED]> wrote:

> Hi! thanks for your response.

> 

> This are the values of the variables you asked for:

> 

> collation_connection: utf8_general_ci

> collation_database: utf8_general_ci

> collation_server: utf8_general_ci

> character_set_client: utf8

> character_set_connection: utf8

> character_set_database: utf8

> character_set_results: NULL

> character_set_server: utf8

> character_set_system: utf8

> 

> These were taken from a query inside the app. From the query browser the 

> only difference is:

> character_set_results: utf8

> 

> Anyway, the same query has the same results in the app and the query 

> browser. The app is using the .net connector

> 

> Thanks

> Andr$s Villanueva

> 

> 

> 

> Gleb Paharenko wrote:

> 

>>Hello.

>>

>>

>>

>>Please, send the output of the following statements:

>>

>>

>>

>>  show variables like '%colla%';

>>

>>  show variables like '%char%';

>>

>>

>>

>>

>>

>>

>>

>>Andr$s Villanueva <[EMAIL PROTECTED]> wrote:

>>

>>  

>>

>>>Anyone??

>>>

>>>

>>

>>  

>>

>>

>>  

>>

>>> Original Message 

>>>

>>>

>>

>>  

>>

>>

>>  

>>

>>>The tables are now entirely in utf8, and that is also the instance's 

>>>

>>>

>>

>>  

>>

>>>default.

>>>

>>>

>>

>>  

>>

>>>I'm using the .net dll to connect and the connection string has charset 

>>>

>>>

>>

>>  

>>

>>>= utf8.

>>>

>>>

>>

>>  

>>

>>>I'm testing everything with the query browser, and i get the exact same 

>>>

>>>

>>

>>  

>>

>>>behaviour.

>>>

>>>

>>

>>  

>>

>>>Again, if anyone wants a script to recreate the tables with some data, I 

>>>

>>>

>>

>>  

>>

>>>can send it to you instantly.

>>>

>>>

>>

>>  

>>

>>

>>  

>>

>>>Thanks

>>>

>>>

>>

>>  

>>

>>>Andr$s Villanueva

>>>

>>>

>>

>>  

>>

>>

>>  

>>

>>>Kevin Cowley wrote:

>>>

>>>

>>

>>  

>>

>>

>>  

>>

Have you check that the collation for the text columns match the

  



>>

>>  

>>

collation for the table and that you've set UTF8 for the query  (set

  



>>

>>  

>>

char set utf8).

  



>>

>>  

>>

>>

>>  

>>

Kevin Cowley

  



>>

>>  

>>

Product Development

  



>>

>>  

>>

Alchemetrics Ltd

  



>>

>>  

>>

SMARTER DATA , FASTER

  



>>

>>  

>>

Tel: 0118 902 9000 (swithcboard)

  



>>

>>  

>>

Tel: 0118 902 9099 (direct)

  



>>

>>  

>>

Web: www.alchemetrics.co.uk

  



>>

>>  

>>

Email: [EMAIL PROTECTED]

  



>>

>>  

>>

>>

>>  

>>

 

  



>>

>>

>>

>>  

>>

> 

> 



-- 
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: ACCESS ODBC Interface whit 5.0.4

[Gleb Paharenko]

Hello.



Have you been here?



  http://bugs.mysql.com/bug.php?id=9211







>I have been using ACCESS to do simpe data editing on our MySQL tables

>for 3

>years.

> I recently installed 5.0.4 on my machine to evaluate it. I linked the

> tables

> into ACCESS through my old ODBC driver and  got 

>  ODBC-update on a linked table 'product_order_choice' failed

>  [Microsoft][ODBC Driver Manager] SQL data type out of rance (#0)

>   I then downloaded and installed the current ODBC connector

>   [3.51.11]

>   thinking maybe it was my old ODBC copy, but get the same result.

>Has anyone else seen this or have any ideas?

>

"Gordon" <[EMAIL PROTECTED]> wrote:



-- 
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: Slow queries, why?

[Gleb Paharenko]

Hello.



There could be a lot of reasons for such a delay. First, you

should switch to bulk inserts and perform all operation as a single

transaction. Avoid usage of the autoextended or per-table tablespaces.

Are you able to upgrade? There could be some performance improvements

in the newer versions.





[EMAIL PROTECTED] wrote:

> 

> Hi,

> 

>I have an interesting problem, i.e upto 20k data is inserted in 20

> min. But for 39k it took 3.5 hours. Could you please help me in this,

> what are all the possible scenarios which leads to this kind of

> problems.  Is there any fine tuning mechanism in Mysql 4.0.23 with

> innodb?

> 

> Please help me in this, it is very urgent.

> 

> Thanks,

> Narasimha

> 



-- 
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: Tables lost in new location of database

[Gleb Paharenko]

Hello.



mysql.exe is a client utility, I've meant the server process. Check that

you're running mysqld-max-nt.exe.





>I am not sure about max - but I am running mysql.exe (I think it is the

>same as mysqld-nt).

>

>No - when I do a select * from on a table in the moved database - I get

>the error that the table does not exist.

>

>well - how can i tell if i have disabled symlinks???

>

>I have not checked using 4.1.11.



Anoop kumar V <[EMAIL PROTECTED]> wrote:



-- 
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: Mysql unix socket and built-in defaults

[Gleb Paharenko]

Hello.



> I see default unix socket file is /tmp/mysql.sock

> which could be removed by someone accidently. 

> Is it normal to keep it that way or keep in a

> protected directory ?



See:

  http://dev.mysql.com/doc/mysql/en/problems-with-mysql-sock.html







"V. Agarwal" <[EMAIL PROTECTED]> wrote:

> Which cnf file (small,medium,large,huge) has params

> that are closer to mysql defaults for starting up

> server?

> 

> I see default unix socket file is /tmp/mysql.sock

> which could be removed by someone accidently. 

> Is it normal to keep it that way or keep in a

> protected directory ?

> Thanks.

> 

> __

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

Re: stability of mysql error strings

[Jigal van Hemert]

- Original Message -
From: "Scott Haneda"
> on 5/4/05 7:22 PM, Paul DuBois at [EMAIL PROTECTED] wrote:
> > Error strings do change format sometimes.
> > And they won't necessarily be in English. :-)
>
> I had a feeling, what is the general method most use to deal with the
above
> scenario?  I really don't want to test error strings every time I update
> mysql, I suppose the safe road is to hand select ahead of time?
Or use:
INSERT new record with username
retrieve record id (if it is an autoincrement field)
UPDATE record with email

You can now handle the 1062 errors of the insert and update seperately.

The scenario with select, insert requires transactions (other processes
could insert a matching record between your select and insert), which are
currently only supported by InnoDB (and BDB) tables.
http://dev.mysql.com/doc/mysql/en/ansi-diff-transactions.html

Another approach would be to do an insert with username and email and in
case of an error do one or two selects to check which field caused the
duplicate error (most of the time there won't be an error, so it would save
you some queries).

Regards, Jigal.


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

Re: Unique Index on multiple columns that can contain NULL in MySQL

[Martijn Tonies]

> From: "Dennis Fogg"
> > I'm getting lots of duplicate rows even though I have a
> > unique index defined over multiple columns.
> > The issue is that multiple NULL values are allowed,
> > even when some values are not null.
> > This could be as specified by the SQL standard,
> > but it's certainly confusing for the developer.
>
> It is confusing. You have to 'grok' NULL "values" to understand the
problems
> that may arise.
> In Boolean logic you're used to two distinct values TRUE and FALSE. The
> introduction of NULL actually introduced a second outcome of comparison
> operators and functions. Since NULL represents "unknown", comparing NULL
to
> NULL will result in MAYBE.
> If you look at NULL as being a yet unknown variable it starts to make
sense:
> (x = 2) : maybe true, maybe false, depending on the value of 'x'.
> (x != 2) : maybe true, maybe false, depending on the value of 'x'.
> In MySQL the outcome MAYBE is represented by NULL, so (NULL = NULL) ->
NULL;
> and (NULL != 2) -> NULL; (NULL * 1) -> NULL; etcetera.
>
> Regarding indexes, there doesn't seem to be any logic involved, other than
> "what the standard says". UNIQUE indexes may have multiple NULL values
> (excepting BDB tables). A PRIMARY key is defined as a combination of
UNIQUE
> and NOT NULL.

Since when does the standard handle Indices?

It's about time that MySQL gets Unique Constraints - these are defined by
the SQL standard :-)

With regards,

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


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

Re: The age old delete duplicates

[Scott Haneda]

on 5/5/05 2:52 AM, Jigal van Hemert at [EMAIL PROTECTED] wrote:

> - Original Message -
> From: "Scott Haneda"
>> on 5/5/05 2:11 AM, Joerg Bruehe at [EMAIL PROTECTED] wrote:
>> Basically, I have a chopping cart, this one is a little weird, for reasons
>> not worth explaining, you have a cart when a user is not logged in, and
> they
>> *may* have one they made at some point when logged in.  There is a chance
>> the logged in cart already has stuff in the non logged in cart, I don't
> want
>> duplicate items in the cart, so I want to just get rid of the oldest one.
> 
> I'd add a UNIQUE index on user id and product id (beware of the handling of
> NULL values!) and use REPLACE to add the new data.
> "REPLACE works exactly like INSERT, except that if an old record in the
> table has the same value as a new record for a PRIMARY KEY or a UNIQUE
> index, the old record is deleted before the new record is inserted."
> http://dev.mysql.com/doc/mysql/en/replace.html

Cant, at least not how this mess of a site has to work.
If a user is not logged in, there has to be the ability to add to a cart,
all I have is a session id from the cookie, they are not logged in.  so
these are inserts into a cart table.  Sure, I can prevent the duplicates for
that session, but there comes a point where they log in.  While the chances
are slim, when they login, there may be a existing cart already in place for
that user.  So I am not inserting anything at that point, but updating old
logged in cart items to the non logged in new ones.  Its just a setting of
the user_id in a update statement where session is either old or new.  This
collects the records to the users account, but, there is that rare case
where they may have ended up with 2 of the same product in the cart.
-- 
-
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: The age old delete duplicates

[Jigal van Hemert]

- Original Message -
From: "Scott Haneda"
> on 5/5/05 2:11 AM, Joerg Bruehe at [EMAIL PROTECTED] wrote:
> Basically, I have a chopping cart, this one is a little weird, for reasons
> not worth explaining, you have a cart when a user is not logged in, and
they
> *may* have one they made at some point when logged in.  There is a chance
> the logged in cart already has stuff in the non logged in cart, I don't
want
> duplicate items in the cart, so I want to just get rid of the oldest one.

I'd add a UNIQUE index on user id and product id (beware of the handling of
NULL values!) and use REPLACE to add the new data.
"REPLACE works exactly like INSERT, except that if an old record in the
table has the same value as a new record for a PRIMARY KEY or a UNIQUE
index, the old record is deleted before the new record is inserted."
http://dev.mysql.com/doc/mysql/en/replace.html

Regards, Jigal.


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

Re: Unique Index on multiple columns that can contain NULL in MySQL

[Jigal van Hemert]

From: "Dennis Fogg"
> I'm getting lots of duplicate rows even though I have a
> unique index defined over multiple columns.
> The issue is that multiple NULL values are allowed,
> even when some values are not null.
> This could be as specified by the SQL standard,
> but it's certainly confusing for the developer.

It is confusing. You have to 'grok' NULL "values" to understand the problems
that may arise.
In Boolean logic you're used to two distinct values TRUE and FALSE. The
introduction of NULL actually introduced a second outcome of comparison
operators and functions. Since NULL represents "unknown", comparing NULL to
NULL will result in MAYBE.
If you look at NULL as being a yet unknown variable it starts to make sense:
(x = 2) : maybe true, maybe false, depending on the value of 'x'.
(x != 2) : maybe true, maybe false, depending on the value of 'x'.
In MySQL the outcome MAYBE is represented by NULL, so (NULL = NULL) -> NULL;
and (NULL != 2) -> NULL; (NULL * 1) -> NULL; etcetera.

Regarding indexes, there doesn't seem to be any logic involved, other than
"what the standard says". UNIQUE indexes may have multiple NULL values
(excepting BDB tables). A PRIMARY key is defined as a combination of UNIQUE
and NOT NULL.

> Here's the test case:
>
> Goal: prevent duplicate rows on the (c1, c2) pair:
This depends on your definition of 'duplicate' in the light of the
NULL-logic I explained earlier.
If there is already a 1-1 pair in the db, inserting another 1-1 pair will
require the index to compare the entry to be inserted with the current
entries in the index. Comparing 1-1 to 1-1 will result in TRUE, so the new
entry is rejected.
If you try the same with a NULL-NULL pair in the db and you try to insert
another NULL-NULL pair, the comparison does not result in TRUE (it would be
MAYBE, thus NULL) and the entry is accepted.

> ++--+--+
> | pk | c1 | c2 |
> ++--+--+
> | 1 | 1 | NULL |
> | 2 | 1 | NULL |
> | 3 | 1 | |
> | 4 | NULL | NULL |
> | 5 | NULL | NULL |
> ++--+--+
> 5 rows in set (0.00 sec)
>
>
> Note: this works even with bdb engine in MySQL:
>
> mysql> alter table test_multi_column_null engine = bdb;
> Query OK, 5 rows affected (0.03 sec)
> Records: 5 Duplicates: 0 Warnings: 0

Does it? Try SHOW CREATE TABLE ; and check whether the engine is
really BDB??
If you do an ALTER TABLE  ENGINE=; the table
will be rebuilt anyway.

I tries your example and the engine type remained MyISAM...

> Conclusion: if you want to enforce uniqueness,
> don't use columns that allow NULL.

That entirely depends on your definition of 'uniqueness' as I explained
before. If you consider NULL to be equal to NULL (which it is not) then you
should only use NOT NULL columns.

Regards, Jigal.


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

Re: mysql_install_db for linux basedir query

[Mark Sargent]

Mark Sargent wrote:
Hi All,
if this script is in scripts by default, why does it have the 
following code,

test -z "$ldata" && ldata=./data
if test -z "$basedir"
then
 basedir=.
 bindir=./bin
 execdir=./bin
 pkgdatadir=./support-files
else
 bindir="$basedir/bin"
 if test -x "$basedir/libexec/mysqld"
 then
   execdir="$basedir/libexec"
 elif test -x "$basedir/sbin/mysqld"
 then
   execdir="$basedir/sbin"
 else
   execdir="$basedir/bin"
 fi
where basedir=. as that would instruct the script to look for all 
dir's from within the script dir, not the mysql dir. Can anyone 
explain why this is so..? Cheers.

Mark Sargent.
Hi All,
does nobody understand why this is so.? I keep having to move the script 
to /usr/local/mysql to get it to run. That is just, well, weird. Anyone 
else out there using the tar.gz install and if so, could you confirm 
that your scripts/mysql_install_db code has the same thing.? Cheers.

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

Re: How can I find this data?

[shaun thornburgh]

Hi Shawn,
Thanks for your reply. The query you suggested seems to return all practices 
that have bookings where the day type does not equal '1' - I changed your 
query so it searches on the day_type rather than work_type as this is what 
is required:

SELECT p.Practice_ID
   ,p.Practice_Name
   ,b.Booking_ID
   ,wt.Project_ID
   ,SUM(If(wt.Day_Type=1,1,0)) as WorkType_1
   ,count(1) as TotalWorkType
FROM Practices p
INNER JOIN Bookings b
   ON b.Practice_ID = p.Practice_ID
INNER JOIN Work_Types wt
   ON wt.Work_Type_ID = b.Work_Type_ID
GROUP BY 1,2,3,4
HAVING WorkType_1=0;
Let me give you an example. Your query returns this in the last row:
+-+--++++---+
| Practice_ID | Practice_Name| Booking_ID | 
Project_ID | WorkType_1 | TotalWorkType |
+-+--++++---+
|   11094 | Bugbrooke Surgery|   7114 |  
   12 |  0 | 1 |
+-+--++++---+

But if i select all bookings where the practice_id = 11094 i get another 
booking where the day_type = 1:

mysql> SELECT * FROM Bookings WHERE Practice_ID = 11094;
++--+-+++-++---+-+-+-+-++---+-+--++---+-+---+-+---+
| Booking_ID | Booking_Type | User_ID | Project_ID | Rep_ID | Practice_ID | 
Booking_Creator_ID | Booking_Creation_Date | Booking_Start_Date  | 
Booking_End_Date| Booking_Completion_Date | Booking_Mileage | 
Booking_Status | Unavailability_ID | Task_ID | Work_Type_ID | 
Additional_Notes 

 | Pre_Event_Copy_Received_By_Scheduling | 
Post_Event_Original_Completed_Form_Received | Section_C | Date_Difference | 
AU_Booking_ID |
++--+-+++-++---+-+-+-+-++---+-+--++---+-+---+-+---+
|   7112 | Booking  |  52 | 12 |638 |   11094 |  
 1174 | 2005-04-19 12:31:51   | 2005-05-04 09:30:00 | 
2005-05-04 18:00:00 | NULL|   0 | Incomplete 
|  NULL |   7 |   22 | x  | No  
  | No  
| No| n/a |  NULL |
|   7114 | Booking  |1601 | 12 |638 |   11094 |  
 1174 | 2005-04-19 12:57:53   | 2005-05-25 09:30:00 | 
2005-05-25 18:00:00 | NULL|   0 | Incomplete 
|  NULL |   7 |   24 | x  | No  
  | No  
| No| n/a |  NULL |
++--+-+++-++---+-+-+-+-++---+-+--++---+-+---+-+---+
2 rows in set (0.02 sec)

mysql> SELECT * FROM Work_Types WHERE Project_ID = 12;
+--++--+---+
| Work_Type_ID | Project_ID | Day_Type | Work_Type |
+--++--+---+
|   22 | 12 |1 | Day 1 |
|   24 | 12 |2 | Day 2 |
+--++--+---+
2 rows in set (0.00 sec)
mysql>
Do you see my problem here?! Any advice would be greatly appreciated...
Shaun

From: [EMAIL PROTECTED]
To: "shaun thornburgh" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: How can I find this data?
Date: Wed, 4 May 2005 09:42:47 -0400
"shaun thornburgh" <[EMAIL PROTECTED]> wrote on 05/04/2005
06:54:23 AM:
> Hi,
>
> I have four tables among others in my database: Bookings, Work_Types,
> Practices & Projects. Book

Re: The age old delete duplicates

[Scott Haneda]

on 5/5/05 2:11 AM, Joerg Bruehe at [EMAIL PROTECTED] wrote:

> Hi Scott, all!
> 
> Scott Haneda wrote:
>> I have been researching on how to deal with duplicate data.  [[...]]
>> 
>> I am not in a situation where I can test for duplicates on insert, so the on
>> duplicate key update deal does me no good here.
>> 
>> Most data points to selecting distinct records to a temp table, deleting the
>> source records, and selecting back into the original, three steps.  I
>> imagine there is a bit of overhead to the create temp table, it afterall, is
>> writing a small file to disk.
>> 
>> saying I want to favor newer records over old, and I have a pk of id, is
>> there any flaw in:
>> SELECT distinct(id) from table where my_param = 'foo' order by id;
>> that gives me the newest unique record id's
>> // little logic to convert result set inot a mysql list
>> DELETE from table where my_param = 'foo' and id NOT IN (the_result)
> 
> 1) As 'id' is a PK in your example, all its values will be distinct
> anyway, so this approach cannot work.

My mistake, sorry, was rushing.
 
> 2) Your mentioning of "favor newer records over old" and relating that
> with 'id' (your PK) is not justified in general, because there is no
> necessity that the 'id' value is formed in ascending sequence - unless
> you use auto-increment, timestamp, or something with a similar effect.

The id is a auto_inc indeed.

> Assuming that "my_param" is the column that must not contain duplicates,
> what you want to do is something like
> 
> SELECT  COUNT(id), my_param FROM table HAVING COUNT(id)> 1

If you can bear with me, let me re-explain a little better:

id auto inc unique key
user_id contains the users id
product_id, this is where there can and will be dupes, I want the newest
saved

So I select distinct(product_id) from table where user_id = '123' order by
id
delete from table where user_id = '123' AND product_id not in (above result
list)

Ok?

> to find those values which should be unique but have duplicates.
> (Note that this does not include a condition on "my_param", assuming the
> duplicates might occur for any value. You might be in a situation where
> you can add a restriction on "my_param").
> 
> 
> If "id" values are really increasing, so you want to keep the maximum,
> you should follow the "groupwise maximum" approach described in the
> manual, it will yield those "id" values you want to keep.

I will need to lookup groupwise, never used that before.

> As an alternative, search for the "groupwise minimum" of all groups with
> a "count" greater than one, these would be values to delete.
> 
>> 
>> So I have two steps, I save the temp table creation, AND I don't bother
>> doing anything at all in the case where there are only uniqe records to
>> begin with, ie: nothing to delete.
>> 
>> Is there something wrong with this, is seems a wee bit more efficient that
>> the temp table deal for my case?
> 
> The way I understood your situation, this is going to be a recurrent
> problem, arising again and again. In that case, I would see to do
> without a table copy approach: Your data are likely to grow, and the
> older data will hopefully be "clean", so you will be shuffling larger
> and larger tables for an (about) constant number of erroneous records.

Basically, I have a chopping cart, this one is a little weird, for reasons
not worth explaining, you have a cart when a user is not logged in, and they
*may* have one they made at some point when logged in.  There is a chance
the logged in cart already has stuff in the non logged in cart, I don't want
duplicate items in the cart, so I want to just get rid of the oldest one.

> I would see to do it "in-place", because then the effort will not grow
> linear with the table size. Sure, the time to find duplicate rows may
> rise as the table grows, but the time to fix should remain about constant.

Thanks for any additional pointers.  I can select all and select distinct,
compare the two in a repeat loop in my code, looking for a nice all in mysql
way to pull it off.
-- 
-
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: The age old delete duplicates

[Joerg Bruehe]

Hi Scott, all!
Scott Haneda wrote:
I have been researching on how to deal with duplicate data.  [[...]]
I am not in a situation where I can test for duplicates on insert, so the on
duplicate key update deal does me no good here.
Most data points to selecting distinct records to a temp table, deleting the
source records, and selecting back into the original, three steps.  I
imagine there is a bit of overhead to the create temp table, it afterall, is
writing a small file to disk.
saying I want to favor newer records over old, and I have a pk of id, is
there any flaw in:
SELECT distinct(id) from table where my_param = 'foo' order by id;
that gives me the newest unique record id's
// little logic to convert result set inot a mysql list
DELETE from table where my_param = 'foo' and id NOT IN (the_result)
1) As 'id' is a PK in your example, all its values will be distinct 
anyway, so this approach cannot work.

2) Your mentioning of "favor newer records over old" and relating that 
with 'id' (your PK) is not justified in general, because there is no 
necessity that the 'id' value is formed in ascending sequence - unless 
you use auto-increment, timestamp, or something with a similar effect.

Assuming that "my_param" is the column that must not contain duplicates, 
what you want to do is something like

   SELECT  COUNT(id), my_param FROM table HAVING COUNT(id)> 1
to find those values which should be unique but have duplicates.
(Note that this does not include a condition on "my_param", assuming the 
duplicates might occur for any value. You might be in a situation where 
you can add a restriction on "my_param").

If "id" values are really increasing, so you want to keep the maximum, 
you should follow the "groupwise maximum" approach described in the 
manual, it will yield those "id" values you want to keep.

As an alternative, search for the "groupwise minimum" of all groups with 
a "count" greater than one, these would be values to delete.

So I have two steps, I save the temp table creation, AND I don't bother
doing anything at all in the case where there are only uniqe records to
begin with, ie: nothing to delete.
Is there something wrong with this, is seems a wee bit more efficient that
the temp table deal for my case?
The way I understood your situation, this is going to be a recurrent 
problem, arising again and again. In that case, I would see to do 
without a table copy approach: Your data are likely to grow, and the 
older data will hopefully be "clean", so you will be shuffling larger 
and larger tables for an (about) constant number of erroneous records.

I would see to do it "in-place", because then the effort will not grow 
linear with the table size. Sure, the time to find duplicate rows may 
rise as the table grows, but the time to fix should remain about constant.

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

The age old delete duplicates

[Scott Haneda]

I have been researching on how to deal with duplicate data.  While I have a
case where there can be duplicate data, and I want to get rid of it, the
general ideas I seem to find are as follows, plus my own, which I would like
opinions on since I have not seen it mentioned.

I am not in a situation where I can test for duplicates on insert, so the on
duplicate key update deal does me no good here.

Most data points to selecting distinct records to a temp table, deleting the
source records, and selecting back into the original, three steps.  I
imagine there is a bit of overhead to the create temp table, it afterall, is
writing a small file to disk.

saying I want to favor newer records over old, and I have a pk of id, is
there any flaw in:
SELECT distinct(id) from table where my_param = 'foo' order by id;
that gives me the newest unique record id's
// little logic to convert result set inot a mysql list
DELETE from table where my_param = 'foo' and id NOT IN (the_result)

So I have two steps, I save the temp table creation, AND I don't bother
doing anything at all in the case where there are only uniqe records to
begin with, ie: nothing to delete.

Is there something wrong with this, is seems a wee bit more efficient that
the temp table deal for my case?

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