Re: Images

[Jigal van Hemert]

From: "GH" <[EMAIL PROTECTED]>

> I was wondering how to get images into and out of a Mysql database
>  was told it was possible.
>
> I have  mysql 4.0.18

The general opinion is that files should be stored in a file system and not
a database. There are circumstances that you might want to store binary data
in a database.
Take a look at the BLOB column types
(http://dev.mysql.com/doc/mysql/en/BLOB.html).

Regards, Jigal.


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

Re: Leasing time on a superfast mysql box

[valentin_nils]

Hi Mark,
(B
(BI have the parts for 4 Opteron systems here (Dual CPU machines)
(B
(BAll Types 844 to 848 luying right in front of me.
(B2x Sata HDD
(B2x SCSI HDD
(B6-12GB MEMORY
(B
(BI also have
(B
(BFedora Core 1
(BSuse Linux 9.0
(BTurbo Linux 8
(BMandrake 10 rc1
(BWindows 64 BETA
(B
(BWhat I am currently figuring out is how to get the SiL 3114 driver
(Binstalled. I already spend a week (without success).
(Bhttp://www.be-known-online.com/modules/newbb/viewforum.php?forum=46
(Bhttp://www.be-known-online.com/modules/newbb/viewtopic.php?topic_id=214&forum=46
(B
(BI give myself max another week before its up and running (I have to do it
(Bbesides my real job).
(B
(BI would be using a dynamic dns connection unless you will afford a static
(BIP (30$ /month).
(B
(BFollowing the Opteron discussions closely I would recommend Suse Linux.
(B
(BIf this could be of interest than please contact me privately and let me
(Bknow your requirements. The servers will be based in Tokyo/Japan.
(B
(BBest regards
(B
(BNils Valentin
(BTokyo / Japan
(B
(Bhttp://www.be-known-online.com/mysql
(B
(B
(B
(B
(B
(B
(B> I have a large database of zip codes with longitude and latitude of
(B> each, and I periodically generate a lookup table for each zip showing
(B> all zip codes within various radii. The process takes a day on my poor
(B> workstations 2.5GHz CPU, but I need to do it faster. Does anyone know of
(B> somewhere I can borrow or lease some time on a very fast mysql server to
(B> do this? All I need is mysql and perl on the machine. All processing
(B> including the trig is done by mysql.
(B>
(B> Thanks,
(B>
(B> Mark
(B>
(B>
(B> --
(B> MySQL General Mailing List
(B> For list archives: http://lists.mysql.com/mysql
(B> To unsubscribe:
(B> http://lists.mysql.com/[EMAIL PROTECTED]
(B>
(B>
(B
(B
(B-- 
(BMySQL General Mailing List
(BFor list archives: http://lists.mysql.com/mysql
(BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update a record but not the timestamp

[Michael Stassen]

The simple solution is to explicitly set the timestamp column to the value 
it already has.  Assuming Time is your timestamp column, you need:

  UPDATE `table`
  SET `AID` = 2, Time = Time
  WHERE `SID` = 5;
See the manual for more 
.

Michael
/dev/null wrote:
We've got about 1,000 records in a table that have timestamps on them.
We've ran into a situation where we need to go back and update one field in
all of those records without altering the timestamp.  since the timestamp is
automatically changed when the record is updated we are in a bind.
The sql we need to run is "UPDATE `table` SET `AID` = 2 WHERE `SID` = 5"
Is there a way to do this sort of update but tell mysql to leave the
timestamps alone?
Maybe by copying the table to backup and then back again (only I can't
figure out the sql to do that)?
Thanks for any and all help.

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

Re: Problem with replication

[valentin_nils]

Hi Dana,
(B
(Bthe tool might be free for developers, but is it also Open source ??
(BDoes it solve the issues described by the threat owner ?
(BIf not, can the owner easily adapt it to solve his problems with it ?
(B
(BI am just adding this question, to make it not look like your are
(Bmissusing the mailing list to just advertise your product and we get some
(Breal usable info here.
(B
(BBest regards
(B
(BNils Valentin
(BTokyo /Japan
(B
(B
(B
(B> On Mon, Sep 27, 2004 at 10:43:53AM -0400, Dana Henderson wrote:
(B>> My name is Dana Henderson.  I am from IT-Map.  A  My SQL Migration
(B>> Technology Partner.
(B>>
(B>> We have announced in this months MySQL Newsletter(Partners Corner)  That
(B>> you
(B>> can get a fantastic, mature MySQL database toolkit for your company for
(B>> free.
(B>>
(B>> http://www.it-map.com/html/mydbpal_.html
(B>>
(B>>
(B>> dbPAL can be downloaded at www.it-map.com.  This will help for
(B>> migration,
(B>> conversion, replication, database documentation, etl and change
(B>> management.
(B>>
(B>> Go ahead and try it out then let me know what you think.
(B>
(B> Does it solve this problem?
(B>
(B> Jeremy
(B> --
(B> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
(B> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
(B>
(B> [book] High Performance MySQL -- http://highperformancemysql.com/
(B>
(B> --
(B> MySQL General Mailing List
(B> For list archives: http://lists.mysql.com/mysql
(B> To unsubscribe:
(B> http://lists.mysql.com/[EMAIL PROTECTED]
(B>
(B>
(B
(B
(B-- 
(BMySQL General Mailing List
(BFor list archives: http://lists.mysql.com/mysql
(BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Leasing time on a superfast mysql box

[Mark Maunder]

I have a large database of zip codes with longitude and latitude of
each, and I periodically generate a lookup table for each zip showing
all zip codes within various radii. The process takes a day on my poor
workstations 2.5GHz CPU, but I need to do it faster. Does anyone know of
somewhere I can borrow or lease some time on a very fast mysql server to
do this? All I need is mysql and perl on the machine. All processing
including the trig is done by mysql. 

Thanks,

Mark


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

test message

[Bhaskar Borthakur]

Am a new member of the list.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update a record but not the timestamp

[/dev/null]

Never mind, I just figured it out:

First, I copied the whole 'hits' table to 'hits_bak', then ran this:

INSERT  INTO `csrtech_dirserv`.`hits`
SELECT  `SID` ,  2 AS  `AID` ,  `Phrase` ,  `IP` ,  `Link` ,  `Time` ,
`Amt` ,  `F`
FROM  `csrtech_dirserv`.`hits_bak`
WHERE  `SID`  =5

So that coppied all fields except AID, which it set to 2.

Hope this helps someone else!


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

update a record but not the timestamp

[/dev/null]

We've got about 1,000 records in a table that have timestamps on them.
We've ran into a situation where we need to go back and update one field in
all of those records without altering the timestamp.  since the timestamp is
automatically changed when the record is updated we are in a bind.

The sql we need to run is "UPDATE `table` SET `AID` = 2 WHERE `SID` = 5"

Is there a way to do this sort of update but tell mysql to leave the
timestamps alone?

Maybe by copying the table to backup and then back again (only I can't
figure out the sql to do that)?

Thanks for any and all help.


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

Re: SELECT queries on replicate DB server

[Jim Grill]

>
> My question is:
> Why would they deem it necessary to use yet a third server?   Could it
> be because the main server and the main slave are constantly being
> updated and they wouldn't want to overload the main slave(which
> is not on as high a horsepower of a box I know for a fact).  Could it
> be because maybe the subset of tables that they put on the third server
> are relatively more stable and hence there arent so many writethroughs
> so it can handle the complex selects better.
>
> All theories gladly accepted...
>
I'm not too sure about the third server either, but I do have an idea. It
wouldn't make much sense if the third server had different data on it. That
would tend to make things difficult to keep up to date - or maybe not. It
might be a slave that they only connect to the master every so often.

It's very common to have applications that write to one server and read from
a slave server. Sometimes many slave servers since there are typically way
more reads than writes.

Perhaps they use the third server so that if the master or slave servers die
there will always be a spare server for reads.

As far as any difference in the tables on the third server... Since it is
doing selects only you can start a slave server with a few options to speed
things up like:  --skip-innodb, --skip-bdb, --low-priority-updates,
and --delay-key-write=ALL which will force the server to use
non-transactional MyIsam tables for better performance.

It's really tough to speculate. Every system administrator would probably do
it a different way.

Jim Grill



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

SELECT queries on replicate DB server

[Gerald Taylor]

 We have a main db server that has a pretty big db and it sometimes gets
overloaded.  We also have a replicated slave server... every update to 
the main server writes through to it and it is the one that is backed up 
to avoid interruption service for the website.

There are certain sections of the website where all the queries are 
select only. So what was done was to replicate the affected tables on 
yet a third server.  Now we're set up so that when that block of code is 
invoked, it connects the third db server to take the load off the main 
one, knowing ahead of time that there won't be any updates to it,
everyone is happy tra la la la life goes on.

My question is:
Why would they deem it necessary to use yet a third server?   Could it
be because the main server and the main slave are constantly being 
updated and they wouldn't want to overload the main slave(which
is not on as high a horsepower of a box I know for a fact).  Could it
be because maybe the subset of tables that they put on the third server
are relatively more stable and hence there arent so many writethroughs
so it can handle the complex selects better.

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

Re: Windows VS Linux Platform

[Daniel Kasak]

Patrick Connolly wrote:
|> Last time I checked ( which was a while ago ) the only difference I 
|> found was that table & field names weren't case sensitive under Windows 

I think you mean ...table and database names
 

Yeah that too.
Like I said, it was a while ago.
--
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: Problems with phpMyAdmin, GD library, and jpegs

[Eric Bergen]

If you are storing a complete image in MySQL you don't need gd at all.
Just output the correct header and the image file.  GD is for creating
images on the fly.  Also if you can store the image in a file on disk
and the path to the image in MySQL.

-- 
Eric Bergen
[EMAIL PROTECTED]


On Mon, 27 Sep 2004 15:06:46 -0700, Keith Brownmiller
<[EMAIL PROTECTED]> wrote:
> Greetings,
> 
> Am having some problems with phpMyAdmin, GD library and jpegs.
> 
> Here is the short version of the story.
> 
> I installed phpMyAdmin,  modified a database to install a graphic image into one of 
> the tables and it showed the graphic from within phpMyAdmin - no problem.
> I then wrote a very simple php route to connect to the database and display the 
> record. - that failed with mention of a problem relating to GD library.
> 
> I then went to the O'Reilly "Programming PHP" book and discovered that I needed the 
> GD library.   It stated that I should be able to do a phpinfo() and find the gd 
> section of the report to find out it it was using the GD library.
> 
> I got that installed and functioning using a simple example from the O'Reilly book 
> and am able to get the black square on the screen.
> 
> Now if I go to phpMyAdmin, I am not able to see the graphic in the database like 
> before nor can I display the graphic from some basic php code.
> 
> Am really stumped on this one.  Can provide more detail to anybody that can assist 
> in getting this problem solved.
> 
> Thanks
> 
> Keith
> Sys Admin
> Collins Computer Innovations
> 
>

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

Re: Images

[Eric Bergen]

It is possible but not very logical. MySQL isn't a filesystem. Unless
you have an extreme situation you should store the path in MySQL and
the image itself in a file.

-- 
Eric Bergen
[EMAIL PROTECTED]

On Mon, 27 Sep 2004 18:20:02 -0400, GH <[EMAIL PROTECTED]> wrote:
> I was wondering how to get images into and out of a Mysql database
>  was told it was possible.
> 
> I have  mysql 4.0.18
> 
> --
> 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]

Images

[GH]

I was wondering how to get images into and out of a Mysql database
 was told it was possible.

I have  mysql 4.0.18

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

Problems with phpMyAdmin, GD library, and jpegs

[Keith Brownmiller]

Greetings,

Am having some problems with phpMyAdmin, GD library and jpegs.

Here is the short version of the story.

I installed phpMyAdmin,  modified a database to install a graphic image into one of 
the tables and it showed the graphic from within phpMyAdmin - no problem.
I then wrote a very simple php route to connect to the database and display the 
record. - that failed with mention of a problem relating to GD library.

I then went to the O'Reilly "Programming PHP" book and discovered that I needed the GD 
library.   It stated that I should be able to do a phpinfo() and find the gd section 
of the report to find out it it was using the GD library.

I got that installed and functioning using a simple example from the O'Reilly book and 
am able to get the black square on the screen.

Now if I go to phpMyAdmin, I am not able to see the graphic in the database like 
before nor can I display the graphic from some basic php code.

Am really stumped on this one.  Can provide more detail to anybody that can assist in 
getting this problem solved.

Thanks

Keith
Sys Admin
Collins Computer Innovations

Re: Newbie question about blob data types ie. Storing .xls in blob

[DreamWerx]

Good binary storage article w/ sample code:

http://php.dreamwerx.net/forums/viewtopic.php?t=6


On Mon, 27 Sep 2004 16:47:37 -0500, Jack Taffar <[EMAIL PROTECTED]> wrote:
> 
> Is it possible to store an excel file into a blob field in mysql?  If so how
> do I go about importing the file into the blob field, and get it back out
> into an .xls file?
> 
> Thanks in advnace
> 
> Jack Taffar
> AOG
> 
> --
> 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]

Newbie question about blob data types ie. Storing .xls in blob

[Jack Taffar]

Is it possible to store an excel file into a blob field in mysql?  If so how
do I go about importing the file into the blob field, and get it back out
into an .xls file?


Thanks in advnace

Jack Taffar
AOG





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

Re: server timezone and system timezon

[Eric Bergen]

Have the location selecting adjust the timezone when it selects. Example
select t + interval 6 hour from t

-- 
Eric Bergen
[EMAIL PROTECTED]


On Mon, 27 Sep 2004 12:24:36 -0600, Elim Qiu <[EMAIL PROTECTED]> wrote:
> Hi, I'm looking for help on timezone issues.
> 
> I'm using mysql for a web application about time related events.
> I've two servers in different locations with different timezones.
> 
> How to setup the database server so that the datetime value will not
> depend on the server machine's timezone?  In other words, how to
> make the datetime value of the same record fetched from both mysql
> servers unaffected by locations?
> 
> Thanks for any helps.
>

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

Re: Date Range

[Dirk Bremer \(NISC\)]

- Original Message - 
From: <[EMAIL PROTECTED]>
To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, September 27, 2004 14:30
Subject: Re: Date Range


> I think you are very close but you are missing the LAST day of each month.
> I think you need to change your WHERE to read
>
> WHERE queue_time >= @Start AND queue_time < @BegOfMonth
>
> Reasoning: If @BegOfMonth is '2004-09-01' then @End will be '2004-08-31'.
> If queue_time is a datetime value somewhen during the day of 8/31 (like
> '2004-08-31 13:15:46') then you miss it with "< @End". Because of the time
> component, you still miss that event if you change the comparator to "<=".
> You need to compare to < the next date (@BegOfMonth).
>
> '2004-08-31 13:15:46' < '2004-08-31'  is false
> '2004-08-31 13:15:46' <= '2004-08-31'  is false
> '2004-08-31 13:15:46' < '2004-09-01'  is true
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine

Shawn,

You are correct. It also eliminates any purpose for the @End variable, which
is fine.
I just tested it and the results were correct. Thanks a lot. It's still
Monday!

Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471

[EMAIL PROTECTED]
www.nisc.cc


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

Re: Conexiones no autorizadas

[Jim Grill]

> 
> Hola ,
> 
> Estoy empezando a notar intentos de ingreso con usuarios ilegales
> no existentes) a mi server.
> 
> Mi pregunta es : Como lo rastreo teniendo como unico dato el numero de
> ip desde el cual se esta conectando?
> 
> O si alguien tiene una mejor idea o ha pasado por lo mismo,
> le agradecere me comente sus experiencias
> 
> Desde ya muchas gracias
> Mauricio
> 
> 
Hola Mauricio,

MySQL List Espanol:
http://lists.mysql.com/mysql-es

Hadios Amigo :-)



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

Re: Addslashes / data sort

[Jim Grill]

> PHP / MySQL
>
>
>
> I'm using PHP to retrieve user input and store it in MySQL.  PHP's
> addslashes function is used on data going into the database and PHP's
> stripslashes function is being used on data coming from the database.
This
> is allowing me to store and retrieve data with no problems, but it's
causing
> problems when trying to sort data.  In particular, data with double or
> single quotes is getting escaped, which changes it's position in the sort.
>
>
Sounds like you are escaping twice. If you have magic_quotes_gpc enabled
then your data is already being escaped when it's submitted. Most likely
this is your problem.

magic_quotes_gpc is a bad hack and is actually disabled in newer releases.
However, if you're a noob, you might want to leave it on. :-) The problem is
that when magic_quotes_gpc adds slashes to escape quotes and then you
explicitly escape them again with add_slashes() you end up with all quotes
escaped twice.

Now your asking: "why then is there only one slash in the database?"

Simple:

try this:

INSERT INTO sometable VALUES('Ed\'s','\'Pizza\'');

and then...

SELECT * FROM sometable;

+--+--+
| name | what |
+--+--+
| Ed's | 'Pizza'  |
+--+--+

The slashes never actually get inserted into the table so there is no need
to call strip_slashes() on the results either. :-)

Jim Grill



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

Re: Date Range

[SGreen]

I think you are very close but you are missing the LAST day of each month. 
I think you need to change your WHERE to read

WHERE queue_time >= @Start AND queue_time < @BegOfMonth

Reasoning: If @BegOfMonth is '2004-09-01' then @End will be '2004-08-31'. 
If queue_time is a datetime value somewhen during the day of 8/31 (like 
'2004-08-31 13:15:46') then you miss it with "< @End". Because of the time 
component, you still miss that event if you change the comparator to "<=". 
You need to compare to < the next date (@BegOfMonth).

'2004-08-31 13:15:46' < '2004-08-31'  is false
'2004-08-31 13:15:46' <= '2004-08-31'  is false
'2004-08-31 13:15:46' < '2004-09-01'  is true

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"Dirk Bremer \(NISC\)" <[EMAIL PROTECTED]> wrote on 09/27/2004 03:18:46 
PM:

> - Original Message - 
> From: <[EMAIL PROTECTED]>
> To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Monday, September 27, 2004 11:40
> Subject: Re: Date Range
> 
> 
> > To compute the date range for "two months ago". (if the current month 
is
> > September, this will return July's data)
> >
> > set @dtBegOfMonth = DATE_SUB(CURDATE(), INTERVAL 
DAYOFMONTH(CURDATE())-1
> > DAY)
> > set @dtEnd = DATE_SUB(@dtBegOfMonth, INTERVAL 1 month)
> > set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 month)
> >
> > Then, continue with the same query as above. These should be really 
fast
> > as you are comparing constant values against what should be indexed 
table
> > data. You also avoid string conversions, substring comparisons, and 
other
> > data type conversions this way. The Date information stays date
> > information
> 
> The following produced the result set that I was interested in.
> 
> set @BegOfMonth = DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 
DAY);
> set @Start = DATE_SUB(@BegOfMonth, INTERVAL 1 month);
> set @End = DATE_SUB(@BegOfMonth, INTERVAL 1 day);
> 
> SELECT ident,
>job_coop,
>cycle,
>lpad(status,10,' ') as status,
>type,
>file_size,
>date_format(queue_time,'%Y-%m-%d %T') as queue_time,
>file_time,
>transfer_start,
>transfer_end
> FROM queue
> WHERE queue_time >= @Start AND queue_time < @End
> ORDER BY ident;
> 
> Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
> USA Central Time Zone
> 636-922-9158 ext. 8652 fax 636-447-4471
> 
> [EMAIL PROTECTED]
> www.nisc.cc
> 

Re: Conexiones no autorizadas

[Mauricio Pellegrini]

Very sorry!!!

I've just mistaken the email address..

This message was intended to be sent to a Spanish SuSE List.

I Apologize.
Mauricio



On Mon, 2004-09-27 at 15:55, Mauricio Pellegrini wrote:
> Hola ,
> 
> Estoy empezando a notar intentos de ingreso con usuarios ilegales
> no existentes) a mi server.
> 
> Mi pregunta es : Como lo rastreo teniendo como unico dato el numero de
> ip desde el cual se esta conectando?
> 
> O si alguien tiene una mejor idea o ha pasado por lo mismo,
> le agradecere me comente sus experiencias
> 
> Desde ya muchas gracias
> Mauricio
> 
> 
> 


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

Re: How to pick the oldest row

[Jim Grill]

> Hi , I'm trying to fetch the oldest row from a result 
> set in wich the resulting rows have duplicates,
> 
> I need only one row for each duplicate.
> 
> The problem is that I need always the oldest row
> Like in the example
> 
> SELECT *
> FROM `mj_tmp` 
> WHERE idk NOT IN 
> (SELECT idk FROM `mj_tmp` 
>  GROUP BY Concat(ida,u) 
>  HAVING Count( Concat(ida,u) ) >1 )
> 
> I would like to get the first of this two rows
> but I'm always getting the second.
> 
> idk ida u d
> 90 14450 13 2004-09-08
> 147 14450 13 2004-09-09
> 
> 
> Is there a way in which I can make sure I get always the oldest row?
> 
Try adding something like ORDER BY d DESC LIMIT 1

Jim Grill




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

Re: Date Range

[Dirk Bremer \(NISC\)]

- Original Message - 
From: <[EMAIL PROTECTED]>
To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, September 27, 2004 11:40
Subject: Re: Date Range


> To compute the date range for "two months ago". (if the current month is
> September, this will return July's data)
>
> set @dtBegOfMonth = DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1
> DAY)
> set @dtEnd = DATE_SUB(@dtBegOfMonth, INTERVAL 1 month)
> set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 month)
>
> Then, continue with the same query as above. These should be really fast
> as you are comparing constant values against what should be indexed table
> data. You also avoid string conversions, substring comparisons, and other
> data type conversions this way. The Date information stays date
> information

The following produced the result set that I was interested in.

set @BegOfMonth = DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY);
set @Start = DATE_SUB(@BegOfMonth, INTERVAL 1 month);
set @End = DATE_SUB(@BegOfMonth, INTERVAL 1 day);

SELECT ident,
   job_coop,
   cycle,
   lpad(status,10,' ') as status,
   type,
   file_size,
   date_format(queue_time,'%Y-%m-%d %T') as queue_time,
   file_time,
   transfer_start,
   transfer_end
FROM queue
WHERE queue_time >= @Start AND queue_time < @End
ORDER BY ident;

Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471

[EMAIL PROTECTED]
www.nisc.cc


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

Newbie - Server won't start after update

[David Lowe]

Greetings!
	I had mysql 4.0.18 installed from the binary package, was working my 
way through the tutorial [totally new to database work].  Then i 
installed the 4.0.20 package [again from your binary], i can't start 
the server.  Here are the results:

PowerBook:/usr/local/mysql portable$ sudo ./bin/mysqld_safe
Starting mysqld daemon with databases from /usr/local/mysql/data
040922 10:23:22  mysqld ended
	I read on this list about permissions issues, so issued the following 
command:

PowerBook:/usr/local/mysql portable$ chown -R mysql /usr/local/mysql
Now the output has changed subtly:
PowerBook:/usr/local/mysql portable$ sudo ./bin/mysqld_safe
Starting mysqld daemon with databases from 
/usr/local/mysql-standard-4.0.20-apple-darwin7.3.0-powerpc/data
040927 11:17:00  mysqld ended

	I verified that i have a group "mysql" and user "mysql", but am not 
sure about much beyond that.  What now?

12" PowerBook G4 1 GHz [768 mem] OS X.3.5, Apple X11 1.0, Xcode 1.1, 
Fink 0.7.0

Real life is known to cause insanity in those most closely associated
with it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

server timezone and system timezon

[Elim Qiu]

Hi, I'm looking for help on timezone issues.

I'm using mysql for a web application about time related events.
I've two servers in different locations with different timezones.

How to setup the database server so that the datetime value will not
depend on the server machine's timezone?  In other words, how to 
make the datetime value of the same record fetched from both mysql 
servers unaffected by locations?

Thanks for any helps.

Re: Date Range

[Paul DuBois]

At 14:50 -0400 9/27/04, [EMAIL PROTECTED] wrote:
Paul DuBois <[EMAIL PROTECTED]> wrote on 09/27/2004 02:07:54 PM:
 At 12:57 -0500 9/27/04, Dirk Bremer (NISC) wrote:
 >- Original Message -
 >From: <[EMAIL PROTECTED]>
 >To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
 >Cc: <[EMAIL PROTECTED]>
 >Sent: Monday, September 27, 2004 11:40
 >Subject: Re: Date Range
 >
 >
 >>  I see that you are on 4.0.18 so you can't use many of the new date
 >>  functions (4.1.1+) but has to be an easier way. Let's try this for a
 >>  "yesterday" query
 >>
 >>  set @dtEnd = CURDATE()
 >>  set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 DAY)
 >>
 >>  SELECT ident,
 >> job_coop,
 >> cycle,
 >> lpad(status,10,' ') as status,
 >> type,
 >> file_size,
 >> date_format(queue_time,'%Y-%m-%d %T') as queue_time,
 >> file_time,
 >> transfer_start,
 >> transfer_end
 >>  FROM queue
 >>  WHERE queue_time >= @dtStart
 >>  AND queue_time < @dtEnd
 >>
 >>
 >>  To compute the date range for "two months ago". (if the current
month is
 >>  September, this will return July's data)
 >>
 >>  set @dtBegOfMonth = DATE_SUB(CURDATE(), INTERVAL
DAYOFMONTH(CURDATE())-1
 >>  DAY)
 >>  set @dtEnd = DATE_SUB(@dtBegOfMonth, INTERVAL 1 month)
 >>  set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 month)
 >>
 >>  Then, continue with the same query as above. These should be really
fast
 >>  as you are comparing constant values against what should be indexed
table
 >>  data. You also avoid string conversions, substring comparisons, and
other
 >>  data type conversions this way. The Date information stays date
 >>  information
 >>
 >
 >Shawn,
 >
 >Thanks for the advice. Unfortunately, your example produces the
following
 >error. Looking at it, I can see nothing obvious that is causing the
error.
 >
 >ERROR 1064: You have an error in your SQL syntax.  Check the manual
that
 >corresponds to your MySQL server version for the right syntax to use
near
 >'set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 DAY)
 You need semicolons at the end of each statement.
 --
 Paul DuBois, MySQL Documentation Team
 Madison, Wisconsin, USA
 MySQL AB, www.mysql.com
Whether he needs semicolons depends on which tool he is using to execute
his statements. If he is using the MySQL client or any of several other
tools, I agree. If he is going through an ODBC connection, he doesn't need
them. (At least my 3.52.x drivers can't accept more than one statement per
request.)
Shawn,
Okay.  I'll amend my statement to:  You either need semicolons at the end
of each statement, or you need to issue each statement separately if you're
using a client that doesn't understand statement terminators.  :-)
--
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: Date Range

[Paul DuBois]

At 12:57 -0500 9/27/04, Dirk Bremer (NISC) wrote:
- Original Message -
From: <[EMAIL PROTECTED]>
To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, September 27, 2004 11:40
Subject: Re: Date Range

 I see that you are on 4.0.18 so you can't use many of the new date
 functions (4.1.1+) but has to be an easier way. Let's try this for a
 "yesterday" query
 set @dtEnd = CURDATE()
 set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 DAY)
 SELECT ident,
job_coop,
cycle,
lpad(status,10,' ') as status,
type,
file_size,
date_format(queue_time,'%Y-%m-%d %T') as queue_time,
file_time,
transfer_start,
transfer_end
 FROM queue
 WHERE queue_time >= @dtStart
 AND queue_time < @dtEnd
 To compute the date range for "two months ago". (if the current month is
 September, this will return July's data)
 set @dtBegOfMonth = DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1
 DAY)
 set @dtEnd = DATE_SUB(@dtBegOfMonth, INTERVAL 1 month)
 set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 month)
 Then, continue with the same query as above. These should be really fast
 as you are comparing constant values against what should be indexed table
 data. You also avoid string conversions, substring comparisons, and other
 data type conversions this way. The Date information stays date
 information
Shawn,
Thanks for the advice. Unfortunately, your example produces the following
error. Looking at it, I can see nothing obvious that is causing the error.
ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 DAY)
You need semicolons at the end of each statement.
--
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: Date Range

[Dirk Bremer \(NISC\)]

> Whether he needs semicolons depends on which tool he is using to execute
> his statements. If he is using the MySQL client or any of several other
> tools, I agree. If he is going through an ODBC connection, he doesn't need
> them. (At least my 3.52.x drivers can't accept more than one statement per
> request.)

I am using the command-line client. I have it working but it is off by
one-month, run as is, it is showing July data rather than August. I am
checking into it.

Dirk Bremer - Systems Programmer II - ESS/AMS  - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471

[EMAIL PROTECTED]
www.nisc.cc


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

Re: MIGRATION OF DATABASE FROM ONE BOX TO ANOTHER BOX

[DreamWerx]

Might try using windows program called SQLYOG.. makes this dead easy..


On Fri, 24 Sep 2004 09:52:10 -0700 (PDT), Seena Blace
<[EMAIL PROTECTED]> wrote:
> Hi,
> How to migrate mysql database from one box to another box ?
> thanks
> -Seena
> 
> 
> -
> Do you Yahoo!?
> vote.yahoo.com - Register online to vote today!
>

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

Re: Newbie - Server won't start after update

[gerald_clark]

David Lowe wrote:
Greetings!
I had mysql 4.0.18 installed from the binary package, was working 
my way through the tutorial [totally new to database work].  Then i 
installed the 4.0.20 package [again from your binary], i can't start 
the server.  Here are the results:

PowerBook:/usr/local/mysql portable$ sudo ./bin/mysqld_safe
Starting mysqld daemon with databases from /usr/local/mysql/data
040922 10:23:22  mysqld ended
And the error log says what?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Date Range

[SGreen]

Paul DuBois <[EMAIL PROTECTED]> wrote on 09/27/2004 02:07:54 PM:

> At 12:57 -0500 9/27/04, Dirk Bremer (NISC) wrote:
> >- Original Message -
> >From: <[EMAIL PROTECTED]>
> >To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
> >Cc: <[EMAIL PROTECTED]>
> >Sent: Monday, September 27, 2004 11:40
> >Subject: Re: Date Range
> >
> >
> >>  I see that you are on 4.0.18 so you can't use many of the new date
> >>  functions (4.1.1+) but has to be an easier way. Let's try this for a
> >>  "yesterday" query
> >>
> >>  set @dtEnd = CURDATE()
> >>  set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 DAY)
> >>
> >>  SELECT ident,
> >> job_coop,
> >> cycle,
> >> lpad(status,10,' ') as status,
> >> type,
> >> file_size,
> >> date_format(queue_time,'%Y-%m-%d %T') as queue_time,
> >> file_time,
> >> transfer_start,
> >> transfer_end
> >>  FROM queue
> >>  WHERE queue_time >= @dtStart
> >>  AND queue_time < @dtEnd
> >>
> >>
> >>  To compute the date range for "two months ago". (if the current 
month is
> >>  September, this will return July's data)
> >>
> >>  set @dtBegOfMonth = DATE_SUB(CURDATE(), INTERVAL 
DAYOFMONTH(CURDATE())-1
> >>  DAY)
> >>  set @dtEnd = DATE_SUB(@dtBegOfMonth, INTERVAL 1 month)
> >>  set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 month)
> >>
> >>  Then, continue with the same query as above. These should be really 
fast
> >>  as you are comparing constant values against what should be indexed 
table
> >>  data. You also avoid string conversions, substring comparisons, and 
other
> >>  data type conversions this way. The Date information stays date
> >>  information
> >>
> >
> >Shawn,
> >
> >Thanks for the advice. Unfortunately, your example produces the 
following
> >error. Looking at it, I can see nothing obvious that is causing the 
error.
> >
> >ERROR 1064: You have an error in your SQL syntax.  Check the manual 
that
> >corresponds to your MySQL server version for the right syntax to use 
near
> >'set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 DAY)
> 
> You need semicolons at the end of each statement.
> 
> 
> -- 
> Paul DuBois, MySQL Documentation Team
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com

Whether he needs semicolons depends on which tool he is using to execute 
his statements. If he is using the MySQL client or any of several other 
tools, I agree. If he is going through an ODBC connection, he doesn't need 
them. (At least my 3.52.x drivers can't accept more than one statement per 
request.)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: high availability question

[Jim Grill]

(Sorry for the long reply)
> If i write to server A, will server B automatically be
> notified of the change, and be properly updated? If i change server B
> instead, will server A be notified and updated?

Yes...Just like that. :-) They will both have the same data. I have a
similar set up in my office. In fact, I have a two way replication of all my
databases on two Linux machines and then I replicate only a few databases
from one of the Linux machines on a windows machine for cross platform
development purposes.

> safe, a dirty hack or a
> feature?

Well. The thing is: the master server has no clue it is a master or how
many slaves are present. So, in effect, a master can also be a slave and
slaves can have slaves. This includes two-way replication. I'd say it's a
feature and not a dirty hack.

Safe? ...that depends.

The only thing that is not safe is when you write to both machines at the
same time. There is a possibility that updates can appear out of order at
heavy load times even after all the changes have propagated both ways. That
is to say that while you will have all your data in both databases but
records may be in different orders if they were inserted or updated before
propagation completed. This could be huge problem is the same record was
updated on both servers or if you rely on LAST_INSERT_ID() to be accurate.
There is a plethora of possible problems due to that fact.

There are two scenarios:

1) For performance: Perform updates and inserts on the master and perform
all reads from the slave. You can also replicate the master and slave on two
more servers to add some redundancy. Two way replication is not necessary
for this situation. If you know that you will read 70% of the time and write
only 30% of the time you could use the master for INSERT, UPDATE, and DELETE
and then use two slaves for reads.

2) For redundancy: Two way replication - both servers will be up to date and
you can read or write to either server. In this scenario it is best to
always read and write to *one server at a time* (the master) unless it
dies - then switch to the slave while repairs are made. You can then STOP
SLAVE on the slave and bring the master back up and replicate the slave from
the mysql client prompt. When the master as back up to date STOP SLAVE on
the master and START SLAVE on the slave again.

Note that in both scenarios two-way replication is not really necessary. The
only real benefit to having two way replication is if your a lazy bum like
me and don't want to have to type anything or think to much when a server
dies or you feel like switching server for some other reason.

I am aware of scenarios where two-way replication is used along with special
client code that will automatically connect to the slave if the master dies.
This way if a database server crashes in the middle of the night the client
code will simply redirect connections to the slave or back to the master if
it somehow comes back to life (how lucky). I'm not too sure how safe this
idea is and I'm sure that some unnecessary overhead is introduced by testing
for live connections all the time.

My best advice to you is to set up a few servers and start testing.
Replication is not at all hard to accomplish.

Best of luck.

Jim Grill

> Hi Jim, Hi all!
>
> Doing a two way replication, assures me that i can write and read data
> from both servers? I mean, the app servers, can read from any data base
> on demand. If i write to server A, will server B automatically be
> notified of the change, and be properly updated? If i change server B
> instead, will server A be notified and updated?
>
> For this to work, what MySQL version do i have to use? 4, 4.1, 5.0
>
> I own both books by Jeremy and Paul, in Paul's book (the second edition,
> BTW) I almost certainly understood that replication is one way only,
> Master2Slave, the "cheat" ( just to give it a name! =) ) of configuring
> Master and Slave in a two way relationship is safe, a dirty hack or a
> feature? Paul's book is for MySQL 4, what version do i need for this to
> work?
>
> I can hard code the app to query any of the two db, but i _MUST_ assure
> that at any given time, they both have the same data.
>
> As always, thanxs! =)
>
> Vic.
>
>
>
> On Mon, 2004-09-27 at 12:43, Jim Grill wrote:
> > > Hi guys!
> > >
> > > I am having some doubts in how to implement a high availability,
> > > geographically distributed MySQL DB.
> > >
> > > The thing is, I would like to have.. let's say two (02) app. servers
> > > with two (02) DB servers, running exactly the same data. In case one
> > > fail, the other will be available. The thing is, how can i ensure that
> > > both DB servers has exactly the same data? I took a look at
Replication
> > > features, but it seems to only use MyISAM tables. This way should not
> > > work since, both App. Servers can insert data at any given moment, but
> > > having ONLY one server isn't the answer neither. I would like to know
> > > any suggestions o

Conexiones no autorizadas

[Mauricio Pellegrini]

Hola ,

Estoy empezando a notar intentos de ingreso con usuarios ilegales
no existentes) a mi server.

Mi pregunta es : Como lo rastreo teniendo como unico dato el numero de
ip desde el cual se esta conectando?

O si alguien tiene una mejor idea o ha pasado por lo mismo,
le agradecere me comente sus experiencias

Desde ya muchas gracias
Mauricio




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