Re: Navicat MySQL GUI for Linux version 8.0.23 is released.
Hmm. Speaking of Navicat, does anybody out there have an easy way to scrub the control coding from scripts developed under Navicat? I expect that they're there mostly for coloration on displays, but it's kind of obnoxious when you want to do anything else with them. -- Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sun and mysql
Olaf Stein wrote: I am still amazed by the fact that youtube is worth 1.5 billion and MySQL AB barely 1 billion. Did they sell under price? Or does Google just have way to much many to spend/waste? Greetings from the just wondering... Olaf MySQL A.B., so far as I know, derives income from training, pubs sales, and enterprise support, with expense for salaries, space leases (at least some staff work from home), and equipment. The value of such an organization is inevitably based on somebody's best guess about future revenues. I'm encouraged about the prospects of MySQL when I remember that Sun is a major sponsor of open source application software. -- Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql CPU 100%
Nik wrote: I've posted below the output of STATUS and SHOW GLOBAL STATUS. Any and all comments would be much appreciated as to how we can get performance back on track. Wow. . .200 logins? 511 open tables? 277+ million sort rows? On a single PC host? And you're complaining about performance? On the face of it, it sounds like you/'re letting the public beat hell out of that machine, and maybe it needs a little help. -- Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search for column value in a string variable?
OK, never mind. I finally found the 'locate' function. I knew it had to be there somewhere! -- Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Search for column value in a string variable?
Hi. . .I'm trying to be lazy and write a query where I stuff a list of names into a variable (@namelist), and then try to find items in column lastname which appear anywhere in that list. It doesn't want to work, and even regexp isn't working, though that's probably my fault. Any suggestions? I'm completely open to various possibilities of delimiters in the namelist. Obviously this would let me reuse the query over and over, just changing the one line. -- Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining number of vowels in a string
I'd rather do it in a sql statement rather than using a scripting language. I'm thinking you might be able to do one select, accumulating 5 siubstring counts (a,e,i,o,u) into 5 variables, and then sum the counts? I'll leave the testing to you. . .:-) Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integrity on large sites
B. Keith Murphy wrote: Here is the kicker. Each box was a top of the line Sun server that had 32 processors and 32 gigs of RAM. They could handle up to 64 procs and 64 gigs. And each cost well over a million dollars for the hardware alone. Running Oracle on it must have cost over 100,000 dollars for software licenses. Granted this was in 2001, but the licensing cost for Oracle haven't gone down any that I am aware of...and the hardware cost will still be quite steep to do this type of thing. You youngsters may not realize that there were billing applications serving millions of customers long, long before there were any kind of database management systems. They employed concepts called flat files and batch processing. And they ran on machines far weaker than anything any of you have on your desk today. Even under something like MS Windows, it would be absolutely possible to configure 3-5 high speed printers and knock out 100,000 bills per hour from an Intel single CPU box. You really have no appreciation of how much power you actually have at your disposal. Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Printing
I hope this isn't a silly question, or something covered in a FAQ. . .but is there any reason to not have at least some primitive print formatting commands in MySQL? Or am I missing something blindingly obvious? Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble connecting to server
At 11:36 AM 4/23/2007, Drew Burchett wrote: I have a mysql v 5.0 server running on Suse Linux 10.1. It has been running steadily and properly for several months now. However, when I came in this morning, my network card in the machine was bad. I replaced the card and reconfigured the network, but ever since then, I can only connect to mysql by using localhost. If I try to connect using the IP address, it simply hangs. If I do a netstat -aln | grep '3306', it shows mysql listening on all IP addresses, and it shows the connection to itself with the flag SYN_SENT. But that's as far as it goes. I've restarted the machine several times in vain hopes that it might kickstart something. I've also tried connecting from other machines, which seems to work perfectly. Any suggestions on how to proceed troubleshooting this? Reconfigured the network. . .Is it possible that the old IP address is no longer the one assigned to the machine? DHCP? Just a thought. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [LICENSING] why so hazy? Comparing to Samba.
At 05:00 PM 2/22/2007, mos wrote: On the other hand, if you developed a web application that ran on MySQL (an accounting package say) and you want to distribute it to 1000 MySQL users without giving them your source code, then you will need a MySQL AB license for each copy ($595,000 in total) even if you give the software away for free. The last line of the license notice reads: Contact MySQL AB if you need clarification of these terms or if you need to ask about alternative arrangements. This kind of suggests to me that they're willing to talk. I expect that as long as you're willing to pay *something*, there's probably a lot of room for negotiation. It's distinctly not in their interest to eliminate collateral development efforts. And there are already several products out there which do connect with MySQL and cost $100. Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Any good free Case tools for MySQL 5.x?
mos wrote: Phil, FabForce doesn't work with MySQL 5 because of the new password encryption. Fabforce never lets me connect to a database. I suppose I could revert back to the old PW mgt scheme but that may weaken the security. There was an earlier post on this list which discussed new vs old password management. The gist is, that the configuration switch controls how *future* passwords are constructed. So, you can switch to old passwords, configure 1 userid for Fabforce with any privileges you choose, and switch to new passwords again. None of your existing arrangements are disturbed, but you can now use the single old-style userid to connect with DBD4, and keep your production users as secure as possible. I've just done this on Win XP, and it works very nicely. Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Performance Question And Problem
At 10:47 PM 11/23/2006, John Kopanas wrote: That is awesome... thanks. I still am not sure exactly though why this take 2 seconds while my methond took over a minute for the same amount of rows. In essence don't the two methods do the same things? No. Your approach was executing the subquery 2000 times for the 2000 records in your company file. And will run 500,000 times when you go to production data. Somebody with better math than I should try to project that. His prep query runs once, and his update query runs once. Scales very nicely. Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some questions on Storage engine
At 10:20 PM 8/22/2006, Chris wrote: You can't store them in memory. http://dev.mysql.com/doc/refman/5.1/en/temporary-table-problems.html Despite what the doc says, I posted a working script here a couple of weeks ago which creates temporary tables with engine=Memory. Either my specification was being ignored in favor of some default--with no error indication, or somebody forgot to document a new feature. This has only been tried by me on Win XP. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
Well, I said earlier that if I found a solution to this, I'd post it. Here it is, with many thanks to Nicholas Bernstein's timely July 7 post to the doc on user variables: It's not particularly elegant, it just gets the job done. If there is a cleaner way to do this, I'm not ashamed to be educated. Barry * Compquery.sql -- Compare Current Year Reg Numbers and Money to Prior Year */ /* */ /* */ /* ACCUMULATE DATA BY MONTH FOR BOTH YEARS*/ Drop Table If Exists Montable, Montable2; Create Temporary Table Montable engine=memory Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex, Sum(Amount) as Paid From capclave2005reg where ( amount 0) Group by Monindex; Create Temporary Table Montable2 engine=memory Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex, Sum(Amount) as Paid From Capclavepresent where ( amount 0) Group by Monindex; /* REPORT FOR BOTH YEARS WITH RUNNING TOTALS*/ Set @cumreg=0, @cumreg2=0, @cumpd=0, @cumpd2=0; Select Month, Year, Registrations, Paid RegIncom, Monindex, @cumreg:[EMAIL PROTECTED] + Registrations RegYearToDate, @cumpd:= @cumpd+Paid RegIncomeYTD From Montable Union Select Month, Year, Registrations, Paid RegIncome, Monindex, @cumreg2:[EMAIL PROTECTED] + Registrations RegYearToDate, @cumpd2:= @cumpd2+Paid RegIncomeYTD From Montable2 ; Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
At 04:15 PM 8/3/2006, Brent Baisley wrote: You might look into WITH ROLLUP. That could easily give you cumulative totals for the year, but off the top of my head I can't think of a way to get it for the months. - Original Message - From: Barry Newton [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, August 02, 2006 10:29 PM Subject: Running Totals? Back with another registration db question: Have a convention database which tracks people as they register all year long; the actual convention is held in October. I've got a fairly simple query which shows how many people registered in each calendar month--useful to compare to prior year to see if we're at least on track with our count. It would make life easier if I could also show a column with the cumulative count for each month. The existing output is: That's what happens with ROLLUP. I'm looking into a possible subquery approach just now. If it works, it will be worth it's own post. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running Totals?
Back with another registration db question: Have a convention database which tracks people as they register all year long; the actual convention is held in October. I've got a fairly simple query which shows how many people registered in each calendar month--useful to compare to prior year to see if we're at least on track with our count. It would make life easier if I could also show a column with the cumulative count for each month. The existing output is: +---+--+---+--+ | Month | Year | Registrations | Monindex | +---+--+---+--+ | October | 2004 |23 | 200410 | | December | 2004 | 5 | 200412 | | January | 2005 | 9 | 200501 | | February | 2005 |11 | 200502 | | April | 2005 | 2 | 200504 | | May | 2005 |48 | 200505 | | June | 2005 |45 | 200506 | | July | 2005 |10 | 200507 | | August| 2005 |17 | 200508 | | September | 2005 |58 | 200509 | | October | 2005 |97 | 200510 | +---+--+---+--+ The cumulative column would ideally show 23,28,37, etc. Also, if anyone has a better way to keep the different years apart than the 'monindex' column, or at least to suppress displaying it, I'll be really interested. The existing query is: Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex From capclave2005reg Where year(DatePaid)=2004 and (amount 0 or Dealer = 'Y') Group by Monindex Union Select Monthname(DatePaid) Month, Year(DatePaid) Year, count(*) as Registrations, Extract(Year_Month from DatePaid) Monindex From capclave2005reg where year(DatePaid)=2005 and (amount 0 or Dealer = 'Y') Group by Monindex; Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Totals?
At 11:10 PM 8/2/2006, Peter Brawley wrote: Barry It would make life easier if I could also show a column with the cumulative count for each month. Set @cum - 0; Select Monthname(DatePaid) Month, Year(DatePaid) Year, Count(*) as Registrations, Extract(Year_Month from DatePaid) AS Monindex, @cum := @cum + Count(*) AS 'Year to date' From capclave2005reg Where year(DatePaid)=2004 and (amount 0 or Dealer = 'Y') Group by Monindex ; PB Looked promising, but gets me the following, which isn't quite right: +---+--+---+--+--+ | Month | Year | Registrations | Monindex | Year to date | +---+--+---+--+--+ | October | 2004 |23 | 200410 | 23 | | December | 2004 | 5 | 200412 |5 | | January | 2005 | 9 | 200501 | 14 | | February | 2005 |11 | 200502 | 16 | | April | 2005 | 2 | 200504 |7 | | May | 2005 |48 | 200505 | 53 | | June | 2005 |45 | 200506 | 50 | | July | 2005 |10 | 200507 | 15 | | August| 2005 |17 | 200508 | 22 | | September | 2005 |58 | 200509 | 63 | | October | 2005 |97 | 200510 | 102 | +---+--+---+--+--+ Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: swapping column values in update
At 06:35 PM 8/1/2006, Martin Jespersen wrote: I just ran the following sql (on mysql 4.1.20): update tbl set col1=col2, col2=col1 To my surprise, mysql updates col1 via col1=col2 before reading it for use in col2=col1, so I end up with the same value in both columns, which, of course, was not my intention. Thinking about it, this behavior in mysql makes perfect sense, so thats not the issue. If this is a one-time operation, it would seem easier to rename the columns. In some cases, even if it's a little more frequent than that. Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql + LVS highjacked (mysql + NFS ramfs)
At 02:05 PM 7/25/2006, Winn Johnston wrote: after talking to a few people on the #mysql irc someone suggested using NFS to create a ramfs to get 100GB+ RAM shared memory to load the entire database into the RAM. Can anyone offer any Pros or Cons to this setup, drawing from personal expierence? thanks -winn johnston Do you really mean 100Gb RAM? Is that actually possible today? Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to query on part of a date column?
I've got a table of people who registered for a convention. Each person has a registration date, kept in a standard date field. How do I select for people who registered in a particular month or year? The obvious tests like: Select * from Capclave2005reg Where Year('Date Paid') = 2004; return no rows. I can extract any piece of that date I want in a SELECT, but can't seem to use it in a WHERE clause at all. There has to be something really obvious that I'm missing? Barry Newton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]