Re: TimeZone
Joseph Cochran wrote: Some countries have multiple timezones, so it is not sufficient to know the country code in order to get the timezone. If they have previously posted the timezone, however, then it should be possible to store that information in a cookie on the client machine that your web layer can retrieve. If you want to permanently tie a timezone to a user (assuming that this is an internal system or other system to which your users authenticate -- if it is a public website you're going to have to use cookies), simply include an extra column in the user's record that has a number that stores its differential from GMT (so the USA east coast would be -5) and save all of your data in GMT, applying the timezone column to the time via datetime functions either in the query or in your web layer. One more complication: daylight savings time are not the same world wide. So I would store the time zone and not the difference with GMT. I personally would do al the time zone calculations in the web layer. Most OSs have libraries with more or less knowledge about daylight savings in various countries/timezones. Using the functions in the language of the web layer you're more likely to get things right. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to link to crystal report
Elizabeth Bonifacio wrote: Hi guys, I'm new in database development, and has been wondering if anyone can suggest a good reporting tool that will help me generate graphical report for my database queries using visual c++? Can I use excell to format my queries into graph? My project involves the developement of syslog server in a windows xp environment using mysql 4.1 and visual C++ version 6.0. Aside from the graphical report, my syslog server should be receiving online logs from few network devices such as fortinet and tripwire using sysrose daemon. Can anyone please advice me on which table type will be most suitable for inserting and querying huge amount of syslog on a real time basis? Hoping for your advice. betcha I don't know about C++ tools, but as for other tools, you can use: - JPGraph, a php graphing library. It produces very high quality, professional looking *graphs* ( not reports ). It's dual-licensed - open-source for open-source projects, and a commercial license for commercial projects http//www.aditus.nu/jpgraph/ - PDF::ReportWriter, my own Perl module, for high quality reports. I obviously recommend it :) It supports importing images from external sources, so you can, for example, use a combination of JPGraph and PDF::ReportWriter http://entropy.homelinux.org/axis_not_evil - Agata, a PHP-Gtk reporting application. It looks *very* nice, but I've never been able to get it working ( gtk-scintilla doesn't compile on my system ) http://www.agata.org.br/us/index.php - JasperReports, a java graphing library with GUI report designer. I haven't used it ( can't handle Java ), but it looks interesting http://jasperreports.sourceforge.net/ - Papyrus, a C reporting app. I used it for a while before rolling my own PDF::ReportWriter. http://papyrus.treshna.com/ -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql and MH_DYLIB error message
Hi, I'm trying to compile an apache module that uses mysql, but I keep getting this weird error message: ld: common symbols not allowed with MH_DYLIB output format with the -multi_module option /usr/local/mysql/lib/libmysqlclient.a(charset.o) definition of common _all_charsets (size 1024) /usr/local/mysql/lib/libmysqlclient.a(my_error.o) definition of common _errbuff (size 512) /usr/bin/libtool: internal link edit command failed Anyone seen this error? or know how to fix it? this is on a mac osx 10.3, using libtool... Here's the original command which causes this error: /usr/local/bin/libtool --silent --mode=link gcc -g -O2 -I/usr/local/mysql/include -Wall -I/usr/local/mysql/include -I/usr/local/apache2/include -I/usr/local/apache2/include -I/usr/local/apache2/include -L/usr/local/mysql/lib -lmysqlclient -lz -lm -L/usr/local/apache2/lib -rpath /usr/local/mysql/lib -o libmod_dav_repos.la -avoid-version mod_dav_repos.lo repos.lo props.lo search.lo dbms.lo util.lo lock.lo version.lo dbms_mysql.lo -lmysqlclient -L/usr/local/apache2/lib -laprutil-0 -lexpat -liconv -L/usr/local/apache2/lib -lapr-0 -lresolv -lpthread Several elements in this command line are repeated because they are generated by a big set of automake macros. thanks dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to link to crystal report
--- Elizabeth Bonifacio <[EMAIL PROTECTED]> wrote: > Hi guys, > I'm new in database development, and has been > wondering if anyone can > suggest a good reporting tool that will help me > generate graphical > report for my database queries using visual c++? Can > I use excell to > format my queries into graph? > If all you need is to query the database and create an Excel SpreadSheet, I'd use perl plus the DBI and Spreadsheet::WriteExcel modules. regards, esv. Enrique Sanchez Vela email: [EMAIL PROTECTED] - It's often easier to fight for one's||We live in the outer space principles than to live up to them||Rev. Kay Greenleaf Adlai Stevenson|| __ Yahoo! Mail Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regarding Security Problem - Murali (India)
First I have to Thank you for creating such a wonderful backend. Problem Statment : My Operating System is Windows 98. I have set password for my MySql database. Its working fine. Following are the databases that i am using in My Data Folder Employee Sales Marketing Mysql Reports But if i replaced that Mysql database with the old one(which is not set password) it's not asking for the password. I know that is how it has been designed but to overcome this problem what shall I do? Please help me out. Thank you V.Murali India
how to link to crystal report
Hi guys, I'm new in database development, and has been wondering if anyone can suggest a good reporting tool that will help me generate graphical report for my database queries using visual c++? Can I use excell to format my queries into graph? My project involves the developement of syslog server in a windows xp environment using mysql 4.1 and visual C++ version 6.0. Aside from the graphical report, my syslog server should be receiving online logs from few network devices such as fortinet and tripwire using sysrose daemon. Can anyone please advice me on which table type will be most suitable for inserting and querying huge amount of syslog on a real time basis? Hoping for your advice. betcha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limit on fulltext match?
Does anyone know if there Is there a limit on the number/length of terms using "match against()"? Thanks! -- Avi
Disasterous database corruption
Hi all. I've been testing out mysql-5.0.10 on my Powerbook ( Gentoo Linux PPC ), and I've hit an incredibly unfortunate bug. It is demonstrated adequately with the following: mysql> use entropy; Database changed mysql> show tables; ERROR 1052 (23000): Column 'TABLE_NAME' in order clause is ambiguous mysql> quit Bye [EMAIL PROTECTED] ~ $ mysqldump -u root --opt --all-databases > full_dump.sql -p Enter password: mysqldump: mysqldump: Couldn't execute 'SHOW DATABASES': Column 'SCHEMA_NAME' in field list is ambiguous (1052) [EMAIL PROTECTED] ~ $ I can't get anything out of the DB with any GUI tools. I assume they all want to inspect the tables and bail out when they hit something like the above. The data is still there, and I can select from tables that I already know the name of ( which, luckily, I do ... for the important stuff ). So I suppose I don't *really* need any help in backing stuff up - I can back up table by table to text files and then re-import. Is anyone interested in examining what went wrong? ie should I create a bug report? I suppose I'd have to upload a zipped copy of my /var/lib/mysql folder or something, since mysql isn't too keen on giving up any data voluntarily. Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeZone
Some countries have multiple timezones, so it is not sufficient to know the country code in order to get the timezone. If they have previously posted the timezone, however, then it should be possible to store that information in a cookie on the client machine that your web layer can retrieve. If you want to permanently tie a timezone to a user (assuming that this is an internal system or other system to which your users authenticate -- if it is a public website you're going to have to use cookies), simply include an extra column in the user's record that has a number that stores its differential from GMT (so the USA east coast would be -5) and save all of your data in GMT, applying the timezone column to the time via datetime functions either in the query or in your web layer. -- Joe On 8/8/05, KH <[EMAIL PROTECTED]> wrote: > Hi, > There is a request from mgmt, when user browse the request (web), the > database will return the requested timestamp at their timezone that > previously posted. How do i do that? Do I need store whole country codes > together timezones in database mysql ? Is there any way to find full > country code together with their timezone? > > > Cheers > KH > > > -- > 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: Remote Connection Problem
Thanks Shawn. It seems to be just that. >>> [EMAIL PROTECTED] 08/08/05 03:02PM >>> "Kent Roberts" <[EMAIL PROTECTED]> wrote on 08/08/2005 04:50:15 PM: > I am having inconsistent behavior maintaining a remote connection > with MySql Administrator/Browser. Brand new installations of clients > on Windows XP and MySql server on Windows 2003 server. It worked > fine at first then suddenly I could no longer connect (Error #1045. > Access denied for user '[EMAIL PROTECTED]' Using password: YES). I tweaked > permissions to the [EMAIL PROTECTED] (MDAS2 is the name of the client > machine) to add SELECT for both the mysql and information_schema > schemas. Connections worked well for most of today when suddenly my > connection was no longer valid and now I can't connect again. Any ideas? > > Thanks in advance, > Kent Roberts > Missoula, MT > DNS issues? Reverse DNS lookups seem to be emerging as a weak point for MySQL. Try running your server with --skip-name-resolve and setting your permissions to be IP address (not domain name) specific and see if the problem persists. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Query HELP!
m i l e s <[EMAIL PROTECTED]> wrote on 08/08/2005 05:05:46 PM: > Shawn, > > Took me a bit to digest what you were sayin but if I get it the way > you splain'd it > then the following should work: > > ++ > > SELECT > cmc_search_members.PropertyName AS pNAME, > cmc_search_members.PropertyEmail AS pEMAIL, > cmc_user_count.user_id, > MAX(cmc_user_count.date_time) AS pDATE, > COUNT(cmc_user_count.user_id) AS pHITS > FROM cmc_search_members > INNER JOIN cmc_user_count ON cmc_search_members.Property_ID = > cmc_user_count.user_id > GROUP BY pNAME, pEMAIL > ORDER BY pDATE DESC > > ++ > > If I want a NON-CUMULATIVE result, ie: > > pNAME + pHITS + pDATE > --- > fillmore+ 198 + 08/08/2005 > mannor inn + 56+ 08/08/2005 > seacrest+ 23+ 08/08/2005 > --- > fillmore+ 102 + 08/07/2005 > mannor inn + 89+ 08/07/2005 > seacrest+ 19+ 08/07/2005 > > etc. > > Then Im assuming the statement above will NOT produce this result ? > > What'd be great is if I could get that in Alphabetical order as > well...I tried adding a > ORDER BY pDATE DESC, pNAME ASC but DESC doesn't help. > > Which is what I was lookin for. > > If you want your results broken down by dates, then you have to group on some sort of date value. Try this: SELECT cmc_search_members.PropertyName AS pNAME, cmc_search_members.PropertyEmail AS pEMAIL, cmc_user_count.date_time AS pDATE, COUNT(cmc_user_count.user_id) AS pHITS FROM cmc_search_members INNER JOIN cmc_user_count ON cmc_search_members.Property_ID = cmc_user_count.user_id GROUP BY PDATE DESC, pNAME, pEMAIL; For more details: http://dev.mysql.com/doc/mysql/en/select.html # If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. MySQL has extended the GROUP BY clause as of version 3.23.34 so that you can also specify ASC and DESC after columns named in the clause: SELECT a, COUNT(b) FROM test_table GROUP BY a DESC That query will break down, by date, all of the hits for any pNAME+pEMAIL combination. If we are not lucky enough that `cmc_user_count`.`date_time` contains only date values but instead it contains dates+times then we need to strip the time elements out in order to get just a daily grouping. It would look something like this: SELECT cmc_search_members.PropertyName AS pNAME, cmc_search_members.PropertyEmail AS pEMAIL, DATE(cmc_user_count.date_time) AS pDATE, COUNT(cmc_user_count.user_id) AS pHITS FROM cmc_search_members INNER JOIN cmc_user_count ON cmc_search_members.Property_ID = cmc_user_count.user_id GROUP BY PDATE DESC, pNAME, pEMAIL; (see http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html for other options) Is this whole GROUP BY thing starting to make a little more sense now? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: query - select from one, update another
Jasper Bryant-Greene wrote: Michael Stassen wrote: Not exactly. They aren't the same. COUNT(id) counts distinct values of id, while COUNT(*) simply counts rows. [snip] Actually, COUNT(id) counts non-NULL values of id. COUNT(DISTINCT id) would count distinct values. http://dev.mysql.com/doc/mysql/en/group-by-functions.html#id2873509 Jasper Don't know where my brain was, but you are exactly right. Sorry for my mistake. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remote Connection Problem
"Kent Roberts" <[EMAIL PROTECTED]> wrote on 08/08/2005 04:50:15 PM: > I am having inconsistent behavior maintaining a remote connection > with MySql Administrator/Browser. Brand new installations of clients > on Windows XP and MySql server on Windows 2003 server. It worked > fine at first then suddenly I could no longer connect (Error #1045. > Access denied for user '[EMAIL PROTECTED]' Using password: YES). I tweaked > permissions to the [EMAIL PROTECTED] (MDAS2 is the name of the client > machine) to add SELECT for both the mysql and information_schema > schemas. Connections worked well for most of today when suddenly my > connection was no longer valid and now I can't connect again. Any ideas? > > Thanks in advance, > Kent Roberts > Missoula, MT > DNS issues? Reverse DNS lookups seem to be emerging as a weak point for MySQL. Try running your server with --skip-name-resolve and setting your permissions to be IP address (not domain name) specific and see if the problem persists. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Query HELP!
Shawn, Took me a bit to digest what you were sayin but if I get it the way you splain'd it then the following should work: ++ SELECT cmc_search_members.PropertyName AS pNAME, cmc_search_members.PropertyEmail AS pEMAIL, cmc_user_count.user_id, MAX(cmc_user_count.date_time) AS pDATE, COUNT(cmc_user_count.user_id) AS pHITS FROM cmc_search_members INNER JOIN cmc_user_count ON cmc_search_members.Property_ID = cmc_user_count.user_id GROUP BY pNAME, pEMAIL ORDER BY pDATE DESC ++ If I want a NON-CUMULATIVE result, ie: pNAME + pHITS + pDATE --- fillmore+ 198 + 08/08/2005 mannor inn + 56+ 08/08/2005 seacrest+ 23+ 08/08/2005 --- fillmore+ 102 + 08/07/2005 mannor inn + 89+ 08/07/2005 seacrest+ 19+ 08/07/2005 etc. Then Im assuming the statement above will NOT produce this result ? What'd be great is if I could get that in Alphabetical order as well...I tried adding a ORDER BY pDATE DESC, pNAME ASC but DESC doesn't help. Which is what I was lookin for. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Remote Connection Problem
I am having inconsistent behavior maintaining a remote connection with MySql Administrator/Browser. Brand new installations of clients on Windows XP and MySql server on Windows 2003 server. It worked fine at first then suddenly I could no longer connect (Error #1045. Access denied for user '[EMAIL PROTECTED]' Using password: YES). I tweaked permissions to the [EMAIL PROTECTED] (MDAS2 is the name of the client machine) to add SELECT for both the mysql and information_schema schemas. Connections worked well for most of today when suddenly my connection was no longer valid and now I can't connect again. Any ideas? Thanks in advance, Kent Roberts Missoula, MT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query - select from one, update another
Michael Stassen wrote: Not exactly. They aren't the same. COUNT(id) counts distinct values of id, while COUNT(*) simply counts rows. [snip] Actually, COUNT(id) counts non-NULL values of id. COUNT(DISTINCT id) would count distinct values. http://dev.mysql.com/doc/mysql/en/group-by-functions.html#id2873509 Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query HELP!
Miles, IS the following query counting cumulative (see below **) pHITS or is it counting individual counts for each user for a particular day. Im not skilled enough to answer this question myself. My instinct says that its counting cumulative values and NOT individual counts for each property name. SELECT search_members.Property_Name AS pNAME, search_members.Property_Email AS pEMAIL, MAX(user_count.u_datetime) AS pDATE, COUNT(user_count.u_userid) AS pHITS FROM search_members INNER JOIN user_count ON search_members.Property_ID = user_count.u_userid GROUP BY pNAME, pEMAIL, search_members.Property_ID ORDER BY pDATE DESC Is that query a bit confused, or is it me? If there's just one Property_Name and one Property_Email per property_Id in the search_members table, there's not a need to GROUP BY pname and pemail, and the query seems intended to return the latest user_count.u_datetime and the count of non-null values of user_count.u_userids for every search_members.property_Id. If there can be multiple names & emails per property_ID, the query will break down the counts by name, email then property_id, which would seem bizarre :-) . PB m i l e s wrote: Hi, IS the following query counting cumulative (see below **) pHITS or is it counting individual counts for each user for a particular day. Im not skilled enough to answer this question myself. My instinct says that its counting cumulative values and NOT individual counts for each property name. SELECT search_members.Property_Name AS pNAME, search_members.Property_Email AS pEMAIL, MAX(user_count.u_datetime) AS pDATE, **COUNT(user_count.u_userid) AS pHITS FROM search_members INNER JOIN user_count ON search_members.Property_ID = user_count.u_userid GROUP BY pNAME, pEMAIL, search_members.Property_ID ORDER BY pDATE DESC I have a feeling that the COUNT line should be something similar to select distinct statement Any ideas ? Sincerely, M i l e s. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query HELP!
m i l e s <[EMAIL PROTECTED]> wrote on 08/08/2005 03:10:21 PM: > > Hi, > > IS the following query counting cumulative (see below **) pHITS or is > it counting individual counts for each user for a particular day. Im > not skilled enough to answer this question myself. My instinct says > that its counting cumulative values and NOT individual counts for > each property name. > > SELECT > search_members.Property_Name AS pNAME, > search_members.Property_Email AS pEMAIL, > MAX(user_count.u_datetime) AS pDATE, > **COUNT(user_count.u_userid) AS pHITS > FROM search_members INNER JOIN user_count ON > search_members.Property_ID = user_count.u_userid > GROUP BY pNAME, pEMAIL, search_members.Property_ID > ORDER BY pDATE DESC > > I have a feeling that the COUNT line should be something similar to > select distinct statement > > Any ideas ? > > Sincerely, > > M i l e s. > Your original query,reformatted for explanatory purposes: SELECT search_members.Property_Name AS pNAME, search_members.Property_Email AS pEMAIL, MAX(user_count.u_datetime) AS pDATE, COUNT(user_count.u_userid) AS pHITS FROM search_members INNER JOIN user_count ON search_members.Property_ID = user_count.u_userid GROUP BY pNAME , pEMAIL , search_members.Property_ID ORDER BY pDATE DESC You wanted to know what this query is calculating, right? Let's look first at your select terms: terms 1 and 2 are direct field values, terms 3 and 4 are the results of aggregate functions. Now let's look at what you are grouping by: pNAME, pEMAIL, and search_members.Property_id. if you had done a GROUP BY *only on* pNAME and pEMAIL, you would have seen each pair of values appear only once in your output along with the last time they did *something* (not sure what it was), the pDate value, and how many times they did it, the pHITS value. However you are also computing those statistics _ for each property_ID _. So if the same pNAME+pEMAIL pair had performed whatever it was they had to do to generate some pDate and pHITS values for more than one Property_ID value, then you will see one pNAME+pEMAIL pair listed for EACH PROPERTY ID to which the statistics apply. One way to see this more clearly is to add the Property_ID column into the SELECT portion of your query. SELECT search_members.Property_Name AS pNAME, search_members.Property_Email AS pEMAIL, search_members.Property_ID, MAX(user_count.u_datetime) AS pDATE, COUNT(user_count.u_userid) AS pHITS FROM search_members INNER JOIN user_count ON search_members.Property_ID = user_count.u_userid GROUP BY pNAME , pEMAIL , search_members.Property_ID ORDER BY pDATE DESC Now you should be able to physically "see" why the pNAME+pEMAIL pairs were duplicating in what may have appeared to be randomly and without reason. I am not sure if I answered your question but this seemed like the most likely issue for confusion. Is it cumulative? Yes. Is it per day? No. Is it per Name+Email+Property_ID? Yes. HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: query - select from one, update another
Sebastian wrote: Gleb Paharenko wrote: Hello. What about this: UPDATE news SET comments = ( SELECT COUNT(id) FROM comments WHERE newsid = news.id GROUP BY newsid ); Hi. i came up with a similar query last night, but i didnt use group by. I have question, is it better to use COUNT(*) ? i thought there is extra mysql optimization when you use * thanks. Not exactly. They aren't the same. COUNT(id) counts distinct values of id, while COUNT(*) simply counts rows. When you say, "extra mysql optimization", I expect you are thinking of the case where you want the number of rows in a table, with no WHERE or GROUP BY clause, like this: SELECT COUNT(*) FROM mytable; If the table is MyISAM, this is very quick, because MyISAM tables keep the current row count in the table information. This doesn't apply to your case, however. On the other hand, it will certainly be the case for you that no id to be counted will be repeated, so COUNT(*) and COUNT(id) will give the same answer. In that case, COUNT(*) may be faster, as there is no need to waste time checking ids for distinctness. I also think you don't want the GROUP BY newsid. It isn't needed, because for each run of the subquery, only 1 newsid is considered, and it causes NULLs to be entered for items with no comments, when I expect you want 0s. Gleb's example data and query: UPDATE news SET comments = ( SELECT COUNT(id) FROM comments WHERE newsid = news.id GROUP BY newsid ); SELECT * FROM news; +--+--+ | id | comments | +--+--+ |1 |2 | |2 |1 | |3 | NULL | |4 |3 | |5 | NULL | +--+--+ 5 rows in set (0.00 sec) Same data, but with COUNT(*) and no GROUP BY: UPDATE news SET comments = ( SELECT COUNT(*) FROM comments WHERE newsid = news.id ); SELECT * FROM news; +--+--+ | id | comments | +--+--+ |1 |2 | |2 |1 | |3 |0 | |4 |3 | |5 |0 | +--+--+ 5 rows in set (0.00 sec) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query HELP!
Hi, IS the following query counting cumulative (see below **) pHITS or is it counting individual counts for each user for a particular day. Im not skilled enough to answer this question myself. My instinct says that its counting cumulative values and NOT individual counts for each property name. SELECT search_members.Property_Name AS pNAME, search_members.Property_Email AS pEMAIL, MAX(user_count.u_datetime) AS pDATE, **COUNT(user_count.u_userid) AS pHITS FROM search_members INNER JOIN user_count ON search_members.Property_ID = user_count.u_userid GROUP BY pNAME, pEMAIL, search_members.Property_ID ORDER BY pDATE DESC I have a feeling that the COUNT line should be something similar to select distinct statement Any ideas ? Sincerely, M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create database outside /mysql/data/
Hi Laurie You can put the DB data on any disk or directory as long as it's location is in the .ini file #Path to the database root datadir="C:/MySQL/Data/" HTH Steve l'[EMAIL PROTECTED] wrote: I installed Mysql on windows and found out that the documentation does not talk about installing the database in another directory or partition of the disk. Is it possible in Windows? I have little space left on the partition where progrxxxFiles/mysql/ is. THanks Laurie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create database outside /mysql/data/
Yes, using symbolic links. http://dev.mysql.com/doc/mysql/en/windows-symbolic-links.html On 8/8/05, l'[EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I installed Mysql on windows and found out that the documentation does not > talk about installing the database in another directory or partition of the > disk. > > Is it possible in Windows? > I have little space left on the partition where progrxxxFiles/mysql/ is. > > THanks > Laurie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create database outside /mysql/data/
I installed Mysql on windows and found out that the documentation does not talk about installing the database in another directory or partition of the disk. Is it possible in Windows? I have little space left on the partition where progrxxxFiles/mysql/ is. THanks Laurie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query - select from one, update another
Nuno Pereira <[EMAIL PROTECTED]> wrote on 08/08/2005 01:49:44 PM: > Sebastian wrote: > > Nuno Pereira wrote: > > > >> [EMAIL PROTECTED] wrote: > >> > >>> Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 > >>> 10:53:55 AM: > >>> > >>> > Gleb Paharenko wrote: > > > > Hello. > > > > > > > > What about this: > > > > > > > > UPDATE news > > > > SET comments = > > > > ( > > > > SELECT COUNT(id) > > > > FROM comments > > WHERE newsid = news.id > > GROUP BY newsid > > > > ); > > > > Hi. i came up with a similar query last night, but i didnt use group > by. > I have question, it it better to use COUNT(*) ? i thought there is > extra > >>> > >>> > mysql optimization when you use * > > thanks. > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 > > > >>> > >>> > >>> IMHO, the better solution is to INCREMENT your comment count *as you > >>> add each comment* . There will be much less SQL processing involved > >>> and it keeps your database in a consistent state. > >>> Doing things your way, you accumulate "uncounted" comments and your > >>> count will be wrong for the period between whole table recounts. If > >>> you update your static "comment count" statistic every time you add > >>> or delete a comment, you won't run into this problem. > >>> > >>> Plus, think of how often you would have to summarize (recount) your > >>> entire table just to register a few additions/deletions. How many > >>> processing cycles will you save by just setting the value to what it > >>> should be at the finish of the comment transaction? > >>> This is one of those time when a "dynamic" solution is too "heavy" > >>> for frequent use. > >>> Shawn Green > >>> Database Administrator > >>> Unimin Corporation - Spruce Pine > >>> > >> > >> It is not a bad idea to also DECREMENT the comment count each time a > >> comment is deleted (if you do it), but that doesn't occur very often. > >> This is implicit in your comment, but is not a bad idea to explicit it. > >> > > > > I sort of agree with you guys but i think there are downsides to both > > methods.. > > if i do update the comment column when comments are added / deleted then > > that is an extra query each time someone posts. in an hour we can get > > several hundred comments, that also means several hundred db queries. > > > > at least i figured i can update these fields every 5 minutes or so and > > save some extra calls each time someone posts a comment. i guess i have > > to figure out which method would be best when dealing with a lot of > > traffic, but i think it will be neglible. right now that subquery runs > > in under .25 ms with 50,000 records to count.. not too bad i guess. > > > > > > You can see the number of updates, deletes, selects by performing a > > SHOW STATUS LIKE 'com_'; > > This shows how many queries of each types the MySQL server got since his > last reboot. > It can help you decide the best method, and the impact of making the > extra query per insert/delete. > If you update the count each _N_ seconds, the readers will see an old > value and they could, for example, not post a reply, because they didn't > saw the value being updated. > I think that normally people prefer the first, or you could the value of > _N_ be smaller (1 minute, for example). > -- > Nuno Pereira > The problem with just counting statement executions is that it doesn't show you the actual load each statement puts on the CPU. A better stat to track would be MySQL's CPU % per minute over the course of an hour. Then, during a period of similar loading, swap methods and track the CPU % again for another hour. The better method would be the one with the lowest contribution to overall load. I don't think that MySQL has an internal statistic like this, I think the OP is going to need to look at the O/S level to collect his stats. For as little load as he has, things should still scale fairly linearly. For instance, let's say these are some initial testing results: Method - # of comments added/deleted - Avg CPU %/min A - 800 - 40 B - 400 - 20 I would count that as equivalent results (no real difference). Even though B showed only half as much processing, it only had half the load. The best comparison would result from comparing performances during periods of equivalent loading. One other very important statistic to measure is "idle time" for your disks. You definitely want the method that produces the LEAST disk activity (most idle time). That means that your disks have more time to fulfill user requests and your entire system will seem more responsive. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: query - select from one, update another
Sebastian wrote: Nuno Pereira wrote: [EMAIL PROTECTED] wrote: Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 10:53:55 AM: Gleb Paharenko wrote: Hello. What about this: UPDATE news SET comments = ( SELECT COUNT(id) FROM comments WHERE newsid = news.id GROUP BY newsid ); Hi. i came up with a similar query last night, but i didnt use group by. I have question, it it better to use COUNT(*) ? i thought there is extra mysql optimization when you use * thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 IMHO, the better solution is to INCREMENT your comment count *as you add each comment* . There will be much less SQL processing involved and it keeps your database in a consistent state. Doing things your way, you accumulate "uncounted" comments and your count will be wrong for the period between whole table recounts. If you update your static "comment count" statistic every time you add or delete a comment, you won't run into this problem. Plus, think of how often you would have to summarize (recount) your entire table just to register a few additions/deletions. How many processing cycles will you save by just setting the value to what it should be at the finish of the comment transaction? This is one of those time when a "dynamic" solution is too "heavy" for frequent use. Shawn Green Database Administrator Unimin Corporation - Spruce Pine It is not a bad idea to also DECREMENT the comment count each time a comment is deleted (if you do it), but that doesn't occur very often. This is implicit in your comment, but is not a bad idea to explicit it. I sort of agree with you guys but i think there are downsides to both methods.. if i do update the comment column when comments are added / deleted then that is an extra query each time someone posts. in an hour we can get several hundred comments, that also means several hundred db queries. at least i figured i can update these fields every 5 minutes or so and save some extra calls each time someone posts a comment. i guess i have to figure out which method would be best when dealing with a lot of traffic, but i think it will be neglible. right now that subquery runs in under .25 ms with 50,000 records to count.. not too bad i guess. You can see the number of updates, deletes, selects by performing a SHOW STATUS LIKE 'com_'; This shows how many queries of each types the MySQL server got since his last reboot. It can help you decide the best method, and the impact of making the extra query per insert/delete. If you update the count each _N_ seconds, the readers will see an old value and they could, for example, not post a reply, because they didn't saw the value being updated. I think that normally people prefer the first, or you could the value of _N_ be smaller (1 minute, for example). -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update delay
On an InnoDB table for 70k records the update action is taking so much time.(More than 30minutes). We got the innodb_buffer_pool_size as 4gigs. IS there anything more to add up to get the processes kick its speed. - javabuddy. People are conversing... without posting their email or filling up their mail box. ~~112352645~~ roomity.com http://roomity.com/launch.jsp No sign up to read or search this Rich Internet App -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query - select from one, update another
Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 01:19:32 PM: > Nuno Pereira wrote: > > > [EMAIL PROTECTED] wrote: > > > >> Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 > >> 10:53:55 AM: > >> > >> > >>> Gleb Paharenko wrote: > >>> > >>> > Hello. > > > > What about this: > > > > UPDATE news > > SET comments = > > ( > > SELECT COUNT(id) > > FROM comments > WHERE newsid = news.id > GROUP BY newsid > > ); > > >>> > >>> Hi. i came up with a similar query last night, but i didnt use group > >>> by. > >>> I have question, it it better to use COUNT(*) ? i thought there is > >>> extra > >> > >>> mysql optimization when you use * > >>> > >>> thanks. > >>> > >>> > >>> -- > >>> No virus found in this outgoing message. > >>> Checked by AVG Anti-Virus. > >>> Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 > >>> > >>> > >> > >> > >> IMHO, the better solution is to INCREMENT your comment count *as you > >> add each comment* . There will be much less SQL processing involved > >> and it keeps your database in a consistent state. > >> Doing things your way, you accumulate "uncounted" comments and your > >> count will be wrong for the period between whole table recounts. If > >> you update your static "comment count" statistic every time you add > >> or delete a comment, you won't run into this problem. > >> > >> Plus, think of how often you would have to summarize (recount) your > >> entire table just to register a few additions/deletions. How many > >> processing cycles will you save by just setting the value to what it > >> should be at the finish of the comment transaction? > >> This is one of those time when a "dynamic" solution is too "heavy" > >> for frequent use. > >> Shawn Green > >> Database Administrator > >> Unimin Corporation - Spruce Pine > >> > > > > It is not a bad idea to also DECREMENT the comment count each time a > > comment is deleted (if you do it), but that doesn't occur very often. > > This is implicit in your comment, but is not a bad idea to explicit it. > > > > I sort of agree with you guys but i think there are downsides to both > methods.. > if i do update the comment column when comments are added / deleted then > that is an extra query each time someone posts. in an hour we can get > several hundred comments, that also means several hundred db queries. > > at least i figured i can update these fields every 5 minutes or so and > save some extra calls each time someone posts a comment. i guess i have > to figure out which method would be best when dealing with a lot of > traffic, but i think it will be neglible. right now that subquery runs > in under .25 ms with 50,000 records to count.. not too bad i guess. > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 > > I agree that live testing is the only way to really know what works for your system. However, I still believe that even with 500 new records an hour, you will consume much less CPU and disk time with the individual updates (especially if you are using InnoDB) than with just one of your massive recount queries. Just as you said, you really need to try it both ways to see which method works best with your system and traffic loading. Please post your testing results when you get the chance. I love these kinds of db theory problems! Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
UTF8 support in MySQL 4.0
SET CHARACTER SET ut8; won't work. My server admin said UTF8 has not been mentioned in 4.0 thus I am unable to use it. So is there any solution on how I can properly use foreign characters and store them in a MySQL 4.0 database? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query - select from one, update another
Nuno Pereira wrote: [EMAIL PROTECTED] wrote: Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 10:53:55 AM: Gleb Paharenko wrote: Hello. What about this: UPDATE news SET comments = ( SELECT COUNT(id) FROM comments WHERE newsid = news.id GROUP BY newsid ); Hi. i came up with a similar query last night, but i didnt use group by. I have question, it it better to use COUNT(*) ? i thought there is extra mysql optimization when you use * thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 IMHO, the better solution is to INCREMENT your comment count *as you add each comment* . There will be much less SQL processing involved and it keeps your database in a consistent state. Doing things your way, you accumulate "uncounted" comments and your count will be wrong for the period between whole table recounts. If you update your static "comment count" statistic every time you add or delete a comment, you won't run into this problem. Plus, think of how often you would have to summarize (recount) your entire table just to register a few additions/deletions. How many processing cycles will you save by just setting the value to what it should be at the finish of the comment transaction? This is one of those time when a "dynamic" solution is too "heavy" for frequent use. Shawn Green Database Administrator Unimin Corporation - Spruce Pine It is not a bad idea to also DECREMENT the comment count each time a comment is deleted (if you do it), but that doesn't occur very often. This is implicit in your comment, but is not a bad idea to explicit it. I sort of agree with you guys but i think there are downsides to both methods.. if i do update the comment column when comments are added / deleted then that is an extra query each time someone posts. in an hour we can get several hundred comments, that also means several hundred db queries. at least i figured i can update these fields every 5 minutes or so and save some extra calls each time someone posts a comment. i guess i have to figure out which method would be best when dealing with a lot of traffic, but i think it will be neglible. right now that subquery runs in under .25 ms with 50,000 records to count.. not too bad i guess. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: explain not explaining long running query?
David Sparks <[EMAIL PROTECTED]> wrote on 08/08/2005 12:20:41 PM: > Hi all! > > Gleb Paharenko wrote: > > Hello. > > > > > > > >>I have a query that is taking days to complete (not good). If I change > > > > > > Really, not good. What does SHOW PROCESSLIST report about the thread of > > this query? > > The query has been running for ~5 days now: > > > Id: 27977 >User: root >Host: localhost > db: fractyl > Command: Query >Time: 421540 > State: Sending data >Info: select count(*) from msgs where message_id > 112000 and > message_id < 112111 > > > I also tried using the BETWEEN syntax but it has exactly the same problem. > > Any other ideas? > > ds > I am in no sense of the word a linux guru (I think I remember you saying this was runnin on a *nix box) but if we can detect no file activity and minimal CPU activity and minimal page swapping, can we assume that MySQL has gone into a 'coma' because something failed silently? IMHO, this is way too long for an indexed lookup. Would some of you *nix gurus out there suggest ways of how to detect what zombified his server (it is still running but doing no useful work). There should be some way of detecting if it has lost some memory handles or there is some sort of deadlock (mutex or semaphore contention) or that a vital subthread is somehow hung or dead Linux is way better at providing kernel/OS level information than windoze so I think he should be in good shape to get to the bottom of this. However, I think we can all agree that his server is kaput and it's time to do an autopsy to figure out why. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: explain not explaining long running query?
Hi all! Gleb Paharenko wrote: > Hello. > > > >>I have a query that is taking days to complete (not good). If I change > > > Really, not good. What does SHOW PROCESSLIST report about the thread of > this query? The query has been running for ~5 days now: Id: 27977 User: root Host: localhost db: fractyl Command: Query Time: 421540 State: Sending data Info: select count(*) from msgs where message_id > 112000 and message_id < 112111 I also tried using the BETWEEN syntax but it has exactly the same problem. Any other ideas? ds -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query - select from one, update another
[EMAIL PROTECTED] wrote: Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 10:53:55 AM: Gleb Paharenko wrote: Hello. What about this: UPDATE news SET comments = ( SELECT COUNT(id) FROM comments WHERE newsid = news.id GROUP BY newsid ); Hi. i came up with a similar query last night, but i didnt use group by. I have question, it it better to use COUNT(*) ? i thought there is extra mysql optimization when you use * thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 IMHO, the better solution is to INCREMENT your comment count *as you add each comment* . There will be much less SQL processing involved and it keeps your database in a consistent state. Doing things your way, you accumulate "uncounted" comments and your count will be wrong for the period between whole table recounts. If you update your static "comment count" statistic every time you add or delete a comment, you won't run into this problem. Plus, think of how often you would have to summarize (recount) your entire table just to register a few additions/deletions. How many processing cycles will you save by just setting the value to what it should be at the finish of the comment transaction? This is one of those time when a "dynamic" solution is too "heavy" for frequent use. Shawn Green Database Administrator Unimin Corporation - Spruce Pine It is not a bad idea to also DECREMENT the comment count each time a comment is deleted (if you do it), but that doesn't occur very often. This is implicit in your comment, but is not a bad idea to explicit it. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query - select from one, update another
[EMAIL PROTECTED] wrote: Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 10:53:55 AM: Gleb Paharenko wrote: Hello. What about this: UPDATE news SET comments = ( SELECT COUNT(id) FROM comments WHERE newsid = news.id GROUP BY newsid ); Hi. i came up with a similar query last night, but i didnt use group by. I have question, it it better to use COUNT(*) ? i thought there is extra mysql optimization when you use * thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 IMHO, the better solution is to INCREMENT your comment count *as you add each comment* . There will be much less SQL processing involved and it keeps your database in a consistent state. Doing things your way, you accumulate "uncounted" comments and your count will be wrong for the period between whole table recounts. If you update your static "comment count" statistic every time you add or delete a comment, you won't run into this problem. Plus, think of how often you would have to summarize (recount) your entire table just to register a few additions/deletions. How many processing cycles will you save by just setting the value to what it should be at the finish of the comment transaction? This is one of those time when a "dynamic" solution is too "heavy" for frequent use. Shawn Green Database Administrator Unimin Corporation - Spruce Pine It is not a bad idea to also DECREMENT the comment count each time a comment is deleted (if you do it), but that doesn't occur very often. This is implicit in your comment, but is not a bad idea to explicit it. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Query Browser Inline help
Shawn, Apology accepted. :) No hard feeling. Scott On 8/8/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > Scott Hamm <[EMAIL PROTECTED]> wrote on 08/08/2005 11:24:22 AM: > > > On 8/8/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > > > > Scott Hamm <[EMAIL PROTECTED]> wrote on 08/08/2005 08:11:18 AM: > > > > > Where can I put in my suggestion (I'm sure others suggested the same) > for > > > MySQL Query Browser? > > > > > > Scott > > > > > > > ROFLMAO -- you really DON'T check the website before asking the > > group do you (or read the manual, or check the archives, or )? I > > won't give you the direct link, that would be just too darn easy. > > > > http://dev.mysql.com/downloads/ > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > Shawn, > > > > I apologize for my simple mindness, English is my second language. > > As you should already notice that my writing is not in technical > > aspect. And what's more my reading skills is much lower (yeah, call > > me "low vocab" I don't care) and often had hard time trying to > > understand the jumbo bumbo on web sites. I often thought "bugs" > > *ONLY* apply to problems in program, not a suggestion or ideas. > > That is why I skipped bugs section trying to look for something that > > I can suggest. > > > > Scott > > > > Scott, > > I owe you a BIG apology. After re-reading your first message (and paying > closer attention to your SUBJECT line) I realize you weren't asking for the > "program" MySQL Query Browser but how to suggest an improvement to the > product MySQL already has. At my first glance, I thought you were asking > MySQL to develop a Query Browser. Obviously I am way off my game today and I > was way too harsh in my response to your question. Please accept my humble > apology. I am very sorry for jumping to the wrong conclusion. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html
Re: MySQL Query Browser Inline help
Scott Hamm <[EMAIL PROTECTED]> wrote on 08/08/2005 11:24:22 AM: > On 8/8/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > Scott Hamm <[EMAIL PROTECTED]> wrote on 08/08/2005 08:11:18 AM: > > > Where can I put in my suggestion (I'm sure others suggested the same) for > > MySQL Query Browser? > > > > Scott > > > > ROFLMAO -- you really DON'T check the website before asking the > group do you (or read the manual, or check the archives, or )? I > won't give you the direct link, that would be just too darn easy. > > http://dev.mysql.com/downloads/ > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > Shawn, > > I apologize for my simple mindness, English is my second language. > As you should already notice that my writing is not in technical > aspect. And what's more my reading skills is much lower (yeah, call > me "low vocab" I don't care) and often had hard time trying to > understand the jumbo bumbo on web sites. I often thought "bugs" > *ONLY* apply to problems in program, not a suggestion or ideas. > That is why I skipped bugs section trying to look for something that > I can suggest. > > Scott > Scott, I owe you a BIG apology. After re-reading your first message (and paying closer attention to your SUBJECT line) I realize you weren't asking for the "program" MySQL Query Browser but how to suggest an improvement to the product MySQL already has. At my first glance, I thought you were asking MySQL to develop a Query Browser. Obviously I am way off my game today and I was way too harsh in my response to your question. Please accept my humble apology. I am very sorry for jumping to the wrong conclusion. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Exporting a database from one PC to another using MySQL 5.0
Hi Eric, you can setup replication on your laptop (master) and your PC (slave). http://dev.mysql.com/doc/mysql/en/replication.html Best regards, Mikhail. - Original Message - From: "Eric Dahlenburg" <[EMAIL PROTECTED]> To: Sent: Monday, August 08, 2005 4:12 PM Subject: Exporting a database from one PC to another using MySQL 5.0 Hi, I am currently a student learning SQL. I have MySQL 5.0 installed at home and on my Laptop. How can I take a database that I have updated on my laptop and transfer it to my home PC so that they are both synchronized ? I tried looking on the forums for this info, but forums locks-up my Internet Explorer for some reason. Thanks, Eric Eric Dahlenburg Spacecoastsales.net [EMAIL PROTECTED] 321-453-7627 Voice/ Fax 321-917-9098 Cell This communication is intended solely for the use of the person(s) to whom it is addressed. This communication may contain confidential information or information otherwise subject to laws and regulations regarding its use, and any unauthorized use, dissemination, distribution or copying of this communication, or any portion thereof, may therefore be legally prohibited. If you are not the intended recipient of this communication you are not authorized to use, disseminate, distribute or copy this communication or any portion thereof, and are requested to notify the sender by return email and delete this communication from your system. -- 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 Query Browser Inline help
On 8/8/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > Scott Hamm <[EMAIL PROTECTED]> wrote on 08/08/2005 08:11:18 AM: > > > Where can I put in my suggestion (I'm sure others suggested the same) > for > > MySQL Query Browser? > > > > Scott > > > > ROFLMAO -- you really DON'T check the website before asking the group do > you (or read the manual, or check the archives, or )? I won't give you > the direct link, that would be just too darn easy. > > http://dev.mysql.com/downloads/ > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine Shawn, I apologize for my simple mindness, English is my second language. As you should already notice that my writing is not in technical aspect. And what's more my reading skills is much lower (yeah, call me "low vocab" I don't care) and often had hard time trying to understand the jumbo bumbo on web sites. I often thought "bugs" *ONLY* apply to problems in program, not a suggestion or ideas. That is why I skipped bugs section trying to look for something that I can suggest. Scott -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html
Re: MySQL Query Browser Inline help
Scott Hamm <[EMAIL PROTECTED]> wrote on 08/08/2005 08:11:18 AM: > Where can I put in my suggestion (I'm sure others suggested the same) for > MySQL Query Browser? > > Scott > ROFLMAO -- you really DON'T check the website before asking the group do you (or read the manual, or check the archives, or )? I won't give you the direct link, that would be just too darn easy. http://dev.mysql.com/downloads/ Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: query - select from one, update another
Sebastian <[EMAIL PROTECTED]> wrote on 08/08/2005 10:53:55 AM: > Gleb Paharenko wrote: > > >Hello. > > > > > > > >What about this: > > > > > > > >UPDATE news > > > >SET comments = > > > >( > > > > SELECT COUNT(id) > > > > FROM comments > > > > WHERE newsid = news.id > > > > GROUP BY newsid > > > >); > > > > > Hi. i came up with a similar query last night, but i didnt use group by. > I have question, it it better to use COUNT(*) ? i thought there is extra > mysql optimization when you use * > > thanks. > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 > > IMHO, the better solution is to INCREMENT your comment count *as you add each comment* . There will be much less SQL processing involved and it keeps your database in a consistent state. Doing things your way, you accumulate "uncounted" comments and your count will be wrong for the period between whole table recounts. If you update your static "comment count" statistic every time you add or delete a comment, you won't run into this problem. Plus, think of how often you would have to summarize (recount) your entire table just to register a few additions/deletions. How many processing cycles will you save by just setting the value to what it should be at the finish of the comment transaction? This is one of those time when a "dynamic" solution is too "heavy" for frequent use. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: query - select from one, update another
Gleb Paharenko wrote: Hello. What about this: UPDATE news SET comments = ( SELECT COUNT(id) FROM comments WHERE newsid = news.id GROUP BY newsid ); Hi. i came up with a similar query last night, but i didnt use group by. I have question, it it better to use COUNT(*) ? i thought there is extra mysql optimization when you use * thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.2/65 - Release Date: 8/7/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to use Like Clause in Perl? Works fine in MySQL control center!
Funny you should bring up this subject, as it was recently rehashed (more like re-re-re-re-hashed!) on perlmonks.org in this discussion thread: http://perlmonks.org/?node_id=481652 The conclusion of the discussion(s) is that there are valid reasons to use single and double quotes in various circumstances, and that the CPU difference due to possible variable interpolation is minor compared to other considerations. If performance is a problem for your app, your time would be better spent profiling your code and address real issues before addressing the non-issue of variable interpolation. JT > -Original Message- > From: Nuno Pereira [mailto:[EMAIL PROTECTED] > Sent: Monday, August 08, 2005 5:04 AM > To: mysql@lists.mysql.com > Subject: Re: How to use Like Clause in Perl? Works fine in > MySQL control center! > > Knowing not too much from perl, I would like to remember the last > paragraph of Jeremiah: > > > Keep in mind that interpolation is work, so using one of the > > single quotes > > strings which does not search your string for variables to > > replace is going > > to be higher performance than the double quoted version, > although the > > difference may be a little or a lot depending on how many > > times the string > > is interpreted (if it is in a loop or something). > > If performance is a problem, don't forget this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE WITH row
I think this should work: Update int_contxtd_details a, int.contxtd_details b Set a.params = b.params Where B.id=25 And a.id!=25 This will work in a php script where we cannot use more than one sql at the same time or also will work y a front end manager -Original Message- From: Nuno Pereira [mailto:[EMAIL PROTECTED] Sent: Friday, August 05, 2005 1:42 PM To: MySQL ML Subject: UPDATE WITH row Following the "SELECT DISTINCT" topic, I have another question. I tryed to UPDATE all the tables, replacing one column in every tables with the contents of one specific row. I tried this, but the syntax is incorrect: UPDATE int_contxtd_details SET params=(SELECT params FROM int_contxtd_details WHERE id=35) WHERE id!=35; Can anyone help? -- Nuno Pereira -- 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: query - select from one, update another
Hello.
No, it isn't. Here is the test case:
create table comments(id int auto_increment,newsid int,primary key(id));
create table news(id int,comments int);
insert into news(id) values('1'),(2),(3),(4),(5);
insert into comments(newsid) values(1),(1),(2),(4),(4),(4);
update news set comments = (select count(id) from comments where
newsid = news.id group by newsid);
mysql> select * from news;
+--+--+
| id | comments |
+--+--+
|1 |2 |
|2 |1 |
|3 | NULL |
|4 |3 |
|5 | NULL |
+--+--+
See:
http://dev.mysql.com/doc/mysql/en/correlated-subqueries.html
Enrique Sanchez Vela <[EMAIL PROTECTED]> wrote:
>
>
>
>
> --- Gleb Paharenko <[EMAIL PROTECTED]> wrote:
>
>> Hello.
>>
>> What about this:
>>
>> UPDATE news
>> SET comments =
>> (
>> SELECT COUNT(id)
>> FROM comments
>> WHERE newsid = news.id
>> GROUP BY newsid
>> );
>>
>>
>
> Isn't the previous query going to update the
> news.comments with the last count from the sub-query?
>
>
--
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: MySQL 5 and 'delimiter'
Hello. For a pity I'm not familiar with Deplhi. > > Thanks. So it seems that it's only a command line statement. > > Any idea why I get the error when executing the full CREATE > from a Delphi VCL component? Weird thing is, the procedure > is saved to the database. > > With regards, > > Martijn Tonies > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL > Server > Upscene Productions > http://www.upscene.com > Database development questions? Check the forum! > http://www.databasedevelopmentforum.com > > -- 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: MySQL Query Browser Inline help
Hello. Probably you want to make a feature request at: http://bugs.mysql.com >Where can I put in my suggestion (I'm sure others suggested the same) >for >MySQL Query Browser? Scott Hamm <[EMAIL PROTECTED]> 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]
Import problem
I used mysqldump with the -t option to only dump the raw data. I'm in the process of importing the dump to a new database with seed data loaded. I'm attempting to override the existing dataset but continue to get the following message when the import encounters a duplicate entry: Duplicate entry '0' for key 1. and bombs out each time. Ss there an option that I can add to allow the load to proceed. Thanks in advance. CL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exporting a database from one PC to another using MySQL 5.0
Hi, I am currently a student learning SQL. I have MySQL 5.0 installed at home and on my Laptop. How can I take a database that I have updated on my laptop and transfer it to my home PC so that they are both synchronized ? I tried looking on the forums for this info, but forums locks-up my Internet Explorer for some reason. Thanks, Eric Eric Dahlenburg Spacecoastsales.net [EMAIL PROTECTED] 321-453-7627 Voice/ Fax 321-917-9098 Cell This communication is intended solely for the use of the person(s) to whom it is addressed. This communication may contain confidential information or information otherwise subject to laws and regulations regarding its use, and any unauthorized use, dissemination, distribution or copying of this communication, or any portion thereof, may therefore be legally prohibited. If you are not the intended recipient of this communication you are not authorized to use, disseminate, distribute or copy this communication or any portion thereof, and are requested to notify the sender by return email and delete this communication from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: malloc troubles on 64-bit machine
On Mon, 1 Aug 2005, Joerg Bruehe wrote: > As a result, the allocation succeeds, but some process gets killed when > the paging space cannot take such an additional page. To the affected > process, this looks like a crash. Linux 2.4 and 2.6 kernels have a setting for their overcommitment behaviour under /proc/sys/vm/overcommit_memory. The different settings are: 0 - Heuristic overcommit handling. Obvious overcommits of address space are refused. Used for a typical system. It ensures a seriously wild allocation fails while allowing overcommit to reduce swap usage. root is allowed to allocate slighly more memory in this mode. This is the default. 1 - Always overcommit. Appropriate for some scientific applications. 2 - Don't overcommit. The total address space commit for the system is not permitted to exceed swap + a configurable percentage (default is 50) of physical RAM. Depending on the percentage you use, in most situations this means a process will not be killed while accessing pages but will receive errors on memory allocation as appropriate. Heuristic overcommit handling seems to be the default, and my problem is in the 'Obvious overcommits of address space are refused'. For some (to me unknown) reason the kernel looks at a single 7GB malloc as if it be an 'obvious overcommit' while 100 2GB mallocs (200GB total) is no problem. :P For now I've set this to '2' which means the kernel won't overcommit anymore, just like any other proper OS... ;-) This makes things far more simple as I can only allocate as much memory as there is physically available now. However it does force me to be a bit more conservative. I have configured InnoDB with a 4GB buffer pool now, which leaves about 3GB for connections (about 300 with my current MySQL settings). Now this seems reasonable. One final question though: my experience with InnoDB is that it really, really likes to be able to fit all of it's data and keys into the buffer pool. This would limit the maximum size of my database to roughly 4GB in this case, correct? This is in a website hosting environment where the database is hit with about 1000 queries/s (mixed read/write). > I am a bit surprised that the Linux kernel management will only allocate > memory if a single chunk of sufficient size is available. My > understanding was that in a paging system this is not necessary. > > If this is (becoming) standard Linux policy, it might be necessary to > demand memory piecewise. One drawback of this approach is increased > bookeeping, if it ever needs to be released. > > I have no idea how the developers view this issue - you might open a > change request if you consider this Linux kernel policy definite. > > You wrote that if a mysql server start fails, you can run "fillmem", and > after its exit the memory will be available. I am not sure whether > Rick's explanation addresses this issue as well - it might be the > "memory defragger" he refers to. If not, the once used chunks might > still be considered "active". I think it all refers to the IMHO buggy (hey, even the manpages state it!) VM memory allocation scheme. As stated I have disabled the overcommitment behaviour for now, which seems to fit better to a dedicated database server. Best regards, -- Matthijs van der Klip Systeembeheerder Spill E-Projects BV Arendstraat 1-3 1223 RE Hilversum Tel. 035-6478248 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query - select from one, update another
--- Gleb Paharenko <[EMAIL PROTECTED]> wrote: > Hello. > > What about this: > > UPDATE news > SET comments = > ( > SELECT COUNT(id) > FROM comments > WHERE newsid = news.id > GROUP BY newsid > ); > > Isn't the previous query going to update the news.comments with the last count from the sub-query? Enrique Sanchez Vela email: [EMAIL PROTECTED] - It's often easier to fight for one's||We live in the outer space principles than to live up to them||Rev. Kay Greenleaf Adlai Stevenson|| __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Query Browser Inline help
Where can I put in my suggestion (I'm sure others suggested the same) for MySQL Query Browser? Scott -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html
Re: Undefined variable: authed
Trevor Tregoweth wrote: Hi There I am trying to run a simple password /php / mysql script for a web page and get the following errors, it works fine on a earlier versions of php / mysql PHP Notice: Undefined variable: help_out_uid in /var/www/html/lcc/secure/secure.php on line 87, PHP Notice: Undefined variable: authed in /var/www/html/lcc/secure/secure.php on line 34 mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386) PHP 4.3.9 (cgi) (built: Oct 20 2004 14:52:04) This is not a MySQL problem, it is a PHP problem. Are those variables containing the values from a form? If that is the case, it seems to me that you were using a 4.0 PHP version. Try to initialize the variables like this: $help_out=$_REQUEST['help_out']; $authed=$_REQUEST['authed']; Thanks for you help Trevor -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 and 'delimiter'
> > Comments from: > > http://bugs.mysql.com/bug.php?id=12344 > > > > might be helpful. > > Thanks. So it seems that it's only a command line statement. > > Any idea why I get the error when executing the full CREATE > from a Delphi VCL component? Weird thing is, the procedure > is saved to the database. Never mind ... found it :-) With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 and 'delimiter'
Hello Gleb, > Hello. > > > Comments from: > http://bugs.mysql.com/bug.php?id=12344 > > might be helpful. Thanks. So it seems that it's only a command line statement. Any idea why I get the error when executing the full CREATE from a Delphi VCL component? Weird thing is, the procedure is saved to the database. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com > > I'm a bit puzzled about the DELIMITER statement. > > > > Is this a command-line SQL statement only, or is this actually processed > > on the server? > > > > And if it's a command-line thingy only, shouldn't I be able to execute > > something > > like: > > CREATE PROCEDURE P_AA() > > MODIFIES SQL DATA > > begin > > delete from v_test; > > delete from v_test; > > end > > > > Which, currently, returns an error like this: > > mySQL Error Code: (1064) > > You have an error in your SQL syntax; check the manual that corresponds to > > your MySQL server version for the right syntax to use near '; > > CREATE PROCEDURE P_AA() > > MODIFIES SQL DATA > > begin > > delete f > > > > > > Any idea? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing and starting with Linux
Hello, Can help me someone with the installation. I tried to install the MySQL-server-4.1.13-0.i386.rpm on Suse Linux 9.3. logged in as root in Linux with rpm -i MySQL-server-4.1.13-0.i386.rpm I got messages chown: 'mysql:mysql': invalid user chown: 'mysql': invalid user [Warning] Asked for 196608 thread stack, but got 126976 [ERROR] Fatal error: Can't change to run as user 'mysql' [ERROR] Aborting [Note] /usr/sbin/mysqld: Shutdown complete. So I tried to make it with mysql_install_db mysql_install_db --user=server(server is the normal user on the Linux-PC) It got messages Installing all prepared tables [Warning] Asked for 196608 thread stack, but got 126976 Fill help tables [Warning] Asked for 196608 thread stack, but got 126976 Then I logged in as "server" on the Linux-PC Looking into the Yast Runlevel-Editor I find mysql but not started. When I try with the console to start Mysql manual with mysql_safe. It got message line 311: /var lib/mysql/server.err: Keine Berechtigung(no authorisation) line 317: /var lib/mysql/server.err: Keine Berechtigung(no authorisation) STOPPING server from pid file /var/lib/mysql/server.pid I try to find something in the Reference manual but couldn't find how to solve the problem. What can I do that I can start mysql? Where can I search form problems? Best Regards, Georg Salvenmoser. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 and 'delimiter'
Hello. Comments from: http://bugs.mysql.com/bug.php?id=12344 might be helpful. "Martijn Tonies" <[EMAIL PROTECTED]> wrote: > Hi there, > > I'm a bit puzzled about the DELIMITER statement. > > Is this a command-line SQL statement only, or is this actually processed > on the server? > > And if it's a command-line thingy only, shouldn't I be able to execute > something > like: > CREATE PROCEDURE P_AA() > MODIFIES SQL DATA > begin > delete from v_test; > delete from v_test; > end > > Which, currently, returns an error like this: > mySQL Error Code: (1064) > You have an error in your SQL syntax; check the manual that corresponds to > your MySQL server version for the right syntax to use near '; > CREATE PROCEDURE P_AA() > MODIFIES SQL DATA > begin > delete f > > > Any idea? > > > With regards, > > Martijn Tonies > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL > Server > Upscene Productions > http://www.upscene.com > Database development questions? Check the forum! > http://www.databasedevelopmentforum.com > > -- 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]
TimeZone
Hi, There is a request from mgmt, when user browse the request (web), the database will return the requested timestamp at their timezone that previously posted. How do i do that? Do I need store whole country codes together timezones in database mysql ? Is there any way to find full country code together with their timezone? Cheers KH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use Like Clause in Perl? Works fine in MySQL control center!
Knowing not too much from perl, I would like to remember the last
paragraph of Jeremiah:
> Keep in mind that interpolation is work, so using one of the
> single quotes
> strings which does not search your string for variables to
> replace is going
> to be higher performance than the double quoted version, although the
> difference may be a little or a lot depending on how many
> times the string
> is interpreted (if it is in a loop or something).
If performance is a problem, don't forget this.
John Trammell wrote:
From 'perldoc perldata':
Variable substitution inside strings is limited to scalar
variables, arrays, and array or hash slices. (In other
words, names beginning with $ or @, followed by an optional
bracketed expression as a subscript.)
You can check this from the command line:
% perl -le 'print "$s -- @s -- %s"'
-- -- %s
So the '%' isn't the issue here. The issue is certainly the (mis)use of
join(), as was pointed out by a previous poster.
-Original Message-
From: Jeremiah Gowdy [mailto:[EMAIL PROTECTED]
Sent: Monday, July 25, 2005 9:14 AM
To: Siegfried Heintze; mysql@lists.mysql.com
Subject: Re: How to use Like Clause in Perl? Works fine in
MySQL control center!
When you use double quotes for strings in Perl, Perl looks
through your
strings for variables like $foo, and replaces them with the
current value of
$foo. This is called interpolation. When you use single quotes, it
considers your string a literal.
So when you use double quotes, you need to escape any special
characters
like $ % " or @. When you use single quotes, the only
character you have to
worry about is '. Here are ways you could make this string work.
Double quotes with special characters escaped (due to interpolation)
"SELECT 'David!' LIKE '\%D\%v\%'"
Single quotes with double quote usage for the SQL quoting (no
escaping
required)
'SELECT "David!" LIKE "%D%v%"'
Single quotes with single quotes escaped for the SQL quoting
'SELECT \'David!\' LIKE \'%D%v%\''
Keep in mind that interpolation is work, so using one of the
single quotes
strings which does not search your string for variables to
replace is going
to be higher performance than the double quoted version, although the
difference may be a little or a lot depending on how many
times the string
is interpreted (if it is in a loop or something).
- Original Message -
From: "Siegfried Heintze" <[EMAIL PROTECTED]>
To:
Sent: Friday, July 22, 2005 4:03 PM
Subject: How to use Like Clause in Perl? Works fine in MySQL
control center!
I'm having trouble getting the like clause to work. It
seems to work fine
in
the MySQL Control Center 9.4.beta. I'm using MySQL 4.0.23-debug.
use DBH;
my $sth = DBH->prepare("SELECT 'David!' LIKE '%D%v%'");
$sth->execute();
my $row;
print join(@$row,",")."\n" while ($row = $sth->fetch);
This does not print a "1" in perl. It just prints a ",".
I've posted a query on this in [EMAIL PROTECTED] with no luck.
Anybody have any suggestions?
Thanks,
Siegfried
--
Nuno Pereira
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help table's locked/missing?
Hello. Use SHOW PROCESSLIST to see what's going on in your database. Fajar Priyanto <[EMAIL PROTECTED]> wrote: > Hi all, > I'm hosting my web on a provider with MySQL 4.0.25-standard. > > I've got this situation when I open my database using phpMyadmin, one of my > table has status is "in use". > > And then when I try to repair the database, MySQL just hung there while > consumed around 50% of CPU. And then when I tried to export the database, > phpMyadmin got stuck at that tables, with a status of SHOW TABLE STATUS FROM > 'thetroubledtable'. > > I tried to contact the hosting provider, but it seems they're having weekend > break. > > Is there any way that I or my hosting provider can do about it? Maybe > restarting the mysql server is enough? Or can the table just being deleted? > Thanks, -- 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: Daily maintenance for mysql
Hello. Have a look here: http://dev.mysql.com/doc/mysql/en/mysqldump.html JM <[EMAIL PROTECTED]> wrote: > Hi, > >Im still new to mysql and Im the processes of reading the manual. > Since I > need a quick answer I need inputs on things to do to maintain the DB. Is > there a similar pg_dump thing? or it doesn't need it? > > tia, > -- 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: MySQL 4.0.23a defunct
Hello. I think that mysqld zombies appear due to death of some MySQL threads, but we don't see any sensible records about it in the error log. I suggest you to switch to official binaries of the latest release from: http://dev.mysql.com/downloads and check if the weird behavior still exists. Arek H <[EMAIL PROTECTED]> wrote: > Gleb Paharenko wrote: > >>Hello. >> >> >> >> >> >>Are there any error messages and stack trace before 'Memory status' >> >>line in the error log? >> >> >> >> >> >> >> > > > This is what it shows > > Status information: > > Current dir: /var/lib/mysql/ > Running threads: 0 Stack size: 196608 > Current locks: > key_cache status: > blocks used:11 > not flushed: 0 > w_requests: 0 > writes: 0 > r_requests: 0 > reads: 0 > > handler status: > read_key:0 > read_next: 0 > read_rnd 0 > read_first: 0 > write: 0 > delete 0 > update: 0 > > Table status: > Opened tables: 0 > Open tables:0 > Open files: 0 > Open streams: 0 > > Alarm status: > Active alarms: 0 > Max used alarms: 1 > Next alarm time: 0 > 050731 15:57:09 /usr/libexec/mysqld: Normal shutdown > > > Regards > Arek > -- 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: myodbc driver araise full table scan
Hello. Please, provide more information about your software and oprating system. Include versions of MySQL and MyODBC. See: http://dev.mysql.com/doc/mysql/en/bug-reports.html http://dev.mysql.com/doc/mysql/en/myodbc-bug-report.html "chenwei" <[EMAIL PROTECTED]> wrote: >i use myodbc to connect to a big table, to my surprise, myodbc araise >full table scan and crash. could anybody help me with that? -- 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: Implication and usage of character sets for i18n data (?)
Hello. I can't make guessing about Hebrew code page, but MySQL support of character sets was very helpful with my Russian cp1251 encoding. Because windows console supports input on my system only in cp866, while other applications - cp1251. So in mysql command line client I was able to dynamically convert characters from cp866 into cp1251 using character_set_xxx variables. Some client on Unixes have koi8-r encoding, while windows clients - cp1251. In this case MySQL character sets support is very helpful as well. Another point - my Fedora distribution uses utf8 encoding as system character set. And utf8 in MySQL helps a lot. More good examples could give people whose databases have data in different languages. Maxim Vexler <[EMAIL PROTECTED]> wrote: >Hello to each and every one of you. > >I'm wondering about the practical effect of character sets in mysql 4.0 & >4.1 as follows. > >What is it good for? Assuming I'm storing text data uin windows-hebrew >(1255) codepage, to be used by my php application. I can have my database & >table to be defined as 'latin1' and it all works just the same as if I would >define the table to have 'hebrew' characterset. >After all, up until the point of using Unicode for storing text data, all >strings are single byte ASCII encoded, and all the db does it to store this >data (assuming I'm not using any collation specific sorting and such...). >I'm a bit confused about the subject and would appreciate any discussion or >a link to some article(s) that would be able to sharpen this for me. > >Why not does mysql even has different charactersets? >Where does it comes into effect for me, as an application developer relaying >on mysql (or similar database software) for the storage of my data? > > -- 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: query - select from one, update another
Hello. What about this: UPDATE news SET comments = ( SELECT COUNT(id) FROM comments WHERE newsid = news.id GROUP BY newsid ); Sebastian <[EMAIL PROTECTED]> wrote: > I have two tables: news and comments. > > i want to count the number of comments for each newsid and update the > count in news.comment > > comments.newsid belongs to news.id > > can i do this with sub queries? im using v4.1.x > i have many records in the comments table so im not sure what the most > efficient way to do it. > > i will likely run this as update every few minutes because doing a join > on the fly is going to hurt performance when i need to display the > comment count on the webpage i think. > > any help? > > > table: news > ++---+ > | id | comments | > ++---+ > | 26 | 0 | > | 21 | 0 | > | 29 | 0 | > +--- > > > table: comments > +-++ > | id | newsid | > +-++ > | 1 |26 | > | 2 |21 | > | 3 |29 | > | 4 |29 | > | 5 |29 | > +-+ > > > -- 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: Alter table type/insert_method question
Hello. > http://dev.mysql.com/doc/mysql/en/alter-table.html document, but I > don't see any examples of altering table types, just a lot of other The syntax for many of the allowable alterations is similar to clauses of the CREATE TABLE statement. This includes table_options modifications, for options such as ENGINE. For example: ALTER TABLE `BRANCH_mod_user_groups` ENGINE=MyISAM; Verdon Vaillancourt <[EMAIL PROTECTED]> wrote: > Hi :) > > I have a table that is created like such... > > CREATE TABLE `BRANCH_mod_user_groups` ( > `group_id` int(11) NOT NULL default '0', > `group_name` varchar(30) default NULL, > `description` text, > `members` text, > PRIMARY KEY (`group_id`) > ) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(HUB_mod_user_groups); > > I'd like to alter it so it is not a merge table, run some other > commands on it, and then alter it back to TYPE=MRG_MyISAM > INSERT_METHOD=FIRST UNION=(HUB_mod_user_groups) > > Is this do-able? I suspect so from perusing this > http://dev.mysql.com/doc/mysql/en/alter-table.html document, but I > don't see any examples of altering table types, just a lot of other > stuff ;) Maybe I'm missing something. > > Thanks, > verdon > > -- 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]
MySQL 5 and 'delimiter'
Hi there, I'm a bit puzzled about the DELIMITER statement. Is this a command-line SQL statement only, or is this actually processed on the server? And if it's a command-line thingy only, shouldn't I be able to execute something like: CREATE PROCEDURE P_AA() MODIFIES SQL DATA begin delete from v_test; delete from v_test; end Which, currently, returns an error like this: mySQL Error Code: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; CREATE PROCEDURE P_AA() MODIFIES SQL DATA begin delete f Any idea? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help table's locked/missing?
Hi all, I'm hosting my web on a provider with MySQL 4.0.25-standard. I've got this situation when I open my database using phpMyadmin, one of my table has status is "in use". And then when I try to repair the database, MySQL just hung there while consumed around 50% of CPU. And then when I tried to export the database, phpMyadmin got stuck at that tables, with a status of SHOW TABLE STATUS FROM 'thetroubledtable'. I tried to contact the hosting provider, but it seems they're having weekend break. Is there any way that I or my hosting provider can do about it? Maybe restarting the mysql server is enough? Or can the table just being deleted? Thanks, -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux2.arinet.org 15:33:22 up 3:09, Mandrakelinux release 10.2 (Limited Edition 2005) for i586 public key: https://www.arinet.org/fajar-pub.key -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling mysql on Itanium2 with Intel 9.0
Hello All, I am having some trouble getting a good compile of mysql on a Itanium2 based system using the Intel 9.0 compilers. I can get it to compile ok. But when I run "make test" it will eventually fail with an error about being unable to contact the server. This happens pretty far in, when it is testing the rplxxx stuff. Would anyone have a set of compiler flags and configure statement that they could recomend? Cheers, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myodbc driver araise full table scan
mysql，您好！ i use myodbc to connect to a big table, to my surprise, myodbc araise full table scan and crash. could anybody help me with that? 致 礼！ chenwei [EMAIL PROTECTED] 2005-08-08
