Re: Version 5.6.2-m5 Boolean Datatype
Hey Neil, Why not just store it as a TINYINT, that's what I do when I only care about 0 or 1 values? On Wed, May 22, 2013 at 2:19 PM, Neil Tompkins neil.tompk...@googlemail.com wrote: Hi Shawn I plan in installing the latest MySQL version tomorrow. Does MySQL not support Bool eg true and false Neil On 22 May 2013, at 19:05, shawn green shawn.l.gr...@oracle.com wrote: Hello Neil, On 5/22/2013 1:05 PM, Neil Tompkins wrote: Hi, Like the link states For clarity: a TINYINT(1) datatype does NOT ENFORCE a boolean value data entry. For instance, it's still possible to insert a value of 2 (any integer up to the TINYINT max value). I personally don't see the added value of a 'BOOLEAN' synonym type which infact behaves unlike a boolean should. Has BOOL, BOOLEAN been taken out of MySQL 5.6 ? On Wed, May 22, 2013 at 6:01 PM, Ian Simpson i...@it.myjobgroup.co.uk wrote: BOOLEAN is a synonym for TINYINT(1) in MySQL: http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html On 22 May 2013 17:55, Neil Tompkins neil.tompk...@googlemail.com wrote: Hi, I've just created some tables that I designed using the MySQL Workbench Model. However, the database type BOOLEAN which was in my models has been converted to TINYINT(1); I'm currently running MySQL Version 5.6.2-m5 on Windows 2008 server. Any ideas why this has been removed ? This is exactly the same behavior that MySQL has had for over a decade. Nothing has been added or removed since release 4.1.0 (2003-04-03) http://dev.mysql.com/doc/refman/4.1/en/numeric-type-overview.html http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html Also, why are you using a pre-release (milestone) version of 5.6 when the full release (GA) versions of 5.6 are available? http://dev.mysql.com/doc/relnotes/mysql/5.6/en/ Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- -- May the source be with you.
Re: One table gets locked by itself
Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One table gets locked by itself
In your my.conf or configuration file look for an attribute that says LOG_SLOW_QUERIES , that should point to the path of your slow query log. On Tue, May 8, 2012 at 10:19 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi Thanks, Where can i find query log for previous one,or i have to do some config in my.ini file, please let me know, Thanks Abhi -Original Message- From: Darryle [mailto:dstepli...@gmail.com] Sent: 08 May 2012 19:42 To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One table gets locked by itself
My plan B was basically what Rick and Claudio said. Check your my.conf file for this variable LONG_QUERY_TIME . That determines how long a query will run before it's considered slow. You may need to adjust that setting, but that will just get rid of the symptom and not the problem at hand. Finding the actual query is the first step. On Tue, May 8, 2012 at 1:42 PM, nixofortune nixofort...@gmail.com wrote: You might run out of file desciptors. Check your open file limits, open table limits vars and corresponding syatus values On 8 May 2012 15:05, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql for os x 10.6 (64bit) cannot start service
Do you see a MySql icon under System Preferences Other ? That's how I start MySql on my Mac. On Wed, Feb 29, 2012 at 9:05 AM, Elim Qiu elim@gmail.com wrote: *I downloaded **Mac OS X ver. 10.6 (x86, 64-bit), DMG Archive, installed to my pretty clean os x 10.6.8 (snow leopard) after (1st time) started apache. The installation went smoothly but the service just cannot be started.* ** -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql for os x 10.6 (64bit) cannot start service
I have version 5.5.17 MySQL Community Server (GPL) on my Mac. On Wed, Feb 29, 2012 at 9:36 AM, Elim Qiu elim@gmail.com wrote: The vertion of MySQL that I cannot start is 5.1.61 (the only one for 5.1* mac at mysql.com) On Wed, Feb 29, 2012 at 7:05 AM, Elim Qiu elim@gmail.com wrote: *I downloaded **Mac OS X ver. 10.6 (x86, 64-bit), DMG Archive, installed to my pretty clean os x 10.6.8 (snow leopard) after (1st time) started apache. The installation went smoothly but the service just cannot be started.* ** -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql for os x 10.6 (64bit) cannot start service
Are you looking in /usr/local/mysql/data ? You should see a username.err file. You might have to sudo to open the file just do sudo tail -f FILENAME or sudo taill -f /path/to/filename/ . On Wed, Feb 29, 2012 at 1:00 PM, Elim Qiu elim@gmail.com wrote: -rw-r--r-- 1 root wheel 17987 Dec 17 09:01 COPYING -rw-r--r-- 1 root wheel 7371 Dec 17 09:01 INSTALL-BINARY -rw-r--r-- 1 root wheel 2552 Dec 17 09:01 README drwxr-xr-x 46 root wheel 1564 Dec 17 09:01 bin drwxr-x--- 8 _mysql wheel 272 Feb 29 10:36 data drwxr-xr-x 4 root wheel 136 Dec 17 09:01 docs drwxr-xr-x 35 root wheel 1190 Dec 17 09:01 include drwxr-xr-x 22 root wheel 748 Feb 29 10:33 lib drwxr-xr-x 4 root wheel 136 Dec 17 09:01 man drwxr-xr-x 15 root wheel 510 Dec 17 09:01 mysql-test drwxr-xr-x 3 root wheel 102 Dec 17 09:01 scripts drwxr-xr-x 35 root wheel 1190 Dec 17 09:01 share drwxr-xr-x 29 root wheel 986 Dec 17 09:01 sql-bench drwxr-xr-x 16 root wheel 544 Dec 17 09:01 support-files I found the MySQL document is often difficult to read, but a web search indicates that should in data directory, but I cannot even cd to that directory (sudo is not good enough!) On Wed, Feb 29, 2012 at 10:33 AM, Larry Martell larry.mart...@gmail.comwrote: On Wed, Feb 29, 2012 at 10:17 AM, Elim Qiu elim@gmail.com wrote: Thanks Larry and Darryle for your help Where the error log should be? http://dev.mysql.com/doc/refman/5.0/en/error-log.html On Wed, Feb 29, 2012 at 8:22 AM, Larry Martell larry.mart...@gmail.com wrote: On Wed, Feb 29, 2012 at 7:46 AM, Elim Qiu elim@gmail.com wrote: Yes, there is an icon. I can open the preference but the start service button cannot do the job What is in the mysql error log? On Wed, Feb 29, 2012 at 7:20 AM, Darryle Steplight dstepli...@gmail.com wrote: Do you see a MySql icon under System Preferences Other ? That's how I start MySql on my Mac. ** -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql for os x 10.6 (64bit) cannot start service
If you are going to use su to officially switch to the root users just make sure you do su - with the dash. On Wed, Feb 29, 2012 at 2:10 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 29.02.2012 19:20, schrieb Larry Martell: Is the sudo succeeding? If it is, then there's no reason you shouldn't be able to cd into that dir. If not, then you're going to have to be able to get root privileges on your own machine. Alternatively, you could explicitly set the location of the error log in your mysql config file (my.cnf) , to a location you can access, e.g. log-error=/tmp/mysqld.log put it under [mysqld] and [mysqld_safe] but you would have still NO PERMISSIONS to that logfile because it is owned by mysqld and a normal user has usually no permissions to daemon-logs especially because /tmp has normally 1777 - everybody can write but after create a file only the owner is allowed to access it why not using su to REALLY switch to root? -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: left join two tables
Hi Johan, I think you probably want something like this. Give the following a shot. SELECT * FROM table1 LEFT JOIN table2 ON table1.ID = table2.subID LEFT JOIN table3 ON table1.ID= table3.subID On Thu, Apr 28, 2011 at 9:41 AM, Johan De Meersman vegiv...@tuxera.be wrote: Hey there, - Original Message - From: Rocio Gomez Escribano r.go...@ingenia-soluciones.com Hi!! Is it possible to create a left join consult with 2 tables?? I mean: SELECT * FROM table1 LEFT JOIN (table2, table3) on table1.ID = table2.subID and table1.ID= table3.subID Pretty close already. Might I suggest sampling the fine manual ? Have a look at http://dev.mysql.com/doc/refman/5.0/en/join.html -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CURRENT insert ID
@Joao - I'm currently building a database out right now that has this scenario. One field can be the primary key, that has a purpose for holding the record id, another field can hold the value. Let say there are two fields, id, s_id. Initially, you insert a record and `id` is now 100 and you update s_id to be 100. But for whatever reason, later down the road you need s_id to be 200. You can just update the s_id field instead of deleting the entire record and inserting an entire new one with X amount of fields. Updating one field is a lot less work than deleting and inserting. I have my tables set up so I won't have to use the primary key for queries, I will only use the s_id field. 2011/1/21 João Cândido de Souza Neto j...@consultorweb.cnt.br I can´t think about how useful for you would be to have two fields with the same value. -- João Cândido de Souza Neto Jerry Schwartz je...@gii.co.jp escreveu na mensagem news:007501cbb98a$177acba0$467062e0$@co.jp... Here it is in a nutshell: I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don't know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE? Here's a more concrete description of the problem: CREATE TABLE t ( id INT(11) AUTO-INCREMENT PRIMARY, xxx INT(11) ); When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.) Is there anything clever I can do? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- -- May the Source be with you.
Re: Update Syntax
Hi Vicor, Look into INSERT ON DUPLICATE or REPLACE statements. You need to have a primary key or unique key for these too work. On Sun, Jul 26, 2009 at 1:11 PM, Victor Subervivictorsube...@gmail.com wrote: Hi; I would like to test the following: update maps set map where site=mysite; to see if there is such an entry in maps. If there is, then update. If there is not, then I would like to execute an insert statement. How do I do that? TIA, Victor -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ordering search results
You might have to change the collation you are currently using to one that best match the language of those weird accents you are referring too. That's part of the reason you may be getting unexpected results with your ORDER BY statement. Also, can you show us your select statements? On Fri, Jul 17, 2009 at 11:06 AM, PJaf.gour...@videotron.ca wrote: Can't find anything on the web that deals with my problem(s). I have to display thousands of book listings by title, sub_title with 10 books per page. The php/mysql code works fine - except: ASC or DESC does not change one iota. I have checked by commandline and find that it is not working at all how I would expect. From commandline, using just title and switching between ASC DESC give totally different results rather than displaying the same data in reverse order. The display is, as mentioned above, 10 books per output page: so, from what appears to me, the ordering seems to be done on the entire db not just on the search results (this is basically from a SELECT statement). Furthermore, not all the data is in 1 table; authors, categories publishers are in separate tables because of 1 to many many to 1 relationships. Still another problem is the use of a number of foreign languages which have those strange accent on many letters that do not order very well. Now, that I have spewed out my problems, would it be possible that there is someone out there who could suggest how to go about figuring this out? Thanks in advance. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow query Performance
Can you show us the output of DESCRIBE score and SHOW INDEX FROM score? On Wed, Jul 15, 2009 at 6:44 PM, Tachu®tachu1+my...@gmail.com wrote: I'm having random query slowness that i can only reproduce once. My main question is that the query runs faster the second time around but i dont have query cache enabled here is some info from mysql profiler; The time is spent mostly on the sending data step first time around 63 rows in set (0.51 sec) show profile all; ++--+--++---+-+--+---+---+---+---+---+---+---+---+-+ | Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line | ++--+--++---+-+--+---+---+---+---+---+---+---+---+-+ | starting | 0.000165 | 0.00 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | | Opening tables | 0.33 | 0.00 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 4450 | | System lock | 0.20 | 0.00 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 258 | | Table lock | 0.28 | 0.00 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 269 | | init | 0.52 | 0.00 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 2337 | | optimizing | 0.36 | 0.00 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_select.cc | 762 | | statistics | 0.000233 | 0.001000 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_select.cc | 944 | | preparing | 0.31 | 0.00 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_select.cc | 954 | | executing | 0.17 | 0.00 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_select.cc | 1638 | | Sending data | 0.504797 | 0.129980 | 0.012998 | 429 | 38 | 2456 | 64 | 0 | 0 | 0 | 0 | 0 | exec | sql_select.cc | 2177 | | end | 0.54 | 0.00 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 2382 | | query end | 0.23 | 0.00 | 0.00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4799 | | freeing items | 0.63 | 0.00 | 0.000999 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5805 | | logging slow query | 0.18 | 0.00 | 0.00 | 0 | 0 | 0 | 0 | 0 |
Re: Hard? query to with group order by group head's name
Hi Elim, I didn't test it out but it sounds like you want to do this SELECT * FROM group_members GROUP BY head_id, member_id ORDER BY name ASC . On Thu, Jul 16, 2009 at 1:20 AM, Elim PDTe...@pdtnetworks.net wrote: My table group_member looks like this: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 1 | Elim | NULL | | 2 | Ann | 1 | | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | +---+---+-+ Record with null head_id means the member is a group head. Record with head_id k are in the group with head whoes id equals k. I like to fetch the rows in the following ordaer | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim | NULL | | 2 | Ann | 1 | That is (1) A head-row follewed by the group members with that head (2)head rows are ordered alphabetically by name. What the query looks like? Thanks -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql select query
1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTianjingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql select query
You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5 for example. Only grab the columns you are actually going to use. On Mon, Jul 13, 2009 at 12:23 AM, TianJingtianj...@genomics.org.cn wrote: thanks for reply, i hava an index on the start_position,the min_postion and the max_postion is constant value, the output of the query is: explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+---+-+ | 1 | SIMPLE | REF_SEQ | range | index_seq_start | index_seq_start | 5 | NULL | 90886 | Using where | ++-+-+---+-+-+-+--+---+-+ index_seq_start is the index on start_postion, 2009/7/13 Darryle Steplight dstepli...@gmail.com 1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTianjingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- Tianjing Bioinformatics Center, Beijing Genomics Institute,Shenzhen Tel:+86-755-25273851 MSN:tianjing...@hotmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql select query
Numeric indexing is a lot faster. You definitely shouldn't use text or varchar types as column types for you min and max values. Do an ALTER TABLE on any column only hold numeric values and switch them to int or mediumint. On Mon, Jul 13, 2009 at 12:36 AM, TianJingtianj...@genomics.org.cn wrote: sorry fo that, but i really need all cols in the table, i think the problem maybe caused by one of the col which is text type, each record of this col has 2000 characters. this makes the size of record more biger. 2009/7/13 Darryle Steplight dstepli...@gmail.com You are still doing SELECT * . Do you really need to return all of the columns in that table or just COL1, COL2, COL5 for example. Only grab the columns you are actually going to use. On Mon, Jul 13, 2009 at 12:23 AM, TianJingtianj...@genomics.org.cn wrote: thanks for reply, i hava an index on the start_position,the min_postion and the max_postion is constant value, the output of the query is: explain select * from REF_SEQ where START_POSITION between 3 and 803; ++-+-+---+-+-+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+---+-+-+-+--+---+-+ | 1 | SIMPLE | REF_SEQ | range | index_seq_start | index_seq_start | 5 | NULL | 90886 | Using where | ++-+-+---+-+-+-+--+---+-+ index_seq_start is the index on start_postion, 2009/7/13 Darryle Steplight dstepli...@gmail.com 1. Don't use SELECT *. Only grab the cols that you only need. Also make sure you have an index on min_position and max_position. After that if your query isn't faster please show us the output of running EXPLAIN select * from table_name where start_postion between min_postion and max_postion . On Mon, Jul 13, 2009 at 12:03 AM, JingTianjingtian.seu...@gmail.com wrote: Hi all, i use select * from table_name where start_postion between min_postion and max_postion to select all the record in the ranges, when the ranges is very large,such as 800(about 1000 record in it), the query is so slow, when i use mysql administrator i find that traffic is higher when the query is begin, could you please give me some advice on how to optimization the query? thanks, -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jingtian.seu...@gmail.com -- Tianjing -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to Optimize distinct with index
Select user_id from user where key1=value and key2=value2 and key3=value2 GROUP BY user_id is faster than Select distinct user_id from user where key1=value and key2=value2 and key3=value2; 2009/6/18 周彦伟 yanwei.z...@opi-corp.com: Hi, I have a sql : Select distinct user_id from user where key1=value and key2=value2 and key3=value2; I add index on (key1,key2,key3,user_id), this sql use temporary table howevery I have thousands of queries per second. How to optimize it? Anthoer question: Select * from user where user_id in(id1,id2,id3,id4,.) order by use_id; I add index on user_id,but after in,order use temporary table, How to optimize it? Thanks! zhouyanwei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: safe query prevent sites from hijacker
pull the plug for the mains and save energy.. It's still early, but it was only a matter of time before people on this list start typing what I was thinking. But for starter, check out http://shiflett.org/ and read his Essential PHP Security book. On Fri, Jun 19, 2009 at 10:03 AM, walter harmswha...@bfs.de wrote: bharani kumar schrieb: Hi All , This is one general question , How to write the safe query , which prevent the site from hijacker , Share your idea's pull the plug for the mains and save energy. there is no silver bullet. take a lecture in security and you will scream who much simple mistakes are made already. security is a habit, a target at best. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: safe query prevent sites from hijacker
Of course I'm assuming you are using PHP. On Fri, Jun 19, 2009 at 10:28 AM, Darryle Steplightdstepli...@gmail.com wrote: pull the plug for the mains and save energy.. It's still early, but it was only a matter of time before people on this list start typing what I was thinking. But for starter, check out http://shiflett.org/ and read his Essential PHP Security book. On Fri, Jun 19, 2009 at 10:03 AM, walter harmswha...@bfs.de wrote: bharani kumar schrieb: Hi All , This is one general question , How to write the safe query , which prevent the site from hijacker , Share your idea's pull the plug for the mains and save energy. there is no silver bullet. take a lecture in security and you will scream who much simple mistakes are made already. security is a habit, a target at best. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fastest way to select on 0/1 flag
Hi Artem, There can be many malicious factors at play here, but if you are not using an index then definitely create on now. It will obviously help you with option 1 and you can still benefit from it with option 2. If you don't have an index, MySQL has to search for you data row by row which is much slower than using an index. 2009/6/15 Artem Kuchin mat...@itlegion.ru: Hello! I cannot figure out the fastest way to do a select on the floowing field: f_spec tinyint not null; It is a table of 100 000 records of products and f_spec is set only for about 200 products. I figure it could be done in two ways: 1) create an index on f_spec and do simple select * from products where f_spec=1; 2) create a separate table create table specs ( product_id int; primary key (product_id) ); then select ids from this table and join with the products table if needed. What is the best way? Also, it is often needed to know only the fact that there is any product with f_spec set. Is using index and doing select id from products where f_spec=1 limit 1 will be very fast ? Regards, Artem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: dumb crash
Hi PJ, Try adding innodb_force_recovery = 4 to the mysqld section of your config file before restarting the server.If you can dump your tables using 4 then only some data on corrupt individual pages is lost. If you have to use innodb_force_recovery = 6, that means your db pages are left in an obsolete state and your B-trees structures may also be corrupt. 2009/6/10 Isart Montane isart.mont...@gmail.com: Hi, any message on the error log? Have you tried restarting the mysql server? On Tue, May 26, 2009 at 7:24 PM, PJ af.gour...@videotron.ca wrote: Hydro Quebec just f***ed my server just as I was booting up three machines; XP is ok, FreeBSD 7.1 is the one with mysql problem, FreeBSD 4.10 - don't know, but boots ok. Result: can't access database. One table seems to abort mysqld. PhpMyAdmin connects to all databases except one. mysql CHECK TABLE producer; ERROR 2006 (HY000): Mysql server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: theproblem-one Error 2006 (HY000: Mysql server has gone away ERROR 2002 (HY000): Can't connect to local lMySQL server through socket '/tmp/mysql.sock' (61) ERROR: Can't connect to the server mysql ps shows mysqls is running in safe mode Can't find anything on googie I've tried SELECT INTO OUTFILE - same errors... WEBMIN shows all dbs except the fro producer and phpMyAdmin resets to login page when trying to access producer table. Any suggestions or bad experiences solved? -- Hervé Kempf: Pour sauver la plančte, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=isart.mont...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best way to convert character set from latin1 to utf8 for existing database?
Uma, I apologize in advance if this is redundant ,because I did not click on any of Ewen's link. Nonetheless, this is the approach I would take. start your mysql server with different --character-set-server and ---collation-server options Type SHOW COLLATION; in your mysql shell to determine which collations are available for each character set If you want to change the character set while running MySql, that may also change the sort order. you must run myisamchk -r -q -set-collation=collation_name on all MyISAM tables or your indexes may not be ordered correctly There are numerous collations for the uft8 charset so I'm assuming mysql is selecting a collation that you don't want to use. Additionally, if you did not run myisamchk on any of your MyISAM tables that may be why you are getting unexpected results. I hope this helps. On Sun, Jun 7, 2009 at 10:29 PM, Uma Bhatbhat@gmail.com wrote: Thank was great piece of info Ewen, Thanks! However this approach works for new data. But the existing data in the database does not show us the Japanese characters from application side. Appreciate responses who 'actually' got to work on this conversion. Thanks! Uma On 6/1/09, ewen fortune ewen.fort...@gmail.com wrote: Uma, On Mon, Jun 1, 2009 at 8:41 AM, Uma Bhat bhat@gmail.com wrote: Hi All, I have read many blogs suggesting some examples for this. But suggestions from you guys who have ACTUALLY worked on such a scenario would help me out the best. Current Database has: DEFAULT CHARACTER SET - latin1 DEFAULT COLLATION : latin1_swedish_ci We need to convert this to DEFAULT CHARACTER SET - utf8 DEFAULT COLLATION : utf8_general_ci Note that this has to be done on a database that has *existing data* in it . Hence just by doing a: ALTER DATABASE dbname CHARSET=utf8; would result in unexpected behaviour of the data. Ryan Lowe blogged about this. http://www.mysqlperformanceblog.com/2009/03/17/converting-character-sets/ He wrote a tool for it (linked from post) http://www.pablowe.net/convert_charset And Schlomi Noach commented that openark also has a tool. http://code.openark.org/forge/openark-kit Cheers, Ewen Thanks! Uma -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why doesn't mySQL stop a query when the browser tab is closedL
Hi Daevid, You can always stop the query by running SHOW PROCESSLIST; from the command line or your MySql Admin tool. The above command will show you all of the queries that are currently running along with their PID# and state. Find the query your want to stop, and run the following command KILL #; (where # is the process id) As far as the relationship between killing queries and the browser, I think the previous comments pretty much summed it up. On Wed, Jun 3, 2009 at 10:22 AM, Jerry Schwartz jschwa...@the-infoshop.com wrote: -Original Message- From: Jay Blanchard [mailto:jblanch...@pocket.com] Sent: Wednesday, June 03, 2009 8:46 AM To: Daevid Vincent; mysql@lists.mysql.com Subject: RE: Why doesn't mySQL stop a query when the browser tab is closedL [snip] I just noticed a horrible thing. [/snip] Keep in mind that the query event is server side and is not tied to the browser (client side) once it has begun because of the statelessness of the connection. You would have to have some sort of onClose() event from the browser that would trigger a query cancellation. [JS] Going beyond that, the browser is at several removes from the MySQL server. Typically the browser talks to the web server, then the web server runs some application code (PHP or whatever), and then the application code talks to the MySQL server. The only part of this chain that knows what the MySQL server is doing is the last bit, the application code, which is typically waiting for a response. Getting back to the user, HTTP itself is a stateless protocol. That means the web server has no way of knowing if the user, the browser, or even the user's computer is still there; it also doesn't really know what the user last did (it's up to the application code to remember that somehow). In order for an end user to cancel a query, there would have to be some way for the user to tell the browser to tell the web server to tell the application code to tell the MySQL server to stop. I'm pretty sure you could create a tired of waiting button for the user, but I haven't done it myself. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql error 2013 Lost connection to MySQL server during query
Hi Per, Maybe you need to beef up your CONNECT_TIMEOUT setting in your .my.cnf file. Are these queries appearing in your slow query logs?What is your LOG_QUERY_TIMES set too? Here are some other settings you may want to play around wtih CONNECT_TIMEOUT INTERACTIVE_TIMEOUT WAIT_TIMEOUT NET_WRITE_TIMEOUT NET_READ_TIMEOUT MAX_CONNECT_ERRORS On Mon, May 25, 2009 at 3:06 AM, Per Jessen p...@computer.org wrote: This weekend we completed migrating a large(ish) mysql server from 5.0.26 on 32bit to 5.0.51a on 64bit. Everything went relatively smoothly, until this morning when I noticed an application had choked on getting Error 2013 Lost connection to MySQL server during query. The application is running remotely on 32bit using mysql library from version 5.0.67. I've been googling quite a bit, but haven't really found anything of any use. I've checked the two configurations, and they are the same. Can anyone help point me in the right direction? Thanks. /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: When will MySQL support array datatype?
Moon, I'm not sure exactly what you are trying to do, but why don't you just serialize() or json_encode() your data into a column? On Tue, Mar 10, 2009 at 9:35 AM, Moon's Father yueliangdao0...@gmail.com wrote: Thanks for your fast reply. Then only temporary table can simulate array datatype. On Mon, Feb 16, 2009 at 3:12 AM, Claudio Nanni claudio.na...@gmail.comwrote: Complex datatypes are not compatible with the concept of relational databases, probably you want to refer to an Object-Oriented DBMS or Object-Relational DBMS. Cheers Claudio Nanni Moon's Father wrote: Hi. Who could tell me when the MySQL support array datatype? Any reply will be appreciated. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqlimport remote host problem
Hi Rene,
Just a head's up. You might want to keep your username/password
credentials private.
On Tue, Mar 10, 2009 at 10:16 PM, René Fournier m...@renefournier.com wrote:
OK, I've managed to do the same thing with just the mysql command line
program:
mysql -h 192.168.0.224 -u root -p alba2
/Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql
Works great. However, the sql file is normally gzipped, so Can I ungzip
the file on the fly (and without removing the .gzip version) and pipe the
contents as I did above? (Yes, I'm UNIX-impaired.) Something like:
mysql -h 192.168.0.224 -u root -p alba2 gzip -dc
/Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz
But so that it works...
...Rene
On 10-Mar-09, at 7:38 PM, René Fournier wrote:
I'm writing script that, each night, copies a small database to my laptop
on the local network. I'm having trouble getting it to work. Here's my
syntax so far (run on the server):
mysqlimport --host=192.168.0.224 --user=root --password alba2
alba2_2009-03-10_00h45m.Tuesday.sql
Which produces:
---
mysqlimport: Error: 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 '-03-10_00h45m IGNORE 0 LINES' at line 1, when using table:
alba2_2009-03-10_00h45m
---
The sql file is produced by automysqlbackup... Not sure what I'm missing,
probably something obvious. Anyway, here's the first part of the sql file I
want to import:
-- MySQL Administrator dump 1.4
--
-- --
-- Server version 5.0.67-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
--
-- Create schema alba2
--
CREATE DATABASE IF NOT EXISTS alba2;
USE alba2;
--
-- Definition of table `alba2`.`accounts`
--
DROP TABLE IF EXISTS `alba2`.`accounts`;
CREATE TABLE `alba2`.`accounts` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`fp` varchar(40) NOT NULL,
`created` int(10) unsigned NOT NULL default '0',
`status` enum('Active','Inactive') NOT NULL default 'Active',
`account_name` varchar(40) NOT NULL,
`account_full_name` varchar(40) NOT NULL,
`address` varchar(40) NOT NULL,
`city` varchar(40) NOT NULL,
`province` varchar(10) NOT NULL,
`postcode` varchar(10) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `alba2`.`accounts`
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub...@renefournier.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best mysql optimization tutorial and/or quick start guide?
High Performance MySql Optimization, Backups, Replication, and more 2nd Edition . Got a problem, pick a chapter and read the solution. This book is awesome, I'm confident you will find what you are looking for :) . On Thu, Mar 5, 2009 at 12:30 PM, Stephen Edberg sbedb...@ucdavis.edu wrote: I've been poking around google looking for tutorials and/or quick start guides on optimizing the mysql server. We just upgraded our DB server from 2 Gb of RAM to 16. But I don't know how to reconfigure mysql to take full advantage of it. Although, just installing the RAM seems to have made a huge difference. Any suggestions for a really good tutorial on configuring the server based on the amount of RAM, etc? Hard to give details on performance optimization without knowing more about the details for your load, but there are a number of cache-related parameters (query cache, key cache and so on) that could be increased to take optimum advantage of the additional RAM. Take a look at http://www.mysqlperformanceblog.com/ The name is pretty self-explanatory, and there's a lot of useful info there on mysql tuning. They are the authors of 'High Performance MySQL' - http://oreilly.com/catalog/9780596101718/index.html - which I've heard good things about (don't have it myself, plan to buy). Also see http://dev.mysql.com/doc/refman/5.0/en/optimization.html (assuming you use 5.0; substitute appropriate version if otherwise). - steve edberg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Error: Duplicate entry '0' for key 'PRIMARY'
Are you trying to do an Insert On Duplicate Key? Do ou want to insert a new row if it doesn't already exist or update one if it does? On Mon, Mar 2, 2009 at 4:09 PM, sam rumaizan samc...@yahoo.com wrote: Are you talking about Length/Values1 --- On Mon, 3/2/09, Gary Smith g...@primeexalia.com wrote: From: Gary Smith g...@primeexalia.com Subject: Re: Error: Duplicate entry '0' for key 'PRIMARY' To: samc...@yahoo.com, mysql@lists.mysql.com Date: Monday, March 2, 2009, 1:58 PM Easy. Ensure that all in the primary key have unique values. With that said, it would be more useful to have a ddl and the query causing the problem. --Original Message-- From: sam rumaizan To: mysql@lists.mysql.com ReplyTo: samc...@yahoo.com Sent: Mar 2, 2009 12:56 PM Subject: Error: Duplicate entry '0' for key 'PRIMARY' Error: Duplicate entry '0' for key 'PRIMARY' how can i fix it ? Sent via BlackBerry by ATT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: catch the error
Hi PJ,
Could it be that you have //include (lib/db1.php); commented
out? Try uncommenting that line and see what happens. The error
message will always print because the query is never executing
properly if you have the db connections file commented out.
On Thu, Feb 26, 2009 at 12:28 PM, PJ af.gour...@videotron.ca wrote:
What is wrond with this file? same identical insert works from console
but not from this file :-(
html
head
titleUntitled/title
/head
body
?
//include (lib/db1.php); // Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
/body
/html
Seems to be good to print out the error message, but that's all. db not
written.
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: catch the error
ok, well if that's the case then do this
$db = mysql_connect('biggie', 'user', 'password', 'test');
That should fix the problem.
On Thu, Feb 26, 2009 at 12:46 PM, PJ af.gour...@videotron.ca wrote:
It is commented out because I am using mysql_connect
I don't think it would be good to use both, since the db1 references
another db. But even when I use the db1.php and change the database and
table, I get the same error message.
But what I did miss is my typo in What is wrond with this file? :-)
Hi PJ,
Could it be that you have //include (lib/db1.php); commented
out? Try uncommenting that line and see what happens. The error
message will always print because the query is never executing
properly if you have the db connections file commented out.
On Thu, Feb 26, 2009 at 12:28 PM, PJ af.gour...@videotron.ca wrote:
What is wrond with this file? same identical insert works from console
but not from this file :-(
html
head
titleUntitled/title
/head
body
?
//include (lib/db1.php); // Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
/body
/html
Seems to be good to print out the error message, but that's all. db not
written.
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: catch the error
Hi PJ,
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
Everyone here is trying to help you and that's cool, but EVERYONE on
this list may not be so nice. The above credentials is definitely the
type of information you want to keep private, unless you don't mind
people potentially accessing your database tables and doing whatever
they like with them.
I suggest doing something like
$db_host = 'localhost;
$db_user = 'foo';
$db_pass= ''bar;
$db_name =''xx;
if you are going to post it on the list.
On Thu, Feb 26, 2009 at 1:22 PM, PJ af.gour...@videotron.ca wrote:
Ricardo Dias Marques wrote:
Hi PJ,
On Thu, Feb 26, 2009 at 17:28, PJ af.gour...@videotron.ca wrote:
What is wrond with this file? same identical insert works from console
but not from this file :-(
[snip]
?
//include (lib/db1.php); // Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
I haven't coded in PHP for a long time, but I think that your problem
is in this line:
$result1 = mysql_query($sql1,$db);
Up to that point, $db (that should point to a database link
identifier) is not defined. You probably want to assign the
mysql_connect result to that $db variable.
So, I think that you will solve your problem by changing your
mysql_connect line FROM the current form:
mysql_connect('biggie', 'user', 'password', 'test');
.. TO this one:
$db = mysql_connect('biggie', 'user', 'password', 'test');
Am I right?
Partly. I had an error in the location of the include. Ashley corrected
the rest but it only works with the include. Not as whown below
?
//include (../lib/db1.php); // Connect to database
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
$db_connect = mysql_connect($db_host, $db_user, $db_pass);
$db_select = mysql_select_db($db_name, $db_connect);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Re: catch the error
Additionally regarding the error handling , add this to the op of your script.
ini_set(display_errors,true);
error_reporting(E_STRICT|E_ALL);
and post the output of your error message.
On Thu, Feb 26, 2009 at 1:40 PM, Ashley Sheridan
a...@ashleysheridan.co.uk wrote:
On Thu, 2009-02-26 at 13:34 -0500, Darryle Steplight wrote:
Hi PJ,
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
Everyone here is trying to help you and that's cool, but EVERYONE on
this list may not be so nice. The above credentials is definitely the
type of information you want to keep private, unless you don't mind
people potentially accessing your database tables and doing whatever
they like with them.
I suggest doing something like
$db_host = 'localhost;
$db_user = 'foo';
$db_pass= ''bar;
$db_name =''xx;
if you are going to post it on the list.
On Thu, Feb 26, 2009 at 1:22 PM, PJ af.gour...@videotron.ca wrote:
Ricardo Dias Marques wrote:
Hi PJ,
On Thu, Feb 26, 2009 at 17:28, PJ af.gour...@videotron.ca wrote:
What is wrond with this file? same identical insert works from console
but not from this file :-(
[snip]
?
//include (lib/db1.php); // Connect to database
mysql_connect('biggie', 'user', 'password', 'test');
$sql1 = INSERT INTO example (name, age) VALUES ('Joe Blow', '69');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
I haven't coded in PHP for a long time, but I think that your problem
is in this line:
$result1 = mysql_query($sql1,$db);
Up to that point, $db (that should point to a database link
identifier) is not defined. You probably want to assign the
mysql_connect result to that $db variable.
So, I think that you will solve your problem by changing your
mysql_connect line FROM the current form:
mysql_connect('biggie', 'user', 'password', 'test');
.. TO this one:
$db = mysql_connect('biggie', 'user', 'password', 'test');
Am I right?
Partly. I had an error in the location of the include. Ashley corrected
the rest but it only works with the include. Not as whown below
?
//include (../lib/db1.php); // Connect to database
$db_host = 'biggie';
$db_user = 'root';
$db_pass = 'gu...@#$';
$db_name = 'biblane';
$db_connect = mysql_connect($db_host, $db_user, $db_pass);
$db_select = mysql_select_db($db_name, $db_connect);
$sql1 = INSERT INTO test (name, age) VALUES ('Arnie Shwartz', '75');
$result1 = mysql_query($sql1,$db);
if (!$result1) {
echo(PError performing 1st query: .
mysql_error() . /P);
exit();
}
?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
I agree. I wouldn't trust me at all! ;)
Ash
www.ashleysheridan.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie Question - MySQL Administrator
Jeff, For starters, it looks like you need a value for VARCHAR. Try the same statement but with VARCHAR(255) . On Fri, Feb 20, 2009 at 1:17 AM, Jeff Murdock jeff_murd...@yahoo.com wrote: This is on a Mac OS X (v10.5.6) system in case that matters. 1. - MySQL Administrator Help button says: HELP Help isn't available for MySQL Administrator. Really, no help or did I screw-up the install somehow? 2. - I tried to create my first Table in MySQL Administrator but got this message: ERROR Error executing SQL commands to create table. 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 'DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064) Trying to Execute this: CREATE TABLE `test`.`AddressBook` ( `RecNo` INT NOT NULL AUTO_INCREMENT, `FirstName` VARCHAR DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NULL, `Street2` VARCHAR DEFAULT NULL, `City` VARCHAR DEFAULT NULL, `State` VARCHAR DEFAULT NULL, `Zip` VARCHAR DEFAULT NULL, `HomePhone` VARCHAR DEFAULT NULL, `CellPhone` VARCHAR DEFAULT NULL, PRIMARY KEY (`RecNo`) ) CHARACTER SET utf8 COMMENT = 'Sample'; Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Selecting a column with a regular expression applied to it?
Hi Ryan, MySql does have regular expressions. See Link http://dev.mysql.com/doc/refman/5.0/en/regexp.html On Thu, Sep 11, 2008 at 9:52 AM, Ryan Stille [EMAIL PROTECTED] wrote: From looking at the MySQL 5 docs, it doesn't look like there is any way to select a column with a regular expression applied to it? I have a column that has ended up with some non ascii characters in it, probably vertical tabs and things like that from MS Excel. I need to sort by this field but its not coming out right because some of the values have these bad characters at the beginning. I'd like to select that column with a regex applied to it that strips out all the non-ascii chars, then sort by that field. Is this possible? All the examples I saw are just using the regex in the where clause. Thanks, -Ryan -- 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: Weird problem with mysql_query
Hi G, There is nothing weird about your results. When you do a Count(*) without a GROUP BY(someColumn) you are essentially asking MySQL how many rows are present in the table. But when you do use Group By someColum , you are asking MySql how many rows do I have of someColumn . It's just a good practice to use GROUP BY when you want to a count of a specific column . mysql select count(*) as 'Count' from logins GROUP BY dawiz The above query should return the results you are looking for. On Tue, Sep 9, 2008 at 6:06 PM, MySql [EMAIL PROTECTED] wrote: We are running MySql version 5.0.45-Debian_1ubuntu3.1-log Debian etch distribution under Ubuntu. If I submit the following query via mysql_query it acts as if the where is not there: select count(*) as 'Count' from logins where player = 'aqwert'; this returns: Count 143578160 Submitting the same query at a MySql prompt works correcty: mysql select count(*) as 'Count' from logins where player = 'dawiz'; +---+ | Count | +---+ | 6026 | +---+ 1 row in set (0.00 sec) Modifying the query to use a group by returns the correct count: TotalCount Total 6026 Is there something I should know about mysql_query and a simple count(*)? G Vaughn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Totaling from several tables away
Hi Brian, Try this. SELECT SUM(mi.calories) FROM Meal_Items as mi, People as P, Meals as m WHERE p.Person_ID = '5' AND p.Person_ID=m.Person_ID AND m.Date = '2009-09-04' AND m.Meal_ID = mi.Meal_id GROUP BY p.Person_ID Hi Gerald: This part is throwing me off ON People.Name=Meals.Name . But I do belief using Join isn't a bad alternative. On Fri, Sep 5, 2008 at 4:36 PM, Gerald L. Clark [EMAIL PROTECTED] wrote: Brian Dunning wrote: How do I query How many calories did Brian eat on 2009-09-04? Table:People +---+---+ + Person_ID + Name | +---+---+ | 5 | Brian | +---+---+ Table:Meals +-+---+---++ | Meal_ID | Person_ID | Meal_Name | Date | +-+---+---++ | 3 | 5 | Breakfast | 2009-09-04 | | 4 | 5 | Lunch | 2009-09-04 | +-+---+---++ Table:Meal_Items +-+-+---+--+ | MealItem_ID | Meal_ID | Item_Name | Calories | +-+-+---+--+ | 16 | 3 | Banana| 100 | | 17 | 3 | Milk | 150 | | 18 | 4 | Cookie| 200 | +-+-+---+--+ SELECT sum(calories) from People INNER JOIN Meals ON People.Name=Meals.Name INNER JOIN Meal_Items on Meals.Meal_ID=Meal_Items.Meal_ID WHERE Name='Brian' AND Date='2009-09-04'; -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- 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: good books or URL for mysql sql tunning
Hi Anada, I recommend MySQL Database Design and Tuning by Robert Schineider. It covers everything from benchmark testing to Innodb Performance Enhancements. I'm 85% done with the book myself. It shows and explains good command-line and MySql Admin tool examples. The techniques discuss in this book are definitely key for large scaling applications. On Wed, Aug 13, 2008 at 7:30 AM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, Can u please guide me to any good books or URL for mysql sql tunning.. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP Question
Hi Michael,
Try placing the following at the top of your PHP code:
ini_set('error_reporting', E_ALL | E_STRICT);
ini_set('display_errors', 'On');
What error messages do you see on your page?
On Tue, May 6, 2008 at 12:07 PM, Jerry Schwartz [EMAIL PROTECTED]
wrote:
In general, if I have a PHP-generated page that produces a blank page, I
execute the PHP code from the command line. It will probably fall over
dead
somewhere along the line, but you'll know if there are any syntax errors.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Michael Condon [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 06, 2008 10:39 AM
To: mysql@lists.mysql.com
Subject: PHP Question
I have a PHP script that seems to be failing. I execute it using
window.location.href(http://www.vote.com/vote2.php;):
?php
$link = mysql_connect('localhost', 'login', 'password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
$result = use election
if (!$result) {
die('Could not connect: ' . mysql_error());
}
$result = mysql_query(update election set Votes = Votes + 1 where
Name='TheGuy');
if (!$result) {
$message = 'Invalid update: ' . mysql_error() . \n;
$message .= 'Whole query: ' . $result;
die($message);
mysql_close($link);
?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
