data directory for mysql
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 -- 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
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. Try SELECT IFNULL(title, topic) AS heading -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
If statment in query
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql UNION
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: mysql UNION
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]
>
>
--
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 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: mysql UNION
Couldn't you just test "page.php?cat=faq" with an if-then-else? Depending on what is the GET variable you then can change the SQL, easy w/PHP. On Sat, 04 Jun 2005 14:15:40 -0400, "Sebastian" <[EMAIL PROTECTED]> said: > 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. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- http://www.fastmail.fm - Faster than the air-speed velocity of an unladen european swallow -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql UNION
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql UNION
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design of a Client-side MySQL Java Load Balancer
image ? you're modifying row n, node x wants to modify it, you need a rollback segment for that ! which value node x will read. i'm considering every DML is a transaction in an RDBMS, so when you say that you don't use transactions, you miss something. about load balancing, how do you track long operations and decide to migrate a transaction to another node ? have you any benchmarks with say 4 nodes , 100 transactions terminating in different times with a a protocol making a first node crash, so a seconf, so a restart of one of them, during the 100 transactions, and each node load ? Selon Kevin Burton <[EMAIL PROTECTED]>: > [EMAIL PROTECTED] wrote: > > >Hi, > >i think that client load-balacer are more Dispatchers than real load > balancer. > > > >load balancing in the database side takes care to number of connections, but > >also node load. So thisis more real. But this issue is difficult. > > > > > > > No... you're making assumptions. With the two-phase protocol I > developed the nodes cooperate and distribute load and connections. They > also handle failover. > > Simply put I can do a better job than hardware balancers because I > already KNOW what MySQL can do. Most load balancers are dumb. > > >even for oracle with 9iRAC and 10gRAC, load balancing is not completely > >controled. > > > >you speak abot load balancing and introduce also the failover notion, which > >isnot a load balancing concept. Fail over is difficult because controling it > >implies that every node must have the image before of every transaction. > > > > > > > Image? > > Failover isn't a load balancing concept? Not according to our hardware > vendor :) > > >With cache fusion, ora > > > > cle RAC gives a solution, but assumes failover only fo select > statements. All DML statements are lost if a > > node is lost. > > The DML situation here is a tough one. For SELECTS I have no problem > with failover. For DML I would have no problem unless you're in a > transaction. > > We don't use transaction and I think they're evil anyway. > > Kevin > > -- > > > Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. > See irc.freenode.net #rojo if you want to chat. > > Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html > >Kevin A. Burton, Location - San Francisco, CA > AIM/YIM - sfburtonator, Web - http://peerfear.org/ > GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compile error
Hello. Usually compiling manually is not a good idea, if there are official binary for your platform available. Ensure that you have the fresh bison. Mir Islam <[EMAIL PROTECTED]> wrote: > I am stumped with this compile problem with 4.0.24 src. Not sure what > is causing it. Can anyone provide some insights? Last time I compiled > on the same environment was 4.0.20 source. And all was ok. > > gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=3D"\"/ms/svc/mysql/4.0.24\"" > -DDATADIR=3D"\"/ms/data/mysql/4.0.24\"" > -DSHAREDIR=3D"\"/ms/svc/mysql/4.0.24/share/mysql\"" -DHAVE_CONFIG_H -I. > -I. -I.. -I../innobase/include -I./../include -I./../regex -I. > -I../include -I. -O3 -DDBUG_OFF -O3 -felide-constructors > -fno-exceptions -fno-rtti -mcpu=3Dv8 -Wa,-xarch=3Dv8plusa =20 > -fno-implicit-templates -fno-exceptions -fno-rtti > -D_FILE_OFFSET_BITS=3D64 -DHAVE_CURSES_H > -I/netapp/home/mislam/devel/database/mysql40/include -DHAVE_RWLOCK_T > -fno-inline -c sql_yacc.cc > /usr/local/share/bison.simple: In function `int yyparse()': > /usr/local/share/bison.simple:347: `YYSIZE_T' undeclared (first use > this function) > /usr/local/share/bison.simple:347: (Each undeclared identifier is > reported only once > /usr/local/share/bison.simple:347: for each function it appears in.) > /usr/local/share/bison.simple:347: parse error before `;' > make[3]: *** [sql_yacc.o] Error 1 > make[3]: Leaving directory `/netapp/home/mislam/devel/database/mysql40/sql' > make[2]: *** [install-recursive] Error 1 > make[2]: Leaving directory `/netapp/home/mislam/devel/database/mysql40/sql' > make[1]: *** [install] Error 2 > make[1]: Leaving directory `/netapp/home/mislam/devel/database/mysql40/sql' > make: *** [install-recursive] Error 1 > -- 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: recovery question
Gleb Paharenko wrote: > Hello. > > REPAIR TABLE ... USE_FRM helps in difficult cases. See: > http://dev.mysql.com/doc/mysql/en/repair-table.html > Thanks Gleb. I'd forgotten about that option. To others who try the same thing - make sure you have enough space in your TMPDIR or set TMPDIR/--tmpdir to a place where you have sufficient space. I started the REPAIR, which ran for a while, then stopped and appeared to be idling. It took me a few hours before I checked the mysqld.log and found out that it had run out of space in /tmp and was waiting for some to be cleared up. -- /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find random records in a subset?
select LIMIT 50; mathias Selon Brian Dunning <[EMAIL PROTECTED]>: > I am using a routine to find 50 random records in a large MySQL > database (about a million records) where I generate a list of 50 > random unique ID's, and then use MySQL's "in" command to find them. I > can't use "order by rand()" due to its performance hit. > > But I have to take it one more step: I want to first limit my found > set to those matching a different search criteria, and then find 50 > of those. > > Anyone? Can this be done all within MySQL, or is it going to require > some humongo PHP arrays? > > -- > 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]
