date:20040904

Storing foreign characters in DB

2004-09-04 Thread MySQL

I'm having a problem figuring out how to deal with foreign characters in
text that was copied from an MS Word document and pasted into a form field,
then stored in a MySQL DB. (I have MySQL 3.23.58 running).

I'm not how sure how these characters are being stored in the MySQL
database, but, when I retrieve the text and run it through PHP's
htmlentities() function, each foreign character is converted into 2 other
foreign characters that don't at all represent the original.

For example, a lowercase u with an umlat over it (ü) is somehow displayed as
an uppercase A with an umlat over it followed by the 1/4 symbol after parsed
by htmlentities(). A lowercase o with an ulmat displays as an uppercase A
with an umlat over it followed by the paragraph symbol. It seems that the
uppercase A w/umlat is a constant, and the next character changes.

How are these foreign characters being stored in the DB? Do I need to do
something in order to store these characters properly, or is this something
I need to somehow do on the PHP side of things??

Thanks!

Monty.


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

RE: MySQL TEXT - Possible Corruption

2004-09-04 Thread Paul Higgins

Hi again,
Sorry for the double post.  I created a new database, recreated all my 
tables, repopulated them, and it seems to work now.  Any ideas as to what 
was causing this?

Paul

From: "Paul Higgins" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: MySQL TEXT - Possible Corruption
Date: Sat, 04 Sep 2004 23:42:51 -0400
Hi,
I've been having a problem using the TEXT column type.  I'm trying to 
insert a serialized PHP object into this field.  However, when I execute 
the insert command (via a php script), I cannot view the data until I 
restart the service.  I query the database using the command prompt, but 
the data that was just entered does not appear.

PHP can access the updated data.  I had this problem before with another 
table, but somehow that one is now fixed.  Any ideas?

Thanks,
Paul
_
On the road to retirement? Check out MSN Life Events for advice on how to 
get there! http://lifeevents.msn.com/category.aspx?cid=Retirement

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

_
Dont just search. Find. Check out the new MSN Search! 
http://search.msn.click-url.com/go/onm00200636ave/direct/01/

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

Re: Help needed with MySQL C API-based client (segfault)

2004-09-04 Thread Ruben Safir Secretary NYLXS

On Fri, Sep 19, 2003 at 09:18:22AM +0500, Vikram Vaswani wrote:
> Hello,
> 
> I need to write a simple C client for a project. I am using the MySQL C
> API. Attached is the code. It occassionally segfaults with no visible
> pattern. Could someone help me figure out why? Or any other comments on the
> code to help me make it better?

You know, I used to read this mailing list religiously before it became
flooded with W32 questions and PHP users.  And I've stay susbscribed 
but I haven't posted to it in many many months.  iIn fact, I didn't notice
I wasn't any longer subscribed.   So I'm writing this application in C 
and GTK and I was thinking, it's finally time to learn to write some MYSQL 
C API stuff.

I've written a lot of Oracle C programs in years past.  And now I'm looking 
at the C API stuff and wow, it is not readly understandable.  I open up
the mysql mail file with mutt, and bang, this is on the top!

I was going to ask the list if anyone has an exmaple of the basic needs
for a MYSQL program which makes a connection, sends a querry.  Checks the
potention errors, and maps the most basic column types to C types.

The docs say to look at examples in the source directory, but those 
aren't yet clear to me to understand.


Ruben

> 
> /* client.c */
> 
> #include 
> #include 
> 
> int main()
> {
> /* declare
> structures and variables */
>   char query[255];
>   int i, j, count;
> 
> MYSQL mysql;
> MYSQL_RES *result;
> MYSQL_ROW row;
>   MYSQL_FIELD
> *field;
> 
> /* initialize MYSQL structure */
> 
> mysql_init(&mysql);
> 
> /* connect to database */
> if
> (!(mysql_real_connect(&mysql, NULL, "root", "", "db1", 0, NULL, 0)))
> 
>  {
> fprintf(stderr, "Error in connection: %s\n",
> mysql_error(&mysql));
> }
> 
> for( ;; )
>   {
> 
> printf("query? ");
>   gets(query);
>   if (strcmp(query,"exit")
> == 0) 
>   {
>   break;
>   }
> 
>   /* execute query
> */
>   /* if error, display error message */
>   /* else check the type of
> query and handle appropriately */
>   if (mysql_query(&mysql, query) != 0)
> 
> {
>   fprintf(stderr, "Error in query: %s\n", mysql_error(&mysql));
>   }
> 
> else
>   {
>   if (result = mysql_store_result(&mysql))
>   {
>   /* SELECT
> query */
>   /* retrieve result set */
>   int numRecords =
> mysql_num_rows(result);
>   int numFields = mysql_num_fields(result);
> 
> for (i = 0; i < numRecords; i++)
>   {
>   row =
> mysql_fetch_row(result);
> 
>   for (j = 0; j < numFields; j++)
>   {
> 
> //field= mysql_fetch_field(result);
>   fprintf(stdout, "%s", row[j]);
> 
>   j != (numFields-1) ? printf(", ") : printf("\n");
>   }
>   }
> 
> fprintf(stdout, "** Query successful, %d rows retrieved **\n",
> numRecords);
>   }
>   else
>   {
>   if (mysql_field_count(&mysql) == 0)
> 
> {
>   /* non-SELECT query */
>   fprintf(stdout, "** Query successful, %d
> rows affected **\n", mysql_affected_rows(&mysql));
>   }
>   else
>   {
> 
>   fprintf(stderr, "Error in reading result set: %s\n",
> mysql_error(&mysql));
>   }
>   }
>   }
> 
>   /* clean up */
> 
> mysql_free_result(result);
>   }
> mysql_close(&mysql);
> }
> 
> --
> I wouldn't recommend sex, drugs, and insanity for everyone, but it works
> for me.
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
__
Brooklyn Linux Solutions

So many immigrant groups have swept through our town 
that Brooklyn, like Atlantis, reaches mythological 
proportions in the mind of the world  - RI Safir 1998

DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002
http://fairuse.nylxs.com

http://www.mrbrklyn.com - Consulting
http://www.inns.net <-- Happy Clients
http://www.nylxs.com - Leadership Development in Free Software
http://www2.mrbrklyn.com/resources - Unpublished Archive or stories and articles from 
around the net
http://www2.mrbrklyn.com/downtown.html - See the New Downtown Brooklyn

1-718-382-0585

-- 
MySQL General Maili

MySQL TEXT - Possible Corruption

2004-09-04 Thread Paul Higgins

Hi,
I've been having a problem using the TEXT column type.  I'm trying to insert 
a serialized PHP object into this field.  However, when I execute the insert 
command (via a php script), I cannot view the data until I restart the 
service.  I query the database using the command prompt, but the data that 
was just entered does not appear.

PHP can access the updated data.  I had this problem before with another 
table, but somehow that one is now fixed.  Any ideas?

Thanks,
Paul
_
On the road to retirement? Check out MSN Life Events for advice on how to 
get there! http://lifeevents.msn.com/category.aspx?cid=Retirement

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

Re: grant tables update backward compatibility

2004-09-04 Thread Bob Hockney

Hi Eric,:

Thanks for responding.  Preliminary tests indicate no problems, although it does of 
necessity make assumptions about which of the new privileges existing users should 
have when upgrading, but they were fairly safe.

Regards,

-Bob

> I would assume that you can since mysql probably does an internal
> select col, col1 to get the grant information and the new tables
> contain everything that the old ones do plus some extra privileges. 
> The only thing I would worry about would be passwords changing. Make a
> small test case and let everybody know. :)
> 
> -Eric
> 
> 
> 
> On Fri, 3 Sep 2004 09:43:28 -0700 (GMT-07:00), [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> wrote:
> > Hi there,
> > 
> > I have been using mysql 3.23.58, and I want to upgrade to 4.0.20.  My question is 
> > this: after I run the script to upgrade the grant tables to support the new 
> > privilgeges, can I then revert back to 3.23.58 seamlessly or will I need to 
> > readjust the grant tables.  Thanks in advance.
> > 
> > -Bob
> > 
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> > 
> > 
> 
> 
> 
> -- 
> Eric Bergen
> [EMAIL PROTECTED]



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

Re: 1 database; 2 scripts; different results

2004-09-04 Thread Amer Neely

Michael Stassen wrote:
Amer Neely wrote:

OK, obviously a bit more background seems to be in order.
My site is hosted but I am not the administrator. I have 2 perl
scripts, behaving as already described. I also have phpMyAdmin access
to my databases. I don't have command line access to the server.

Well, that's a start. Do you run the perl scripts from the command line
or via a web interface? If the latter, is your script running through
mod_perl with persistent connections?

Both scripts, which reside in the same directory on the server, are run through the web browser. And
they do access the same database, and the same tables.

I don't use InnoDB or transactions.

OK.
What is happening is that I delete a record with one script, but it
still shows up when the db is queried by the other script. It DOESN'T
show up in the one I used to delete the record.

Right, I got that. But you still haven't showed us the relevant parts
of the scripts. How are we to help diagnose the problem without seeing
the connect line and the query for each script?
Here are the relevant snippets. Three tables have to accessed in order for a
record to be deleted.
$sth=$dbh->do("DELETE FROM $TITLES WHERE ArtistID = $ThisArtistID AND TitleID =
$ThisTitleID");
$sth=$dbh->do("DELETE FROM $TRACKS WHERE TitleID = $ThisTitleID AND ArtistID =
$ThisArtistID");
$sth=$dbh->do("DELETE FROM $ARTISTS WHERE ArtistID = $ThisArtistID");

phpMyAdmin shows me that the record still exists in the db. My scripts
are not the problem.

What does this mean? A phpMyAdmin query against the db retrieves the
entire, supposedly-deleted row? You can see the id of the row as a link
to the row? You can see that the row count is still 103?
A query in phpMyAdmin to 'select * from Artists where artistid=124' is the query I ran.
Before I used phpMyAdmin to delete the row, it was still there, after supposedly being deleted by my
script. And yet, that same script acted as though the row (and in fact complete record) was deleted.
A query for that particular artist, title, or any tracks came back empty.

Have you tried deleting the row in phpmyadmin? If you can, then we can
be sure it's a script problem. If you cannot, then it may be a mysql
problem.
Yes, I was able to delete the row with phpMyAdmin. And it finally stopped
appearing in both scripts.

When I tried to execute a FLUSH TABLES command with phpMyAdmin, that's
when I get the error about not having RELOAD privilege. Sounds pretty
clear to me.

Right. It is clear that you need the RELOAD privilege to FLUSH TABLES.
But that's a red herring. You do not need FLUSH TABLES to delete a
row. In fact, FLUSH TABLES has nothing to do with deleting rows. FLUSH
TABLES closes all tables and wipes out the cache.
OK, I see what your saying now.

So tell me, is this a 'properly functioning mysql server'?

If FLUSH TABLES fixed the problem, that would mean that your mysql
server was improperly caching the row in question after it was deleted.
In other words, that would mean mysql was broken. But we know that is
not the case. If mysql were keeping the row in the cache even though it
had been deleted, it would show up in all your clients, not all but one.
I can't run FLUSH TABLES, and the row was showing up in 1 of the 2 scripts, so I don't know how to
respond to that. It seems to be a paradox.

So, as I said, without seeing the relevant parts of your scripts, it is
impossible to do more than guess. I'll go ahead and take a shot. There
have been about 3 threads similar to this recently in which it turned
out that one script connected to the production server while the other
pointed to the development server. Have you double-checked your
connection strings to make sure they are identical?
Yes, as mentioned in this response, both scripts reside in the same directory, and there is only 1
database they are accessing. I checked to make sure I didn't have 2 sets of tables (upper-case,
lower-case) but that isn't the case either. I made a mistake earlier about the mysql version. It is
running on a Linux box, so case is important.

The connection code for each is identical, because one script is a saved-as copy of the other. Only
differences being in the action of some of the form submit buttons.

--
/* All outgoing email scanned by AVG Antivirus */
Amer Neely, Softouch Information Services
Home of Spam Catcher & North Bay Information Technology Networking Group
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
Perl | PHP | MySQL | CGI programming for all data entry forms.
"We make web sites work!"
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Is it possible to have Undeletable Records?

2004-09-04 Thread Michael Ayers

Wesley Furgiuele wrote:
I don't think so. I think your current method of either storing it in
the query or with a boolean field is your best bet. What I've seen
done in the past is that records get marked with a user level and
there is either one or a group of users who are allowed to delete  or
modify those otherwise permanent records.
Wes
On Fri, 03 Sep 2004 11:38:35 +0100, zzapper <[EMAIL PROTECTED]> wrote:
 

Hi Y'All
Is it possible to have undeletable/unmodifiable  records in a table of otherwise 
modifiable &
deleteable & createable records?
At present I do it at the Update/Delete level where I have clauses which prevent 
certain records
being changed. I suppose I could also have an extra boolean field "Record Read only", 
but is there
anyway to specify at the data level?
zzapper (vim, cygwin, wiki & zsh)
--
vim -c ":%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?"
http://www.vim.org/tips/tip.php?tip_id=305  Best of Vim Tips
   

 

Here is an off the wall idea. 
Create a MERGE table for the data. 
Put the undeletable data into one table then pack it. 
Put the other data into a normal myisam file.
I have no idea if this will work.  But I think it is worth a try.
Good Luck


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

Re: 1 database; 2 scripts; different results

2004-09-04 Thread Michael Stassen

Amer Neely wrote:

I'm pretty sure it's not.  With a properly functioning mysql server, 
you need neither FLUSH TABLES nor the RELOAD privilege to delete a row 
from a table.  See the manual for a description of FLUSH TABLES 
.

I did and imagine my surprise when I read this:
"You should use the FLUSH statement if you want to clear some of the 
internal caches MySQL uses. To execute FLUSH, you must have the RELOAD 
privilege."

There is a subtle difference between actually deleting a row and whether 
MySQL shows it to you or not. Even though the row is still in the table, 
a query from the perl script that I used to 'delete' it didn't return 
it. It had been flagged as deleted for that session, but that is not the 
same as being deleted. Which is why it was visible from another script.
That's not what that means.  If a table has been changed (row deleted), the 
cache is no longer valid and must be updated.  Running FLUSH TABLES is *NOT* 
part of deleting rows.

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

Re: 1 database; 2 scripts; different results

2004-09-04 Thread Michael Stassen

Amer Neely wrote:

OK, obviously a bit more background seems to be in order.
My site is hosted but I am not the administrator. I have 2 perl scripts, 
behaving as already described. I also have phpMyAdmin access to my 
databases. I don't have command line access to the server.
Well, that's a start.  Do you run the perl scripts from the command line or 
via a web interface?  If the latter, is your script running through mod_perl 
with persistent connections?

I don't use InnoDB or transactions.
OK.
What is happening is that I delete a record with one script, but it 
still shows up when the db is queried by the other script. It DOESN'T 
show up in the one I used to delete the record.
Right, I got that.  But you still haven't showed us the relevant parts of 
the scripts.  How are we to help diagnose the problem without seeing the 
connect line and the query for each script?

phpMyAdmin shows me that the record still exists in the db. My scripts 
are not the problem.
What does this mean?  A phpMyAdmin query against the db retrieves the 
entire, supposedly-deleted row?  You can see the id of the row as a link to 
the row?  You can see that the row count is still 103?

Have you tried deleting the row in phpmyadmin?  If you can, then we can be 
sure it's a script problem.  If you cannot, then it may be a mysql problem.

When I tried to execute a FLUSH TABLES command with 
phpMyAdmin, that's when I get the error about not having RELOAD 
privilege. Sounds pretty clear to me.
Right.  It is clear that you need the RELOAD privilege to FLUSH TABLES.  But 
that's a red herring.  You do not need FLUSH TABLES to delete a row.  In 
fact, FLUSH TABLES has nothing to do with deleting rows.  FLUSH TABLES 
closes all tables and wipes out the cache.

So tell me, is this a 'properly functioning mysql server'?
If FLUSH TABLES fixed the problem, that would mean that your mysql server 
was improperly caching the row in question after it was deleted.  In other 
words, that would mean mysql was broken.  But we know that is not the case. 
 If mysql were keeping the row in the cache even though it had been 
deleted, it would show up in all your clients, not all but one.

So, as I said, without seeing the relevant parts of your scripts, it is 
impossible to do more than guess.  I'll go ahead and take a shot.  There 
have been about 3 threads similar to this recently in which it turned out 
that one script connected to the production server while the other pointed 
to the development server.  Have you double-checked your connection strings 
to make sure they are identical?

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

Re: 1 database; 2 scripts; different results

2004-09-04 Thread Amer Neely

Michael Stassen wrote:

Amer Neely wrote:
Amer Neely wrote:
Brian Reichert wrote:
On Sat, Sep 04, 2004 at 02:40:20PM -0400, Amer Neely wrote:
My question is, in my admin script I deleted a record, but it
still shows up using the other script. The admin script shows 102
records and the other shows 103. The record does show up in the
'public' script, so it's not just a mis-count. How is this
possible? Is there a step I'm missing because I'm using 2
different scripts to access the same tables? That doesn't make
sense. Can someone shed some light on what might be happening here?

Web caching? Flushing tables after your delete?

I just checked the database with phpMyAdmin. The record I deleted
through my script is still there, but doesn't show up in a query for
it. Tried to execute the 'FLUSH TABLES' query with phpMyAdmin, but
got this instead:

#1227 - Access denied. You need the RELOAD privilege for this operation
So I'll need to see about getting RELOAD privilege I guess. Thanks
for pointing in the right direction.

Michael Stassen wrote:
You can eliminate webserver/browser/caching issues from
consideration by trying your query directly in the mysql client.

Are you using InnoDB and transactions? It sounds to me as if you
deleted the record but have not yet committed. In that case, the
deleted record will be gone for the client which started the
transaction, but will still be available to all other clients until
you commit.

Michael
No, I don't use InnoDB or transactions. I'm waiting to hear back from my

Aside from the symptom match, I asked because you mentioned "buttons to
commit the action" in your first message.

host. I don't have access to the mysql client on the server. I'm
pretty sure it's the RELOAD privilege I'm missing. Thanks though.

I'm pretty sure it's not. With a properly functioning mysql server, you
need neither FLUSH TABLES nor the RELOAD privilege to delete a row from
a table. See the manual for a description of FLUSH TABLES
.
I did and imagine my surprise when I read this:
"You should use the FLUSH statement if you want to clear some of the internal caches MySQL uses. To
execute FLUSH, you must have the RELOAD privilege."

There is a subtle difference between actually deleting a row and whether MySQL shows it to you or
not. Even though the row is still in the table, a query from the perl script that I used to 'delete'
it didn't return it. It had been flagged as deleted for that session, but that is not the same as
being deleted. Which is why it was visible from another script.
At this point, we don't have enough information to do more than guess.
If you're sure it's neither browser caching nor a transaction, that
leaves something in your scripts, so you should give us some details.
What scripting language do you use? What do your queries look like?
What results do you get. And what do you mean by "I just checked the
database with phpMyAdmin. The record I deleted through my script is
still there,
but doesn't show up in a query for it."?

Michael

Re: Forcing case sensitivity in queries

2004-09-04 Thread Yannick Warnier

Le dim 05/09/2004 à 00:14, Yannick Warnier a écrit :
> Hi,
> 
> When I execute a query in MySQL client, it seems like I cannot get the
> case-sensitivity to be activated.
> 
> If I ask:
> SELECT * FROM mytable WHERE field = 'd';
> 
> I get all records containing 'd' or 'D'.
> 
> How do I enable case-sensitivity?

OK sorry, I found that using 'binary' solves the problem.
SELECT * FROM mytable WHERE field = binary 'd';


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

Re: Forcing case sensitivity in queries

2004-09-04 Thread Michael Stassen

You need BINARY .
  SELECT * FROM mytable WHERE BINARY field = 'd';
Michael
Yannick Warnier wrote:
Hi,
When I execute a query in MySQL client, it seems like I cannot get the
case-sensitivity to be activated.
If I ask:
SELECT * FROM mytable WHERE field = 'd';
I get all records containing 'd' or 'D'.
How do I enable case-sensitivity?
Thanks,
Yannick

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

Re: Graphics and blobs

2004-09-04 Thread Stuart Felenstein

My apologies for rushing in with a question that gave
little in the way of details.
I've straightend out the mess ;)
Just in case it's of any relevance, I've opted to
store the gifs in a file directory and use a varchar
field to  make a link to the file / directory.
All is good.
Sorry again!
Stuart
--- Paul DuBois <[EMAIL PROTECTED]> wrote:

> At 9:56 -0700 9/4/04, Stuart Felenstein wrote:
> >This maybe OT but perhaps somone has a clue here.
> >I am storing gifs in a Blob, well tinyblob field.
> >
> >I was under the assumption they could just be
> >pulled out and shown on a web page similar to
> running
> >any other query.
> >
> >Apparently not, someone have a hint ?
> 
> You're asking us to guess what it was that you tried
> and also
> what particular symptoms of failure you were seeing.
> 
> My guess: Your blob values are longer than 255
> bytes, the
> maximum size of a TINYBLOB column.
> 
> -- 
> 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]
> 
> 


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

Re: 1 database; 2 scripts; different results

2004-09-04 Thread Amer Neely

Michael Stassen wrote:

Web caching? Flushing tables after your delete?

#1227 - Access denied. You need the RELOAD privilege for this operation
So I'll need to see about getting RELOAD privilege I guess. Thanks
for pointing in the right direction.

Michael Stassen wrote:
You can eliminate webserver/browser/caching issues from
consideration by trying your query directly in the mysql client.

Michael
No, I don't use InnoDB or transactions. I'm waiting to hear back from my

Aside from the symptom match, I asked because you mentioned "buttons to
commit the action" in your first message.
Yes, sorry. In my scripts.

host. I don't have access to the mysql client on the server. I'm
pretty sure it's the RELOAD privilege I'm missing. Thanks though.

At this point, we don't have enough information to do more than guess.
If you're sure it's neither browser caching nor a transaction, that
leaves something in your scripts, so you should give us some details.
What scripting language do you use? What do your queries look like?
What results do you get. And what do you mean by "I just checked the
database with phpMyAdmin. The record I deleted through my script is
still there,
but doesn't show up in a query for it."?

Michael
OK, obviously a bit more background seems to be in order.
My site is hosted but I am not the administrator. I have 2 perl scripts, behaving as already
described. I also have phpMyAdmin access to my databases. I don't have command line access to the
server.

I don't use InnoDB or transactions.
What is happening is that I delete a record with one script, but it still shows up when the db is
queried by the other script. It DOESN'T show up in the one I used to delete the record.

phpMyAdmin shows me that the record still exists in the db. My scripts are not the problem. When I
tried to execute a FLUSH TABLES command with phpMyAdmin, that's when I get the error about not
having RELOAD privilege. Sounds pretty clear to me.

So tell me, is this a 'properly functioning mysql server'?
--
/* All outgoing email scanned by AVG Antivirus */
Amer Neely, Softouch Information Services
Home of Spam Catcher & North Bay Information Technology Networking Group
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
Perl | PHP | MySQL | CGI programming for all data entry forms.
"We make web sites work!"
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Forcing case sensitivity in queries

2004-09-04 Thread Yannick Warnier

Hi,

When I execute a query in MySQL client, it seems like I cannot get the
case-sensitivity to be activated.

If I ask:
SELECT * FROM mytable WHERE field = 'd';

I get all records containing 'd' or 'D'.

How do I enable case-sensitivity?

Thanks,

Yannick


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

Re: 1 database; 2 scripts; different results

2004-09-04 Thread Michael Stassen


Amer Neely wrote:
Amer Neely wrote:
Brian Reichert wrote:
On Sat, Sep 04, 2004 at 02:40:20PM -0400, Amer Neely wrote:
My question is, in my admin script I deleted a record, but it still 
shows up using the other script. The admin script shows 102 records 
and the other shows 103. The record does show up in the 'public' 
script, so it's not just a mis-count. How is this possible? Is 
there a step I'm missing because I'm using 2 different scripts to 
access the same tables? That doesn't make sense. Can someone shed 
some light on what might be happening here?
Web caching?  Flushing tables after your delete?
I just checked the database with phpMyAdmin. The record I deleted 
through my script is still there, but doesn't show up in a query for 
it. Tried to execute the 'FLUSH TABLES' query with phpMyAdmin, but 
got this instead:

#1227 - Access denied. You need the RELOAD privilege for this operation
So I'll need to see about getting RELOAD privilege I guess. Thanks 
for pointing in the right direction.
Michael Stassen wrote:
You can eliminate webserver/browser/caching issues from consideration 
by trying your query directly in the mysql client.

Are you using InnoDB and transactions?  It sounds to me as if you 
deleted the record but have not yet committed.  In that case, the 
deleted record will be gone for the client which started the 
transaction, but will still be available to all other clients until 
you commit.

Michael
No, I don't use InnoDB or transactions. I'm waiting to hear back from my 
Aside from the symptom match, I asked because you mentioned "buttons to 
commit the action" in your first message.

host. I don't have access to the mysql client on the server. I'm pretty 
sure it's the RELOAD privilege I'm missing. Thanks though.
I'm pretty sure it's not.  With a properly functioning mysql server, you 
need neither FLUSH TABLES nor the RELOAD privilege to delete a row from a 
table.  See the manual for a description of FLUSH TABLES 
.

At this point, we don't have enough information to do more than guess.  If 
you're sure it's neither browser caching nor a transaction, that leaves 
something in your scripts, so you should give us some details.  What 
scripting language do you use?  What do your queries look like?  What 
results do you get.  And what do you mean by "I just checked the database 
with phpMyAdmin. The record I deleted through my script is still there,
but doesn't show up in a query for it."?

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

Re: 1 database; 2 scripts; different results

2004-09-04 Thread Amer Neely

Amer Neely wrote:
Brian Reichert wrote:
On Sat, Sep 04, 2004 at 02:40:20PM -0400, Amer Neely wrote:
My question is, in my admin script I deleted a record, but it still
shows up using the other script. The admin script shows 102 records
and the other shows 103. The record does show up in the 'public'
script, so it's not just a mis-count. How is this possible? Is there
a step I'm missing because I'm using 2 different scripts to access
the same tables? That doesn't make sense. Can someone shed some
light on what might be happening here?

Web caching? Flushing tables after your delete?
I just checked the database with phpMyAdmin. The record I deleted
through my script is still there, but doesn't show up in a query for
it. Tried to execute the 'FLUSH TABLES' query with phpMyAdmin, but got
this instead:

#1227 - Access denied. You need the RELOAD privilege for this operation
So I'll need to see about getting RELOAD privilege I guess. Thanks for
pointing in the right direction.

Michael Stassen wrote:
You can eliminate webserver/browser/caching issues from consideration by
trying your query directly in the mysql client.

Michael
No, I don't use InnoDB or transactions. I'm waiting to hear back from my host. I don't have access
to the mysql client on the server. I'm pretty sure it's the RELOAD privilege I'm missing. Thanks though.

Re: 1 database; 2 scripts; different results

2004-09-04 Thread Michael Stassen

You can eliminate webserver/browser/caching issues from consideration by 
trying your query directly in the mysql client.

Are you using InnoDB and transactions?  It sounds to me as if you deleted 
the record but have not yet committed.  In that case, the deleted record 
will be gone for the client which started the transaction, but will still be 
available to all other clients until you commit.

Michael
Amer Neely wrote:
Brian Reichert wrote:
On Sat, Sep 04, 2004 at 02:40:20PM -0400, Amer Neely wrote:
My question is, in my admin script I deleted a record, but it still 
shows up using the other script. The admin script shows 102 records 
and the other shows 103. The record does show up in the 'public' 
script, so it's not just a mis-count. How is this possible? Is there 
a step I'm missing because I'm using 2 different scripts to access 
the same tables? That doesn't make sense. Can someone shed some light 
on what might be happening here?

Web caching?  Flushing tables after your delete?
I just checked the database with phpMyAdmin. The record I deleted 
through my script is still there, but doesn't show up in a query for it. 
Tried to execute the 'FLUSH TABLES' query with phpMyAdmin, but got this 
instead:

#1227 - Access denied. You need the RELOAD privilege for this operation
So I'll need to see about getting RELOAD privilege I guess. Thanks for 
pointing in the right direction.

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

Re: 1 database; 2 scripts; different results

2004-09-04 Thread Amer Neely

Brian Reichert wrote:
On Sat, Sep 04, 2004 at 02:40:20PM -0400, Amer Neely wrote:
My question is, in my admin script I deleted a record, but it still shows 
up using the other script. The admin script shows 102 records and the 
other shows 103. The record does show up in the 'public' script, so it's 
not just a mis-count. How is this possible? Is there a step I'm missing 
because I'm using 2 different scripts to access the same tables? That 
doesn't make sense. Can someone shed some light on what might be happening 
here?

Web caching?  Flushing tables after your delete?
I just checked the database with phpMyAdmin. The record I deleted through my script is still there, 
but doesn't show up in a query for it. Tried to execute the 'FLUSH TABLES' query with phpMyAdmin, 
but got this instead:

#1227 - Access denied. You need the RELOAD privilege for this operation
So I'll need to see about getting RELOAD privilege I guess. Thanks for pointing in the 
right direction.
--
/* All outgoing email scanned by AVG Antivirus */
Amer Neely, Softouch Information Services
Home of Spam Catcher & North Bay Information Technology Networking Group
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
Perl | PHP | MySQL | CGI programming for all data entry forms.
"We make web sites work!"
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 1 database; 2 scripts; different results

2004-09-04 Thread Amer Neely

Brian Reichert wrote:
On Sat, Sep 04, 2004 at 02:40:20PM -0400, Amer Neely wrote:
My question is, in my admin script I deleted a record, but it still shows 
up using the other script. The admin script shows 102 records and the 
other shows 103. The record does show up in the 'public' script, so it's 
not just a mis-count. How is this possible? Is there a step I'm missing 
because I'm using 2 different scripts to access the same tables? That 
doesn't make sense. Can someone shed some light on what might be happening 
here?

Web caching?  Flushing tables after your delete?
Yes, I had thought of caching in my browser so flushed everything. I'm using Mozilla and Firefox and 
have found that they share several pieces of information (this drove me nuts for a while), so you 
have to basically flush both browsers, and even then sometimes shut down and start over. But that 
didn't resolve this situation.

I'll try flushing after deleting (no pun intended) :) Thanks.
--
/* All outgoing email scanned by AVG Antivirus */
Amer Neely, Softouch Information Services
Home of Spam Catcher & North Bay Information Technology Networking Group
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
Perl | PHP | MySQL | CGI programming for all data entry forms.
"We make web sites work!"
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 1 database; 2 scripts; different results

2004-09-04 Thread Brian Reichert

On Sat, Sep 04, 2004 at 02:40:20PM -0400, Amer Neely wrote:
> My question is, in my admin script I deleted a record, but it still shows 
> up using the other script. The admin script shows 102 records and the 
>  other shows 103. The record does show up in the 'public' script, so it's 
> not just a mis-count. How is this possible? Is there a step I'm missing 
> because I'm using 2 different scripts to access the same tables? That 
> doesn't make sense. Can someone shed some light on what might be happening 
> here?

Web caching?  Flushing tables after your delete?

-- 
Brian Reichert  <[EMAIL PROTECTED]>
37 Crystal Ave. #303Daytime number: (603) 434-6842
Derry NH 03038-1713 USA BSD admin/developer at large

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

Question concerning lack of binaries with OpenSSL support

2004-09-04 Thread Douglas K. Fischer

This is an issue I have seen many people ask over the last year or two, but 
I can't say I've ever seen a comprehensive answer (searched the archives 
heavily, too).

I realize there are no binaries available directly from MySQL with OpenSSL 
support compiled in. I am curious as to why (I'm sure there is a very good 
reason for this as I have no doubt such binaries would be available otherwise).

Under most circumstances, I would not consider compiling MySQL from 
scratch, based upon the number of people (MySQL developers and gurus) who 
have counseled against going so (citing that the precompiled binaries will 
almost always offer better performance and stability). However, I am faced 
with some security requirements from some financials customers who require 
that our installation of MySQL offer encryption of client connections (and 
slave connections as well once we migrate to 4.1). This leaves me in a 
position where I am going to need to go down the path of compilation (and 
before someone mentions it yes I've looked at doing this via stunnel etc 
but it has been decided that native SSL support is going to be needed to 
meet all the audit requirements).

I suppose my questions are this:
1. What issues are preventing MySQL from distributing binaries that have 
OpenSSL support compiled in?
2. Are these issues something that may be resolved in the (hopefully near) 
future?
3. If the answers to 1 and 2 are negative, what would be the advice for 
compilation of MySQL (compiler version, etc) that would ensure the most 
successful compilation (i.e. I want to avoid sacrificing performance and/or 
stability).

Many thanks,
Doug

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

1 database; 2 scripts; different results

2004-09-04 Thread Amer Neely

I have one database that is currently being accessed by 2 scripts which query the database. One is 
for me to administer the db contents. The other is for public display, and so has the final buttons 
 disabled for 'Delete', 'Add', 'Modify', 'Dump', and 'Restore'. Users can go through the motions of 
each feature to see how things look, but the buttons to commit the action are disabled. Both scripts 
access the same tables in the database.

My question is, in my admin script I deleted a record, but it still shows up using the other script. 
 The admin script shows 102 records and the other shows 103. The record does show up in the 
'public' script, so it's not just a mis-count. How is this possible? Is there a step I'm missing 
because I'm using 2 different scripts to access the same tables? That doesn't make sense. Can 
someone shed some light on what might be happening here?

MySQL 4.0.20a for Win32
--
/* All outgoing email scanned by AVG Antivirus */
Amer Neely, Softouch Information Services
Home of Spam Catcher & North Bay Information Technology Networking Group
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
Perl | PHP | MySQL | CGI programming for all data entry forms.
"We make web sites work!"
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 4.1.4-gamma cannot compile

2004-09-04 Thread Paul DuBois

At 13:29 -0400 9/4/04, Michael Stassen wrote:
Paul DuBois wrote:
At 16:14 +0800 9/1/04, Unreal HSHH wrote:
 got this error and broken when making mysql 4.1.4-gamma
 automake-1.7: not found
 WARNING:
   `automake-1.7' is needed, and you do not seem to have it handy on your
   system.  You might have modified some files without having the
   proper tools for further handling them.  Check the `README' file,
   it often tells you about the needed prerequirements for installing
   this package.  You may also peek at any GNU archive site, in case
   some other package would contain this missing `automake-1.7' program.
To give some context, this happens (for me, at least) in 
cmd-line-utils/libedit.

 But I already have automake-1.9
I have automake-1.6.  (Mac OS X, 10.3.5, if it matters.)
 autoconf-2.59_2
 automake-1.9
 bison-1.875_3
 gmake-3.80_2
 libtool-1.5.6_1
 m4-1.4.1
I see something similar.
If I do this first:
(cd innobase; aclocal;autoheader;autoconf;automake)
aclocal;autoheader;autoconf;automake
And then run configure, the build succeeds.  Does that work for you?
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Yes, it works for me, but we shouldn't have to do that, right?
Right.  I'm trying to gather information about the problem so it can be fixed.

I think this is a compounding of 2 problems:
Problem 1: configure has automake-1.7 hard-coded, so a build will 
not succeed if automake is needed unless you have precisely that 
version (or at least appear to).  INSTALL-SOURCE says any 
automake >= 1.5 will do, by the way.  Note that this is why missing 
is giving the generic "something is missing" error message rather 
than the specific "automake is missing" message.

Problem 2: Something is missing/wrong in the source (presumably in 
cmd-line-utils/libedit) that triggers the need for automake, 
exposing proble 1.  I note that mysql 4.1.3 also has automake-1.7 
hard-coded, but I did not run into this problem building 4.1.3.

Michael

--
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: Graphics and blobs

2004-09-04 Thread Paul DuBois

At 9:56 -0700 9/4/04, Stuart Felenstein wrote:
This maybe OT but perhaps somone has a clue here.
I am storing gifs in a Blob, well tinyblob field.
I was under the assumption they could just be
pulled out and shown on a web page similar to running
any other query.
Apparently not, someone have a hint ?
You're asking us to guess what it was that you tried and also
what particular symptoms of failure you were seeing.
My guess: Your blob values are longer than 255 bytes, the
maximum size of a TINYBLOB column.
--
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: 4.1.4-gamma cannot compile

2004-09-04 Thread Michael Stassen

Paul DuBois wrote:
>At 16:14 +0800 9/1/04, Unreal HSHH wrote:
>
>> got this error and broken when making mysql 4.1.4-gamma
>>
>> automake-1.7: not found
>> WARNING:
>>   `automake-1.7' is needed, and you do not seem to have it handy on your
>>   system.  You might have modified some files without having the
>>   proper tools for further handling them.  Check the `README' file,
>>   it often tells you about the needed prerequirements for installing
>>   this package.  You may also peek at any GNU archive site, in case
>>   some other package would contain this missing `automake-1.7' program.
To give some context, this happens (for me, at least) in cmd-line-utils/libedit.
>> But I already have automake-1.9
I have automake-1.6.  (Mac OS X, 10.3.5, if it matters.)
>> autoconf-2.59_2
>> automake-1.9
>> bison-1.875_3
>> gmake-3.80_2
>> libtool-1.5.6_1
>> m4-1.4.1
>
>I see something similar.
>
>If I do this first:
>
>(cd innobase; aclocal;autoheader;autoconf;automake)
>aclocal;autoheader;autoconf;automake
>
>And then run configure, the build succeeds.  Does that work for you?
>
>--
>Paul DuBois, MySQL Documentation Team
>Madison, Wisconsin, USA
>MySQL AB, www.mysql.com
Yes, it works for me, but we shouldn't have to do that, right?
I think this is a compounding of 2 problems:
Problem 1: configure has automake-1.7 hard-coded, so a build will not 
succeed if automake is needed unless you have precisely that version (or at 
least appear to).  INSTALL-SOURCE says any automake >= 1.5 will do, by the 
way.  Note that this is why missing is giving the generic "something is 
missing" error message rather than the specific "automake is missing" message.

Problem 2: Something is missing/wrong in the source (presumably in 
cmd-line-utils/libedit) that triggers the need for automake, exposing proble 
1.  I note that mysql 4.1.3 also has automake-1.7 hard-coded, but I did not 
run into this problem building 4.1.3.

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

Re: Problems Compiling

2004-09-04 Thread Michael Stassen

--prefix is the only option you passed to configure?  You must have missed 
the recommendations in the manual.  Take a look at how MySQL builds their 
official binaries . 
There is also a section on building and other issues under Linux 
.  I can't say whether that 
will prevent the segfault, though.  There could be something wrong with your 
build environment.

You may find the simplest course would be to download the official binary 
distribution from mysql.com .

Michael
Andrew Wheeler wrote:
I am not sure that this is the correct list. I did not
see any list that seemed more appropriate.
I have RedHat 8:
gcc-3.2-7
I run ./configure --prefix=/usr/local/mysql
which according to the log exited with code 0
Then when I run make I get the following errors. My
question is what is the signifigance and how do I
correct.
a ../innobase/thr/libthr.a ../innobase/sync/libsync.a
../innobase/data/libdata.a ../innobase/mach/libmach.a
../innobase/ha/libha.a ../innobase/dyn/libdyn.a
../innobase/mem/libmem.a ../innobase/sync/libsync.a
../innobase/ut/libut.a ../innobase/os/libos.a
../innobase/ut/libut.a -lrt ../isam/libnisam.a
../merge/libmerge.a ../myisam/libmyisam.a
../myisammrg/libmyisammrg.a ../heap/libheap.a
../vio/libvio.a ../mysys/libmysys.a ../dbug/libdbug.a
../regex/libregex.a ../strings/libmystrings.a -ldl
-lpthread -lz -lcrypt -lnsl -lm -lpthread
make[4]: Leaving directory
`/root/downloads/mysql-4.0.20/sql'
make[3]: *** [all-recursive] Segmentation fault
make[3]: Leaving directory
`/root/downloads/mysql-4.0.20/sql'
make[2]: *** [all] Error 2
make[2]: Leaving directory
`/root/downloads/mysql-4.0.20/sql'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory
`/root/downloads/mysql-4.0.20'
Segmentation fault
Andrew 

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

Graphics and blobs

2004-09-04 Thread Stuart Felenstein

This maybe OT but perhaps somone has a clue here.
I am storing gifs in a Blob, well tinyblob field.

I was under the assumption they could just be 
pulled out and shown on a web page similar to running
any other query.

Apparently not, someone have a hint ?

Thanks
Stuart

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

Re: Updating a one-to-many with a MIN result from 'many' for each 'one' record?

2004-09-04 Thread Michael Stassen

Yes, there's a better way -- use a temporary table:
  CREATE TEMPORARY TABLE minopen
 SELECT Account_Number, MIN(Open_DATE) AS min_open_date
 FROM Table1
 GROUP BY Account_Number;
  UPDATE Table2, minopen
  SET Table2.Open_Date = minopen.min_open_date
  WHERE Table2.Account_Number = minopen.Account_Number;
  DROP TABLE minopen
Michael
Wesley Furgiuele wrote:
I have a common update that I need to run that is a bottleneck in a
lot of the reports that are being requested. If anybody else has dealt
with a similar situation but with more success, any tips would be
appreciated.
Basically, I have a one-to-many relationship and I want to update
every record in the 'one' table with a MIN result from the 'many'
table.
Although I realize this doesn't work, the query I wish I could run is this:
UPDATE Table2, Table1 SET Table2.Open_Date = MIN( Table1.Open_Date )
WHERE Table2.Account_Number = Table1.Account_Number
Table1
Account_Number CHAR( 10 )
Asset_Number INT( 5 )
Open_Date DATE
Table2
Account_Number CHAR( 10 )
Open_Date DATE
Sample Data
Table1
Account Number AssetOpen Date
012345 000502003-01-01
012345 006012002-08-10
0001234567 006012002-06-15
9003004325 006012002-11-16
Table2
Account Number Open Date
012345 2002-08-10
0001234567 2002-06-15
9003004325 2002-11-16
What is going on is that Table2 is just a unique list of accounts and
I would like to update it with the earliest open date for any asset
owned by that account number in Table1. Currently, I am using PHP to
issue a set of queries and it can take 1-2 minutes to run through it.
First I get all the account numbers in Table2 (SELECT Account_Number
FROM Table2).
Then I loop through them, one at a time, getting the minimum Open_Date
from Table1 for each account number (SELECT Open_Date FROM Table1
WHERE Account_Number = $account_number ORDER BY Open_Date ASC LIMIT
1). Before moving to the next account number, I run another query to
update Table2 with the Open_Date acquired in the previous query
(UPDATE Table2 SET Open_Date = $open_date WHERE Account_Number =
$account_number).
Is there a better way to do this, either by having MySQL do all the
work, instead of using PHP to loop through? Or is there no way to
avoid looping?
Thanks again for any advice.
Wes
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Is it possible to have Undeletable Records?

2004-09-04 Thread Wesley Furgiuele

I don't think so. I think your current method of either storing it in
the query or with a boolean field is your best bet. What I've seen
done in the past is that records get marked with a user level and
there is either one or a group of users who are allowed to delete  or
modify those otherwise permanent records.

Wes


On Fri, 03 Sep 2004 11:38:35 +0100, zzapper <[EMAIL PROTECTED]> wrote:
> Hi Y'All
> 
> Is it possible to have undeletable/unmodifiable  records in a table of otherwise 
> modifiable &
> deleteable & createable records?
> 
> At present I do it at the Update/Delete level where I have clauses which prevent 
> certain records
> being changed. I suppose I could also have an extra boolean field "Record Read 
> only", but is there
> anyway to specify at the data level?
> 
> zzapper (vim, cygwin, wiki & zsh)
> --
> 
> vim -c ":%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?"
> 
> http://www.vim.org/tips/tip.php?tip_id=305  Best of Vim Tips

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

Updating a one-to-many with a MIN result from 'many' for each 'one' record?

2004-09-04 Thread Wesley Furgiuele

I have a common update that I need to run that is a bottleneck in a
lot of the reports that are being requested. If anybody else has dealt
with a similar situation but with more success, any tips would be
appreciated.

Basically, I have a one-to-many relationship and I want to update
every record in the 'one' table with a MIN result from the 'many'
table.

Although I realize this doesn't work, the query I wish I could run is this:
UPDATE Table2, Table1 SET Table2.Open_Date = MIN( Table1.Open_Date )
WHERE Table2.Account_Number = Table1.Account_Number

Table1
Account_Number CHAR( 10 )
Asset_Number INT( 5 )
Open_Date DATE

Table2
Account_Number CHAR( 10 )
Open_Date DATE

Sample Data
Table1
Account Number AssetOpen Date
012345 000502003-01-01
012345 006012002-08-10
0001234567 006012002-06-15
9003004325 006012002-11-16

Table2
Account Number Open Date
012345 2002-08-10
0001234567 2002-06-15
9003004325 2002-11-16

What is going on is that Table2 is just a unique list of accounts and
I would like to update it with the earliest open date for any asset
owned by that account number in Table1. Currently, I am using PHP to
issue a set of queries and it can take 1-2 minutes to run through it.

First I get all the account numbers in Table2 (SELECT Account_Number
FROM Table2).

Then I loop through them, one at a time, getting the minimum Open_Date
from Table1 for each account number (SELECT Open_Date FROM Table1
WHERE Account_Number = $account_number ORDER BY Open_Date ASC LIMIT
1). Before moving to the next account number, I run another query to
update Table2 with the Open_Date acquired in the previous query
(UPDATE Table2 SET Open_Date = $open_date WHERE Account_Number =
$account_number).

Is there a better way to do this, either by having MySQL do all the
work, instead of using PHP to loop through? Or is there no way to
avoid looping?

Thanks again for any advice.

Wes

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

SQL Error 1030 / Error 124 - Additional info

2004-09-04 Thread Helmut Leininger

Hi,
I get an SQL Error 1030 with error 124 (MySQL error:  124 = Wrong index
given to function) during the execution of the following SQL statement:
select t.Datum, t.kmStand,  t.kmStand - max(h.kmStand) as km, t.Betrag,
round(t.Menge,1) as Liter,
round(t.Menge * 100 / (t.kmStand - max(h.kmStand)), 1) as Verbrauch,
round(t.Betrag * 100 / (t.kmStand - max(h.kmStand)),2) as 'ct/km',
round(t.Betrag / t.Menge, 3) as 'EUR/Liter',
t.Kommentar from Treibstoff as t, Treibstoff as h
where h.kmStand < t.kmStand
group by 2
order by t.Datum, t.kmStand;

The table is the following:
+---+--+--+-++---+
| Field | Type | Null | Key | Default| Extra |
+---+--+--+-++---+
| Datum | date |  | | -00-00 |   |
| kmStand   | int(10) unsigned |  | PRI | 0  |   |
| Menge | float|  | | 0  |   |
| Betrag| decimal(9,2) |  | | 0.00   |   |
| Kommentar | varchar(150) | YES  | | NULL   |   |
+---+--+--+-++---+
What is wrong?
Additional Info:
If I change the comparison in the where clause, the SQL errors 
disappear. But, of course, the query delivers a (logically) wrong result.

Mysql is 5.0.1alpha-snapshot on Windows XP.
I do not remember this problem in 5.0.0alpha
Regards
Helmut

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

SQL Error 1030 / Error 124

2004-09-04 Thread Helmut Leininger

Hi,
I get an SQL Error 1030 with error 124 (MySQL error:  124 = Wrong index 
given to function) during the execution of the following SQL statement:

select t.Datum, t.kmStand,  t.kmStand - max(h.kmStand) as km, t.Betrag,
round(t.Menge,1) as Liter,
round(t.Menge * 100 / (t.kmStand - max(h.kmStand)), 1) as Verbrauch,
round(t.Betrag * 100 / (t.kmStand - max(h.kmStand)),2) as 'ct/km',
round(t.Betrag / t.Menge, 3) as 'EUR/Liter',
t.Kommentar from Treibstoff as t, Treibstoff as h
where h.kmStand < t.kmStand
group by 2
order by t.Datum, t.kmStand;
The reason seems to be the group by clause.
The table is the following:
+---+--+--+-++---+
| Field | Type | Null | Key | Default| Extra |
+---+--+--+-++---+
| Datum | date |  | | -00-00 |   |
| kmStand   | int(10) unsigned |  | PRI | 0  |   |
| Menge | float|  | | 0  |   |
| Betrag| decimal(9,2) |  | | 0.00   |   |
| Kommentar | varchar(150) | YES  | | NULL   |   |
+---+--+--+-++---+
What is wrong?
Mysql is 5.0.1alpha-snapshot on Windows XP.
I do not remember this problem in 5.0.0alpha
Regards
Helmut

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

RE: please explain why this query isn't optimized

2004-09-04 Thread Donny Simonton

The other simple solution is to do something like this.

Select changed FROM archived_stats order by changed DESC limit 0,1

I haven't actually tried it, it may be just as slow.

If you always know that you have dates in the changed for the past week or
past day, this may be an even better solution for you.

Select changed from archived_stats where changed > DATE_SUB(NOW(), INTERVAL
1 DAY) order by changed DESC limit 0,1

Hope this helps.

Donny

> -Original Message-
> From: Dave Dyer [mailto:[EMAIL PROTECTED]
> Sent: Saturday, September 04, 2004 2:58 AM
> To: Dan Nelson
> Cc: Donny Simonton; [EMAIL PROTECTED]
> Subject: Re: please explain why this query isn't optimized
> 
> 
> >
> >> Getting the same answer, from a simpler query, in infinitely
> >> less time, just seems wrong to me.
> >
> >Makes perfect sense.  Simpler queries *are* easier to optimize, you
> >know :)
> 
> 
> Makes perfect sense. Thanks, I think the relevant points
> have been covered.
> 




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

Re: Problem on InnoDB - Tablespace enough but engine said table full

2004-09-04 Thread Ady Wicaksono

Heiki
 
Thx... it's work :D
 
Sorry, one liner

Heikki Tuuri <[EMAIL PROTECTED]> wrote:
Ady,

- Alkuperäinen viesti - 
Lähettäjä: "Ady Wicaksono" 
Vastaanottaja: "Ady Wicaksono" ; "Heikki Tuuri"

Kopio: 
Lähetetty: Friday, September 03, 2004 10:41 AM
Aihe: Re: Problem on InnoDB - Tablespace enough but engine said table full


> I try to detect using MC (Midnight Commander) and found that after
;/data4/ibdata25:1802M
>
> it won't write anymore...
>
> I remove these data file and add /ibdata1/ibdata10:1500M and
/data1/ibdata11:1500M
>
> I believe, All data below is empty but corrupt :(
>
>  data file defintion --
> #/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;
>
#/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ib
data14:1500M;/data2/ibdata15:1500M;/data2/ibdata16
>
:1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/da
ta3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ib
> data22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M;
> --- data file defintion --
>
> I change my innodb_data_file_path to:
>
> innodb_data_file_path =
/data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15
00M;/data0/ibdata5:1500M;/
>
data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9
:1500M;/data4/ibdata25:1802M;/data1/ibdata10:1500M
> ;/data1/ibdata11:1500M
>
> It's working since i know i have 877184 pages now, later i simply add
something like /data1/ibdata12:1500M
>
> but, i still have error when starting, here is
>
> InnoDB: Error: tablespace size stored in header is 877184 pages, but
> InnoDB: the sum of data file sizes is 953856 pages
>
> How to fix it ? since i found every data in innodb_data_file_path is not
empty

(953856 - 877184) / 64 = 1198 MB

1) Stop the mysqld server.
2) Add a new 1198M ibdata file at the end of innodb_data_file_path.
3) When you start mysqld, InnoDB will write that new ibdata file full of
zeros, and increment the tablespace size stored in the tablespace header by
1198M.
4) Then stop the mysqld server, remove the extra 1198M ibdata file from
innodb_data_file_path and delete that extra ibdata file.
5) Start mysqld again.
6) Voila! Now the tablespace size stored in the tablespace header agrees
with the combined size of the files in innodb_data_file_path!

Be very careful! From now on always follow the instructions at
http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html
when you want to add a new ibdata file.

> Thx

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php


-
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.

Re: HELP ME WITH THIS

2004-09-04 Thread Karma Dorji

Dear Dobromir Velev
and members of list,
Thank you so much for the help, yes the sql query works perfectly, though i
did add some, as to cancel the duplicates ones, and also to not to take into
negative timings,
please advice on this too,

select SEC_TO_TIME(unix_timestamp(concat(e.Date,' ',e.Time)) -
unix_timestamp(concat(s.Date,' ',s.Time))) as time, s.CallingStationId,
s.CalledStationId, s.Date, s.Time, s.AcctStatusType, e.Date,
e.Time, e.AcctStatusType
from VOIP s left join VOIP e on
( s.CallingStationId=e.CallingStationId and
s.CalledStationId=e.CalledStationId and e.AcctStatusType='Stop' )
where s.AcctStatusType='Start' and s.Time < e.Time and s.Date <= e.Date
group by s.Time,e.CallingStationId
order by s.date,s.time;

Thanks,
Karma


- Original Message - 
From: "Dobromir Velev" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, September 03, 2004 6:48 PM
Subject: Re: HELP ME WITH THIS


> Hi,
> The only way I can think of is to join the table to itself. It should look
> something like this:
>
> select unix_timestamp(concat(s.date,' ',s.time)) -
> unix_timestamp(concat(e.date,' ',e.time))
> from table s left join table e on
> ( s.CallingStationId=e.CallingStationId and
> s.CalledStationId=e.CalledStationId and e.AcctStatusType='Stop' )
> where s.AcctStatusType='Start'
> order by s.date,s.time;
>
> I haven't tested it so you will  probably need to play a bit with the
> date/time formating but it should be enough to give you the idea. It will
only
> work if there is only one session for each CallingStationId and
> CalledStationId pair, otherwise you will receive a lot of irrelevant
results.
>
>
> -- 
>
> Dobromir Velev
>
> On Friday 03 September 2004 15:21, Peter J Milanese wrote:
> > If it were all in one row, you may be able to compare datetime fields.
> >
> > I do not know if you can do this with 2 rows, and the query will
probably
> > be rough.
> >
> > Did you design the table? Can you create it so that your row has start
and
> > stop times, instead of creating another row?
> >
> > > -Original Message-
> > > From: Karma Dorji [mailto:[EMAIL PROTECTED]
> > > Sent: Friday, September 03, 2004 5:06 AM
> > > To: [EMAIL PROTECTED]
> > > Subject: HELP ME WITH THIS
> > >
> > > Hello can anyone help me with this,
> > >
> > > i have a table, like the one below,
> > > i need to find the time difference between the Start and Stop from a
> > > particular CallingStationId to particular CalledStationId.
> >
> >
++--++---+
> >
> > > --
> > > +---+
> > >
> > > | Date   | Time | CallingStationId   | CalledStationId   |
> > >
> > > AcctStatusType   | AcctSessionTime   |
> >
> >
++--++---+
> >
> > > --
> > > +---+
> > >
> > > | 09/01/2004 | 17:28:27 | 02327125   | 00018151635   |
Start
> > > |
> > > | 09/01/2004 | 19:00:34 | 02320176   | 01181471822125|
Start
> > > |
> > > | 09/01/2004 | 19:10:08 | 17113080   | 01022586815   |
Start
> > >
> > >  09/01/2004 | 20:28:27 | 02327125   | 00018151635   | Sop
> > >
> > > | 09/01/2004 | 21:00:34 | 02320176   | 01181471822125|
Stop
> > > |
> > > | 09/01/2004 | 22:10:08 | 17113080   | 01022586815   |
Stop
> > >
> > > Thanking you all in advance.
> > >
> > > Karma
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>


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

Re: please explain why this query isn't optimized

2004-09-04 Thread Dave Dyer


>
>> Getting the same answer, from a simpler query, in infinitely
>> less time, just seems wrong to me.
>
>Makes perfect sense.  Simpler queries *are* easier to optimize, you
>know :) 


Makes perfect sense. Thanks, I think the relevant points
have been covered.


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

37 matches

Mail list logo