phpmyadmin problems with quoting exported text
I just tried to use the output of the export function on phpmyadmin and got a million errors. After looking at the file I found that certain columns that are strings were not quoted at all. I can't find any reason why some are and some are not quoted. Anyone have any idea why this is happening? -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Rhino wrote: I'm glad the explanation helped. I figured that the solution alone wouldn't be very useful if it wasn't explained since it is not obvious to most people how correlated queries work :-) I really wasn't trying to center you out with my rant about MySQL version. It's been a long-running frustration for me; I've tried to urge people to give this information in their notes to the list but an awful lot of people never think to mention their version. Then, an already lengthy reply gets even longer as you try to explain: If you have Version 3.x, the solution is this If you have Version 4.x the solution is this. If you have Version 5.x, the solution is this. You get the picture. Okay, let me explain the temp tables approach now. Most subqueries are not correlated so the subquery gets done once and then its result gets plugged into the outer query. For example, given a table called Employee in the Sample database which has one row for each employee and each row contains an employee number, last name, and salary, this query will return the employee number and last name of every employee who makes more than the average salary for all employees in the table: --- select empno, lastname from Sample.Employee where salary (select avg(salary) from Sample.Employee); --- If subqueries are supported, as in Version 4.1.x or above of MySQL, you just run that exact query and you will get a list of all of the people who make more than the average salary. However, if you are using an older version of MySQL which doesn't support subqueries, you need to use a temporary table approach. Basically, you create a temporary table that contains the same results that the subquery would have given you, then join it to the outer query. [Many, but not all, subqueries can be rewritten as joins.] For example: --- #Create the temporary table that will contain the average salary for all employees. create table if not exists temp_average_salary (average_salary decimal(9,2)); #Populate the temporary table with the average salary for all employees. insert into temp_average_salary(select avg(salary) from Sample.Employee); #Do a join of the employee table and the temporary table to obtain only employees who make #more than the average salary. select empno, lastname, salary from Sample.Employee e inner join temp_average_salary t where e.salary t.average_salary; --- This would give the same result as the original solution that used subqueries although there is obviously more work to do in accomplishing the desired result. Initially, I was almost certain that this approach could not be made to work for your problem due to its correlated nature but a couple of experiments persuaded me that it was possible after all. It's actually quite similar to the example I gave above, except that this time the temporary table is not just a single value on a single row but a table containing one row for each VBS_id, showing the max (latest) date for any row containing that VBS_id. Here's the definition of the table and the correct method of populating the table: --- create table if not exists temp_table (VBS_id int not null, max_date date not null); insert into temp_table (select VBS_id, max(date) from VBS_table group by VBS_id); --- If you display the contents of the temporary table, you get: --- VBS_idmax_date 112005-10-08 122005-10-08 --- Now, you simply join the VBS_table and the temp_table, as follows: --- select v.VBS_id, v.date, v.weight from VBS_table v inner join temp_table t on v.VBS_id = t.VBS_id and v.date = t.max_date; --- The result is exactly what you wanted: --- VBS_iddateweight 112005-10-0811.52 122005-10-0810.50 --- You could apply this solution to versions of MySQL that don't have subquery support; just remember that you need to re-create the temp_table immediately before you do the join each time; otherwise, you are going to determine whatever weights were in effect whenever you first populated the temp_table, not the current weights. In your case though, you can make life better for yourself. Since you have view support, you can simply create a view that is the equivalent to the temp_table and then join the view to the VBS_table to get the data you want. Since the view is always up-to-date, you eliminate the need to have the temp_table at all. Something like this: --- create view VBS_max_dates (VBS_id, max_date) as select VBS_id, max(date) from VBS_table group by VBS_id; select v.VBS_id, v.date, v.weight from VBS_table v inner join VBS_max_dates z on v.VBS_id = z.VBS_id and v.date = z.max_date; --- Note that the view is only created once, not each time you are about to do the join!! One other solution jumps to mind that should be just as good. Instead of creating temp tables or views, why not just put the original correlated subquery that I gave you into a stored procedure? Then, all you'd need to do is call that stored procedure every
Re: Add New User
Bruce Martin [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] When I log in as root using: mysql -u root -p mysql I get the mysql prompt: mysql I then issue this command or statement: mysqlGRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED BY 'some_password' WITH GRANT OPTION; You haven't specified the host, try this : To connect only from the localhost : GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'some_password' WITH GRANT OPTION; Or if you want to be able to connect from any host : GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'%' IDENTIFIED BY 'some_password' WITH GRANT OPTION; Or if you would like to be able to connect from some host : GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'host_name_or_ip' IDENTIFIED BY 'some_password' WITH GRANT OPTION; I get the following returned: Query OK, 0 rows affected (0.00 sec) Why is this not working? To test it further I try to log in as testUser but it tells me access denied for user [EMAIL PROTECTED] Even if I grant the testUser @ localhost. I can look in the user table and sure enough user [EMAIL PROTECTED] is there. Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete all but the newest 100 records?
If you have auto_incremnt id in the table you can do the following: delete from table a where id start_id and id = end_id Or you can delete by timestamp. delete from table a where date between 'start_date' and 'end_date'. On 11/6/05, Brian Dunning [EMAIL PROTECTED] wrote: I'd like to delete all but the newest 100 records from a table. I have a timestamp field. I can't figure out how to do this in a single SQL statement - can anyone help? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Add New User
-Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: zondag 6 november 2005 6:26 To: Mark Cc: 'Bruce Martin'; mysql@lists.mysql.com Subject: Re: Add New User Because, afterwards, you forgot to say: FLUSH PRIVILEGES; Perhaps? - Mark No. FLUSH PRIVILEGES is not needed with GRANT http://dev.mysql.com/- doc/refman/5.0/en/grant.html. Funny, I always thought FLUSH PRIVILEGES was required when the GRANT s- tatement creates a new user in the process. True enough, though, I can (no longer?) find that in the manual. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete all but the newest 100 records?
söndagen den 6 november 2005 16:15 skrev Brian Dunning: I'd like to delete all but the newest 100 records from a table. I have a timestamp field. I can't figure out how to do this in a single SQL statement - can anyone help? Thanks! I'd try something like this: delete from x where ID not in (select ID from x order by timestamp desc limit 100); Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add New User
Ok thank you for your replies. I finally got it working, but as you stated I granted too many permissions. I found that in the user table my testUser had N for every column with the exception of the last few which where 0. I first logged into the database using: shellmysql -u root -p mysql So I used: mysqlGRANT ALL PRIVILEGES ON test.* TO 'testUser'@'localhost' IDENTIFIED BY 'some_password'; and: mysqlGRANT ALL PRIVILEGES ON test.* TO 'testUser'@'%' IDENTIFIED BY 'some_password'; Then I did: mysql select * from user where user.User='testUser'; Which returned: | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | +---+--+--- +-+-+-+-+- +---+-+---+--+--- ++-++ +--++---+-- +--+-+--+-- ++-+ +--+--++- +--+---+-+- +--+ | % | testUser | *12F46AB3EF1939F7B217B125466177AFA18495CF | N | N | N | N | N | N | N | N | N| N | N | N | N | N | N| N | N | N| N| N | N| N| N | N | N | N| | | | | 0 | 0 | 0 |0 | | localhost | testUser | *12F46AB3EF1939F7B217B125466177AFA18495CF | N | N | N | N | N | N | N | N | N| N | N | N | N | N | N| N | N | N| N| N | N| N| N | N | N | N| | | | | 0 | 0 | 0 |0 | I then did: UPDATE user SET Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_p riv='Y' WHERE user.User='testUser'; I can now access the server using my test user: testUser. However, even though I specified 'test.*' my test user can access the mysql database. doesn't test.* limit the user to the test database? or should it have been 'test' no quotes of course. Is there a way to update the database access without deleting the user and redoing it all? Thanks for the help. On Nov 6, 2005, at 12:23 AM, Michael Stassen wrote: Bruce Martin wrote: When I log in as root using: mysql -u root -p mysql I get the mysql prompt: mysql I then issue this command or statement: mysqlGRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED BY 'some_password' WITH GRANT OPTION; First, your hostname is blank. That won't work. You need to either specify a hostname, or use the wildcard character, '%'. See the manual for details http://dev.mysql.com/doc/refman/5.0/en/connection-access.html. Second, why are you making testuser equivalent to root? Don't give a user any more privileges than necessary. For a test user, I'd suggest starting with just GRANT ALL PRIVILEGES ON test.* TO 'testUser'@'localhost' IDENTIFIED BY 'some_password'; I get the following returned: Query OK, 0 rows affected (0.00 sec) Why is this not working? What makes you think it didn't work? You didn't get an error message. Instead, you got Query OK. Looks like it worked, to me. To test it further I try to log in as testUser but it tells me access denied for user [EMAIL PROTECTED] That's not the whole error message. Please show us your exact comand, and copy/paste the exact error message. We could try to guess what's wrong (probably there is no [EMAIL PROTECTED]), but it's better not to guess. Even if I grant the testUser @
Re: Add New User
söndagen den 6 november 2005 18:39 skrev Bruce Martin: However, even though I specified 'test.*' my test user can access the mysql database. That's because this statement: UPDATE user SET Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_p riv='Y' WHERE user.User='testUser'; granted those privileges on _all_ databases. Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqli
Hello. There are some examples inside the discussion which should work: http://forums.mysql.com/read.php?98,19146,19496 Scott Hamm wrote: I was working on Php web page and attemtped to use stored procedure.=20 Last night on Php line, they said that I should consider MySQLi and to ask MySQL list about it. Do anyone know anything about executing a stored procedure from php page? Something to do with interface or?=20 Do I need something extra for it or? Scott -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add New User
Ok thank you, I think I have it now. Now to get my Client application working. On Nov 6, 2005, at 1:16 PM, Björn Persson wrote: söndagen den 6 november 2005 18:39 skrev Bruce Martin: However, even though I specified 'test.*' my test user can access the mysql database. That's because this statement: UPDATE user SET Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create _p riv='Y' WHERE user.User='testUser'; granted those privileges on _all_ databases. Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error compiling 5.0.15
I am experiencing problems when trying to compile 5.0.15 on RH 7.3. I run configure --prefix /usr/local/mysql with no apparent errors. when I run make I get the following error: ha_innodb.o: In function `check_trx_exists(THD *)': ha_innodb.o(.text+0x30a): undefined reference to `ut_dbg_zero' ha_innodb.o: In function `innobase_query_caching_of_table_permitted(THD *, char *, unsigned int, unsigned long long *)': ha_innodb.o(.text+0x3ab): undefined reference to `ut_dbg_zero' ha_innodb.o(.text+0x3b6): undefined reference to `ut_dbg_zero' ha_innodb.o: In function `innobase_init(void)': ha_innodb.o(.text+0x69c): undefined reference to `ut_dbg_zero' ha_innodb.o(.text+0x72e): undefined reference to `ut_dbg_zero' ha_innodb.o(.text+0x98c): more undefined references to `ut_dbg_zero' follow collect2: ld returned 1 exit status make[4]: *** [mysqld] Error 1 make[4]: Leaving directory `/usr/src/mysql-5.0.15/sql' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/usr/src/mysql-5.0.15/sql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/src/mysql-5.0.15/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-5.0.15' make: *** [all] Error 2 Any ideas are greatly appreciated. Also, I know I can use the binary but I have been instaling from src for quite a while and would like to continue if my current OS can handle it. Thanks in advance Ed . . . . . . . . . . . . . . . . . . Randomly Generated Quote (614 of 1009): It is bad luck to be superstitious. -- Andrew W. Mathis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB maintenance question?
I have an ibdata file that is growing and growing and growing Are there maintenance routines that I can run to keep this file in check? My database is performing close to half a million transactions a day and this file is now well over 75GB. Can I expect this file to continue to grow, until I run out of disk space. I am currently using MySQL 4.1 but will be moving to 5.x soon. Thanks, Lane
Stored procedures using a variable tablename
Hello, I am moving my first steps with stored procedures in Mysql and I was trying to write a stored procedure using a tablename given as a variable. In this example the local variable m_sequence has the value of the table I want to use. CREATE PROCEDURE p_new_id (IN tablename VARCHAR(128), OUT new_id INT) BEGIN DECLARE m_new_id INT DEFAULT 0; DECLARE m_sequence VARCHAR(128); SELECT CONCAT(_sequence_, tablename) INTO m_sequence; INSERT INTO m_sequence VALUES (NULL); SELECT last_insert_id() INTO m_new_id; DELETE FROM m_sequence WHERE sequence m_new_id; SET new_id = m_new_id; END; However when I try to execute it, it gives me this error message: Table 'db.m_sequence' doesn't exist Meaning that a table literally called m_sequence was not found, but i would like to use the table inside the m_sequence variable instead. Anyone knows if it's possible and how? Thank you very much Stefano Locati - Obliquid http://software.obliquid.it/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heikki: What will become of InnoDb once MySQL license runs out
On 5 Nov 2005, at 03:47, Ezra Taylor wrote: They will crush anyone that gets in there way. Well, if recent events are any indication, Oracle's approach to 'crushing' the opposition is to give them very large amounts of money. If that's being crushed, I'm up for it. Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heikki: What will become of InnoDb once MySQL license runs out
At 08:33 PM 11/4/2005, you wrote: Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, November 05, 2005 12:11 AM Subject: Heikki: What will become of InnoDb once MySQL license runs out Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. the current GPL version of MySQL/InnoDB will of course be available then by the very nature of the GPL license. Sure but if people have commercial applications that use InnoDb, then what? Is there a surprise tax waiting for them next year? The MySQL AB - Innobase Oy OEM agreement is about commercial non-GPL MySQL/InnoDB licenses. About that agreement I want to refer to the official press release of Oracle Corporation: http://www.oracle.com/innodb/index.html From the website: InnoDB's contractual relationship with MySQL comes up for renewal next year. Oracle fully expects to negotiate an extension of that relationship. Terms of the transaction were not disclosed. It seems to me Oracle now has MySQL AB by the short and curlies. bg Negotiating a fair contract renewal could be painful under these conditions, don't you think? If Oracle is that much in favor of continuing the InnoDb contract with MySQL, why didn't they pre-announce it saying the terms and conditions would be the same as before. Or are they going to change the contract so they collect $500 or even $1000 extra for every commercial application that is distributed with InnoDb? If this happens, what alternative will MySQL be offering their users who need transactions and RI? If yes, will you still be supporting it or will it be up to MySQL AB? I want to refer to the official press release where Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology says: Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software. And did he say at what cost to the MySQL developers? It never struck me that Larry Ellison was a humanitarian who wanted a competitor to succeed. (Did Larry hit his head?vbg) Did Oracle give you any reason as to why they wanted to buy InnoDb? Are they going to be replacing Oracle's row locking with InnoDb? If they're not going to be using InnoDb, why buy it? This is looking more like a preemptive strike against MySQL. In which case, why would they honor the next contract? It's a lot like seeing a neighboring army surround your oil fields and then hear them say, No cause for alarm! We're here to help you improve your pumping efficiencies!. You just have to wonder how sincere are they? Should I trust Larry Ellison with the deed to my house? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heikki: What will become of InnoDb once MySQL license runs out
At 08:59 PM 11/4/2005, you wrote: At 21:49 -0500 11/4/05, Ezra Taylor wrote: Last one people: I just realized that Heikki is monitoring our post pertaining to innodb. This guy/gal is an oracle employee. The enemy is amongst us. Beware. Haha Haha Ezra Ezra, Your basis for claiming that Heikki is the enemy is ... what? I think that is all tongue in cheek. If Heikki can retire on a beach some place for the rest of his life, then I'm all for it. I just would like to see Oracle state what will be in the next agreement with MySQL AB so programmers don't have this foreboding fear that the giant is coming to town to wreck havoc on the villagers. Mike (already building torches and catapults to keep the monster at bay) On 11/4/05, Ezra Taylor [EMAIL PROTECTED] wrote: To Mysql users: Just to remind you all, Oracle is a business that expects to make money. As you all know, Mysql is a threat to the fat cats such as Oracle,DB2,MSSql and others. If you think Oracle is going to play fair with us then you will believe that crack will one day be a multi vitamin. For those you that don't know what crack is, it's a drug that will fuck your life up. Ezra Taylor On 11/4/05, Heikki Tuuri [EMAIL PROTECTED] wrote: Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, November 05, 2005 12:11 AM Subject: Heikki: What will become of InnoDb once MySQL license runs out Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. the current GPL version of MySQL/InnoDB will of course be available then by the very nature of the GPL license. The MySQL AB - Innobase Oy OEM agreement is about commercial non-GPL MySQL/InnoDB licenses. About that agreement I want to refer to the official press release of Oracle Corporation: http://www.oracle.com/innodb/index.html If yes, will you still be supporting it or will it be up to MySQL AB? I want to refer to the official press release where Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology says: Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software. TIA Mike Regards, Heikki Tuuri Vice President, server technology Oracle Corporation -- 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] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- 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]
Padding date results
I'm generating data to use for a php graph-drawing utility where I summarise data into daily counts of events relating to an 'issue' item from an 'event' table via a 'session' table. My queries are currently along these lines: SELECT DATE_FORMAT(event.timestamp, '%Y-%m-%d') AS adate, COUNT (event.id) AS eventcount FROM issue, `session`, event WHERE `session`.issue_id = issue.id AND event.session_id = `session`.id AND DATE_FORMAT(event.timestamp, '%Y-%m-%d') = '$sdate' AND DATE_FORMAT(event.timestamp, '%Y-%m-%d') '$edate' AND event.eventtype = '$eventtype' GROUP BY adate ORDER BY adate Given $sdate = '2005-10-01' and $edate = '2005-10-06', I might get results like: 2005-10-01 10 2005-10-02 12 2005-10-04 8 2005-10-05 2 These are fine, but notice that days 03 and 06 had 0 results and so don't appear in the result set at all. I'd like results like this: 2005-10-01 10 2005-10-02 12 2005-10-03 0 2005-10-04 8 2005-10-05 2 2005-10-06 0 At present I have a PHP function to pad these gaps in date ranges with zero values, but I suspect I could be getting MySQL to do this. How? I could have a table containing all possible dates and do a left join with that, but that just seems like a crap idea! Perhaps create a set of fixed values for GROUP BY? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB maintenance question?
Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html Consider about switching to per-table tablespace: http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html have an ibdata file that is growing and growing and growing Are there maintenance routines that I can run to keep this file in check? My database is performing close to half a million transactions a day and this file is now well over 75GB. Can I expect this file to continue to grow, until I run out of disk space. I am currently using MySQL 4.1 but will be moving to 5.x soon. Williams, P. Lane wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Padding date results
Marcus Bointon [EMAIL PROTECTED] wrote on 11/06/2005 05:53:50 PM: I'm generating data to use for a php graph-drawing utility where I summarise data into daily counts of events relating to an 'issue' item from an 'event' table via a 'session' table. My queries are currently along these lines: SELECT DATE_FORMAT(event.timestamp, '%Y-%m-%d') AS adate, COUNT (event.id) AS eventcount FROM issue, `session`, event WHERE `session`.issue_id = issue.id AND event.session_id = `session`.id AND DATE_FORMAT(event.timestamp, '%Y-%m-%d') = '$sdate' AND DATE_FORMAT(event.timestamp, '%Y-%m-%d') '$edate' AND event.eventtype = '$eventtype' GROUP BY adate ORDER BY adate Given $sdate = '2005-10-01' and $edate = '2005-10-06', I might get results like: 2005-10-01 10 2005-10-02 12 2005-10-04 8 2005-10-05 2 These are fine, but notice that days 03 and 06 had 0 results and so don't appear in the result set at all. I'd like results like this: 2005-10-01 10 2005-10-02 12 2005-10-03 0 2005-10-04 8 2005-10-05 2 2005-10-06 0 At present I have a PHP function to pad these gaps in date ranges with zero values, but I suspect I could be getting MySQL to do this. How? I could have a table containing all possible dates and do a left join with that, but that just seems like a crap idea! Perhaps create a set of fixed values for GROUP BY? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk Both methods you describe are the commonly used techniques to solve your particular problem. Every RDBMS system I have used responds in exactly the same way to your query. A) a database should not respond with data it does not have. B) I don't know of a SQL statement (in any SQL dialect) that auto-generates a list of dates (or any other series of values) that you could use in this situation. Many SQL languages have looping constructs but I do not think that is what you are looking for. I think you were hoping for a single function/modifier that would have caused the missing dates to appear. AFAIK, SQL does not have such a modifier (not just MySQL but any SQL-based RDBMS) You suggest creating a set of values for the GROUP BY statement... how is that functionally different than using another table and LEFT joining? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: phpmyadmin problems with quoting exported text
Which version? Which export type? Strings TEXT, VARCHAR would be quoted. INT would not, I think. Their forum might be a better place. www.phpmyadmin.net. 2wsxdr5 wrote: I just tried to use the output of the export function on phpmyadmin and got a million errors. After looking at the file I found that certain columns that are strings were not quoted at all. I can't find any reason why some are and some are not quoted. Anyone have any idea why this is happening? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Evaluating text as an expression
I am trying to set up a table where the returned value is a result of evaluating an expression that is stored as text (or varchar). The idea is to have a table with a couple of fields that can contain numeric values or expressions eg NameFred Years 3 Commission base 10% Commission Commission Base + (Years * 2)% I sort imagines that I could do it like SELECT Name, Evaluate(Commission) or as a subquery. Another example I have is to be able to store queries in a table and be able to call them in one call to the database rather than through the provider eg Select evaluate(queryText) from queryTable where queryId = x This is probably a bit more redundant now that 5 has stored procedures etc but still... any ideas Thanks Duncan
Re: Delete all but the newest 100 records?
The problem is the most recent 100 records won't be sequential. There are records with many different identifiers, in random order mixed with other records that I don't want deleted, and each time I do this I'm going to be limiting each subset of identified records to only the most recent 100. So I can't depend on the availability of a autoincrement field. Pretend it's a table of student test scores. I want to delete all of Johnny's test scores except his 100 most recent, but I don't want to delete anyone else's test scores in the same table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete all but the newest 100 records?
Hi Brian, I'd like to delete all but the newest 100 records from a table. I have a timestamp field. I can't figure out how to do this in a single SQL statement - can anyone help? Thanks! This should work: DELETE FROM tbl ORDER BY ts DESC LIMIT 100, 99; Note that ORDER BY and LIMIT are a MySQL-extensions to DELETE, not part of the SQL standards. Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heikki: What will become of InnoDb once MySQL license runs out
On Sun, 2005-11-06 at 16:45 -0600, mos wrote: If this happens, what alternative will MySQL be offering their users who need transactions and RI? The GPLed version of InnoDB? -- Jasper Bryant-Greene General Manager Album Limited e: [EMAIL PROTECTED] w: http://www.album.co.nz/ b: http://jbg.name/ p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303 a: PO Box 579, Christchurch 8015, New Zealand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: phpmyadmin problems with quoting exported text
John Taylor-Johnston wrote: Which version? Which export type? Strings TEXT, VARCHAR would be quoted. INT would not, I think. Their forum might be a better place. www.phpmyadmin.net. I am using phpMyAdmin 2.6.1-rc. I have no control over that as I am not the admin on the server. All the columns I am talking about are varchar. Some get quoted and some do not. Also some integer columns get quoted for some reason I am unaware of. With out quoting the strings it makes the output worthless. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete all but the newest 100 records?
On Nov 6, 2005, at 6:05 PM, Jeremy Cole wrote: Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 Yahoo employs MySQL geeks? Always wondered what db that big index runs on... :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heikki: What will become of InnoDb once MySQL license runs
Mike, the opinions below are my personal opinions. They do not reflect the official standpoint of Oracle Corporation. - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, November 07, 2005 12:51 AM Subject: Re: Heikki: What will become of InnoDb once MySQL license runs At 08:33 PM 11/4/2005, you wrote: Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, November 05, 2005 12:11 AM Subject: Heikki: What will become of InnoDb once MySQL license runs out Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. the current GPL version of MySQL/InnoDB will of course be available then by the very nature of the GPL license. Sure but if people have commercial applications that use InnoDb, then what? Is there a surprise tax waiting for them next year? When we in December 2002 negotiated the current MySQL AB - Innobase Oy OEM contract with MySQL AB's CEO Mårten Mickos, Mårten wanted a clause that makes all the details of the OEM contract confidential. Therefore, I have not been able to disclose the details of the current OEM agreement. The MySQL AB - Innobase Oy OEM agreement is about commercial non-GPL MySQL/InnoDB licenses. About that agreement I want to refer to the official press release of Oracle Corporation: http://www.oracle.com/innodb/index.html From the website: InnoDB's contractual relationship with MySQL comes up for renewal next year. Oracle fully expects to negotiate an extension of that relationship. Terms of the transaction were not disclosed. It seems to me Oracle now has MySQL AB by the short and curlies. bg Negotiating a fair contract renewal could be painful under these conditions, don't you think? I believe in a situation like this it is possible to negotiate a fair contract renewal. In December 2002 the situation was different, and we were able to negotiate a new OEM contract. If Oracle is that much in favor of continuing the InnoDb contract with MySQL, why didn't they pre-announce it saying the terms and conditions would be the same as before. Or are they going to change the contract so they collect $500 or even $1000 extra for every commercial application that is distributed with InnoDb? But companies usually do not pre-announce the bids they are going to make. MySQL AB has not pre-announced MySQL's commercial non-GPL license prices in 2006. A problem is that an OEM contract is between two companies. One company cannot pre-announce what the other company might decide to do. If this happens, what alternative will MySQL be offering their users who need transactions and RI? If yes, will you still be supporting it or will it be up to MySQL AB? I want to refer to the official press release where Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology says: Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software. And did he say at what cost to the MySQL developers? It never struck me that Larry Ellison was a humanitarian who wanted a competitor to succeed. (Did Larry hit his head?vbg) Did Oracle give you any reason as to why they wanted to buy InnoDb? Are they going to be replacing Oracle's row locking with InnoDb? If they're not going to be using InnoDb, why buy it? This is looking more like a preemptive strike against MySQL. In which case, why would they honor the next contract? Future plans of Oracle and Innobase Oy are confidential. Like Jochem van Dieten said earlier in this thread, Oracle is a public company, and disclosure of future plans must go through a channel approved by the SEC. Mike Regards, Heikki Oracle/Innobase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Evaluating text as an expression
See comments interspersed below. Rhino - Original Message - From: Duncan Miller [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, November 06, 2005 8:36 PM Subject: Evaluating text as an expression I am trying to set up a table where the returned value is a result of evaluating an expression that is stored as text (or varchar). The idea is to have a table with a couple of fields that can contain numeric values or expressions eg Name Fred Years 3 Commission base 10% Commission Commission Base + (Years * 2)% I sort imagines that I could do it like SELECT Name, Evaluate(Commission) or as a subquery. Assuming you want to invoke this code with a function name, as in your example, what you're requesting is called a UDF (user-defined function). These are supported as early as MySQL 4.1. Basically, you create a function with a name of your choosing (usually with some restrictions), then write some code behind it to do the work you want. Then you drop that code into MySQL and it becomes just another function that you can use, just like the standard ones built into MySQL. See this page of the 4.1 manual for more information: http://dev.mysql.com/doc/refman/4.1/en/create-function.html. Another example I have is to be able to store queries in a table and be able to call them in one call to the database rather than through the provider eg Select evaluate(queryText) from queryTable where queryId = x This is probably a bit more redundant now that 5 has stored procedures etc but still... I haven't seen the exact functionality you are describing in either DB2 or MySQL but what you are describing is not too different from stored procedures. A stored procedure is basically the name of some code that you can invoke, passing in parameters if you like, and that returns a result set. They are invoked via CALL statements though, not via SELECT statements. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/162 - Release Date: 05/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
locked status problem
Hi, i've got a problem with my server because some times per day, something occurs and server load average grows until 20 due to mysql. When it occurs, with show processlist, I can see a lot of queued queries in locked state ( more than 100 queued). You can see the load average at http://cluster2.genteya.com/load.png ( notice these peaks, 4 o 5 peaks per day). I know a query get into locked state when the table that it's trying to access is locked, but the queries that are in locked state are related to different tables, so it means that all tables are locked when it occurs. I know too that a table is auto-locked when an update or insert is being done, but when all those queries are queued in locked state i can't see any insert/update that affect to a bunch of rows, my updates/inserts always affects to a single row, then i've no idea of what could cause this situation. System is Dual XEON 2.7Ghz ( 4processors), 2Gb RAM and SCSI HDD. Tables are myisam and above you have the info from mysql config and status. I'm waiting hopeful for some help. Thanks ! Ivan L. my.cnf: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-innodb datadir=/disco3/mysql/data #datadir=/usr/local/mysql/data set-variable=long_query_time=1 log-slow-queries = /var/log/mysqld_low.log set-variable=max_connections=2000 key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 10M #2M read_buffer_size = 8M #2M read_rnd_buffer_size = 15M #8M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 50M query_cache_type = 1 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 mysql show status; +++ | Variable_name | Value | +++ | Aborted_clients| 44 | | Aborted_connects | 6 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 512828130 | | Bytes_sent | 1759373754 | | Com_admin_commands | 0 | | Com_alter_db | 0 | | Com_alter_table| 0 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 228518 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 1 | | Com_dealloc_sql| 0 | | Com_delete | 64244 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db| 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql| 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open| 0 | | Com_ha_read| 0 | | Com_help | 0 | | Com_insert | 638593 | | Com_insert_select | 3854 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 0 | | Com_optimize | 46 | | Com_preload_keys | 0 | | Com_prepare_sql| 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 46 | | Com_replace| 41040 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 702124 | | Com_set_option | 140| | Com_show_binlog_events | 0 | | Com_show_binlogs | 1 | | Com_show_charsets | 35 | | Com_show_collations| 35 | | Com_show_column_types | 0 | | Com_show_create_db | 2 | | Com_show_create_table | 4 | | Com_show_databases | 14 | | Com_show_errors| 0 | | Com_show_fields| 24 | | Com_show_grants
Re: Error compiling 5.0.15
Ed, what does gcc --version say? I am able to compile with gcc-3.3.1. in ut0dbg.h we have: #if defined(__GNUC__) (__GNUC__ 2) # define UT_DBG_FAIL(EXPR) UNIV_UNLIKELY(!((ulint)(EXPR))) #else extern ulintut_dbg_zero; /* This is used to eliminate compiler warnings */ # define UT_DBG_FAIL(EXPR) !((ulint)(EXPR) + ut_dbg_zero) #endif and in ut0dbg.c: #if defined(__GNUC__) (__GNUC__ 2) #else /* This is used to eliminate compiler warnings */ ulint ut_dbg_zero = 0; #endif Looks like your compiler does define __GNUC__ 2 when you compile ut0dbg.c, but it does NOT define it when you compile ha_innodb.cc. This could be because ha_innodb.cc is C++ code. Another explanation would be that for some reason make fails to link ./innobase/libut.a in. If you look at the gcc output in compilation, do you see it being linked in? Regards, Heikki Oracle/Innobase - Original Message - From: Ed Kasky [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, November 06, 2005 10:08 PM Subject: Error compiling 5.0.15 I am experiencing problems when trying to compile 5.0.15 on RH 7.3. I run configure --prefix /usr/local/mysql with no apparent errors. when I run make I get the following error: ha_innodb.o: In function `check_trx_exists(THD *)': ha_innodb.o(.text+0x30a): undefined reference to `ut_dbg_zero' ha_innodb.o: In function `innobase_query_caching_of_table_permitted(THD *, char *, unsigned int, unsigned long long *)': ha_innodb.o(.text+0x3ab): undefined reference to `ut_dbg_zero' ha_innodb.o(.text+0x3b6): undefined reference to `ut_dbg_zero' ha_innodb.o: In function `innobase_init(void)': ha_innodb.o(.text+0x69c): undefined reference to `ut_dbg_zero' ha_innodb.o(.text+0x72e): undefined reference to `ut_dbg_zero' ha_innodb.o(.text+0x98c): more undefined references to `ut_dbg_zero' follow collect2: ld returned 1 exit status make[4]: *** [mysqld] Error 1 make[4]: Leaving directory `/usr/src/mysql-5.0.15/sql' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/usr/src/mysql-5.0.15/sql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/src/mysql-5.0.15/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/mysql-5.0.15' make: *** [all] Error 2 Any ideas are greatly appreciated. Also, I know I can use the binary but I have been instaling from src for quite a while and would like to continue if my current OS can handle it. Thanks in advance Ed . . . . . . . . . . . . . . . . . . Randomly Generated Quote (614 of 1009): It is bad luck to be superstitious. -- Andrew W. Mathis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Evaluating text as an expression
Thanks. I see what you mean. I used the 'Evaluate' as an example because in some code you can use that function to execute a text string as code. I sort of thought there may be something similar in SQL / MySQL to allow the execution of a resultant string as if it were code. And yeah a lot of things that were workarounds before can now be done as stored procedures. Of course I could just pass the string to a generic stored procedure to return the result. I'm assuming I can call a stored procedure within an SQL command. Will check it out further. Thanks again Rhino wrote: See comments interspersed below. Rhino - Original Message - From: Duncan Miller [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, November 06, 2005 8:36 PM Subject: Evaluating text as an expression I am trying to set up a table where the returned value is a result of evaluating an expression that is stored as text (or varchar). The idea is to have a table with a couple of fields that can contain numeric values or expressions eg Name Fred Years 3 Commission base 10% Commission Commission Base + (Years * 2)% I sort imagines that I could do it like SELECT Name, Evaluate(Commission) or as a subquery. Assuming you want to invoke this code with a function name, as in your example, what you're requesting is called a UDF (user-defined function). These are supported as early as MySQL 4.1. Basically, you create a function with a name of your choosing (usually with some restrictions), then write some code behind it to do the work you want. Then you drop that code into MySQL and it becomes just another function that you can use, just like the standard ones built into MySQL. See this page of the 4.1 manual for more information: http://dev.mysql.com/doc/refman/4.1/en/create-function.html. Another example I have is to be able to store queries in a table and be able to call them in one call to the database rather than through the provider eg Select evaluate(queryText) from queryTable where queryId = x This is probably a bit more redundant now that 5 has stored procedures etc but still... I haven't seen the exact functionality you are describing in either DB2 or MySQL but what you are describing is not too different from stored procedures. A stored procedure is basically the name of some code that you can invoke, passing in parameters if you like, and that returns a result set. They are invoked via CALL statements though, not via SELECT statements. Rhino
