Sql - Problem with Left Join
You are taking the INNER JOIN with caddrescontactperson. Presumably, you need something like this: Select A.DepartmentName,A.Address,P.Postcode,P.cityname, CP.firstname from caddress A,cpostinfo P left Join CContactPerson CP on CP.ID =1001 left join caddresscontactperson CACP on CACP.ContactpersonID=CP.ID and CACP.AddressID=A.ID left join ccontactinfo CCI on CACP.ID=CCI.AddressContactPersonID and CCI.AddressID=-1 and CCI.ContactInfoTypeID=1 where A.ID=10 and A.PostInfoID=P.ID > Date: Wed, 12 Nov 2003 23:20:10 +0100 (CET) > Subject: Sql - Problem with Left Join > From: "Kim G. Pedersen" <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Hello > the little Query1 works until I add the left join : > Query 1 ) > Select A.DepartmentName,A.Address,P.Postcode,P.cityname > from caddress A,cpostinfo P > there A.ID=10 and A.PostInfoID=P.ID > Query 2) > Select A.DepartmentName,A.Address,P.Postcode,P.cityname, > CP.firstname > left Join CContactPerson CP , caddresscontactperson CACP ,ccontactinfo > CCI on CP.ID =1001 and CACP.ContactpersonID=CP.ID and > CACP.AddressID=A.ID and CACP.ID=CCI.AddressContactPersonID and > CCI.AddressID=-1 and CCI.ContactInfoTypeID=1 > from caddress A,cpostinfo P > there A.ID=10 and A.PostInfoID=P.ID > Query1 return : > -> company , streeet ,2000,copenhagen > Query2 return : > -> Empty > I know that the jeft join will give zero result , > since the table caddresscontactperson are empty. > But I do not Understand Why I not get > -> company , streeet ,2000,copenhagen , NULL > Normally a left join should not effect ur result. > Hope Someone can give me clue > using Mysql 4.1.0 > regards > Kim G. Pedersen > macaos/elprint Development -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Synchronization and replication of two MYSQL databases
Try SQLyog Job Agent at http://www.webyog.com Karam --- Prashant Akerkar <[EMAIL PROTECTED]> wrote: > Dear friends > > We have our application with data tier as mysql > database in our office > running on windows platform which we need to > synchronize with the same > database on a remote system at data centre on Linux. > > > Awaiting your reply at the earliest, > > Thanks & Regards, > Prashant A > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking Row
I am a bit of a newbie to MySQL so there may be an easy answer, but if I have a multi-threaded application that is accessing a MySQL database is there a way to lock a row in a table so that only one thread can alter an entry? For instance a way to do a query and then cycle thru the recordset by first locking the row (so I know no other thread is altering it) and then doing what I need to do and then unlocking it?
sorting using substring
I am trying to write a query that will sort results using one or two substrings in item number. So for example I have item numbers that start with SE, TS, N0, W00, etc. So let say I want results sorted in the following order TS, SE, N0, W0 I have tried the following in my query but it's not sorting properly: ORDER BY FIELD(LEFT(item_number, 2) , 'TS'), FIELD(LEFT(item_number, 1) 'N','W') Any suggestions. I am using MYSQL 3.23 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of MySQL children ?
On Wed, Nov 12, 2003 at 08:53:50PM -0500, George Webb wrote: > > # top > 8:45pm up 6 days, 3:52, 8 users, load average: 0.06, 0.17, 0.17 > 84 processes: 82 sleeping, 2 running, 0 zombie, 0 stopped > CPU states: 6.6% user, 5.4% system, 0.0% nice, 87.9% idle > Mem:25468K av, 24268K used,1200K free, 0K shrd,2544K buff > Swap: 65528K av, 28484K used, 37044K free8604K cached > > PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND > 5045 mysql 10 0 10384 1336 660 S 0.1 5.2 0:26 mysqld > 5037 mysql 9 0 10384 1336 660 S 0.0 5.2 0:11 mysqld > 5039 mysql 9 0 10384 1336 660 S 0.0 5.2 0:07 mysqld > 5040 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld > 5041 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld > 5042 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld > 5043 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld > 5044 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld > 5046 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld > 5047 mysql 9 0 10384 1336 660 S 0.0 5.2 0:03 mysqld > 5012 root 9 0 1844 0 S 0.0 0.0 0:00 mysqld_safe > > So isn't each of the ten (10) processes using 10384 K, for a total of > 103840 K? Uhm, no. The "size" is 10384k, but each thread proc is using 1336, and 660 of that is shared. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 60 days, processed 2,279,766,163 queries (438/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking a row
I am a bit of a newbie to MySQL so there may be an easy answer, but if I have a multi-threaded application that is accessing a MySQL database is there a way to lock a row in a table so that only one thread can alter an entry? For instance a way to do a query and then cycle thru the recordset by first locking the row (so I know no other thread is altering it) and then doing what I need to do and then unlocking it?
RE: GRANT TO SELECTED COLUMNS
Hi Chris/Lists, Thanks, I'll take note of your comments. As per Paul D, probably the error is by not naming the specific columns in the SELECT query. SELECT * will give an error since testuser has no access for the other columns... Thanks Again, Richard "Chris" <[EMAIL PROTECTED]>13/11/2003 11:35 AM To: "Mysql List" <[EMAIL PROTECTED]> cc: (bcc: BORNAY Richard/Engr/STATS/ST Group) Subject: RE: GRANT TO SELECTED COLUMNS You can't login as testuser and try to give yourself privileges, it just doesn't work. GRANT SELECT ON DATABASE.CHECK_SBIN TO testuser IDENTIFIED BY 'password' Put an entry into mysql.user, with the host set to '%' [Any host, are you sure that's what you want?] and all privileges set to 'N'. It also put an entry into mysql.tables_priv (testuser@'%') giving testuser Select privileges on the table DATABASE.CHECK_SBIN [Note, this gives testuser select privileges to the whole table, so it doesn't even check column level Select privileges] GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser Added the colum,n Select privilege to mysql.tables_priv.Column_priv and add an entry into mysql.columns_priv for testuser@'%' giving access to the specified fields. Since the testuser has Select privileges on the whole table, you get all the columns. So all your troubles came from running the first query, giving testuser Full select privileges on the table. running this query as root should fix your problem: REVOKE SELECT ON DATABASE.CHECK_SBIN FROM testuser; if that doesn't work you might try unsetting the Select privilege FROM mysql.tables_priv.Table_priv WHERE User='testuser' AND Host='%' Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: RE: GRANT TO SELECTED COLUMNS Hi Chris/Lists, In fact, I was able to login using both ways, here's what I did. 1. mysql -u testuser -ptestuser -hlocalhost DATABASE >> OK 2. In the mysql prompt, I issue the query 'SELECT * FROM CHECK_SBIN' >> Access denied ** This is were I'm getting the error Access denied for user 'testuser' Thanks, Richard "Chris" <[EMAIL PROTECTED]>13/11/2003 10:34 AM To: "Mysql List" <[EMAIL PROTECTED]> cc: (bcc: BORNAY Richard/Engr/STATS/ST Group) Subject: RE: GRANT TO SELECTED COLUMNS That second query, byt itself, should have done it, were you logged in as testuser when you tried to run it? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:26 PM To: [EMAIL PROTECTED] Subject: GRANT TO SELECTED COLUMNS Hello List, I have a table named CHECK_SBIN with fields id,jobid,filename,sbin,count, then I would like to create an account lets say testuser with SELECT privileges only to a specific columns. In this case I would like to open only id and jobid to 'testuser'. I have already created an account to testuser in two ways using the commands below. 1. Provide SELECT privileges first on the CHECK_SBIN table - GRANT SELECT ON DATABASE.CHECK_SBIN TO testuser IDENTIFIED BY 'password' >> OK Then issue another GRANT statement for the specific columns - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: I was able to access mysql using the account and seeing only CHECK_SBIN but when I do 'SELECT * FROM CHECK_SBIN' it shows me all the columns in the table. 2. Use only the second SQL - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: An error occured 'Access denied for testuser'. Note: The grant tables are all checked correctly. System: MySQL 4.0.13 Solaris 8 I checked the manual and mailing list archive and was not successful in finding any related matter to my problem. Any help would be appreciated. Thanks, Richard -- 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: GRANT TO SELECTED COLUMNS
Hi Paul/Lists, Thank you for your very clear explanations... In my 1st option, I think I misunderstood p427 of your book(MySQL) by granting first a SELECT privileges on the table before issuing the UPDATE on specific columnsSo now I learned that It should not be the case for SELECTING specific columns. In my 2nd option, Yes, thats my expectation when issuing SELECT * command will only give the privileged columns even there are other columns in the table. NOTE: I already tried to SELECT the specific columns (by naming it explicitly) and it worked now... Thanks Again, Richard Paul DuBois <[EMAIL PROTECTED]>13/11/2003 11:14 AM To: BORNAY Richard/Engr/STATS/ST [EMAIL PROTECTED] Domain, [EMAIL PROTECTED] cc: Subject: RE: GRANT TO SELECTED COLUMNS At 10:55 AM +0800 11/13/03, [EMAIL PROTECTED] wrote: >Hi Chris/Lists, >In fact, I was able to login using both ways, here's what I did. >1. mysql -u testuser -ptestuser -hlocalhost DATABASE >> OK >2. In the mysql prompt, I issue the query 'SELECT * FROM CHECK_SBIN' >> >Access denied > ** This is were I'm getting the error Access denied for user >'testuser' > >Thanks, Richard That looks correct. Your requirements are unclear: In your first attempt, you grant access to the entire table with one GRANT statement, then to just a couple of columns with the second GRANT statement. This gives you access to the entire table (because of the first statement), which apparently is unsatisfactory -- you say you want access granted only for the two columns. In your second attempt, you grant access only to the two columns. This should, I think, give you what you want -- but then above you issue a SELECT * statement, which implies that you want access to the entire table. But you can't *have* access to the entire table, because you've granted access only to two columns. So I at least am unable to determine what you really want. Perhaps you are thinking that SELECT * should retrieve only those columns for which you have access. But that's not what it means. It means ALL columns in the table. To select only the columns for which you have granted access, you must name them explicitly. The exception would be if the table contains only those two columns. In that case, SELECT * should work. But my impression is that your table has more than just the two columns. > > > >"Chris" <[EMAIL PROTECTED]>13/11/2003 10:34 AM > > >To: "Mysql List" <[EMAIL PROTECTED]> >cc: (bcc: BORNAY Richard/Engr/STATS/ST Group) >Subject: RE: GRANT TO SELECTED COLUMNS > > > > > > > >That second query, byt itself, should have done it, were you logged in as >testuser when you tried to run it? > >Chris > >-Original Message- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >Sent: Wednesday, November 12, 2003 5:26 PM >To: [EMAIL PROTECTED] >Subject: GRANT TO SELECTED COLUMNS > > >Hello List, >I have a table named CHECK_SBIN with fields id,jobid,filename,sbin,count, >then I would like to create an account lets say testuser with SELECT >privileges only to a specific columns. In this case I would like to open >only id and jobid to 'testuser'. >I have already created an account to testuser in two ways using the >commands below. >1. Provide SELECT privileges first on the CHECK_SBIN table > - GRANT SELECT ON DATABASE.CHECK_SBIN TO testuser IDENTIFIED BY >'password' >> OK > Then issue another GRANT statement for the specific columns > - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK > >Comments: > I was able to access mysql using the account and seeing only >CHECK_SBIN but when I do 'SELECT * FROM CHECK_SBIN' it shows > me all the columns in the table. > >2. Use only the second SQL > - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK > > Comments: > An error occured 'Access denied for testuser'. > >Note: The grant tables are all checked correctly. > >System: >MySQL 4.0.13 >Solaris 8 > >I checked the manual and mailing list archive and was not successful in >finding any related matter to my problem. Any help would be appreciated. > >Thanks, Richard -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GRANT TO SELECTED COLUMNS
You can't login as testuser and try to give yourself privileges, it just doesn't work. GRANT SELECT ON DATABASE.CHECK_SBIN TO testuser IDENTIFIED BY 'password' Put an entry into mysql.user, with the host set to '%' [Any host, are you sure that's what you want?] and all privileges set to 'N'. It also put an entry into mysql.tables_priv (testuser@'%') giving testuser Select privileges on the table DATABASE.CHECK_SBIN [Note, this gives testuser select privileges to the whole table, so it doesn't even check column level Select privileges] GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser Added the colum,n Select privilege to mysql.tables_priv.Column_priv and add an entry into mysql.columns_priv for testuser@'%' giving access to the specified fields. Since the testuser has Select privileges on the whole table, you get all the columns. So all your troubles came from running the first query, giving testuser Full select privileges on the table. running this query as root should fix your problem: REVOKE SELECT ON DATABASE.CHECK_SBIN FROM testuser; if that doesn't work you might try unsetting the Select privilege FROM mysql.tables_priv.Table_priv WHERE User='testuser' AND Host='%' Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 6:56 PM To: [EMAIL PROTECTED] Subject: RE: GRANT TO SELECTED COLUMNS Hi Chris/Lists, In fact, I was able to login using both ways, here's what I did. 1. mysql -u testuser -ptestuser -hlocalhost DATABASE >> OK 2. In the mysql prompt, I issue the query 'SELECT * FROM CHECK_SBIN' >> Access denied ** This is were I'm getting the error Access denied for user 'testuser' Thanks, Richard "Chris" <[EMAIL PROTECTED]>13/11/2003 10:34 AM To: "Mysql List" <[EMAIL PROTECTED]> cc: (bcc: BORNAY Richard/Engr/STATS/ST Group) Subject: RE: GRANT TO SELECTED COLUMNS That second query, byt itself, should have done it, were you logged in as testuser when you tried to run it? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:26 PM To: [EMAIL PROTECTED] Subject: GRANT TO SELECTED COLUMNS Hello List, I have a table named CHECK_SBIN with fields id,jobid,filename,sbin,count, then I would like to create an account lets say testuser with SELECT privileges only to a specific columns. In this case I would like to open only id and jobid to 'testuser'. I have already created an account to testuser in two ways using the commands below. 1. Provide SELECT privileges first on the CHECK_SBIN table - GRANT SELECT ON DATABASE.CHECK_SBIN TO testuser IDENTIFIED BY 'password' >> OK Then issue another GRANT statement for the specific columns - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: I was able to access mysql using the account and seeing only CHECK_SBIN but when I do 'SELECT * FROM CHECK_SBIN' it shows me all the columns in the table. 2. Use only the second SQL - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: An error occured 'Access denied for testuser'. Note: The grant tables are all checked correctly. System: MySQL 4.0.13 Solaris 8 I checked the manual and mailing list archive and was not successful in finding any related matter to my problem. Any help would be appreciated. Thanks, Richard -- 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: secure automated access (was "Re: Backing up all databases")
That sounds like the solution. Thanks for your help, everyone. > The simplest solution is to keep the password in the .my.cnf file in > your home directory. See http://www.mysql.com/doc/en/Option_files.html > in the manual. > > In the case of root cron jobs then, you need a .my.cnf readable only by > root in root's home. It should include > > [client] > password="mysql_root_password" > > As mysql reads the .my.cnf file, this avoids the ps "sniffing" problem, > and also keeps the password out of the script. Because you make the > .my.cnf file readable only by root, other users can't see it. (If they > can see it, you've got bigger problems than just the mysql password). > > Michael -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GRANT TO SELECTED COLUMNS
At 10:55 AM +0800 11/13/03, [EMAIL PROTECTED] wrote: Hi Chris/Lists, In fact, I was able to login using both ways, here's what I did. 1. mysql -u testuser -ptestuser -hlocalhost DATABASE >> OK 2. In the mysql prompt, I issue the query 'SELECT * FROM CHECK_SBIN' >> Access denied ** This is were I'm getting the error Access denied for user 'testuser' Thanks, Richard That looks correct. Your requirements are unclear: In your first attempt, you grant access to the entire table with one GRANT statement, then to just a couple of columns with the second GRANT statement. This gives you access to the entire table (because of the first statement), which apparently is unsatisfactory -- you say you want access granted only for the two columns. In your second attempt, you grant access only to the two columns. This should, I think, give you what you want -- but then above you issue a SELECT * statement, which implies that you want access to the entire table. But you can't *have* access to the entire table, because you've granted access only to two columns. So I at least am unable to determine what you really want. Perhaps you are thinking that SELECT * should retrieve only those columns for which you have access. But that's not what it means. It means ALL columns in the table. To select only the columns for which you have granted access, you must name them explicitly. The exception would be if the table contains only those two columns. In that case, SELECT * should work. But my impression is that your table has more than just the two columns. "Chris" <[EMAIL PROTECTED]>13/11/2003 10:34 AM To: "Mysql List" <[EMAIL PROTECTED]> cc: (bcc: BORNAY Richard/Engr/STATS/ST Group) Subject: RE: GRANT TO SELECTED COLUMNS That second query, byt itself, should have done it, were you logged in as testuser when you tried to run it? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:26 PM To: [EMAIL PROTECTED] Subject: GRANT TO SELECTED COLUMNS Hello List, I have a table named CHECK_SBIN with fields id,jobid,filename,sbin,count, then I would like to create an account lets say testuser with SELECT privileges only to a specific columns. In this case I would like to open only id and jobid to 'testuser'. I have already created an account to testuser in two ways using the commands below. 1. Provide SELECT privileges first on the CHECK_SBIN table - GRANT SELECT ON DATABASE.CHECK_SBIN TO testuser IDENTIFIED BY 'password' >> OK Then issue another GRANT statement for the specific columns - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: I was able to access mysql using the account and seeing only CHECK_SBIN but when I do 'SELECT * FROM CHECK_SBIN' it shows me all the columns in the table. 2. Use only the second SQL - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: An error occured 'Access denied for testuser'. Note: The grant tables are all checked correctly. System: MySQL 4.0.13 Solaris 8 I checked the manual and mailing list archive and was not successful in finding any related matter to my problem. Any help would be appreciated. Thanks, Richard -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GRANT TO SELECTED COLUMNS
Hi Chris/Lists, In fact, I was able to login using both ways, here's what I did. 1. mysql -u testuser -ptestuser -hlocalhost DATABASE >> OK 2. In the mysql prompt, I issue the query 'SELECT * FROM CHECK_SBIN' >> Access denied ** This is were I'm getting the error Access denied for user 'testuser' Thanks, Richard "Chris" <[EMAIL PROTECTED]>13/11/2003 10:34 AM To: "Mysql List" <[EMAIL PROTECTED]> cc: (bcc: BORNAY Richard/Engr/STATS/ST Group) Subject: RE: GRANT TO SELECTED COLUMNS That second query, byt itself, should have done it, were you logged in as testuser when you tried to run it? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:26 PM To: [EMAIL PROTECTED] Subject: GRANT TO SELECTED COLUMNS Hello List, I have a table named CHECK_SBIN with fields id,jobid,filename,sbin,count, then I would like to create an account lets say testuser with SELECT privileges only to a specific columns. In this case I would like to open only id and jobid to 'testuser'. I have already created an account to testuser in two ways using the commands below. 1. Provide SELECT privileges first on the CHECK_SBIN table - GRANT SELECT ON DATABASE.CHECK_SBIN TO testuser IDENTIFIED BY 'password' >> OK Then issue another GRANT statement for the specific columns - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: I was able to access mysql using the account and seeing only CHECK_SBIN but when I do 'SELECT * FROM CHECK_SBIN' it shows me all the columns in the table. 2. Use only the second SQL - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: An error occured 'Access denied for testuser'. Note: The grant tables are all checked correctly. System: MySQL 4.0.13 Solaris 8 I checked the manual and mailing list archive and was not successful in finding any related matter to my problem. Any help would be appreciated. Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Page Numbers
I have a function that accepts a page number and an amount per page value then generates the numbers for the LIMIT clause. It requires SQL_CALC_FOUND_ROWS to be specified in the query. iNum=10; // Rows per page iPage=1; // Current page, first page is 1 iStart= (iPage-1) * iNum; // Starting row number Run the query ::SELECT SQL_CALC_FOUND_ROWS FROM ... LIMIT iStart,iNum;:: iFoundRows = ::SELECT FOUND_ROWS();:: iTotalPages = ceil(iFoundRows / iNum); // Total number of displayable pages; >From there you can determine what sort of data you want to display (like every 5th page, etc.) Note: SQL_CALC_FOUND_ROWS/FOUND_ROWS() was introduced in 4.0, to get the total number of rows prior to 4.0 I believe you had to run the same query without the LIMIT clause and use the number of rows returned. This is the best way I have found to do this, you could do a google search for 'MySQL paginate' to get more info. If anyone else has a better way to do this I'd like to hear it... Chris -Original Message- From: Mike Blezien [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 6:11 PM To: MySQL List Subject: Page Numbers Hello, we've set up many displays using the "Previous" and "Next" simple linking set up of search results. But now would like to implement the page number style, IE. "Previous" [1] [2] [3] "Next" style format and was hoping someone may have a sample/example queries to accomplish this. Usually working with 10 results per page display. TIA -- MikeBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [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: GRANT TO SELECTED COLUMNS
That second query, byt itself, should have done it, were you logged in as testuser when you tried to run it? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:26 PM To: [EMAIL PROTECTED] Subject: GRANT TO SELECTED COLUMNS Hello List, I have a table named CHECK_SBIN with fields id,jobid,filename,sbin,count, then I would like to create an account lets say testuser with SELECT privileges only to a specific columns. In this case I would like to open only id and jobid to 'testuser'. I have already created an account to testuser in two ways using the commands below. 1. Provide SELECT privileges first on the CHECK_SBIN table - GRANT SELECT ON DATABASE.CHECK_SBIN TO testuser IDENTIFIED BY 'password' >> OK Then issue another GRANT statement for the specific columns - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: I was able to access mysql using the account and seeing only CHECK_SBIN but when I do 'SELECT * FROM CHECK_SBIN' it shows me all the columns in the table. 2. Use only the second SQL - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: An error occured 'Access denied for testuser'. Note: The grant tables are all checked correctly. System: MySQL 4.0.13 Solaris 8 I checked the manual and mailing list archive and was not successful in finding any related matter to my problem. Any help would be appreciated. Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Repeated table corruptions
Hi, No problems there: 031113 1:18:33 /applic/mysql-max-4.0.13-dec-osf5.1-alphaev67/bin/mysqld: Normal shutdown 031113 1:18:34 InnoDB: Starting shutdown... 031113 1:18:36 InnoDB: Shutdown completed 031113 1:18:36 /applic/mysql-max-4.0.13-dec-osf5.1-alphaev67/bin/mysqld: Shutdown Complete 031113 01:18:37 mysqld ended Unix isn't being shutdown, only the Mysql Server program. Thanks Quentin Quentin Bennett Senior Analyst Infinity Solutions Ltd PO Box 3323, Auckland Ph: 09 921 8146 Fx: 09 309 4142 www.infinitytransport.net > -Original Message- > From: Stephen Brownlow [mailto:[EMAIL PROTECTED] > Sent: Thursday, 13 November 2003 3:11 p.m. > To: Quentin Bennett; Dathan Vance Pattishall > Cc: [EMAIL PROTECTED] > Subject: Re: Repeated table corruptions > > > Have a look at the MySQL server error log. > It should show messages about Shutting down. > > If it does not report the shutdowns, Unix would just be > killing it, and that could be causing, not solving the > problems. MySQL shutdowns might not happen when Unix is > shutdown, because sometimes the rc.d scripts are not > installed or are wrong. > > Stephen > > - Original Message - > From: "Quentin Bennett" <[EMAIL PROTECTED]> > To: "Dathan Vance Pattishall" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Thursday, November 13, 2003 7:17 AM > Subject: RE: Repeated table corruptions > > > Hi, > > Thanks - the tables were converted to MyISAM a while ago, and > have run with no problem. Because they have been repaired > before, following a previous crash, I believe that prior to > the shut down last night all was well. > > I have run in to the issue of trying to repair a 'live' > table, with subsequent disaster, but that is not the issue > here - the issue is why does the table get corrupted in the > first place. > > The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repeated table corruptions
Have a look at the MySQL server error log. It should show messages about Shutting down. If it does not report the shutdowns, Unix would just be killing it, and that could be causing, not solving the problems. MySQL shutdowns might not happen when Unix is shutdown, because sometimes the rc.d scripts are not installed or are wrong. Stephen - Original Message - From: "Quentin Bennett" <[EMAIL PROTECTED]> To: "Dathan Vance Pattishall" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, November 13, 2003 7:17 AM Subject: RE: Repeated table corruptions Hi, Thanks - the tables were converted to MyISAM a while ago, and have run with no problem. Because they have been repaired before, following a previous crash, I believe that prior to the shut down last night all was well. I have run in to the issue of trying to repair a 'live' table, with subsequent disaster, but that is not the issue here - the issue is why does the table get corrupted in the first place. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of MySQL children ?
Thanks, Arjen. That is interesting about linux "threads" showing up as separate processes. They each have separate PID's, so I am guessing that it really doesn't matter what you call it; each thread still takes up the same amount of memory as the 'ps' command reports. In my case: # ps vaw |grep mysqld PID TTY STAT TIME MAJFL TRS DRS RSS %MEM COMMAND 5012 pts/5S 0:00235 505 17064 0.0 /bin/sh ./bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/lo 5037 pts/5S 0:11 16341 3577 20830 1308 5.1 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.c 5039 pts/5S 0:07 2237 3577 20830 1308 5.1 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.c 5040 pts/5S 0:00 2 3577 20830 1308 5.1 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.c 5041 pts/5S 0:00 0 3577 20830 1308 5.1 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.c 5042 pts/5S 0:00 1 3577 20830 1308 5.1 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.c 5043 pts/5S 0:00 0 3577 20830 1308 5.1 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.c 5044 pts/5S 0:00 1 3577 20830 1308 5.1 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.c 5045 pts/5S 0:26 10873 3577 20830 1308 5.1 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.c 5046 pts/5S 0:00 15 3577 20830 1308 5.1 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.c 5047 pts/5S 0:03 1419 3577 20830 1308 5.1 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.c # top 8:45pm up 6 days, 3:52, 8 users, load average: 0.06, 0.17, 0.17 84 processes: 82 sleeping, 2 running, 0 zombie, 0 stopped CPU states: 6.6% user, 5.4% system, 0.0% nice, 87.9% idle Mem:25468K av, 24268K used,1200K free, 0K shrd,2544K buff Swap: 65528K av, 28484K used, 37044K free8604K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 5045 mysql 10 0 10384 1336 660 S 0.1 5.2 0:26 mysqld 5037 mysql 9 0 10384 1336 660 S 0.0 5.2 0:11 mysqld 5039 mysql 9 0 10384 1336 660 S 0.0 5.2 0:07 mysqld 5040 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld 5041 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld 5042 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld 5043 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld 5044 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld 5046 mysql 9 0 10384 1336 660 S 0.0 5.2 0:00 mysqld 5047 mysql 9 0 10384 1336 660 S 0.0 5.2 0:03 mysqld 5012 root 9 0 1844 0 S 0.0 0.0 0:00 mysqld_safe So isn't each of the ten (10) processes using 10384 K, for a total of 103840 K? If so, this seems excessive, don't you think, considering my 32 M of RAM and two or three concurrent connections? On Thu, Nov 13, 2003 at 11:17:33AM +1000, Arjen Lentz wrote: > The > connection-specific variables can be adjusted. > You can also set the maximum # of connections the server will allow. Can you suggest *which* variables to try changing? I already tried 'thread_concurrency', but this did not take effect -- we still got ten threads on the 'ps' / 'top' report. Also, do you know, if we set this variable to, say, 2, then will mysqld automatically increase it if necessary? E.g. like apache does with its children. Thanks again for your insights. Regards, George Webb [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Page Numbers
Hello, we've set up many displays using the "Previous" and "Next" simple linking set up of search results. But now would like to implement the page number style, IE. "Previous" [1] [2] [3] "Next" style format and was hoping someone may have a sample/example queries to accomplish this. Usually working with 10 results per page display. TIA -- MikeBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Max_connections problem
Hi. Can someone help me with problem of max connection when it rech the limit cause some process has lock table so I want to login as root to make some kill that process but I can login because of max connection bloack me. And I known some user with PROCESS_ACL can login is root has this privileges or not and if has how to login with this option or if not has this privileges how to set this privileges. Thanks Kittiphum Worachat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT TO SELECTED COLUMNS
Hello List, I have a table named CHECK_SBIN with fields id,jobid,filename,sbin,count, then I would like to create an account lets say testuser with SELECT privileges only to a specific columns. In this case I would like to open only id and jobid to 'testuser'. I have already created an account to testuser in two ways using the commands below. 1. Provide SELECT privileges first on the CHECK_SBIN table - GRANT SELECT ON DATABASE.CHECK_SBIN TO testuser IDENTIFIED BY 'password' >> OK Then issue another GRANT statement for the specific columns - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: I was able to access mysql using the account and seeing only CHECK_SBIN but when I do 'SELECT * FROM CHECK_SBIN' it shows me all the columns in the table. 2. Use only the second SQL - GRANT SELECT (id, jobid) ON DATABASE.CHECK_SBIN TO testuser >> OK Comments: An error occured 'Access denied for testuser'. Note: The grant tables are all checked correctly. System: MySQL 4.0.13 Solaris 8 I checked the manual and mailing list archive and was not successful in finding any related matter to my problem. Any help would be appreciated. Thanks, Richard
Re: Number of MySQL children ?
Hi George, On Thu, 2003-11-13 at 08:49, George Webb wrote: > Would someone please explain how to set the number of child mysqld > processes which start when mysql (mysql-standard-4.0.14-pc-linux-i686) > starts up? > > I am using MySQL on a memory-poor (32MB RAM) machine, and MySQL > seems to hog about 10MB per child process, and there are ten (10) of them > at startup. All this, and usually there is only one or two active > connections. So I was thinking more like two (2) child processes would > be better, but I can't seem to change it from the default (ten (10)). > > BTW, I experimented with the server parameters > (http://www.mysql.com/doc/en/Server_parameters.html), but still there are > at least ten (10) mysqld child process always running. MySQL does not use multiple processes. It is single process, multithreaded. What may be confusing you is the fact that in the Linux 2.2 and 2.4 kernels, ps shows threads as processes. I believe this has been fixed in 2.6. Anyway it's purely a visual thing, threads are threads anyway. Apart from some internal threads, MySQL uses a thread for each connection. These threads do need some memory of course. The connection-specific variables can be adjusted. You can also set the maximum # of connections the server will allow. Regards, Arjen. -- Arjen Lentz, Technical Writer, Trainer Brisbane, QLD Australia MySQL AB, www.mysql.com Melbourne 1 December (5 days): Using & Managing MySQL Training Training,Support,Licenses,T-shirts @ https://order.mysql.com/?marl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Equivalent Function Needed
The IF function works pretty much like Oracle's decode. But I recommend using CASE..WHEN for compatibility between the two. Check out: http://www.mysql.com/doc/en/Control_flow_functions.html > -Original Message- > From: Jonathan Rosenberg [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 4:54 PM > To: [EMAIL PROTECTED] > Subject: Equivalent Function Needed > > > I use the following function in Oracle > SELECT decode(status,'A','Active','L','Active','Former') FROM Table > > What it means is: if table column status = A, return > "Active", if status=L, return "Active", else return "Former" > in the select statement. > > Decode in mysql has nothing to do with this functionality and > I didn't see a function while browsing the docs online that > did this. Is there an equivalent function in mySQL? > > Thanks > > > * > Jon Rosenberg > www.DeanForAmerica.com > www.OhioForDean.org > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Equivalent Function Needed
I use the following function in Oracle SELECT decode(status,'A','Active','L','Active','Former') FROM Table What it means is: if table column status = A, return "Active", if status=L, return "Active", else return "Former" in the select statement. Decode in mysql has nothing to do with this functionality and I didn't see a function while browsing the docs online that did this. Is there an equivalent function in mySQL? Thanks * Jon Rosenberg www.DeanForAmerica.com www.OhioForDean.org
Index Question
Hi. using MYSql 3.28 I have a surname column with a standard index and this is the column were search are performed on, currently the filed only has the one name i.e.: Surname: smith I want to include other column data in the search i.e. fathersname, so I create an index on that column and in my search I use a statement like: surname = '"& globsurname & "' or fathersname = '"& globsurname & "' this does work but tends to slow the thing down, So Can I take the name from fathersname and include it in the Surname field so I have say: Surname: smith jones It does not seem to work for me, maybe I need a particular separator ? I want to go this way as each record my have several names that I want to be able to search on. Thanks in advance Regards John Berman
RE: Having MySQL listen on multiple(2) ports at the same time
We will most likely be using that method. We have to translate our ipchains to iptables first. -Original Message- From: Kevin Carlson [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 2:56 PM To: Misaochankun Cc: [EMAIL PROTECTED] Subject: Re: Having MySQL listen on multiple(2) ports at the same time You can emulate this using IPTables and a FORWARD rule. If you are unfamiliar with IPTables see http://www.iptables.com/ for documentation. Misaochankun wrote: >Can this be done? >No, I do not mean running multiple MySQL servers. >I need to have MySQL listen on two separate ports at the same time. >Reason being, the new port is needed for a routing issue, and the >default port 3306 needs to stay up to respond to normal traffic. > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having MySQL listen on multiple(2) ports at the same time
You can emulate this using IPTables and a FORWARD rule. If you are unfamiliar with IPTables see http://www.iptables.com/ for documentation. Misaochankun wrote: Can this be done? No, I do not mean running multiple MySQL servers. I need to have MySQL listen on two separate ports at the same time. Reason being, the new port is needed for a routing issue, and the default port 3306 needs to stay up to respond to normal traffic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Number of MySQL children ?
Would someone please explain how to set the number of child mysqld processes which start when mysql (mysql-standard-4.0.14-pc-linux-i686) starts up? I am using MySQL on a memory-poor (32MB RAM) machine, and MySQL seems to hog about 10MB per child process, and there are ten (10) of them at startup. All this, and usually there is only one or two active connections. So I was thinking more like two (2) child processes would be better, but I can't seem to change it from the default (ten (10)). BTW, I experimented with the server parameters (http://www.mysql.com/doc/en/Server_parameters.html), but still there are at least ten (10) mysqld child process always running. Thanks for your help! George Webb [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
Hey - Progress! But first, I had to correct: can't use "-p ", must use the longer form: "--password=" Then I can run it! One problem. It deleted the first 1000 records, rather than leaving the last 1000 records in the db. What twist is needed to get that right? ? --- Scott H <[EMAIL PROTECTED]> wrote: > When I run: > > mysql -p -u > < > deleteold.sql > > ...and within deleteold.sql, I have only this > text (2 lines): > > select (@aa:=seq) as low_seq from syslogTB > order > by seq limit 1000,1; > delete from syslogTB where seq < @aa; > > I just get back a screen full of syntax > suggestions. Where is my mistake here? > > --- Dan Greene wrote: > > for example your script would be > > mysql --user=myuserid --password=mypassword > -h > > hostname < deleteold.sql > > > > see: > > http://www.mysql.com/doc/en/Batch_mode.html > > > > > -Original Message- > > > From: Scott H > > [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, November 12, 2003 4:22 PM > > > To: Dan Greene > > > Cc: MySQL Mailing List > > > Subject: RE: maintaining size of a db > > > > > > > > > Cool idea, but I don't think you can really > > do > > > it. When I try, mysql just gives me back > the > > > syntax stuff.I'm a bit perplexed > - > > I > > > would have thought this would be a > > semi-common > > > issue in db maintenance, but no one seems > to > > have > > > set this up. I'm still trying, please send > > any > > > other ideas/suggestions my way... thx!! > > > > > > --- Dan Greene wrote: > > > > you may be able to put both statements to > a > > > > text file, let's call it deleteold.sql > > > > > > > > then your cron job would be : > > > > > > > > mysql (put your connect stuff here) < > > > > deleteold.sql > > > > > > > > > From: Scott H > > > > > Well, it sort of helps. But that > section > > is > > > > > about future enhancements intended for > > mysql. > > > > > I need to set something up now, with > the > > > > current > > > > > stable version. One thing I read > (can't > > find > > > > it > > > > > now) indicated that the current version > > (I'm > > > > > actually running 4.0.15a) has "limited" > > > > support for subqueries - but I don't know > > > > > >exactly how far that goes. > > > > > > > > > > So let me set the stage a bit more - > I'll > > > > assume > > > > > for now there is no reasonably simple > way > > to > > > > work > > > > > with the actual size of the database on > > disk, > > > > and > > > > > instead will go with the idea that I > can > > > > expect > > > > > the size of any one record to be of > some > > > > average. > > > > > So, according to Dan's suggestion, if > I > > do a > > > > > little math, and control the number of > > > > records, I > > > > > can control the size of the db -- > > > > approximately. > > > > > That's fine. Thus if have "seq" as an > > > > > autoincrement field, and I wanted to > stay > > > > around > > > > > say 1000 records, deleting the oldest > > > > records, I > > > > > would need to run a cron job that would > > > > somehow > > > > > nest or relate these 2 sql statements: > > > > > > > > > > select (@aa:=seq) as low_seq from > > logtable > > > > order > > > > > by seq limit 1000,1 > > > > > > > > > > delete from logtable where seq < @aa > > > > > > > > > > I've tried putting this into a subquery > > > > format > > > > > but no luck. But I'm a noob, so I keep > > > > trying, > > > > > thinking I might hit on the right > syntax. > > > > > > Or, is > > > > > there some way to pull the value of > > "low_seq" > > > > > into an environment variable and use it > > in a > > > > > script file to run the 2nd statement? > > Other > > > > > ideas? > > > > > > > > > > thanks, scott > > > > > > > > > > --- Michael McTernan wrote: > > > > > > >From the manual: > > > > > > > > > > > > 1.8.4.1 Subqueries > > > > > > > > > > > > Subqueries are supported in MySQL > > version > > > > 4.1. > > > > > > See section 1.6.1 Features > > > > > > Available in MySQL 4.1. > > > > > > > > > > > > Hope that helps, > > > > > > > > > > > > Mike > > > > > > > > > > > > > From: Scott H > > > > > > > OK, I *THINK* I follow you here. > > Couple > > > > of > > > > > > > questions. I'm reading an online > > > > tutorial > > > > > > trying > > > > > > > to figure this out, and I am led to > > > > believe > > > > > > mysql > > > > > > > can't do nested queries, aka > > sub-queries. > > > > But > > > > > > you > > > > > > > say it can? Is this recent? And I > > don't > > > > have > > > > > > a > > > > > > > timestamp field, I have an > > autoincrement > > > > > > field, > > > > > > > but what do you mean by the > > "(@aa:=id)" > > > > > > thing? I > > > > > > > don't follow that. thanks. > > > > > > > > > > > > > > --- Dan Greene > > > > > > <[EMAIL PROTECTED]> > > > > > > > wrote: > > > > > > > > What I would do is a classical > > > > > > guesstimate > > > > > > > > > > > > > > > > find the average size per record > > (data > > > > file > > > > > > > >
Sql - Problem with Left Join
Hello the little Query1 works until I add the left join : Query 1 ) Select A.DepartmentName,A.Address,P.Postcode,P.cityname from caddress A,cpostinfo P there A.ID=10 and A.PostInfoID=P.ID Query 2) Select A.DepartmentName,A.Address,P.Postcode,P.cityname, CP.firstname left Join CContactPerson CP , caddresscontactperson CACP ,ccontactinfo CCI on CP.ID =1001 and CACP.ContactpersonID=CP.ID and CACP.AddressID=A.ID and CACP.ID=CCI.AddressContactPersonID and CCI.AddressID=-1 and CCI.ContactInfoTypeID=1 from caddress A,cpostinfo P there A.ID=10 and A.PostInfoID=P.ID Query1 return : -> company , streeet ,2000,copenhagen Query2 return : -> Empty I know that the jeft join will give zero result , since the table caddresscontactperson are empty. But I do not Understand Why I not get -> company , streeet ,2000,copenhagen , NULL Normally a left join should not effect ur result. Hope Someone can give me clue using Mysql 4.1.0 regards Kim G. Pedersen macaos/elprint Development -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: new install failure
The user you are performing this as does not have the necessary permissions. -Original Message- From: DePhillips, Michael P [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:38 PM To: [EMAIL PROTECTED] Subject: new install failure HI list When installing mysql 4.16 on debian linux 2.4.20-20.8smp I get the following errors... ---snip- -- 031112 16:05:08 mysqld started 031112 16:05:08 InnoDB: Started 031112 16:05:08 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 031112 16:05:08 Aborting ---end snip host.frm is in mysql/data where it belongs, any idea why this is happening? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA ?
have you verified that the number of columns match? That the data types are matching as well? -Original Message- From: rmck [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:01 PM To: [EMAIL PROTECTED] Subject: LOAD DATA ? Hello, My Load data command seems not to load data into my db: mysql> load data local infile '/opt/week/ip.0311100440' into table logs.Nov03 ignore 2 lines; Query OK, 1 row affected (0.66 sec) Records: 48273 Deleted: 0 Skipped: 48272 Warnings: 48273 mysql> I have plenty of space where my data files are.. Can someone point me in the right direction to see why it is skipping those the table looks ok mysql> show table status; ++++---++--- --+-+--+---++--- --+-+-+- +-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++---++--- --+-+--+---++--- --+-+-+- +-+ | Nov03 | MyISAM | Dynamic| 72426930 |112 | 8140043192 | 1099511627775 | 5872661504 | 112 | 83656882 | 2003-11-06 07:19:13 | 2003-11-12 12:23:12 | 2003-11-12 10:04:36 | max_rows=10 | Rob -- 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: new install failure
That did it thanks Pete I need to my installs earlier in the day ;) -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 4:44 PM To: DePhillips, Michael P; [EMAIL PROTECTED] Subject: RE: new install failure Have you checked permissions - the files should be owned by mysql Peter -Original Message- From: DePhillips, Michael P [mailto:[EMAIL PROTECTED] Sent: 12 November 2003 21:38 To: [EMAIL PROTECTED] Subject: new install failure HI list When installing mysql 4.16 on debian linux 2.4.20-20.8smp I get the following errors... ---snip- -- 031112 16:05:08 mysqld started 031112 16:05:08 InnoDB: Started 031112 16:05:08 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 031112 16:05:08 Aborting ---end snip host.frm is in mysql/data where it belongs, any idea why this is happening? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
When I run: mysql -p -u < deleteold.sql ...and within deleteold.sql, I have only this text (2 lines): select (@aa:=seq) as low_seq from syslogTB order by seq limit 1000,1; delete from syslogTB where seq < @aa; I just get back a screen full of syntax suggestions. Where is my mistake here? --- Dan Greene wrote: > for example your script would be > mysql --user=myuserid --password=mypassword -h > hostname < deleteold.sql > > see: > http://www.mysql.com/doc/en/Batch_mode.html > > > -Original Message- > > From: Scott H > [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, November 12, 2003 4:22 PM > > To: Dan Greene > > Cc: MySQL Mailing List > > Subject: RE: maintaining size of a db > > > > > > Cool idea, but I don't think you can really > do > > it. When I try, mysql just gives me back the > > syntax stuff.I'm a bit perplexed - > I > > would have thought this would be a > semi-common > > issue in db maintenance, but no one seems to > have > > set this up. I'm still trying, please send > any > > other ideas/suggestions my way... thx!! > > > > --- Dan Greene wrote: > > > you may be able to put both statements to a > > > text file, let's call it deleteold.sql > > > > > > then your cron job would be : > > > > > > mysql (put your connect stuff here) < > > > deleteold.sql > > > > > > > From: Scott H > > > > Well, it sort of helps. But that section > is > > > > about future enhancements intended for > mysql. > > > > I need to set something up now, with the > > > current > > > > stable version. One thing I read (can't > find > > > it > > > > now) indicated that the current version > (I'm > > > > actually running 4.0.15a) has "limited" > > > support for subqueries - but I don't know > > > >exactly how far that goes. > > > > > > > > So let me set the stage a bit more - I'll > > > assume > > > > for now there is no reasonably simple way > to > > > work > > > > with the actual size of the database on > disk, > > > and > > > > instead will go with the idea that I can > > > expect > > > > the size of any one record to be of some > > > average. > > > > So, according to Dan's suggestion, if I > do a > > > > little math, and control the number of > > > records, I > > > > can control the size of the db -- > > > approximately. > > > > That's fine. Thus if have "seq" as an > > > > autoincrement field, and I wanted to stay > > > around > > > > say 1000 records, deleting the oldest > > > records, I > > > > would need to run a cron job that would > > > somehow > > > > nest or relate these 2 sql statements: > > > > > > > > select (@aa:=seq) as low_seq from > logtable > > > order > > > > by seq limit 1000,1 > > > > > > > > delete from logtable where seq < @aa > > > > > > > > I've tried putting this into a subquery > > > format > > > > but no luck. But I'm a noob, so I keep > > > trying, > > > > thinking I might hit on the right syntax. > > > > Or, is > > > > there some way to pull the value of > "low_seq" > > > > into an environment variable and use it > in a > > > > script file to run the 2nd statement? > Other > > > > ideas? > > > > > > > > thanks, scott > > > > > > > > --- Michael McTernan wrote: > > > > > >From the manual: > > > > > > > > > > 1.8.4.1 Subqueries > > > > > > > > > > Subqueries are supported in MySQL > version > > > 4.1. > > > > > See section 1.6.1 Features > > > > > Available in MySQL 4.1. > > > > > > > > > > Hope that helps, > > > > > > > > > > Mike > > > > > > > > > > > From: Scott H > > > > > > OK, I *THINK* I follow you here. > Couple > > > of > > > > > > questions. I'm reading an online > > > tutorial > > > > > trying > > > > > > to figure this out, and I am led to > > > believe > > > > > mysql > > > > > > can't do nested queries, aka > sub-queries. > > > But > > > > > you > > > > > > say it can? Is this recent? And I > don't > > > have > > > > > a > > > > > > timestamp field, I have an > autoincrement > > > > > field, > > > > > > but what do you mean by the > "(@aa:=id)" > > > > > thing? I > > > > > > don't follow that. thanks. > > > > > > > > > > > > --- Dan Greene > > > > > <[EMAIL PROTECTED]> > > > > > > wrote: > > > > > > > What I would do is a classical > > > > > guesstimate > > > > > > > > > > > > > > find the average size per record > (data > > > file > > > > > > > size + index file(s) size / # > records > > > in > > > > > table) > > > > > > > > > > > > > > using that, find the data used per > day > > > > > > > > > > > > > > using that, figure out how many > days, > > > on > > > > > > > average it takes to hit 20GB > > > > > > > > > > > > > > let's say it's 89 days. > > > > > > > > > > > > > > right off the top, take 10% off for > > > safety, > > > > > now > > > > > > > we're at 80 days > > > > > > > > > > > > > > presuming your table has a > timestamp > > > field: > > > > > > > > > > > > > > delete from log_table WHERE > > > TO_DAYS(NOW()) > > > > > - > > > > > > > TO_DAYS(date_col) > 80 > > > > > > > > > > > > > > if you don't have a timesta
RE: new install failure
Have you checked permissions - the files should be owned by mysql Peter -Original Message- From: DePhillips, Michael P [mailto:[EMAIL PROTECTED] Sent: 12 November 2003 21:38 To: [EMAIL PROTECTED] Subject: new install failure HI list When installing mysql 4.16 on debian linux 2.4.20-20.8smp I get the following errors... ---snip- -- 031112 16:05:08 mysqld started 031112 16:05:08 InnoDB: Started 031112 16:05:08 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 031112 16:05:08 Aborting ---end snip host.frm is in mysql/data where it belongs, any idea why this is happening? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: C API
There is a C++ package called OTL (http://otl.sourceforge.net/home.htm). It supports both MySQL through MyODBC, and Oracle. It works great with Oracle applications, but we have not used it with MySQL. Thanks, Brad Teale Universal Weather and Aviation, Inc. mailto:[EMAIL PROTECTED] 713-944-1440 ext. 3623 Arrange things so that a person needs to know nothing, and you'll end up with a person who is capable of nothing. -- K. Brown -Original Message- From: Priyanka Gupta [mailto:[EMAIL PROTECTED] Sent: Monday, October 20, 2003 7:14 PM To: [EMAIL PROTECTED] Subject: C API Is there a way to have a common C API for MySQL and Oracle. I am writing some software that I would like to work with both MYSQL or Oracle as the backend server? priyanka _ Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet Service. Try it FREE for one month! http://join.msn.com/?page=dept/dialup -- 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]
new install failure
HI list When installing mysql 4.16 on debian linux 2.4.20-20.8smp I get the following errors... ---snip- -- 031112 16:05:08 mysqld started 031112 16:05:08 InnoDB: Started 031112 16:05:08 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 031112 16:05:08 Aborting ---end snip host.frm is in mysql/data where it belongs, any idea why this is happening? Thanks
Re: Some help with a complex query
* Elisenda > The problem is that it 's too slow and the result doesn't appear. > I am going to try to explain the query. Please do that using the EXPLAIN SELECT statement: http://www.mysql.com/doc/en/EXPLAIN.html > This will show what index is beeing used on the different joins, and approximately how many rows the server must handle to produce your result. I suspect that in this case there are no index on some of the columns beeing used for the joins, whih means the server must scan the entire table multiple times. This will often result in a query that appears to 'hang', no result is returned. The server is actually working on the result, but it will take 'forever', you will normally kill your connection before you recieve anything. More below... > Fields from Table FASE: (300.000 records) > > ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, > SQL_ID_PY char(6), > SQL_ID_CE char(6), > PR_flag INT, > PR_Date_Visita_2 Date, > AU_PR_Aula varchar(255) (it a field that contain SQL_ID_PY_SQL_ID_CE_PR) > > Field from Table CE (30.000 records) > > CE_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, > CE_ID_CE char(6), > CE_Centro varchar(32), > CE_Domicilio varchar(32), > CE_CP varchar(5), > CE_Poblacion varchar(30), > CE_ID_Capital char(2), > CE_Capital varchar(30), > CE_ID_PROV char(2), > CE_PROV varchar(15), > CE_ID_CCAA char(2), > CE_CCAA varchar(15) > > Field from Table CA (30.000 records) > > CA_ID INT NOT NULL PRIMARY KEY, > CA_ID_User char(6), > CA_ID_CE char(6), > CA_Centro varchar(32), > CA_ID_Idioma_A INT, > CA_Horario varchar(30) > > Fields from table AU (700.000 records) > > AU_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, > AU_ID_CE char(6), > AU_ID_PY char(6), > AU_ID_FASE INT, > AU_A_M INT, > AU_A_F INT, > AU_Aula varchar(32) (it a field that contain AU_ID_PY_AU_ID_CE_PR) > > Fields from table PP (200.000 records) > > PP_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, > PP_ID_PP char(6), > PP_ID_CE char(6), > PP_Contacto char(50), > PP_ID_Cargo char(6), > PP_Cargo char(32) There seems to be only primary keys on these tables? No other index defined? If that is the case, this is probably the reason of your problem. Put an index on any column used to join other tables, the so-called foreign keys. > I select from Fase some records. From fase I only want records (SQL_ID_CE) > that have FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG= '1'. From this selection, You can create a combined index on SQL_ID_PY and PR_FLAG: CREATE INDEX SQL_ID_PY_PR_FLAG_INDEX ON FASE (SQL_ID_PY,PR_FLAG) > I only want records that in AU have AU.AU_NIVEL= '13.14' and in CA have > CA.CA_ID_IDIOMA_A= '6'. Then probably both AU.AU_NIVEL and CA.CA_ID_IDIOMA_A should be indexed. > In WHERE I write > > AU.AU_Aula= fase.AU_PR_Aula AND > AU.AU_ID_CE = CA.CA_ID_CE AND > CE.CE_ID_CE = CA.CA_ID_CE AND > CE.CE_ID_CE = Fase.SQL_ID_CE AND > CE.CE_ID_CE = PP.PP_ID_CE AND > Fase.PR_PP_ID_Coord = PP.PP_ID_PP > > > Main relation in all tables is SQL_ID_CE. Then all columns related to SQL_ID_CE should have an index. Probably also some of the other fields mentioned above: AU.AU_Aula, fase.AU_PR_Aula, AU.AU_ID_CE, CA.CA_ID_CE, CE.CE_ID_CE, Fase.SQL_ID_CE, PP.PP_ID_CE, Fase.PR_PP_ID_Coord, PP.PP_ID_PP. MySQL will not use more than one index per table per select statement, but which index to use may vary, depending on the criteria and the distribution of your data. In what order the tables are read, will also vary. Don't be afraid of indexing "too many" columns, you can easily remove any unused index after you have identified which you really need. The EXPLAIN SELECT statement will let you identify the actual index used, but you may need to test with various data, because of the internal join optimizer behaviour mentioned above, the index used may change depending on your criteria/data. By the way, why don't you use the primary keys? It is very common to use the primary keys for some of the joins when joining many tables. For instance, you join the PP table using PP_ID_PP and Fase.PR_PP_ID_Coord, is it supposed to return multiple PP rows for each Fase row? If you only except one, i.e. PP_ID_PP is unique in the PP table, then you should have a UNIQUE index on it, or promote it to primary key, or maybe use the primary key in place of this column? > I don't know if I explain myself or it is too boring to continue reading. I'm not bored. :) > It will be fantastic if some can help me. I don't know if I am doing > something wrong or what. I think you only need indexing. Run EXPLAIN SELECT first, save the output (or post it here), put on some indexes, run EXPLAIN SELECT again, and see the difference. When all the numbers in the 'rows' column of the explain select output multiplied together is a relatively low number, your query should be fast... please include the result of EXPLAIN SELECT if you have more questions/problems. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAI
RE: maintaining size of a db
Cool idea, but I don't think you can really do it. When I try, mysql just gives me back the syntax stuff.I'm a bit perplexed - I would have thought this would be a semi-common issue in db maintenance, but no one seems to have set this up. I'm still trying, please send any other ideas/suggestions my way... thx!! --- Dan Greene wrote: > you may be able to put both statements to a > text file, let's call it deleteold.sql > > then your cron job would be : > > mysql (put your connect stuff here) < > deleteold.sql > > > From: Scott H > > Well, it sort of helps. But that section is > > about future enhancements intended for mysql. > > I need to set something up now, with the > current > > stable version. One thing I read (can't find > it > > now) indicated that the current version (I'm > > actually running 4.0.15a) has "limited" > support for subqueries - but I don't know > >exactly how far that goes. > > > > So let me set the stage a bit more - I'll > assume > > for now there is no reasonably simple way to > work > > with the actual size of the database on disk, > and > > instead will go with the idea that I can > expect > > the size of any one record to be of some > average. > > So, according to Dan's suggestion, if I do a > > little math, and control the number of > records, I > > can control the size of the db -- > approximately. > > That's fine. Thus if have "seq" as an > > autoincrement field, and I wanted to stay > around > > say 1000 records, deleting the oldest > records, I > > would need to run a cron job that would > somehow > > nest or relate these 2 sql statements: > > > > select (@aa:=seq) as low_seq from logtable > order > > by seq limit 1000,1 > > > > delete from logtable where seq < @aa > > > > I've tried putting this into a subquery > format > > but no luck. But I'm a noob, so I keep > trying, > > thinking I might hit on the right syntax. > Or, is > > there some way to pull the value of "low_seq" > > into an environment variable and use it in a > > script file to run the 2nd statement? Other > > ideas? > > > > thanks, scott > > > > --- Michael McTernan wrote: > > > >From the manual: > > > > > > 1.8.4.1 Subqueries > > > > > > Subqueries are supported in MySQL version > 4.1. > > > See section 1.6.1 Features > > > Available in MySQL 4.1. > > > > > > Hope that helps, > > > > > > Mike > > > > > > > From: Scott H > > > > OK, I *THINK* I follow you here. Couple > of > > > > questions. I'm reading an online > tutorial > > > trying > > > > to figure this out, and I am led to > believe > > > mysql > > > > can't do nested queries, aka sub-queries. > But > > > you > > > > say it can? Is this recent? And I don't > have > > > a > > > > timestamp field, I have an autoincrement > > > field, > > > > but what do you mean by the "(@aa:=id)" > > > thing? I > > > > don't follow that. thanks. > > > > > > > > --- Dan Greene > > > <[EMAIL PROTECTED]> > > > > wrote: > > > > > What I would do is a classical > > > guesstimate > > > > > > > > > > find the average size per record (data > file > > > > > size + index file(s) size / # records > in > > > table) > > > > > > > > > > using that, find the data used per day > > > > > > > > > > using that, figure out how many days, > on > > > > > average it takes to hit 20GB > > > > > > > > > > let's say it's 89 days. > > > > > > > > > > right off the top, take 10% off for > safety, > > > now > > > > > we're at 80 days > > > > > > > > > > presuming your table has a timestamp > field: > > > > > > > > > > delete from log_table WHERE > TO_DAYS(NOW()) > > > - > > > > > TO_DAYS(date_col) > 80 > > > > > > > > > > if you don't have a timestamp field, > but > > > you do > > > > > have an autoincrement id field: > > > > > > > > > > figure out number of records on average > = > > > 20gb > > > > > (say it's 2M) > > > > > again, use 10% for safety (1.8M) > > > > > > > > > > select (@aa:=id) as low_id from > logtable > > > order > > > > > by id limit 1800,1 > > > > > delete from logtable where id < @aa > > > > > > > > > > (do subqueries work with a limit > clause?) > > > > > > > > > > > > > > > > -Original Message- > > > > > > From: Scott H > > > > > [mailto:[EMAIL PROTECTED] > > > > > > Sent: Wednesday, November 12, 2003 > 11:19 > > > AM > > > > > > To: Dan Greene; MySQL Mailing List > > > > > > Subject: RE: maintaining size of a db > > > > > > > > > > > > > > > > > > Yes sir, exactly. It's just that's > what > > > I'm > > > > > > looking for, and can't figure out. I > can > > > set > > > > > up > > > > > > a cron job, but what exactly would > the > > > SQL > > > > > delete > > > > > > statement be that would allow me to > > > delete > > > > > old > > > > > > records in such a way that the db > > > maintains > > > > > an > > > > > > approximately constant size on disk? > > > > > (Failing > > > > > > that perhaps a delete statement that > > > would > > > > > just > > > > > > have it maintain a constant # of > records? > > > > > > ...maybe t
RE: Having MySQL listen on multiple(2) ports at the same time
your best bet would be to do a port-forwarding methodology, at the os level, not in mysql itself I've used some for SOAP tracing, to intercept and view http traffic, but I'm sure there are some out there that just forward traffic w/o a gui > -Original Message- > From: Misaochankun [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 4:06 PM > To: [EMAIL PROTECTED] > Subject: Having MySQL listen on multiple(2) ports at the same time > > > Can this be done? > No, I do not mean running multiple MySQL servers. > I need to have MySQL listen on two separate ports at the same time. > Reason being, the new port is needed for a routing issue, and the > default port 3306 needs to stay up to respond to normal traffic. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backup question.
Yes, you can do it like this: Prompt> mysqldump --add-drop-table --host=source.IP.addr.spec -uuser -ppassword databasename | mysql -uuser -ppassword I've found that it helps things if you add --no-data to the source side on the first pass, then remove that clause and run it again. Dave -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:54 PM To: [EMAIL PROTECTED] Subject: Backup question. I would like to backup databases from a linux MySQL server to another linux machine on the same private network but I don' see in the docs how I can do this with mysqlhotcopy or mysqldump. Is there any way to do this besides using ftp. Any help would be appreicated. Richard -- 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]
Having MySQL listen on multiple(2) ports at the same time
Can this be done? No, I do not mean running multiple MySQL servers. I need to have MySQL listen on two separate ports at the same time. Reason being, the new port is needed for a routing issue, and the default port 3306 needs to stay up to respond to normal traffic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA ?
Hello, My Load data command seems not to load data into my db: mysql> load data local infile '/opt/week/ip.0311100440' into table logs.Nov03 ignore 2 lines; Query OK, 1 row affected (0.66 sec) Records: 48273 Deleted: 0 Skipped: 48272 Warnings: 48273 mysql> I have plenty of space where my data files are.. Can someone point me in the right direction to see why it is skipping those the table looks ok mysql> show table status; ++++---++-+-+--+---++-+-+-+-+-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++---++-+-+--+---++-+-+-+-+-+ | Nov03 | MyISAM | Dynamic| 72426930 |112 | 8140043192 | 1099511627775 | 5872661504 | 112 | 83656882 | 2003-11-06 07:19:13 | 2003-11-12 12:23:12 | 2003-11-12 10:04:36 | max_rows=10 | Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Digest Again, PLEASE SOMEONE HELP?
Unless I am misinformed, subscription to the DIGEST format of this list should cause all messages to come in one daily e-mail? Can someone else who is subscribed this way please at least let me know that this is at least the case for them? I continue to get every single post as an individual e-mail. I have tried canceling and re-subscribing with the DIGEST option selected, to no avail. I have also not received any response from admin. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4 Built in SSL?
Would anyone be kind enough to provide an example of using MySQL 4 with it's apparent built in SSL functionality to connect from a Win client to *Nix box? Am I correct in assuming that this new functionality means that I will not have to use stunnel or ssh tunneling? Thanks for any info! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
you may be able to put both statements to a text file, let's call it deleteold.sql then your cron job would be : mysql (put your connect stuff here) < deleteold.sql > -Original Message- > From: Scott H [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 3:17 PM > To: Michael McTernan; Dan Greene > Cc: MySQL Mailing List > Subject: RE: maintaining size of a db > > > Well, it sort of helps. But that section is > about future enhancements intended for mysql. I > need to set something up now, with the current > stable version. One thing I read (can't find it > now) indicated that the current version (I'm > actually running 4.0.15a) has "limited" support > for subqueries - but I don't know exactly how far > that goes. > > So let me set the stage a bit more - I'll assume > for now there is no reasonably simple way to work > with the actual size of the database on disk, and > instead will go with the idea that I can expect > the size of any one record to be of some average. > So, according to Dan's suggestion, if I do a > little math, and control the number of records, I > can control the size of the db -- approximately. > That's fine. Thus if have "seq" as an > autoincrement field, and I wanted to stay around > say 1000 records, deleting the oldest records, I > would need to run a cron job that would somehow > nest or relate these 2 sql statements: > > select (@aa:=seq) as low_seq from logtable order > by seq limit 1000,1 > > delete from logtable where seq < @aa > > I've tried putting this into a subquery format > but no luck. But I'm a noob, so I keep trying, > thinking I might hit on the right syntax. Or, is > there some way to pull the value of "low_seq" > into an environment variable and use it in a > script file to run the 2nd statement? Other > ideas? > > thanks, scott > > --- Michael McTernan wrote: > > >From the manual: > > > > 1.8.4.1 Subqueries > > > > Subqueries are supported in MySQL version 4.1. > > See section 1.6.1 Features > > Available in MySQL 4.1. > > > > Hope that helps, > > > > Mike > > > > > From: Scott H > > > OK, I *THINK* I follow you here. Couple of > > > questions. I'm reading an online tutorial > > trying > > > to figure this out, and I am led to believe > > mysql > > > can't do nested queries, aka sub-queries. But > > you > > > say it can? Is this recent? And I don't have > > a > > > timestamp field, I have an autoincrement > > field, > > > but what do you mean by the "(@aa:=id)" > > thing? I > > > don't follow that. thanks. > > > > > > --- Dan Greene > > <[EMAIL PROTECTED]> > > > wrote: > > > > What I would do is a classical > > guesstimate > > > > > > > > find the average size per record (data file > > > > size + index file(s) size / # records in > > table) > > > > > > > > using that, find the data used per day > > > > > > > > using that, figure out how many days, on > > > > average it takes to hit 20GB > > > > > > > > let's say it's 89 days. > > > > > > > > right off the top, take 10% off for safety, > > now > > > > we're at 80 days > > > > > > > > presuming your table has a timestamp field: > > > > > > > > delete from log_table WHERE TO_DAYS(NOW()) > > - > > > > TO_DAYS(date_col) > 80 > > > > > > > > if you don't have a timestamp field, but > > you do > > > > have an autoincrement id field: > > > > > > > > figure out number of records on average = > > 20gb > > > > (say it's 2M) > > > > again, use 10% for safety (1.8M) > > > > > > > > select (@aa:=id) as low_id from logtable > > order > > > > by id limit 1800,1 > > > > delete from logtable where id < @aa > > > > > > > > (do subqueries work with a limit clause?) > > > > > > > > > > > > > -Original Message- > > > > > From: Scott H > > > > [mailto:[EMAIL PROTECTED] > > > > > Sent: Wednesday, November 12, 2003 11:19 > > AM > > > > > To: Dan Greene; MySQL Mailing List > > > > > Subject: RE: maintaining size of a db > > > > > > > > > > > > > > > Yes sir, exactly. It's just that's what > > I'm > > > > > looking for, and can't figure out. I can > > set > > > > up > > > > > a cron job, but what exactly would the > > SQL > > > > delete > > > > > statement be that would allow me to > > delete > > > > old > > > > > records in such a way that the db > > maintains > > > > an > > > > > approximately constant size on disk? > > > > (Failing > > > > > that perhaps a delete statement that > > would > > > > just > > > > > have it maintain a constant # of records? > > > > > ...maybe this would be much simpler?) > > > > > > > > > > --- Dan Greene wrote: > > > > > > cronjob a sql script that runs a delete > > > > > > statement for old jobs daily > > > > > > > > > > > > > --- Egor Egorov wrote: > > > > > > > > Scott H wrote: > > > > > > > >> Can't seem to find this one in the > > > > manual > > > > > > or > > > > > > > >> archives - how do I control a db > > to > > > > > > maintain > > > > > > > >> its size to an arbitrary value, > > say 20 > > > > GB? > > > > > > I > > > > > > > >>
RE: Repeated table corruptions
Hi, Thanks - the tables were converted to MyISAM a while ago, and have run with no problem. Because they have been repaired before, following a previous crash, I believe that prior to the shut down last night all was well. I have run in to the issue of trying to repair a 'live' table, with subsequent disaster, but that is not the issue here - the issue is why does the table get corrupted in the first place. Regards Quentin Bennett Senior Analyst Infinity Solutions Ltd PO Box 3323, Auckland Ph: 09 921 8146 Fx: 09 309 4142 www.infinitytransport.net > -Original Message- > From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] > Sent: Thursday, 13 November 2003 9:16 a.m. > To: Quentin Bennett > Subject: RE: Repeated table corruptions > > > Are you shutting down mysql and running myisamchk -r? If not > then you can cause some major problems for your data. > > Also this was an ISAM table an old IBM format that > historically is unreliable in mySQL, thus the need for the > much more stable myISAM format. > > During the conversion process there might have been corrupted > data, which is causing some weird things to this day. Maybe > the best thing to do is to mysqldump the table, then > re-create it from the dump. > > > > - Dathan Vance Pattishall > - Sr. Programmer and mySQL DBA for FriendFinder Inc. > - http://friendfinder.com/go/p40688 > > > -->-Original Message- > -->From: Quentin Bennett [mailto:[EMAIL PROTECTED] > -->Sent: Wednesday, November 12, 2003 11:46 AM > -->To: [EMAIL PROTECTED] > -->Subject: Repeated table corruptions > --> > -->Hi, > --> > -->Repost - its happened again. Anyone any ideas PLEASE! > --> > -->Hi, > --> > -->Advice on the cause of the following table corruption > would be much > -->appreciated. > --> > -->Some background: > --> > -->This customer has been running MySQL for 3 or more years, and, for > back > -->up purposes, shuts MySQL down each night, takes a snapshot of the > tables > -->for backup, and restarts. > --> > -->We have suffered a table corruption before, of the same table, when > the > -->table was an ISAM table under version 3.23.40. We have > since upgraded > to > -->4.0.13, and changed the table to MyISAM type. > --> > -->Searching the list archive for 'Wrong bytesec' on mysql.com returns > no > -->results. Google returns some, but mainly old ones. Maybe > an appendix > to > -->the manual giving some information about the possible things that > -->myisamchk can find/repair would be useful? > --> > -->Are there any known problems with shutting down and re-starting so > -->regularly? Would a flush-tables followed by a check that > there are no > -->open tables achieve the same result. Is there a way of > stopping new > -->connections to a running server? > --> > -->mysql> REPAIR TABLE tt_tickets_bck > --> > -->and > --> > --># myisamchk -r tt_tickets_bck > --> > -->both fixed the issue, but I'm being asked why the > corruption happened > in > -->the first place. Myisamchk -vvv resulted in 212424 lines of > information. > --> > -->Thanks in advance > --> > -->Quentin Bennett > --> > --># mysqladmin version > --> > -->mysqladmin Ver 8.40 Distrib 4.0.13, for dec-osf5.1 on alphaev67 > --> > -->(-- Binaries from mysql.com --) > --> > -->Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB > This > -->software comes with ABSOLUTELY NO WARRANTY. This is free software, > and > -->you are welcome to modify and redistribute it under the GPL license > --> > -->Server version 4.0.13-max-log > --> > -->Protocol version 10 > --> > -->Connection Localhost via UNIX socket > --> > -->UNIX socket /data/mysql/mysql.sock > --> > -->Uptime: 1 hour 21 min 23 sec > --> > -->Threads: 37 Questions: 42387 Slow queries: 16 Opens: 258 Flush > --> > -->tables: 2 Open tables: 113 Queries per second avg: 8.681 > --> > -->mysql> check table tt_tickets_bck; > --> > -->++---+--+-- > -- > --- > --> > -->+ > --> > -->| Table | Op | Msg_type | Msg_text > --> > -->| > --> > -->++---+--+-- > -- > --- > --> > -->+ > --> > -->| mercury.tt_tickets_bck | check | warning | Table is marked as > crashed > --> > -->| > --> > -->| mercury.tt_tickets_bck | check | warning | 2 clients is using or > --> > -->hasn't closed the table properly | > --> > -->| mercury.tt_tickets_bck | check | warning | Size of datafile is: > --> > -->290516700 Should be: 285712092 | > --> > -->| mercury.tt_tickets_bck | check | error | Unexpected byte: 5 at > --> > -->link: 285506400 | > --> > -->| mercury.tt_tickets_bck | check | error | Corrupt > --> > -->| > --> > -->++---+--+-- > -- > --- > --> > -->+ > --> > -->5 rows in set (58.28 sec) > --> > --># myisamchk -vvv -r tt_tickets_bck > --> > -->- recov
RE: maintaining size of a db
Well, it sort of helps. But that section is about future enhancements intended for mysql. I need to set something up now, with the current stable version. One thing I read (can't find it now) indicated that the current version (I'm actually running 4.0.15a) has "limited" support for subqueries - but I don't know exactly how far that goes. So let me set the stage a bit more - I'll assume for now there is no reasonably simple way to work with the actual size of the database on disk, and instead will go with the idea that I can expect the size of any one record to be of some average. So, according to Dan's suggestion, if I do a little math, and control the number of records, I can control the size of the db -- approximately. That's fine. Thus if have "seq" as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq < @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of "low_seq" into an environment variable and use it in a script file to run the 2nd statement? Other ideas? thanks, scott --- Michael McTernan wrote: > >From the manual: > > 1.8.4.1 Subqueries > > Subqueries are supported in MySQL version 4.1. > See section 1.6.1 Features > Available in MySQL 4.1. > > Hope that helps, > > Mike > > > From: Scott H > > OK, I *THINK* I follow you here. Couple of > > questions. I'm reading an online tutorial > trying > > to figure this out, and I am led to believe > mysql > > can't do nested queries, aka sub-queries. But > you > > say it can? Is this recent? And I don't have > a > > timestamp field, I have an autoincrement > field, > > but what do you mean by the "(@aa:=id)" > thing? I > > don't follow that. thanks. > > > > --- Dan Greene > <[EMAIL PROTECTED]> > > wrote: > > > What I would do is a classical > guesstimate > > > > > > find the average size per record (data file > > > size + index file(s) size / # records in > table) > > > > > > using that, find the data used per day > > > > > > using that, figure out how many days, on > > > average it takes to hit 20GB > > > > > > let's say it's 89 days. > > > > > > right off the top, take 10% off for safety, > now > > > we're at 80 days > > > > > > presuming your table has a timestamp field: > > > > > > delete from log_table WHERE TO_DAYS(NOW()) > - > > > TO_DAYS(date_col) > 80 > > > > > > if you don't have a timestamp field, but > you do > > > have an autoincrement id field: > > > > > > figure out number of records on average = > 20gb > > > (say it's 2M) > > > again, use 10% for safety (1.8M) > > > > > > select (@aa:=id) as low_id from logtable > order > > > by id limit 1800,1 > > > delete from logtable where id < @aa > > > > > > (do subqueries work with a limit clause?) > > > > > > > > > > -Original Message- > > > > From: Scott H > > > [mailto:[EMAIL PROTECTED] > > > > Sent: Wednesday, November 12, 2003 11:19 > AM > > > > To: Dan Greene; MySQL Mailing List > > > > Subject: RE: maintaining size of a db > > > > > > > > > > > > Yes sir, exactly. It's just that's what > I'm > > > > looking for, and can't figure out. I can > set > > > up > > > > a cron job, but what exactly would the > SQL > > > delete > > > > statement be that would allow me to > delete > > > old > > > > records in such a way that the db > maintains > > > an > > > > approximately constant size on disk? > > > (Failing > > > > that perhaps a delete statement that > would > > > just > > > > have it maintain a constant # of records? > > > > ...maybe this would be much simpler?) > > > > > > > > --- Dan Greene wrote: > > > > > cronjob a sql script that runs a delete > > > > > statement for old jobs daily > > > > > > > > > > > --- Egor Egorov wrote: > > > > > > > Scott H wrote: > > > > > > >> Can't seem to find this one in the > > > manual > > > > > or > > > > > > >> archives - how do I control a db > to > > > > > maintain > > > > > > >> its size to an arbitrary value, > say 20 > > > GB? > > > > > I > > > > > > >> want to just rotate records, > deleting > > > > > those > > > > > > >> that are oldest. > > > > > > > > > > > > > > You can't restrict size of the > database > > > > > only > > > > > > > with MySQL, use disk quotas. > > > > > > > > > > > > No! That would just stop mysql right > in > > > its > > > > > > tracks (so to speak...) when it got > too > > > > > large. > > > > > > But I want old records sloughed off > and > > > the > > > > > db to > > > > > > continue running. (This is for a > central > > > > > syslog > > > > > > box.) > > > > > > > > > > > > > > > > . > > > > > > > > __ > > > > Do you Yahoo!? > > > > Protect your identity with Yahoo! Mail > > > AddressGuard >
RE: Time consuming table regeneration: JAVA & MySQL
You should determine where your bottlenecks are first. Are the updates on columns with keys? Can you disable the keys during the load? Are you using extended inserts? Is there available memory? What is the cpu doing? etc... -Original Message- From: karthikeyan [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 8:37 AM Cc: [EMAIL PROTECTED] Subject: Time consuming table regeneration: JAVA & MySQL I am in the process of regenerating all the tables as per the requirement of custom designed JAVA and JSP codes for 'chemistry' application. It requires lot of time to read, modify and write back to database. The Total number of data points per table containing various types of data (INT, VARCHAR, SMALLTEXT,MEDIUMTEXT, DOUBLE etc.,) 1) 5,86,000 x 28; (~17 hours) 2) 2,37,000 x 28; ... I believe (as usual) the delay it is due to Java routines rather than MySQL. However I would like to take help of experts here to modify MySQL configuration to improve the performance. Currently I am using default configuration. Should I upgrade to new version of MySQL (like jdk 1.3 to jdk1.4) if what are the precaution I should take to avoid loss of data .. === #This File was made using the WinMySQLAdmin 1.4 Tool #4/30/2003 8:51:39 AM #Uncomment or Add only the keys that you know how works. #Read the MySQL Manual for instructions [mysqld] basedir=C:/mysql #bind-address=127.0.0.1 datadir=C:/mysql/data #language=C:/mysql/share/your language directory #slow query log#= tmpdir=c:/temp #port=3306 #set-variable=key_buffer=16M [WinMySQLadmin] Server=C:/mysql/bin/mysqld-nt.exe user=karthi password= other significant i variables as seen in WinMySQLadmin 1.4: (can we get this information as a text file? from mysql ) = variable name : value = bulk_insert_buffer_size: 8388608 innodb_buffer_pool_size: 8388608 innodb_log_buffer_size: 1048576 innodb_log_file_size: 5242880 interactive_timeout: 28800 join_buffer_size: 131072 key_buffer_size: 8388300 max_allowed_packet: 1048576 max_binlog_cache_size: 4294967295 max_binlog_size: 1073741824 max_heap_table_size: 16777216 max_join_size: 4294967295 max_write_lock_count: 4294967295 myisam_max_extra_sort_file_size: 268435456 myisam_max_sort_file_size: 2147483647 sort_buffer_size: 2097144 tmp_table_size: 33554432 version: 4.0.12-nt wait_timeout: 28800 etc., = M.Karthikeyan http://www.molecularsociety.org/ -- 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: table creation
Create the column as a timestamp and the column will be updated on insert and update. -Original Message- From: Mikel - [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 10:17 AM To: [EMAIL PROTECTED] Subject: table creation Hello list: Is it possible to create a table with a date column with current date as is default value?, like curdate() or now() values?, any ideas. - MySQL 3.23.58-Max-log - RH. 7.3 Greetings and thanks in advanced Mikel _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.microsoft.com/es -- 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]
bug in replication?
ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1597257529, event_type: 49 ERROR: Could not read entry at offset 240378281 : Error in log format or read error On the master it's reporting this error. The data_len is 1.48 GB which is an error. I know that this is not the case since I don't insert 1.4GB of data in one sql statement. At this offset, the binary log is corrupt. Also the size of the binary file is: (Note: size and position are related) 240378300 Nov 12 00:04 ef112-bin.060 I read someplace that this is due to the binlog pointer becoming displaced. I don't believe it's a big problem for me since only 19 bytes of data is missing and queries to this box are never <= 19 bytes. I assume its some padding of data/or bad data. Unfortunately the only way that I can recover from a possible missed event on the master-is to read every row of the master and compare it with every row of the slave once the master and slave are in-sync. Anyone have better solution? - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688
RE: Backup question.
mysqldump -uname -ppassword -BDatabase | mysql -uname -ppasswrod -Ddatabase -hremotehost -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:54 PM To: [EMAIL PROTECTED] Subject: Backup question. I would like to backup databases from a linux MySQL server to another linux machine on the same private network but I don' see in the docs how I can do this with mysqlhotcopy or mysqldump. Is there any way to do this besides using ftp. Any help would be appreicated. Richard -- 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]
Query with temporary table
Is is possible to do a select query with a left join from a real table to a temporary table? I'm trying it but keep getting "unkown table 'tablename' in field list" error. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repeated table corruptions
Hi, Repost - its happened again. Anyone any ideas PLEASE! Hi, Advice on the cause of the following table corruption would be much appreciated. Some background: This customer has been running MySQL for 3 or more years, and, for back up purposes, shuts MySQL down each night, takes a snapshot of the tables for backup, and restarts. We have suffered a table corruption before, of the same table, when the table was an ISAM table under version 3.23.40. We have since upgraded to 4.0.13, and changed the table to MyISAM type. Searching the list archive for 'Wrong bytesec' on mysql.com returns no results. Google returns some, but mainly old ones. Maybe an appendix to the manual giving some information about the possible things that myisamchk can find/repair would be useful? Are there any known problems with shutting down and re-starting so regularly? Would a flush-tables followed by a check that there are no open tables achieve the same result. Is there a way of stopping new connections to a running server? mysql> REPAIR TABLE tt_tickets_bck and # myisamchk -r tt_tickets_bck both fixed the issue, but I'm being asked why the corruption happened in the first place. Myisamchk -vvv resulted in 212424 lines of information. Thanks in advance Quentin Bennett # mysqladmin version mysqladmin Ver 8.40 Distrib 4.0.13, for dec-osf5.1 on alphaev67 (-- Binaries from mysql.com --) Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.13-max-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /data/mysql/mysql.sock Uptime: 1 hour 21 min 23 sec Threads: 37 Questions: 42387 Slow queries: 16 Opens: 258 Flush tables: 2 Open tables: 113 Queries per second avg: 8.681 mysql> check table tt_tickets_bck; ++---+--+--- + | Table | Op | Msg_type | Msg_text | ++---+--+--- + | mercury.tt_tickets_bck | check | warning | Table is marked as crashed | | mercury.tt_tickets_bck | check | warning | 2 clients is using or hasn't closed the table properly | | mercury.tt_tickets_bck | check | warning | Size of datafile is: 290516700 Should be: 285712092 | | mercury.tt_tickets_bck | check | error | Unexpected byte: 5 at link: 285506400 | | mercury.tt_tickets_bck | check | error | Corrupt | ++---+--+--- + 5 rows in set (58.28 sec) # myisamchk -vvv -r tt_tickets_bck - recovering (with sort) MyISAM-table 'tt_tickets_bck' Data records: 5641601 - Fixing index 1 - Searching for keys, allocating buffer for 46268 keys Wrong bytesec: 31- 0- 0 at 304; Skipped Wrong bytesec: 49- 55- 53 at 320; Skipped Wrong bytesec: 5- 87- 50 at 328; Skipped Wrong bytesec: 48- 53- 55 at 285506420; Skipped . . . Wrong bytesec: 4- 49- 53 at 692; Skipped Wrong bytesec: 5- 87- 50 at 700; Skipped Found block with impossible length 3289448 at 285506560; Skipped Wrong bytesec: 27- 0- 0 at 1080; Skipped Wrong bytesec: 56- 54- 48 at 1096; Skipped Wrong bytesec: 87- 50- 29 at 1108; Skipped . . . Wrong bytesec: 27- 0- 0 at 5080; Skipped Wrong bytesec: 53- 53- 55 at 5096; Skipped Wrong bytesec: 87- 50- 29 at 5108; Skipped Found block with too small length at 5116; Skipped Wrong bytesec: 27- 0- 0 at 5120; Skipped Wrong bytesec: 53- 53- 55 at 5136; Skipped . . . Wrong bytesec: 56- 50- 56 at 5762080; Skipped Wrong bytesec: 87- 50- 29 at 5762092; Skipped Delete link points outside datafile at 285512060 Wrong bytesec: 27- 0- 0 at 5762148; Skipped Wrong bytesec: 54- 57- 57 at 5762164; Skipped . . Wrong bytesec: 200- 3- 1 at 290516684; Skipped - Merging 5621599 keys - Last merge and dumping keys - Fixing index 2 - Searching for keys, allocating buffer for 139678 keys - Merging 5621599 keys - Last merge and dumping keys - Fixing index 3 - Searching for keys, allocating buffer for 104727 keys - Merging 5621599 keys - Last merge and dumping keys Data records: 5621599 # Quentin Bennett The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated.
Backup question.
I would like to backup databases from a linux MySQL server to another linux machine on the same private network but I don' see in the docs how I can do this with mysqlhotcopy or mysqldump. Is there any way to do this besides using ftp. Any help would be appreicated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Kill a query
On Wed, Nov 12, 2003 at 02:29:12PM -0500, Jeff McKeon wrote: > Is there a way to abort a query after it's running? You can use the KILL command from a separate connection. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 59 days, processed 2,262,830,373 queries (437/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maybe easy, maybe hard SELECT puzzle :)
Jonathan Terhorst wrote:
I can't figure out if this is actually challenging or if it's a stupid
question. Table1 is a normal old relation that describes a bunch of objects:
Table1 (id INT PRIMARY KEY NOT NULL, Name varchar(255), Size int) etc.
Associated with each Table1 record is a variable-length of 3-byte
alphanumeric feature codes e.g. "A20". I've put these into table called
Features, that is,
Features (id INT PRIMARY KEY NOT NULL REFERENCES Table1, FeatureCode char(3)
NOT NULL);
So a typical record from Table1 might look like:
id: 123
Name: Foo
Size: 42
And the corresponding Features entries:
id: 123
FeatureCode: A01
id: 123
FeatureCode: Z99
id: 123
FeatureCode: X42
My question is how to pull all records from Table1 that have certain
FeatureCodes associate with them. If it's just one FeatureCode (say, 'T56')
I'm looking for then it's easy:
SELECT * from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='T56';
But it I want all records from Table1 that have features 'A01' _and_ 'B02',
clearly
SELECT * from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='A01' AND Features.FeatureCode='B02' doesn't work.
One way I have found to implement this is
SELECT Table1.id from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='A01' OR Features.FeatureCode='B02' GROUP BY Table1.id
HAVING COUNT(*)=2;
e.g. counting the duplications of id and selecting those equal to the number
of FeatureCodes I'm searching for. But this seems somehow inelegant, and I'm
justing wondering if there's a better way that's staring me in the face.
Thanks
My first thought whaen I saw your question was that you could join the
Features table with itself on the id, looking for one feature on the
left and the other feature on the right. Something like this:
SELECT Table1.id AS id
FROM Features AS f1, Features AS f2, Table1
WHERE f1.id=f2.id AND f1.id=Table1.id
AND f1.FeatureCode='A01' AND f2.FeatureCode='B02';
Whether you would call that better or elegant relative to the solution
you've already found is up to you.
You could also modify your version to use IN, as suggested by Shane
Allen (I don't think DISTINCT will help you, but I could be wrong).
Then it would look like this:
SELECT Table1.id from Table1 JOIN Features USING(id)
WHERE Features.FeatureCode IN ('A01', 'B02')
GROUP BY Table1.id
HAVING COUNT(*)=2;
Perhaps that looks better/more elegant?
In particular, if you want to do this programmatically for any number of
features, this way is probably easier. For example, to find the ids in
Table1 with 3 specified features, the first version above would be
SELECT Table1.id AS id
FROM Features AS f1, Features AS f2, Features AS f3, Table1
WHERE f1.id=f2.id AND f2.id=f3.id AND f1.id=Table1.id
AND f1.FeatureCode='A01' AND f2.FeatureCode='B02'
AND f3.FeatureCode='Z99';
Whereas the second version would become
SELECT Table1.id from Table1 JOIN Features USING(id)
WHERE Features.FeatureCode IN ('A01', 'B02', 'Z99')
GROUP BY Table1.id
HAVING COUNT(*)=3;
As you can see, the latter generalizes well to
SELECT Table1.id from Table1 JOIN Features USING(id)
WHERE Features.FeatureCode IN (list of FeatureCodes)
GROUP BY Table1.id
HAVING COUNT(*)=#_of_FeatureCodes_in_list;
I suspect this may be more efficient than the multiple join way, as
well, but I wouldn't swear to it without testing first.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API: undefined reference
Thanx so much! mysql_real_connect() certainly works.
Best,
hAj
on 2003.11.11 17:55, Nick Gaugler at [EMAIL PROTECTED] wrote:
> http://www.mysql.com/doc/en/mysql_connect.html
>
> This function is deprecated. It is preferable to use
> mysql_real_connect() instead.
>
>
>
> #include
> #include
>
> int main(void) {
> MYSQL mysql;
>
> if(mysql_init(&mysql) == NULL) {
> fprintf(stderr,"Unable to initlize MySQL structure.\n");
> return(1);
> }
>
> if(mysql_real_connect(&mysql,"127.0.0.1","user","password", "db", 0,
> NULL, 0) == NULL) {
> fprintf(stderr,"Unable to connect to MySQL: %s\n",
> mysql_error(&mysql));
> return(1);
> }
>
> return(0);
> }
>
>
> mybox:/home/nickgsu > gcc seeLog.c -o seeLog -I/usr/local/mysql/include
> -L/usr/local/mysql/lib -lmysqlclient -lz
> mybox:/home/nickgsu > ./seeLog
> Unable to connect to MySQL: Access denied for user: '[EMAIL PROTECTED]'
> (Using password: YES)
>
>
>
>
> Good luck,
>
>
> nickg
>
>
> -Original Message-
> From: hAj [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 10, 2003 4:03 PM
> To: [EMAIL PROTECTED]
> Subject: C API: undefined reference
>
> Hello MySQL pros worldwide,
>
>
> ~/www ->cat seeLog.c
> #define USE_OLD_FUNCTIONS
> #include
> #include
>
> int main() {
> MYSQL mysql;
> MYSQL *mysqldb = NULL;
>
> mysqldb = mysql_connect(&mysql, "geneofcube.net", "USERID",
> "PASSWORD");
>
> return 0;
> }
> ~/www ->gcc seeLog.c -o seeLog -I/usr/include -L/usr/lib -lmysqlclient
> /tmp/ccEj3tmv.o: In function `main':
> /tmp/ccEj3tmv.o(.text+0x2a): undefined reference to `mysql_connect'
> collect2: ld returned 1 exit status
> ~/www ->
>
>
> As shown above, I'm having a problem getting rid of a compilation error
> (undefined reference) coming out with a very simple c code (seeLog.c)
> which
> I wrote for a testing purpose.
> Got no I idea what I'm doing wrong or missing here.
>
> I'd appreciate any of your suggestions.
>
>
> Best,
> hAj
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Kill a query
Is there a way to abort a query after it's running? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mike -- ...and then Michael Stassen said... % % Mikael Fridh wrote: [And thank you to Mikael, too.] % % >On Wednesday 12 November 2003 17.15, David T-G wrote: % > % >>What if one user connects to the database as different users (as I do, in ... % > % >set up different my.cnf files for your different scripted users. % >Make sure that they have proper permissions % >then call mysql from your scripts like this: % >mysql --defaults-file=/home/blah/secret/stupid-user.my.cnf % >mysql --defaults-file=/home/blah/secret/clever-user.my.cnf A... % > % >more info from "mysql --help" OK. % % Right. Note that the --defaults-file= option does not just specify an % alternate .my.cnf, it also prevents looking in the global configuration % files /etc/my.cnf and mysql_data_dir/my.cnf Right. % (/usr/local/mysql/data/my.cnf, for example). So, if you have anything % set in one of the global files which you need, you'll have to duplicate % those settings in your local files. Right. Kind of a bummer. % % Alternatively, you can use the --defaults-extra-file= option. This % reads the named file in between the global files and .my.cnf. Last % mention of a setting wins, so you would not put a password in .my.cnf in % this case. Aha! Most excellent. Yes, I think that that would be a good plus safe way to go. Then one need only make sure that mysql is in the path under cron :-) % % Wouldn't it be nice if these options were mentioned in the mysql man % page? Or is it just me? Well, it could be, but if it's there I missed it, too! Thanks again & HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/sooCGb7uCXufRwARAiPsAJ0aMingtyWkmjwTehyBohOGJ19/BACdFW/x wkUhMt+ALUtMM060bUnxvM4= =QcY8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
64-bit client connect to 32-bit Server
Hello All, Can a 64-bit MySQL client connect to a 32-bit server? Are there any restrictions? Thanks, Dave Ritter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some help with a complex query
I'm sorry I didn't explain anything. The problem is that it 's too slow and the result doesn't appear. I am going to try to explain the query. Fields from Table FASE: (300.000 records) ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, SQL_ID_PY char(6), SQL_ID_CE char(6), PR_flag INT, PR_Date_Visita_2 Date, AU_PR_Aula varchar(255) (it a field that contain SQL_ID_PY_SQL_ID_CE_PR) Field from Table CE (30.000 records) CE_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, CE_ID_CE char(6), CE_Centro varchar(32), CE_Domicilio varchar(32), CE_CP varchar(5), CE_Poblacion varchar(30), CE_ID_Capital char(2), CE_Capital varchar(30), CE_ID_PROV char(2), CE_PROV varchar(15), CE_ID_CCAA char(2), CE_CCAA varchar(15) Field from Table CA (30.000 records) CA_ID INT NOT NULL PRIMARY KEY, CA_ID_User char(6), CA_ID_CE char(6), CA_Centro varchar(32), CA_ID_Idioma_A INT, CA_Horario varchar(30) Fields from table AU (700.000 records) AU_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, AU_ID_CE char(6), AU_ID_PY char(6), AU_ID_FASE INT, AU_A_M INT, AU_A_F INT, AU_Aula varchar(32) (it a field that contain AU_ID_PY_AU_ID_CE_PR) Fields from table PP (200.000 records) PP_ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, PP_ID_PP char(6), PP_ID_CE char(6), PP_Contacto char(50), PP_ID_Cargo char(6), PP_Cargo char(32) I select from Fase some records. From fase I only want records (SQL_ID_CE) that have FASE.SQL_ID_PY='P081' AND FASE.PR_FLAG= '1'. From this selection, I only want records that in AU have AU.AU_NIVEL= '13.14' and in CA have CA.CA_ID_IDIOMA_A= '6'. In WHERE I write AU.AU_Aula= fase.AU_PR_Aula AND AU.AU_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = CA.CA_ID_CE AND CE.CE_ID_CE = Fase.SQL_ID_CE AND CE.CE_ID_CE = PP.PP_ID_CE AND Fase.PR_PP_ID_Coord = PP.PP_ID_PP Main relation in all tables is SQL_ID_CE. I don't know if I explain myself or it is too boring to continue reading. It will be fantastic if some can help me. I don't know if I am doing something wrong or what. > * Elisenda >> I have a query which tries to select different fields from 5 different >> tables. >> >> In WHERE part I have write all the conditions and relationships. >> Perhaps two many. > > Joining 5 tables should not be a problem, but having indexes on the relevant > columns may be essential, especially on large tables. > >> The main table for me is FASE. From this table I try to find all the other >> information. >> >> I guess I'm doing something wrong but I don't know what. > > What is the problem? Do you get an error message, does it return unexpected > results, or is it just too slow? > >> SELECT >> >> CE.CE_CENTRO, >> CE.CE_DOMICILIO, >> CE.CE_CP, >> CE.CE_POBLACION, >> CE.CE_PROV, >> PP.PP_CONTACTO, >> PP.PP_CARGO, >> CA.CA_HORARIO, >> AU.AU_A_M, >> AU.AU_A_F, >> FASE.PR_DATE_VISITA_1 >> >> FROM AU, CA, CE,FASE,PP >> >> WHERE >> >> FASE.SQL_ID_PY='P081' AND >> FASE.PR_FLAG= '1' AND >> CA.CA_ID_IDIOMA_A= '6' AND >> AU.AU_NIVEL= '13.14' AND >> AU.AU_Aula= fase.AU_PR_Aula AND >> AU.AU_ID_CE = CA.CA_ID_CE AND >> CE.CE_ID_CE = CA.CA_ID_CE AND >> CE.CE_ID_CE = Fase.SQL_ID_CE AND >> CE.CE_ID_CE = PP.PP_ID_CE AND >> Fase.PR_PP_ID_Coord = PP.PP_ID_PP > > It's difficult to suggest changes without knowing what the problem is... :) > I can however safely suggest that you use a consistent letter casing on your > table names... is it FASE, Fase or fase? On some MySQL servers this will > make a difference, on others it may not. (I think mysql on windows is case > insensitive by default, but this may be changed at compile-time, iirc.) > > Please tell us what the problem is, and if it's about efficiency, post the > output of "EXPLAIN SELECT ", that should get us started. :) > > -- > Roger > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: newbie question on data accumulation
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 -->-Original Message- -->From: joffrey leevy [mailto:[EMAIL PROTECTED] -->Sent: Wednesday, November 12, 2003 9:58 AM -->To: [EMAIL PROTECTED] -->Subject: newbie question on data accumulation --> -->Hi all --> -->Curious as to what happens after data is repeatedly -->selected from a mysql table overtime. If the data is not fragmented from constant deletes, it will continue to stay at a constant fast rate. The table is put into a table cache to reduce the amount of opens, the data is stored in shared memory in a key buffer, thus the speed of mysql. If your table does not change, and the query is the same turn on the query cache, you'll see a 256 times speed increase. Does it -->accumulate as junk data, stored at some location and -->eventually slow down the database/program/server? The queries do eventually slow down if there are a lot of deletes that fragments your table. This can be repaired with an optimize table. -->Does any purging have to take place? Don't know what you mean with this question. --> --> -->Thanks -->J --> --> -->__ -->Do you Yahoo!? -->Protect your identity with Yahoo! Mail AddressGuard -->http://antispam.yahoo.com/whatsnewfree --> -->-- -->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]
Can not make PULL from mysql.bkbits.net
Hi, I wish to make a "pull" for mysql-5.0 from http://mysql.bkbits.net but always is nothing to pull. I try with : bk pull http://mysql.bkbits.net/mysql-5.0 I wonder, what happens ? Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED]
RE: Error 127 = Record-file is crashed
Yes let it finish. Never stop a repair in progress. Now myisamchk is rebuilding the index file since youre the table is extremely corrupt. Myisamchk can run faster if you set some buffer properties. Put this in youre my.cnf file for next time. [myisamchk] key_buffer=256M sort_buffer=256M read_buffer=2M write_buffer=2M - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 -->-Original Message- -->From: rmck [mailto:[EMAIL PROTECTED] -->Sent: Tuesday, November 11, 2003 5:27 PM -->To: Dathan Vance Pattishall -->Subject: RE: Error 127 = Record-file is crashed --> -->Thank you for the reply --> -->Before you replied I killed the first "myisamchk" thinking I should do -->"REPAIR TABLE" -->Then your email came and I killed the "REPAIR TABLE" and -->reran -->myisamchk -rf Nov03. -->Thats whats running now "myisamchk -rf Nov03" --> -->NOW I noticed my .MYI file is at 1024K: --> -->[root]# ls -al Nov* -->-rw-rw1 mysqlmysql8802 Nov 6 07:04 Nov03.frm -->-rw-rw1 mysqlmysql8812359152 Nov 11 01:25 Nov03.MYD -->-rw-rw1 mysqlmysql1024 Nov 11 16:33 Nov03.MYI -->-rw-r--r--1 root root 67108864 Nov 11 17:05 Nov03.TMD --> -->Man I really messed this up? Should I let this keep on running?? --> -->Thank You -->Rob --> -->-Original Message- -->From: Dathan Vance Pattishall <[EMAIL PROTECTED]> -->Sent: Nov 11, 2003 4:19 PM -->To: 'rmck' <[EMAIL PROTECTED]>, [EMAIL PROTECTED] -->Subject: RE: Error 127 = Record-file is crashed --> --> --> -->- Dathan Vance Pattishall --> - Sr. Programmer and mySQL DBA for FriendFinder Inc. --> - http://friendfinder.com/go/p40688 --> --> -->-->-Original Message- -->-->From: rmck [mailto:[EMAIL PROTECTED] -->-->Sent: Tuesday, November 11, 2003 3:17 PM -->-->To: [EMAIL PROTECTED] -->-->Subject: Error 127 = Record-file is crashed -->--> -->-->Hello, -->--> -->-->I have "mysql-standard-4.0.16-pc-linux-i686" installed on a 4gig mem, -->-->2cpu system, RH 9. -->-->I have a large table (Data records: 72426930) that is now giving me -->this -->-->error "Error 127" -->-->when doing selects... -->--> -->-->This is my first crash of any kind with Mysql. So I'm a liitle -->confused -->-->on what I should do. -->-->The manual states -->-->use myisamchk, then you read further and it says use REPAIR TABLE... -->--> -->-->I'm confused can someone give me a 1 to end step on repairing this -->-->table?? -->--> -->-->At this point I brought my mysqld down. -->--> -->-->Tried running this: -->-->[root]# myisamchk -r Nov03 -->-->- recovering (with keycache) MyISAM-table 'Nov03' -->-->Data records: 72426930 -->-->myisamchk: error: Can't create new tempfile: '/opt/logs/Nov03.TMD' -->-->MyISAM-table 'Nov03' is not fixed because of errors -->-->Try fixing it by using the --safe-recover (-o) or the --force (-f) -->option -->--> -->-->So I tried this: -->-->[root]# myisamchk -rf Nov03 -->-->[EMAIL PROTECTED] ip_logs]# myisamchk -rf Nov03 -->-->- recovering (with keycache) MyISAM-table 'Nov03' -->-->Data records: 72426930 -->-->5939000 -->--> -->-->It looks like its doing something -->--> -->-->my data dir and the table in question: -->-->-rw-rw1 mysqlmysql8812359152 Nov 11 01:25 Nov03.MYD -->-->-rw-rw1 mysqlmysql5346325504 Nov 11 14:52 Nov03.MYI -->-->-rw-rw1 mysqlmysql631242752 Nov 11 14:52 Nov03.TMD -->--> -->-->Please advise... --> -->Good you turned off mysql and ran this command. If you didn't you would -->just cause further corruption. Next the TMD file means that as myisamchk -->repairs, this temp file above is appended as the datafile is compared to -->the index file. This is to ensure that the source data file is not -->modified (yet) in case of row loss. If you need to recover the lost rows -->that occur during this repair (source data file will be stored in a .BAK -->file) then it's possible. --> -->So, what you need to do right now is wait till that 631242752 file size -->equals the 8812359152 file size. --> -->Myisamchk will report how many rows have been lost if any during the end -->of the repair. --> --> -->--> -->-->Thanks -->-->Rob -->--> -->--> -->--> -->-->-- -->-->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]
bdb: page 0: illegal page type or format
Dear DBA's I'm setting up a database and altered several tables to BDB type in order to enable transactions. Everything went fine until I restarted mysqld. Starting mysql now gets messages like Didn't find any fields in table 'xxx' for those tables altered to BDB type. Looking in the error logs, the problem was engendered at start-up: /usr/sbin/mysqld: ready for connections. Version: '4.0.13-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 031112 11:35:58 bdb: page 0: illegal page type or format 031112 11:35:58 bdb: PANIC: Invalid argument I'm reading the docs and P DuBois' book, but am stuck. I would appreciate any suggestions what steps to take next. I know it's really basic and I beg your indulgence. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database-design
You may find the SET column type of use here.
http://www.mysql.com/doc/en/SET.html
You could declare column Quarter as
Quarter SET ("one", "two", "three", "four") NOT NULL
or even
Quarter SET ("1", "2", "3", "4") NOT NULL
Then mysql will do some of the work for you.
Michael
Meli Meli wrote:
Thanks to everybody for helping me.
I agree with the suggestion to change the quarter fields in to
one single field. The suggestion from Dan Greene to store the
quarters as binary values sounds good to me. So I will do it this way.
To explain you more about this table. The quarter fields are representing
8 hours of a working day. Each quarter represents 2 hours. First quarter
represents first 2 hours and so on.. I need to register activities made
during the day and if there is an activitie, I need to register in which
quarter of the day. So there made be no activity ore one or to etc.
The year, month, week, and day fields are actually year, cycle, week
and day. This fields are not representing exact dates because the year is
divided in to cycles. So one year has 8 cycles and one cycle has 6 weeks
But also these fields I will combine to one data field and store a binary value.
The table finally looks like:
Id
Quarter ->(binary value)
Eventdate ->(binary value)
Timestamp
Comments
That’s it!
Thanks an regards
Martin
Dan Greene <[EMAIL PROTECTED]> wrote:
I think that I must be missing something, as I agree with all the suggestions that to change the seperate date element columns to a single date field, but Meli's original post had a date falling into multiple quarters. Now to my knowledge, a date can only be in one quarter, from a calendar point of view, so maybe there's something more to Meli's issue...
to store the info more efficiently for what you're saying, you could also use binary as a guide
1 2 3 4
s n r t
t d d h
___
8 4 2 1
- - - -
1000 = 8
0100 = 4
0010 = 2
0001 = 1
1100 = 12
1010 = 10
1001 = 9
0110 = 6
0101 = 5
0011 = 3
1110 = 14
1101 = 13
1011 = 11
0111 = 7
= 15
= 0 (which you don't have below but here for completeness)
and store a single number that represents the pattern you have below, replacing 'null' with 0 and x as 1
x null null null
null x null null
null null x null
null null null x
x x null null
x null x null
x null null x
null x x null
null x null x
null null x x
x x x null
x x null x
x null x x
null x x x
x x x x
Ladies and Gentlemen, the first real use of the bitwise section of the java certification exam I have ever used
-Original Message-
From: Brent Baisley [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 12, 2003 9:01 AM
To: Meli Meli
Cc: [EMAIL PROTECTED]
Subject: Re: Database-design
Why would you created separate fields for each quarter?
Create a field
called quarter and store a number in it. You could also combine year,
month and day into a date field, which would make it easier to search
on ranges.
So, I think your table should look like this:
id
quarter
eventdate
week
On Tuesday, November 11, 2003, at 02:33 PM, Meli Meli wrote:
I have a table with following structure:
Id
first quarter
second quarter
third quarter
last quarter
year
month
week
day
On an entry not all fields of the four quarter fields are
covered with
values.
Following combinations are possible:
first quarter | second quarter | third quarter | last quarter
x null null null
null x null null
null null x null
null null null x
x x null null
x null x null
x null null x
null x x null
null x null x
null null x x
x x x null
x x null x
x null x x
null x x x
x x x x
The table will receive many thousands of entry's.
Would it be better to divide the table in to 15 small
tables in order
to not register fields with null values?
Thanks for helping
Regards Martin
-
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
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]
-
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
William R. Mussatto skrev: > Joe Lewis said: >> We're experiencing the same issues, but not neccesarily after an >> upgrade. We're using MySQL 4.0.12 and FreeBSD 4.7 Release. We're >> getting max'd connections only on specific users, and the "show >> processlist" is returning only the "show processlist" process. the >> results of netstat show absolutely nothing. >> >> What I think is happening is the connections are not properly getting >> closed. The users are allowed to connect after a "flush user_resources" >> is run. Is there a bug in the particular version of MySQL (4.0.12) >> where the "user connections" are not getting decremented when a >> connection is "closed"? >> >> Joe >> >> Henrik Skotth wrote: >> >>> I'm mostly using mytop, and that's the way that I see that there are >>> no (are almost no) connections when the server claims that it is above >>> the connection limit... So I guess that there's something seriously >>> wrong then... Any ideas what? >>> >>> -- Henrik >>> >>> >>> [EMAIL PROTECTED] (Pete Harlan) skrev: >>> What does "show processlist" say when the connections are maxed out? (You may have to leave a client logged in to reserve a slot so you can submit this query.) If it shows only a few connections, then there's something seriously wrong. If it shows a ton of idle connections, it should tell you which machines they are coming from and which users, and that should help you track down who's holding connections open. --Pete On Wed, Nov 12, 2003 at 01:15:54AM +0100, Henrik Skotth wrote: >Hello! > >I have tested this now, and that isn't the case. Any other ideas? > >-- Henrik > > >Michael McTernan skrev: > >>Hi, >> >>Have you tried "netstat -a" on the box with the MySQL server? This >> command >>(Linux) will show what is connected to where, and will help you >> double check >>that there really aren't any open connections to the server. >> >>Thanks, >> >>Mike >> >> >>>-Original Message- >>>From: Henrik Skotth [mailto:[EMAIL PROTECTED] >>>Sent: 10 November 2003 18:54 >>>To: [EMAIL PROTECTED] >>>Subject: Re: max_user_connections problem after upgrading >>> >>> >>>That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so > >there > >>>is >>>no limit to exceed. >>>Also, we aren't getting the error messages ALL the time, they start > >to > >>>appear after a day or two and gets more and more frequent untill I >>> restart >>>mysql. Any other ideas? >>> >>>-- Henrik >>> >>>gerald_clark skrev: >>> Are you sure you are net exceeding the setting for maximum connections per hour for that user? Henrik Skotth wrote: >Hi! > >What I meant was that even if there are currently only two user > connections being used, and the limit is 300, we still get the >>> >>>"already >>>more than max_user_connections" error... >>> >-- Henrik > >gerald_clark skrev: > > > >>Henrik Skotth wrote: >> >> >> >> >>>Hello all, >>> >>>We recently upgraded to 4.0, it went >>>very well and the performance gains have been great. >>>But now the server has started to act strangely. Every few >>> days, > >the > >>> >server starts to refuse connections, saying > > > >>>that there is already more than max_user_connections, but there > >is > >>> >really only one or two active connections and our > >max_user_connections > >>>is 300. I have to take down and restart the server to solve the > >problem, > >>>and it keeps happening over and over again every few days... >>> > >>>Am I the only one having this problem? Any suggestions? >>> >>>Regards, >>>-- Henrik Skotth, Hogwarts.nu > >> >>Are there 298 or 299 inactive connections? >>If so, why are they not being closed? > Is the user running "show processlist" allowed to see all processes (e.g. > 'root')? > In my case, yes. And all the processes (except for the "show processlist") are being run by the same user (mysql). -- Henrik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
>From the manual: 1.8.4.1 Subqueries Subqueries are supported in MySQL version 4.1. See section 1.6.1 Features Available in MySQL 4.1. Hope that helps, Mike > -Original Message- > From: Scott H [mailto:[EMAIL PROTECTED] > Sent: 12 November 2003 17:45 > To: Dan Greene; MySQL Mailing List > Subject: RE: maintaining size of a db > > > OK, I *THINK* I follow you here. Couple of > questions. I'm reading an online tutorial trying > to figure this out, and I am led to believe mysql > can't do nested queries, aka sub-queries. But you > say it can? Is this recent? And I don't have a > timestamp field, I have an autoincrement field, > but what do you mean by the "(@aa:=id)" thing? I > don't follow that. thanks. > > --- Dan Greene <[EMAIL PROTECTED]> > wrote: > > What I would do is a classical guesstimate > > > > find the average size per record (data file > > size + index file(s) size / # records in table) > > > > using that, find the data used per day > > > > using that, figure out how many days, on > > average it takes to hit 20GB > > > > let's say it's 89 days. > > > > right off the top, take 10% off for safety, now > > we're at 80 days > > > > presuming your table has a timestamp field: > > > > delete from log_table WHERE TO_DAYS(NOW()) - > > TO_DAYS(date_col) > 80 > > > > if you don't have a timestamp field, but you do > > have an autoincrement id field: > > > > figure out number of records on average = 20gb > > (say it's 2M) > > again, use 10% for safety (1.8M) > > > > select (@aa:=id) as low_id from logtable order > > by id limit 1800,1 > > delete from logtable where id < @aa > > > > (do subqueries work with a limit clause?) > > > > > > > -Original Message- > > > From: Scott H > > [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, November 12, 2003 11:19 AM > > > To: Dan Greene; MySQL Mailing List > > > Subject: RE: maintaining size of a db > > > > > > > > > Yes sir, exactly. It's just that's what I'm > > > looking for, and can't figure out. I can set > > up > > > a cron job, but what exactly would the SQL > > delete > > > statement be that would allow me to delete > > old > > > records in such a way that the db maintains > > an > > > approximately constant size on disk? > > (Failing > > > that perhaps a delete statement that would > > just > > > have it maintain a constant # of records? > > > ...maybe this would be much simpler?) > > > > > > --- Dan Greene wrote: > > > > cronjob a sql script that runs a delete > > > > statement for old jobs daily > > > > > > > > > --- Egor Egorov wrote: > > > > > > Scott H wrote: > > > > > >> Can't seem to find this one in the > > manual > > > > or > > > > > >> archives - how do I control a db to > > > > maintain > > > > > >> its size to an arbitrary value, say 20 > > GB? > > > > I > > > > > >> want to just rotate records, deleting > > > > those > > > > > >> that are oldest. > > > > > > > > > > > > You can't restrict size of the database > > > > only > > > > > > with MySQL, use disk quotas. > > > > > > > > > > No! That would just stop mysql right in > > its > > > > > tracks (so to speak...) when it got too > > > > large. > > > > > But I want old records sloughed off and > > the > > > > db to > > > > > continue running. (This is for a central > > > > syslog > > > > > box.) > > > > > > > > > > > > . > > > > > > __ > > > Do you Yahoo!? > > > Protect your identity with Yahoo! Mail > > AddressGuard > > > http://antispam.yahoo.com/whatsnewfree > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > = > -- > > To announce that there must be no criticism of the President, or > that we are to stand by the President, right or wrong, is not > only unpatriotic and servile, but is morally treasonable to the > American public. > -- Theodore Roosevelt, 1918 > > > > > > > .. > > __ > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard > http://antispam.yahoo.com/whatsnewfree > > -- > 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]
newbie question on data accumulation
Hi all Curious as to what happens after data is repeatedly selected from a mysql table overtime. Does it accumulate as junk data, stored at some location and eventually slow down the database/program/server? Does any purging have to take place? Thanks J __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
Joe Lewis said: > We're experiencing the same issues, but not neccesarily after an > upgrade. We're using MySQL 4.0.12 and FreeBSD 4.7 Release. We're > getting max'd connections only on specific users, and the "show > processlist" is returning only the "show processlist" process. the > results of netstat show absolutely nothing. > > What I think is happening is the connections are not properly getting > closed. The users are allowed to connect after a "flush user_resources" > is run. Is there a bug in the particular version of MySQL (4.0.12) > where the "user connections" are not getting decremented when a > connection is "closed"? > > Joe > > Henrik Skotth wrote: > >> I'm mostly using mytop, and that's the way that I see that there are >> no (are almost no) connections when the server claims that it is above >> the connection limit... So I guess that there's something seriously >> wrong then... Any ideas what? >> >> -- Henrik >> >> >> [EMAIL PROTECTED] (Pete Harlan) skrev: >> >>>What does "show processlist" say when the connections are maxed out? >>> (You may have to leave a client logged in to reserve a slot so you can >>> submit this query.) >>> >>>If it shows only a few connections, then there's something seriously >>> wrong. If it shows a ton of idle connections, it should tell you >>> which machines they are coming from and which users, and that should >>> help you track down who's holding connections open. >>> >>>--Pete >>> >>> >>>On Wed, Nov 12, 2003 at 01:15:54AM +0100, Henrik Skotth wrote: >>> Hello! I have tested this now, and that isn't the case. Any other ideas? -- Henrik Michael McTernan skrev: >Hi, > >Have you tried "netstat -a" on the box with the MySQL server? This > command >(Linux) will show what is connected to where, and will help you > double check >that there really aren't any open connections to the server. > >Thanks, > >Mike > > >>-Original Message- >>From: Henrik Skotth [mailto:[EMAIL PROTECTED] >>Sent: 10 November 2003 18:54 >>To: [EMAIL PROTECTED] >>Subject: Re: max_user_connections problem after upgrading >> >> >>That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so there >>is >>no limit to exceed. >>Also, we aren't getting the error messages ALL the time, they start to >>appear after a day or two and gets more and more frequent untill I >> restart >>mysql. Any other ideas? >> >>-- Henrik >> >>gerald_clark skrev: >> >>>Are you sure you are net exceeding the setting for >>>maximum connections per hour for that user? >>> >>>Henrik Skotth wrote: >>> >>> Hi! What I meant was that even if there are currently only two user connections being used, and the limit is 300, we still get the >> >>"already >>more than max_user_connections" error... >> -- Henrik gerald_clark skrev: >Henrik Skotth wrote: > > > > >>Hello all, >> >>We recently upgraded to 4.0, it went >>very well and the performance gains have been great. >>But now the server has started to act strangely. Every few >> days, the >> server starts to refuse connections, saying >>that there is already more than max_user_connections, but there is >> really only one or two active connections and our max_user_connections >>is 300. I have to take down and restart the server to solve the problem, >>and it keeps happening over and over again every few days... >> >>Am I the only one having this problem? Any suggestions? >> >>Regards, >>-- Henrik Skotth, Hogwarts.nu > >Are there 298 or 299 inactive connections? >If so, why are they not being closed? Is the user running "show processlist" allowed to see all processes (e.g. 'root')? William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access
Mikael Fridh wrote: On Wednesday 12 November 2003 17.15, David T-G wrote: % [client] % password="mysql_root_password" What if one user connects to the database as different users (as I do, in fact; sometimes all-db-root, sometimes one-db-root, sometimes read-only user)? Let's back this out of "root" and go to "ordinary" or perhaps "kinda-limited-root" (who might have all privileges but only over a single database). And I admit that I haven't read the docs [regarding this] yet, but can one specify user A = pass A and user B = pass B and so on? set up different my.cnf files for your different scripted users. Make sure that they have proper permissions then call mysql from your scripts like this: mysql --defaults-file=/home/blah/secret/stupid-user.my.cnf mysql --defaults-file=/home/blah/secret/clever-user.my.cnf more info from "mysql --help" Right. Note that the --defaults-file= option does not just specify an alternate .my.cnf, it also prevents looking in the global configuration files /etc/my.cnf and mysql_data_dir/my.cnf (/usr/local/mysql/data/my.cnf, for example). So, if you have anything set in one of the global files which you need, you'll have to duplicate those settings in your local files. Alternatively, you can use the --defaults-extra-file= option. This reads the named file in between the global files and .my.cnf. Last mention of a setting wins, so you would not put a password in .my.cnf in this case. Wouldn't it be nice if these options were mentioned in the mysql man page? Or is it just me? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
OK, I *THINK* I follow you here. Couple of questions. I'm reading an online tutorial trying to figure this out, and I am led to believe mysql can't do nested queries, aka sub-queries. But you say it can? Is this recent? And I don't have a timestamp field, I have an autoincrement field, but what do you mean by the "(@aa:=id)" thing? I don't follow that. thanks. --- Dan Greene <[EMAIL PROTECTED]> wrote: > What I would do is a classical guesstimate > > find the average size per record (data file > size + index file(s) size / # records in table) > > using that, find the data used per day > > using that, figure out how many days, on > average it takes to hit 20GB > > let's say it's 89 days. > > right off the top, take 10% off for safety, now > we're at 80 days > > presuming your table has a timestamp field: > > delete from log_table WHERE TO_DAYS(NOW()) - > TO_DAYS(date_col) > 80 > > if you don't have a timestamp field, but you do > have an autoincrement id field: > > figure out number of records on average = 20gb > (say it's 2M) > again, use 10% for safety (1.8M) > > select (@aa:=id) as low_id from logtable order > by id limit 1800,1 > delete from logtable where id < @aa > > (do subqueries work with a limit clause?) > > > > -Original Message- > > From: Scott H > [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, November 12, 2003 11:19 AM > > To: Dan Greene; MySQL Mailing List > > Subject: RE: maintaining size of a db > > > > > > Yes sir, exactly. It's just that's what I'm > > looking for, and can't figure out. I can set > up > > a cron job, but what exactly would the SQL > delete > > statement be that would allow me to delete > old > > records in such a way that the db maintains > an > > approximately constant size on disk? > (Failing > > that perhaps a delete statement that would > just > > have it maintain a constant # of records? > > ...maybe this would be much simpler?) > > > > --- Dan Greene wrote: > > > cronjob a sql script that runs a delete > > > statement for old jobs daily > > > > > > > --- Egor Egorov wrote: > > > > > Scott H wrote: > > > > >> Can't seem to find this one in the > manual > > > or > > > > >> archives - how do I control a db to > > > maintain > > > > >> its size to an arbitrary value, say 20 > GB? > > > I > > > > >> want to just rotate records, deleting > > > those > > > > >> that are oldest. > > > > > > > > > > You can't restrict size of the database > > > only > > > > > with MySQL, use disk quotas. > > > > > > > > No! That would just stop mysql right in > its > > > > tracks (so to speak...) when it got too > > > large. > > > > But I want old records sloughed off and > the > > > db to > > > > continue running. (This is for a central > > > syslog > > > > box.) > > > > > > > > . > > > > __ > > Do you Yahoo!? > > Protect your identity with Yahoo! Mail > AddressGuard > > http://antispam.yahoo.com/whatsnewfree > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > = -- To announce that there must be no criticism of the President, or that we are to stand by the President, right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public. -- Theodore Roosevelt, 1918 . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
We're experiencing the same issues, but not neccesarily after an upgrade. We're using MySQL 4.0.12 and FreeBSD 4.7 Release. We're getting max'd connections only on specific users, and the "show processlist" is returning only the "show processlist" process. the results of netstat show absolutely nothing. What I think is happening is the connections are not properly getting closed. The users are allowed to connect after a "flush user_resources" is run. Is there a bug in the particular version of MySQL (4.0.12) where the "user connections" are not getting decremented when a connection is "closed"? Joe Henrik Skotth wrote: I'm mostly using mytop, and that's the way that I see that there are no (are almost no) connections when the server claims that it is above the connection limit... So I guess that there's something seriously wrong then... Any ideas what? -- Henrik [EMAIL PROTECTED] (Pete Harlan) skrev: What does "show processlist" say when the connections are maxed out? (You may have to leave a client logged in to reserve a slot so you can submit this query.) If it shows only a few connections, then there's something seriously wrong. If it shows a ton of idle connections, it should tell you which machines they are coming from and which users, and that should help you track down who's holding connections open. --Pete On Wed, Nov 12, 2003 at 01:15:54AM +0100, Henrik Skotth wrote: Hello! I have tested this now, and that isn't the case. Any other ideas? -- Henrik Michael McTernan skrev: Hi, Have you tried "netstat -a" on the box with the MySQL server? This command (Linux) will show what is connected to where, and will help you double check that there really aren't any open connections to the server. Thanks, Mike -Original Message- From: Henrik Skotth [mailto:[EMAIL PROTECTED] Sent: 10 November 2003 18:54 To: [EMAIL PROTECTED] Subject: Re: max_user_connections problem after upgrading That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so there is no limit to exceed. Also, we aren't getting the error messages ALL the time, they start to appear after a day or two and gets more and more frequent untill I restart mysql. Any other ideas? -- Henrik gerald_clark skrev: Are you sure you are net exceeding the setting for maximum connections per hour for that user? Henrik Skotth wrote: Hi! What I meant was that even if there are currently only two user connections being used, and the limit is 300, we still get the "already more than max_user_connections" error... -- Henrik gerald_clark skrev: Henrik Skotth wrote: Hello all, We recently upgraded to 4.0, it went very well and the performance gains have been great. But now the server has started to act strangely. Every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem, and it keeps happening over and over again every few days... Am I the only one having this problem? Any suggestions? Regards, -- Henrik Skotth, Hogwarts.nu Are there 298 or 299 inactive connections? If so, why are they not being closed? -- 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] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maybe easy, maybe hard SELECT puzzle :)
At 09:24 PM 11/10/2003, Leo wrote: notice the _and_ ? *grin* good point. I guess my reply (which I found out later was unnecessary since the question had already been answered) would have been better stated had I just pointed out the distinct keyword rather than constructing a sample query. :) ah well -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
I'm mostly using mytop, and that's the way that I see that there are no (are almost no) connections when the server claims that it is above the connection limit... So I guess that there's something seriously wrong then... Any ideas what? -- Henrik [EMAIL PROTECTED] (Pete Harlan) skrev: > What does "show processlist" say when the connections are maxed out? > (You may have to leave a client logged in to reserve a slot so you can > submit this query.) > > If it shows only a few connections, then there's something seriously > wrong. If it shows a ton of idle connections, it should tell you > which machines they are coming from and which users, and that should > help you track down who's holding connections open. > > --Pete > > > On Wed, Nov 12, 2003 at 01:15:54AM +0100, Henrik Skotth wrote: >> Hello! >> >> I have tested this now, and that isn't the case. Any other ideas? >> >> -- Henrik >> >> >> Michael McTernan skrev: >> > Hi, >> > >> > Have you tried "netstat -a" on the box with the MySQL server? This >> > command >> > (Linux) will show what is connected to where, and will help you double >> > check >> > that there really aren't any open connections to the server. >> > >> > Thanks, >> > >> > Mike >> > >> >> -Original Message- >> >> From: Henrik Skotth [mailto:[EMAIL PROTECTED] >> >> Sent: 10 November 2003 18:54 >> >> To: [EMAIL PROTECTED] >> >> Subject: Re: max_user_connections problem after upgrading >> >> >> >> >> >> That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so >> there >> >> is >> >> no limit to exceed. >> >> Also, we aren't getting the error messages ALL the time, they start >> to >> >> appear after a day or two and gets more and more frequent untill I >> >> restart >> >> mysql. Any other ideas? >> >> >> >> -- Henrik >> >> >> >> gerald_clark skrev: >> >> > Are you sure you are net exceeding the setting for >> >> > maximum connections per hour for that user? >> >> > >> >> > Henrik Skotth wrote: >> >> > >> >> >>Hi! >> >> >> >> >> >>What I meant was that even if there are currently only two user >> >> >>connections being used, and the limit is 300, we still get the >> >> "already >> >> more than max_user_connections" error... >> >> >> >> >> >>-- Henrik >> >> >> >> >> >>gerald_clark skrev: >> >> >> >> >> >> >> >> >>>Henrik Skotth wrote: >> >> >>> >> >> >>> >> >> >>> >> >> Hello all, >> >> >> >> We recently upgraded to 4.0, it went >> >> very well and the performance gains have been great. >> >> But now the server has started to act strangely. Every few days, >> the >> >> >> >> >> >> >>server starts to refuse connections, saying >> >> >> >> >> >> >> >> that there is already more than max_user_connections, but there >> is >> >> >> >> >> >> >>really only one or two active connections and our >> max_user_connections >> >> is 300. I have to take down and restart the server to solve the >> problem, >> >> and it keeps happening over and over again every few days... >> >> >> >> >> >> >> >> Am I the only one having this problem? Any suggestions? >> >> >> >> Regards, >> >> -- Henrik Skotth, Hogwarts.nu >> >> >> >> >> >> >> >> >> >> >> >> >>>Are there 298 or 299 inactive connections? >> >> >>>If so, why are they not being closed? >> >> >>> >> >> >>> >> >> >>>-- >> >> >>>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] >> > >> > >> > >> > >> > >> > >> > -- >> > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database-design
Thanks to everybody for helping me. I agree with the suggestion to change the quarter fields in to one single field. The suggestion from Dan Greene to store the quarters as binary values sounds good to me. So I will do it this way. To explain you more about this table. The quarter fields are representing 8 hours of a working day. Each quarter represents 2 hours. First quarter represents first 2 hours and so on.. I need to register activities made during the day and if there is an activitie, I need to register in which quarter of the day. So there made be no activity ore one or to etc. The year, month, week, and day fields are actually year, cycle, week and day. This fields are not representing exact dates because the year is divided in to cycles. So one year has 8 cycles and one cycle has 6 weeks. But also these fields I will combine to one data field and store a binary value. The table finally looks like: Id Quarter ->(binary value) Eventdate ->(binary value) Timestamp Comments Thats it! Thanks an regards Martin Dan Greene <[EMAIL PROTECTED]> wrote: I think that I must be missing something, as I agree with all the suggestions that to change the seperate date element columns to a single date field, but Meli's original post had a date falling into multiple quarters. Now to my knowledge, a date can only be in one quarter, from a calendar point of view, so maybe there's something more to Meli's issue... to store the info more efficiently for what you're saying, you could also use binary as a guide 1 2 3 4 s n r t t d d h ___ 8 4 2 1 - - - - 1000 = 8 0100 = 4 0010 = 2 0001 = 1 1100 = 12 1010 = 10 1001 = 9 0110 = 6 0101 = 5 0011 = 3 1110 = 14 1101 = 13 1011 = 11 0111 = 7 = 15 = 0 (which you don't have below but here for completeness) and store a single number that represents the pattern you have below, replacing 'null' with 0 and x as 1 > > x null null null > > null x null null > > null null x null > > null null null x > > x x null null > > x null x null > > x null null x > > null x x null > > null x null x > > null null x x > > x x x null > > x x null x > > x null x x > > null x x x > > x x x x Ladies and Gentlemen, the first real use of the bitwise section of the java certification exam I have ever used > -Original Message- > From: Brent Baisley [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 9:01 AM > To: Meli Meli > Cc: [EMAIL PROTECTED] > Subject: Re: Database-design > > > Why would you created separate fields for each quarter? > Create a field > called quarter and store a number in it. You could also combine year, > month and day into a date field, which would make it easier to search > on ranges. > So, I think your table should look like this: > id > quarter > eventdate > week > > > On Tuesday, November 11, 2003, at 02:33 PM, Meli Meli wrote: > > > > > I have a table with following structure: > > > > Id > > first quarter > > second quarter > > third quarter > > last quarter > > year > > month > > week > > day > > > > On an entry not all fields of the four quarter fields are > covered with > > values. > > Following combinations are possible: > > > > first quarter | second quarter | third quarter | last quarter > > > > > > > > x null null null > > > > null x null null > > > > null null x null > > > > null null null x > > > > x x null null > > > > x null x null > > > > x null null x > > > > null x x null > > > > null x null x > > > > null null x x > > > > x x x null > > > > x x null x > > > > x null x x > > > > null x x x > > > > x x x x > > > > > > > > The table will receive many thousands of entry's. > > > > Would it be better to divide the table in to 15 small > tables in order > > to not register fields with null values? > > > > > > > > Thanks for helping > > > > Regards Martin > > > > > > > > - > > Do you Yahoo!? > > Protect your identity with Yahoo! Mail AddressGuard > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 > > > -- > 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] - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: secure automated access
On Wednesday 12 November 2003 17.15, David T-G wrote: > % [client] > % password="mysql_root_password" > What if one user connects to the database as different users (as I do, in > fact; sometimes all-db-root, sometimes one-db-root, sometimes read-only > user)? Let's back this out of "root" and go to "ordinary" or perhaps > "kinda-limited-root" (who might have all privileges but only over a > single database). And I admit that I haven't read the docs [regarding > this] yet, but can one specify user A = pass A and user B = pass B and so > on? set up different my.cnf files for your different scripted users. Make sure that they have proper permissions then call mysql from your scripts like this: mysql --defaults-file=/home/blah/secret/stupid-user.my.cnf mysql --defaults-file=/home/blah/secret/clever-user.my.cnf more info from "mysql --help" Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rollback error
Hi In version 3.23.57 when i do a rollback i get this error message and the changes are not undone, why? This is what i typed: (NOTE: 'insert.sql' insert a row correctly in an InnoDB table) mysql> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> \. /home/fernando/scripts/insert.sql Query OK, 1 row affected (0.00 sec) mysql> ROLLBACK; ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back http://webmail.wanadoo.es. Tu correo gratuito, rápido y en español -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
What I would do is a classical guesstimate find the average size per record (data file size + index file(s) size / # records in table) using that, find the data used per day using that, figure out how many days, on average it takes to hit 20GB let's say it's 89 days. right off the top, take 10% off for safety, now we're at 80 days presuming your table has a timestamp field: delete from log_table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) > 80 if you don't have a timestamp field, but you do have an autoincrement id field: figure out number of records on average = 20gb (say it's 2M) again, use 10% for safety (1.8M) select (@aa:=id) as low_id from logtable order by id limit 1800,1 delete from logtable where id < @aa (do subqueries work with a limit clause?) > -Original Message- > From: Scott H [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 11:19 AM > To: Dan Greene; MySQL Mailing List > Subject: RE: maintaining size of a db > > > Yes sir, exactly. It's just that's what I'm > looking for, and can't figure out. I can set up > a cron job, but what exactly would the SQL delete > statement be that would allow me to delete old > records in such a way that the db maintains an > approximately constant size on disk? (Failing > that perhaps a delete statement that would just > have it maintain a constant # of records? > ...maybe this would be much simpler?) > > --- Dan Greene wrote: > > cronjob a sql script that runs a delete > > statement for old jobs daily > > > > > --- Egor Egorov wrote: > > > > Scott H wrote: > > > >> Can't seem to find this one in the manual > > or > > > >> archives - how do I control a db to > > maintain > > > >> its size to an arbitrary value, say 20 GB? > > I > > > >> want to just rotate records, deleting > > those > > > >> that are oldest. > > > > > > > > You can't restrict size of the database > > only > > > > with MySQL, use disk quotas. > > > > > > No! That would just stop mysql right in its > > > tracks (so to speak...) when it got too > > large. > > > But I want old records sloughed off and the > > db to > > > continue running. (This is for a central > > syslog > > > box.) > > > > . > > __ > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard > http://antispam.yahoo.com/whatsnewfree > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
What does "show processlist" say when the connections are maxed out? (You may have to leave a client logged in to reserve a slot so you can submit this query.) If it shows only a few connections, then there's something seriously wrong. If it shows a ton of idle connections, it should tell you which machines they are coming from and which users, and that should help you track down who's holding connections open. --Pete On Wed, Nov 12, 2003 at 01:15:54AM +0100, Henrik Skotth wrote: > Hello! > > I have tested this now, and that isn't the case. Any other ideas? > > -- Henrik > > > Michael McTernan skrev: > > Hi, > > > > Have you tried "netstat -a" on the box with the MySQL server? This > > command > > (Linux) will show what is connected to where, and will help you double > > check > > that there really aren't any open connections to the server. > > > > Thanks, > > > > Mike > > > >> -Original Message- > >> From: Henrik Skotth [mailto:[EMAIL PROTECTED] > >> Sent: 10 November 2003 18:54 > >> To: [EMAIL PROTECTED] > >> Subject: Re: max_user_connections problem after upgrading > >> > >> > >> That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so there > >> is > >> no limit to exceed. > >> Also, we aren't getting the error messages ALL the time, they start to > >> appear after a day or two and gets more and more frequent untill I > >> restart > >> mysql. Any other ideas? > >> > >> -- Henrik > >> > >> gerald_clark skrev: > >> > Are you sure you are net exceeding the setting for > >> > maximum connections per hour for that user? > >> > > >> > Henrik Skotth wrote: > >> > > >> >>Hi! > >> >> > >> >>What I meant was that even if there are currently only two user > >> >>connections being used, and the limit is 300, we still get the > >> "already > >> more than max_user_connections" error... > >> >> > >> >>-- Henrik > >> >> > >> >>gerald_clark skrev: > >> >> > >> >> > >> >>>Henrik Skotth wrote: > >> >>> > >> >>> > >> >>> > >> Hello all, > >> > >> We recently upgraded to 4.0, it went > >> very well and the performance gains have been great. > >> But now the server has started to act strangely. Every few days, the > >> > >> > >> >>server starts to refuse connections, saying > >> >> > >> >> > >> that there is already more than max_user_connections, but there is > >> > >> > >> >>really only one or two active connections and our max_user_connections > >> is 300. I have to take down and restart the server to solve the problem, > >> and it keeps happening over and over again every few days... > >> >> > >> >> > >> Am I the only one having this problem? Any suggestions? > >> > >> Regards, > >> -- Henrik Skotth, Hogwarts.nu > >> > >> > >> > >> > >> > >> >>>Are there 298 or 299 inactive connections? > >> >>>If so, why are they not being closed? > >> >>> > >> >>> > >> >>>-- > >> >>>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] > > > > > > > > > > > > > > -- > > 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: Mysql just stopped working on my server - any way to track down the problem?
I guess you could say that. The machine is Redhat 9 too. I logged in locally and connected to the mysql server fine. Everything had appeared as normal. I could see all the databases, and \u to them. Is there a way I can turn on some extended logging to get more info on the problem as it will probably arise again in the future? Thx- Matt On Tue, 2003-11-11 at 21:35, Matt W wrote: > Hi Matt, > > So one database "disappeared" all of a sudden while the others are OK? > Is MySQL running on FreeBSD? If so, that'll be the problem :-) and we'll > point you toward the fix. > > > Matt > > > - Original Message - > From: "Matt Babineau" > Sent: Tuesday, November 11, 2003 3:28 PM > Subject: RE: Mysql just stopped working on my server - any way to track > down the problem? > > > > Its not a startup problem, that works fine. What happened was my php > > application stopped working. This server has multiple databases on it > > mind you. The other databases were working fine. > > > > So one database stopped working and the application could not connect > to > > it. so I did a 'service mysql restart' and it started working again > like > > magic. I looked in the .err file and it only shows when the mysql > server > > stops and starts. I can't see the file on this machine, but it just > > seems to be logging starts and stops. > > > > Thanks for the reply hope this helps- > > > > Matt > > > > On Tue, 2003-11-11 at 19:20, Dathan Vance Pattishall wrote: > > > Can you post what the *.err file said? A key/value in /etc/my.cnf > might > > > be causing a startup problem. > > > > > > > > > - Dathan Vance Pattishall > > > - Sr. Programmer and mySQL DBA for FriendFinder Inc. > > > - http://friendfinder.com/go/p40688 > > > > > > > > > -->-Original Message- > > > -->From: Matt Babineau [mailto:[EMAIL PROTECTED] > > > -->Sent: Tuesday, November 11, 2003 1:16 PM > > > -->To: [EMAIL PROTECTED] > > > -->Subject: Mysql just stopped working on my server - any way to > track > > > down > > > -->the problem? > > > --> > > > -->Hi All- > > > --> > > > -->I am running 4.0.15-standard on RH9. My mysql database just > stopped > > > -->working, is there a way I can log information about why it stops > like > > > -->this? the *.err was unhelpful. > > > --> > > > -->Any help here is appreciated. Thanks > > > --> > > > -->-Matt > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subquery?
Dear all A have a table mytable in which some records have the same value in col1. I'd like to know how show up only once, how many twice, etc. The following does what I want, but I think there should be a more elegant way, possibly invlving subquires. create table counts (n int); insert into counts(n) select count(*) from mytable group by col1; select n,count(*) from counts group by n; drop table counts; Thanks for a hint, W. Bauer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1045: Access denied...
I get your point Patrick. I was customer support engineer myself. You cant always think about all eventualities can you ;-), its really hard and needs a special way of thinking. Anyway that little "lesson" saved my day. ;-) Thanks a lot. Nils Valentin On Thursday 13 November 2003 01:22, you wrote: > Oh yes. Some in the past have taken it literally. Always better to be > very simple and very explicit in your instructions to users. > > I support users in 45 retail stores across the US. Sometimes we have to > have them shut down an application and reboot. Some of them have said, > "Oh, I just press the power button to shut it off?" > Aaaa! > > Patrick > > -Original Message- > From: Nils Valentin [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 8:18 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: Error 1045: Access denied... > > > Hi Patrick > > How true ;-) hohohoho > > Sorry for my really bad English, I just imagine that you might have had an > experience already from somebody taking it literally ?? > > I will try to avoid it. > > Thanks for the warning ;-) > > Best regards > > Nils Valentin > > On Thursday 13 November 2003 01:13, Patrick Dowd wrote: > > Nils, > > > > I have made it a practice to never tell a user to "HIT" anything. > > "PRESS" is much safer. > > > > Patrick > > > > -Original Message- > > From: Nils Valentin [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, November 12, 2003 8:06 AM > > To: [EMAIL PROTECTED] > > Subject: Fwd: Re: Error 1045: Access denied... > > > > > > > > > > Hi Fernando; > > > > you are logged in as anonymous user. log into mysql like this > > > > mysql -u root -p > > > > Hit enter if no password is set yet. > > > > -- > > kind regards > > > > Nils Valentin > > Tokyo/Japan > > > > http://www.be-known-online.com/mysql/ > > > > On Thursday 13 November 2003 00:47, Fernando wrote: > > > Hello, > > > i've just installed the 3.23.57 version in my account and i don't have > > > > root > > > > > privileges. The problem is the if i try to create a database, like this > > > > > > mysql> create database example; > > > > > > i get the following message: > > > > > > "Error 1045: Access denied for user : '@localhost'. > > > > > > Is this because i'm not the root? > > > If i try to use a database (one it doesn't exist) like this > > > > > > mysql> use my_database; > > > > > > i get the following line > > > > > > "Error 1044: Access denied for user : '@localhost' to database > > > my_database". > > > > > > I know that it should give me an error because the database doesn't > > > exist, but why this "access denied" message? Thanks in advance! > > > > > > > > > > > > http://webmail.wanadoo.es. Tu correo gratuito, rápido y en español > > > > --- > > > > -- > > kind regards > > > > Nils Valentin > > Tokyo/Japan > > > > http://www.be-known-online.com/mysql/ -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Synchronization and replication of two MYSQL databases
"Prashant Akerkar" <[EMAIL PROTECTED]> wrote: > > We have our application with data tier as mysql database in our office > running on windows platform which we need to synchronize with the same > database on a remote system at data centre on Linux. > Look at the replication: http://www.mysql.com/doc/en/Replication.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: maintaining size of a db
Yes sir, exactly. It's just that's what I'm looking for, and can't figure out. I can set up a cron job, but what exactly would the SQL delete statement be that would allow me to delete old records in such a way that the db maintains an approximately constant size on disk? (Failing that perhaps a delete statement that would just have it maintain a constant # of records? ...maybe this would be much simpler?) --- Dan Greene wrote: > cronjob a sql script that runs a delete > statement for old jobs daily > > > --- Egor Egorov wrote: > > > Scott H wrote: > > >> Can't seem to find this one in the manual > or > > >> archives - how do I control a db to > maintain > > >> its size to an arbitrary value, say 20 GB? > I > > >> want to just rotate records, deleting > those > > >> that are oldest. > > > > > > You can't restrict size of the database > only > > > with MySQL, use disk quotas. > > > > No! That would just stop mysql right in its > > tracks (so to speak...) when it got too > large. > > But I want old records sloughed off and the > db to > > continue running. (This is for a central > syslog > > box.) . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1045: Access denied...
Hi Patrick How true ;-) hohohoho Sorry for my really bad English, I just imagine that you might have had an experience already from somebody taking it literally ?? I will try to avoid it. Thanks for the warning ;-) Best regards Nils Valentin On Thursday 13 November 2003 01:13, Patrick Dowd wrote: > Nils, > > I have made it a practice to never tell a user to "HIT" anything. "PRESS" > is much safer. > > Patrick > > -Original Message- > From: Nils Valentin [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 8:06 AM > To: [EMAIL PROTECTED] > Subject: Fwd: Re: Error 1045: Access denied... > > > > > Hi Fernando; > > you are logged in as anonymous user. log into mysql like this > > mysql -u root -p > > Hit enter if no password is set yet. > > -- > kind regards > > Nils Valentin > Tokyo/Japan > > http://www.be-known-online.com/mysql/ > > On Thursday 13 November 2003 00:47, Fernando wrote: > > Hello, > > i've just installed the 3.23.57 version in my account and i don't have > > root > > > privileges. The problem is the if i try to create a database, like this > > > > mysql> create database example; > > > > i get the following message: > > > > "Error 1045: Access denied for user : '@localhost'. > > > > Is this because i'm not the root? > > If i try to use a database (one it doesn't exist) like this > > > > mysql> use my_database; > > > > i get the following line > > > > "Error 1044: Access denied for user : '@localhost' to database > > my_database". > > > > I know that it should give me an error because the database doesn't > > exist, but why this "access denied" message? Thanks in advance! > > > > > > > > http://webmail.wanadoo.es. Tu correo gratuito, rápido y en español > > --- > > -- > kind regards > > Nils Valentin > Tokyo/Japan > > http://www.be-known-online.com/mysql/ -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table creation
Hello list: Is it possible to create a table with a date column with current date as is default value?, like curdate() or now() values?, any ideas. - MySQL 3.23.58-Max-log - RH. 7.3 Greetings and thanks in advanced Mikel _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.microsoft.com/es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael, et al -- ...and then Michael Stassen said... % % The simplest solution is to keep the password in the .my.cnf file in % your home directory. See http://www.mysql.com/doc/en/Option_files.html % in the manual. OK. % % In the case of root cron jobs then, you need a .my.cnf readable only by % root in root's home. It should include % % [client] % password="mysql_root_password" What if one user connects to the database as different users (as I do, in fact; sometimes all-db-root, sometimes one-db-root, sometimes read-only user)? Let's back this out of "root" and go to "ordinary" or perhaps "kinda-limited-root" (who might have all privileges but only over a single database). And I admit that I haven't read the docs [regarding this] yet, but can one specify user A = pass A and user B = pass B and so on? % % As mysql reads the .my.cnf file, this avoids the ps "sniffing" problem, Yep. % and also keeps the password out of the script. Because you make the Gotcha. That might be all it takes. % .my.cnf file readable only by root, other users can't see it. (If they % can see it, you've got bigger problems than just the mysql password). Yeah :-) Even if we're *not* talking about root, too. % % Michael Thanks & HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/sly5Gb7uCXufRwARAgDSAKCmi5sKMsTJTZ4uRA0KyeGmLlMjQwCglTCX xhTJvgm/w/BftAHINt8e8Ho= =qeDz -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1045: Access denied...
Fernando <[EMAIL PROTECTED]> wrote: > Hello, > i've just installed the 3.23.57 version in my account and i don't have root > privileges. The problem is the if i try to create a database, like this > > mysql> create database example; > > i get the following message: > > "Error 1045: Access denied for user : '@localhost'. > > Is this because i'm not the root? Because you doesn't have rights to create a database. The above error means that you are connected as anonymous use. By default anonymous user has permissions only on the database 'test'. > If i try to use a database (one it doesn't exist) like this > > mysql> use my_database; > > i get the following line > > "Error 1044: Access denied for user : '@localhost' to database my_database". > > I know that it should give me an error because the database doesn't exist, but why > this "access denied" message? Because you doesn't have permissions :) If you had appropriate privileges you should get "Unknown database" error. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Fwd: Re: Error 1045: Access denied...
Hi Fernando; you are logged in as anonymous user. log into mysql like this mysql -u root -p Hit enter if no password is set yet. -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ On Thursday 13 November 2003 00:47, Fernando wrote: > Hello, > i've just installed the 3.23.57 version in my account and i don't have root > privileges. The problem is the if i try to create a database, like this > > mysql> create database example; > > i get the following message: > > "Error 1045: Access denied for user : '@localhost'. > > Is this because i'm not the root? > If i try to use a database (one it doesn't exist) like this > > mysql> use my_database; > > i get the following line > > "Error 1044: Access denied for user : '@localhost' to database > my_database". > > I know that it should give me an error because the database doesn't exist, > but why this "access denied" message? Thanks in advance! > > > > http://webmail.wanadoo.es. Tu correo gratuito, rápido y en español --- -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Re: Error 1045: Access denied...
Hi Fernando; you are logged in as anonymous user. log into mysql like this mysql -u root -p Hit enter if no password is set yet. -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ On Thursday 13 November 2003 00:47, Fernando wrote: > Hello, > i've just installed the 3.23.57 version in my account and i don't have root > privileges. The problem is the if i try to create a database, like this > > mysql> create database example; > > i get the following message: > > "Error 1045: Access denied for user : '@localhost'. > > Is this because i'm not the root? > If i try to use a database (one it doesn't exist) like this > > mysql> use my_database; > > i get the following line > > "Error 1044: Access denied for user : '@localhost' to database > my_database". > > I know that it should give me an error because the database doesn't exist, > but why this "access denied" message? Thanks in advance! > > > > http://webmail.wanadoo.es. Tu correo gratuito, rápido y en español --- -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dirt Slow Query On Datetime Range...the saga continues
In order to make sure of a multi-column index, you have to order the
WHERE clauses in the same order as the columns in the index. Since you
query Realm first, then AcctStartTime, then AcctStopTime, MySQL would
use an index on those columns in that order. You can either add a
differently ordered index of the same columns or not, just make sure
your query has the columns in the right order.
- Gabriel
On Tuesday, November 11, 2003, at 05:57 PM, Michael Shuler wrote:
Thanks for the quick help everyone...OK I made a few changes
The Query is now:
SELECT COUNT( * ) AS CallCount FROM ServiceRADIUSAccounting WHERE
(Realm =
'testrealm.com') AND ('2003-10-11 16:00:00' BETWEEN AcctStartTime AND
AcctStopTime)
Which uses the RealmAndStart index (which as you see in the next line
has
been improved slightly).
And I modified the last key to:
KEY `RealmAndStart` (`Realm`,`AcctStartTime`,`AcctStopTime`)
I also though about it for a while and had an inspirational idea that
if I
make a key that looks like this:
KEY `StartStopRealm` (`AcctStartTime`,`AcctStopTime`,`Realm`)
It would go even faster because it will narrow down to the records
within
the time frame (which is about 1000 records) and then down by the
realm name
which would result in ~150 records to count. Oddly enough in the
EXPLAIN it
doesn't even consider it as a possible index to use. What gets even
more
odd is that I swapped the BETWEEN and the Realm in the WHERE clause
and then
it decided to use just the plain Realm index...
SELECT COUNT( * ) AS CallCount FROM ServiceRADIUSAccounting WHERE
('2003-10-11 16:00:00' BETWEEN AcctStartTime AND AcctStopTime) AND
(Realm =
'testrealm.com')
Is this a bug and is there a way to force MySQL to use an index that
you
know is a better choice?
Thanks again,
Michael Shuler
-Original Message-
From: Michael Shuler [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 11, 2003 3:44 PM
To: [EMAIL PROTECTED]
Subject: Dirt Slow Query On Datetime Range
OK, I give up. To anyone out there who can help me, please explain why
this
query runs slower than dirt. The table has about 1,300,000 records in
it,
which is not supposed to be a big deal for MySQL to deal with. I have
tried
it with MyISAM and then changed it to InnoDB which made it even slower
but
at least the rest of my queries can continue and not be blocked. This
query
takes 30 seconds on a dual 1GHz 1GB RAM RedHat 9 PC. In my opinion it
should be 10x faster than that at the very least.
This table is used for RADIUS accounting, all I want to do is find the
peak
utilization port utilization for the day. The only way I have figured
out
how to do this is take samples every 5 min and store the highest one.
Here
is the table:
CREATE TABLE `ServiceRADIUSAccounting` (
`RadAcctId` bigint(21) NOT NULL auto_increment,
`AcctSessionId` varchar(32) NOT NULL default '',
`AcctUniqueId` varchar(32) NOT NULL default '',
`UserName` varchar(64) NOT NULL default '',
`Realm` varchar(64) default '',
`NASIPAddress` varchar(15) NOT NULL default '',
`NASPortId` int(12) default NULL,
`NASPortType` varchar(32) default NULL,
`AcctStartTime` datetime NOT NULL default '-00-00 00:00:00',
`AcctStopTime` datetime NOT NULL default '-00-00 00:00:00',
`AcctSessionTime` int(12) default NULL,
`AcctAuthentic` varchar(32) default NULL,
`ConnectInfo_start` varchar(32) default NULL,
`ConnectInfo_stop` varchar(32) default NULL,
`XmitSpeed` varchar(6) default NULL,
`RecvSpeed` varchar(6) default NULL,
`AcctInputOctets` int(12) default NULL,
`AcctOutputOctets` int(12) default NULL,
`CalledStationId` varchar(11) NOT NULL default '',
`CallingStationId` varchar(11) NOT NULL default '',
`AcctTerminateCause` varchar(32) NOT NULL default '',
`ServiceType` varchar(32) default NULL,
`FramedProtocol` varchar(32) default NULL,
`FramedIPAddress` varchar(15) NOT NULL default '',
`AcctStartDelay` int(12) default NULL,
`AcctStopDelay` int(12) default NULL,
PRIMARY KEY (`RadAcctId`),
KEY `UserName` (`UserName`),
KEY `FramedIPAddress` (`FramedIPAddress`),
KEY `AcctSessionId` (`AcctSessionId`),
KEY `AcctUniqueId` (`AcctUniqueId`),
KEY `AcctStartTime` (`AcctStartTime`),
KEY `AcctStopTime` (`AcctStopTime`),
KEY `NASIPAddress` (`NASIPAddress`),
KEY `Realm` (`Realm`),
KEY `RealmAndStart` (`Realm`,`AcctStartTime`)
) TYPE=InnoDB AUTO_INCREMENT=4468368 ;
And here is the query:
SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting WHERE (Realm
=
'testreal.com') AND (AcctStartTime <= '2003-11-11 15:30:00' AND
AcctStopTime
= '2003-11-11 15:30:00')
When I do an explain I get:
tabletype possible_keys
key key_len ref rows Extra
ServiceRADIUSAccounting ref
AcctStartTime,AcctStopTime,Realm,RealmAndStart RealmAndStart 65
const
73394 Using where
Perhapse my InnoDB file needs to be "optimized" if such a thing
exists. I
don't know why this takes so long but I can definitly use some
Cannot create InnoDB table
Philip, the problem is probably that the table already exists in the InnoDB internal data dictionary. With 4.0.17, I get: mysql> CREATE TABLE ids (id varchar(255)) TYPE=InnoDB; ERROR 1005 (0): Can't create table './test/ids.frm' (errno: 121) mysql> Output to the .err log: 031112 17:48:39 InnoDB: Error: table test/ids already exists in InnoDB internal InnoDB: data dictionary. Have you deleted the .frm file InnoDB: and not used DROP TABLE? Have you used DROP DATABASE InnoDB: for InnoDB tables in MySQL version <= 3.23.43? InnoDB: See the Restrictions section of the InnoDB manual. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and moving the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. InnoDB: You can look further help from section 15.1 of InnoDB: http://www.innodb.com/ibman.html Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html ... List:MySQL General Discussion« Previous MessageNext Message » From:Philip MolterDate:November 12 2003 4:44pm Subject:Cannot create InnoDB table I have a situation using InnoDB where I cannot create even the most basic of tables with a given name. mysql> create table card_batch ( a int ); ERROR 1005: Can't create table './test_gn/card_batch.frm' (errno: 121) mysql> show tables like 'card%'; Empty set (0.01 sec) mysql> show variables like 'version'; +---+-+ | Variable_name | Value | +---+-+ | version | 4.0.15a-log | +---+-+ 1 row in set (0.00 sec) In another database on the same server, using the same InnoDB files, I can create the table just fine. Why does it keep telling me that it cannot create the table because of "Duplicate key on write or update"? The MySQL version os 4.0.15a. The operating system is Solaris 9. Philip * Philip Molter * Texas.Net Internet * http://www.texas.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
cronjob a sql script that runs a delete statement for old jobs daily > -Original Message- > From: Scott H [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 10:40 AM > To: MySQL Mailing List > Subject: Re: maintaining size of a db > > > --- Egor Egorov wrote: > > Scott H wrote: > >> Can't seem to find this one in the manual or > >> archives - how do I control a db to maintain > >> its size to an arbitrary value, say 20 GB? I > >> want to just rotate records, deleting those > >> that are oldest. > > > > You can't restrict size of the database only > > with MySQL, use disk quotas. > > No! That would just stop mysql right in its > tracks (so to speak...) when it got too large. > But I want old records sloughed off and the db to > continue running. (This is for a central syslog > box.) > > > __ > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard > http://antispam.yahoo.com/whatsnewfree > > -- > 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]
Error 1045: Access denied...
Hello, i've just installed the 3.23.57 version in my account and i don't have root privileges. The problem is the if i try to create a database, like this mysql> create database example; i get the following message: "Error 1045: Access denied for user : '@localhost'. Is this because i'm not the root? If i try to use a database (one it doesn't exist) like this mysql> use my_database; i get the following line "Error 1044: Access denied for user : '@localhost' to database my_database". I know that it should give me an error because the database doesn't exist, but why this "access denied" message? Thanks in advance! http://webmail.wanadoo.es. Tu correo gratuito, rápido y en español -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot create InnoDB table
I have a situation using InnoDB where I cannot create even the most basic of tables with a given name. mysql> create table card_batch ( a int ); ERROR 1005: Can't create table './test_gn/card_batch.frm' (errno: 121) mysql> show tables like 'card%'; Empty set (0.01 sec) mysql> show variables like 'version'; +---+-+ | Variable_name | Value | +---+-+ | version | 4.0.15a-log | +---+-+ 1 row in set (0.00 sec) In another database on the same server, using the same InnoDB files, I can create the table just fine. Why does it keep telling me that it cannot create the table because of "Duplicate key on write or update"? The MySQL version os 4.0.15a. The operating system is Solaris 9. Philip * Philip Molter * Texas.Net Internet * http://www.texas.net/ * [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maintaining size of a db
--- Egor Egorov wrote: > Scott H wrote: >> Can't seem to find this one in the manual or >> archives - how do I control a db to maintain >> its size to an arbitrary value, say 20 GB? I >> want to just rotate records, deleting those >> that are oldest. > > You can't restrict size of the database only > with MySQL, use disk quotas. No! That would just stop mysql right in its tracks (so to speak...) when it got too large. But I want old records sloughed off and the db to continue running. (This is for a central syslog box.) __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was "Re: Backing up all databases")
The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password="mysql_root_password" As mysql reads the .my.cnf file, this avoids the ps "sniffing" problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael David T-G wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dan, et al -- ...and then Dan Greene said... % % you could put a shell script as the actual cron job, and make the % file only read-able by root, using an environment variable as the % password passed (defined in shell script file), so that way even if % someone 'sniffs' the process via 'ps -ef' they don't see the actual % password (if they happen to catch the setting of the env var that's % another story, but _much_ less likely) What, you've never used 'ps aguxwwe' before? Heh. The closest I might come to a better answer is the same sort of scripted setup but to redirect mysql from a file (or a heredoc) containing the password so that it doesn't show up anywhere in ps. But you still have the password right there in the file; that sure sucks! This actually brings up a different question: how can one reliably and securely and yet without human interaction run database queries? I've wondered this for a while, and I think I brought it up on this list some time ago, and there was some talk of X.500 certificates, but I don't recall anything beyond that (including any sort of satisfactory answer). Those of you who store your passwords in the script file for all to see, how do you keep all from seeing them? And those of you who don't, what are you doing to stay secure? And those of you who have a different approach, what is it? TIA & HAND :-D - -- David T-G * There is too much animal courage in -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Capacity Planning - Methodology/Documentation
Hi there! I need to do a capacity planning for my MySQL database. Can anyone help me finding information to do it? is there any methodology or at least some information about it? I think I have anything I need to do it, I used to do it for Oracle but we're working with MySQL now, and I have no idea about parameters, etc (if they exists), and I was asked to do a capacity planning based on any supported methodology. :s Can anyone point me in the right direction? Does anyone know where I can find some information? Thank you very much Hector L
Re: back up database
On 11/12/2003 02:58 AM Didier Godot wrote: Hi everyone, this is my first post, you will excuse me for my english cause french is my fluent language. So i'm also new to mysql, tonight my question is how to make a back up of all the database on MySQL 4.0.12 running on netware server 6 i hava acces to the web console but i don't know the process to do that. thanks for you help... Hi Didier, you just missed a huge thread on this. Check in the archives for it. Adasm -- mySQL 4.1.0 + connector/J 3.0.9 + j2sdk 1.4.2 + Linux 2.4.20 RH9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
