file privilege
hi... so i have this user in the user table in the mysql db that has all the privileges but grant. now i need for that user to have the file privilege so it can do into outfile. thing is i don't see that privilege anywhere in the db table. i know that i have to do the grant file on user@localhst thing but what table does that write to? also if i have all the other privileges granted and just need to add the file one, do i have to re-grant all the rest of those or it'd just add the file privilege to all the already granted ones? the tables_priv, columns_priv and procs_priv don't have any file privilege listed. this is the list of privileges i can see off the db table: | Select_priv | enum('N','Y') | Insert_priv | enum('N','Y') | Update_priv | enum('N','Y') | Delete_priv | enum('N','Y') | Create_priv | enum('N','Y') | Drop_priv | enum('N','Y') | Grant_priv| enum('N','Y') | References_priv | enum('N','Y') | Index_priv| enum('N','Y') | Alter_priv| enum('N','Y') | Create_tmp_table_priv | enum('N','Y') | Lock_tables_priv | enum('N','Y') | Create_view_priv | enum('N','Y') | Show_view_priv| enum('N','Y') | Create_routine_priv | enum('N','Y') | Alter_routine_priv| enum('N','Y') | Execute_priv | enum('N','Y') | Event_priv| enum('N','Y') | Trigger_priv | enum('N','Y') thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: file privilege
thanks carlos. so if i go with: grant file on user that wont infringe on the all other privileges the user already has, correct? thanks.. On 1/31/12 12:47 AM, Carlos Proal wrote: Hi, You have look at the user table which has global privileges, db is specific to each database. Carlos On Mon, Jan 30, 2012 at 10:56 PM, kalin m ka...@el.net mailto:ka...@el.net wrote: hi... so i have this user in the user table in the mysql db that has all the privileges but grant. now i need for that user to have the file privilege so it can do into outfile. thing is i don't see that privilege anywhere in the db table. i know that i have to do the grant file on user@localhst thing but what table does that write to? also if i have all the other privileges granted and just need to add the file one, do i have to re-grant all the rest of those or it'd just add the file privilege to all the already granted ones? the tables_priv, columns_priv and procs_priv don't have any file privilege listed. this is the list of privileges i can see off the db table: | Select_priv | enum('N','Y') | Insert_priv | enum('N','Y') | Update_priv | enum('N','Y') | Delete_priv | enum('N','Y') | Create_priv | enum('N','Y') | Drop_priv | enum('N','Y') | Grant_priv| enum('N','Y') | References_priv | enum('N','Y') | Index_priv| enum('N','Y') | Alter_priv| enum('N','Y') | Create_tmp_table_priv | enum('N','Y') | Lock_tables_priv | enum('N','Y') | Create_view_priv | enum('N','Y') | Show_view_priv| enum('N','Y') | Create_routine_priv | enum('N','Y') | Alter_routine_priv| enum('N','Y') | Execute_priv | enum('N','Y') | Event_priv| enum('N','Y') | Trigger_priv | enum('N','Y') thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MYD, MYI and TMD files
thanks. it took about 15 hrs. but right now everything seems ok. does the check last as long as the repair? On 1/24/12 3:51 AM, Johan De Meersman wrote: - Original Message - From: kalin m ka...@el.net ok. thanks. i thought so. it's been about 8 hrs so far. and the TMD file is about half the MYD/MYI file. although MYI is about a gig smaller than the MYD. which one has the TDM to reach in size in order for the repair to be complete? The MYI file contains only indices, which are pretty much disposable. The MYD contains the actual data, and is what myisamcheck will be reading in order to salvage as much data as possible. It will basically try to copy every record into the temp file, then switch out the files and do a full reindexing run. It's not a bad idea to have a copy of your original MYD file, just in case the repair doesn't quite yield what you'd hoped. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MYD, MYI and TMD files
hey. this list used to be pretty active. did anything change? is there another place to ask questions like these? thanks... On 1/23/12 5:02 PM, kalin m wrote: hi all.. i started a repair on a table that has an MYD file of 9.2 gigs. the MYI file is 7.7 gigs. the TMD file, which i'm assuming is a temporary file used in the repair process, is currently 400mb. this has been going on for about 30 min now. the question is does the TMD file need to get anywhere near the size of any of this MYD or MYI files in order to get this repair done?! and if so why is it so slow? i'm not really planning to stop the repair command cause i read somewhere that this might not be a good idea. can i stop it without risking any data loss or table damage? thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MYD, MYI and TMD files
ok. thanks. i thought so. it's been about 8 hrs so far. and the TMD file is about half the MYD/MYI file. although MYI is about a gig smaller than the MYD. which one has the TDM to reach in size in order for the repair to be complete? thanks. On 1/23/12 11:27 PM, Michael Dykman wrote: This is still the list although more quiet than it used to be. Repairing a table is already a fragile process.. I would not try to interrupt it if the data has no backup. - michael dykman On Mon, Jan 23, 2012 at 11:04 PM, kalin m ka...@el.net wrote: hey. this list used to be pretty active. did anything change? is there another place to ask questions like these? thanks... On 1/23/12 5:02 PM, kalin m wrote: hi all.. i started a repair on a table that has an MYD file of 9.2 gigs. the MYI file is 7.7 gigs. the TMD file, which i'm assuming is a temporary file used in the repair process, is currently 400mb. this has been going on for about 30 min now. the question is does the TMD file need to get anywhere near the size of any of this MYD or MYI files in order to get this repair done?! and if so why is it so slow? i'm not really planning to stop the repair command cause i read somewhere that this might not be a good idea. can i stop it without risking any data loss or table damage? thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update and times
Simcha Younger wrote: On Mon, 04 Oct 2010 16:11:08 -0400 kalin m ka...@el.net wrote: what i'm trying to do is update the column only of one of those times isn't yet passed. and it works. except sometimes... like these 2 unix times: this was in the table under unix time: 12862162385941345... this 12862162510269684 got passed in the update command as in: update the_table set updated = 1 where unix_time 12862162510269684 limit 1; executing this query didn't update the record. why? The two values you have here are equal: sample data : 12862162510269684 query: where unix_time 12862162510269684 and therefore the 'less than' query did not match that row. sorry... not following the value in the table was 12862162385941345. the time in the query was 12862162510269684. mysql select 12862162385941345 12862162510269684; +---+ | 12862162385941345 12862162510269684 | +---+ | 1 | +---+ 1 row in set (0.00 sec)
update and times
hi all... i'm doing tests with a table that gets updated based on random unix times it contains. there is a column that has a bunch or random times that look like: +-+---+ | date_time | unix_time| +-+---+ | 2010-10-01 10:24:52 | 12859430921341418 | | 2010-10-01 21:18:13 | 12859822937839442 | | 2010-10-01 16:08:00 | 12859636809115039 | | 2010-10-01 19:47:43 | 12859768633824661 | | 2010-10-01 16:48:30 | 12859661104829142 | | 2010-10-01 15:25:37 | 12859611374324533 | | 2010-10-01 12:27:28 | 12859504483288358 | +-+---+ what i'm trying to do is update the column only of one of those times isn't yet passed. and it works. except sometimes... like these 2 unix times: this was in the table under unix time: 12862162385941345... this 12862162510269684 got passed in the update command as in: update the_table set updated = 1 where unix_time 12862162510269684 limit 1; executing this query didn't update the record. why? thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: update and times
right the unix times in the example table were just that - examples from a few days ago... the example with the query was a 'real one' something that happened today... it's a 64 bit machine. the unix times are stored in a bigint column. the times in the column and the update statement are the same length... here is the information for that column/table: +---+-+--+-+---++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+---++ | id| int(10) unsigned| NO | PRI | NULL | auto_increment | | normal_time| datetime | NO | | || | unix_time| bigint(20) unsigned | NO | UNI | 0 || | updated | tinyint(1) | NO | | 0 || +---+-+--+-+---++ at the end what is compared is two long int values - 12862162510269684 and 12862162385941345 in the update query Gavin Towey wrote: Those unix_time values don't seem to correspond to the dates you have. select NOW(), UNIX_TIMESTAMP(NOW()); +-+---+ | NOW() | UNIX_TIMESTAMP(NOW()) | +-+---+ | 2010-10-04 13:18:08 |1286223488 | +-+---+ 1286223428 vs 12862162510269684 Your value has far too many digits. That's also beyond the range of a 32 bit int. Are you using BIGINT, or VARCHAR? I suspect the issues is due because of something different about the values you have in your table. Try posting the SHOW CREATE TABLE table \G output, and a sample INSERT statement to populate the table. That way someone can try to reproduce the behavior you're seeing. -Original Message- From: kalin m [mailto:ka...@el.net] Sent: Monday, October 04, 2010 1:11 PM To: [MySQL] Subject: update and times hi all... i'm doing tests with a table that gets updated based on random unix times it contains. there is a column that has a bunch or random times that look like: +-+---+ | date_time | unix_time| +-+---+ | 2010-10-01 10:24:52 | 12859430921341418 | | 2010-10-01 21:18:13 | 12859822937839442 | | 2010-10-01 16:08:00 | 12859636809115039 | | 2010-10-01 19:47:43 | 12859768633824661 | | 2010-10-01 16:48:30 | 12859661104829142 | | 2010-10-01 15:25:37 | 12859611374324533 | | 2010-10-01 12:27:28 | 12859504483288358 | +-+---+ what i'm trying to do is update the column only of one of those times isn't yet passed. and it works. except sometimes... like these 2 unix times: this was in the table under unix time: 12862162385941345... this 12862162510269684 got passed in the update command as in: update the_table set updated = 1 where unix_time 12862162510269684 limit 1; executing this query didn't update the record. why? thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
subquery multiple rows
hi all... i have a bit of a problem with this: table products: -- prod | category | -| boots | winter| boots | summer | boots | spring | shoes | spring | shoes | winter| shoes | fall | shoes | summer | -- when i do this: select distinct prod as m, (select category from products where email = m) as n from products; i get: ERROR 1242 (21000): Subquery returns more than 1 row i know that the subquery returns more than one rows. i hope so... what i'd like to see as result is: - m | n | - boots | winter, summer, spring | shoes | spring, winter, fall , summer | - or at least: --- m | n | --- boots | 3 | shoes | 4 | thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: subquery multiple rows
yea.. almost. but it helped a lot. now i know about those functions too. thank you... Nathan Sullivan wrote: I think you want to do something like this: select prod, group_concat(category separator ', ') from products group by prod; Hope this helps. On Wed, Apr 07, 2010 at 08:37:04AM -0700, kalin m wrote: hi all... i have a bit of a problem with this: table products: -- prod | category | -| boots | winter| boots | summer | boots | spring | shoes | spring | shoes | winter| shoes | fall | shoes | summer | -- when i do this: select distinct prod as m, (select category from products where email = m) as n from products; i get: ERROR 1242 (21000): Subquery returns more than 1 row i know that the subquery returns more than one rows. i hope so... what i'd like to see as result is: - m | n | - boots | winter, summer, spring | shoes | spring, winter, fall , summer | - or at least: --- m | n | --- boots | 3 | shoes | 4 | thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
user expires?
hi all... i had a weired thing happened is it possible for a user privileges to expire?! suddenly today an application stopped working and i was getting the message that the user can't login. now, i did mess with it last night trying to give it file privileges but since i would have to give it file privileges to everything (doesn't make sense) i didn't do it. i did try thought with: mysql grant file to the [EMAIL PROTECTED] etc. it didn't work and i left it alone. didn't flush any privileges. it appears all the privileges were taken off the user anyway?! i just did grant all again and it seems to be working again... whats up with that?!??! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select into outfile php problem
hi all... i have a strange problem with a simple script that is doing select into outfile... the thing just does that. it does a select into an outfile. if i print the query that's passed to mysql_result in the script and then copy it and paste it z`into the mysql client it works fine but it doesn't do anything within the script. there are no error messages, no error logs whatsoever on the php side or mysql error log. checked permissions too. i even set them up to 777 to make sure it's not that. it still doesn't do it. i also tested as root. and root obviously has the File privilege there too... what else can it be? the same script works with no problems on a different machine. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if count
right null did it... thanks... Leonardus Setyabudi wrote: try something like : count(if(a.type = 'SBR', 1, null)) count will ignore null value .. and only count the one with 'SBR' value br, Leo On 24/09/08 12:09, kalin m wrote: no, not really... sum is part of the query: count(if(a.Type = SBR, a.amount,'')), sum(a.amount) group by.. i want this: go through the records. count only the ones the have type = SBR sum all amounts etc... etc... group by date with rollup i.e. i'd like to (assuming in the above example that _type_ and _amount_ are column names) sum all the amounts but count only the ones with a certain type, all other amounts that are different type should not be part of the count, but still should be summed.. how can i do that?! thanks Perrin Harkins wrote: On Tue, Sep 23, 2008 at 9:29 PM, kalin m [EMAIL PROTECTED] wrote: count(if(a.Type = Signature Based Return, a.amount,'')) group by order by I think you're looking for sum(). - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
if count
hi all... can somebody explain why a conditional count like this one doesn't work: count(if(a.Type = Signature Based Return, a.amount,'')) group by order by or if(a.Type = Signature Based Return, count(a.amount),'') group by order by... thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if count
no, not really... sum is part of the query: count(if(a.Type = SBR, a.amount,'')), sum(a.amount) group by.. i want this: go through the records. count only the ones the have type = SBR sum all amounts etc... etc... group by date with rollup i.e. i'd like to (assuming in the above example that _type_ and _amount_ are column names) sum all the amounts but count only the ones with a certain type, all other amounts that are different type should not be part of the count, but still should be summed.. how can i do that?! thanks Perrin Harkins wrote: On Tue, Sep 23, 2008 at 9:29 PM, kalin m [EMAIL PROTECTED] wrote: count(if(a.Type = Signature Based Return, a.amount,'')) group by order by I think you're looking for sum(). - Perrin
conditional sum
hi... how do i do conditional sums? like: select a.job, sum(if b.amount 0 then amount end if ) from t1 as a left join t2 as b on a.account=b.accoun where a.account = b.account group by a.job; or select a.job, if b.amount 0 then sum(b.amount) end if from t1 as a left join t2 as b on a.account=b.accoun where a.account = b.account group by a.job; or something like it. it's be awesome if it can be done... thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: conditional sum
i got closer but i can't figure out this: individually: A) select a.job, sum(b.money) from t1 as a left join t2 as b on a.account = b.account where a.job = ca1 and b.money 0; +--+---+ | job | sum(b.money) | +--+---+ | ca1| 1464 | +--+---+ B) select a.job, sum(b.money) from t1 as a left join t2 as b on a.account = b.account where a.job = ca1 and b.money 0; +--+---+ | job| sum(b.money) | +--+---+ | ca1 | -129 | +--+---+ BUT if i put both together: select a.job, sum(b.money), sum(c.money) from t1 as a left join t2 as b on a.account = b.account left join t2 as c on a.account = c.account where a.job = ca1and c.money 0 and b.money 0; i get: +--+---+---+ | job| sum(b.money) | sum(c.money) | +--+---+---+ | ca1 |180 | -129 | +--+---+---+ it seems the condition c.money 0 is respected but the b.money 0 is acting weired... i changed the order but that's not it. and the 1464 is the correct number for b.money... any ideas?!. thanks. kalin m wrote: hi... how do i do conditional sums? like: select a.job, sum(if b.amount 0 then amount end if ) from t1 as a left join t2 as b on a.account=b.accoun where a.account = b.account group by a.job; or select a.job, if b.amount 0 then sum(b.amount) end if from t1 as a left join t2 as b on a.account=b.accoun where a.account = b.account group by a.job; or something like it. it's be awesome if it can be done... thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql cli 5.0.51b
hi all... i just build from source 5.0.51b on a freebsd 7... works fine. but the cli displays \40 for white space on remembered commands. like in flush\040privileges;. pretty annoying specially for long ones. how to fix? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql cli 5.0.51b
i use the cli exclusively... how can i fix this?! kalin m wrote: hi all... i just build from source 5.0.51b on a freebsd 7... works fine. but the cli displays \40 for white space on remembered commands. like in flush\040privileges;. pretty annoying specially for long ones. how to fix? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql cli 5.0.51b
the \40 is all over the .mysql_history?!? kalin m wrote: i use the cli exclusively... how can i fix this?! kalin m wrote: hi all... i just build from source 5.0.51b on a freebsd 7... works fine. but the cli displays \40 for white space on remembered commands. like in flush\040privileges;. pretty annoying specially for long ones. how to fix? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database for a library
assuming this is for books/pages to be displayed on line... how about if you just keep just important stuff in mysql like chapter titles and how many pages the book has, author, edition, etc then you keep files for each page (or book) in the filesystem - xml, txt, etc - and you just make a pagination navigation (and styling) depending of the book's number of pages, chapters, etc... since book's contents don't change you don't need that as dynamic data if they have to download the whole book make them into pdfs. or something Khaled al-Horani wrote: Hello, I'm building a web application for a library but I faced a problem ... NOTICE: This web app includes a feature that you can read the books online ... I have to save the books either in the database (mysql for sure) or on the hard disks ... but I preferred DB choice because of many befits (search, speed, time and many others) ... The problem is how to save the book, till now I have three choices: 1- save the whole book in one record of type long text (downside is the bandwidth ,,, it requires a lot of bandwidth to transfer the whole book to the user) 2- save each chapter of each book in a record 3- save each page of each book in one record (the downside is the huge amount of records but the benefits are saving bandwidth and easier pagination) I prefer the third choice but is mysql capable of handling this huge amount of records (it maybe be millions because I have over 10,000 book) thanks in advanced -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 5.0.51b and ssl
hi all... i just installed openssl 0.9.8h and trying to build mysql 5.0.51b with it on a freebsd 7 machine. i get this: /usr/bin/ld: /usr/local/ssl/lib/libssl.a(t1_srvr.o): relocation R_X86_64_32 can not be used when making a shared object; recompile with -fPIC /usr/local/ssl/lib/libssl.a: could not read symbols: Bad value any ideas? thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5.0.51b and ssl
from what i understand this is not uncommon. and it goes back to 2005. and it has to do with the fact that the machine identifies itself as amd64 even though it's an intel machine. what's not clear is what needs to be recompiled whit -fPIC?! my guess is openssl needs to. why? are there any flags i can change in the Makefile for the mysql build? does it have to be a 'shared'? static?! thanks... kalin m wrote: hi all... i just installed openssl 0.9.8h and trying to build mysql 5.0.51b with it on a freebsd 7 machine. i get this: /usr/bin/ld: /usr/local/ssl/lib/libssl.a(t1_srvr.o): relocation R_X86_64_32 can not be used when making a shared object; recompile with -fPIC /usr/local/ssl/lib/libssl.a: could not read symbols: Bad value any ideas? thanks... ___ [EMAIL PROTECTED] mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
indexes and speeds
hi all... just wondering what is the performance difference between: PRIMARY KEY [/|index_type|/] (/|index_col_name|/1,/|index_col_name|/2) at the time of the table creation or create index index_name1 on table_name (/|index_col_name|/1); create index index_name2 on table_name (/|index_col_name|/2); after the table has been made? the question i guess is: is there performance advantage to have a primary key defined on two (or more) fields (columns) at the time of table creation or is it better to have different indexes (keys) defined separately for each column that needs to be indexed? thanks
default my.cnf?
hi all... i have a 5.0.33 build from source on a freebsd 4.10 machine... i'm looking for a my.cnf file. ps tells me that the base dir is /usr/local but there is no my.cnf there. and i cant find one anywhere. i can get all the variables set up from the cli but i need to change some of them. i guess i can use mysqladmin but just wondering - can i just do a cnf under /usr/local/etc with the variables i need to change? ktrace is showing me that mysqld is not looking for any .cnf?! i mean i can try that but it's a very busy server and i wouldn't mess to much with it. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]