Re: Translating Data Into NULL

[Andy Bakun]

On Sat, 2005-02-19 at 23:55, David Blomstrom wrote:

> > Why not set columns to NOT NULL and default to space
> > for character and 0 
> > for numeric?
> 
> OK, you're suggesting I...
> 
> 1. Set all the columns to NOT NULL
> 2. Set 0 as the default for numeric fields
> 3. Set "space" for the default in the other fields?

Using a single space as the default for a column won't work because
mysql trims (removes trailing whitespace) from inserted values.

Doing something like this sounds like a really bad work around.  The
data you want should be stored and appear in the right columns.


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

Re: MIN(foo) as bar WHERE bar>50

[Andy Bakun]

On Fri, 2004-09-24 at 21:31, Laszlo Thoth wrote:
> Here's the kids:
> 
> mysql> SELECT p.name as parent,c.name as
> child,(TO_DAYS(NOW())-TO_DAYS(c.dob))/365 as age FROM people as p LEFT JOIN
> people as c ON p.name=c.parent WHERE c.name IS NOT NULL ORDER BY p.dob;

You seem to be missing a parent child relationship from your results for
some reason.  When I run your queries, I also get a 141 year old child:

| James Smith | Samuel P  | 141.07 |

> mysql> SELECT p.name as parent,c.name as
> child,MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) as minage FROM people as p LEFT
> JOIN people as c ON p.name=c.parent WHERE c.name IS NOT NULL AND minage > 50
> GROUP BY parent ORDER BY p.dob;
> ERROR 1054: Unknown column 'minage' in 'where clause'
> 
> Unfortunately it doesn't look like I can SELECT on the MIN() result: I can only
> specify which rows go into the MIN().  How can I perform this select?

Look up the HAVING clause in the mysql manual.  I believe HAVING is not
standard SQL, and is a MySQL specific addition. 

   select p.name as parent,
  c.name as child,
  MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) as minage
 from people as p left join 
  people as c on p.name = c.parent
where c.name is not null
group by parent
-> having minage > 50
order by p.dob;

I personally try to avoid using HAVING on queries where the WHERE clause
would return a lot of rows because it needs to look at every row to see
if it satisfies the HAVING condition and this can be extremely
inefficient.  But it's useful and sometimes you can't avoid using it. 
Your query could also be extremely inefficient because you need to apply
the formula to calculate minage to all rows.

HAVING is essentially like making a temporary table and then selecting
from that temporary table.  I could be considered a stop-gap method of
simple subqueries to tide us over until MySQL supports sub-queries
(sometime in the 5.x timeframe, I believe).

A more efficient query might be something like this pseudo-code:

   all rows where max(dob) < date_sub(now(), interval 50 years)

which should give you similar results... the query is going to look
something like this:

  select p.name as parent,
 max(c.dob) as dob_of_youngest_child
from people p left join
 people c on p.name = c.parent
   where c.name is not null
   group by parent
  having dob_of_youngest_child < date_sub(now(), interval 50 year)

This may end up being more efficient because the values being applied to
max() are constant values (from the table, and are not the result of a
formula) and "date_sub(now(), interval 50 year)" is also a constant
(calculated at query parse time and not for every row, and optimized
away).

Use the EXPLAIN syntax to check for efficiency.



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

Re: The UNION makes us strong^H^H^H^Hcrazy

[Andy Bakun]

On Fri, 2004-09-10 at 14:12, [EMAIL PROTECTED] wrote:

> According to the docs, this should work in versions past mySQL 4, and I seem
> to be running a version rather later than that
> 
> mysql  Ver 11.18 Distrib 3.23.52, for pc-linux (i686)
> 
> What am I doing wrong here? I have two valid SELECT statements; the field
> sizes and types are the same (indeed, empssn is the same field).  This
> SHOULD provide me with what I'm looking for, but...

The command:
  mysql --version
shows the version of the mysql client program.  You are running version
11.18 of the mysql client program from the MySQL 3.23.52 distribution,
or what is commonly called "MySQL 3".

The syntax allowed in queries is dependent on the version of the mysqld
(and other) binaries that the SERVER users.  I suspect you are running
mysqld from the MySQL 3.23.52 distribution also.  This a version that is
earlier than MySQL 4, and doesn't support UNIONs.


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

Re: How to select field names?

[Andy Bakun]

On Tue, 2004-09-07 at 12:38, Jeremy McEntire wrote:
> Clarification.
> 
> I'm using modular arithmetic on a table of recently viewed items.  My fields
> are:
> 
>   user_id, pid0, pid1, pid2, pid3, pid4, inc
> 
> user_id is the user's unique identification.
> pid* is the product's unique identification.
> inc is a number, modulo 5, corresponding to
> the last pid column updated for this user.

You are not normalizing your data properly.  What happens when you want
to keep track of an additional product?  Will you alter the table
definition to add a new column?  A lot of code will need to be revisited
to ensure that you are taking the correct modulus of the inc column to
get the correct data in that case.  It is not hard to do what you want
to accomplish, but because your table is not normalized, the queries are
either impossibly hard or hardly possible.

You should design your table with the following three columns:

   userid, productname, lastviewed

Where each user could have multiple rows, like so:

'me',  'pid0', '2004-04-01'
'me',  'pid1', '2004-04-02'
'you', 'pid1', '2004-05-15'
'you', 'pid0', '2004-06-01'
'me',  'pid2', '2004-06-10'
'you', 'pid5', '2004-07-01'

If you only want to keep track of the five most recent, then only ever
select the five most recent:

select * from table where userid = 'me' order by lastviewed desc limit 5

(cleaning out old entries is beyond the scope of this message, but you
should be able to figure it out).

When someone views a product, say I view pid6, issue an insert statement
to record that they viewed it:

insert into table 
(userid, productname, lastviewed) 
values 
('me', 'pid6', now())

If you set up your primary keys correctly, then you can do a REPLACE
INTO statement rather than an INSERT so if they look at the same product
again the lastviewed column will get updated (so the last five product
views will always be unique products, rather than finding out that the
last five products views were the same product viewed five times).

> So, when the user visits a page, I want to verify that they
> don't currently have this product in their "recently viewed"
> list.

Using my sample table above:

select count(1) from table where userid = 'me' and productname = 'pid4'

will return 0 (since I have not viewed pid4).  I've viewed five products
other than pid4 more recently than I've viewed pid4 (or I have not
viewed pid4 at all).  If you DON'T clean out the table, you can keep a
running list of all the products viewed and when the user viewed them.

> To do so, I'd like to know if the product id is in any of pid0, pid1, pid2,
> pid3, or pid4.  To accomplish this, I could use a simple OR statement.  But,
> the information I really want is in which column that product id appears.
> Using PHP, I can simply grab the key from the array returned by the query.
> I was hoping MySQL offered this functionality; evidently, it does not.

I serious hope no one adds this "functionality" to MySQL, because it
would only serve to encourage questionable database design.

-- 
Andy Bakun <[EMAIL PROTECTED]>


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

Re: Writing to an MySQL Database

[Andy Bakun]

On Fri, 2004-07-02 at 09:05, Michael Mason wrote:
> It’s the “Data Capture section that’s causing issues. I keep getting
> an error telling me there’s an “Unexpected $” on a line that’s
> actually outside even the HTML tag.

Errors like "unexpected $" that are output by a code or statement parser
are sometimes related to the parser running off the end of its input. 
"$" is tokening parser parlance for "end of input/file".  So "unexpected
$" really means "unexpectedly reached the end of the file".  As others
have pointed out, this is because you were missing a quote that closes a
string.

-- 
Andy Bakun: a killer headache 
<[EMAIL PROTECTED]>


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

Re: *very* strange...

[Andy Bakun]

On Sat, 2004-03-20 at 01:03, Chris Knipe wrote:
> Lo everyone,
> 
> I'm *baffled* completely I've never seen something like this before.
> :  I tried this exact query from PHP, Perl, as well as the MySQL
> thingy... They ALL give the same result - it must therefore be my table
> ...
> mysql> INSERT INTO Accounts (Username, Password) VALUES
> (Username='[EMAIL PROTECTED]', Password='password');
> Query OK, 1 row affected (0.00 sec)
> 
> mysql> SELECT * FROM Accounts WHERE Username='[EMAIL PROTECTED]';
> Empty set (0.00 sec)
> 
> mysql>
> 
> HOWEVER
> 
> mysql> SELECT * FROM Accounts WHERE Username='0';
> +-+--+--+--+--+
> | EntryID | Username | Password | isActive | isCapped |
> +-+--+--+--+--+
> |  48 | 0| 0| y| n|
> +-+--+--+--+--+
> 1 row in set (0.00 sec)
> 
> There's my entry.  It's the row that was inserted, because I can see from
> the EntryID (Auto Increment).
> 
> Now, WTF is wrong here...   Nevermind what the value is I send to Username /
> Password, Mysql *ALWAYS* replaces the data specified with a 0.  Needless to
> say, due to the UNIQUE keys, I dont ever get the data into my table :(((

You are using the wrong syntax for what you want to do.  What you want
is this:

insert into table (username, password) values ('username', 'password')

Skip out the "username=" and "password=" part.  What you are ending up
doing here is that the values portion of the insert statement, these two
parts:
   Username='[EMAIL PROTECTED]'
   Password='password'
Are being evaluated as comparison expressions.  Since the string
'password' does not equal the value of the column Password (although
this seems like an odd syntax to allow without a where clause), the
value of these two columns is zero.  Simplified, your query ends up
being (after the expressions are evaluated):

insert into table (username, password) values (0, 0)

because both of the expressions you put in the values evaluate to 0.

I hope this is clear enough.

-- 
Andy Bakun <[EMAIL PROTECTED]>


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

Re: Which is MySQL optimised for - BETWEEN or AND?

[Andy Bakun]

On Thu, 2004-02-19 at 08:24, Alex Greg wrote:

> I have a select query which is selecting all records in a table (which has
> around 8,000,000 rows in). "time" is a field of type "time". Should I be using
> <= and >= or BETWEEN to find records in a certain range? Which does MySQL
> optimise for?

I can not speak to specifics in the code, but I have 150 million row
InnoDB table that has an int field that stores a unix_timestamp() and
I've gotten slightly better performance using BETWEEN rather than AND,
but this is largely anecdotal, as I don't have hard numbers.  Your best
bet would be to test performance yourself.  Be sure you are using an
index on that column in your query (verify that with explain) -- using
an index will make the most difference, of course.

-- 
Andy Bakun: get used to it 
<[EMAIL PROTECTED]>


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

Re: mysqld without LinuxThreads

[Andy Bakun]

On Fri, 2004-01-09 at 05:14, Chris Nolan wrote:
> On Fri, 2004-01-09 at 20:58, Andy Bakun wrote:
> > On Thu, 2004-01-08 at 05:24, Chris Nolan wrote:
> > 
> > > 3. Wait for a while. Linux 2.6 includes (as does the RedHat 9.0 and
> > > ES/WS/AS 3.0 kernels) NPTL - the Native POSIX Threads for Linux
> > > implementation which is superior in many ways and does not use clone()
> > > at all. As you'll no longer want to use clone() for threaded apps (trust
> > > me on this), I'm betting that all interested parties will start
> > > complaining in the relevant mailing lists.
> > 
> > Chris, do you have some references for this?  I'd love to read up on it
> > to find out what's coming.  All references to NPTL I can find via google
> > as it relates to changes in 2.6 talk about how 2.6 has additional
> > clone() options that NPTL takes advantage of (most of them reference a
> > text that is available at http://kerneltrap.org/node/view/422).  Also,
> > why are you down on clone() -- I trust you, but I want to learn! :)
> > 
> My references come from the LKML and KernelTrap! :-) I'm a bit of a geek
> in this respect, and quite enjoy reading up on OS internals. If you have
> a look at Ulrich Drepper's home page (somewhere on Redhat's servers)
> he's got some info about the beast as he wrote NPTL. Admittedly, it
> almost seems as if he wrote it in response to NGPT (Next Generation
> POSIX Threads - a competing but now unsupported thread implementation
> for kernel 2.6 inclusion).
 ... (good stuff deleted for brevity)...
> Everyone, please pull me up on anything I've said here that sounds like
> tripe!

Thanks!  The only complaint I have with your explanation is that NPTL,
as outlined in a PDF I found by Ulrich Drepper about the implementation
of NPTL (and which was most likely the original source of the
information contained in the kerneltrap link I provided), does, in fact,
use clone(), but requires additional flags that are more
POSIX-standard-thread-oriented rather than process-oriented.  If this is
the same as "does not use clone() at all" is left as an exercise for the
reader. :)

Of course, clone(2) was created a way long time ago (pre 2.0 AFAIK, but
I'm having trouble finding references to that, or Linus's rant about the
creation of clone on lkml, which I know I've read, but can't find right
now) as a more generalized, more-options-for-the-programmer interface to
forking, with some thought-ahead to stuff that could be used for
threads.  Hind sight is 20/20, though, and there are obvious
deficiencies when it comes to threads.  Obviously, as time advances and
libraries are written, it only makes sense that's it's being more
refined to bring it up with better, modern usage.

-- 
Andy Bakun <[EMAIL PROTECTED]>


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

Re: mysqld without LinuxThreads

[Andy Bakun]

On Thu, 2004-01-08 at 05:24, Chris Nolan wrote:

> 3. Wait for a while. Linux 2.6 includes (as does the RedHat 9.0 and
> ES/WS/AS 3.0 kernels) NPTL - the Native POSIX Threads for Linux
> implementation which is superior in many ways and does not use clone()
> at all. As you'll no longer want to use clone() for threaded apps (trust
> me on this), I'm betting that all interested parties will start
> complaining in the relevant mailing lists.

Chris, do you have some references for this?  I'd love to read up on it
to find out what's coming.  All references to NPTL I can find via google
as it relates to changes in 2.6 talk about how 2.6 has additional
clone() options that NPTL takes advantage of (most of them reference a
text that is available at http://kerneltrap.org/node/view/422).  Also,
why are you down on clone() -- I trust you, but I want to learn! :)

-- 
Andy Bakun <[EMAIL PROTECTED]>


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

Re: mysqld without LinuxThreads

[Andy Bakun]

On Wed, 2004-01-07 at 10:38, [EMAIL PROTECTED] wrote:
> Hi all,
> does anyone know if it's possible to compile MySQL under Linux so that
> mysqld doesn't rely upon LinuxThreads, but makes direct call to fork()
> or clone() system calls instead?

As far as I know and can tell from reading docs, Linux's pthread
implementation DOES use clone to create threads.  In most cases, pthread
is a wrapper around whatever threading services the host system
provides.

> I'm looking for a way to avoid the exploitation of pthreads under
> Linux.

Why wouldn't you want to use an industry standard threading model?

-- 
Andy Bakun <[EMAIL PROTECTED]>


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

Re: How to use API to write blobs

[Andy Bakun]

On Fri, 2003-12-26 at 19:26, Angus March wrote:
> I'm trying to use the API to write to a MEDIUMBLOB, but I'm getting a very
> vague error: something about a problem "near '' on line 1". I'm forumating
> the query string w/
> 
> sprintf(query, "INSERT INTO support_files (session_id,file_type,file_body)
> VALUES (%ld,%ld,", sessionID,fileType);
> 
> Then w/calls to things like memcpy, and unformatted reads from a stream, I
> append the blob to the end of that, and finalize it w/a ')'. I'm very
> careful about what I do w/my pointers, and not to use string-handling
> functions on the blob data. I also use mysql_real_query(), which is where
> the error is returned. From what I can tell from the on-line documentation,
> this is the right way to handle blobs. I also tried:
> 
> sprintf(query, "INSERT INTO support_files (session_id,file_type,file_body)
> VALUES (%ld,%ld,0)", sessionID,fileType); //exact same as before, but the
> blob is a mere '0'
> 
> Is this right? I can use a char * to point to binary data, can't I?
> I'm running Linux RH9, if that makes a difference.

If you are using MySQL 4.1, it would be a lot easier to use "Prepared
SQL statements" as outlined in section 11.1.4 of the manual, which
allows you to pass variable parts of queries in a length-specified
binary format, rather than requiring that all the data in the query be
escaped for MySQL.  

Short of using 4.1, you could do something like you've done above with
sprintf but, but with the whole query.  I find it a little easier to be
sure that the whole query is valid by having the entire query in a
single string rather than trying to build the query by appending
successive parts.  In this case, you can also test that the query works
with data that doesn't need to be escaped without butchering your code
(you can just change the format string in the snprintf to some literal
data to verify that it works.

 -->8-pseudo-code--8<
char *blob_data = "some big buffer of blob data";
unsigned long blob_data_length = you-should-know-this-value;
blob_data_escaped = (char *) malloc(blob_data_length * 2+1);
escaped_length = mysql_real_escape_string(
   (MYSQL *) mysql, 
   (char *) blob_data_escaped, 
   (const char *) blob_data, 
   (unsigned long) blob_data_length);
/*
  blob_data_escaped[escaped_length] should be the only null byte
  in blob_data_escaped now, so the snprintf below should work just
  fine
 */

query_length = some-huge-amount-that-can-hold-
   escaped_length-plus-the-rest-of-the-query;
query = (char *)malloc(query_length);

snprintf(query, 
query_length, 
"insert into s (i,f,b) values (%d, %f, '%s')",
some_integer,
some_float,
blob_data);
 -->8-pseudo-code--8<

See the query string?  Other than the fact it contains the printf escape
sequences, it's a syntacticly correct query, closing parens and all. 
You could change that to:

snprintf(query, 
query_length, 
"insert into s (i,f,b) values (%d, %f, '%s')",
1,
2.0,
"blob\\'test\\0has embedded null byte");

to make sure the query is syntacticly correct.  If you print out the
query (so you know what it is), then try pasting it into the mysql
client, you should be able to run it without problems (as a way to test
where there are problems with it).  The C library functions that print
stuff out are bound by the same string-interpretation limits that the
MySQL library is, so if your query comes up short, or syntacticly
incorrect due to those limitations, you'll see it.

You could also abstract this out the pseudo-code I've provided above to
a function that formats a query for you if you give it all the data
values and the lengths of the input buffers and whatnot (thereby making
it work somewhat more like the prepared statement support in 4.1).

(BTW, you should get in the habit of using snprintf rather than sprintf,
if your platform supports it, in order to avoid buffer overruns).

-- 
Andy Bakun <[EMAIL PROTECTED]>


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

RE: DB not restoring from dump file

[Andy Bakun]

On Wed, 2003-10-29 at 14:18, Chris W. Parker wrote:
> Matt W <mailto:[EMAIL PROTECTED]>
> on Monday, October 27, 2003 5:29 PM said:
> 
> > As to why mysqldump would "create a dump file with a syntax error in
> > it," that's because *you* (or the application creator) used a reserved
> > word for a column/index name (bad idea) and mysqldump
> 
> I always try to be careful about this and I even compared all the column
> names in my db to the reserved word list on the MySQL site and didn't
> find any matches.

It may be best to always use the --quote-names option to mysqldump,
which would avoid any problems you might encounter with reserved words
being used in column and table names.

-- 
Andy Bakun: when uselessness just isn't enough 
<[EMAIL PROTECTED]>


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

Re: subtraction on datetime fields

[Andy Bakun]

On Wed, 2002-12-11 at 04:44, Tom Roos wrote:
> hi
> 
> i want to build a query in which i have 2 datetime fields which i subtract 
> from one another. what is the result set? is it in (milli)seconds, is it a 
> unix timestamp? what type of convertion do i have to apply to report the 
> difference in minutes?

I usually convert to unix time (seconds since 1970) then subtract.  Then
use sec_to_time to get hours:mins:sec, or convert to a displayable value
in the client software.

select unix_timestamp('2002-12-02 15:30:00') -
   unix_timestamp('2002-11-15 21:11:08') as tdiff;

+-+
| tdiff   |
+-+
| 1448332 |
+-+

select sec_to_time(unix_timestamp('2002-12-02 15:30:00') -
   unix_timestamp('2002-11-15 21:11:08')) as tdiff;

+---+
| tdiff |
+---+
| 402:18:52 |
+---+

Or to get minutes, rather than using sec_to_time, divide by 60.

You can't just subtract the datetime values from each other because
they'll be converted to integers (that is, '2002-12-02 15:30:00' becomes
20021202153000) and then subtraction doesn't work as each place in the
resultant integer doesn't fully hold all digits 0 through 9.
Fortunately, date comparsion (using > and <) still work because the date
is arranged with the most signifcant portion being further left.

Andy.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Can I get the matching expression from REGEXP

[Andy Bakun]

Look up SUBSTRING_INDEX in the mysql manual.

select SUBSTRING_INDEX(colX,' ',-1) from table

is what I think you want.  This will return everything after the first
space found.  May want to stick an if(...) construct in there for where
you don't want the rows without the spaces (see below).

Then something like:

update table set colY = SUBSTRING_INDEX(colX,' ',-1);

To satisfy your condition of the first two rows only (in your example):

select if(substring_index(colX, ' ', -1)) = colX,
NULL, 
substring_index(colX, ' ', -1)) as colY
from xx1 having colY is not NULL;

and for the update:

update table set colY = substring_index(colX, ' ', -1)
  where substring_index(colX, ' ', -1)) <> colX;

Andy.

On Tue, 2002-12-10 at 17:40, Mike Bosschaert wrote:
> Hi,
> In one of my tables I have a column which contains a combination of a string 
> (characters only), a space and a 1 or 2 letter combination. The string has no 
> fixed lenght. Like:
> 
>abcd ef
>bcdefgh i
>etc
> 
> Now I want to remove the 1 or 2 letter combination from this column into 
> another column. I can select the rows with 
> 
> WHERE colX REGEXP ". .{1,2}$"
> 
> This wil return the first two rows only. But I cannot figure out how to get 
> the query to return the matching result (being ef and i).
> 
> Any help appreciated



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Select * From table where name Like 'help'; Help

[Andy Bakun]

On Mon, 2002-12-09 at 16:10, Beauford.2003 wrote:
> Andy,
> 
> I mentioned in my previous email that I am using PHP, and I have also tried
> putting quotes around $var (many different ways) with no better results.
> REGEXP just gives a syntax error when I do this.

I'm sorry, I did miss the where you said you were using PHP. Just found
it.  But I was responding to:

   The real value of $var is not inserted before it is sent to the
   server.  REGEXP thinks that 'var' is part of the search pattern
   because $ is a reserved operator for REGEXP.

This is most definitely a quoting issue with your PHP string.  If it is
quoted correctly, the MySQL server will never see the string $var
appearing after the keyword REGEXP, so if $ has meaning to a regular
expression wouldn't matter (unless the value of $var contained a $). 
I've used REGEXP with (really complex) dynamicly built regular
expressions stored in PHP variables a number of times and have never had
a problem as you've described above.

> Through the suggestion of another list user I have found a way that suits my
> needs using the "where name like binary '$var'". It's jury rigged, but works
> the way I need it to work for the most part.

Don't give up now!  A perfect solution can be found! :)

Andy


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Select * From table where name Like 'help'; Help

[Andy Bakun]

You still have not mentioned what language you are using to interface
with MySQL.  If you are using PHP or Perl, then things like single and
double quotes will make a difference here, and looking in the MySQL
manual will not help you.  Check the string that contains the query, if
it looks like this:

   $q = 'select  from table where col regexp "$var"';

change it to

   $q = "select  from table where col regexp '$var'";

The enclosing single quotes are keeping the value of $var from being
interpolated into the string in the above example.  See the manual for
your programming language for more information on variable
interpolation.

On Mon, 2002-12-09 at 08:22, Beauford.2003 wrote:
> I am using PHP on my website, but this is certainly a MySQL question.
> 
> > That shouldn't matter, because the real value $var is inserted before
> > the query is send to the MySQL server, where REGEXP of the query is
> > evaluated. If there is a problem, you need to be much more
> > specific. Quote the error message, show the relevant part of your code
> > and so on.
> 
> The real value of $var is not inserted before it is sent to the server.
> REGEXP thinks that 'var' is part of the search pattern because $ is a
> reserved operator for REGEXP. So what gets sent to the server is totally
> different than what I want to be sent. There are no errors because the
> syntax is correct, its just not correct for the search I want it to perform.
> So the questions still remains, how do I get REGEXP to treat $var as a
> variable and not part of its own syntax.
> 
> I have been looking at http://www.mysql.com/doc/en/Regexp.html, but it does
> not cover this.
> 
> - Original Message -
> From: "Benjamin Pflugmann" <[EMAIL PROTECTED]>
> To: "Beauford.2003" <[EMAIL PROTECTED]>
> Cc: "Robert Citek" <[EMAIL PROTECTED]>; "MySQL List"
> <[EMAIL PROTECTED]>
> Sent: Monday, December 09, 2002 3:30 AM
> Subject: Re: Select * From table where name Like 'help'; Help
> 
> 
> > Hello.
> >
> > On Mon 2002-12-09 at 01:00:33 -0500, [EMAIL PROTECTED] wrote:
> > > I am doing this search from a website,
> >
> > What does that mean? Which scripting language do you use? PHP? This is
> > more a question for your "website" environment than about MySQL.
> >
> > > and from what I can tell there is no way to use a variable
> > > (i.e. $var) with REGEXP as the $ sign has a special meaning to
> > > REGEXP.
> >
> > That shouldn't matter, because the real value $var is inserted before
> > the query is send to the MySQL server, where REGEXP of the query is
> > evaluated. If there is a problem, you need to be much more
> > specific. Quote the error message, show the relevant part of your code
> > and so on.
> >
> > HTH,
> >
> > Benjamin.
> >
> > --
> > [EMAIL PROTECTED]
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

replication, multiple masters

[Andy Bakun]

I have successfully configured two mysql instances to replicate to each
other (According to /doc/en/Replication_Features.html, it is possible to
do it in a A->B->C->A relationship, but I only did it with two servers
and I don't have log-slave-updates on (I think if I did, it would
immediately stop the slave thread as the updates get caught in a loop).

FYI, I'm using a self-compiled RPM of 3.23.51 on Linux 2.4.  All my
tables are InnoDB.

It's very slick, updates on either server get propagated to the other
server.  I have not stress tested it yet, and my (simple) application
only does updates to a single server at a time.

The only problem is the auto_increment columns in the tables.  Updates
that occur on both machines at the same time, that generate the same
auto_increment value, causes the slave threads to die: and rightly so. 
I can, of course, program my application to generate non-conflicting,
server independant key values without the need for the auto_increment,
but has anyone had any experience with this?  Is this the only
impediment to doing full two-way replication?  What other 'data
corruption' or 'out of sync' issues are there to worry about?

Andy.
[EMAIL PROTECTED]




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

replication, multiple masters

[Andy Bakun]

I have successfully configured two mysql instances to replicate to each
other (According to /doc/en/Replication_Features.html, it is possible to
do it in a A->B->C->A relationship, but I only did it with two servers
and I don't have log-slave-updates on (I think if I did, it would
immediately stop the slave thread as the updates get caught in a loop).

FYI, I'm using a self-compiled RPM of 3.23.51 on Linux 2.4.  All my
tables are InnoDB.

It's very slick, updates on either server get propagated to the other
server.  I have not stress tested it yet, and my (simple) application
only does updates to a single server at a time.

The only problem is the auto_increment columns in the tables.  Updates
that occur on both machines at the same time, that generate the same
auto_increment value, causes the slave threads to die: and rightly so. 
I can, of course, program my application to generate non-conflicting,
server independant key values without the need for the auto_increment,
but has anyone had any experience with this?  Is this really the only
impediment to doing full two-way replication?

Andy.
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Incorrect DATE_FORMAT output for weeks with year e.g. '%v-%y'

[Andy Bakun]

I don't know about "incorrect", but confusing, sure.  It is easy to
predict what is going to be returned based on the documentation.

On Thu, 2002-11-21 at 14:19, Joe Siegrist wrote:

> I don't agree that mysql is 'right' here though, I realize that if you
> simply strip out the year for the date it would be '01',  but if you wrap
> the week number, you shouldn't you increment the year as well? SELECT
> DATE_FORMAT('2001-12-31', '%v-%y'); returning '01-01' is inaccurate to say
> the least.

mysql> select DATE_FORMAT('2001-12-31', '%v-%y');
+---+
| DATE_FORMAT('2001-12-31', '%Y%v') |
+---+
| 200101|
+---+

In the date_format query given above, the %v (01) is correct, as
2001-12-31 is in the first week of 2002.  But the %v doesn't report that
it's giving the week for 2002, it only reports the week.  Compare to:

mysql> select yearweek('2001-12-31');
++
| yearweek('2001-12-31') |
++
| 200201 |
++

date_format's %v (and the week() function) prints the week of the year
as extracted from the date, which may fall in the next year. 
date_format's %Y prints the year extracted from the date, which, for all
dates in 2001, is 2001.  yearweek() (and %X%v, see below) takes both the
year and the date into account when calcuating the value to return, and
date_format's %Y doesn't: each substitution is done independantly of the
other substitutions, ie

  set @d = '2001-12-31';
  select date_format(@d, '%Y%v');

is equivalent to

  select concat(date_format(@d, '%Y'),date_format(@d, '%v'));

but neither of them are necessarily equivalent to

  select yearweek(@d);

but that's equivalent to

  select date_format(@d, '%X%v');


> %x and %X have the same problem as %v (since they use it).

I don't know if it's actually a "problem", note in docs under
yearweek():

Note that the week number is different from what the WEEK()
function would return (0) as WEEK() returns the week in the
context of the given year. 

... that is, the year in the date given to week()/%u (which is why week
can return 53), that is.

The difference between %u and %v is the difference between using
yearweek() and week() with the optional second argument to specify if
the week starts on sunday or monday.

Andy.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: performance tuning (generating summary tables)

[Andy Bakun]

On Thu, 2002-11-21 at 09:47, Johannes Ullrich wrote:

> And more difficult, try to look at your application design and try
> to come up with 'cache tables' that are generated by a cron job
> periodically and are used for most queries.

This is an excellent suggestion and may make you think about your
application in a different way.  But beware -- sometimes the code that
is used live doesn't translate well to generating
cache/precalculation/summary tables.

We have a directed graph stored in the database as a list of vertexes. 
To generate a summary table showing all the paths through the graph, I
used code directly from our front end system (most of the front end
system only needs to move over one branch of the graph).  This didn't
scale well to having to traverse the entire graph.  With the summary
table, iteratively traversing the graph wouldn't be necessary anyway.

It was taking over 1,200,000 queries to traverse the graph using SQL
iteratively/recursively, which was pushing the load on the machine to
over 3 and was taking 45 minutes to run, even though all the tables were
indexed, 'explain' reported no full table scans or file sorts, and none
of the individual queries appeared in the slow query log. (the queries
were simple, no complex joins, of the nature 'select sub from graph
where super = ').

The solution was to use perl to read the tables (and thus the graph)
into perl's memory (as perl data structures: arrays of references to
arrays to references to arrays, etc) and traverse the graph in perl
directly.  Then perl generates the data for the new summary table. 
Doing it this way cut down the run time to 18 seconds!  The overhead of
parsing all the SQL queries and the connection overhead and just
generally having to go to the database everytime was too much.

The lesson I learned here: when all the data is at your disposal at
once, it pays to rethink the problem and your data structures.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php