Re: Heap table says its Fuul?
Hi, On Mar 24, 2005, at 6:07 PM, [EMAIL PROTECTED] wrote: Mysql is telling me my Heap table is full. Now I set it to 128M. my.cnf line tmp_table_size = 128M Try changing the setting called max_heap_table_size. tmp_table_size only has to do with internal temporary tables that are used to resolve a query (ie. when you see a 'Using Temporary' in the EXPLAIN) The Table filled up at 12.7M This appears to be very close to 128M with a decimal out of place. Did I find a Bug? Am I doing something wrong? Is the tmp_table_size a PER TABLE or for all mysql heap tables? I can't seem to get past this 12.7M mark I need 128M of heap to run my looping searches with. CREATE TABLE `fsearch_searchheap` ( `searchAffid` int(11) NOT NULL default '0', `searchKeyword` varchar(100) NOT NULL default '', `searchReferrer` varchar(100) NOT NULL default '', `searchIp` varchar(15) NOT NULL default '', KEY `searchAffid` (`searchAffid`), KEY `searchKeyword` (`searchKeyword`) ) ENGINE=MEMORY DEFAULT Select * from fsearch_search; Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext In Boolean Wildcard Questions
From: Freddie Bingham [EMAIL PROTECTED] (1) Why is such a query as this allowed? WHERE MATCH (text) AGAINST ('+s*' IN BOOLEAN MODE) This returns everything that starts with an 's', as expected by the syntax. Why is it not stopped by the mysql minimum word length? I The minimum word length still applies to the items that are found! A record with so very cruel in the `text` column will certainly not be found. have no desire to allow a search for every word that begins with an 's' as it can be resourceful and will return results with questionable usage. So I'll take care of not allowing these searches in a higher level It is always wise to check user input before applying it to a query! (2) Why does this query also return everything that begins with an 's' WHERE MATCH (text) AGAINST ('[EMAIL PROTECTED]' IN BOOLEAN MODE) Mysql is obviously silently stripping the list of garbage characters away from the word, which I assume was also done with the indexed list of words. See: http://dev.mysql.com/doc/mysql/en/fulltext-search.html -- MySQL uses a very simple parser to split text into words. A ``word'' is any sequence of true word characters (letters, digits, and underscores), optionally separated by no more than one sequential ''' character. For example, wasn't is parsed as a single word, but wasn''t is parsed as two words wasn and t. (And then t would be ignored as too short according to the rules following.) Also, single quotes at the ends of words are stripped; only embedded single quotes are retained. --- As documented, MySQL will 'ignore' those characters while building the index as they are not part of a 'word' ('word' as defined in the paragraph I quoted). As far as the search 'word' is concerned, MySQL supports a limited list of operator, so you can assume that everything else is silently ignored... I now have a serious issue since I what appears to be a long word, but is really only one character long. Putting quotes around the word seems to force it to be taken literally but that also takes the * literally, breaking the wildcard search. Where can I find a list of these characters that are silently stripped out? http://dev.mysql.com/doc/mysql/en/fulltext-boolean.html lists the operators that are supported: + - () ~ * WHERE MATCH (text) AGAINST ('[EMAIL PROTECTED]' IN BOOLEAN MODE) This returns all results that begin with a 't', but not 's'! This will probably be equal to : '+t* s*' or: must contain word starting with t, possible containing word(s) starting with s. Which is reflected in these results: text relevance passe0 Type error 1 type sweetheart! 1.730698 supergranny 0 then this query: WHERE MATCH(text) AGAINST ('[EMAIL PROTECTED]' IN BOOLEAN MODE) '+ qwerty* s* t*' The first + is not leading a word, so it will be ignored: 'qwerty* s* t*' So sweet sour set sells sins per se 1 so 0 passe 0 Type error 1 type sweetheart!! 2 supergranny 1 qwertyuiop 1 qwertyuiop same type2 Matches all words that beginning with a 's' or a 't'! or beginning with 'qwerty' Anyone have a concise explanation just exactly how the wildcard character works in terms of real world strings such as this? Hopefully the examples above are clear enough. If you want to only support searches for words longer than say three characters it would be wise to: - only allow alphanumeric characters, single quotes, white space and supported operators - remove anything else and check for length of 'words' If the entry passes these checks you can probably apply it to a query (after escaping quotes!!). The checks can be performed fairly easy if you use some kind of regular expressions in the application that uses the query. Regards Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries inside UDF
From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com Subject: Re: Queries inside UDF Date: Mon, 21 Mar 2005 11:05:39 + sguazt sguazt wrote: Hi! From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mysql@lists.mysql.com Subject: Re: Queries inside UDF Date: Thu, 17 Mar 2005 08:22:46 + .. You can actually access a DB within a UDF, but you should do the same than when you're using the C api of mysql. So you need a way to get database name, user/passwd and port without user-input, and do mysql_init, mysql_connect, mysql_query, and so on, like when you are accessing that database using a 'external' C program. Also bear in mind that it should be thread-safe ;) a way to extend udf, is ... stored procedure ? Yes I did it ... look my first post (http://lists.mysql.com/mysql/181361) There you can find and example of a C-code I used to write my UDF ... but when using the UDF inside MySQL I've got Can't connect to MySQL server on 'localhost' (111) you should check the permission. Instead compiling the C-code as stand-alone program (removing the xxx_init/xxx_deinit functions and adding a main) all is OK, the query is executed and the program normally exits. did you execute it on your DB server ? (not on your WS) yes I did. The strange fact is just the stand alone program works and the library doesn't even if the connection parameters (host, port, user, password) are the same. The DBMS is on my machine, so to connect to it I use: host: localhost (I've also tried 127.0.0.1, NULL, ...) port: 3306 (I've also tried 0) user: root password: NULL (I've also tried ) -- Marco _ Ricerche online più semplici e veloci con MSN Toolbar! http://toolbar.msn.it/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding an index without rebuilding all others
Hello. ALTER TABLE works in the following way: Create a new table named A-xxx with the requested structural changes. Copy all rows from the original table to A-xxx. Rename the original table to B-xxx. Rename A-xxx to your original table name. Delete B-xxx. As ALTER table creates a new table, it rebuilds indexes. CheHax [EMAIL PROTECTED] wrote: Hello list, I have a big table, 25M records and a lot of indexes on it. Sometimes, it happens that we need to create an new index on the table, and I was wondering if there is a solution to do an ALTER TABLE... ADD INDEX without having mySQL to rebuild all indexes ? Thanks, CheHax -- 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: Too many connections
Hello. May be this would be helpful: http://dev.mysql.com/doc/mysql/en/user-resources.html Jan Pieter Kunst [EMAIL PROTECTED] wrote: Hello all, Suppose I have a LAMP server which functions good enough for daily use by humans, but is occasionally brought to its knees by an automated website-downloader, when such a download involves a lot of database searches, which uses up all the available MySQL connections. Is there anything to do in the MySQL layer of the server to mitigate this problem? I was thinking of a setting like 'database xxx can have only n percent of the maximum number of connections at any given time', but such a setting doesn't seem to exist. Any ideas? Thanks, Jan Pieter Kunst -- 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]
Repairing Data packing with zeros
Hi, I needed to repair some data that had been entered inconsistently, I was in a rush so I did it manually via SqlYOG. (too much of a rush to consider how to do it quickly!) The bad data in one column propertyID was of kind \w\w\w\d eg ONE1 \w\w\w\d\d eg ABC23 \w\w\w\d\d\d eg FGH123 when they all needed to packed with zeros as necessary \w\w\w\d\d\d\d so ONE1- ONE0001 ABC23 - ABC0023 FGH123 - FGH0123 What Update Query should/could I have used? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
About column privileges, COLUMN_INSERT...
I want to allow some users to SELECT, INSERT, UPDATE all columns in a table EXCEPT 1 (last one). Is it possile in MYSQL and how? So my question is : What COLUMN_INSERT privilege does? How can I INSERT_COLUMN without having to insert whole row? Does COLUMN_INSERT = TABLE_INSERT? I've found some answers in Manual but: In MySQL, if you have the INSERT privilege on only some of the columns in a table, you can execute INSERT statements on the table; the columns for which you don't have the INSERT privilege are set to their default values. Standard SQL requires you to have the INSERT privilege on all columns. 1. What about UPDATE privilege: 2. the columns for which you don't have the INSERT privilege are set to their default values. seems incorrect: When i try to INSERT or UPDATE last column for which I have only TABLE_SELECT privilege using MySQL Front 2.5 on Mysql 4.0.24 server it's OK ?!?! Value changed and visible from everywhere! NO error(s)... Mysql Front uses older client dll (3.23 I think) but shouldn't this rule be enforced from server (side) not client (side)? MySQL Front 3.0+ behaves corectly : SQL Error 1142 UPDATE COMMAND denied to user ... for table tbl_name. yet again if I try the same with latest MySQl Query Browser 1.1.6: ! UPDATE command denied to user: '[EMAIL PROTECTED]' for table 'tbl_name' Nothing changes P.S. How older client cheats newest server is still mistery to me... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More on column privileges, COLUMN_UPDATE...
http://dev.mysql.com/doc/mysql/en/grant.html Sorry for previous post - my mistake: I added record as admin and continued using that user ;-) now logged as ordinary user another problem(s) arise: MySQL Server 4.0.24, trying varoius clients (dlls) 3.23, 4.0.x, 5.x? comes with MySQL Query Browser 1.1.6 to no avail Manual still states privileges are evaluated this way: global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges i.e. my [EMAIL PROTECTED] has NO global priv, has DB_SELECT only, host = %, has TABLE_SELECT only and has NO column privileges on all columns except UPDATE, INSERT, SELECT on last_col so.. by manual it is possible to INSERT a COLUMN/ROW and/or UPDATE last_col [OR column privileges IN effect] SHOW GRANTS FOR user - shows privs only downto TABLE level only :( yet they really ARE in mysql.columns_priv? In MySQL, if you have the INSERT privilege on only some of the columns in a table, you can execute INSERT statements on the table; the columns for which you don't have the INSERT privilege are set to their default values. Standard SQL requires you to have the INSERT privilege on all columns. alas :-( UPDATE tbl set last_col=25; !UPDATE command denued to [EMAIL PROTECTED] for table tbl and for INSERT !INSERT command denied to [EMAIL PROTECTED] for table tbl Why SHOW GRANTS FOR user doesn't show Column_priv since they are in mysql.columns_priv? Is this server version issue mysql 4.0.24 accepts column_priv w/o error(s) justs doesn't enforce them? -- Remo Tex wrote: I want to allow some users to SELECT, INSERT, UPDATE all columns in a table EXCEPT 1 (last one). Is it possile in MYSQL and how? So my question is : What COLUMN_INSERT privilege does? How can I INSERT_COLUMN without having to insert whole row? Does COLUMN_INSERT = TABLE_INSERT? I've found some answers in Manual but: In MySQL, if you have the INSERT privilege on only some of the columns in a table, you can execute INSERT statements on the table; the columns for which you don't have the INSERT privilege are set to their default values. Standard SQL requires you to have the INSERT privilege on all columns. 1. What about UPDATE privilege: 2. the columns for which you don't have the INSERT privilege are set to their default values. seems incorrect: When i try to INSERT or UPDATE last column for which I have only TABLE_SELECT privilege using MySQL Front 2.5 on Mysql 4.0.24 server it's OK ?!?! Value changed and visible from everywhere! NO error(s)... Mysql Front uses older client dll (3.23 I think) but shouldn't this rule be enforced from server (side) not client (side)? MySQL Front 3.0+ behaves corectly : SQL Error 1142 UPDATE COMMAND denied to user ... for table tbl_name. yet again if I try the same with latest MySQl Query Browser 1.1.6: ! UPDATE command denied to user: '[EMAIL PROTECTED]' for table 'tbl_name' Nothing changes P.S. How older client cheats newest server is still mistery to me... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Heap table says its Full?
I took a guess at that yesterday. I left the tmp_table_size 128M added the line max_heap_table_size 500M But to no avale. Still limited in the number to 12.7M I am using 4.1.8 as installed by Cpanel. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: Harrison Fisk [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 1:30 AM To: [EMAIL PROTECTED] Cc: Mysql Subject: Re: Heap table says its Fuul? Hi, On Mar 24, 2005, at 6:07 PM, [EMAIL PROTECTED] wrote: Mysql is telling me my Heap table is full. Now I set it to 128M. my.cnf line tmp_table_size = 128M Try changing the setting called max_heap_table_size. tmp_table_size only has to do with internal temporary tables that are used to resolve a query (ie. when you see a 'Using Temporary' in the EXPLAIN) The Table filled up at 12.7M This appears to be very close to 128M with a decimal out of place. Did I find a Bug? Am I doing something wrong? Is the tmp_table_size a PER TABLE or for all mysql heap tables? I can't seem to get past this 12.7M mark I need 128M of heap to run my looping searches with. CREATE TABLE `fsearch_searchheap` ( `searchAffid` int(11) NOT NULL default '0', `searchKeyword` varchar(100) NOT NULL default '', `searchReferrer` varchar(100) NOT NULL default '', `searchIp` varchar(15) NOT NULL default '', KEY `searchAffid` (`searchAffid`), KEY `searchKeyword` (`searchKeyword`) ) ENGINE=MEMORY DEFAULT Select * from fsearch_search; Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.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]
SET question - @ or @@?
While I was RTFM to find out how long SET variables last, I noticed that my book uses @@VarName but the sample code I'm using has @ VarName. I didn't see a reference to the single @ in the SET section of my book. So, what is the difference between @@ and @? Thank you.
Re: SET question - @ or @@?
At 10:23 -0600 3/25/05, Stembridge, Michael wrote: While I was RTFM to find out how long SET variables last, I noticed that my book uses @@VarName but the sample code I'm using has @ VarName. I didn't see a reference to the single @ in the SET section of my book. So, what is the difference between @@ and @? @ is used for user variables (variables that you define yourself) @@ is used for system variables, such as storage_engine or sql_mode. http://dev.mysql.com/doc/mysql/en/set-option.html http://dev.mysql.com/doc/mysql/en/variables.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET question - @ or @@?
- Original Message - From: Stembridge, Michael [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, March 25, 2005 5:23 PM Subject: SET question - @ or @@? While I was RTFM to find out how long SET variables last, I noticed that my book uses @@VarName but the sample code I'm using has @ VarName. I didn't see a reference to the single @ in the SET section of my book. So, what is the difference between @@ and @? Read: http://dev.mysql.com/doc/mysql/en/set-option.html about different types of variables and their syntax and http://dev.mysql.com/doc/mysql/en/variables.html about user variables in particular... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Heap table says its Full?
Ok, Never mind my last statement because I didn't change anything and it worked this morning. Next problem I copied a 21MB db to the heap and it reported 248M of data once there? Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 8:12 AM To: Harrison Fisk Cc: Mysql Subject: RE: Heap table says its Full? I took a guess at that yesterday. I left the tmp_table_size 128M added the line max_heap_table_size 500M But to no avale. Still limited in the number to 12.7M I am using 4.1.8 as installed by Cpanel. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: Harrison Fisk [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 1:30 AM To: [EMAIL PROTECTED] Cc: Mysql Subject: Re: Heap table says its Fuul? Hi, On Mar 24, 2005, at 6:07 PM, [EMAIL PROTECTED] wrote: Mysql is telling me my Heap table is full. Now I set it to 128M. my.cnf line tmp_table_size = 128M Try changing the setting called max_heap_table_size. tmp_table_size only has to do with internal temporary tables that are used to resolve a query (ie. when you see a 'Using Temporary' in the EXPLAIN) The Table filled up at 12.7M This appears to be very close to 128M with a decimal out of place. Did I find a Bug? Am I doing something wrong? Is the tmp_table_size a PER TABLE or for all mysql heap tables? I can't seem to get past this 12.7M mark I need 128M of heap to run my looping searches with. CREATE TABLE `fsearch_searchheap` ( `searchAffid` int(11) NOT NULL default '0', `searchKeyword` varchar(100) NOT NULL default '', `searchReferrer` varchar(100) NOT NULL default '', `searchIp` varchar(15) NOT NULL default '', KEY `searchAffid` (`searchAffid`), KEY `searchKeyword` (`searchKeyword`) ) ENGINE=MEMORY DEFAULT Select * from fsearch_search; Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repairing Data packing with zeros
zzapper wrote: ONE1- ONE0001 ABC23 - ABC0023 FGH123 - FGH0123 What Update Query should/could I have used? Something like this perhaps? UPDATE table_name SET propertyID = CONCAT(LEFT(propertyID, 3), LPAD(SUBSTRING(propertyID, 4), 4, '0')) WHERE propertyID REGEXP '^[A-Z]{3}[0-9]{1,3}$'; -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repairing Data packing with zeros
On Fri, 25 Mar 2005 12:03:26 -0500, wrote: zzapper wrote: ONE1- ONE0001 ABC23 - ABC0023 FGH123 - FGH0123 What Update Query should/could I have used? Something like this perhaps? UPDATE table_name SET propertyID = CONCAT(LEFT(propertyID, 3), LPAD(SUBSTRING(propertyID, 4), 4, '0')) WHERE propertyID REGEXP '^[A-Z]{3}[0-9]{1,3}$'; Keith, Thanx that works just fine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using between
Hello Is there a way when searching for a range of values for a particular field that mysql would not have to look at the entire table ... I'm guessing with some type of composite key or something? ... e.g. SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; Field1 and field2 are indexed but that doesn't help because you're looking for a range of values instead of a particular value -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Segmentation fault while executing Mysql application.
Hello sir, I have written an application by using MySQL API for retrieving result of simple query select * from Table_Name but there are some modification in compilation process for getting desire results. Modification or Alteration are: 1. I have Installed full packages of MySQL-3.23.54-11during Linux RedHat (Shrike) installation. 2. I have written an application by using following MySQL API: i- mysql_init(mysql); ii- mysql_real_connect(mysql,host,user,pass,db,0,NULL,0) iii- mysql_real_query(mysql,query,50) iv- mysql_store_result(mysql) v- mysql_fetch_row(res) vi- mysql_num_fields(res) vii-mysql_free_result(res) viii- mysql_close(mysql); 3. I am using following files: i- mysql.h -- Header file for fucntion prototyping and use for Data-Type declaration. ii-mysql_com.h and mysql_version.h -- These header files are required for mysql.h file iii- libmysqlclient.a -- Use for required API and for compilation iv- Esql.c -- My own Application Note: mysql.h, mysql_com.h and mysql_version.h are belong to MySQL-3.23.54-11 package but libmysqlclient.a is belong to MySQL-4.1.10 package and all mentioned file are contains in a Folder 'Application'. 4. Compilation procedure: i- gcc -o App Esql.c -lz /path/to/Application/libmysqlclient.a 5. Execution procedure: i- Application ./App 6. Execution Error: i- I have retrieved desire result with respect to query. ii- But Following error occur after print desire result on screen at end: I- Segmentation fault. 7. Experiments: i- I have examined application behaviour with libmysqlclient.a file of same package MySQL-3.23.54-11. ii-I have gained all those result what i had expected. 8. Reason: i-Source distribution MySQL-3.23.54-11.tar.gz does not install properly even all requirement are met as describe in mannual. ii-Source distribution MySQL-4.1.10.tar.gz install properly iii- I required all those files which is required for creating libmysqlclient.a file; *.c and *.o of MySQL-3.23.54-11 package which install during linux installation. So, these were fact and figure which being happen in developing application. Kindly, suggest me. I 'll be very greatful to you. IMRAN SHABBIR Software Engineer Vibrant Wits Software Inc. Pakistan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using between
At 01:48 PM 3/25/2005, you wrote: Hello Is there a way when searching for a range of values for a particular field that mysql would not have to look at the entire table ... I'm guessing with some type of composite key or something? ... e.g. SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; Field1 and field2 are indexed but that doesn't help because you're looking for a range of values instead of a particular value Rob, I'm having a hard time wrapping my head around your example.It's like the tail wagging the dog.g Putting variables and columns to your syntax I get: select cust_num from table_invoices where 5 between invoice_amt and tax; I think you meant to say: select aCol from aTable where aCol between val1 and val2; If aCol is indexed, then MySQL will use the index to get a Range on val1 and val2. Just use Explain and you'll see the index that it's using. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heap table says its Full?
Hello. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or TRUNCATE TABLE, or altered with ALTER TABLE. [EMAIL PROTECTED] wrote: I took a guess at that yesterday. I left the tmp_table_size 128M added the line max_heap_table_size 500M But to no avale. Still limited in the number to 12.7M I am using 4.1.8 as installed by Cpanel. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: Harrison Fisk [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 1:30 AM To: [EMAIL PROTECTED] Cc: Mysql Subject: Re: Heap table says its Fuul? Hi, On Mar 24, 2005, at 6:07 PM, [EMAIL PROTECTED] wrote: Mysql is telling me my Heap table is full. Now I set it to 128M. my.cnf line tmp_table_size = 128M Try changing the setting called max_heap_table_size. tmp_table_size only has to do with internal temporary tables that are used to resolve a query (ie. when you see a 'Using Temporary' in the EXPLAIN) The Table filled up at 12.7M This appears to be very close to 128M with a decimal out of place. Did I find a Bug? Am I doing something wrong? Is the tmp_table_size a PER TABLE or for all mysql heap tables? I can't seem to get past this 12.7M mark I need 128M of heap to run my looping searches with. CREATE TABLE `fsearch_searchheap` ( `searchAffid` int(11) NOT NULL default '0', `searchKeyword` varchar(100) NOT NULL default '', `searchReferrer` varchar(100) NOT NULL default '', `searchIp` varchar(15) NOT NULL default '', KEY `searchAffid` (`searchAffid`), KEY `searchKeyword` (`searchKeyword`) ) ENGINE=MEMORY DEFAULT Select * from fsearch_search; Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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: adding an incremented count from page hits
On Wednesday 23 March 2005 20:00, Chip Wiegand wrote: count=count+1; --- This is the added bit --- ? --- The count doesn't increment in the database. The table has a column called 'count'. Two things: variables start with a $ and the variable count in your example does not... this means error!, change the count to $count 2nd, You should update your table like this: mysql_query(UPDATE `manuals_list` SET `count`='$count' WHERE ...selection here... LIMIT 1;); Also, I see that you are ignoring mysql security! Please think about sql injection attacks. You should use the mysql_escape_string function on any and all user inputed values, use backticks to delimit database/table/field names, and quotes to delimit values. Can someone please please point me in the right direction? Thanks, http://dev.mysql.com/doc/mysql/ Andy -- Registered Linux User Number 379093 -- Feel free to check out these few php utilities that I released under the GPL2 and that are meant for use with a php cli binary: http://www.vlaamse-kern.com/sas/ -- pgpVeoCP0qrd6.pgp Description: PGP signature
RE: using between
Well, we have this db with various ip address ranges and the country of origin associated with each ... the format is: countryOfOrigin FromIP ToIP --- -- US some lower boundsome upper bound Canada some lower boundsome upper bound Etc... So the real statement would be: Select countryOfOrigin From IPRangeTable where targetIP between FromIP and ToIP Obviously, this looks at every record to see if targetIP is in the range. I'm just trying to think of a better way to do it The ranges are mutually exclusive so once it finds it, that would be it. I guess I could put a 'limit 1' on there to get it to quit once it finds it. Is there something better? -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 2:36 PM To: MySQL list Subject: Re: using between At 01:48 PM 3/25/2005, you wrote: Hello Is there a way when searching for a range of values for a particular field that mysql would not have to look at the entire table ... I'm guessing with some type of composite key or something? ... e.g. SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; Field1 and field2 are indexed but that doesn't help because you're looking for a range of values instead of a particular value Rob, I'm having a hard time wrapping my head around your example.It's like the tail wagging the dog.g Putting variables and columns to your syntax I get: select cust_num from table_invoices where 5 between invoice_amt and tax; I think you meant to say: select aCol from aTable where aCol between val1 and val2; If aCol is indexed, then MySQL will use the index to get a Range on val1 and val2. Just use Explain and you'll see the index that it's using. Mike -- 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: using one query to save data in 4 tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious if this would be possible, and which version of mysql would be needed. Basically, I want to store a user in one table, then get the id for the user I just saved, and store three more rows, each in a different database, using the user's id in the insert statements. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCRH8iikQgpVn8xrARAsw5AJ0SxPoMMcy1QUa1GNNEdfg51Q7Q8ACfYHg7 NdFWteuQU4JjSfx7yYS++9k= =JZD5 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using between
Have you considered just doing a parse on the the IP ranges and having 8 columns in your database, then write your query to work inside the 8 columns Cols = from_zone, from_net, from_subnet, from_node, to_zone, to_net, to_subnet, to_node Then just parse the ip you are looking up and write your query that way. MySQL should beable to reduce the amount of rows it needs to look at pretty quickly this way. Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 1:05 PM To: 'mos'; 'MySQL list' Subject: RE: using between Well, we have this db with various ip address ranges and the country of origin associated with each ... the format is: countryOfOrigin FromIP ToIP --- -- US some lower boundsome upper bound Canada some lower boundsome upper bound Etc... So the real statement would be: Select countryOfOrigin From IPRangeTable where targetIP between FromIP and ToIP Obviously, this looks at every record to see if targetIP is in the range. I'm just trying to think of a better way to do it The ranges are mutually exclusive so once it finds it, that would be it. I guess I could put a 'limit 1' on there to get it to quit once it finds it. Is there something better? -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 2:36 PM To: MySQL list Subject: Re: using between At 01:48 PM 3/25/2005, you wrote: Hello Is there a way when searching for a range of values for a particular field that mysql would not have to look at the entire table ... I'm guessing with some type of composite key or something? ... e.g. SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; Field1 and field2 are indexed but that doesn't help because you're looking for a range of values instead of a particular value Rob, I'm having a hard time wrapping my head around your example.It's like the tail wagging the dog.g Putting variables and columns to your syntax I get: select cust_num from table_invoices where 5 between invoice_amt and tax; I think you meant to say: select aCol from aTable where aCol between val1 and val2; If aCol is indexed, then MySQL will use the index to get a Range on val1 and val2. Just use Explain and you'll see the index that it's using. Mike -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL C API problems
I'm having some difficulty with a program I wrote which uses the MySQL C api. I'm trying to migrate my server from version 4.0 to version 4.1. I have no difficulty connecting to the server using the mysql.exe command line utility, but when I try to connect from my program using the same parameters, it fails to connect with the Error 1045 (Access denied for user). I use the mysqlclient.lib library to link to my windows program. The library I have is a bit old, but I can't seem to figure out which version it is easily. Is the solution as simple as getting the new developers libraries? Specific versions are below. works with : 4.0.16-standard server doesn't work with: 4.1.7-standard server - Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL C API problems
Password hashing was changed in 4.1 to improve authentication security, but this complicates backward compatibility. See the manual for details: http://dev.mysql.com/doc/mysql/en/old-client.html http://dev.mysql.com/doc/mysql/en/password-hashing.html Your best bet is to link your app against the 4.1 client library. Michael Andrew Prock wrote: I'm having some difficulty with a program I wrote which uses the MySQL C api. I'm trying to migrate my server from version 4.0 to version 4.1. I have no difficulty connecting to the server using the mysql.exe command line utility, but when I try to connect from my program using the same parameters, it fails to connect with the Error 1045 (Access denied for user). I use the mysqlclient.lib library to link to my windows program. The library I have is a bit old, but I can't seem to figure out which version it is easily. Is the solution as simple as getting the new developers libraries? Specific versions are below. works with : 4.0.16-standard server doesn't work with: 4.1.7-standard server - Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext boolean question
Hi, How can I limit the results of a fulltext search to the entries that only have the keywords I'm searching for? I'm searching the title field of a product database and I only want results if the title has all of the words specified. I tried putting the word AND between each word and mysql responds with products even when the title only has one of the key words. Here's an example query: select *, ( match(title) against ('dragon and ice and blue' IN BOOLEAN MODE) ) as score from products where ( match(title) against ('dragon and ice and blue' IN BOOLEAN MODE) ) order by score DESC This query returns products with titles like Land of Fire and Ice. Since that product title doesn't have the word dragon in it, I don't understand why it's even showing up in the results set (and I'd like it to stop if that's possible). Thank, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlconnecernet not closing connections w/2003
We have a quote server on both a 2003 server and an XP machine. The quote server is an asp.net server. The XP machine is closing the connections without a problem. For some reason the mysqlconnecernet is not closing the connections when used on the 2003 server. It connection count climbs to 100 our maximum connection setting, and then we get a Time out when attempting to get a connection from the pool Has anyone run into this problem? Any help you can give use will be greatly appreciated. TIA Dan
Re: Fulltext boolean question
Try using: +dragon +ice +blue I'm fairly sure that's in the FULLTEXT search portion of the online docs. Chris Ed Lazor wrote: Hi, How can I limit the results of a fulltext search to the entries that only have the keywords I'm searching for? I'm searching the title field of a product database and I only want results if the title has all of the words specified. I tried putting the word AND between each word and mysql responds with products even when the title only has one of the key words. Here's an example query: select *, ( match(title) against ('dragon and ice and blue' IN BOOLEAN MODE) ) as score from products where ( match(title) against ('dragon and ice and blue' IN BOOLEAN MODE) ) order by score DESC This query returns products with titles like Land of Fire and Ice. Since that product title doesn't have the word dragon in it, I don't understand why it's even showing up in the results set (and I'd like it to stop if that's possible). Thank, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL C API problems
On Fri, 25 Mar 2005, Michael Stassen wrote: Password hashing was changed in 4.1 to improve authentication security, but this complicates backward compatibility. See the manual for details: http://dev.mysql.com/doc/mysql/en/old-client.html http://dev.mysql.com/doc/mysql/en/password-hashing.html Your best bet is to link your app against the 4.1 client library. I just tried this. Or I think I did. I uninstalled, and reinstalled mysql v4.1.10a, which I assume has the 4.1 client library. I recompiled, relinked, and I get the same error. Again, I can connect using the mysql CLI, it's just connecting through the API which is giving me problems. I can connect to the 4.1.1 server with the mysql CLI, but only the 4.0 server with the API. I did have to make one minor change to the code to get it to recompile. Is there some other API issue which I need to be aware of? The code I use to connect is: _mysqlconn = mysql_init (NULL); if (_mysqlconn == NULL) { print_error (NULL, mysql_init() failed (probably out of memory)); return -1; } /* connect to server */ if (mysql_real_connect (_mysqlconn, host_name, user_name, password, database_name, opt_port_num, opt_socket_name, opt_flags) == NULL) { print_error (_mysqlconn, mysql_real_connect() failed); mysql_close (_mysqlconn); _mysqlconn = NULL; return -1; } And the failure occurs during mysql_real_connect with error 1045 Access denied for user: and not the Client does not support authentication protocol requested by server; consider upgrading MySQL client that the web-page indicates that I might expect. Again, I *can* connect with the mysql CLI, it is only from the API where I'm having difficulty. Referencing the web page, it appears that the 4.1 server is using 16-byte passwords, and not the 41-byte passwords. It might be that the server was started with --old-passwords option, but how can I find out if that is the case? It might be that the API is generating long password values, when I need short password values. Can I control this behaviour through the API? - Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext boolean question
Oh hey, very cool - thanks. I was assuming that + and AND were the same thing, guess not =) -Ed -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 4:21 PM To: mysql@lists.mysql.com Subject: Re: Fulltext boolean question Try using: +dragon +ice +blue I'm fairly sure that's in the FULLTEXT search portion of the online docs. Chris Ed Lazor wrote: Hi, How can I limit the results of a fulltext search to the entries that only have the keywords I'm searching for? I'm searching the title field of a product database and I only want results if the title has all of the words specified. I tried putting the word AND between each word and mysql responds with products even when the title only has one of the key words. Here's an example query: select *, ( match(title) against ('dragon and ice and blue' IN BOOLEAN MODE) ) as score from products where ( match(title) against ('dragon and ice and blue' IN BOOLEAN MODE) ) order by score DESC This query returns products with titles like Land of Fire and Ice. Since that product title doesn't have the word dragon in it, I don't understand why it's even showing up in the results set (and I'd like it to stop if that's possible). Thank, Ed -- 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 C API problems
On Fri, 25 Mar 2005, Andrew Prock wrote: On Fri, 25 Mar 2005, Michael Stassen wrote: Password hashing was changed in 4.1 to improve authentication security, but this complicates backward compatibility. See the manual for details: http://dev.mysql.com/doc/mysql/en/old-client.html http://dev.mysql.com/doc/mysql/en/password-hashing.html Your best bet is to link your app against the 4.1 client library. Thank you for your assitance. I have determined that amidst all my horrid code there is a bug that is totally unrelated to MySQL, and it's version number, but that was related to port numbers. Since both servers are at the same domain, they are accessed through different ports, and while I thought I was handling ports correctly, I was always using the default port. Thanks again, - Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 MySQL Errors
I can't figure out why I get an error message relating to fiels borderstates and bordernotes when I try to create a new table. One is VARCHAR, the other TEXT, and both are NULL, like almost every field on the table. Can someone tell me what I'm doing wrong? Thanks. SQL-query: CREATE TABLE `geog` ( `NID` INT( 3 ) NOT NULL AUTO_INCREMENT , `IDArea` CHAR( 3 ) NOT NULL , `Name` VARCHAR( 50 ) NOT NULL , `coordNS` VARCHAR( 7 ) DEFAULT NULL , `coordNS2` VARCHAR( 3 ) DEFAULT NULL , `coordEW` VARCHAR( 8 ) DEFAULT NULL , `coordEW2` VARCHAR( 3 ) DEFAULT NULL , `coordnotes` TEXT DEFAULT NULL , `location` VARCHAR( 255 ) DEFAULT NULL , `mapref` VARCHAR( 33 ) DEFAULT NULL , `areak` DECIMAL( 9, 2 ) DEFAULT NULL , `arealandk` DECIMAL( 9, 2 ) DEFAULT NULL , `areawaterk` DECIMAL( 9, 2 ) DEFAULT NULL , `areanotes` VARCHAR( 255 ) DEFAULT NULL , `aracomp` VARCHAR( 100 ) DEFAULT NULL , `borderk` INT( 6, 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes` TEXT DEFAULT NULL , `coastk` DECIMAL( 6.1 ) DEFAULT NULL , `coastnotes` TEXT DEFAULT NULL , `martersea` INT( 3 ) DEFAULT NULL , `marconzone` INT( 2 ) DEFAULT NULL , `marconshelf` VARCHAR( 255 ) DEFAULT NULL , `marexecon` INT( 3 ) DEFAULT NULL , `marexfish` INT( 3 ) DEFAULT NULL , `marnotes` VARCHAR( 255 ) DEFAULT NULL , `climate` VARCHAR( 255 ) DEFAULT NULL , `elevhighpt` VARCHAR( 255 ) DEFAULT NULL , `elevhighm` INT( 4 ) DEFAULT NULL , `elevlowpt` VARCHAR( 255 ) DEFAULT NULL , `elevlowm` INT( 4 ) DEFAULT NULL , `elevnotes` VARCHAR( 255 ) DEFAULT NULL , `terrain` TEXT DEFAULT NULL , `natres` TEXT DEFAULT NULL , `nathaz` TEXT DEFAULT NULL , `envagree` TEXT DEFAULT NULL , `envagree2` VARCHAR( 255 ) DEFAULT NULL , `envissues` TEXT DEFAULT NULL , `landuse` INT( 2, 2 ) DEFAULT NULL , `landusecrops` INT( 2, 2 ) DEFAULT NULL , `landuseother` INT( 2, 2 ) DEFAULT NULL , `landusenotes` VARCHAR( 100 ) DEFAULT NULL , `irrigate` INT( 6, 2 ) DEFAULT NULL , `irrigatenotes` VARCHAR( 255 ) DEFAULT NULL , `geognotes` TEXT DEFAULT NULL , PRIMARY KEY ( `NID` ) , INDEX ( `IDArea` ) ) MySQL said: Documentation #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 '1) DEFAULT NULL, `borderstates` VARCHAR(255) DEFAULT NULL, `bordernotes` TEXT DE' at line 1 __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 MySQL Errors
On Fri, Mar 25, 2005 at 07:27:47PM -0800, David Blomstrom wrote: I can't figure out why I get an error message relating to fiels borderstates and bordernotes when I try to create a new table. One is VARCHAR, the other TEXT, and both are NULL, like almost every field on the table. Can someone tell me what I'm doing wrong? Thanks. [snip] `borderk` INT( 6, 1 ) DEFAULT NULL , This is your problem. INT does not take two parameters. [ snip ] MySQL said: Documentation #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 '1) DEFAULT NULL, `borderstates` VARCHAR(255) DEFAULT NULL, `bordernotes` TEXT DE' at line 1 That's why the error message says near 1) Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recovering Lost Database
If you accidentally drop a database instead of a database TABLE in phpMyAdmin, is there any way to recover the lost database, or is it gone forever? Thanks. __ 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]
Re: Recovering Lost Database
In the last episode (Mar 25), David Blomstrom said: If you accidentally drop a database instead of a database TABLE in phpMyAdmin, is there any way to recover the lost database, or is it gone forever? Only if you have a backup... -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recovering Lost Database
--- Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Mar 25), David Blomstrom said: If you accidentally drop a database instead of a database TABLE in phpMyAdmin, is there any way to recover the lost database, or is it gone forever? Only if you have a backup... Thanks. -- Dan Nelson [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Create Table Error
Can someone tell me what I'm doing wrong? When I first tried to create this table, I got an error message pointing out two fields that looked perfectly fine to me. I couldn't figure it out, so I just deleted the fields - but it then fingered two different fields. I deleted them, and now it says there's a problem with two other fields. I don't get it. Thanks. CREATE TABLE `geog` ( `NID` INT( 3 ) NOT NULL AUTO_INCREMENT , `IDArea` CHAR( 3 ) NOT NULL , `Name` VARCHAR( 50 ) NOT NULL , `coordNS` VARCHAR( 7 ) DEFAULT NULL , `coordNS2` VARCHAR( 3 ) DEFAULT NULL , `coordEW` VARCHAR( 8 ) DEFAULT NULL , `coordEW2` VARCHAR( 3 ) DEFAULT NULL , `coordnotes` TEXT DEFAULT NULL , `location` VARCHAR( 255 ) DEFAULT NULL , `mapref` VARCHAR( 33 ) DEFAULT NULL , `areak` DECIMAL( 9, 2 ) DEFAULT NULL , `arealandk` DECIMAL( 9, 2 ) DEFAULT NULL , `areawaterk` DECIMAL( 9, 2 ) DEFAULT NULL , `areanotes` VARCHAR( 255 ) DEFAULT NULL , `aracomp` VARCHAR( 100 ) DEFAULT NULL , `borderk` INT( 6, 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes` TEXT DEFAULT NULL , `coastk` DECIMAL( 6.1 ) DEFAULT NULL , `coastnotes` TEXT DEFAULT NULL , `martersea` INT( 3 ) DEFAULT NULL , `marconzone` INT( 2 ) DEFAULT NULL , `marconshelf` VARCHAR( 255 ) DEFAULT NULL , `marexecon` INT( 3 ) DEFAULT NULL , `marexfish` INT( 3 ) DEFAULT NULL , `marnotes` VARCHAR( 255 ) DEFAULT NULL , `climate` VARCHAR( 255 ) DEFAULT NULL , `elevhighpt` VARCHAR( 255 ) DEFAULT NULL , `elevhighm` INT( 4 ) DEFAULT NULL , `elevlowpt` VARCHAR( 255 ) DEFAULT NULL , `elevlowm` INT( 4 ) DEFAULT NULL , `elevnotes` VARCHAR( 255 ) DEFAULT NULL , `terrain` TEXT DEFAULT NULL , `natres` TEXT DEFAULT NULL , `nathaz` TEXT DEFAULT NULL , `envagree` TEXT DEFAULT NULL , `envagree2` VARCHAR( 255 ) DEFAULT NULL , `envissues` TEXT DEFAULT NULL , `landuse` INT( 2, 2 ) DEFAULT NULL , `landusecrops` INT( 2, 2 ) DEFAULT NULL , `landuseother` INT( 2, 2 ) DEFAULT NULL , `landusenotes` VARCHAR( 100 ) DEFAULT NULL , `irrigate` INT( 6, 2 ) DEFAULT NULL , `irrigatenotes` VARCHAR( 255 ) DEFAULT NULL , `geognotes` TEXT DEFAULT NULL , PRIMARY KEY ( `NID` ) , INDEX ( `IDArea` ) ) MySQL said: Documentation #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 ' 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes`' at line 17 __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Create Table Error
Please ignore this thread. I think my main problem is I need glasses! --- David Blomstrom [EMAIL PROTECTED] wrote: Can someone tell me what I'm doing wrong? When I first tried to create this table, I got an error message pointing out two fields that looked perfectly fine to me. I couldn't figure it out, so I just deleted the fields - but it then fingered two different fields. I deleted them, and now it says there's a problem with two other fields. I don't get it. Thanks. CREATE TABLE `geog` ( `NID` INT( 3 ) NOT NULL AUTO_INCREMENT , `IDArea` CHAR( 3 ) NOT NULL , `Name` VARCHAR( 50 ) NOT NULL , `coordNS` VARCHAR( 7 ) DEFAULT NULL , `coordNS2` VARCHAR( 3 ) DEFAULT NULL , `coordEW` VARCHAR( 8 ) DEFAULT NULL , `coordEW2` VARCHAR( 3 ) DEFAULT NULL , `coordnotes` TEXT DEFAULT NULL , `location` VARCHAR( 255 ) DEFAULT NULL , `mapref` VARCHAR( 33 ) DEFAULT NULL , `areak` DECIMAL( 9, 2 ) DEFAULT NULL , `arealandk` DECIMAL( 9, 2 ) DEFAULT NULL , `areawaterk` DECIMAL( 9, 2 ) DEFAULT NULL , `areanotes` VARCHAR( 255 ) DEFAULT NULL , `aracomp` VARCHAR( 100 ) DEFAULT NULL , `borderk` INT( 6, 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes` TEXT DEFAULT NULL , `coastk` DECIMAL( 6.1 ) DEFAULT NULL , `coastnotes` TEXT DEFAULT NULL , `martersea` INT( 3 ) DEFAULT NULL , `marconzone` INT( 2 ) DEFAULT NULL , `marconshelf` VARCHAR( 255 ) DEFAULT NULL , `marexecon` INT( 3 ) DEFAULT NULL , `marexfish` INT( 3 ) DEFAULT NULL , `marnotes` VARCHAR( 255 ) DEFAULT NULL , `climate` VARCHAR( 255 ) DEFAULT NULL , `elevhighpt` VARCHAR( 255 ) DEFAULT NULL , `elevhighm` INT( 4 ) DEFAULT NULL , `elevlowpt` VARCHAR( 255 ) DEFAULT NULL , `elevlowm` INT( 4 ) DEFAULT NULL , `elevnotes` VARCHAR( 255 ) DEFAULT NULL , `terrain` TEXT DEFAULT NULL , `natres` TEXT DEFAULT NULL , `nathaz` TEXT DEFAULT NULL , `envagree` TEXT DEFAULT NULL , `envagree2` VARCHAR( 255 ) DEFAULT NULL , `envissues` TEXT DEFAULT NULL , `landuse` INT( 2, 2 ) DEFAULT NULL , `landusecrops` INT( 2, 2 ) DEFAULT NULL , `landuseother` INT( 2, 2 ) DEFAULT NULL , `landusenotes` VARCHAR( 100 ) DEFAULT NULL , `irrigate` INT( 6, 2 ) DEFAULT NULL , `irrigatenotes` VARCHAR( 255 ) DEFAULT NULL , `geognotes` TEXT DEFAULT NULL , PRIMARY KEY ( `NID` ) , INDEX ( `IDArea` ) ) MySQL said: Documentation #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 ' 1 ) DEFAULT NULL , `borderstates` VARCHAR( 255 ) DEFAULT NULL , `bordernotes`' at line 17 __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]