Re: PHP / Mysql people question
Hi,
First mysql_fetch_row() returns an array so it doesn't make much sense to
compare it to 5. If you want to check tne number of records the query has
returned you should use mysql_num_rows() - like this
if(mysql_num_rows($result)=5){ }
or if you want to check the value returned by the query something like this
should work though I would personally add some additional error testing.
if(mysql_result($result,0,0)=5){ }
HTH
--
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/
On Wednesday 25 August 2004 18:02, Stuart Felenstein wrote:
Thought earlier that I could get by on just plain SQL.
Php needed. I'm getting a parse error on the if
line.
This is supposed to be a trigger then before
transaction counts the number of rows , id already
inserted, if exceeded , kick back error message , halt
transaction.
Not sure where I need to go with this statement to
make it work. Ideas , help, suggestions welcome!
//start trigger Trigger1
function KT_TriggerSTARTER_Trigger1($tNG) {
$result = mysql_query(SELECT LurkTitleTim.LurkID
WHERE
LurkID='$new_input');
if ($row=mysql_fetch_row($result) = 5) { error- data
already exists }
else { go ahead and add data with INSERT statement
}
}
Thank you ,
Stuart
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Secure Connection(e.g. SSL) Question
Ah I did not know that. Excellent. Still does not get round the issue that MySQL in most default binary distributions does not include SSL. Which I think is a shame, but then again is probably a lib issue. Thanks for that. Greg -Original Message- From: Eamon Daly [mailto:[EMAIL PROTECTED] Sent: 25 August 2004 19:28 To: [EMAIL PROTECTED] Subject: Re: MySQL Secure Connection(e.g. SSL) Question FYI, DBD::MySQL does support SSL. See http://search.cpan.org/~rudy/DBD-mysql-2.9004/lib/DBD/mysql.pm and search for mysql_ssl. You'll need to provide mysql_ssl_client_key mysql_ssl_client_cert mysql_ssl_ca_file as part of the DSN on the perl side, and set ssl-ca, ssl-key, and ssl-cert on the server. There's more on the subject at mysql.com: http://dev.mysql.com/doc/mysql/en/Secure_requirements.html Eamon Daly - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 24, 2004 11:12 AM Subject: RE: MySQL Secure Connection(e.g. SSL) Question I need to connect to a remote MySQL database from a PC using SSL. I would prefer to connect using perl DBD. Does anyone have a suggestion how I can accomplish this task or an alternative solution? What about stunnel or ssh tunnels and then use DBD::mysql on top. ISTR that DBD::mysql cannot use SSL'ed mysql client connections and your remote server would have to be complied to support it. Greg Thank You -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP / Mysql people question
First let me say thank you for all the responses.
Now, let me say I'm not sure what exactly I need to
do. Explanation - Primarily I have absolutely no
experience with PHP , but I can learn, so that's
helpful :)
The thing I'm trying to figure out is what I need to
learn, because currently I'm developing in
Dreamweaver, using a set of extensions from a company
call Interakt (this isn't a advertisement or
endorsement). Specifically using the Impakt
extensions. So , the bottom line is this is a PHP
-Adodb model. Now all I know is that Adodb is an
abstraction layer to PHP.
The code that wound up working for me is the following
(the KT_id are the session variables to identify the
user):
global $DBconnection, $HTTP_SESSION_VARS;
$colname__Recordset = '-1';
if (isset($HTTP_SESSION_VARS['KT_id_xxx'])) {
$colname__Recordset =
$HTTP_SESSION_VARS['KT_id_xxx'];
}
$query_Recordset = sprintf(SELECT * FROM LurkCerts
WHERE LurkID =
%s,
$colname__Recordset);
$Recordset =
$DBconnection-SelectLimit($query_Recordset) or
die($conn_name-ErrorMsg());
$totalRows_Recordset = $Recordset-RecordCount();
if ($totalRows_Recordset = 10)
$tNG-setError(-1,Too many table records for current
user!);
Bottom line I have no idea what language this is.
Anyway , hope this is not a confusing post.
Thank you ,
Stuart
--- Dobromir Velev [EMAIL PROTECTED] wrote:
Hi,
First mysql_fetch_row() returns an array so it
doesn't make much sense to
compare it to 5. If you want to check tne number of
records the query has
returned you should use mysql_num_rows() - like this
if(mysql_num_rows($result)=5){ }
or if you want to check the value returned by the
query something like this
should work though I would personally add some
additional error testing.
if(mysql_result($result,0,0)=5){ }
HTH
--
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/
On Wednesday 25 August 2004 18:02, Stuart Felenstein
wrote:
Thought earlier that I could get by on just plain
SQL.
Php needed. I'm getting a parse error on the if
line.
This is supposed to be a trigger then before
transaction counts the number of rows , id already
inserted, if exceeded , kick back error message ,
halt
transaction.
Not sure where I need to go with this statement to
make it work. Ideas , help, suggestions welcome!
//start trigger Trigger1
function KT_TriggerSTARTER_Trigger1($tNG) {
$result = mysql_query(SELECT LurkTitleTim.LurkID
WHERE
LurkID='$new_input');
if ($row=mysql_fetch_row($result) = 5) { error-
data
already exists }
else { go ahead and add data with INSERT
statement
}
}
Thank you ,
Stuart
--
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 beginner question
Having duplicates is not a problem. As long as you don't have a UNIQUE index on it. Something else is happening in your code that is putting out the HTML I would guess. P.S. It should be SELECT * FROM jspCart_products; (your table, not your database) B Wiley Snyder wrote: Hello, hope this is the right list I created a table with the following code CREATE TABLE jspCart_products ( ProductID int primary key, CategoryID int, ModelNumber varChar(75), ModelName varChar(250), ProductImage varchar(250), UnitCost decimal(9,2), Description BLOB, ); The CategoryID has duplicate entrys. When I use SELECT * FROM mydatabase I see the categories just fine but when they are sent to an html page they are null values. Would that be a screw-up in my code or is it becuase I need to specify when I initially build the table like above that it uses duplicates? i hope that makes sense and thanks for replys in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP / Mysql people question
Right, I am not on any list for a line by line feed /
analysis. Actually I tried signing up twice yesterday
for the PHP general and never received email
confirmation. Anyway sure I'll find my way !
Thank you
Stuart
--- [EMAIL PROTECTED] wrote:
i would suggest that your questions would be more
appropriately asked
on the php mailing list. one suggestion, however.
look at the php
documentation and get a general understanding of
things first.
[there's been a spate of people basically asking for
line-by-line
debugging of their code which has caused the read
the manual bar to
be pushed somewhat higher.]
-- Original Message --
From: Stuart Felenstein [EMAIL PROTECTED]
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
Date: Thursday, August 26, 2004 02:19:34 AM -0700
Subject: Re: PHP / Mysql people question
First let me say thank you for all the responses.
Now, let me say I'm not sure what exactly I need
to
do. Explanation - Primarily I have absolutely no
experience with PHP , but I can learn, so that's
helpful :)
The thing I'm trying to figure out is what I need
to
learn, because currently I'm developing in
Dreamweaver, using a set of extensions from a
company
call Interakt (this isn't a advertisement or
endorsement). Specifically using the Impakt
extensions. So , the bottom line is this is a PHP
-Adodb model. Now all I know is that Adodb is an
abstraction layer to PHP.
The code that wound up working for me is the
following
(the KT_id are the session variables to identify
the
user):
global $DBconnection, $HTTP_SESSION_VARS;
$colname__Recordset = '-1';
if (isset($HTTP_SESSION_VARS['KT_id_xxx'])) {
$colname__Recordset =
$HTTP_SESSION_VARS['KT_id_xxx'];
}
$query_Recordset = sprintf(SELECT * FROM
LurkCerts
WHERE LurkID =
%s,
$colname__Recordset);
$Recordset =
$DBconnection-SelectLimit($query_Recordset) or
die($conn_name-ErrorMsg());
$totalRows_Recordset = $Recordset-RecordCount();
if ($totalRows_Recordset = 10)
$tNG-setError(-1,Too many table records for
current
user!);
Bottom line I have no idea what language this
is.
Anyway , hope this is not a confusing post.
Thank you ,
Stuart
--- Dobromir Velev [EMAIL PROTECTED] wrote:
Hi,
First mysql_fetch_row() returns an array so it
doesn't make much sense to
compare it to 5. If you want to check tne number
of
records the query has
returned you should use mysql_num_rows() - like
this
if(mysql_num_rows($result)=5){ }
or if you want to check the value returned by the
query something like this
should work though I would personally add some
additional error testing.
if(mysql_result($result,0,0)=5){ }
HTH
--
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/
On Wednesday 25 August 2004 18:02, Stuart
Felenstein
wrote:
Thought earlier that I could get by on just
plain
SQL.
Php needed. I'm getting a parse error on the
if
line.
This is supposed to be a trigger then before
transaction counts the number of rows , id
already
inserted, if exceeded , kick back error message
,
halt
transaction.
Not sure where I need to go with this statement
to
make it work. Ideas , help, suggestions
welcome!
//start trigger Trigger1
function KT_TriggerSTARTER_Trigger1($tNG) {
$result = mysql_query(SELECT
LurkTitleTim.LurkID
WHERE
LurkID='$new_input');
if ($row=mysql_fetch_row($result) = 5) {
error-
data
already exists }
else { go ahead and add data with INSERT
statement
}
}
Thank you ,
Stuart
--
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]
-- End Original Message --
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL beginner question
- Original Message - From: B Wiley Snyder [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 26, 2004 1:56 AM Subject: mySQL beginner question Hello, hope this is the right list I created a table with the following code CREATE TABLE jspCart_products ( ProductID int primary key, CategoryID int, ModelNumber varChar(75), ModelName varChar(250), ProductImage varchar(250), UnitCost decimal(9,2), Description BLOB, ); The CategoryID has duplicate entrys. When I use SELECT * FROM mydatabase I see the categories just fine but when they are sent to an html page they are null values. Would that be a screw-up in my code or is it becuase I need to specify when I initially build the table like above that it uses duplicates? i hope that makes sense and thanks for replys in advance. It's not clear to me what you are saying when you say that CategoryID has duplicate entries. Do you mean that you have this in your table: ProductIDCategoryIDModelNumberModelNameProductImage UnitCostDescription 11 B200Starburst blahblahblah50.00x'...' 21 C240Stonehenge foofoofoofoo30.00x'...' i.e. products that have different product numbers have the same category ID? If so, is that what you want in your table? It seems perfectly reasonable to me to have this situation in your table. When you say that the CategoryID is appearing as a null in your HTML reports, do you mean that they are not null in the tables? If that is the case, then it is almost certainly something in your code isn't handling the values in the CategoryID column correctly. You may want to post the code that reads the rows of the tables and formats it for the HTML page if you can't find the problem yourself. To be absolutely sure that the nulls being displayed by the program are bogus, run this query from your command line: select ProductID, CategoryID from yourtable where CategoryID is null If this query produces an empty result, you can be dead certain that your program is turning non-null CategoryIDs into nulls. If this query produces a non-empty result, check the HTML report and see if the rows containing null CategoryIDs match exactly the rows that contain nulls in the command line query; if they do, your application code is working perfectly and your table data has nulls in the CategoryID. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries on Fast Server?
I'm gathering by the lack of response that perhaps MySQL is incapable of executing a
count of the number of fulltext matches on 3 million rows.
I really thought that MySQL 4 was really suppose to be able to handle such a load
I still think my configuration may be to blame
?
- John
--
Could you send the output of an EXPLAIN for your query?
Sure, pretty sure the index is fine though:
mysql EXPLAIN SELECT COUNT(*) FROM product_fulltext WHERE MATCH (search_text) AGAINST
('black');
+--+--+---+-+-+--+--+-+
| table| type | possible_keys | key | key_len | ref | rows |
Extra |
+--+--+---+-+-+--+--+-+
| product_fulltext | fulltext | search_text | search_text | 0 | |1 |
Using where |
+--+--+---+-+-+--+--+-+
1 row in set (0.00 sec)
[EMAIL PROTECTED] wrote:
Have you checked the Optimization section of the manual yet?
http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html
Oh yes, as I've attempted to configure the my.cnf file for best performance. The
query is correct. The fulltext index is correct as I built the fulltext index on
the
single column (took 9 minutes) and even did repair and optimize on the table...
so I
don't think its the index. I'm thinking its the server config...
- John
[EMAIL PROTECTED] wrote:
I'm running into a problem with some queries running on a dedicated mysql server
(2.0
GHz, 2GB RAM). Fulltext searching really exemplifies this as most MATCH, AGAINST
queries
are taking 5-20 seconds. Performance was excellent for some reason one day (0.2
-
0.75
seconds) but it was only fast for a day or so.
Here's the rundown:
TABLE: fulltext_table (some_id, the_text)
Rows: 3,237,981
Type: MyISAM
Size: 920.8 MB
QUERY: SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text) AGAINST
('blue');
or
QUERY: SELECT SQL_CALC_FOUND_ROWS some_id FROM fulltext_table WHERE MATCH
(the_text)
AGAINST ('blue') LIMIT 0, 20;
Both are problematic. I even tried placing a limit of 2 on the first query
but
it didn't improve anything. The table has a fulltext index on the column and is
optimized. No other users are connected to the server.
Is there a RED FLAG in here somewhere?
MySQL configuration settings (using my-huge.cnf template):
key_buffer = 500M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 10M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
#thread_cache = 8
thread_concurrency = 8
#- Modifications --- #
ft_min_word_len = 3
set-variable = table_cache=1024
set-variable = max_heap_table_size=64M
set-variable = tmp_table_size=128M
set-variable = query_cache_limit=2M
query_cache_type=1
Performance Test:
SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text) AGAINST ('white');
+--+
| COUNT(*) |
+--+
|95074 |
+--+
1 row in set (27.83 sec)
Statistics for vmstat 1 (my apologies if this doesn't look pretty):
---
procs memory swap io system cpu
r b swpd free buff cache si sobibo incs us sy id wa
0 0 19500 17800 42432 177172800 060 11330 0 0 99 1
0 1 19500 21524 42428 1765728 7240 960 0 536 444 5 1 82 12
0 1 19500 19512 42424 176782000 2348 0 912 592 0 1 50 49
0 1 19500 17788 42424 176954000 1980 0 868 588 0 1 51 48
0 1 19500 17568 42424 176976000 2300 0 723 401 0 0 50 49
0 1 19500 17704 42428 176962000 193620 662 364 0 0 51 49
0 1 19500 17560 42428 176976400 2224 0 696 400 0 0 51 49
0 1 19500 17504 42424 176982400 2136 0 670 380 0 0 51 49
0 1 19500 17616 42424 176971200 2228 0 693 415 0 0 51 49
0 1 19508 17608 42420 176972408 2348 8 692 389 0 0 50 50
0 1 19508 17532 42428 176979200 1896 108 654 366 0 0 50 49
0 1 19512 17644 42424 176968404 2220 4 720 450 0 1 50 49
0 1 19516 17620 42420 176971204 2104 4 707 424 0 0 51 48
0 1 19516 17744 42420 176958800 2476 0 762 462 0 1 50 49
0 1 19516 17532 42416 176980400 2292 0 719 401 0 0 51 49
procs memory swap io system cpu
r b swpd free buff cache si sobibo incs us sy id wa
0 1 19516 17388 42424 176994000 221616 699 388 0 0 51 49
0 1 19516 17632 42420 176970000 1836 0 629 380 0 0 51 49
0 1 19516 17596 42420 176973200 2112 0 661 374 0 1 51 48
0 1 19516 17580 42416 176975200
ANNC: MySQL Administrator 1.0.11
MySQL Administrator 1.0.11 has been released. MySQL Administrator is a GUI management console for MySQL, with support for tasks such as managing users, configuring MySQL, performing backups, editing table definitions etc. More information at: http://www.mysql.com/products/administrator/ You can download sources and binaries for Windows and Linux from: http://dev.mysql.com/downloads/administrator/ This release contains a few bug fixed relating to user administration and the table editor. -- Alfredo Kojima, GUI Developer MySQL AB, www.mysql.com Buenos Aires, Argentina Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could this be dangerous :Values and Labels
At 02:36 PM 8/25/2004, you wrote: Or maybe just bad practice. Thought before I go any further I'll ask. I have a few static tables that list out items and the primary key is an assigned ID. Meaning I did not set auto-increment. As I add items I will add the associated ID number. Now to the transactional tables. While the label has the item listed in the form, it gets inserted into the table with the ID number. To illustrate: Static_Table ID Value 1 United 2 Jet Blue 3 Southwest 4 American Dynamic_Table MemberIDAirline_Pref 200 1 201 4 202 3 203 4 204 1 Pros cons dangers advantages comments ? Thank you Stuart Stuart I personally would switch to a 2 letter code for the Airline Code, as in: UN United JB Jet Blue SW SouthWest AM American simply because it will be much easier to train people and there will be fewer mistakes. It is much easier to remember AM for American instead of 4. It would be a different thing if you had over a hundred airlines, then you could use numbers. But for a few dozen airlines, letter codes are easier to understand. Especially when you print simple reports, AM is a lot more readable than 4. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could this be dangerous :Values and Labels
I actually did make the switch to mnemonics (as suggested by Rhino) I've gone slightly beyond 2 letters though due to the fact that some of my labels might be more then confusing. I've tried to stick with 3 letters on average. It does seem like a better way to go. Stuart --- mos [EMAIL PROTECTED] wrote: At 02:36 PM 8/25/2004, you wrote: Or maybe just bad practice. Thought before I go any further I'll ask. I have a few static tables that list out items and the primary key is an assigned ID. Meaning I did not set auto-increment. As I add items I will add the associated ID number. Now to the transactional tables. While the label has the item listed in the form, it gets inserted into the table with the ID number. To illustrate: Static_Table ID Value 1 United 2 Jet Blue 3 Southwest 4 American Dynamic_Table MemberIDAirline_Pref 200 1 201 4 202 3 203 4 204 1 Pros cons dangers advantages comments ? Thank you Stuart Stuart I personally would switch to a 2 letter code for the Airline Code, as in: UN United JB Jet Blue SW SouthWest AM American simply because it will be much easier to train people and there will be fewer mistakes. It is much easier to remember AM for American instead of 4. It would be a different thing if you had over a hundred airlines, then you could use numbers. But for a few dozen airlines, letter codes are easier to understand. Especially when you print simple reports, AM is a lot more readable than 4. 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]
replication threads on different CPUs
I'm a bit of a linux newbie and a newbie to replication, so I'll try to ask this question simply... we're using mysql 4 to do replication, and I notice on the master I have this from using mytop: Id User Host/IP DB TimeCmd Query or State -- --- -- --- -- 2670 root localhost rmps 0 Query show full processlist 2668 repl rh-mysql-4 409 Binlog Has sent all binlog to slave; waiting for binlog to be updated 2666 repl rh-mysql-2 411 Binlog Has sent all binlog to slave; waiting for binlog to be updated 2667 repl rh-mysql-3 411 Binlog Has sent all binlog to slave; waiting for binlog to be updated it looks like three seperate threads are running on the master here, one for each slave. in this particular machine, we have 2 HT processors, so 4 possible CPU threads. if it's not done automatically by Linux (RedHat 9) or MySQL, can I configure these threads to use their own CPU to maximize the performance? I assume MySQL or Linux will do this automatically. we have the same machines in use for the slaves...so can I make the slave IO thread execute on one processor, and the slave SQL thread execute on another? does this happen automatically as well? thanks, -L Luke Crouch 918-461-5326 [EMAIL PROTECTED]
Re: ANNC: MySQL Administrator 1.0.11
Alfredo Kengi Kojima said: MySQL Administrator 1.0.11 has been released. MySQL Administrator is a GUI management console for MySQL, with support for tasks such as managing users, configuring MySQL, performing backups, editing table definitions etc. More information at: http://www.mysql.com/products/administrator/ You can download sources and binaries for Windows and Linux from: http://dev.mysql.com/downloads/administrator/ This release contains a few bug fixed relating to user administration and the table editor. -- Alfredo Kojima, GUI Developer MySQL AB, www.mysql.com Buenos Aires, Argentina You should warn people that it doen't support 3.x series I believe. Since that is all that is in the Debian std. release this is an issue. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries on Fast Server?
Capable? I can't think if why it wouldn't be capable. From your posts I
assume your definition of capable in this case is a quick response.
Are you running 4.0 or 4.1? I think the indexing was changed in 4.1 so
it would give you better response. 5-20 seconds does seem long,
assuming your disks are fast.
You may try doubling or tripling your sort_buffer and
myisam_sort_buffer settings and maybe you read_buffer. Remember the
template configuration files are still all purpose configurations. You
are really looking to optimize a specific area.
Unfortunately, I don't have a dataset large enough yet to test myself,
but I am curious. Improved performance is on the to do list.
On Aug 26, 2004, at 9:07 AM, [EMAIL PROTECTED] wrote:
I'm gathering by the lack of response that perhaps MySQL is incapable
of executing a count of the number of fulltext matches on 3 million
rows.
I really thought that MySQL 4 was really suppose to be able to handle
such a load
I still think my configuration may be to blame
?
- John
--
Could you send the output of an EXPLAIN for your query?
Sure, pretty sure the index is fine though:
mysql EXPLAIN SELECT COUNT(*) FROM product_fulltext WHERE MATCH
(search_text) AGAINST
('black');
+--+--+---+-+-
+--+--+-+
| table| type | possible_keys | key | key_len
| ref | rows |
Extra |
+--+--+---+-+-
+--+--+-+
| product_fulltext | fulltext | search_text | search_text | 0
| |1 |
Using where |
+--+--+---+-+-
+--+--+-+
1 row in set (0.00 sec)
[EMAIL PROTECTED] wrote:
Have you checked the Optimization section of the manual yet?
http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html
Oh yes, as I've attempted to configure the my.cnf file for best
performance. The
query is correct. The fulltext index is correct as I built the
fulltext index on
the
single column (took 9 minutes) and even did repair and optimize
on the table...
so I
don't think its the index. I'm thinking its the server config...
- John
[EMAIL PROTECTED] wrote:
I'm running into a problem with some queries running on a dedicated
mysql server
(2.0
GHz, 2GB RAM). Fulltext searching really exemplifies this as most
MATCH, AGAINST
queries
are taking 5-20 seconds. Performance was excellent for some reason
one day (0.2
-
0.75
seconds) but it was only fast for a day or so.
Here's the rundown:
TABLE: fulltext_table (some_id, the_text)
Rows: 3,237,981
Type: MyISAM
Size: 920.8 MB
QUERY: SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text)
AGAINST
('blue');
or
QUERY: SELECT SQL_CALC_FOUND_ROWS some_id FROM fulltext_table WHERE
MATCH
(the_text)
AGAINST ('blue') LIMIT 0, 20;
Both are problematic. I even tried placing a limit of 2 on the
first query
but
it didn't improve anything. The table has a fulltext index on the
column and is
optimized. No other users are connected to the server.
Is there a RED FLAG in here somewhere?
MySQL configuration settings (using my-huge.cnf template):
key_buffer = 500M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 10M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
#thread_cache = 8
thread_concurrency = 8
#- Modifications --- #
ft_min_word_len = 3
set-variable = table_cache=1024
set-variable = max_heap_table_size=64M
set-variable = tmp_table_size=128M
set-variable = query_cache_limit=2M
query_cache_type=1
Performance Test:
SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text) AGAINST
('white');
+--+
| COUNT(*) |
+--+
|95074 |
+--+
1 row in set (27.83 sec)
Statistics for vmstat 1 (my apologies if this doesn't look pretty):
---
procs memory swap io system
cpu
r b swpd free buff cache si sobibo incs us
sy id wa
0 0 19500 17800 42432 177172800 060 11330
0 0 99 1
0 1 19500 21524 42428 1765728 7240 960 0 536 444
5 1 82 12
0 1 19500 19512 42424 176782000 2348 0 912 592
0 1 50 49
0 1 19500 17788 42424 176954000 1980 0 868 588
0 1 51 48
0 1 19500 17568 42424 176976000 2300 0 723 401
0 0 50 49
0 1 19500 17704 42428 176962000 193620 662 364
0 0 51 49
0 1 19500 17560 42428 176976400 2224 0 696 400
0 0 51 49
0 1 19500 17504 42424 176982400 2136 0 670 380
0 0 51 49
0 1 19500 17616 42424 176971200 2228 0 693 415
0 0 51 49
0 1 19508 17608 42420 176972408 2348 8 692 389
0 0 50 50
0 1 19508 17532 42428 176979200 1896 108 654 366
0 0 50 49
0 1
RE: replication threads on different CPUs
Luke, As far as I know you can't do that in mysql, it would have to be at the kernel level. Replication threads, really don't use much cpu anyway at least not on the master, since all it's doing is basically reading a binary file. Now the kernel itself does do something like you are talking about, but I have no idea how it decides what is going to use what proc. Now, my master box which is only running mysql and which is replicating to 2 different slaves, but also handles all selects and inserts for about 6 webservers looks like this. Cpu0 : 10.1% us, 4.0% sy, 0.0% ni, 71.5% id, 12.6% wa, 0.5% hi, 1.2% si Cpu1 : 2.2% us, 1.1% sy, 0.0% ni, 94.0% id, 2.5% wa, 0.0% hi, 0.1% si Cpu2 : 10.3% us, 4.1% sy, 0.0% ni, 70.7% id, 12.9% wa, 0.8% hi, 1.3% si Cpu3 : 2.9% us, 1.4% sy, 0.0% ni, 92.4% id, 3.2% wa, 0.0% hi, 0.2% si Cpu4 : 8.9% us, 3.5% sy, 0.0% ni, 80.1% id, 5.8% wa, 0.6% hi, 1.2% si Cpu5 : 2.3% us, 1.1% sy, 0.0% ni, 93.7% id, 2.7% wa, 0.0% hi, 0.1% si Cpu6 : 8.8% us, 3.4% sy, 0.0% ni, 80.5% id, 5.6% wa, 0.6% hi, 1.2% si Cpu7 : 2.5% us, 1.2% sy, 0.0% ni, 93.2% id, 3.0% wa, 0.0% hi, 0.2% si So the load is being shared by all of the procs, just no idea what decides where. Donny -Original Message- From: Crouch, Luke H. [mailto:[EMAIL PROTECTED] Sent: Thursday, August 26, 2004 10:36 AM To: [EMAIL PROTECTED] Subject: replication threads on different CPUs I'm a bit of a linux newbie and a newbie to replication, so I'll try to ask this question simply... we're using mysql 4 to do replication, and I notice on the master I have this from using mytop: Id User Host/IP DB TimeCmd Query or State -- --- -- --- -- 2670 root localhost rmps 0 Query show full processlist 2668 repl rh-mysql-4 409 Binlog Has sent all binlog to slave; waiting for binlog to be updated 2666 repl rh-mysql-2 411 Binlog Has sent all binlog to slave; waiting for binlog to be updated 2667 repl rh-mysql-3 411 Binlog Has sent all binlog to slave; waiting for binlog to be updated it looks like three seperate threads are running on the master here, one for each slave. in this particular machine, we have 2 HT processors, so 4 possible CPU threads. if it's not done automatically by Linux (RedHat 9) or MySQL, can I configure these threads to use their own CPU to maximize the performance? I assume MySQL or Linux will do this automatically. we have the same machines in use for the slaves...so can I make the slave IO thread execute on one processor, and the slave SQL thread execute on another? does this happen automatically as well? thanks, -L Luke Crouch 918-461-5326 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Correct date query syntax
Hi, All: I have a database which contains date attribute in string format (like 2004-08-12). I want to genearte a report based on period time. I use the syntax: date1 ='2004-08-12' date2='2004-08-18' SELECT * FROM account WHERE (TO_DAYS(date) = TODAYS(date1)) and (TO_DAYS(date) = TO_DAYS(date2)); The report script complains the condition after WHERE clause. The reason I use TO_DAYS is that I want to convert string date data into integer for comparison. Can I use TO_DAYS() like this way ? Thanks a lot. Yong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Correct date query syntax
On Thu, 26 Aug 2004 11:31:46 -0500, Yong Wang [EMAIL PROTECTED] wrote: I have a database which contains date attribute in string format (like 2004-08-12). I want to genearte a report based on period time. I use the syntax: date1 ='2004-08-12' date2='2004-08-18' SELECT * FROM account WHERE (TO_DAYS(date) = TODAYS(date1)) and (TO_DAYS(date) = TO_DAYS(date2)); Is date the name of your field? Change it, it is a reserved word in SQL. The report script complains the condition after WHERE clause. The reason I use TO_DAYS is that I want to convert string date data into integer for comparison. Can I use TO_DAYS() like this way ? Even if you can, don't: it isn't needed. Just use a plain BETWEEN predicate without functions: SELECT * FROM account WHERE date BETWEEN date1 AND date2 Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Correct date query syntax
I believe you are not letting MySQL do enough work for you. The date format is perfect, even as a string, to perform the comparison you are trying to perform. SELECT @currTime := NOW(); +-+ | @currTime := NOW() | +-+ | 2004-08-26 12:48:16 | +-+ 1 row in set (0.00 sec) SELECT @currTime as CurrentTime , (@currTime = '2005-08-01') , (@currTime '2005-08-01') , (@currTime '2004-08-26 12:00:00')\G *** 1. row *** CurrentTime: 2004-08-26 12:48:16 (@currTime = '2005-08-01'): 1 (@currTime '2005-08-01'): 0 (@currTime '2004-08-26 12:00:00'): 1 1 row in set (0.00 sec) As you can see, MySQL is perfectly capable of comparing dates and datetimes if you just let it. There is no reason to convert them to any sort of intermediate value as it will do it for you internally and automatically. Please read this to see just how many different ways Dates can be specified: http://dev.mysql.com/doc/mysql/en/DATETIME.html You could try this as your query: SET @date1 ='2004-08-12', date2='2004-08-18'; SELECT * FROM account WHERE (date = date1) AND (date = date2); and it should work just fine. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Yong Wang [EMAIL PROTECTED] wrote on 08/26/2004 12:31:46 PM: Hi, All: I have a database which contains date attribute in string format (like 2004-08-12). I want to genearte a report based on period time. I use the syntax: date1 ='2004-08-12' date2='2004-08-18' SELECT * FROM account WHERE (TO_DAYS(date) = TODAYS(date1)) and (TO_DAYS(date) = TO_DAYS(date2)); The report script complains the condition after WHERE clause. The reason I use TO_DAYS is that I want to convert string date data into integer for comparison. Can I use TO_DAYS() like this way ? Thanks a lot. Yong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql command line queries not working....
I know the subject is not totally descriptive of the problem, but: mysql -h -u *** -p*** mikerocks latest_distribution.sql Is producing no results. I mean none, totally, no errors, nothing. The host, user and pass are all correct (of course removed here for security ;) ) and have the correct permissions. The latest_distribution.sql is simply a mysqldump of a database... And I have narrowed down the problem to the comments that the dump puts in there: # phpMyAdmin SQL Dump # version 2.5.6 # http://www.phpmyadmin.net Etc... Every comment in there aborts the rest of the file... Again, not with any errors, it just simply stops. Mysql on originating server: Ver 11.18 Distrib 3.23.54, for pc-linux (i686) Mysql on destination server: Ver 12.20 Distrib 4.0.13, for pc-linux (i686) Additional info: The mysqldump was created on the originating server. There is nothing strange in the mysqldump. The latest_distribution.sql works if imported as an sql query via PhpMyAdmin. I need to do this command line (part of an automated setup), so the questions are: 1. Is this a bug or some sort of setting that is messed up or something? 2. Can the mysqldump file be created without comments, as removing them every time there is a new dump will be tedious to say the least... 3. Am I a total idiot and have missed something dead simple? TIA! -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql command line queries not working....
Can you run this from inside the monitor and see if any messages are sent to the consol? \. latest_distribution.sql -Original Message- From: Mike Morton To: [EMAIL PROTECTED] Sent: 8/26/04 12:05 PM Subject: Mysql command line queries not working I know the subject is not totally descriptive of the problem, but: mysql -h -u *** -p*** mikerocks latest_distribution.sql Is producing no results. I mean none, totally, no errors, nothing. The host, user and pass are all correct (of course removed here for security ;) ) and have the correct permissions. The latest_distribution.sql is simply a mysqldump of a database... And I have narrowed down the problem to the comments that the dump puts in there: # phpMyAdmin SQL Dump # version 2.5.6 # http://www.phpmyadmin.net Etc... Every comment in there aborts the rest of the file... Again, not with any errors, it just simply stops. Mysql on originating server: Ver 11.18 Distrib 3.23.54, for pc-linux (i686) Mysql on destination server: Ver 12.20 Distrib 4.0.13, for pc-linux (i686) Additional info: The mysqldump was created on the originating server. There is nothing strange in the mysqldump. The latest_distribution.sql works if imported as an sql query via PhpMyAdmin. I need to do this command line (part of an automated setup), so the questions are: 1. Is this a bug or some sort of setting that is messed up or something? 2. Can the mysqldump file be created without comments, as removing them every time there is a new dump will be tedious to say the least... 3. Am I a total idiot and have missed something dead simple? TIA! -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- 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 4.0.18/Dual AMD64 thread zombie even when wait_timeout is set to 60 seconds! Bug?
Hi guys, We have a problem with Dual AMD64 Opteron/MySQL 4.0.18/Mandrake 10 for a very high volume site. We are evaluating the performance on our new server AMD64 and it seems it's slow compared to Dual Xeon/MySQL 4.0.15/RedHat8 and Dual Xeon/MySQL 4.0.18/Mandrake 10. And it seems there are zombie threads. 570 threads in 1 hour and we didn't even use JDBC connection pooling at all. These threads are supposed to be gone within 60 seconds, since we set that option in mysqld. Note that we run many SELECT queries (can be up to 150 queries/seconds), but the system does not indicate any slow query: it's 0! Our configuration is Apache 2.0.48 + Tomcat 5.0.27 + MySQL 4.0.18 with MySQL connector/J 3.0.14 (latest stable). The Redhat 8 runs on Apache 2 + Tomcat 4.0 + MySQL 4.0.15. The old Redhat 8 on Xeon was fine. We have another machine running Mandrake 10 on Xeon and they were fine under the same load. I have set the wait_timeout to 60 seconds, and it appears to be fine within 10 minutes, all the threads that are in sleep mode disappeared after 60 seconds. After a few minutes though, it's back like it was before. Is this Mandrake problem? MySQL problem? I read in here than Mandrake win hands down on AMD64 compared to FreeBSD. http://news.gw.com/freebsd.amd64/1030 What do you think cause this problem on MySQL/AMD64? Is there such problem in v4.0.18? Thanks, Prana Here's the info: === This MySQL server has been running for 0 days, 1 hours, 20 minutes and 48 seconds. It started up on Aug 26, 2004 at 11:24 PM. === Query statistics: Since its startup, 261,670 queries have been sent to the server. Total ø per hour ø per minute ø per second 261,670 194,309.41 3,238.49 53.97 === Uptime: 5559 Threads: 569 Questions: 261705 Slow queries: 0 Opens: 74 Flush tables: 1 Open tables: 68 Queries per second avg: 47.078 === Mandrake Packages: MySQL-common-4.0.18-1.1.100mdk lib64mysql12-4.0.18-1.1.100mdk perl-Mysql-1.22_19-9mdk MySQL-4.0.18-1.1.100mdk MySQL-client-4.0.18-1.1.100mdk php-mysql-4.3.4-1mdk [EMAIL PROTECTED] root]# mysql -e 'show variables like %time%' +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | flush_time | 0 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 60| | long_query_time | 10| | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | slow_launch_time | 2 | | timezone | SGT | | wait_timeout | 30| +--+---+ [my.cnf]== [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] wait_timeout=30 interactive_timeout=60 port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking log-slow-queries key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 128 query_cache_size = 32M max_connections=1200 thread_concurrency = 4 connect_timeout = 5 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout == Prana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql command line queries not working....
OK, more debugging, and this is what I have come up with The latest_distribution.sql file was NOT created via a mysqldump, but rather via phpmyadmin export... The comments from phpmyadmin are denoted with the '#' character The comments from a true mysqldump are denoted with the '--' characters... When doing the command line import with the mysqldump file, it all works good... So, I guess this is a warning to anyone who uses phpmyadmin (2.5.6 anyhow) that if you do an export, it is NOT a valid mysql import... At least not on my servers ;) Thanks to everyone who answered so quick! (and PS - if anyone can confirm or correct these assumptions, please do let me know. Thanks!) On 8/26/04 1:08 PM, Victor Pendleton [EMAIL PROTECTED] wrote: Can you run this from inside the monitor and see if any messages are sent to the consol? \. latest_distribution.sql -Original Message- From: Mike Morton To: [EMAIL PROTECTED] Sent: 8/26/04 12:05 PM Subject: Mysql command line queries not working I know the subject is not totally descriptive of the problem, but: mysql -h -u *** -p*** mikerocks latest_distribution.sql Is producing no results. I mean none, totally, no errors, nothing. The host, user and pass are all correct (of course removed here for security ;) ) and have the correct permissions. The latest_distribution.sql is simply a mysqldump of a database... And I have narrowed down the problem to the comments that the dump puts in there: # phpMyAdmin SQL Dump # version 2.5.6 # http://www.phpmyadmin.net Etc... Every comment in there aborts the rest of the file... Again, not with any errors, it just simply stops. Mysql on originating server: Ver 11.18 Distrib 3.23.54, for pc-linux (i686) Mysql on destination server: Ver 12.20 Distrib 4.0.13, for pc-linux (i686) Additional info: The mysqldump was created on the originating server. There is nothing strange in the mysqldump. The latest_distribution.sql works if imported as an sql query via PhpMyAdmin. I need to do this command line (part of an automated setup), so the questions are: 1. Is this a bug or some sort of setting that is messed up or something? 2. Can the mysqldump file be created without comments, as removing them every time there is a new dump will be tedious to say the least... 3. Am I a total idiot and have missed something dead simple? TIA! -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Template
I finally got Php/MySQL/Apache all set on Windows 2000 professional workstation. I'm looking for various php templates that keeps track of associates, quantity produced, hours for each quantity produced and so on. Is there such a template out there on the web that I can use? TIA, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Template
- Original Message - From: Scott Hamm [EMAIL PROTECTED] To: 'Mysql ' (E-mail) [EMAIL PROTECTED] Sent: Thursday, August 26, 2004 2:37 PM Subject: Template I finally got Php/MySQL/Apache all set on Windows 2000 professional workstation. I'm looking for various php templates that keeps track of associates, quantity produced, hours for each quantity produced and so on. Is there such a template out there on the web that I can use? I don't know php so maybe 'templates' is a term specific to that language. If that is the case, ignore this note. If it isn't, just what do you mean? Are you looking for table definitions or programs or something else altogether? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL C API Examples
I have arranged some MySQL C API examples at http://www.geocities.com/jahan.geo/mysql_c_by_example.html Comments/ Suggestion welcome. -- Aftab Jahan Subedar CEO/Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 North Jatrabari Dhaka 1204 Bangladesh http://www.SubedarTechnologies.com ( down now ) http://www.DhakaStockExchangeGame.com/ ( down now ) http://www.CEOBangladesh.com/( down now ) http://www.NYSEGame.com( down now ) tel://+88027519050 EMail://[EMAIL PROTECTED] - Directly to my notebook -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT from two tables with condition
Hi! I have got two tables: 'item' and 'propertie'. Table 'item' has index column named 'id_propertie' which refers to one row in table 'propertie'. Table 'propertie' has string column 'value'. I want to get the value of colun 'value' from table 'propertie' which index is same than in table 'item', BUT if the column 'id_propertie' in tabel 'item' is null the value for 'value' is 'empty'. Is it possible to make query like this? ___ Parhaat hakupalvelut yhdessä osoitteessa: http://www.eniro.fi/ Suomalaisten yritysten tuotteet ja palvelut: http://yritykset.eniro.fi/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sanity Check : Error in sql
I'm running into a problem here where mySQL is saying unknown column. Education_table TypeID Type HS High School CO College MA Masters SQL: Select * from Education_table (and no the table is not named table) From: TypeID = Value , Type = Label Choose High School Error Msg: Unknown column 'HS' in 'field list' I tried adding where typeid = type but that makes not sense. I'm now wondering and running (after I post) if type is a no-no-word. I know I post way to often. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Template
How many places have you looked? I would use a search engine (Google, Yahoo, etc) and try various combinations of terms like PHP, Samples, Production, Source code, sample, and so on. I would keep searching until I felt confident that: a) nobody else has posted any code for a series of web pages that did what I want to do. b) I found a sample close enough to what I want to do that I could tweak it to fit my needs c) I found enough information about PHP development to enable me to build it from scratch. If you have a MySQL related question please, PLEASE, come back. There are lots of very knowledgeable people on this list that will be happy to help you with your database issues but most of us just don't have the resources to take up the slack for the PHP lists. Sorry. Best Wishes, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Hamm [EMAIL PROTECTED] wrote on 08/26/2004 02:37:59 PM: I finally got Php/MySQL/Apache all set on Windows 2000 professional workstation. I'm looking for various php templates that keeps track of associates, quantity produced, hours for each quantity produced and so on. Is there such a template out there on the web that I can use? TIA, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sanity Check : Error in sql
From: Stuart Felenstein [mailto:[EMAIL PROTECTED] I'm running into a problem here where mySQL is saying unknown column. Education_table TypeID Type HS High School CO College MA Masters SQL: Select * from Education_table (and no the table is not named table) From: TypeID = Value , Type = Label Choose High School Error Msg: Unknown column 'HS' in 'field list' I tried adding where typeid = type but that makes not sense. I'm now wondering and running (after I post) if type is a no-no-word. Try wrapping Value in single- or double-quotes in your code. It sounds like it's essentially generating this query: Select * from Education_table where TypeID = HS; instead of... Select * from Education_table where TypeID = 'HS'; HTH! -- Mike Johnson Smarter Living, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sanity Check : Error in sql
Stuart Felenstein wrote: SQL: Select * from Education_table (and no the table is not named table) From: TypeID = Value , Type = Label Choose High School Error Msg: Unknown column 'HS' in 'field list' You need to show the exact SQL statement that you're sending. Obviously SELECT * FROM Education_table isn't the whole thing, and it's unclear what all that From: and Choose garbage afterward is. Judging by the error message, your SQL statement is trying to select a column called HS that doesn't exist in the table, but you're not showing us the SQL statement that gives the error. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sanity Check : Error in sql
Okay ..got to be looking for id's with string, not numerics. Case closed. Oi Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: I'm running into a problem here where mySQL is saying unknown column. Education_table TypeID Type HS High School CO College MA Masters SQL: Select * from Education_table (and no the table is not named table) From: TypeID = Value , Type = Label Choose High School Error Msg: Unknown column 'HS' in 'field list' I tried adding where typeid = type but that makes not sense. I'm now wondering and running (after I post) if type is a no-no-word. I know I post way to often. Stuart -- 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]
huge innodb data files
Our DB in production currently has 2 innodb data files, the second one (which is marked autoextend) has now grown past 26 gigs. We are experiencing weird speed problems with one of the tables. Even though there are no rows in this table, performing any kind of select takes about 2 minutes to execute. Has anyone had a similar problem before ? What can I do to speed up queries to this table ? thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update Questions
Greetings All. I am currently running 3.23.14alpha and have just downloaded the 4.0.20 binary version. I am running under the Slackware Distribution of Linux. I have a variety of PHP related things running currently. Without destroying the current databases and the current tables under the mysql database what would be the simplest method to put the update in place. Hopefully without breaking anything. Thanks in advance Keith
4.1.3 and prepared statements
Title: 4.1.3 and prepared statements
Hi all-
Is the MySQL C API for prepared statements working at all in 4.1.3? I'm trying to do a relatively simple select test, and I consistently get an error on execute = MySQL client run out of memory. But it's a singleton select of one column and the first time I execute?
If this doesn't work, I'm very afraid...
I've attached my simple code.
Here's the table:
create table cfg_2
(config_key varchar(30) not null,
section_key varchar(30) not null,
property_key varchar(255) not null,
property_value varchar(255),
primary key(config_key, section_key, property_key)
) engine=myisam;
insert into cfg_2 values('svp001','stats','number.of.threads','2');
Some other notes:
Solaris 9 Sparc, gcc 3.3.2, using 32 bit, max binary version of mysql for Solaris 9, linked with reentrant client lib (libmyclient_r), using a my-medium.cnf config.
Should I just bag the whole prepared statement thing until it gets stable? And when would that be?
Thx,
Rick
...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DBI and Mysql error 2006
Hi there,
I'm having a problem with MySQL within a Perl::DBI usage. I've turned it
upside-down and cannot find what it's related to.
My Perl script creates multiple databases and populates them with
tables. The problem appears when creating the second database (in the
foreach statement):
#
# Get a database connection to use to create new databases
#
my $tempDSN = 'DBI:mysql:database=test:host=localhost';
our $tempDBH =DBI-connect($tempDSN,$login,$password)
|| die Could not connect to test database: $! ;
#
# Go through a list of databases and create them one by one
#
foreach my $db ( keys(%$list_of_DBs) ) {
#
# Create a DB with the handler we have
#
my $create = $tempDBH-func('createdb',$db,'admin')
||die Could not create database $db : $!;
#
# Connect to the newly created database
#
my $dbDSN = DBI:mysql:database=$db:host=localhost;
my $dbDBH = DBI-connect($dbDSN,$login,$password)
|| die Could not connect to database $db : $!;
#
# Create a set of tables in this database
#
foreach my $table ( keys(%$list_of_tables) ) {
$dbDBH-do(CREATE TABLE ... );
}
#
# Disconnect from the newly created database
#
$dbDBH-disconnect() || die ...;
}
#
# Disconnect from the temporary database connection
#
$tempDBH-disconnect();
So the first database is created with all it's table, but when I get to
the second, I get this error:
Could not create database ...
And when asking more detail with $DBI::errstr:
ERROR: 2006 'MySQL server has gone away'
I've really searched this down in my doc, on the net, and with
DBI-trace(5) but the only thing I figured out is that somehow the
temporary database connection is shut down by something without asking.
But I'm not sure... How could I?
Anyway, if somebody has any idea what this might come from or has come
to something similar in the past, please give me a hint.
Thanks,
Yannick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1.3 and prepared statements
Looks like my attachment did not make it to the post. So here's the code in
question - any thoughts?:
code
#include iostream
#include cstdlib
#include cstring
#include cstdio
#include mysql.h
using namespace std;
int
main(int argc, char *argv[])
{
cout start up endl;
MYSQL * connection;
connection = mysql_init(0);
connection = mysql_real_connect(connection, 0, sfwd, sfwd, sfwd,
0, 0,0);
if (0 == connection)
{
cerr connect failed: mysql_error(connection) endl;
return -1;
}
static const char * sql_statement =
select property_value from cfg_2
where config_key=? and section_key=? and property_key=?;
MYSQL_STMT * stmt_handle = mysql_stmt_init(connection);
if (0 == stmt_handle)
{
cerr stmt init failed: mysql_error(connection) endl;
return -1;
}
int mysql_return_code = mysql_stmt_prepare(stmt_handle,
sql_statement,
strlen(sql_statement)
);
if (0 != mysql_return_code)
{
cerr stmt prepare failed: mysql_stmt_error(stmt_handle)
endl;
mysql_stmt_close(stmt_handle);
return -1;
}
MYSQL_BIND bind_var[3];
unsigned long var_length[3];
my_bool not_null = 0;
char config_key[31] = {'\0'};
char section_key[31] = {'\0'};
char property_key[256] = {'\0'};
bind_var[0].buffer_type = MYSQL_TYPE_VAR_STRING;
bind_var[0].buffer = (char *)config_key;
bind_var[0].buffer_length = sizeof(config_key);
bind_var[0].length = var_length[0];
bind_var[0].is_null = not_null;
bind_var[1].buffer_type = MYSQL_TYPE_VAR_STRING;
bind_var[1].buffer = (char *)section_key;
bind_var[1].buffer_length = sizeof(section_key);
bind_var[1].length = var_length[1];
bind_var[1].is_null = not_null;
bind_var[2].buffer_type = MYSQL_TYPE_VAR_STRING;
bind_var[2].buffer = (char *)property_key;
bind_var[2].buffer_length = sizeof(property_key);
bind_var[2].length = var_length[2];
bind_var[2].is_null = not_null;
strncpy(config_key, svp001, sizeof(section_key)-1);
strncpy(section_key, stats, sizeof(section_key)-1);
strncpy(property_key, number.of.threads, sizeof(property_key)-1);
mysql_return_code = mysql_stmt_bind_param(stmt_handle, bind_var);
if (0 != mysql_return_code)
{
cerr stmt bind failed: mysql_stmt_error(stmt_handle)
endl;
mysql_stmt_close(stmt_handle);
return -1;
}
mysql_return_code = mysql_stmt_execute(stmt_handle);
if (0 != mysql_return_code)
{
cerr stmt execute failed: mysql_stmt_error(stmt_handle)
endl;
mysql_stmt_close(stmt_handle);
return -1;
}
char property_value[256] = {'\0'};
MYSQL_BIND result_var[1];
unsigned long result_var_length[1];
my_bool is_null[1];
result_var[0].buffer_type = MYSQL_TYPE_VAR_STRING;
result_var[0].buffer = (char *)property_value;
result_var[0].buffer_length = sizeof(property_value)-1;
result_var[0].is_null = is_null[0];
result_var[0].length = result_var_length[0];
mysql_return_code = mysql_stmt_bind_result(stmt_handle, result_var);
if (0 != mysql_return_code)
{
cerr stmt bind rslt failed: mysql_stmt_error(stmt_handle)
endl;
mysql_stmt_close(stmt_handle);
return -1;
}
mysql_return_code = mysql_stmt_store_result(stmt_handle);
if (0 != mysql_return_code)
{
cerr stmt store rslt failed: mysql_stmt_error(stmt_handle)
endl;
mysql_stmt_close(stmt_handle);
return -1;
}
mysql_return_code = mysql_stmt_fetch(stmt_handle);
if (0 != mysql_return_code MYSQL_NO_DATA != mysql_return_code)
{
cerr stmt fetch failed: mysql_stmt_error(stmt_handle)
endl;
mysql_stmt_free_result(stmt_handle);
mysql_stmt_close(stmt_handle);
return -1;
}
if (MYSQL_NO_DATA != mysql_return_code)
{
cout stmt - got: property_value endl;
}
mysql_stmt_free_result(stmt_handle);
mysql_stmt_close(stmt_handle);
cout done endl;
return 0;
}
/code
_
From: Rick Robinson [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 26, 2004 6:24 PM
To: Mysql
Subject: 4.1.3 and prepared statements
Hi all-
Is the MySQL C API for prepared statements working at all in 4.1.3? I'm
trying to do a relatively simple select test, and I consistently get an
error on execute = MySQL client run out of memory. But it's a singleton
select of one column and the first time I execute?
If this doesn't work, I'm very afraid...
I've attached my simple code.
Here's the table:
create table cfg_2
(config_key varchar(30) not null,
section_key varchar(30) not null,
property_key varchar(255) not null,
property_value varchar(255),
primary key(config_key, section_key, property_key)
) engine=myisam;
Re: Brainstorming' time! (export to excel via java)
As for programming skills, I'm pretty familiar with Java so I feel sure I could write a suitable utility with that. In fact, unless I had a really urgent need, I would prefer to write my own stuff just because I like writing code If you wanted to go the java route I suggest checking out http://jdbforms.sourceforge.net/wiki.php which automatically generates jsp forms by reading your table definition. You can then add a simple button to export to excel such as: db:gotoButton caption=export_to_excel singleRow=false destTable=BOOK destination=/excelReport/books?filename=BOOK.xlssheetname=BOOKLIST / (example from dbforms/examples/bookstore/howto/howtoPrintaExcelReport.jsp) so then regular users can use a web based form to insert/update/filter/sort the information and then export what they needed to xls. This above example uses the Jakarta POI - Java API To Access Microsoft Format Files --http://jakarta.apache.org/poi/ Otherwise you could also use DbForms to pass the data to a JasperReport which allows excel output as well http://jasperreports.sourceforge.net/ see dbforms/examples/bookstore/howto/howtoPrintaExcelReport.jsp also see the JasperReports Chapter of the UsersGuide for dbforms if interested on how to configure it. dbforms also lets you do neat things like Select type, sum(value) as sumValue where date ? group by type having sumValue ? where because the date should be selected by the user, you can not use a fixed view in the database (see the dbform's usersguide chapter on Query Support) Have a good one, -- Shawn Karma is immutable, so act accordingly! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow Queries on Fast Server?
John,
From my experience it is a lot more on how big is your data, not necessarily
the amount of data. Which version of mysql are you running? Are you using
a mysql prepared version (you downloaded it from mysql.com). I'm using
4.1.3 and I have a table that has a char 68 with 29 million rows that is
fulltext indexed and all of my queries using something similar to yours take
0.1 to 0.2 seconds max. Also if you provided your full table structure
including the indexes that would help.
Donny
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 26, 2004 8:08 AM
To: [EMAIL PROTECTED]
Subject: Re: Slow Queries on Fast Server?
I'm gathering by the lack of response that perhaps MySQL is incapable of
executing a count of the number of fulltext matches on 3 million rows.
I really thought that MySQL 4 was really suppose to be able to handle such
a load
I still think my configuration may be to blame
?
- John
--
Could you send the output of an EXPLAIN for your query?
Sure, pretty sure the index is fine though:
mysql EXPLAIN SELECT COUNT(*) FROM product_fulltext WHERE MATCH
(search_text) AGAINST
('black');
+--+--+---+-+-+---
---+--+-+
| table| type | possible_keys | key | key_len |
ref | rows |
Extra |
+--+--+---+-+-+---
---+--+-+
| product_fulltext | fulltext | search_text | search_text | 0 |
|1 |
Using where |
+--+--+---+-+-+---
---+--+-+
1 row in set (0.00 sec)
[EMAIL PROTECTED] wrote:
Have you checked the Optimization section of the manual yet?
http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html
Oh yes, as I've attempted to configure the my.cnf file for best
performance. The
query is correct. The fulltext index is correct as I built the fulltext
index on
the
single column (took 9 minutes) and even did repair and optimize on
the table...
so I
don't think its the index. I'm thinking its the server config...
- John
[EMAIL PROTECTED] wrote:
I'm running into a problem with some queries running on a dedicated
mysql server
(2.0
GHz, 2GB RAM). Fulltext searching really exemplifies this as most
MATCH, AGAINST
queries
are taking 5-20 seconds. Performance was excellent for some reason one
day (0.2
-
0.75
seconds) but it was only fast for a day or so.
Here's the rundown:
TABLE: fulltext_table (some_id, the_text)
Rows: 3,237,981
Type: MyISAM
Size: 920.8 MB
QUERY: SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text)
AGAINST
('blue');
or
QUERY: SELECT SQL_CALC_FOUND_ROWS some_id FROM fulltext_table WHERE
MATCH
(the_text)
AGAINST ('blue') LIMIT 0, 20;
Both are problematic. I even tried placing a limit of 2 on the
first query
but
it didn't improve anything. The table has a fulltext index on the
column and is
optimized. No other users are connected to the server.
Is there a RED FLAG in here somewhere?
MySQL configuration settings (using my-huge.cnf template):
key_buffer = 500M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 10M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
#thread_cache = 8
thread_concurrency = 8
#- Modifications --- #
ft_min_word_len = 3
set-variable = table_cache=1024
set-variable = max_heap_table_size=64M
set-variable = tmp_table_size=128M
set-variable = query_cache_limit=2M
query_cache_type=1
Performance Test:
SELECT COUNT(*) FROM fulltext_table WHERE MATCH (the_text) AGAINST
('white');
+--+
| COUNT(*) |
+--+
|95074 |
+--+
1 row in set (27.83 sec)
Statistics for vmstat 1 (my apologies if this doesn't look pretty):
---
procs memory swap io system
cpu
r b swpd free buff cache si sobibo incs us sy
id wa
0 0 19500 17800 42432 177172800 060 11330 0 0
99 1
0 1 19500 21524 42428 1765728 7240 960 0 536 444 5
1 82 12
0 1 19500 19512 42424 176782000 2348 0 912 592 0
1 50 49
0 1 19500 17788 42424 176954000 1980 0 868 588 0
1 51 48
0 1 19500 17568 42424 176976000 2300 0 723 401 0
0 50 49
0 1 19500 17704 42428 176962000 193620 662 364 0
0 51 49
0 1 19500 17560 42428 176976400 2224 0 696 400 0
0 51 49
0 1 19500 17504 42424 176982400 2136 0 670 380 0
0 51 49
0 1 19500 17616 42424 176971200 2228 0 693 415 0
0 51 49
0 1 19508 17608 42420 176972408 2348 8 692 389 0
0 50 50
0 1 19508 17532 42428 17697920
table conversion problems
Hi all, Started a conversion from MyISAM to InnoDB; it's been almost two days and the statement is still executing... The (MyISAM) data table size is almost 4G. There were two reasons for this conversion: to start supporting transactions and to avoid the 4G limit of MyISAM tables; this table has been created without explicitly specifying MAX_ROWS and AVG_ROW_LENGTH. The avg row length is 28 bytes, there's only a primary key comprised of 3 integers. The state of this thread that's performing the conversion is Copy to tmp table. We need to start updating the table as soon as possible... Is there a way to monitor the progress? Is the tmp table allocated in InnoDB tablespace? What are the consequences of killing the thread? Will it waste whatever InnoDB tablespace has been already used for this conversion? I'm using Mysqlcc. How long may it take to cancel this statement by pushing Cancel execution and clear results button? Sometimes it takes a while... What does this button actually do? Thanks in advance for your help! -- Sergei
RE: table conversion problems
Sergei, I don't know much about innodb, but myisam doesn't have a 4 gig limit unless you are using a dynamic type of table. If you are using a fixed table which is by using int, char, etc... Not text, varchar, blobs. As long as you don't use the last ones, you don't have a 4 gig limit. As far as your questions about innodb, can't help you there. Except for in my case when I have switched tables to innodb, I copy them in chunks to speed up the process. That's what it says on the innodb website, so that's what we did. Donny -Original Message- From: Sergei Skarupo [mailto:[EMAIL PROTECTED] Sent: Thursday, August 26, 2004 8:36 PM To: Mysql List (E-mail) Subject: table conversion problems Hi all, Started a conversion from MyISAM to InnoDB; it's been almost two days and the statement is still executing... The (MyISAM) data table size is almost 4G. There were two reasons for this conversion: to start supporting transactions and to avoid the 4G limit of MyISAM tables; this table has been created without explicitly specifying MAX_ROWS and AVG_ROW_LENGTH. The avg row length is 28 bytes, there's only a primary key comprised of 3 integers. The state of this thread that's performing the conversion is Copy to tmp table. We need to start updating the table as soon as possible... Is there a way to monitor the progress? Is the tmp table allocated in InnoDB tablespace? What are the consequences of killing the thread? Will it waste whatever InnoDB tablespace has been already used for this conversion? I'm using Mysqlcc. How long may it take to cancel this statement by pushing Cancel execution and clear results button? Sometimes it takes a while... What does this button actually do? Thanks in advance for your help! -- Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
another newb question for you help...
Hi again, I'm inserting some test products into my new mysql database but it only lets me enter so much? I thought BLOB would allow alot more characters? Anyway, this was how I constructed the database. CREATE TABLE jspCart_products ( ProductID int primary key, CategoryID int, ModelNumber varChar(75), ModelName varChar(250), ProductImage varchar(250), UnitCost decimal(9,2), Description BLOB, ); This is what I am trying to enter... INSERT into jspCart_products VALUES(188, 4, f23, modelxyz,../images/product1.jpg, 20.95,Hi my name is frank and I'd like to say like WOW man.This is a lengthy description of this product. It is A numero Uno on my list like wow I love this product so much it is unbelievable!); But it only lets me type in the command window this much INSERT into jspCart_products VALUES(188, 4, f23, modelxyz,../images/product1.jpg, 20.95,Hi my name is frank and I'd like to say like WOW man.This is a lengthy description of this product. Can someone help me out here? Thanks in advance, -Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table conversion problems
Hi Donny, Thanks for your reply. This table only uses ints and floats, but the floats are allowed to be null, which means, as far as I understand, that it's not a fixed row length... By the way, Paul DuBois writes about the 4G limit in MySQL, second edition, and does not mention that fixed rows make a difference as far as the size limit is concerned. Could you please point me to a page about copying data in chunks on InnoDB website? In any case, it's kind of too late now -- my alter command is already running :) -- Sergei -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: Thursday, August 26, 2004 6:37 PM To: Sergei Skarupo; 'Mysql List (E-mail)' Subject: RE: table conversion problems Sergei, I don't know much about innodb, but myisam doesn't have a 4 gig limit unless you are using a dynamic type of table. If you are using a fixed table which is by using int, char, etc... Not text, varchar, blobs. As long as you don't use the last ones, you don't have a 4 gig limit. As far as your questions about innodb, can't help you there. Except for in my case when I have switched tables to innodb, I copy them in chunks to speed up the process. That's what it says on the innodb website, so that's what we did. Donny -Original Message- From: Sergei Skarupo [mailto:[EMAIL PROTECTED] Sent: Thursday, August 26, 2004 8:36 PM To: Mysql List (E-mail) Subject: table conversion problems Hi all, Started a conversion from MyISAM to InnoDB; it's been almost two days and the statement is still executing... The (MyISAM) data table size is almost 4G. There were two reasons for this conversion: to start supporting transactions and to avoid the 4G limit of MyISAM tables; this table has been created without explicitly specifying MAX_ROWS and AVG_ROW_LENGTH. The avg row length is 28 bytes, there's only a primary key comprised of 3 integers. The state of this thread that's performing the conversion is Copy to tmp table. We need to start updating the table as soon as possible... Is there a way to monitor the progress? Is the tmp table allocated in InnoDB tablespace? What are the consequences of killing the thread? Will it waste whatever InnoDB tablespace has been already used for this conversion? I'm using Mysqlcc. How long may it take to cancel this statement by pushing Cancel execution and clear results button? Sometimes it takes a while... What does this button actually do? Thanks in advance for your help! -- Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: another newb question for you help...
- Original Message - From: B Wiley Snyder [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 26, 2004 10:31 PM Subject: another newb question for you help... Hi again, I'm inserting some test products into my new mysql database but it only lets me enter so much? I thought BLOB would allow alot more characters? Anyway, this was how I constructed the database. CREATE TABLE jspCart_products ( ProductID int primary key, CategoryID int, ModelNumber varChar(75), ModelName varChar(250), ProductImage varchar(250), UnitCost decimal(9,2), Description BLOB, ); This is what I am trying to enter... INSERT into jspCart_products VALUES(188, 4, f23, modelxyz,../images/product1.jpg, 20.95,Hi my name is frank and I'd like to say like WOW man.This is a lengthy description of this product. It is A numero Uno on my list like wow I love this product so much it is unbelievable!); But it only lets me type in the command window this much INSERT into jspCart_products VALUES(188, 4, f23, modelxyz,../images/product1.jpg, 20.95,Hi my name is frank and I'd like to say like WOW man.This is a lengthy description of this product. Can someone help me out here? Your immediate question is essentially about the longest command that you can type at the command line; I'm not really sure of the answer. I very rarely do long commands like that from the command line. However, just for the heck of it, I just signed on the MySQL and got to a command prompt. I found that you can make the command pretty much as long as you like: just press enter whenever you're running out of room and a new line with a new '-' prompt will appear for you to write the next part of your command. End your statement with a semicolon, press Enter and the continuation prompts will stop and your statement will execute. Now, *please* tell me that you don't propose to populate your entire table this way and that you are just trying to create a couple of rows from the command line so that you know how to do it I don't think anyone with any pretensions of being professional would populate anything but the tiniest test table via the command line. Put your data into a flat file - or export it into a flat file from wherever it is now - and use the LOAD DATA utility to populate your table. You can build scripts that include the LOAD DATA command plus whatever SQL you need to create and/or verify the contents of your table; creating and running the script will only take you a few minutes once you get on to the technique and be a LOT less painful - and less error-prone - than writing individual INSERT statements for every row in your table. I can post some examples if you're not sure what I mean. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
