MySQL My program
Dear all, I have a JSP program whish is using MySQL database engine. It was working successfully,Now it doesn't work. Note: When i use mysqlshow command , i see test bank only.But if i use mysql shell use show databases,I can see all my databases. It's emergency. Please help me -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL My program
Mohsen Pahlevanzadeh wrote: Dear all, I have a JSP program whish is using MySQL database engine. It was working successfully,Now it doesn't work. Note: When i use mysqlshow command , i see test bank only.But if i use mysql shell use show databases,I can see all my databases. It's emergency. Please help me Now when i use --skip-grant-tables i can see my databases. Please help me... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL My program
Mohsen, First off, what version of MySQL are you running and on what platform? Now when i use --skip-grant-tables i can see my databases. Please help me... When you use MySQL via a shell I would assume you're logging in as root? You also say you can only see a test database, can you not see a mysql database there as well? I would suggest that somehow either the mysql database has been dropped/destroyed or perhaps just the db, host, tables_priv, and user tables of that database have been dropped/destroyed/messed up. Log in via shell and show us the result of the following commands... mysql use mysql; mysql show tables; mysql SELECT COUNT(*) FROM db; mysql SELECT COUNT(*) FROM user; If you can't see the database and/or the tables I'm talking about you may want to just check your backups are working and come back to us. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL My program
Mohsen, First off, what version of MySQL are you running and on what platform? Now when i use --skip-grant-tables i can see my databases. Please help me... When you use MySQL via a shell I would assume you're logging in as root? You also say you can only see a test database, can you not see a mysql database there as well? I would suggest that somehow either the mysql database has been dropped/destroyed or perhaps just the db, host, tables_priv, and user tables of that database have been dropped/destroyed/messed up. Log in via shell and show us the result of the following commands... mysql use mysql; mysql show tables; mysql SELECT COUNT(*) FROM db; mysql SELECT COUNT(*) FROM user; If you can't see the database and/or the tables I'm talking about you may want to just check your backups are working and come back to us. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL My program
Mohsen, I'm not sure you're receiving any of this as you also seem to have a rather over-eager spam filter as well... Symantec Mail Security detected prohibited content in a message sent from your address (SYM:40763633734165155763) Subject of the message: Re: MySQL My program Recipient of the message: [EMAIL PROTECTED] [EMAIL PROTECTED];mysql@lists.mysql.com mysql@lists.mysql.com ...which is a bit odd as I sent the second message as plain-old-text and nothing else. Sorry for the double postings to the list by the way. shrug / Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Data Vanishing with FireFox
I think it's your PHP application; how did you debug your application? All this is doing is letting our customer add their contacts to the database. This is on the quick add form and asks them to enter a first and last name and an email. I debugged by re-writing it temporarily to do this... $SQL = 'INSERT INTO Contacts (ContFirst,ContLast,ContEmail,UserID) VALUES (' .$_POST[ContFirst].','.$_POST[ContLast] .','.$_POST[ContEmail].','.$MyID.')'; echo $SQL; $result = mysql_query($SQL,$db); On the theory that the echo shouldn't change the contents of the $SQL variable. Then on the same client machine, I open both browsers and launch the page. In both browsers I can see the exact same statement (including the value of $MyID) on the screen but in IE it works and on FireFox it doesn't. As soon as I see the SQL on the screen I query directly (not through PHP) to pull out the records and see that UserID is missing. What data type is column 'C'? Is it ENUM? Nope. Nothing compilicated... ContFirst,ContLast are Char(30), ContEmail is Char(100), UserID (the one failing) is INT(4). The typical values of UserID (at least when debugging, and still failing) were numbers from 1-20. Again... since I'm seeing the exact same statement on the screen in the echo, theory has it that the exact same statement is being passed into the mysql_query in the next line. This is why I'm so baffled. Normally I echo the actual statement and it's obvious what my error is. (Oh... a comma...) OR I echo the statement and see that FireFox handles cookies different, or passes form values different, or truncates $_GET at a different length or some other equally obscure thing but still that the STATEMENT differs and I have something to start tracing from. Here the statement is identical on both browsers and one fails and the other doesn't. Any other ideas? Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Data Vanishing with FireFox
Did you make a mistake when showing us your PHP code? There's a missing single quote after the period on the second line of you $SQL = Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Matt Neimeyer [mailto:[EMAIL PROTECTED] Sent: Thursday, December 28, 2006 9:33 AM To: mysql@lists.mysql.com Subject: Re: MySQL Data Vanishing with FireFox I think it's your PHP application; how did you debug your application? All this is doing is letting our customer add their contacts to the database. This is on the quick add form and asks them to enter a first and last name and an email. I debugged by re-writing it temporarily to do this... $SQL = 'INSERT INTO Contacts (ContFirst,ContLast,ContEmail,UserID) VALUES (' .$_POST[ContFirst].','.$_POST[ContLast] .','.$_POST[ContEmail].','.$MyID.')'; echo $SQL; $result = mysql_query($SQL,$db); On the theory that the echo shouldn't change the contents of the $SQL variable. Then on the same client machine, I open both browsers and launch the page. In both browsers I can see the exact same statement (including the value of $MyID) on the screen but in IE it works and on FireFox it doesn't. As soon as I see the SQL on the screen I query directly (not through PHP) to pull out the records and see that UserID is missing. What data type is column 'C'? Is it ENUM? Nope. Nothing compilicated... ContFirst,ContLast are Char(30), ContEmail is Char(100), UserID (the one failing) is INT(4). The typical values of UserID (at least when debugging, and still failing) were numbers from 1-20. Again... since I'm seeing the exact same statement on the screen in the echo, theory has it that the exact same statement is being passed into the mysql_query in the next line. This is why I'm so baffled. Normally I echo the actual statement and it's obvious what my error is. (Oh... a comma...) OR I echo the statement and see that FireFox handles cookies different, or passes form values different, or truncates $_GET at a different length or some other equally obscure thing but still that the STATEMENT differs and I have something to start tracing from. Here the statement is identical on both browsers and one fails and the other doesn't. Any other ideas? Matt -- 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 Data Vanishing with FireFox
Matt, $SQL = 'INSERT INTO Contacts (ContFirst,ContLast,ContEmail,UserID) VALUES (' .$_POST[ContFirst].','.$_POST[ContLast] .','.$_POST[ContEmail].','.$MyID.')'; echo $SQL; $result = mysql_query($SQL,$db); If userid is an int, why quote the $myid value? Did you check what's echoed against the server log? PB - Matt Neimeyer wrote: I think it's your PHP application; how did you debug your application? All this is doing is letting our customer add their contacts to the database. This is on the quick add form and asks them to enter a first and last name and an email. I debugged by re-writing it temporarily to do this... $SQL = 'INSERT INTO Contacts (ContFirst,ContLast,ContEmail,UserID) VALUES (' .$_POST[ContFirst].','.$_POST[ContLast] .','.$_POST[ContEmail].','.$MyID.')'; echo $SQL; $result = mysql_query($SQL,$db); On the theory that the echo shouldn't change the contents of the $SQL variable. Then on the same client machine, I open both browsers and launch the page. In both browsers I can see the exact same statement (including the value of $MyID) on the screen but in IE it works and on FireFox it doesn't. As soon as I see the SQL on the screen I query directly (not through PHP) to pull out the records and see that UserID is missing. What data type is column 'C'? Is it ENUM? Nope. Nothing compilicated... ContFirst,ContLast are Char(30), ContEmail is Char(100), UserID (the one failing) is INT(4). The typical values of UserID (at least when debugging, and still failing) were numbers from 1-20. Again... since I'm seeing the exact same statement on the screen in the echo, theory has it that the exact same statement is being passed into the mysql_query in the next line. This is why I'm so baffled. Normally I echo the actual statement and it's obvious what my error is. (Oh... a comma...) OR I echo the statement and see that FireFox handles cookies different, or passes form values different, or truncates $_GET at a different length or some other equally obscure thing but still that the STATEMENT differs and I have something to start tracing from. Here the statement is identical on both browsers and one fails and the other doesn't. Any other ideas? Matt -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.28/605 - Release Date: 12/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Data Vanishing with FireFox
I see it was my mistake, not yours. I'm going to shut up now. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Matt Neimeyer [mailto:[EMAIL PROTECTED] Sent: Thursday, December 28, 2006 9:33 AM To: mysql@lists.mysql.com Subject: Re: MySQL Data Vanishing with FireFox I think it's your PHP application; how did you debug your application? All this is doing is letting our customer add their contacts to the database. This is on the quick add form and asks them to enter a first and last name and an email. I debugged by re-writing it temporarily to do this... $SQL = 'INSERT INTO Contacts (ContFirst,ContLast,ContEmail,UserID) VALUES (' .$_POST[ContFirst].','.$_POST[ContLast] .','.$_POST[ContEmail].','.$MyID.')'; echo $SQL; $result = mysql_query($SQL,$db); On the theory that the echo shouldn't change the contents of the $SQL variable. Then on the same client machine, I open both browsers and launch the page. In both browsers I can see the exact same statement (including the value of $MyID) on the screen but in IE it works and on FireFox it doesn't. As soon as I see the SQL on the screen I query directly (not through PHP) to pull out the records and see that UserID is missing. What data type is column 'C'? Is it ENUM? Nope. Nothing compilicated... ContFirst,ContLast are Char(30), ContEmail is Char(100), UserID (the one failing) is INT(4). The typical values of UserID (at least when debugging, and still failing) were numbers from 1-20. Again... since I'm seeing the exact same statement on the screen in the echo, theory has it that the exact same statement is being passed into the mysql_query in the next line. This is why I'm so baffled. Normally I echo the actual statement and it's obvious what my error is. (Oh... a comma...) OR I echo the statement and see that FireFox handles cookies different, or passes form values different, or truncates $_GET at a different length or some other equally obscure thing but still that the STATEMENT differs and I have something to start tracing from. Here the statement is identical on both browsers and one fails and the other doesn't. Any other ideas? Matt -- 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 Data Vanishing with FireFox
Matt Neimeyer wrote: I think it's your PHP application; how did you debug your application? All this is doing is letting our customer add their contacts to the database. This is on the quick add form and asks them to enter a first and last name and an email. I debugged by re-writing it temporarily to do this... $SQL = 'INSERT INTO Contacts (ContFirst,ContLast,ContEmail,UserID) VALUES (' .$_POST[ContFirst].','.$_POST[ContLast] .','.$_POST[ContEmail].','.$MyID.')'; echo $SQL; $result = mysql_query($SQL,$db); On the theory that the echo shouldn't change the contents of the $SQL variable. Then on the same client machine, I open both browsers and launch the page. In both browsers I can see the exact same statement (including the value of $MyID) on the screen but in IE it works and on FireFox it doesn't. As soon as I see the SQL on the screen I query directly (not through PHP) to pull out the records and see that UserID is missing. If UserID is missing ($MyID) track back and see what sets this. If $MyID is missing, it may/may not be the browsers fault. (You've not given us enough code to really help. I have 20 or so 'simple form' apps here all work fine with both IE and FireFox (inserts/updates/deletes/selects) in both PHP and MSASP. ) Can you paste in both statements fully filled out from both browsers? So we can see what the data is looking like (same data.) As an aside, you may want to be doing SQL injection checking or sanitizing if you aren't already doing so. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Filling blanks on SELECT
Thanks to Chris yesterday, I managed to figure some things out by myself. Now I'm faced with another problem. Given the same database again: ++---+--+-+---++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+---++ | uid| mediumint(8) unsigned | NO | PRI | NULL | auto_increment | | temp_f | float(4,1)| YES | | NULL || | temp_c | float(4,1)| YES | | NULL || | windchill | float(4,1)| YES | | NULL || | dewpoint | float(4,1)| YES | | NULL || | time | timestamp | NO | | CURRENT_TIMESTAMP || ++---+--+-+---++ I gather data from it based on select intervals and dump the result into arrays. My problem is what to do if there's a gap. For example, let's assume I have data for 10:11, 10:12, 10:14, 10:15, 10:20. Notice the missing ones in there, 10:13, 10:16 to 10:19 - say the server had a hiccup and couldn't gather info. Now, I'm doing a 60 minute select based on Chris' suggestion as follows: select hour(time) as the_hour, minute(time) as the_minute, avg(temp_f) as average_temp_f from data where time now() - interval 60 minute group by the_hour, the_minute; After that I put it all into an array (or arrays since that's what the resulting code needs to be fed into something else) ?php while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $mins[] = $row['the_minute']; $temp_f[] = $row['avg_temp_f']; $temp_c[] = $row['avg_temp_c']; } ? These arrays then get fed into a charting program. The problem is that it (the chart) has no idea that there are blanks in there. It takes the values as they're fed. So my question is, is there a way to have MySQL return an empty record for the gaps? If I'm asking for 60 records, and there are only 55, is there some way of figuring out (within MySQL) which ones aren't there and return a blank instead? Am I going way outside the scope of MySQL here? -- W | It's not a bug - it's an undocumented feature. + Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 IT Director / SysAdmin / Websmith . 800.441.3873 x130 Photo Craft Imaging . 3550 Arapahoe Ave. #6 http://www.pcraft.com . . .. Boulder, CO 80303, U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: innodb_log_files_in_group
Hi Juan, The default (and recommended) is 2. The log files, save the trasactions into file in circular order. This files are like a redolog files in oracle. This log file are useful when you recover your database after some crash for example or when you use a replication mysql. innodb_buffer_pool_size you don`t have any restrictions ( normally 80% of phisical mem). Now, for the innodb_log_file_size ( 25% of buffer_pool_size) , the combined size of the InnoDB log files must not exceed 4 GB. If you have 2 log files, you can make each at most 2000 MB in size. When you startup your MySQL using innodb, in your my.cnf you can set the variable innodb_log_files_in_group. For example, if your set innodb_log_files_in_group=2 ( ib_log_file001, ib_log_file002 are created) the total size must not exceed 4G. Thanks, I think I recall reading that text before. It doesn't really answer my question though. Why is this option there? Is it really of no benefit? Why was time spent coding and debugging the feature to have more than 2 logfiles if there is no benefit of doing it? If this options is useless why wasn't it removed in MySQL 5.x? Cheers, ds Is there any benefit/reason to set innodb_log_files_in_group to something other than 2? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filling blanks on SELECT
Ashley M. Kirchner wrote: ?php while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $mins[] = $row['the_minute']; $temp_f[] = $row['avg_temp_f']; $temp_c[] = $row['avg_temp_c']; } ? I'd try php here, something like: ?php while($row = mysql_fetch_array($result,MYSQL_ASSOC)) { $mins[] = empty($row['the_minute']) ? 'blank value' : $row['the_minute']; $temp_f[] = empty($row['avg_temp_f']) ? 'blank value' : $row['avg_temp_f']; $temp_c[] = empty($row['avg_temp_c']) ? 'blank value' : $row['avg_temp_c']; } ? Of course, you'd want to replace blank value with whatever a blank record is (0, '', etc.). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Re: Filling blanks on SELECT
Chris White wrote: I'd try php here, something like: Problem is, PHP doesn't know which record is blank. I select for 60 records and MySQL returns 55. How is PHP supposed to know which 5 are blank? -- W | It's not a bug - it's an undocumented feature. + Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 IT Director / SysAdmin / Websmith . 800.441.3873 x130 Photo Craft Imaging . 3550 Arapahoe Ave. #6 http://www.pcraft.com . . .. Boulder, CO 80303, U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Re: Filling blanks on SELECT
In the last episode (Dec 28), Ashley M. Kirchner said: Chris White wrote: I'd try php here, something like: Problem is, PHP doesn't know which record is blank. I select for 60 records and MySQL returns 55. How is PHP supposed to know which 5 are blank? Mysql doesn't know either. All it's doing is storing numbers. Is it possible to pass the timestamp to your graphing program and tell it to use time as its X-axis (both gnuplot and ploticus can, for example)? If plotted as a bar chart with 1-minute-wide bars, missing records will just be blanks. If not, you'll probably have to read the records and store them in an array indexed by minute. Then when you're done reading from mysql, walk the array from 0 to 59 and write out each element. That way you're guaranteed 60 output rows. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bizarre InnoDB Error Message ( trx-active_trans == 0, but trx-conc_state != TRX_NOT_STARTED)
Hello, I noticed the error messages below in my MySQL error log and found them a bit perplexing. Can't find anything on them in the MySQL documentation. If anyone has any clue what they mean it is greatly appreciated. As a sidenote, SHOW INNODB STATUS completes, but only shows through the DEADLOCK section. It almost appears as if the deadlock listing is so long that it runs out of buffer and doesn't get to the TRANSACTIONS or other status sections. Thank you in advance! Best Regards, Jason ---ERROR MESSAGE--- 061228 19:02:55 [ERROR] trx-active_trans == 0, but trx-conc_state != TRX_NOT_STARTED 061228 19:02:55 [Warning] MySQL is closing a connection that has an active InnoDB transaction. 0 row modifications will roll back. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MySQL] Re: Filling blanks on SELECT
Dan Nelson wrote: If not, you'll probably have to read the records and store them in an array indexed by minute. Then when you're done reading from mysql, walk the array from 0 to 59 and write out each element. That way you're guaranteed 60 output rows. Eh, did something different. Now I'm opening the db, inserting a time stamp whenever the script runs, which is every minute. At the end of the data gathering routine, it will go back and update that record for that time stamp. If for some reason gathering data fails, it simply won't update the db after that, but at least there's a time stamp with empty data points there. Of course, this doesn't solve the problem if the machine itself goes down, but hey, there's only so much one can do. This now allows me to still get 60 record returned, regardless of records being blank (or NULL) or not. The graphing program will do the rest. -- W | It's not a bug - it's an undocumented feature. + Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 IT Director / SysAdmin / Websmith . 800.441.3873 x130 Photo Craft Imaging . 3550 Arapahoe Ave. #6 http://www.pcraft.com . . .. Boulder, CO 80303, U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Max size and row numbers
Hey everyone I have more of a general question regarding your experience with large tables. I currently have a table (MyISAM, 6 columns, lots of reading access, some writing) with about 70.000.000 records, using 2.5GB of diskspace. I am running MySQL 5.0.* on a RedHat Enterprise AS 4 system (2 CPU, 8GB Ram). I just read that the max size for a table is 256TB in a default installation. I have basically no experience with tables that big and mine is potentially growing to several hundred million records. First of all, are there theoretical limitations (if the 256TB are correct I would be fine with that I guess) in size and number of records? More importantly, what are the practical limitations and/or pitfalls? Is ext3 as filesystem a limiting factor? If you have experience or know of good links regarding this topic, please let me know Thanks in advance Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL syntax
Hi, I'm getting the following error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' enabled = '1'' at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. Thanks, Scott _ Experience the magic of the holidays. Talk to Santa on Messenger. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://imagine-windowslive.com/minisites/santabot/default.aspx?locale=en-us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax
Hi, have you checked the 'enabled' field datatype or can you give the query. Thanks ViSolve DB Team. - Original Message - From: Scott Yamahata [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 29, 2006 11:59 AM Subject: SQL syntax Hi, I'm getting the following error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' enabled = '1'' at line 3 INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. Thanks, Scott _ Experience the magic of the holidays. Talk to Santa on Messenger. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://imagine-windowslive.com/minisites/santabot/default.aspx?locale=en-us -- 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: SQL syntax
Hi Scott, at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' The error is because you havent specified the value for the column countryid. If you do not want to insert the value to the column countryid then use the following query.. do not leave the value of column blank. INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid =NULL , enabled = '1' Thanks, ViSolve DB Team - Original Message - From: Scott Yamahata [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 29, 2006 11:59 AM Subject: SQL syntax Hi, I'm getting the following error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' enabled = '1'' at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. Thanks, Scott _ Experience the magic of the holidays. Talk to Santa on Messenger. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://imagine-windowslive.com/minisites/santabot/default.aspx?locale=en-us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.29/607 - Release Date: 12/28/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max size and row numbers
Hi, The maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. If you need a MyISAM table that is larger than 4GB in size (and your operating system supports large files), the CREATE TABLE statement allows AVG_ROW_LENGTH and MAX_ROWS options. The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64TB. On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the Large File Support (LFS) patch for the ext2 filesystem. On Linux 2.4, patches also exist for ReiserFS to get support for big files (up to 2TB). However, the maximum available file size still depends on several factors, one of them being the filesystem used to store MySQL tables. You can check the maximum table size for a table with the SHOW TABLE STATUS statement or with myisamchk -dv tbl_name. If your large table is read-only, you can use myisampack to compress it. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. Thanks Visolve DB Team - Original Message - From: Olaf Stein [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 29, 2006 4:14 AM Subject: Max size and row numbers Hey everyone I have more of a general question regarding your experience with large tables. I currently have a table (MyISAM, 6 columns, lots of reading access, some writing) with about 70.000.000 records, using 2.5GB of diskspace. I am running MySQL 5.0.* on a RedHat Enterprise AS 4 system (2 CPU, 8GB Ram). I just read that the max size for a table is 256TB in a default installation. I have basically no experience with tables that big and mine is potentially growing to several hundred million records. First of all, are there theoretical limitations (if the 256TB are correct I would be fine with that I guess) in size and number of records? More importantly, what are the practical limitations and/or pitfalls? Is ext3 as filesystem a limiting factor? If you have experience or know of good links regarding this topic, please let me know Thanks in advance Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]