Re: mirroring oracle database

[Edward Peschko]

On Sun, Jun 05, 2005 at 04:41:16PM -0700, sol beach wrote:
> IMO, you have much more a lively imagination than realistic, in depth
> & technical knowlege in either MYSQL or Oracle.
> Current production MYSQL does NOT have stored procedures.

Current production mysql doesn't, but current development does (5.02).

Given that this is something that is coming online about 6 months down the fly,
and is a direction that we are thinking about moving, and given how much
that such an effort would save you - and given the fact that all the data
in question is being backed up in an oracle database, as far as I can see,
the risk is minor and the rewards major.

All it really has to do is keep data for a minor interval (say, a day). Then
it can be synced with the oracle database in a batch job. 

I say its worth a shot. If its not doable now, its perhaps doable in 6 months.
And some people agree with me apparently:

http://www.convert-in.com/ora2sql.htm

which I was thinking about reverse engineering to an extent as a starting point.
 
Thanks for the vote of confidence btw, and the elegent, almost statesman-way 
that you expressed it..

But seriously, why the testy response? Are you affiliated in any way with 
oracle?
Isn't the whole point of mysql to ultimately provide a RDBMS that can be used 
instead of DB2 or Oracle anyways?

And does anybody have helpful, real, experience along these lines that they'd 
like to share rather than just opinions?

Ed

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

Re: MySQL (SQL) Newbie.. Need help with a Query

[Michael Stassen]

Sebastian wrote:


Cory Robin wrote:


I'm trying to return all records between two dates..  The fields are
datetime fields...

Which is better?  The following or using BETWEEN? (A little lost here)

SELECT * FROM passengers WHERE
reservation_date_time >= '2005-01-01 12:10:00'
AND reservation_date_time <= '2005-05-01 12:10:00';
 


i think you should be using BETWEEN

WHERE your_field BETWEEN '2005-01-01 12:10:00' AND '2005-05-01 12:10:00'


They are identical to mysql, but I think the BETWEEN version is easier 
to read for most of us humans.


Michael

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

Re: mysql UNION

[Michael Stassen]

Sebastian wrote:


Ah.. OK. I'll give that a shot instead..
Also, now that i am doing some thinking on optimizing... should the $cat 
clause be present first in the where clause? since it decides if it 
should get results or not. for example:


WHERE MATCH(...) AGAINST(... IN BOOLEAN MODE) AND ('$cat' = 'news' OR 
@cat = '')


or:

WHERE ('$cat' = 'news' OR '$cat' = '') AND MATCH(...) AGAINST(... IN 
BOOLEAN MODE)


does it matter in terms of performance?


I'm not sure.  In every case, the $cat clause is either impossible or 
certain, so it can be optimized away (taking the whole query with it in 
the impossible case).  Hence, there will be no difference in the 
processing of tables once the execution plan has been chosen.  The only 
possible difference would be determined by how much time the optimizer 
wastes on other parts of the WHERE clause before noticing an impossible 
$cat part.  I don't know precisely how the optimizer does this, so I 
don't know if order makes a difference, nor which order would be best if 
it does.  You could try it both ways, but I doubt you'd detect any 
difference in a single query.  If I had to guess, though, I'd guess that 
if it makes any difference, then you are right that first is best.


Also, for completeness, note that if there is any more to your WHERE 
clause than just the MATCH AGAINST, you may need to use parentheses 
around the entire rest of the WHERE clause, aside from the $cat part. 
For example, AND is higher precedence than OR, so


  WHERE ('$cat' = 'news' OR '$cat' = '') AND cond1 OR cond2

wouldn't work the way you want.  To guard against that, I'd suggest 
always adding the parentheses around the rest of the conditions:


  WHERE ('$cat' = 'news' OR '$cat' = '') AND (other conditions)

Michael

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

Show InnoDB Status

[ManojW]

Greeting,
 Is there any comprehensive documentation on "show Innodb status" command? I 
found the mysql help page a bit short and the whole exercise has left me with 
many questions unanswered hence any tips from pratical experience that would 
help in Innodb performance tuning would be greatly appreciated!

Cheers

Manoj 

mirroring oracle database

[Edward Peschko]

hey all,

I'm trying to convince some people here to adopt MySql as a relational database 
here. However, we can't start from a clean slate; we have a very mature oracle
database that applications point to right now, and so we need a migration path.

I was thinking of taking the following steps:


a) finding a Java API that transparently supports both MySQL and 
   Oracle data access and stored procedure calls.

b) instrumenting the Oracle database so that all tables support
   timestamps on data rows.

c) mirroring the Oracle database in MySQL.

d) making interface code connecting the MySQL database to the 
Oracle database (and both applying updates to the database
as well as data.


In other words, I'm looking to make a MySQL -> Oracle mirroring tool, and was 
wondering if anybody had experience with this sort of thing. 

As I see it, if we pull this off we could save quite a bit in licensing costs 
- we'd still have oracle around, but it would only be a datastore for talking 
to 
other oracle databases, and run by batch, not accessed by end users.

Ed

(
ps - here are the concerns I have right now about doing this... 
How well can stored procs be translated over? how about views, 
triggers and indexes?
)

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

Re: replication stopped

[Atle Veka]

It's impossible to say pretty much without more information. Have you
checked the error log on the slave? How about 'SHOW SLAVE STATUS'? Did
your "backup guy" gzip all binlogs, if so that would most definitely stop
replication.


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Fri, 3 Jun 2005 [EMAIL PROTECTED] wrote:

>
> I took a couple of days vacation last week and naturally the /data
> partition of my database server filled up because of something I was
> running and had forgotten about.  So my backup guy, thinking to help
> out, gzipped all my .bin logs.  Now I am noticing replication seems to
> have stopped on my other server.  Not sure if the two events are related
> or not.  It could be that things got all hosed up not because of the
> gzipping but because the partition filled up in the first place?
> Anyway, what do I do now?  Do I need to do a new dump and start
> replication all over again?
>
> --ja
>
> --
>
>

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

Re: sleeping processes

[Carl]

Ronny,

I think this is actually quite simple.  All of your statistic/information
says you are not closing the connection... so where in your code are you
closing the connection?

In one of my projects, I open the data manager as:

// open a MySql database
if (!(sock=mysql_init(0)))
{
_lclose(hfile);
MessageBox(hDlg,"Couldn't initialize mysql struct","Convert SportsLog
Data",MB_OK);
EndDialog(hDlg, LOWORD(wParam));
return TRUE;
}

// mysql_options(sock,MYSQL_READ_DEFAULT_GROUP,"connect");
if
(!mysql_real_connect(sock,ipNumber,userName,password,gvDatabase,3306,NULL,0)
)
{
_lclose(hfile);
sprintf(tstuff,"Couldn't connect to engine!\n%s\n",mysql_error(sock));
MessageBox(hDlg,tstuff,"Convert SportsLog Data",MB_OK);
EndDialog(hDlg, LOWORD(wParam));
return TRUE;
}

if (mysql_select_db(sock,gvDatabase))
{
_lclose(hfile);
sprintf(tstuff,"Couldn't select database test: Error: %s\n",
mysql_error(sock));
MessageBox(hDlg,tstuff,"Convert SportsLog Data",MB_OK);
EndDialog(hDlg, LOWORD(wParam));
return TRUE;
}

Then, I use the following code to select, etc.:

if (mysql_real_query(sock, query, strlen(query))) {
queryError(query);
}

After I pull the information I want out of the result set, I close the
result set with:

mysql_free_result(tableRes);

At the end of the program, I close the socket.

Note two things:

1.  I don't open a connection for each query, using the already open socket
instead (good for some things, not good for other ones.)  But, for the
appropriate applications, it is very fast.

2.  You have to free the result set after every select.

By the way, this code came right out of Googling.

Thanks and good luck,

Carl


- Original Message -
From: "Ronny Melz" <[EMAIL PROTECTED]>
To: 
Sent: Sunday, June 05, 2005 4:57 PM
Subject: Re: sleeping processes


>
> Thanks so far for your advice,
>
> > Is it possible that your application doesn't close connection properly?
> that is exactly what also I think is the problem's cause, but I am unable
to
> locate the place where it actually does happen. My code seems
straightforward
> and I had looked over it some other more experienced people which were
unable
> to find the bug as well... weird
>
> > Check with netstat the states of connections between your application
> > and server.
> '$ netstat | grep mysql' dumps a list increasing proportionally to 'mysql>
> show full processlist' up to the point where max_connections are reached:
> then the mysql processlist reports max_connections+1 pids (including the
> terminal I use to get the processlist) whereas a '$ netstat | grep mysql |
wc
> -l' does never return due to an ever increasing number of open
connections.
> Each of them is in state TIME_WAIT.
>
> > Do you see some sleeping processes
> > with ps utility or 'mysqladmin processlist' command?
> AFAIK, 'mysqladmin processlist' prints the same as a 'mysql> show full
> processlist', right? It's max_connections sleeping processes plus the
> processlist query.
>
> Your hint to watch out for sleeping processes with ps was interesting.
> Actually, I have some 14 processes ('ps lax | grep mysql') without running
my
> program but max_connections+14 if it is running. Each of the processes is
> sleeping. I still don't have any idea, do you?
>
> Any suggestions appreciated.
> Ronny
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005
>
>



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005


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

Re: MySQL (SQL) Newbie.. Need help with a Query

[Sebastian]

Cory Robin wrote:


I'm trying to return all records between two dates..  The fields are
datetime fields...

Which is better?  The following or using BETWEEN? (A little lost here)

SELECT * FROM passengers WHERE
reservation_date_time >= '2005-01-01 12:10:00'
AND reservation_date_time <= '2005-05-01 12:10:00';
 



i think you should be using BETWEEN

WHERE your_field BETWEEN '2005-01-01 12:10:00' AND '2005-05-01 12:10:00'

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

MySQL (SQL) Newbie.. Need help with a Query

[Cory Robin]

I'm trying to return all records between two dates..  The fields are
datetime fields...

Which is better?  The following or using BETWEEN? (A little lost here)

SELECT * FROM passengers WHERE
reservation_date_time >= '2005-01-01 12:10:00'
AND reservation_date_time <= '2005-05-01 12:10:00';



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

Re: Empty database name error

[Ed Kasky]

I added the line to the configuration file and removed "--log-error=" line 
from startup line but it still creates the /usr/local/mysql/var/yoda2.err 
as well as /var/log/mysql/error.log


I am assuming it is reading from my.cnf as I get the following when I run 
"mysqladmin variables"

log_error   | /var/log/mysql/error.log

I also checked for possible duplicate my.cnf files but there is only one.

Is there another way to check to be sure it's reading the configuration file?

At 12:40 PM Sunday, 6/5/2005, Gleb Paharenko wrote -=>

Unfortunately I could give suggestion only about your second question.
Is it possible that /var/log/mysql/error.log created by mysqld_safe
(you're specifying it with --log-error command line option) and
/usr/local/mysql/var/yuda2.err is created by mysqld process if it
founds problems before applying location of error log to it's internal
variable? Specify

  log-error   = /var/log/mysql/error.log

in your configuration file, and check that MySQL Server  actually reads 
this file.


Ed Kasky wrote:
> Hello there -
>
> I have a couple of questions regarding a new install of MySql 4.1.12 on
> RH 7.2.  Being new to this list, I sure do hope this hasn't been covered
> before.  I have scrubbed Google and searched the archives for this list
> but can't find an explanation or a solution to 2 issues:
>
> 1.  I get the following error when starting the daemon:
>
> 050605  7:08:51 [Warning] Found an entry in the 'db' table with empty
> database name; Skipped
>
> Is this something that should be fixed and if so, how does one go about it?
>
> 2.  I have set the error log to /var/log/mysql/error.log in the init
> script:
>
> LOG_ERROR="/var/log/mysql/error.log"
> $bindir/safe_mysqld --datadir=$datadir --pid-file=$pid_file
> --log-error=$LOG_ERROR
>
> However, I am still getting 2 error logs:
>
> What I am assuming is the default "/usr/local/mysql/var/yoda2.err"
> and the one specified - "/var/log/mysql/error.log"
>
> Is this expected behavior?  Is there another place aside from
> /etc/my.cnf that I might look?
>
> Thanks in advance for any tips and/or suggestions.
>
> Ed
>
> . . . . . . . . . . . . . . . . . .
> Randomly Generated Quote (116 of 975):
> "It had long since come to my attention that people of
>  accomplishment rarely sat back and let things happen
>  to them. They went out and happened to things."  - Elinor Smith
>
>


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




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


. . . . . . . . . . . . . . . . . .
Randomly Generated Quote (203 of 975):
"To climb steep hills requires slow pace at first."
 - William Shakespeare


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

Re: mysql UNION

[Sebastian]

Michael Stassen wrote:


Sebastian wrote:


Michael Stassen wrote:




Now, I never said this couldn't be done in SQL.  Assuming $cat is 
already set, the statement in $sql below should do what you asked for:


  $sql = "SELECT id, 'news' AS type,  FROM news
  WHERE ($cat = '' OR $cat = 'news')
UNION
  SELECT id, 'faq' AS type,  FROM faq
  WHERE ($cat = '' OR $cat = 'faq')
UNION
  SELECT id, 'forum' AS type,  FROM forum
  WHERE ($cat = '' OR $cat = 'forum')"





Michael



you just gave me an idea.. maybe this is better:

SET @cat = '$cat';

SELECT id,  'news' AS type,
WHERE [expression...] AND (@cat = 'news' OR @cat = '')
 UNION
  SELECT id,  'faq' AS type,
  WHERE [expression...] AND (@cat = 'faq' OR @cat = '')

UNION
 [SELECT ]

works regardless if $cat is set or not.



That was the point.  The query I gave, quoted above, works whether 
$cat is set or not (though I forgot to quote $cat in the comparisons 
-- sorry).  No need to modify it to use a mysql user variable, either. 
Taking into account the news that your real query is more complicated, 
this would look something like:


  $sql = "SELECT id, 'news' AS type, ..other_columns.. FROM news
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'news')
UNION
  SELECT id, 'faq' AS type, ..other_columns.. FROM faq
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'faq')
UNION
  SELECT id, 'forum' AS type, ..other_columns.. FROM forum
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'forum')";

Michael



Ah.. OK. I'll give that a shot instead..
Also, now that i am doing some thinking on optimizing... should the $cat 
clause be present first in the where clause? since it decides if it 
should get results or not. for example:


WHERE MATCH(...) AGAINST(... IN BOOLEAN MODE) AND ('$cat' = 'news' OR 
@cat = '')


or:

WHERE ('$cat' = 'news' OR @cat = '') AND MATCH(...) AGAINST(... IN 
BOOLEAN MODE)


does it matter in terms of performance?

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

Re: sleeping processes

[Ronny Melz]

Thanks so far for your advice,

> Is it possible that your application doesn't close connection properly?
that is exactly what also I think is the problem's cause, but I am unable to 
locate the place where it actually does happen. My code seems straightforward 
and I had looked over it some other more experienced people which were unable 
to find the bug as well... weird

> Check with netstat the states of connections between your application
> and server. 
'$ netstat | grep mysql' dumps a list increasing proportionally to 'mysql> 
show full processlist' up to the point where max_connections are reached: 
then the mysql processlist reports max_connections+1 pids (including the 
terminal I use to get the processlist) whereas a '$ netstat | grep mysql | wc 
-l' does never return due to an ever increasing number of open connections. 
Each of them is in state TIME_WAIT.

> Do you see some sleeping processes 
> with ps utility or 'mysqladmin processlist' command?
AFAIK, 'mysqladmin processlist' prints the same as a 'mysql> show full 
processlist', right? It's max_connections sleeping processes plus the 
processlist query.

Your hint to watch out for sleeping processes with ps was interesting. 
Actually, I have some 14 processes ('ps lax | grep mysql') without running my 
program but max_connections+14 if it is running. Each of the processes is 
sleeping. I still don't have any idea, do you?

Any suggestions appreciated.
Ronny

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

Re: mysql UNION

[Michael Stassen]

Sebastian wrote:


Michael Stassen wrote:



Now, I never said this couldn't be done in SQL.  Assuming $cat is 
already set, the statement in $sql below should do what you asked for:


  $sql = "SELECT id, 'news' AS type,  FROM news
  WHERE ($cat = '' OR $cat = 'news')
UNION
  SELECT id, 'faq' AS type,  FROM faq
  WHERE ($cat = '' OR $cat = 'faq')
UNION
  SELECT id, 'forum' AS type,  FROM forum
  WHERE ($cat = '' OR $cat = 'forum')"




Michael


you just gave me an idea.. maybe this is better:

SET @cat = '$cat';

SELECT id,  'news' AS type,
WHERE [expression...] AND (@cat = 'news' OR @cat = '')
 UNION
  SELECT id,  'faq' AS type,
  WHERE [expression...] AND (@cat = 'faq' OR @cat = '')

UNION
 [SELECT ]

works regardless if $cat is set or not.


That was the point.  The query I gave, quoted above, works whether $cat 
is set or not (though I forgot to quote $cat in the comparisons -- 
sorry).  No need to modify it to use a mysql user variable, either. 
Taking into account the news that your real query is more complicated, 
this would look something like:


  $sql = "SELECT id, 'news' AS type, ..other_columns.. FROM news
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'news')
UNION
  SELECT id, 'faq' AS type, ..other_columns.. FROM faq
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'faq')
UNION
  SELECT id, 'forum' AS type, ..other_columns.. FROM forum
  ..JOINs to other tables..
  WHERE (various complicated conditions)
AND ('$cat' = '' OR '$cat' = 'forum')";

Michael

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

Re: Access denied

[Anoop kumar V]

The reason i sthat you have not provided authorisation privileges to the 
database or to the tables within the database for that user...

Very often we think granting all to the database is enough to make our app 
work with a particular user - this may be true for some databases - but in 
mysql you must do a grant all on your_database.* for that user - you must 
explicitly authorise the user for each table (using * or each table name) 
within the database.

Hope that helps,
Anoop

On 6/4/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote:
> 
> Hello.
> 
> 
> 
> See:
> 
> http://dev.mysql.com/doc/mysql/en/access-denied.html
> 
> http://dev.mysql.com/doc/mysql/en/resetting-permissions.html
> 
> 
> 
> 
> 
> Seena Blace <[EMAIL PROTECTED]> wrote:
> 
> > [-- text/plain, encoding 8bit, charset: iso-8859-1, 17 lines --]
> 
> >
> 
> > Hi,
> 
> >
> 
> > I have been noticing following error when trying to connect mysql.
> 
> >
> 
> > ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: 
> NO)
> 
> > or
> 
> > ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: 
> YES)
> 
> >
> 
> > What could be reason?
> 
> >
> 
> > Is there any way I can connect to database without changing passwd?
> 
> > thanks
> 
> >
> 
> >
> 
> > -
> 
> > Discover Yahoo!
> 
> > Get on-the-go sports scores, stock quotes, news & more. Check it out!
> 
> 
> 
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET  
> http://www.ensita.net/
> __ ___ ___  __
> / |/ /_ __/ __/ __ \/ / Gleb Paharenko
> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET 
> <___/ www.mysql.com 
> 
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Thanks and best regards,
Anoop

Re: mysql UNION

[Michael Stassen]

Sebastian wrote:


Michael Stassen wrote:


So, why spend days trying to come up with complicated, ugly sql when 
it can be done in 5 minutes with simple, easy-to-read php?


Michael

i only posted snip of the query.. it is much larger and complex than 
just a SELECT. i am going MATCH, AGAINST, LEFT JOINS, and other 
operations for a search engine. doing it in the app (php in my case)


Well, that's the first problem.  When you ask about a simplified version 
of your problem, you run the risk of getting answers which apply to your 
simplified problem rather than your real problem.  Unless you are 
certain that answers to the simplified version will be generally 
applicable (apparently not the case here), you should post your real query.


would mean i would end up with a huge block of SQL code which would seem 
more ugly IMO..  


I don't see it.  Your basic query is longer and more complicated than 
you've shown, but you still have the same problem.  You need one query 
if $cat is set, 3 unioned queries if $cat is not.


.. on top of that i am paginating the results which would 
mean i would have to duplicate the code you posted above since there 
would be no point in selecting many other columns just to get a row 
count for the pagination part so i couldn't re-use the IF statement 
output as shown above.


But don't you have this problem anyway?  You need a pagination query and 
a full query, each of which is one version of the long, complicated 
query you describe, with "MATCH, AGAINST, LEFT JOINS, and other 
operations", or the other.  If the only difference is the list of 
columns selected, it shouldn't be too hard to put that in a variable and 
reuse the if..else.


Of course, ugliness, like beauty, is in the eye of the beholder. 
Perhaps you'll prefer the one-statement-in-sql version I sent in my 
first reply to Mathias' suggestions.  It's just a matter of adding 2 
conditions to each WHERE clause in the UNION, so I think it will meet 
your specs, even for your real query.


Michael

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

Re: mysql UNION

[Sebastian]

Michael Stassen wrote:


[EMAIL PROTECTED] wrote:


Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :

mysql> select id,'news' as selected, type from news
-> union select id,'faq' as selected, type from faq
-> union select id,'forum' as selected, type from forum;
+--+--+---+
| id   | selected | type  |
+--+--+---+
|1 | news | news  |
|2 | faq  | faq   |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)


FIRST CRAZY METHOD :
*
mysql> set @cat='news';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from (
-> select id,'news' as selected, type from news
-> union select id,'faq' as selected, type from faq
-> union select id,'forum' as selected, type from forum
-> ) Temp
-> where [EMAIL PROTECTED];
+--+--+--+
| id   | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)


SECOND CRAZY METHOD (I prefer):
*

set @cat := 'news';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;

+--+--+
| id   | selected |
+--+--+
|1 | news |
+--+--+
1 row in set (0.00 sec)

deallocate prepare stmt;


* another click with ?cat=faq

set @cat := 'faq';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;

mysql> execute stmt;
+--+--+
| id   | selected |
+--+--+
|2 | faq  |
+--+--+
1 row in set (0.00 sec)

deallocate prepare stmt;

OTHER CRAZY METHODS - coming emails :o)


A+
Mathias



The first method is horribly inefficient (and requires mysql 4.1+).  
It reads all 3 tables, unions the resulting rows, checks for (and 
removes) duplicate rows, then finally throws away roughly 2/3 of the 
results (the rows from the 2 unwanted tables.  Compare that to the 
simple query which only addresses the 1 desired table.  Mathias is 
aware of this, which is why he gives the second method.  It creates 
the simple, one-table query using the value of $cat to choose which 
table.


The big problem here is that neither of these methods actually do what 
you asked for.  That is, neither works if $cat is not set.  With both 
methods, you will get no rows unless $cat is set.  In fact, the second 
method will give a syntax eror, as there will be no table name in the 
FROM clause.


Now, I never said this couldn't be done in SQL.  Assuming $cat is 
already set, the statement in $sql below should do what you asked for:


  $sql = "SELECT id, 'news' AS type,  FROM news
  WHERE ($cat = '' OR $cat = 'news')
UNION
  SELECT id, 'faq' AS type,  FROM faq
  WHERE ($cat = '' OR $cat = 'faq')
UNION
  SELECT id, 'forum' AS type,  FROM forum
  WHERE ($cat = '' OR $cat = 'forum')"

MySQL's optimizer should be smart enough to notice that if $cat is 
set, 2 of the 3 unioned queries will have impossible WHERE clauses, so 
those queries will be skipped, making this relatively efficient.


Even though this works, though, I think it's a bit of a hack.  It's 
clever SQL which allows two separate queries to masquerade as one, 
based on the value of $cat.  I really think the simple, separate query 
solution I sent in my previous message


  if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
  {
$sql = "SELECT id, '$cat' AS type, ... FROM $cat";
  }
  else
  {
$sql = " SELECT id, 'news' AS type,  FROM news
UNION
 SELECT id, 'faq' AS type,  FROM faq
UNION
 SELECT id, 'forum' AS type,  FROM forum";
  }

is better, as it is clearer what we are doing (one query if we know 
the table, three queries unioned if we don't), so it should be easier 
to understand and maintain down the road (by you or the next guy).


Michael


you just gave me an idea.. maybe this is better:

SET @cat = '$cat';

SELECT id,  'news' AS type,
WHERE [expression...] AND (@cat = 'news' OR @cat = '')
 
UNION

  SELECT id,  'faq' AS type,
  WHERE [expression...] AND (@cat = 'faq' OR @cat = '')

UNION
 [SELECT ]

works regardless if $cat is set or not.


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

Re: mysql UNION

[Michael Stassen]

[EMAIL PROTECTED] wrote:


I agree with you,
just see that if ..
sql = "SELECT id, '$cat' AS type, ... FROM $cat";
is exactly what is done in prepare statement.


Yes, I acknowledged that in my next message (a reply to yours).


dynamic sql is better than application level statement preparation, when you use
stored procedure. but since this is the habit in mysql, i'll keep this for
oracle, and other rdbms.


It's not a question of habit.  Stored procedures don't exist in mysql 
until 5.0, which is still in beta and not for production use.  We should 
not assume that people use 5.0 unless they explicitly say they do.  If 
we don't assume 5.0, this point is moot.



P.S. haven't spent days to help. It is easy. And since it was a week-end :o)


Right.  That was a reference to the original poster's statement, "i am 
stuck on this for a few days already."



Mathias


Michael

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

Re: mysql UNION

[Michael Stassen]

[EMAIL PROTECTED] wrote:


Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :

mysql> select id,'news' as selected, type from news
-> union select id,'faq' as selected, type from faq
-> union select id,'forum' as selected, type from forum;
+--+--+---+
| id   | selected | type  |
+--+--+---+
|1 | news | news  |
|2 | faq  | faq   |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)


FIRST CRAZY METHOD :
*
mysql> set @cat='news';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from (
-> select id,'news' as selected, type from news
-> union select id,'faq' as selected, type from faq
-> union select id,'forum' as selected, type from forum
-> ) Temp
-> where [EMAIL PROTECTED];
+--+--+--+
| id   | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)


SECOND CRAZY METHOD (I prefer):
*

set @cat := 'news';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;

+--+--+
| id   | selected |
+--+--+
|1 | news |
+--+--+
1 row in set (0.00 sec)

deallocate prepare stmt;


* another click with ?cat=faq

set @cat := 'faq';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;

mysql> execute stmt;
+--+--+
| id   | selected |
+--+--+
|2 | faq  |
+--+--+
1 row in set (0.00 sec)

deallocate prepare stmt;

OTHER CRAZY METHODS - coming emails :o)


A+
Mathias


The first method is horribly inefficient (and requires mysql 4.1+).  It 
reads all 3 tables, unions the resulting rows, checks for (and removes) 
duplicate rows, then finally throws away roughly 2/3 of the results (the 
rows from the 2 unwanted tables.  Compare that to the simple query which 
only addresses the 1 desired table.  Mathias is aware of this, which is 
why he gives the second method.  It creates the simple, one-table query 
using the value of $cat to choose which table.


The big problem here is that neither of these methods actually do what 
you asked for.  That is, neither works if $cat is not set.  With both 
methods, you will get no rows unless $cat is set.  In fact, the second 
method will give a syntax eror, as there will be no table name in the 
FROM clause.


Now, I never said this couldn't be done in SQL.  Assuming $cat is 
already set, the statement in $sql below should do what you asked for:


  $sql = "SELECT id, 'news' AS type,  FROM news
  WHERE ($cat = '' OR $cat = 'news')
UNION
  SELECT id, 'faq' AS type,  FROM faq
  WHERE ($cat = '' OR $cat = 'faq')
UNION
  SELECT id, 'forum' AS type,  FROM forum
  WHERE ($cat = '' OR $cat = 'forum')"

MySQL's optimizer should be smart enough to notice that if $cat is set, 
2 of the 3 unioned queries will have impossible WHERE clauses, so those 
queries will be skipped, making this relatively efficient.


Even though this works, though, I think it's a bit of a hack.  It's 
clever SQL which allows two separate queries to masquerade as one, based 
on the value of $cat.  I really think the simple, separate query 
solution I sent in my previous message


  if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
  {
$sql = "SELECT id, '$cat' AS type, ... FROM $cat";
  }
  else
  {
$sql = " SELECT id, 'news' AS type,  FROM news
UNION
 SELECT id, 'faq' AS type,  FROM faq
UNION
 SELECT id, 'forum' AS type,  FROM forum";
  }

is better, as it is clearer what we are doing (one query if we know the 
table, three queries unioned if we don't), so it should be easier to 
understand and maintain down the road (by you or the next guy).


Michael

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

Re: sleeping processes

[Gleb Paharenko]

Hello.



Is it possible that your application doesn't close connection properly?

Check with netstat the states of connections between your application 

and server. Run netstat on the server. Do you see some sleeping processes

with ps utility or 'mysqladmin processlist' command?







Ronny Melz wrote:

>Dear all,

> 

> I have a problem with the mysql interface for c, which after a couple of

>  hours thinking about bad or faulty programming are eliminated with

>  probability almost 100%.

> 

> within a loop, I do a mysql_query(), which is executed and I poll the result

> and everything is fine. Up to when max_connections of the mysqld is reached:

> each of the queries leaves after executing a sleeping mysql process behind on

> the server, which hence throws the "Too many connections" error (or under

> certain conditions even a "Can't create TCP/IP socket (24)").

> 

> Just because of this problem I recently upgraded mysql (including the

> libraries) from 4.0.21 to 4.1.12, but the problem still persists.

> 

> Does anybody have an idea of how to solve this problem?

> Any suggestions are appreciated.

> 

> Best,

> Ronny

> 

> 

> p.s.: code

> #define GET_W_NR \

> "select wort_nr from wortliste where wort_bin='%s' limit 1"

> MYSQL mysql;

> MYSQL_RES *res;

> MYSQL_ROW row;

> char *query;

> 

> init...

>   {

>   mysql_init(&mysql);

>   if ( !mysql_real_connect( &mysql, "127.0.0.1", "username", "passwd",

> "dbase",

>  0, "/var/lib/mysql/mysql.sock", 0 ) )

> {

> fprintf(stderr, "Failed: %s\n",mysql_error(&mysql));

> }

>   if (!(query = (char*) malloc(256*sizeof(char fprintf(stderr, "no

> pointer");

>   }

> 

> this code of a function is called in a loop and returns with a mysql_error 

> after `max_connections` cycles:

> {

> sprintf(query, GET_W_NR, refWort);

> if ( mysql_query( &mysql, query ) )

> {

> fprintf(stderr, "query failed: %s\n", mysql_error(&mysql));

> return 0;

> }

> if ( !( res = mysql_store_result( &mysql ) ) )

> {fprintf(stderr, "store failed: %s\n", mysql_error(&mysql)); return

> 0;}

> if ( row = mysql_fetch_row( res ) )

> ref_word_nr = atoi(row[0]);

> else

> {fprintf(stderr, "fetch failed: %s\n", mysql_error(&mysql)); return

> 0;}

> mysql_free_result(res);

> }

> 

> exit...

>   free(query);

>   mysql_close(&mysql);

> 



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




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

Re: Empty database name error

[Gleb Paharenko]

Hello.



Unfortunately I could give suggestion only about your second question.

Is it possible that /var/log/mysql/error.log created by mysqld_safe

(you're specifying it with --log-error command line option) and

/usr/local/mysql/var/yuda2.err is created by mysqld process if it

founds problems before applying location of error log to it's internal

variable? Specify



  log-error   = /var/log/mysql/error.log



in your configuration file, and check that MySQL Server  actually reads this 
file.









Ed Kasky wrote:

> Hello there -

> 

> I have a couple of questions regarding a new install of MySql 4.1.12 on 

> RH 7.2.  Being new to this list, I sure do hope this hasn't been covered 

> before.  I have scrubbed Google and searched the archives for this list 

> but can't find an explanation or a solution to 2 issues:

> 

> 1.  I get the following error when starting the daemon:

> 

> 050605  7:08:51 [Warning] Found an entry in the 'db' table with empty 

> database name; Skipped

> 

> Is this something that should be fixed and if so, how does one go about it?

> 

> 2.  I have set the error log to /var/log/mysql/error.log in the init 

> script:

> 

> LOG_ERROR="/var/log/mysql/error.log"

> $bindir/safe_mysqld --datadir=$datadir --pid-file=$pid_file 

> --log-error=$LOG_ERROR

> 

> However, I am still getting 2 error logs:

> 

> What I am assuming is the default "/usr/local/mysql/var/yoda2.err"

> and the one specified - "/var/log/mysql/error.log"

> 

> Is this expected behavior?  Is there another place aside from 

> /etc/my.cnf that I might look?

> 

> Thanks in advance for any tips and/or suggestions.

> 

> Ed

> 

> . . . . . . . . . . . . . . . . . .

> Randomly Generated Quote (116 of 975):

> "It had long since come to my attention that people of

>  accomplishment rarely sat back and let things happen

>  to them. They went out and happened to things."  - Elinor Smith

> 

> 



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




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

Re: mysql UNION

[Sebastian]

Michael Stassen wrote:


Sebastian wrote:


Michael Stassen wrote:


Sebastian wrote:


i have a query with 3 union selects:

   SELECT id, 'news' AS type,  FROM news

   UNION
 SELECT id, 'faq' AS type,  FROM faq

   UNION

   SELECT id, 'forum' AS type,  FROM forum

which works just fine and selects everything from all 3 tables.. 
but say i want to make a condition to only select from either 'faq' 
, 'news' or 'forum' how can i do this?


example, if a user visits a link suck as: page.php?cat=faq it will 
only select from 'faq' .. is this possible to do right in the 
query? when there is no ?cat= then all three selects run.


makes sense? i am stuck on this for a few days already.
thanks.



Why don't you do this in your app?  If cat is set, issue the 
appropriate single-table query, otherwise issue the union.  Surely 
that would be simpler than trying to build one multi-purpose query.


Michael



I was hoping i could do some crazy thing like WHERE type = 'faq' so i 
can do it all from one block of code.



Frankly, I don't see the benefit of this.  What is the payoff?  php 
was designed for this sort of conditional execution, sql wasn't.  
You've spent several days trying to find a way to do this in sql.  How 
long would it have taken you to write the if...else... statement in 
php? Let's see:


if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
{
  $sql = "SELECT id, '$cat' AS type, ... FROM $cat";
}
else
{
  $sql = " SELECT id, 'news' AS type,  FROM news
  UNION
   SELECT id, 'faq' AS type,  FROM faq
  UNION
   SELECT id, 'forum' AS type,  FROM forum";
}

So, why spend days trying to come up with complicated, ugly sql when 
it can be done in 5 minutes with simple, easy-to-read php?


Michael



i only posted snip of the query.. it is much larger and complex than 
just a SELECT. i am going MATCH, AGAINST, LEFT JOINS, and other 
operations for a search engine. doing it in the app (php in my case) 
would mean i would end up with a huge block of SQL code which would seem 
more ugly IMO.. on top of that i am paginating the results which would 
mean i would have to duplicate the code you posted above since there 
would be no point in selecting many other columns just to get a row 
count for a the pagination part so i couldn't re-use the IF statement 
output as shown above.


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

Not able to connect to Mysql server from network machine

[Reema Troiana]

Hi All,

I'm trying to connect to MySql server from MySql Administrator on a network 
machine. I specify the Server host as IP of the machine where DB server is 
running and Port as 3306.

Username as 'root' and the password

I have tried with other usernames and specifying machine name instead of IP. It 
doesn't work in any case. 

But i always get this error:

"Could not connect to the specififed instance. MySQL Error number 2003" and it 
says:

"If you want to check network connection, please ping"
When i click "ping" i get reply from the machine i'm trying to connect to

I'm able to connect to localhost, i.e., if i have the DB server on the same 
machine. MySql server is running on XP machine. Is there any option that i have 
to enable to make network machine to log on to Mysql DB server??

I'll appreciate any help.

Thanks,
Reema

 
 

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

Re: mysql UNION

[mfatene]

I agree with you,
just see that if ..
sql = "SELECT id, '$cat' AS type, ... FROM $cat";
is exactly what is done in prepare statement.

dynamic sql is better than application level statement preparation, when you use
stored procedure. but since this is the a habit in mysql, i'll keep this for
oracle, and other rdbms.

P.S. haven't spent days to help. It is easy. And since it was a week-end :o)

Mathias

Selon Michael Stassen <[EMAIL PROTECTED]>:

> Sebastian wrote:
>
> > Michael Stassen wrote:
> >
> >> Sebastian wrote:
> >>
> >>> i have a query with 3 union selects:
> >>>
> >>>SELECT id, 'news' AS type,  FROM news
> >>>
> >>>UNION
> >>>  SELECT id, 'faq' AS type,  FROM faq
> >>>
> >>>UNION
> >>>
> >>>SELECT id, 'forum' AS type,  FROM forum
> >>>
> >>> which works just fine and selects everything from all 3 tables.. but
> >>> say i want to make a condition to only select from either 'faq' ,
> >>> 'news' or 'forum' how can i do this?
> >>>
> >>> example, if a user visits a link suck as: page.php?cat=faq it will
> >>> only select from 'faq' .. is this possible to do right in the query?
> >>> when there is no ?cat= then all three selects run.
> >>>
> >>> makes sense? i am stuck on this for a few days already.
> >>> thanks.
> >>>
> >>
> >> Why don't you do this in your app?  If cat is set, issue the
> >> appropriate single-table query, otherwise issue the union.  Surely
> >> that would be simpler than trying to build one multi-purpose query.
> >>
> >> Michael
> >
> > I was hoping i could do some crazy thing like WHERE type = 'faq' so i
> > can do it all from one block of code.
>
> Frankly, I don't see the benefit of this.  What is the payoff?  php was
> designed for this sort of conditional execution, sql wasn't.  You've
> spent several days trying to find a way to do this in sql.  How long
> would it have taken you to write the if...else... statement in php?
> Let's see:
>
> if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
> {
>$sql = "SELECT id, '$cat' AS type, ... FROM $cat";
> }
> else
> {
>$sql = " SELECT id, 'news' AS type,  FROM news
>UNION
> SELECT id, 'faq' AS type,  FROM faq
>UNION
> SELECT id, 'forum' AS type,  FROM forum";
> }
>
> So, why spend days trying to come up with complicated, ugly sql when it
> can be done in 5 minutes with simple, easy-to-read php?
>
> Michael
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>



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

Re: mysql UNION

[Michael Stassen]

Sebastian wrote:


Michael Stassen wrote:


Sebastian wrote:


i have a query with 3 union selects:

   SELECT id, 'news' AS type,  FROM news

   UNION
 SELECT id, 'faq' AS type,  FROM faq

   UNION

   SELECT id, 'forum' AS type,  FROM forum

which works just fine and selects everything from all 3 tables.. but 
say i want to make a condition to only select from either 'faq' , 
'news' or 'forum' how can i do this?


example, if a user visits a link suck as: page.php?cat=faq it will 
only select from 'faq' .. is this possible to do right in the query? 
when there is no ?cat= then all three selects run.


makes sense? i am stuck on this for a few days already.
thanks.



Why don't you do this in your app?  If cat is set, issue the 
appropriate single-table query, otherwise issue the union.  Surely 
that would be simpler than trying to build one multi-purpose query.


Michael


I was hoping i could do some crazy thing like WHERE type = 'faq' so i 
can do it all from one block of code.


Frankly, I don't see the benefit of this.  What is the payoff?  php was 
designed for this sort of conditional execution, sql wasn't.  You've 
spent several days trying to find a way to do this in sql.  How long 
would it have taken you to write the if...else... statement in php? 
Let's see:


if ($cat == 'news' or $cat == 'faq' or $cat == 'forum')
{
  $sql = "SELECT id, '$cat' AS type, ... FROM $cat";
}
else
{
  $sql = " SELECT id, 'news' AS type,  FROM news
  UNION
   SELECT id, 'faq' AS type,  FROM faq
  UNION
   SELECT id, 'forum' AS type,  FROM forum";
}

So, why spend days trying to come up with complicated, ugly sql when it 
can be done in 5 minutes with simple, easy-to-read php?


Michael

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

Re: Slow LIMIT Query

[Doug V]

Hi,

I have tried to simply the problem and it exists without any JOINs.


have you given the query ?


SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 18, 10 -> .43 sec

SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 0, 10 -> .0007 sec


have you described your tables ?


stuffed_date is INDEXed


have your given the size of each table ?


The table is about 200k rows.


have you list the indexes ?


stuff table has several indices, including 'id' and 'stuffed_date'.


have you specify the storage type ?


MYISAM

In your followup message, you mention reverse sorting the query. I imagine 
on the application side I would need to reverse sort again to get the 
correct order. Are there any other ways to speed up such a query? Thanks.




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

Re: LOAD DATA INFILE - what is the path to file?

[Michael Stassen]

Chris wrote:


I have been using LOAD DATA INFILE to load an ASCII data file into my
database. The datafile is uploaded to the server temp area and the name of
the file is passed to LOAD DATA INFILE query like:

LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS
TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

I now want to load data using LOAD DATA INFILE from a data file located
within my http_public directory. I can create a path to the file from my
DOCUMENT_ROOT, however passing that path to LOAD DATA INFILE generates this
error: (NOTE: I set the file permissions to 777)


Don't do that, it's horribly insecure.  Anybody could modify this file 
before mysql loads it.  You should probably never make a file 
world-writable.  Mysql only needs to read the file, so set the 
permissions to 744.  Better yet, make it owned by the mysql group, and 
set permissions to 740.



Can't get stat of '/home/path_to_my_file/datafile.txt' (Errcode: 13)


  ~: perror 13
  OS error code  13:  Permission denied

In order to read the file, the mysql user must have read permission on 
the file (you've done that), and must have execute permission on every 
directory in the path to the file.  So, for mysql to read 
/home/path/to/file/datafile.txt, you will need to set permissions of 711 
on /home, /home/path, /home/path/to, and /home/path/to/file, in addition 
to the 744 permissions on datafile.txt.



Now if I just create a query like:

LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS
TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

I get this error
File './my_database_name/datafile.txt' not found (Errcode: 2)


  ~: perror 2
  OS error code   2:  No such file or directory


Which seems to tell me that LOAD DATA INFILE is looking for my data file in
a location that is outside my hosting account. I just have an account with a
shared hosting service provider.


Without a leading /, the path is treated as a relative path -- relative 
to the server's data directory.  Your file isn't there, hence the error.



So how would I specify a path to a file that is outside the directory where
my database is located?


With a full path, as you did originally.  You just have to make sure 
mysql has all the permissions neede to access it.



OBSERVATION: It appears the tmp directory must be in the database path
because, files uploaded to the tmp dir can be loaded using LOAD DATA INFILE.


No, /tmp works because it (usually) has 1777 permissions, so mysql has 
the necessary execute permission to access /tmp's contents.



Thanks for replies,
Chris


Michael

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

Re: Slow LIMIT Query

[mfatene]

But i give you a suggestion (theoritical) :
if data are reverse sorted , LIMIT 18, 10 will be LIMIt 2, 10

who knows ?

Mathias

Selon [EMAIL PROTECTED]:

> Hi,
> i and i think all people will think the same : i can't help. why ?
>
> have you described your tables ?
> have your given the size of each table ?
> have you list the indexes ?
> have you specify the storage type ?
> and
> have you given the query ?
>
> if you're looking for just a theoritical response, docs.mysql.com can give
> it.
>
> and sorry, this is not an ofense .
>
> Mathias
>
>
> Selon Doug V <[EMAIL PROTECTED]>:
>
> > When I do a SELECT using STRAIGHT JOIN against multiple tables where the
> > main table has about 200k rows, it is very fast retrieving the latest rows,
> > ie "LIMIT 0, 10", but extremely slow retrieving older rows, for example,
> > "LIMIT 18 , 10". Doing an EXPLAIN shows that no filesort or temporary
> > table is being used. When I do the SELECT without the STRAIGHT JOIN, it
> does
> > do a filesort and is a little bit faster. Is there anyway to speed such a
> > query up? Thanks.
> >
> >
> >
> > --
> > 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: LOAD DATA INFILE - what is the path to file?

[mfatene]

Hi,
load data infile tries to load data from specified directory on the server.

if your file is on the client, try LOAD DATA LOCAL and verify the parameter
--enable-local-infile

more details are in http://dev.mysql.com/doc/mysql/en/load-data-local.html about
data on the web server.

Nota bene :
***
D:\>perror 13
OS error code  13:  Permission denied

You have an os permission eroor.

Mathias

Selon Chris <[EMAIL PROTECTED]>:

> I have been using LOAD DATA INFILE to load an ASCII data file into my
> database. The datafile is uploaded to the server temp area and the name of
> the file is passed to LOAD DATA INFILE query like:
>
> LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS
> TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
>
> I now want to load data using LOAD DATA INFILE from a data file located
> within my http_public directory. I can create a path to the file from my
> DOCUMENT_ROOT, however passing that path to LOAD DATA INFILE generates this
> error: (NOTE: I set the file permissions to 777)
>
> Can't get stat of '/home/path_to_my_file/datafile.txt' (Errcode: 13)
>
> Now if I just create a query like:
>
> LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS
> TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
>
> I get this error
> File './my_database_name/datafile.txt' not found (Errcode: 2)
>
> Which seems to tell me that LOAD DATA INFILE is looking for my data file in
> a location that is outside my hosting account. I just have an account with a
> shared hosting service provider.
>
> So how would I specify a path to a file that is outside the directory where
> my database is located?
>
> OBSERVATION: It appears the tmp directory must be in the database path
> because, files uploaded to the tmp dir can be loaded using LOAD DATA INFILE.
>
> Thanks for replies,
> Chris
>
>
>
>
>
> --
> 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: Slow LIMIT Query

[mfatene]

Hi,
i and i think all people will think the same : i can't help. why ?

have you described your tables ?
have your given the size of each table ?
have you list the indexes ?
have you specify the storage type ?
and
have you given the query ?

if you're looking for just a theoritical response, docs.mysql.com can give it.

and sorry, this is not an ofense .

Mathias


Selon Doug V <[EMAIL PROTECTED]>:

> When I do a SELECT using STRAIGHT JOIN against multiple tables where the
> main table has about 200k rows, it is very fast retrieving the latest rows,
> ie "LIMIT 0, 10", but extremely slow retrieving older rows, for example,
> "LIMIT 18 , 10". Doing an EXPLAIN shows that no filesort or temporary
> table is being used. When I do the SELECT without the STRAIGHT JOIN, it does
> do a filesort and is a little bit faster. Is there anyway to speed such a
> query up? Thanks.
>
>
>
> --
> 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]

Slow LIMIT Query

[Doug V]

When I do a SELECT using STRAIGHT JOIN against multiple tables where the 
main table has about 200k rows, it is very fast retrieving the latest rows, 
ie "LIMIT 0, 10", but extremely slow retrieving older rows, for example, 
"LIMIT 18 , 10". Doing an EXPLAIN shows that no filesort or temporary 
table is being used. When I do the SELECT without the STRAIGHT JOIN, it does 
do a filesort and is a little bit faster. Is there anyway to speed such a 
query up? Thanks.




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

LOAD DATA INFILE - what is the path to file?

[Chris]

I have been using LOAD DATA INFILE to load an ASCII data file into my
database. The datafile is uploaded to the server temp area and the name of
the file is passed to LOAD DATA INFILE query like:

LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS
TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

I now want to load data using LOAD DATA INFILE from a data file located
within my http_public directory. I can create a path to the file from my
DOCUMENT_ROOT, however passing that path to LOAD DATA INFILE generates this
error: (NOTE: I set the file permissions to 777)

Can't get stat of '/home/path_to_my_file/datafile.txt' (Errcode: 13)

Now if I just create a query like:

LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS
TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

I get this error
File './my_database_name/datafile.txt' not found (Errcode: 2)

Which seems to tell me that LOAD DATA INFILE is looking for my data file in
a location that is outside my hosting account. I just have an account with a
shared hosting service provider.

So how would I specify a path to a file that is outside the directory where
my database is located?

OBSERVATION: It appears the tmp directory must be in the database path
because, files uploaded to the tmp dir can be loaded using LOAD DATA INFILE.

Thanks for replies,
Chris





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

Re: If statment in query

[Michael Stassen]

Sebastian wrote:


I have two fields: topic | title

topic does not always have data in it, so i want to select `title` when 
`topic` is null..


i thought i could do this (does not work):

IF(title IS NULL, topic, title) AS heading

Thanks.


There is nothing wrong with this, as long as it is part of a complete query.

  SELECT IF(title IS NULL, topic, title) AS heading FROM yourtable;

should do exactly what you say you want.  Simon Garner's suggestion

  SELECT IFNULL(title, topic) AS heading FROM yourtable;

should work, as well.

General advice: "does not work" is nearly useless when asking questions. 
 You need to describe the problem to get useful help.  Show the 
complete query, then tell us what went wrong.  Did you get an error 
message?  If so, paste it in.  Did you get unexpected results?  If so, 
show us what you got and what you expected.  Also, be sure to include 
the version of mysql you use, as answers often depend on the version.


Michael

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

Empty database name error

[Ed Kasky]

Hello there -

I have a couple of questions regarding a new install of MySql 4.1.12 on RH 
7.2.  Being new to this list, I sure do hope this hasn't been covered 
before.  I have scrubbed Google and searched the archives for this list but 
can't find an explanation or a solution to 2 issues:


1.  I get the following error when starting the daemon:

050605  7:08:51 [Warning] Found an entry in the 'db' table with empty 
database name; Skipped


Is this something that should be fixed and if so, how does one go about it?

2.  I have set the error log to /var/log/mysql/error.log in the init script:

LOG_ERROR="/var/log/mysql/error.log"
$bindir/safe_mysqld --datadir=$datadir --pid-file=$pid_file 
--log-error=$LOG_ERROR


However, I am still getting 2 error logs:

What I am assuming is the default "/usr/local/mysql/var/yoda2.err"
and the one specified - "/var/log/mysql/error.log"

Is this expected behavior?  Is there another place aside from /etc/my.cnf 
that I might look?


Thanks in advance for any tips and/or suggestions.

Ed

. . . . . . . . . . . . . . . . . .
Randomly Generated Quote (116 of 975):
"It had long since come to my attention that people of
 accomplishment rarely sat back and let things happen
 to them. They went out and happened to things."  - Elinor Smith


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

sleeping processes

[Ronny Melz]

   Dear all,

I have a problem with the mysql interface for c, which after a couple of
 hours thinking about bad or faulty programming are eliminated with
 probability almost 100%.

within a loop, I do a mysql_query(), which is executed and I poll the result
and everything is fine. Up to when max_connections of the mysqld is reached:
each of the queries leaves after executing a sleeping mysql process behind on
the server, which hence throws the "Too many connections" error (or under
certain conditions even a "Can't create TCP/IP socket (24)").

Just because of this problem I recently upgraded mysql (including the
libraries) from 4.0.21 to 4.1.12, but the problem still persists.

Does anybody have an idea of how to solve this problem?
Any suggestions are appreciated.

Best,
Ronny


p.s.: code
#define GET_W_NR \
"select wort_nr from wortliste where wort_bin='%s' limit 1"
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
char *query;

init...
  {
  mysql_init(&mysql);
  if ( !mysql_real_connect( &mysql, "127.0.0.1", "username", "passwd",
"dbase",
 0, "/var/lib/mysql/mysql.sock", 0 ) )
{
fprintf(stderr, "Failed: %s\n",mysql_error(&mysql));
}
  if (!(query = (char*) malloc(256*sizeof(char fprintf(stderr, "no
pointer");
  }

this code of a function is called in a loop and returns with a mysql_error 
after `max_connections` cycles:
{
sprintf(query, GET_W_NR, refWort);
if ( mysql_query( &mysql, query ) )
{
fprintf(stderr, "query failed: %s\n", mysql_error(&mysql));
return 0;
}
if ( !( res = mysql_store_result( &mysql ) ) )
{fprintf(stderr, "store failed: %s\n", mysql_error(&mysql)); return
0;}
if ( row = mysql_fetch_row( res ) )
ref_word_nr = atoi(row[0]);
else
{fprintf(stderr, "fetch failed: %s\n", mysql_error(&mysql)); return
0;}
mysql_free_result(res);
}

exit...
  free(query);
  mysql_close(&mysql);

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

Too many connections

[Ronny Melz]

   Dear all,

I have a problem with the mysql interface for c, which after a couple of
 hours thinking about bad or faulty programming are eliminated with
 probability almost 100%.

within a loop, I do a mysql_query(), which is executed and I poll the result
and everything is fine. Up to when max_connections of the mysqld is reached:
each of the queries leaves after executing a sleeping mysql process behind on
the server, which hence throws the "Too many connections" error (or under
certain conditions even a "Can't create TCP/IP socket (24)").

Just because of this problem I recently upgraded mysql (including the
libraries) from 4.0.21 to 4.1.12, but the problem still persists.

Does anybody have an idea of how to solve this problem?
Any suggestions are appreciated.

Best,
Ronny


p.s.: code
#define GET_W_NR \
"select wort_nr from wortliste where wort_bin='%s' limit 1"
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
char *query;

init...
  {
  mysql_init(&mysql);
  if ( !mysql_real_connect( &mysql, "127.0.0.1", "username", "passwd",
"dbase",
 0, "/var/lib/mysql/mysql.sock", 0 ) )
{
fprintf(stderr, "Failed: %s\n",mysql_error(&mysql));
}
  if (!(query = (char*) malloc(256*sizeof(char fprintf(stderr, "no
pointer");
  }

this code of a function is called in a loop and returns with a mysql_error 
after `max_connections` cycles:
{
sprintf(query, GET_W_NR, refWort);
if ( mysql_query( &mysql, query ) )
{
fprintf(stderr, "query failed: %s\n", mysql_error(&mysql));
return 0;
}
if ( !( res = mysql_store_result( &mysql ) ) )
{fprintf(stderr, "store failed: %s\n", mysql_error(&mysql)); return
0;}
if ( row = mysql_fetch_row( res ) )
ref_word_nr = atoi(row[0]);
else
{fprintf(stderr, "fetch failed: %s\n", mysql_error(&mysql)); return
0;}
mysql_free_result(res);
}

exit...
  free(query);
  mysql_close(&mysql);

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

Re: Access denied

[Gleb Paharenko]

Hello.



See:

  http://dev.mysql.com/doc/mysql/en/access-denied.html

http://dev.mysql.com/doc/mysql/en/resetting-permissions.html





Seena Blace <[EMAIL PROTECTED]> wrote:

> [-- text/plain, encoding 8bit, charset: iso-8859-1, 17 lines --]

> 

> Hi,

> 

> I have been noticing following error when trying to connect mysql.

> 

> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)

> or 

> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)

> 

> What could be reason?

> 

> Is there any way I can connect to database without changing passwd?

> thanks

> 

>

> -

> Discover Yahoo!

> Get on-the-go sports scores, stock quotes, news & more. Check it out!



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




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

Re: replication stopped

[Gleb Paharenko]

Hello.



Replication process runs tree threads, two on slave and one on master, so

if your master was running with no free space it's behavior unpredictable and

replication could halt. SHOW SLAVE STATUS usually reports errors. I would stop 
replication and setup it again using, for example,

LOAD DATA FROM MASTER. But if the data size is huge enough I would check

the master.info and relay-log.info and continue the replication from

where it had stopped. See:

  http://dev.mysql.com/doc/mysql/en/slave-logs.html









[EMAIL PROTECTED] wrote:

> 

> I took a couple of days vacation last week and naturally the /data partition 
> of my database server filled up because of something I was running and had 
> forgotten about.  So my backup guy, thinking to help out, gzipped all my .bin 
> logs.  Now I am noticing replication seems to have stopped on my other 
> server.  Not sure if the two events are related or not.  It could be that 
> things got all hosed up not because of the gzipping but because the partition 
> filled up in the first place?  Anyway, what do I do now?  Do I need to do a 
> new dump and start replication all over again?

> 

> --ja

> 



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




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

Re: mysql total replication question

[Gleb Paharenko]

Hello.



In my replication setup with 4.1.11 master and 5.0.4 slave

CREATE TABLE and CREATE DATABASE  statements are replicated

perfectly.







"Shannon R." <[EMAIL PROTECTED]> wrote:

> Hi All!

> 

> I'm using the latest mysql 4.0.x and I've successfully

> set-up database replication on it over 2 machines.

> 

> I have noticed though that mysql doesn't seem to

> replicate CREATE TABLE operations on the master to the

> slave. Is this a limitation? Or I just missed

> something. If so, can someone please give me a clue or

> point me to the right place documentations?

> 

> Also, is it possible to setup replication so that

> everything in the master is replicated to the slave,

> included CREATE DATABASE operations?

> 

> Many Thanks!

> Shannon

> 

> 

> __

> Do You Yahoo!?

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

> http://mail.yahoo.com 

> 



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




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

Too many connections

[Ronny Melz]

Dear all,

I have a problem with the mysql interface for c, which after a couple of
 hours thinking about bad or faulty programming are eliminated with
 probability almost 100%.

within a loop, I do a mysql_query(), which is executed and I poll the result
and everything is fine. Up to when max_connections of the mysqld is reached:
each of the queries leaves after executing a sleeping mysql process behind on
the server, which hence throws the "Too many connections" error (or under
certain conditions even a "Can't create TCP/IP socket (24)").

Just because of this problem I recently upgraded mysql (including the
libraries) from 4.0.21 to 4.1.12, but the problem still persists.

Does anybody have an idea of how to solve this problem?
Any suggestions are appreciated.

Best,
Ronny


p.s.: code
#define GET_W_NR \
"select wort_nr from wortliste where wort_bin='%s' limit 1"
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
char *query;

init...
  {
  mysql_init(&mysql);
  if ( !mysql_real_connect( &mysql, "127.0.0.1", "username", "passwd",
"dbase",
 0, "/var/lib/mysql/mysql.sock", 0 ) )
{
fprintf(stderr, "Failed: %s\n",mysql_error(&mysql));
}
  if (!(query = (char*) malloc(256*sizeof(char fprintf(stderr, "no
pointer");
  }

this code of a function is called in a loop and returns with a mysql_error 
after `max_connections` cycles:
{
sprintf(query, GET_W_NR, refWort);
if ( mysql_query( &mysql, query ) )
{
fprintf(stderr, "query failed: %s\n", mysql_error(&mysql));
return 0;
}
if ( !( res = mysql_store_result( &mysql ) ) )
{fprintf(stderr, "store failed: %s\n", mysql_error(&mysql)); return
0;}
if ( row = mysql_fetch_row( res ) )
ref_word_nr = atoi(row[0]);
else
{fprintf(stderr, "fetch failed: %s\n", mysql_error(&mysql)); return
0;}
mysql_free_result(res);
}

exit...
  free(query);
  mysql_close(&mysql);

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

mysql total replication question

[Shannon R.]

Hi All!

I'm using the latest mysql 4.0.x and I've successfully
set-up database replication on it over 2 machines.

I have noticed though that mysql doesn't seem to
replicate CREATE TABLE operations on the master to the
slave. Is this a limitation? Or I just missed
something. If so, can someone please give me a clue or
point me to the right place documentations?

Also, is it possible to setup replication so that
everything in the master is replicated to the slave,
included CREATE DATABASE operations?

Many Thanks!
Shannon


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

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

Re: mysql UNION

[mfatene]

The second method is dynamic sql with prepare statement from string.

It's better for the reason that the query is generated to retrieve data from
just one table (not an union which implies 3 tables).

The day your tables will be huge, i'm sure you will use the second method.

two crazy people can find a method because only one hand can't aplaude !

Mathias

Selon Sebastian <[EMAIL PROTECTED]>:

> Hi, your second method is probably a little too confusing (advanced) for
> me to understand.
> I used your first method which works fine.. thanks for the crazy stuff,
> somtimes you need two crazy people to come up with a solution ;)
>
> [EMAIL PROTECTED] wrote:
>
> >Hi Sebastian;
> >There is always crazy things somewhere.
> >I'll give you two methods for that :
> >
> >mysql> select id,'news' as selected, type from news
> >-> union select id,'faq' as selected, type from faq
> >-> union select id,'forum' as selected, type from forum;
> >+--+--+---+
> >| id   | selected | type  |
> >+--+--+---+
> >|1 | news | news  |
> >|2 | faq  | faq   |
> >|3 | forum| forum |
> >+--+--+---+
> >3 rows in set (0.00 sec)
> >
> >
> >FIRST CRAZY METHOD :
> >*
> >mysql> set @cat='news';
> >Query OK, 0 rows affected (0.00 sec)
> >
> >mysql> select * from (
> >-> select id,'news' as selected, type from news
> >-> union select id,'faq' as selected, type from faq
> >-> union select id,'forum' as selected, type from forum
> >-> ) Temp
> >-> where [EMAIL PROTECTED];
> >+--+--+--+
> >| id   | selected | type |
> >+--+--+--+
> >|1 | news | news |
> >+--+--+--+
> >1 row in set (0.00 sec)
> >
> >
> >SECOND CRAZY METHOD (I prefer):
> >*
> >
> >
> >set @cat := 'news';
> >set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
> >select @sql;
> >prepare stmt from @sql ;
> >execute stmt;
> >
> >+--+--+
> >| id   | selected |
> >+--+--+
> >|1 | news |
> >+--+--+
> >1 row in set (0.00 sec)
> >
> >deallocate prepare stmt;
> >
> >
> >* another click with ?cat=faq
> >
> >set @cat := 'faq';
> >set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
> >select @sql;
> >prepare stmt from @sql ;
> >execute stmt;
> >
> >mysql> execute stmt;
> >+--+--+
> >| id   | selected |
> >+--+--+
> >|2 | faq  |
> >+--+--+
> >1 row in set (0.00 sec)
> >
> >deallocate prepare stmt;
> >
> >
> >
> >
> >OTHER CRAZY METHODS - coming emails :o)
> >
> >
> >
> >A+
> >Mathias
> >
> >
> >
> >Selon Sebastian <[EMAIL PROTECTED]>:
> >
> >
> >
> >>Michael Stassen wrote:
> >>
> >>
> >>
> >>>Sebastian wrote:
> >>>
> >>>
> >>>
> i have a query with 3 union selects:
> 
>    SELECT id, 'news' AS type,  FROM news
> 
>    UNION
>  SELECT id, 'faq' AS type,  FROM faq
> 
>    UNION
> 
>    SELECT id, 'forum' AS type,  FROM forum
> 
> which works just fine and selects everything from all 3 tables.. but
> say i want to make a condition to only select from either 'faq' ,
> 'news' or 'forum' how can i do this?
> 
> example, if a user visits a link suck as: page.php?cat=faq it will
> only select from 'faq' .. is this possible to do right in the query?
> when there is no ?cat= then all three selects run.
> 
> makes sense? i am stuck on this for a few days already.
> thanks.
> 
> 
> 
> >>>Why don't you do this in your app?  If cat is set, issue the
> >>>appropriate single-table query, otherwise issue the union.  Surely
> >>>that would be simpler than trying to build one multi-purpose query.
> >>>
> >>>Michael
> >>>
> >>>
> >>I was hoping i could do some crazy thing like WHERE type = 'faq' so i
> >>can do it all from one block of code.
> >>
> >>
> >>
> >>
> >>
> >>
>



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

Re: data directory for mysql

[Andras Kende]

> Hi all,
>
> I have been using /var/mysql/data as my data directory till now ,
> suddenly i realise its all filled up with some 20M left which will
> finish in a day or two , repartioning my disks is not an option , what
> do i do to restart mysql with a new data directory say
> /usr/local/mysql/data/: changing the file my.cnf is not helping in my
> case, the data is going to the same directory again and not the one i
> specified in my.cnf
>
> TIA
>
>
> Aut disce Aut Discede Aut Vincere Aut Mori
> Either learn or leave Either conquer or die
> Digz
>
>
>

You could also jut move the data to /usr/local/mysql/data/
and create a symbolic link to it from /var/mysql/data

Best regards,

Andras Kende
http://www.kende.com


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