mysqlbinlog problem
Hello, I have hab problem with mysqlbinlog. I'm testing point in time recovery. The Server Version of MySQL is 5.0.22. Statement: ./mysqlbinlog --stop-date=2006-12-29 08:40:00 logfile | mysql -u user -p The error: ERROR at line 12: Unknown command '\C'. By piping the output in a file line 12 looks like: /*!\C latin1 */; Where is the fault? regards Spiker -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax
Hi Scott, all, Scott Yamahata wrote: Hi, I'm getting the following error message: 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 ' enabled = '1'' at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. The error in your statement is that it does not provide a value for the countryid field. I suspect this whole statement was generated by your application, but the input data did not contain a value for this field. In such a case, your statement must not contain the countryid = part, and the field will receive its default value. Alternatively, you can explicitly set that field to NULL (provided your create table allows NULL for it). Then, there is an error in your mail: You intended to start a new thread, but your mail contains an in-reply-to header which makes it part of another thread. Probably you used some reply function where write (new) was appropriate. I assume you did not intentionally hijack that other thread, but it does make reading more difficult than necessary. Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count the number of specific rows
Hi, Well this question may seem funny, but I really need to know! The problem is with counting a user's contribution in my site. Suppose that each user that send an email will be stored in DB. Now I want to count number of times that a user has sent an email. I can simply use this : SELECT id FROM sent WHERE member_id= ... and the use count($result) to count the number, but I want a faster way, if possible. Thanks PS : This is *MySQL 4.0.x* -- Warm Regards, Amir Mirghassemi
Re: Count the number of specific rows
On Friday 29 December 2006 14:02, Servers24 Network wrote: Hi, Well this question may seem funny, but I really need to know! The problem is with counting a user's contribution in my site. Suppose that each user that send an email will be stored in DB. Now I want to count number of times that a user has sent an email. I can simply use this : SELECT id FROM sent WHERE member_id= ... and the use count($result) to count the number, but I want a faster way, if possible. select count(id) from sent. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count the number of specific rows
Servers24, Well this question may seem funny... No, a funny question would start something like Why did the nun cross the road?. ;^) The problem is with counting a user's contribution in my site. Suppose that each user that send an email will be stored in DB. Now I want to count number of times that a user has sent an email. I can simply use this : SELECT id FROM sent WHERE member_id= ... and the use count($result) to count the number, but I want a faster way, if Get MySQL to do the counting... SELECT COUNT(*) FROM sent WHERE member_id = ...; ...saying COUNT(*) is certainly no slower than saying COUNT(id) and the COUNT function is certainly faster than iterating over the result set using whatever language, also make sure you have indexed the member_id field as well. Alternatively you maybe able to incorporate a **|SQL_CALC_FOUND_ROWS |**into an existing query and then do a **| |**|FOUND_ROWS()| to optimize things (see http://dev.mysql.com/doc/refman/4.1/en/information-functions.html) or failing that run a nightly query to compile the stats into a new table containing just member_id and emails_contributed fields? Those would be the standard options. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count the number of specific rows
I can simply use this : SELECT id FROM sent WHERE member_id= ... and the use count($result) to count the number, but I want a faster way, if possible. SELECT COUNT(id) FROM sent WHERE member_id= ... PB Servers24 Network wrote: Hi, Well this question may seem funny, but I really need to know! The problem is with counting a user's contribution in my site. Suppose that each user that send an email will be stored in DB. Now I want to count number of times that a user has sent an email. I can simply use this : SELECT id FROM sent WHERE member_id= ... and the use count($result) to count the number, but I want a faster way, if possible. Thanks PS : This is *MySQL 4.0.x* No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.29/607 - Release Date: 12/28/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.29/607 - Release Date: 12/28/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max size and row numbers
Thanks for the detailed answer. So basically the limitations come from the OS and the file system used. What is the best file system to use for mysql (not considering the filesize limitations)? Thanks Olaf On 12/29/06 2:25 AM, ViSolve DB Team [EMAIL PROTECTED] wrote: Hi, The maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. If you need a MyISAM table that is larger than 4GB in size (and your operating system supports large files), the CREATE TABLE statement allows AVG_ROW_LENGTH and MAX_ROWS options. The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64TB. On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the Large File Support (LFS) patch for the ext2 filesystem. On Linux 2.4, patches also exist for ReiserFS to get support for big files (up to 2TB). However, the maximum available file size still depends on several factors, one of them being the filesystem used to store MySQL tables. You can check the maximum table size for a table with the SHOW TABLE STATUS statement or with myisamchk -dv tbl_name. If your large table is read-only, you can use myisampack to compress it. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. Thanks Visolve DB Team - Original Message - From: Olaf Stein [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 29, 2006 4:14 AM Subject: Max size and row numbers Hey everyone I have more of a general question regarding your experience with large tables. I currently have a table (MyISAM, 6 columns, lots of reading access, some writing) with about 70.000.000 records, using 2.5GB of diskspace. I am running MySQL 5.0.* on a RedHat Enterprise AS 4 system (2 CPU, 8GB Ram). I just read that the max size for a table is 256TB in a default installation. I have basically no experience with tables that big and mine is potentially growing to several hundred million records. First of all, are there theoretical limitations (if the 256TB are correct I would be fine with that I guess) in size and number of records? More importantly, what are the practical limitations and/or pitfalls? Is ext3 as filesystem a limiting factor? If you have experience or know of good links regarding this topic, please let me know Thanks in advance Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL export to csv
Can anyone tell me how to export a mysql db to a csv file suitable for my client to import into his windows access program. -- Regards, Alf Stocktonwww.stockton.co.za The abuse of greatness is when it disjoins remorse from power. -- William Shakespeare, Julius Caesar My email disclaimer is available at www.stockton.co.za/disclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL export to csv
If there's only a couple of tables you could just export to CSV per table (phpmyadmin makes this easy). Simple for the Access user to import. Select... into outfile... is fine. See: http://dev.mysql.com/doc/refman/5.1/en/select.html D. Eric Chadbourne Data Analyst Alf Stockton [EMAIL PROTECTED] 12/29/2006 10:42 AM To: mysql@lists.mysql.com cc: bcc: Subject:MySQL export to csv Can anyone tell me how to export a mysql db to a csv file suitable for my client to import into his windows access program. -- Regards, Alf Stocktonwww.stockton.co.za The abuse of greatness is when it disjoins remorse from power. -- William Shakespeare, Julius Caesar My email disclaimer is available at www.stockton.co.za/disclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] This message (including any attachments) contains information that may be confidential to Koninklijke Ahold N.V. or its group entities. If you are the intended recipient, you are authorized to use the information only as expressly authorized by Ahold and must strictly adhere to applicable security procedures, including without limitation the following: - You must protect files using the password and level of protection in which the file was provided to you; and - You may transmit files only as necessary for the task you are assigned using secure systems and appropriate encryption means. If you have received the message in error, please advise the sender by reply e-mail, and destroy all copies of the original message (including any attachments). Please direct any questions to the sender of this message.
Re: Max size and row numbers
Olaf, Thanks for the detailed answer. So basically the limitations come from the OS and the file system used. What is the best file system to use for mysql (not considering the filesize limitations)? Thanks Olaf The best is probably ZFS if you really are intent on make things huge, http://en.wikipedia.org/wiki/Zettabyte_File_System but check out the very useful http://en.wikipedia.org/wiki/Comparison_of_file_systems page as well. Dunno about speed-wise, but you'd end up in an argument centering around RAID probably? Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL export to csv
Dwight E Chadbourne wrote: If there's only a couple of tables you could just export to CSV per table (phpmyadmin makes this easy). Simple for the Access user to import. Great. In fact there is only one table but I cannot see where in phpmyadmin the export is done..? -- Regards, Alf Stocktonwww.stockton.co.za All things that are, are with more spirit chased than enjoyed. -- Shakespeare, Merchant of Venice My email disclaimer is available at www.stockton.co.za/disclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL export to csv
Hi Alf, SELECT INTO OUTFILE 'file_name' [export_options] FROM yourtable could of agreat help in your situation Regards, Mikhail Berman -Original Message- From: Alf Stockton [mailto:[EMAIL PROTECTED] Sent: Friday, December 29, 2006 11:34 AM To: mysql@lists.mysql.com Subject: Re: MySQL export to csv Dwight E Chadbourne wrote: If there's only a couple of tables you could just export to CSV per table (phpmyadmin makes this easy). Simple for the Access user to import. Great. In fact there is only one table but I cannot see where in phpmyadmin the export is done..? -- Regards, Alf Stocktonwww.stockton.co.za All things that are, are with more spirit chased than enjoyed. -- Shakespeare, Merchant of Venice My email disclaimer is available at www.stockton.co.za/disclaimer.html -- 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: Count the number of specific rows
Servers24, Hi Philip, Thank you very much for your help. Can you please tell me the differemce between COUNT(*) and COUNT(id) ? Thanks again. Actually sorry I was a bit misleading there. MySQL is optimized to calculate... SELECT COUNT(*) FROM aTable; ...but given the fact you've got a where condition that optimizations probably no longer applicable, so I'd stick with... SELECT COUNT(id) FROM sent WHERE member_id = ...; ...the real difference would be when you do a SELECT COUNT(DISTINCT id) instead of a SELECT COUNT(DISTINCT *) which are different questions but not relevant to your situation. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JSP/JSTL problem
Hi I have created a table with the following specs: create table `cust` ( `ssn` int(9) NOT NULL PRIMARY KEY, `submitdate` date, `submitto` int(3), `first` varchar(30), `last` varchar(30), `loanAmt` decimal(10,2), `company` int(3), `fee` int(3), `appType` int(3), `appSource` int(3), `appStatus` int(3), `dateStatus` date, `fundedAmt` decimal(10,2) ) When I try to do select (first, last) where ssn=notthere I get a Result Set that is non null even though the ssn is not in the table. My logic is as follows: a) if ssn is found returnsql:connection id=conn1 sql:urljdbc:mysql://trig2:3306/appsubmit?user=bsteinpassword=siket281/sql:url sql:drivercom.mysql.jdbc.Driver/sql:driver /sql:connection !-- jsp:getProperty name=conn1 property=closed/ -- sql:preparedStatement id=stmt conn=conn1 sql:query select first, last from cust where ssn=? /sql:query sql:setColumn position=1%= ssn %/sql:setColumn sql:resultSet id=rset sql:getColumn colName=first to=fname / sql:getColumn colName=last to =lname/ /sql:resultSet sql:wasEmpty sql:preparedStatement id=stmt2 conn=conn1 Applicant successfully added sql:query insert into cust (ssn, submitdate, submitto, first, last, loanAmt, company, fee, appType, appSource) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) /sql:query sql:execute sql:setColumn position=1 %= Integer.parseInt(ssn) % /sql:setColumn sql:setColumn position=2 %= + - + mm + - + dd % /sql:setColumn sql:setColumn position=3 %= Integer.parseInt(finance) % /sql:setColumn sql:setColumn position=4 %= first % /sql:setColumn sql:setColumn position=5 %= last % /sql:setCosql:setColumn position=9 %= Integer.parseInt(apptype) %/sql:setColumn sql:setColumn position=10 %= Integer.parseInt(appsource) %/sql:setColumn /sql:execute /sql:preparedStatement /sql:wasEmpty sql:wasNotEmpty Applicant already present/sql:wasNotEmpty /sql:preparedStatement sql:closeConnection conn=conn1/ lumn sql:setColumn position=6 %= Float.parseFloat(loanAmt) %/sql:setColumn sql:setColumn position=7 %= Integer.parseInt(company) %/sql:setColumn sql:setColumn position=8 %= Integer.parseInt(fee) %/sql:setColumn b) if ssn is not found then insert I am posting the relevant JSTL code. I'd appreciate your kind comments. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[patch] Typo in the french translation
Hi, Here is a patch correcting a translation error: the french word for connection is connexion. I dont want open a bugzilla account just for this, so i post it on this ml. This patch is a diff from the file mysql-5.1.14-beta/sql/share/errmsg.txt. Cheers, -- Vincent http://www.magicninja.org/public.asc Fingerprint: C8E2 4129 1D57 6B01 1247 5B35 50F0 550F C144 FC59 953c953 fre Trop de connections --- fre Trop de connexions 1830c1830 fre %s: Prêt pour des connections --- fre %s: Prêt pour des connexions 1953c1953 fre Ne peut créer la connection IP (socket) --- fre Ne peut créer la connexion IP (socket) 3034c3034 fre L'hôte '%-.64s' est bloqué à cause d'un trop grand nombre d'erreur de connection. Débloquer le par 'mysqladmin flush-hosts' --- fre L'hôte '%-.64s' est bloqué à cause d'un trop grand nombre d'erreur de connexion. Débloquer le par 'mysqladmin flush-hosts' 3536c3536 fre Erreur de lecture reçue du pipe de connection --- fre Erreur de lecture reçue du pipe de connexion 4334c4334 fre L'utilisateur %-.64s possède déjà plus de 'max_user_connections' connections actives --- fre L'utilisateur %-.64s possède déjà plus de 'max_user_connections' connexions actives pgpy2vuPOl8JM.pgp Description: PGP signature
Re: Max size and row numbers
Olaf, That's a very broad question depending upon your exact requirements to be honest. So long as you've considered file size limitations and you've opted for a decent RAID system there's not too much more to worry about, try and buy the fastest disks possible obviously i.e. 15,000 RPM ones that are specifically designed for RAID setups etc... Regards, Phil There is a RAID now an there will definitely one in the new setup. As far as the max file sizes from the file systems go I am not worried after all I read. Thanks for those links btw. Besides the size though, what should I pay attention to when selecting the file system Thanks Olaf On 12/29/06 11:31 AM, Philip Mather [EMAIL PROTECTED] wrote: Olaf, Thanks for the detailed answer. So basically the limitations come from the OS and the file system used. What is the best file system to use for mysql (not considering the filesize limitations)? Thanks Olaf The best is probably ZFS if you really are intent on make things huge, http://en.wikipedia.org/wiki/Zettabyte_File_System but check out the very useful http://en.wikipedia.org/wiki/Comparison_of_file_systems page as well. Dunno about speed-wise, but you'd end up in an argument centering around RAID probably? Regards, Phil - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL export to csv
Oh it's easy. Select database, export, select table, select csv, check save as file, go. I'm using an older version but I'd bet it hasn't changed much. You'll figure it out. D. Eric Chadbourne My play site: http://www.NewMAG.org/ Alf Stockton [EMAIL PROTECTED] 12/29/2006 11:33 AM To: mysql@lists.mysql.com cc: bcc: Subject:Re: MySQL export to csv Dwight E Chadbourne wrote: If there's only a couple of tables you could just export to CSV per table (phpmyadmin makes this easy). Simple for the Access user to import. Great. In fact there is only one table but I cannot see where in phpmyadmin the export is done..? -- Regards, Alf Stocktonwww.stockton.co.za All things that are, are with more spirit chased than enjoyed. -- Shakespeare, Merchant of Venice My email disclaimer is available at www.stockton.co.za/disclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] This message (including any attachments) contains information that may be confidential to Koninklijke Ahold N.V. or its group entities. If you are the intended recipient, you are authorized to use the information only as expressly authorized by Ahold and must strictly adhere to applicable security procedures, including without limitation the following: - You must protect files using the password and level of protection in which the file was provided to you; and - You may transmit files only as necessary for the task you are assigned using secure systems and appropriate encryption means. If you have received the message in error, please advise the sender by reply e-mail, and destroy all copies of the original message (including any attachments). Please direct any questions to the sender of this message.
