Re: Problem searching in grouped rows
Barry schrieb: Hello everyone! I have a problem with matching in grouped rows. I have: - one DB with customers - one DB with advertisement articles - one DB that holds what customer got which article the linked DB looks like: CREATE TABLE adverticlelink ( c_id int(11) NOT NULL, aa_id int(11) NOT NULL, recieved date NOT NULL, PRIMARY KEY (k_id,ml_id) ) ENGINE=MyISAM; ++-+---+ |c_id|aa_id|recieved | ++-+---+ |4 |2|48642465464| |4 |6|35465432234| |4 |15 |31354513213| ++-+---+ I want now to match customers that got for example the advertisement umbrella but not the advertisement zippo. No idea how to start that query. On top of that is use the MySQL Version 3.23.54, for pc-linux (i686). Anyone with any ideas? I did try it with WHERE (aa_id = 4) AND (aa_id != 6) GROUP BY c_id Also tried the HAVINg clause but that looked really false. Thanks for any replies :) Barry Noone? :( -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
¿mysql_stmt_bind_param under windows bug?
I tried to execute the example code (see below) from the function
mysql_stmt_execute I found in mysql 4.1 documentation
(http://dev.mysql.com/doc/refman/4.1/en/mysql-stmt-execute.html). This source
code runs perfect under linux red hat with version 4.1.7 but it prints out an
error if I try to run it under windows XP SP2.
This is the output of this code
prepare, INSERT successful
total parameters in INSERT: 3
mysql_stmt_bind_param() failed
Using unsupported buffer type: 838860800 (parameter: 2)
It seems that the function mysql_stmt_bind_param is not working under this
environment.
I tried installing version 4.1.20 and linking my program with the new library
but the result is the same.
Is that a bug of windows version? Can anyone help?
Thanks in advanced
Javier Arias
/*/
#define STRING_SIZE 50
#define DROP_SAMPLE_TABLE DROP TABLE IF EXISTS test_table
#define CREATE_SAMPLE_TABLE CREATE TABLE test_table(col1 INT,\
col2 VARCHAR(40),\
col3 SMALLINT,\
col4 TIMESTAMP)
#define INSERT_SAMPLE INSERT INTO test_table(col1,col2,col3) VALUES(?,?,?)
MYSQL_STMT*stmt;
MYSQL_BINDbind[3];
my_ulonglong affected_rows;
int param_count;
short small_data;
int int_data;
char str_data[STRING_SIZE];
unsigned long str_length;
my_bool is_null;
if (mysql_query(mysql, DROP_SAMPLE_TABLE))
{
fprintf(stderr, DROP TABLE failed\n);
fprintf(stderr, %s\n, mysql_error(mysql));
exit(0);
}
if (mysql_query(mysql, CREATE_SAMPLE_TABLE))
{
fprintf(stderr, CREATE TABLE failed\n);
fprintf(stderr, %s\n, mysql_error(mysql));
exit(0);
}
/* Prepare an INSERT query with 3 parameters */
/* (the TIMESTAMP column is not named; the server */
/* sets it to the current date and time) */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, mysql_stmt_init(), out of memory\n);
exit(0);
}
if (mysql_stmt_prepare(stmt, INSERT_SAMPLE, strlen(INSERT_SAMPLE)))
{
fprintf(stderr, mysql_stmt_prepare(), INSERT failed\n);
fprintf(stderr, %s\n, mysql_stmt_error(stmt));
exit(0);
}
fprintf(stdout, prepare, INSERT successful\n);
/* Get the parameter count from the statement */
param_count= mysql_stmt_param_count(stmt);
fprintf(stdout, total parameters in INSERT: %d\n, param_count);
if (param_count != 3) /* validate parameter count */
{
fprintf(stderr, invalid parameter count returned by MySQL\n);
exit(0);
}
/* Bind the data for all 3 parameters */
memset(bind, 0, sizeof(bind));
/* INTEGER PARAM */
/* This is a number type, so there is no need to specify buffer_length */
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)int_data;
bind[0].is_null= 0;
bind[0].length= 0;
/* STRING PARAM */
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)str_data;
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null= 0;
bind[1].length= str_length;
/* SMALLINT PARAM */
bind[2].buffer_type= MYSQL_TYPE_SHORT;
bind[2].buffer= (char *)small_data;
bind[2].is_null= is_null;
bind[2].length= 0;
/* Bind the buffers */
if (mysql_stmt_bind_param(stmt, bind))
{
/*THE ERROR
IS HERE/
fprintf(stderr, mysql_stmt_bind_param() failed\n);
fprintf(stderr, %s\n, mysql_stmt_error(stmt));
exit(0);
}
your chance
Please, read and let me know what do you feel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL on MySQL 5.0.22
Hi, I am using 5.0.22 on Windows. As I read in the reference manual, almost ALL SSL values/options are specific to 5.1.x versions. Do I stand _ANY_ chance to use SSL anyway? Specifically, I want to know exactly HOW I can enable SSL support for MySQL on Windows using which options, variables, etc. Thanks in advance, -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: your chance
I read that message and had no feelings. [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Please, read and let me know what do you feel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: your chance
Same here On Mon, 2006-06-26 at 06:46 -0300, João Cândido de Souza Neto wrote: I read that message and had no feelings. [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Please, read and let me know what do you feel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is selectivity?
Hi! leegold wrote: Reading about DBs I am seeing the term selectivity. What does it mean? Seems like it has something to do with the distribution or pattern of data in tables(?) Right, it does. A search criterion is highly selective if it _ex_cludes a high proportion of the data. My standard example is an index on the field gender (or sex) of a table listing all members of a typical army: As most soldiers are male, a search ... where gender = 'm' would return something like 90 % of the table's rows, so it is not very selective. It's coming up in discussions about optimization... Yes. Depending on the command and its complexity, one general approach to optimization may be to reduce the amount of data to handle as fast as possible - by applying the criterion with the highest selectivity. Especially with a cost-based optimizer, correct assumptions about the selectivity of the various criteria are needed to determine the best strategy. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem searching in grouped rows
I'll give it a shot. First, select the people that got the first advertisement: SELECT c_id,aa_id FROM adverticelink WHERE aa_id=4 From that result, you want to additionally filter out who didn't get the second advertisement. Since that information is contained in the same table, you want to do a self join. A self join will require you to use an alias name for the table, since you can't have two tables with the same name. We'll use a1 and a2 as the alias names. Additionally, you want to do a left join to retain all the records from your originally query. So you are actually joining the query of those who received the first ad, with those who received the second ad. Since you are doing a left join, those who didn't receive the second ad will not have a value for the aa_id field. It will be NULL. SELECT a1.c_id,a1.aa_id,a2.aa_id FROM adverticelink AS a1 LEFT JOIN adverticelink AS a2 ON (a1.c_id=a2.c_id AND a2.aa_id=6) WHERE a1.aa_id=4 AND a2.aa_id IS NULL That should work in 3.23. - Original Message - From: Barry [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, June 26, 2006 3:58 AM Subject: Re: Problem searching in grouped rows Barry schrieb: Hello everyone! I have a problem with matching in grouped rows. I have: - one DB with customers - one DB with advertisement articles - one DB that holds what customer got which article the linked DB looks like: CREATE TABLE adverticlelink ( c_id int(11) NOT NULL, aa_id int(11) NOT NULL, recieved date NOT NULL, PRIMARY KEY (k_id,ml_id) ) ENGINE=MyISAM; ++-+---+ |c_id|aa_id|recieved | ++-+---+ |4 |2|48642465464| |4 |6|35465432234| |4 |15 |31354513213| ++-+---+ I want now to match customers that got for example the advertisement umbrella but not the advertisement zippo. No idea how to start that query. On top of that is use the MySQL Version 3.23.54, for pc-linux (i686). Anyone with any ideas? I did try it with WHERE (aa_id = 4) AND (aa_id != 6) GROUP BY c_id Also tried the HAVINg clause but that looked really false. Thanks for any replies :) Barry Noone? :( -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- 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: selecting data from 2 local DBs with same table structure
Thanks Peter, this should give me a head start. I will try it and mail again if I get stuck. Craig On 6/25/06, Peter Brawley [EMAIL PROTECTED] wrote: I need the query to select the data from table1 on db1 and then select the data from table1 on db2 and return a result of where if a field data value is found in both tables that matches on name for example, it would count the occurrences. Not entirely clear. Do you mean ... SELECT name, COUNT(field) AS Count1, (SELECT COUNT(field) FROM db2.tbl2 WHERE db2.tbl2.name=db1.tbl1.name) AS Count2 FROM db1.tbl1 GROUP BY name; ? PB - Cx Cx wrote: Hello List! Does anybody know how to select and build a result set from two or more databases with the same table structures that are on the same server. I need the query to select the data from table1 on db1 and then select the data from table1 on db2 and return a result of where if a field data value is found in both tables that matches on name for example, it would count the occurrences. Thanks in advance Craig -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.3/374 - Release Date: 6/23/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.3/374 - Release Date: 6/23/2006
FreeBSD 6 and MySQL with DBs on a NAS
*** This happens for me using FreeBSD 6.0 or FreeBSD 6.1 with the most recent MySQL 4.1 or 5.0 built from ports and when the DBMS data files reside on a NetApp NAS share shared over NFS. It only seems to happen with very frequently written-to tables. I sent this to the list last week and no one responded. *** Hi, I was wondering if anyone else had encountered this issue and/or come up with what needs to be done to resolve it: I currently have MySQL 5.0.22 built from ports on a FreeBSD 6.1 machine with the DB data residing on a NetApp share connected via NFS. A strange thing happens often after a few hours or a couple of days, some tables that are very active start to crash for no apparent reason as far as I can tell. Example output from check table tablename: ++---+--+---+ | Table | Op| Msg_type | Msg_text | ++---+--+---+ | dbname.tablename | check | warning | Table is marked as crashed | | dbname.tablename | check | error| Found key at page 18259968 that points to record outside datafile | | dbname.tablename | check | error| Corrupt | ++---+--+---+ I've seen this happen on FreeBSD 6.0 and 6.1 with MySQL 4.1.x and MySQL 5.0.x built from ports. Has anyone else seen this and if so has a resolution been found? -- Mark P. Hennessy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ¿mysql_stmt_bind_param under windows bug?
On Monday 26 June 2006 01:04 am, Arias Gonzalez, Javier wrote: memset(bind, 0, sizeof(bind)); This is probably your issue right here. You've already got bind[3] with storage allocation for 3 MYSQL_BIND's. Now you're filling bind[0] with 0's, effectively erasing the allocated MYSQL_BIND in bind[0]. Take that line out and things should work. -- Chris White PHP Programmer / DBlair Witch Project Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL on MySQL 5.0.22
Hi, How can I connect from a Windows client (VB/ODBC Connector) to a MySQL Server 5.0.22, also running on Windows (Max version installed as a service using Local-Service Account) with all tables using InnoDB storage engine USING SSL? Perhaps using OpenSSL on a Linux-based installation would be easier. But mine is a Windows-based MySQL Server. Any hints, comments, help? -- Best regards, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Entries
If I have a multiple choice on a form and want to store that in my DB, then how should I set up my table? I have been reading up on these, but everyone seems to have a different opinion on how to accomplish this task. What I am looking to do give the user a few options to check when submitting a form. select name=sports multiple id=sport_type option value=baseballBaseball/option option value=footballFootball/option option value=soccerSoccer/option option value=hockeyHockey/option /select Would my table look like this: CREATE TABLE sports ( sports_id int(11) not null auto_incremement, sport_name text not null, primary key (sports_id) ); INSERT INTO `tbl_options` VALUES (1, 'Baseball'); INSERT INTO `tbl_options` VALUES (2, 'Football'); INSERT INTO `tbl_options` VALUES (3, 'Soccer'); INSERT INTO `tbl_options` VALUES (4, 'Hockey'); ... Would using text as the way to store make it easier to retrieve the data in a manner that would be readable on a web page? Thanks, Nick
Re: A lot of HD Writing
At 10:36 AM 6/25/2006, Santiago del Castillo wrote: Hi, thanks for answering! I am using MyISAM tables. So, based in your answer i assume that is usual to have a lot of HD Writing, isn't it? Only if your application is writing to the database. If you have no application running, then MySQL should have no activity. So you must have some sort of application writing or reading from the database. What does Show ProcessList display? (Run MySQL.Exe and run this command.) This will tell you which user is connected to the database and what sql command is executing. Post the results here so we can look at it. Mike Thanks! Santiago mos wrote: At 06:55 PM 6/24/2006, Santiago del Castillo wrote: Hi, is usual to have a lot of HD writing on a MySQL server where (according to mytop) there are between 800 and 1200 queries per second? my MRTG is showing a lot of HD Writing and i wanted to know if it's usual. The database is growing about 11 MB every 3 minutes. FYI, I don't have any log-type (binlog, queries log or slow queries log) activated. Well, if this keeps up, you better run out and buy more hard drives.g Are you using InnoDb or MyISAM tables? You can run Show ProcessList to see what task is currently executing on the MySQL server. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Entries
Nicholas Vettese wrote: If I have a multiple choice on a form and want to store that in my DB, then how should I set up my table? I have been reading up on these, but everyone seems to have a different opinion on how to accomplish this task. What I am looking to do give the user a few options to check when submitting a form. select name=sports multiple id=sport_type option value=baseballBaseball/option option value=footballFootball/option option value=soccerSoccer/option option value=hockeyHockey/option /select Would my table look like this: CREATE TABLE sports ( sports_id int(11) not null auto_incremement, sport_name text not null, primary key (sports_id) ); INSERT INTO `tbl_options` VALUES (1, 'Baseball'); INSERT INTO `tbl_options` VALUES (2, 'Football'); INSERT INTO `tbl_options` VALUES (3, 'Soccer'); INSERT INTO `tbl_options` VALUES (4, 'Hockey'); ... Would using text as the way to store make it easier to retrieve the data in a manner that would be readable on a web page? Thanks, Nick I personally would fill the values with the ID numbers myself for a couple of reasons: a) Your sports_id is PK and is auto incremented b) You'll save some bytes for your users for downloading (and save some for yourself) by using a number (just a few bytes per id, opposed to the entire sports name.) Text would make it easier later if you ever needed to put some reay long sports name, but a char field may work in this case as well. ... You would end up with this where the sports_id is used for the value. select name=sports multiple id=sport_type option value=1Baseball/option option value=2Football/option option value=3Soccer/option option value=4Hockey/option /select Tho, this is what *I* would do. It may not be the best solution. You'll have to look at a number of factors before deciding on how to store/display data to/from a database. You'll want something fast and decently scalable, with out seeing the whole picture it is hard to tell you exactly what *you* should be doing. :-D -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Entries
On Monday 26 June 2006 08:03 am, Nicholas Vettese wrote: If I have a multiple choice on a form and want to store that in my DB, then how should I set up my table? I have been reading up on these, but everyone seems to have a different opinion on how to accomplish this task. What I am looking to do give the user a few options to check when submitting a form. select name=sports multiple id=sport_type option value=baseballBaseball/option option value=footballFootball/option option value=soccerSoccer/option option value=hockeyHockey/option /select Hmm, ok... Would my table look like this: CREATE TABLE sports ( sports_id int(11) not null auto_incremement, sport_name text not null, Woh there! TEXT is probably overkill, I'd recommend VARCHAR for that (120 or so, 255 if you're feeling lucky). As far as the sport_name, you could store it as lowercase, and use the ucwords (http://www.php.net/ucwords) function in PHP to make it uppercase for the values. primary key (sports_id) ); -- Chris White PHP Programmer/DB BD Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unknown tables
Oh, OK. Well, guess which driver I'm using... InnoDB. :-) Oh well. Thanks, Jesse - Original Message - From: Quentin Bennett [EMAIL PROTECTED] To: Jesse [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com Sent: Sunday, June 18, 2006 5:28 PM Subject: RE: Unknown tables but I assume you mean to check the information_schema database and the schemata table? No, Rich meant check the mysql data directory for a karate directory. MySQL databases (for MyISAM tables) are stored in their own directory, and each table is a set of 3 files, so its easy to check for file existance, permissions etc. I can't comment on InnoDB tables. Quentin The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Defaulting Date Field
What is the best way to default a date/time field to the current date/time? I've tried using Now(), but I get an error of course. The only other way I know of to do this is to add a trigger, which I can do, but I find them bothersome, because they don't tend to backup and restore properly. Is there another way to do this? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help on join query
Hi All, Given this table: +-+-+++- |sip_status | sip_method | sip_callid | username | fromtag| totag | time| timestamp | +-+-+++- |200| INVITE | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:27:39 | 2006-06-19 05:27:39 | |200| ACK| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:27:39 | 2006-06-19 05:27:39 | |200| INVITE | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:27:59 | 2006-06-19 05:27:59 | |200| ACK| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:00 | 2006-06-19 05:28:00 | |200| INVITE | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:15 | 2006-06-19 05:28:15 | |200| ACK| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:15 | 2006-06-19 05:28:15 | |200| INVITE | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:16 | 2006-06-19 05:28:16 | |200| ACK| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:16 | 2006-06-19 05:28:16 | |200| INVITE | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:16 | 2006-06-19 05:29:16 | |200| ACK| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:17 | 2006-06-19 05:29:17 | |406| REFER | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:19 | 2006-06-19 05:29:19 | |200| BYE| [EMAIL PROTECTED] | 0061396962022 |3359683324-99483 | e8936439e4bdfb0co0 | 2006-06-19 13:29:19 | 2006-06-19 05:29:19 | |481| BYE| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:19 | 2006-06-19 05:29:19 | |200| INVITE | [EMAIL PROTECTED] | 5093 | 73af10c095f4a93do1 | 3359711691-612956 | 2006-06-19 21:20:25 | 2006-06-19 13:20:25 | |200| ACK| [EMAIL PROTECTED] | 5093 | 73af10c095f4a93do1 | 3359711691-612956 | 2006-06-19 21:20:25 | 2006-06-19 13:20:25 | |481| BYE| [EMAIL PROTECTED] | 5093 | 73af10c095f4a93do1 | 3359711691-612956 | 2006-06-19 21:22:11 | 2006-06-19 13:22:11 | |481| BYE| [EMAIL PROTECTED] | 0027164306000 | 3359711691-612956 | 73af10c095f4a93do1 | 2006-06-19 21:22:14 | 2006-06-19 13:22:14 | +--+--+++--- I tried this query: SELECT t1.sip_callid as callid, t1.username as username, t1.sip_method as t1meth, t2.sip_method as t2meth, t3.sip_method as t3meth, t1.time as start, t2.time as stop, TIMEDIFF(t2.time,t1.time) as timediff FROM acc AS t1 INNER JOIN acc AS t2 ON t1.sip_callid = t2.sip_callid AND ((t1.fromtag = t2.fromtag AND t1.totag = t2.totag) OR (t1.fromtag = t2.totag AND t1.totag = t2.fromtag)) LEFT JOIN acc AS t3 ON t1.sip_callid = t3.sip_callid AND ((t1.fromtag = t2.fromtag AND t1.totag = t2.totag) OR (t1.fromtag = t2.totag AND t1.totag = t2.fromtag)) AND t3.sip_method='INVITE' AND t1.time t3.time WHERE t1.sip_method='INVITE' AND t2.sip_method='BYE' AND t3.sip_method IS NULL; but i still get this result +--+--++++-+-+--+ | callid | username | t1meth | t2meth | t3meth | start | stop| timediff | +--+--++++-+-+--+ | [EMAIL PROTECTED] | 5093 | INVITE | BYE| NULL | 2006-06-19 21:20:25 | 2006-06-19 21:22:11 | 00:01:46 | | [EMAIL PROTECTED] | 5093 | INVITE | BYE| NULL | 2006-06-19 21:20:25 | 2006-06-19 21:22:14 | 00:01:49 | | [EMAIL PROTECTED] | 5743 | INVITE | BYE| NULL | 2006-06-19 13:27:39 | 2006-06-19 13:29:19 | 00:01:40 | | [EMAIL PROTECTED] | 5743 | INVITE | BYE| NULL | 2006-06-19 13:27:39 | 2006-06-19 13:29:19 | 00:01:40 | +--+--++++-+-+--+ What query should I use to make it look like these:
Re: Windows Compiled Help MySQL Reference Manual -- Error
On 6/13/06, Jake Peavy [EMAIL PROTECTED] wrote: On 6/7/06, Jake Peavy [EMAIL PROTECTED] wrote: On 6/7/06, Paul DuBois [EMAIL PROTECTED] wrote: At 17:30 -0600 6/7/06, Jake Peavy wrote: Hey yall, I'm unable to open the .chm I just downloaded for 5.1. I get the following error: Cannot open the file: mk:@MSITStore:C:\Documents and Settings\jpeavy1\My Documents\refman-5.1-en.chm. Any ideas? It does seem to be corrupt. We'll take a look into it. Thanks. Great. Can you respond to this when you have an updated file up? How's this coming along? Still corrupt I see. Seems like it ought to be a pretty quick fix. wtf? _still_ corrupted? -- -jp We all know the magic word is please. As in the sentence, Please don't kill me. Too bad Chuck Norris doesn't believe in magic.
Re: Defaulting Date Field
Jesse, a TIMESTAMP column in your table can be set to have the 'now' value when a row is inserted, or updated, or both. http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html Note the warning about TIMESTAMP behavior differing significantly prior to 4.1. Dan Jesse wrote: What is the best way to default a date/time field to the current date/time? I've tried using Now(), but I get an error of course. The only other way I know of to do this is to add a trigger, which I can do, but I find them bothersome, because they don't tend to backup and restore properly. Is there another way to do this? Thanks, Jesse -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error loading data from file ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
Does MySQL have any constraints when it comes to the number columns that it can accurately support/import in any one table/file. When I see this file that is failing to get properly read into the database, that's what comes to mind... It's failing because of some kind of constraint or threshold of the db so instead of giving a meaningful messgae, it just says: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed Ferindo On 6/23/06, Ferindo Middleton [EMAIL PROTECTED] wrote: I guess my general reason for posting this was to ask: Are there any known issues with the LOAD DATA INFILE comand in MySQL? However, I stripped all the data in the file (test.tab) down to one record which still wouldn't load. Here is the command: mysql LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Tomcat 5.5/ webapps/utrad/docs/rebuild_scratch_area/test.tab' INTO TABLE reggie; The fields inside the file are tab-delimited and look like this: 16411 5 Rupert Settles Settles Settles 1 esunindyo \N 207 \N 12 \N 2005-01-03 2005-01-07 1 1 1 0 \N Deobligation 1 2 \N fmiddleton 0 0 \N \N 11:00:00 \N \N \N 0 0 4 0 0 \N \N \N 1 The structure of the TABLE reggie is this: CREATE TABLE `reggie ` ( `id` bigint(20) unsigned NOT NULL auto_increment, `title_salutation_id` bigint(20) unsigned NOT NULL, `firstname` varchar(128) NOT NULL, `middlename` varchar(128) NOT NULL default '', `lastname` varchar(128) NOT NULL default '', `suffix` varchar(128) NOT NULL default '', `paper_received` tinyint(1) NOT NULL default '1', `addr` text, `cc_email_list` text, `fortran_id` bigint(20) default NULL, `office` text, `class_id` bigint(20) unsigned NOT NULL, `schedule_id` bigint(20) default NULL, `start_date` date NOT NULL default '-00-00', `end_date` date NOT NULL default '-00-00', `enrolled` tinyint(1) default NULL, `attended` tinyint(1) default NULL, `completed` tinyint(1) default NULL, `cancelled` tinyint(1) default '0', `cancelled_comments` text, `comments` text, `email_confirmation_sent` tinyint(1) NOT NULL, `employment_status_id` bigint(20) unsigned NOT NULL default '0', `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `last_user_updated` text, `waitlisted` tinyint(1) default '0', `overflow_registrant` tinyint(1) default '0', `attach_hotel_listing_directions` tinyint(1) default NULL, `instructor_legacy` text, `time_legacy` time default NULL, `ssn_legacy` text, `position_grade_title` text, `office_phone_legacy` text, `contractor_legacy` tinyint(1) default NULL, `no_show` tinyint(1) default NULL, `funding_id` bigint(20) unsigned NOT NULL default '0', `incomplete` tinyint(1) default NULL, `prerequisites_completed` tinyint(1) default NULL, `score` smallint(5) unsigned default NULL, `per_diem_cost` decimal(10,0) default NULL, `travel_cost` decimal(10,0) default NULL, `first_migration` tinyint(1) unsigned default NULL, PRIMARY KEY (`firstname`,`middlename`,`lastname`,`suffix`,`class_id`,`start_date`,`end_date`), UNIQUE KEY `id` (`id`), KEY `fk_registration_class_id_must_always_match_a_classes_id` (`class_id`), KEY `fk_registration_title_id_must_always_match_title_salutations_id` (`title_salutation_id`), KEY `fk_registration_bureau_id_must_always_match_a_bureaus_id` (`bureau_id`), KEY `fk_funding_id_check_for_registration_and_attendance` (`funding_id`), KEY `fk_employment_status_id_check_for_registration_and_attendance` (`employment_status_id`), CONSTRAINT `fk_employment_status_id_check_for_registration_and_attendance` FOREIGN KEY (`employment_status_id`) REFERENCES `employment_statuses` (`id`), CONSTRAINT `fk_funding_id_check_for_registration_and_attendance` FOREIGN KEY (`funding_id`) REFERENCES `funding_types` (`id`), CONSTRAINT `fk_registration_bureau_id_must_always_match_a_bureaus_id` FOREIGN KEY (`bureau_id`) REFERENCES `bureaus` (`id`), CONSTRAINT `fk_registration_class_id_must_always_match_a_classes_id` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`), CONSTRAINT `fk_registration_title_id_must_always_match_title_salutations_id` FOREIGN KEY (`title_salutation_id`) REFERENCES `title_salutations` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Do you know why this this one data record won't load?... why MySQL says: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed Ferindo On 6/23/06, Gerald L. Clark [EMAIL PROTECTED] wrote: Ferindo Middleton wrote: I'm trying to load data into a table from a file but I get an error message: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed This error message isn't very specific as to what is going wrong and I have no idea what it is about the data file that is wrong. Of course, I know that there is in fact data in the file and that it is proper data types matching the table structure so I don't know why this error message is occuring or what it is
Re: error loading data from file ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
Ferindo - I believe there is a 64K limit on the length of a record in a MyISAM table (text and binary columns excluded), but I haven't ever seen a number of columns limit. Based on your table description below I don't think you're close to such a problem. Based on the error you receive (no data), I think it's much more likely the command you're using isn't quite right for the data source you've got. See http://dev.mysql.com/doc/refman/5.0/en/load-data.html for all the ins and outs of LOAD DATA INFILE. Off the top of my head, two things: 1 - you are using tab delimited data. By default, I believe LOAD DATA INFILE expects a comma delimited file. You should therefore specify FIELDS TERMINATED BY \t 2 - you are using Windows, which uses CRLF line endings. I believe LOAD DATA INFILE by default expects UNIX standard line endings. You should therefore specify LINES TERMINATED BY \r\n Something like this then: LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Tomcat 5.5/ webapps/utrad/docs/rebuild_scratch_area/test.tab' INTO TABLE reggie FIELDS TERMINATED BY \t LINES TERMINATED BY \r\n; Hope this helps Dan Ferindo Middleton wrote: Does MySQL have any constraints when it comes to the number columns that it can accurately support/import in any one table/file. When I see this file that is failing to get properly read into the database, that's what comes to mind... It's failing because of some kind of constraint or threshold of the db so instead of giving a meaningful messgae, it just says: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed Ferindo On 6/23/06, Ferindo Middleton [EMAIL PROTECTED] wrote: I guess my general reason for posting this was to ask: Are there any known issues with the LOAD DATA INFILE comand in MySQL? However, I stripped all the data in the file (test.tab) down to one record which still wouldn't load. Here is the command: mysql LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Tomcat 5.5/ webapps/utrad/docs/rebuild_scratch_area/test.tab' INTO TABLE reggie; The fields inside the file are tab-delimited and look like this: 16411 5 Rupert Settles Settles Settles 1 esunindyo \N 207 \N 12 \N 2005-01-03 2005-01-07 1 1 1 0 \N Deobligation 1 2 \N fmiddleton 0 0 \N \N 11:00:00 \N \N \N 0 0 4 0 0 \N \N \N 1 The structure of the TABLE reggie is this: CREATE TABLE `reggie ` ( `id` bigint(20) unsigned NOT NULL auto_increment, `title_salutation_id` bigint(20) unsigned NOT NULL, `firstname` varchar(128) NOT NULL, `middlename` varchar(128) NOT NULL default '', `lastname` varchar(128) NOT NULL default '', `suffix` varchar(128) NOT NULL default '', `paper_received` tinyint(1) NOT NULL default '1', `addr` text, `cc_email_list` text, `fortran_id` bigint(20) default NULL, `office` text, `class_id` bigint(20) unsigned NOT NULL, `schedule_id` bigint(20) default NULL, `start_date` date NOT NULL default '-00-00', `end_date` date NOT NULL default '-00-00', `enrolled` tinyint(1) default NULL, `attended` tinyint(1) default NULL, `completed` tinyint(1) default NULL, `cancelled` tinyint(1) default '0', `cancelled_comments` text, `comments` text, `email_confirmation_sent` tinyint(1) NOT NULL, `employment_status_id` bigint(20) unsigned NOT NULL default '0', `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `last_user_updated` text, `waitlisted` tinyint(1) default '0', `overflow_registrant` tinyint(1) default '0', `attach_hotel_listing_directions` tinyint(1) default NULL, `instructor_legacy` text, `time_legacy` time default NULL, `ssn_legacy` text, `position_grade_title` text, `office_phone_legacy` text, `contractor_legacy` tinyint(1) default NULL, `no_show` tinyint(1) default NULL, `funding_id` bigint(20) unsigned NOT NULL default '0', `incomplete` tinyint(1) default NULL, `prerequisites_completed` tinyint(1) default NULL, `score` smallint(5) unsigned default NULL, `per_diem_cost` decimal(10,0) default NULL, `travel_cost` decimal(10,0) default NULL, `first_migration` tinyint(1) unsigned default NULL, PRIMARY KEY (`firstname`,`middlename`,`lastname`,`suffix`,`class_id`,`start_date`,`end_date`), UNIQUE KEY `id` (`id`), KEY `fk_registration_class_id_must_always_match_a_classes_id` (`class_id`), KEY `fk_registration_title_id_must_always_match_title_salutations_id` (`title_salutation_id`), KEY `fk_registration_bureau_id_must_always_match_a_bureaus_id` (`bureau_id`), KEY `fk_funding_id_check_for_registration_and_attendance` (`funding_id`), KEY `fk_employment_status_id_check_for_registration_and_attendance` (`employment_status_id`), CONSTRAINT `fk_employment_status_id_check_for_registration_and_attendance` FOREIGN KEY (`employment_status_id`) REFERENCES `employment_statuses` (`id`), CONSTRAINT `fk_funding_id_check_for_registration_and_attendance` FOREIGN KEY (`funding_id`) REFERENCES
Query Speed
I have a query which I can execute in Microsoft SQL, and it's instantaneous. However, In MySQL, I've only been able to get it down to 48 seconds: SELECT S.State, ST.StateName, S.Sub, C.ChapterType, (SELECT Count(*) FROM (Members M JOIN Chapters C1 ON C1.ID=M.ChapterID) JOIN Schools S1 on S1.ID=C1.SchoolID WHERE S1.State=S.State AND S1.Sub=S.Sub AND C1.ChapterType=C.ChapterType) AS TotMem, (SELECT Count(*) FROM ((AdvChapterLink ACL JOIN LocalAdvisors LA ON LA.ID=ACL.AdvisorID) JOIN Chapters C2 ON C2.ID=ACL.ChapterID) JOIN Schools S2 ON S2.ID=C2.SchoolID WHERE S2.State=S.State AND S2.Sub=S.Sub AND C2.ChapterType=C.ChapterType AND LA.InvoiceNo IS NOT NULL) AS TotAdv FROM (Chapters C JOIN Schools S ON S.ID=C.SchoolID) JOIN State ST ON S.State=ST.State GROUP BY S.State, ST.StateName, S.Sub, C.ChapterType ORDER BY S.State, S.Sub, C.ChapterType I have added indexes to make sure that all of the JOINs and WHEREs can operate efficiently. This helped tremendously, as my first attempt at this query timed out, so I have no idea how long it would have actually taken. I'm doing this query using ASP on a Windows XP Pro machine, however, doing it in the MySQL Query Browser takes just as long (as one would expect). The tables are all InnoDB. Is there anything else I can do to help speed this query up? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Speed
Hi Jesse, I am not 100% sure cause I have only been using MySQL for ~6 months but I do read this mailing list everyday and have learned a lot. I believe that InnoDB tables to not maintain a count(*) for the tables so it has to physically count the rows. I believe MyISAM tables do maintain that count(*) so the tables were MyISAM they count(*) would be faster. That may be where the slowness is coming from. Again, as I am new to MySQL, this may be totally off the wall. Maybe someone else more experienced with MySQL could verify this. Thanks, --Randall Price -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Monday, June 26, 2006 4:28 PM To: MySQL List Subject: Query Speed I have a query which I can execute in Microsoft SQL, and it's instantaneous. However, In MySQL, I've only been able to get it down to 48 seconds: SELECT S.State, ST.StateName, S.Sub, C.ChapterType, (SELECT Count(*) FROM (Members M JOIN Chapters C1 ON C1.ID=M.ChapterID) JOIN Schools S1 on S1.ID=C1.SchoolID WHERE S1.State=S.State AND S1.Sub=S.Sub AND C1.ChapterType=C.ChapterType) AS TotMem, (SELECT Count(*) FROM ((AdvChapterLink ACL JOIN LocalAdvisors LA ON LA.ID=ACL.AdvisorID) JOIN Chapters C2 ON C2.ID=ACL.ChapterID) JOIN Schools S2 ON S2.ID=C2.SchoolID WHERE S2.State=S.State AND S2.Sub=S.Sub AND C2.ChapterType=C.ChapterType AND LA.InvoiceNo IS NOT NULL) AS TotAdv FROM (Chapters C JOIN Schools S ON S.ID=C.SchoolID) JOIN State ST ON S.State=ST.State GROUP BY S.State, ST.StateName, S.Sub, C.ChapterType ORDER BY S.State, S.Sub, C.ChapterType I have added indexes to make sure that all of the JOINs and WHEREs can operate efficiently. This helped tremendously, as my first attempt at this query timed out, so I have no idea how long it would have actually taken. I'm doing this query using ASP on a Windows XP Pro machine, however, doing it in the MySQL Query Browser takes just as long (as one would expect). The tables are all InnoDB. Is there anything else I can do to help speed this query up? Thanks, Jesse -- 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]
ASP Reporting EOF?
When I run the following query in my ASP Application: SELECT S.State, S.Sub, S.Region, S.District, Sum(Males) AS TotMales, Sum(Females) AS TotFemales, Sum(AfricanAmerican) AS TotAfricanAmericans, Sum(Asian) AS TotAsians, Sum(Caucasian) AS TotCaucasians, Sum(Hispanic) AS TotHispanics, Sum(NativeAmerican) AS TotNativeAmericans, Sum(Other) AS TotOthers, Sum(Grade6) AS TotGrade6s, Sum(Grade7) AS TotGrade7s, Sum(Grade8) AS TotGrade8s, Sum(Grade9) AS TotGrade9s, Sum(Grade10) AS TotGrade10s, Sum(Grade11) AS TotGrade11s, Sum(Grade12) AS TotGrade12s, Sum(AgeBelow22) AS TotAgeBelow22s, Sum(Age22_25) AS TotAge22_25s, Sum(Age26_30) AS TotAge26_30s, Sum(Age31_40) AS TotAge31_40, Sum(AgeOver40) AS TotAgeOver40s, Sum(Disabilities) AS TotDisabilitiess, Sum(EducationallyDisabled) AS TotEducationallyDisableds, Sum(EconomicallyDisadvantaged) AS TotEconomicallyDisadvantageds, Sum(LimitedEnglishProficiency) AS TotLimitedEnglishProficiencys, Sum(NonTraditional) AS TotNonTraditionals FROM Chapters C, Schools S WHERE C.SchoolID=S.ID GROUP BY S.State, S.Sub, S.Region, S.District ORDER BY S.State, S.Sub, S.Region, S.District The dataset returns EOF, however, it's NOT EOF. I can execute this query in MySQL Query Browser, and it returns 215 rows. I've recently converted this table from InnoDB to MyISAM, which seems to be faster, but I can't figure out why it would say it was at EOF when it's not! I've got other queries that don't return EOF, why would this one? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Speed
On the chance that such a thing were actually happening, I converted all of my tables over to MyISAM, because it says that it is very fast, and I'm not using Transactions anyway. If that's the only feature that InnoDB gives me, I'd much rather have the speed. Anyway, it actually increased the time of this query from about 48 seconds to about 1:40 or so. More than twice as much. However, I've noticed that other things do seem to be running faster since converting to MyISAM, but this query is not. Thanks, Jesse - Original Message - From: Price, Randall [EMAIL PROTECTED] To: Jesse [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com Sent: Monday, June 26, 2006 4:47 PM Subject: RE: Query Speed Hi Jesse, I am not 100% sure cause I have only been using MySQL for ~6 months but I do read this mailing list everyday and have learned a lot. I believe that InnoDB tables to not maintain a count(*) for the tables so it has to physically count the rows. I believe MyISAM tables do maintain that count(*) so the tables were MyISAM they count(*) would be faster. That may be where the slowness is coming from. Again, as I am new to MySQL, this may be totally off the wall. Maybe someone else more experienced with MySQL could verify this. Thanks, --Randall Price -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Monday, June 26, 2006 4:28 PM To: MySQL List Subject: Query Speed I have a query which I can execute in Microsoft SQL, and it's instantaneous. However, In MySQL, I've only been able to get it down to 48 seconds: SELECT S.State, ST.StateName, S.Sub, C.ChapterType, (SELECT Count(*) FROM (Members M JOIN Chapters C1 ON C1.ID=M.ChapterID) JOIN Schools S1 on S1.ID=C1.SchoolID WHERE S1.State=S.State AND S1.Sub=S.Sub AND C1.ChapterType=C.ChapterType) AS TotMem, (SELECT Count(*) FROM ((AdvChapterLink ACL JOIN LocalAdvisors LA ON LA.ID=ACL.AdvisorID) JOIN Chapters C2 ON C2.ID=ACL.ChapterID) JOIN Schools S2 ON S2.ID=C2.SchoolID WHERE S2.State=S.State AND S2.Sub=S.Sub AND C2.ChapterType=C.ChapterType AND LA.InvoiceNo IS NOT NULL) AS TotAdv FROM (Chapters C JOIN Schools S ON S.ID=C.SchoolID) JOIN State ST ON S.State=ST.State GROUP BY S.State, ST.StateName, S.Sub, C.ChapterType ORDER BY S.State, S.Sub, C.ChapterType I have added indexes to make sure that all of the JOINs and WHEREs can operate efficiently. This helped tremendously, as my first attempt at this query timed out, so I have no idea how long it would have actually taken. I'm doing this query using ASP on a Windows XP Pro machine, however, doing it in the MySQL Query Browser takes just as long (as one would expect). The tables are all InnoDB. Is there anything else I can do to help speed this query up? Thanks, Jesse -- 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: Query Speed
Jesse, can you post table structures ( SHOW CREATE TABLE tablename ) and the output you get from EXPLAIN followed by the query below? Also what version of MySQL you're on, and high level details of the hardware (RAM, disks, processors, OS). That will all be helpful in trying to help you out here. Dan Jesse wrote: I have a query which I can execute in Microsoft SQL, and it's instantaneous. However, In MySQL, I've only been able to get it down to 48 seconds: SELECT S.State, ST.StateName, S.Sub, C.ChapterType, (SELECT Count(*) FROM (Members M JOIN Chapters C1 ON C1.ID=M.ChapterID) JOIN Schools S1 on S1.ID=C1.SchoolID WHERE S1.State=S.State AND S1.Sub=S.Sub AND C1.ChapterType=C.ChapterType) AS TotMem, (SELECT Count(*) FROM ((AdvChapterLink ACL JOIN LocalAdvisors LA ON LA.ID=ACL.AdvisorID) JOIN Chapters C2 ON C2.ID=ACL.ChapterID) JOIN Schools S2 ON S2.ID=C2.SchoolID WHERE S2.State=S.State AND S2.Sub=S.Sub AND C2.ChapterType=C.ChapterType AND LA.InvoiceNo IS NOT NULL) AS TotAdv FROM (Chapters C JOIN Schools S ON S.ID=C.SchoolID) JOIN State ST ON S.State=ST.State GROUP BY S.State, ST.StateName, S.Sub, C.ChapterType ORDER BY S.State, S.Sub, C.ChapterType I have added indexes to make sure that all of the JOINs and WHEREs can operate efficiently. This helped tremendously, as my first attempt at this query timed out, so I have no idea how long it would have actually taken. I'm doing this query using ASP on a Windows XP Pro machine, however, doing it in the MySQL Query Browser takes just as long (as one would expect). The tables are all InnoDB. Is there anything else I can do to help speed this query up? Thanks, Jesse -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 3.23.58 5.0.22?
Dan, I haven't seen any other responses, so I'll chime in with my $.02. I think you should have very few problems upgrading from 3.23.58 to 5.0.22. I think you will in fact be able to do pretty much what you describe. I've upgraded in both fashions in the past (re-importing mysqldump output and re-using existing MyISAM data dirs files), and both are pretty straightforward. I've gone 3.23 to 4.0, 4.0 to 4.1, and 4.1 to 5.0, over the course of a few years, all smoothly. I wouldn't hesitate to try a 3.23 direct to 5.0 upgrade either way. If InnoDB were involved I'd be hesitant to upgrade without using mysqldump, due to my own lack of experience with InnoDB. Potential gotchas: 1 - re-importing a mysqldump can take a while. 2 - you'll need a fair bit of disk space available to hold the mysqldump output and the resulting new database files 3 - If you are using any ISAM tables (MyISAM is OK) in 3.23 those could be problematic, as the table type / storage engine will be specified in the mysqldump output, and ISAM is no longer supported. 4 - passwords changed significantly with 4.1, but you can use the old password scheme with 4.1 and 5.0. You just have to know that you need to specify to allow old passwords. http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html HTH, Dan Dan Trainor wrote: Good morning, all - I've read for quite a while tonight, but still haven't been able to figure out - can I upgrade directly from 3.23.58 to 5.0.22? I've read that I'd have to do something like 3.23.58 4.0 4.1 5.0.22, but then also the 'mysql_upgrade' application which, as documented, sounds like it can do magic things. To be quite honest I'd like nothing more than to take a 'mysqldump' of the database, and just re-import that, and run an app such as 'mysql_upgrade' against it and call it good. This will all be done on a pretty recent Linux distribution, CentOS 4.3. Nothing out of the ordinary, but an upgrade such as this one is definitely a bit out of my realm. I was able to do such an upgrade a few weeks ago, but added stuff like permissions by hand - there were only a few. However, for this particular instance, there's 250+ MySQL users. Anyone have any suggestions? Thanks! -dant -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ASP Reporting EOF?
When you run the query in the Query Browser, do any of the records return a 'NULL' value? If so, then MS's ASP engine would return an EOF because MySQL's NULL is NOT the same as objRS(fld.name) = . J.R. -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Monday, June 26, 2006 4:58 PM To: MySQL List Subject: ASP Reporting EOF? When I run the following query in my ASP Application: SELECT S.State, S.Sub, S.Region, S.District, Sum(Males) AS TotMales, Sum(Females) AS TotFemales, Sum(AfricanAmerican) AS TotAfricanAmericans, Sum(Asian) AS TotAsians, Sum(Caucasian) AS TotCaucasians, Sum(Hispanic) AS TotHispanics, Sum(NativeAmerican) AS TotNativeAmericans, Sum(Other) AS TotOthers, Sum(Grade6) AS TotGrade6s, Sum(Grade7) AS TotGrade7s, Sum(Grade8) AS TotGrade8s, Sum(Grade9) AS TotGrade9s, Sum(Grade10) AS TotGrade10s, Sum(Grade11) AS TotGrade11s, Sum(Grade12) AS TotGrade12s, Sum(AgeBelow22) AS TotAgeBelow22s, Sum(Age22_25) AS TotAge22_25s, Sum(Age26_30) AS TotAge26_30s, Sum(Age31_40) AS TotAge31_40, Sum(AgeOver40) AS TotAgeOver40s, Sum(Disabilities) AS TotDisabilitiess, Sum(EducationallyDisabled) AS TotEducationallyDisableds, Sum(EconomicallyDisadvantaged) AS TotEconomicallyDisadvantageds, Sum(LimitedEnglishProficiency) AS TotLimitedEnglishProficiencys, Sum(NonTraditional) AS TotNonTraditionals FROM Chapters C, Schools S WHERE C.SchoolID=S.ID GROUP BY S.State, S.Sub, S.Region, S.District ORDER BY S.State, S.Sub, S.Region, S.District The dataset returns EOF, however, it's NOT EOF. I can execute this query in MySQL Query Browser, and it returns 215 rows. I've recently converted this table from InnoDB to MyISAM, which seems to be faster, but I can't figure out why it would say it was at EOF when it's not! I've got other queries that don't return EOF, why would this one? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: Upgrading from 3.23.58 5.0.22?
On 6/25/06, Dan Trainor [EMAIL PROTECTED] wrote: Good morning, all - I've read for quite a while tonight, but still haven't been able to figure out - can I upgrade directly from 3.23.58 to 5.0.22? I've read that I'd have to do something like 3.23.58 4.0 4.1 5.0.22, but then also the 'mysql_upgrade' application which, as documented, sounds like it can do magic things. I strongly recommend you following this line. There were significant changes between 3, 4, 4.1 and 5. I say that because I found many problems, and 4 to 4.1 were a kinda messy upgrade. Folow: http://dev.mysql.com/doc/refman/5.0/en/upgrade.html http://dev.mysql.com/doc/refman/4.1/en/upgrade.html And you should be fine (I didn't, hehe). To be quite honest I'd like nothing more than to take a 'mysqldump' of the database, and just re-import that, and run an app such as 'mysql_upgrade' against it and call it good. Sorry, I don't think you can do that, the privileges table, for instance, have changed a LOT, so, there's this fix_privileges script on 4.1. This will all be done on a pretty recent Linux distribution, CentOS 4.3. Nothing out of the ordinary, but an upgrade such as this one is definitely a bit out of my realm. I was able to do such an upgrade a few weeks ago, but added stuff like permissions by hand - there were only a few. However, for this particular instance, there's 250+ MySQL users. Anyone have any suggestions? Upgrade to 4, then 4.1, then 5. Follow the above links, its quite fast as you'll only be careful with things your particular system is affected. PS: BACKUP EVERYTHING!!! 2 copies are never too good -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Speed
EXPLAIN output is a good way to see how MySQL is planning to execute your query - which indexes it chooses to use, how much work it thinks it needs to do for each table reference. My understanding is that you can get an approximate / rough idea of operations needed by multiplying all the 'rows' columns in the EXPLAIN output. For you, that's 54 * 9 * 69 * 1 * 65 * 1 * 2 * 1 * 65 * 1 * 24 = 6800695200 which is obviously a lot of operations. Your state table may not have an index on the column you are joining on. Try adding one there. (Don't see your 'state' table def here to check). What does your CPU usage look like while this query is running? And what does 'SHOW PROCESSLIST' tell you while this query is running? Dan Jesse wrote: Thanks for the help. Just so you know, I stated in the original message that the tables are InnoDB, but I've since converted them to MyISAM to see if that helped. It didn't. Here's the information you wanted: Here are the table structures: CREATE TABLE `members` ( `ID` int(10) NOT NULL, `ChapterID` int(10) default NULL, `FirstName` varchar(25) character set utf8 default NULL, `MI` varchar(1) character set utf8 default NULL, `LastName` varchar(25) character set utf8 default NULL, `UID` varchar(15) character set utf8 default NULL, `MemberType` varchar(20) character set utf8 default NULL, `InvoiceNo` varchar(7) character set utf8 default NULL, `PayDate` datetime default NULL, `MembershipExpires` datetime default NULL, `NLCEligible` tinyint(1) NOT NULL default '1', `PayNatDues` tinyint(1) NOT NULL default '1', `GPA` decimal(18,2) default NULL, `GradYear` int(10) default NULL, `Gender` varchar(1) character set utf8 default NULL, `BusEdCourse` varchar(40) character set utf8 default NULL, `AddDate` datetime default NULL, `PhotoID` smallint(5) default NULL, PRIMARY KEY (`ID`), KEY `IX_Members` (`LastName`,`FirstName`,`MI`), KEY `IX_Members_1` (`UID`), KEY `IX_Members_2` (`InvoiceNo`), KEY `IX_Members_3` (`ChapterID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `chapters` ( `ID` int(10) NOT NULL, `SchoolID` int(10) default NULL, `Name` varchar(50) character set utf8 default NULL, `ChapterType` varchar(25) character set utf8 default NULL, `UID` varchar(8) character set utf8 default NULL, `NextMemNo` int(10) default NULL, `Males` int(10) default NULL, `Females` int(10) default NULL, `AfricanAmerican` int(10) default NULL, `Asian` int(10) default NULL, `Caucasian` int(10) default NULL, `Hispanic` int(10) default NULL, `NativeAmerican` int(10) default NULL, `Other` int(10) default NULL, `Grade6` int(10) default NULL, `Grade7` int(10) default NULL, `Grade8` int(10) default NULL, `Grade9` int(10) default NULL, `Grade10` int(10) default NULL, `Grade11` int(10) default NULL, `Grade12` int(10) default NULL, `Freshmen` int(10) default NULL, `Sophomore` int(10) default NULL, `Junior` int(10) default NULL, `Senior` int(10) default NULL, `PostGraduate` int(10) default NULL, `AgeBelow22` int(10) default NULL, `Age22_25` int(10) default NULL, `Age26_30` int(10) default NULL, `Age31_40` int(10) default NULL, `AgeOver40` int(10) default NULL, `Disabilities` int(10) default NULL, `EducationallyDisabled` int(10) default NULL, `EconomicallyDisadvantaged` int(10) default NULL, `LimitedEnglishProficiency` int(10) default NULL, `NonTraditional` int(10) default NULL, `TempInvNo` varchar(7) character set utf8 default NULL, `MatSentDate` datetime default NULL, `TransferDate` datetime default NULL, `AddDate` datetime default NULL, `Reactivated` tinyint(1) default NULL, `OverrideNLCHotel` tinyint(1) NOT NULL default '0', PRIMARY KEY (`ID`), KEY `IX_Chapters` (`UID`), KEY `IX_Chapters_1` (`SchoolID`), KEY `IX_Chapters_2` (`ChapterType`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `schools` ( `ID` int(10) NOT NULL, `Name` varchar(50) character set utf8 default NULL, `Address` varchar(50) character set utf8 default NULL, `City` varchar(30) character set utf8 default NULL, `State` varchar(2) character set utf8 default NULL, `Sub` varchar(2) character set utf8 default NULL, `Zip` varchar(10) character set utf8 default NULL, `BOContact` varchar(35) character set utf8 default NULL, `BOAddress` varchar(40) character set utf8 default NULL, `BOCity` varchar(30) character set utf8 default NULL, `BOState` varchar(2) character set utf8 default NULL, `BOZip` varchar(10) character set utf8 default NULL, `BOPhone` varchar(13) character set utf8 default NULL, `Phone` varchar(15) character set utf8 default NULL, `Ext` varchar(10) character set utf8 default NULL, `Fax` varchar(15) character set utf8 default NULL, `Region` varchar(10) character set utf8 default NULL, `District` varchar(10) character set utf8 default NULL, `InvoiceRequired` tinyint(1) default '0', `PrincipalsName` varchar(50) default NULL, `PrincipalsEMail` varchar(65) default NULL, PRIMARY KEY (`ID`), KEY `IX_Schools` (`Name`),
Leading zero where strlen 5
I need to update a column, if the string length is less than 5, I want to add leading zeros to it until it has 5. These are zip codes, I think there are no 00 leading zips, so most should all be four chars long. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Leading zero where strlen 5
The best way to do this is with code, however, here is A way to do it (I am
sure that there are more than one...)
UPDATE tbl_Name SET ZipCodes = concat('0',ZipCodes) WHERE length(ZipCodes) =
4
Of course, this will involve you changing the length() if the ZipCode has
only 3 digits.
Also, of course, try this with a LIMIT to make sure that this updates your
fields correctly. I know it works on mine...
J.R.'s $0.02
-Original Message-
From: Scott Haneda [mailto:[EMAIL PROTECTED]
Sent: Monday, June 26, 2006 7:17 PM
To: mysql@lists.mysql.com
Subject: Leading zero where strlen 5
I need to update a column, if the string length is less than 5, I want to
add leading zeros to it until it has 5. These are zip codes, I think there
are no 00 leading zips, so most should all be four chars long.
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
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]
Merging two fields; references to fields
Hello NG,
I have two tables, three and four fields inside, in my mysql database,
created with phpmyadmin:
t_authors
1 authorid (primary key, auto_increment)
2 lastname
3 firstname
t_books
1 bookid (primary key, auto_increment)
2 authorid (Typ:index, reference to t_authors.authorid, done
with phpmyadmin)
3 title
4 subtitle
Now I want to create a view from t_authors, so that the fields
of lastname and firstname from the t_authors tabel are one field with
the value inside lastname, firstname:
create view v_authornames as
select authorid, lastname || ', ' || firstname from t_authors
The purpose of lastname || ', ' || firstname is to get one field with
comma separated values lastname and firstname form t_authors.
How can I merge this two fields, lastname and firstname, that I will get
one field and inside this field will be the values lastname, firstname from
the
t_authors table? I don't know the right syntax for
merging the fields? Syntax: lastname || ', ' || firstname is false.
There is another problem inserting sequently values in
the two tables:
insert into t_authors (lastname, firstname)
values ('Meyers', 'Scott');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Effektiv C++ Programmieren',
'50 Wege zur Verbesserung Ihrer Programme und Entwuerfe');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Mehr Effektiv C++ Programmieren',
'35 neue Wege zur Verbesserung Ihrer Entwuerfe und Programme');
insert into t_authors (lastname, firstname)
values ('Schlossnagle', 'George');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Advanced PHP Programming', 'A practical guide');
The problem is the authorid of t_books:
which value should I take for authorid of t_books?
And how to phpmyadmin? The field authorid.t_books should be
referenced to the field authorid.t_authors.
Auto_increment of authorid.t_authors is working fine for me.
Auto_increment of bookid.t_books is also working fine for me.
But how can I implement the reference between authorid.t_authors
and authorid.t_books in phpmyadmin and which value for
authorid.t_books.should I set in the command:
insert into t_books (authorid, title, subtitle)
values ('1'), 'Effektiv C++ Programmieren',
'50 Wege zur Verbesserung Ihrer Programme und Entwuerfe');
?
Field authorid.t_books should have the same
auto_incremented values as the field authorid.t_authors.
Best regards
Andreas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Leading zero where strlen 5
J.R. Bullington wrote:
The best way to do this is with code, however, here is A way to do it (I am
sure that there are more than one...)
UPDATE tbl_Name SET ZipCodes = concat('0',ZipCodes) WHERE length(ZipCodes) =
4
How about
UPDATE tbl_Name SET ZipCodes = right(concat('0',ZipCodes), 5) WHERE
length(ZipCodes) 5
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Leading zero where strlen 5
J.R. Bullington wrote:
The best way to do this is with code, however, here is A way to do it (I am
sure that there are more than one...)
UPDATE tbl_Name SET ZipCodes = concat('0',ZipCodes) WHERE length(ZipCodes) =
4
How about
UPDATE tbl_Name SET ZipCodes = right(concat('0',ZipCodes), 5) WHERE
length(ZipCodes) 5
Works, perfect, thanks to both of you.
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Speed
Sorry, I had an extra '9' in there. Math is actually: 54 * 69 * 1 * 65 * 1 * 2 * 1 * 65 * 1 * 24 = 755632800 Still a lot of operations, and if you index the state field, you can potentially reduce it by a factor of 54. Dan Dan Buettner wrote: EXPLAIN output is a good way to see how MySQL is planning to execute your query - which indexes it chooses to use, how much work it thinks it needs to do for each table reference. My understanding is that you can get an approximate / rough idea of operations needed by multiplying all the 'rows' columns in the EXPLAIN output. For you, that's 54 * 9 * 69 * 1 * 65 * 1 * 2 * 1 * 65 * 1 * 24 = 6800695200 which is obviously a lot of operations. Your state table may not have an index on the column you are joining on. Try adding one there. (Don't see your 'state' table def here to check). What does your CPU usage look like while this query is running? And what does 'SHOW PROCESSLIST' tell you while this query is running? Dan Jesse wrote: Thanks for the help. Just so you know, I stated in the original message that the tables are InnoDB, but I've since converted them to MyISAM to see if that helped. It didn't. Here's the information you wanted: Here are the table structures: CREATE TABLE `members` ( `ID` int(10) NOT NULL, `ChapterID` int(10) default NULL, `FirstName` varchar(25) character set utf8 default NULL, `MI` varchar(1) character set utf8 default NULL, `LastName` varchar(25) character set utf8 default NULL, `UID` varchar(15) character set utf8 default NULL, `MemberType` varchar(20) character set utf8 default NULL, `InvoiceNo` varchar(7) character set utf8 default NULL, `PayDate` datetime default NULL, `MembershipExpires` datetime default NULL, `NLCEligible` tinyint(1) NOT NULL default '1', `PayNatDues` tinyint(1) NOT NULL default '1', `GPA` decimal(18,2) default NULL, `GradYear` int(10) default NULL, `Gender` varchar(1) character set utf8 default NULL, `BusEdCourse` varchar(40) character set utf8 default NULL, `AddDate` datetime default NULL, `PhotoID` smallint(5) default NULL, PRIMARY KEY (`ID`), KEY `IX_Members` (`LastName`,`FirstName`,`MI`), KEY `IX_Members_1` (`UID`), KEY `IX_Members_2` (`InvoiceNo`), KEY `IX_Members_3` (`ChapterID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `chapters` ( `ID` int(10) NOT NULL, `SchoolID` int(10) default NULL, `Name` varchar(50) character set utf8 default NULL, `ChapterType` varchar(25) character set utf8 default NULL, `UID` varchar(8) character set utf8 default NULL, `NextMemNo` int(10) default NULL, `Males` int(10) default NULL, `Females` int(10) default NULL, `AfricanAmerican` int(10) default NULL, `Asian` int(10) default NULL, `Caucasian` int(10) default NULL, `Hispanic` int(10) default NULL, `NativeAmerican` int(10) default NULL, `Other` int(10) default NULL, `Grade6` int(10) default NULL, `Grade7` int(10) default NULL, `Grade8` int(10) default NULL, `Grade9` int(10) default NULL, `Grade10` int(10) default NULL, `Grade11` int(10) default NULL, `Grade12` int(10) default NULL, `Freshmen` int(10) default NULL, `Sophomore` int(10) default NULL, `Junior` int(10) default NULL, `Senior` int(10) default NULL, `PostGraduate` int(10) default NULL, `AgeBelow22` int(10) default NULL, `Age22_25` int(10) default NULL, `Age26_30` int(10) default NULL, `Age31_40` int(10) default NULL, `AgeOver40` int(10) default NULL, `Disabilities` int(10) default NULL, `EducationallyDisabled` int(10) default NULL, `EconomicallyDisadvantaged` int(10) default NULL, `LimitedEnglishProficiency` int(10) default NULL, `NonTraditional` int(10) default NULL, `TempInvNo` varchar(7) character set utf8 default NULL, `MatSentDate` datetime default NULL, `TransferDate` datetime default NULL, `AddDate` datetime default NULL, `Reactivated` tinyint(1) default NULL, `OverrideNLCHotel` tinyint(1) NOT NULL default '0', PRIMARY KEY (`ID`), KEY `IX_Chapters` (`UID`), KEY `IX_Chapters_1` (`SchoolID`), KEY `IX_Chapters_2` (`ChapterType`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `schools` ( `ID` int(10) NOT NULL, `Name` varchar(50) character set utf8 default NULL, `Address` varchar(50) character set utf8 default NULL, `City` varchar(30) character set utf8 default NULL, `State` varchar(2) character set utf8 default NULL, `Sub` varchar(2) character set utf8 default NULL, `Zip` varchar(10) character set utf8 default NULL, `BOContact` varchar(35) character set utf8 default NULL, `BOAddress` varchar(40) character set utf8 default NULL, `BOCity` varchar(30) character set utf8 default NULL, `BOState` varchar(2) character set utf8 default NULL, `BOZip` varchar(10) character set utf8 default NULL, `BOPhone` varchar(13) character set utf8 default NULL, `Phone` varchar(15) character set utf8 default NULL, `Ext` varchar(10) character set utf8 default NULL, `Fax` varchar(15) character set utf8 default NULL, `Region` varchar(10) character set utf8
Records in front of and behind another record
Mysql 4 I have a frustrating case here, a list of numbers, say, 0 through 30,000 or so, however, there are gaps in the numbers, so not purely sequential. I am faced with selecting one record from the dataset, that's simple, however, before that select, I need to make sure the record is there, and if it is not, find the one either just before it, or just after it. Whichever is closest. If they are the same, lean on either one, I don't really care. Any suggestion on this one would be appreciated, I can do this with 3 separate queries, but that is taking too long, since I have to do this with two separate datasets and shove this all back out a browser to the user. thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FreeBSD 6 and MySQL with DBs on a NAS
On Monday, 26 June 2006 at 10:41:16 -0400, [EMAIL PROTECTED] wrote: *** This happens for me using FreeBSD 6.0 or FreeBSD 6.1 with the most recent MySQL 4.1 or 5.0 built from ports and when the DBMS data files reside on a NetApp NAS share shared over NFS. It only seems to happen with very frequently written-to tables. I sent this to the list last week and no one responded. *** Hi, I was wondering if anyone else had encountered this issue and/or come up with what needs to be done to resolve it: I currently have MySQL 5.0.22 built from ports on a FreeBSD 6.1 machine with the DB data residing on a NetApp share connected via NFS. A strange thing happens often after a few hours or a couple of days, some tables that are very active start to crash for no apparent reason as far as I can tell. Example output from check table tablename: ++---+--+---+ Table | Op| Msg_type | Msg_text ++---+--+---+ dbname.tablename | check | warning | Table is marked as crashed dbname.tablename | check | error| Found key at page 18259968 that points to record outside datafile | dbname.tablename | check | error| Corrupt ++---+--+---+ I've seen this happen on FreeBSD 6.0 and 6.1 with MySQL 4.1.x and MySQL 5.0.x built from ports. Has anyone else seen this and if so has a resolution been found? This is a complicated one. There are at least three variables: 1. MySQL is typically most heavily tested on Linux; you're running FreeBSD. 2. You're using the version of FreeBSD from the Ports Collection, not our own build. 3. You're running over NFS, to a different implementation. Of these variables, I'd say that (2) is probably completely irrelevant. Of the other two, I'd put my money on (3). You can test this if you can move the database to local disk, at least fora while. If the problem no longer occurs, there's a good reason to believe that my guess is right. In this case, it's not a MySQL problem. The best thing to do then would be to report it via the FreeBSD bug reporting system (http://bugs.FreeBSD.org/). If the problem still occurs, it would be good to get more information about the database and query structure. Greg -- Greg Lehey, Senior Software Engineer, Online Backup MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Diary http://www.lemis.com/grog/diary.html Are you MySQL certified? http://www.mysql.com/certification/ pgpdHxyxEL6oh.pgp Description: PGP signature
Re: Records in front of and behind another record
Mysql 4 I have a frustrating case here, a list of numbers, say, 0 through 30,000 or so, however, there are gaps in the numbers, so not purely sequential. I am faced with selecting one record from the dataset, that's simple, however, before that select, I need to make sure the record is there, and if it is not, find the one either just before it, or just after it. Whichever is closest. If they are the same, lean on either one, I don't really care. Any suggestion on this one would be appreciated, I can do this with 3 separate queries, but that is taking too long, since I have to do this with two separate datasets and shove this all back out a browser to the user. Clearing this up a little, I have data like this: 3, 4, 5, 8, 9, 10 If I am looking for 6, it is not there, I want back 5 and 8, in this case, 5 is closest, so I would like 5 back, but both are ok, as I can use server side code to determine the closest. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Records in front of and behind another record
Mysql 4 I have a frustrating case here, a list of numbers, say, 0 through 30,000 or so, however, there are gaps in the numbers, so not purely sequential. I am faced with selecting one record from the dataset, that's simple, however, before that select, I need to make sure the record is there, and if it is not, find the one either just before it, or just after it. Whichever is closest. If they are the same, lean on either one, I don't really care. Any suggestion on this one would be appreciated, I can do this with 3 separate queries, but that is taking too long, since I have to do this with two separate datasets and shove this all back out a browser to the user. Clearing this up a little, I have data like this: 3, 4, 5, 8, 9, 10 If I am looking for 6, it is not there, I want back 5 and 8, in this case, 5 is closest, so I would like 5 back, but both are ok, as I can use server side code to determine the closest. I am thinking UNION and two SQL queries would do this, how is UNION optimized, is it more or less the same as running two selects? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is the fsync() fake on FreeBSD6.1?
Hi, I benchmarked MySQL 4.1.18 on FreeBSD 6.1 and Debian 3.1 using Super Smack 1.3 some days ago. The benchmark table is CREATE TABLE `Account` ( `aid` int(11) NOT NULL auto_increment, `name` char(20) NOT NULL default '', `flag` int(11) NOT NULL default '0', `uidcount` int(11) NOT NULL default '0', `balance` int(11) NOT NULL default '0', `point` int(11) NOT NULL default '0', `blocktm` int(11) NOT NULL default '0', `ipnum` int(10) unsigned default NULL, `newdate` datetime default NULL, PRIMARY KEY (`aid`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; And it has 10,000,000 rows. The SQL statement is update Account set balance= balance + 1 where aid=?; The result is followed: OSClientsResult(queries per second) TPS(got from iostat) FreeBSD6.150 516.1 about 2000 Debian3.1 50 49.8 about 200 The result surprise me. The MySQL Performance on FreeBSD6.1 is about 10 times of on Debian3.1，and the output of iostat also shows it. I know that MySQL uses fsync() to flush both the data and log files at default when using innodb engine( http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html). Our evaluating computer only has a 1RPM SCSI hard disk. I think it can do about 200 sequential fsync() calls per second if the fsync() is real. Is the fsync() on FreeBSD6.1 fake? I mean than the data is only written to the drives memory and so can be lost if power goes down. And how I can confirm this? If the fsync() is fake, how can I get the real fsync? Any comment is welcome! PS: 1. Our evaluating computer is DELL PowerEdge 1650。Its hardware configuration is followed: CPU: 2 * Intel Pentium III 1.33GHz 512KB Level 2 Cache(smp) Memory: 1024MB ECC SDRAM HD: SEAGATE ST336706LC（36GB Ultra160 SCSI 1RPM） NIC: Intel(R) PRO/1000 Network Connection 2. Some important parameters in MySQL configuration file are here: log-bin sync_binlog=1 innodb_safe_binlog innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 regards, Leo Huang
Server at 99%
Folks, please forgive the newbie panic but I believe I've gotten myself into trouble. After experiencing some problems with a db I decided to run the 'Repair' commands from the MySQL Administrator program. After failing to find anything significant I (somewhat foolishly) ran the Use FRM repair option believing it would simply recreate the index. I now have an XServe running with mysqld at @ 98% CPU and it's been like that for over two hours. I can connect via mysql (CLI) or the MySQL Administrator program but I can't select a db to use. The total of the data on the server is only around 40 MB. I'm concerned (terrified?) if I do anything I'll hopelessly corrupt everything. I do have a fairly recent backup but I'd far prefer to get the existing dbs back again. Can anyone advise what I should do? Should I just let it keep going? How long would this type of repair take with only 40 MB of data? Is there anything else I can try? Any help would be greatly appreciated. regards //kim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Records in front of and behind another record
Scott Haneda wrote: Mysql 4 I have a frustrating case here, a list of numbers, say, 0 through 30,000 or so, however, there are gaps in the numbers, so not purely sequential. I am faced with selecting one record from the dataset, that's simple, however, before that select, I need to make sure the record is there, and if it is not, find the one either just before it, or just after it. Whichever is closest. If they are the same, lean on either one, I don't really care. Any suggestion on this one would be appreciated, I can do this with 3 separate queries, but that is taking too long, since I have to do this with two separate datasets and shove this all back out a browser to the user. It really would be helpful when posting a question like this if you would actually show us the three queries and tell us how long too long is. It would also help to know the structure of your table. Scott Haneda wrote: Clearing this up a little, I have data like this: 3, 4, 5, 8, 9, 10 If I am looking for 6, it is not there, I want back 5 and 8, in this case, 5 is closest, so I would like 5 back, but both are ok, as I can use server side code to determine the closest. OK, that's clear. Scott Haneda wrote: I am thinking UNION and two SQL queries would do this, how is UNION optimized, is it more or less the same as running two selects? Usually, but a UNION of what two queries? I won't comment on the efficiency of a query I haven't seen. This can be done in one query. You didn't give any details, so I'll make them up. The table is named scotts_table, the numbers are in the column named val, and the target value is 413. I'll use user variables for clarity, but they aren't necessary. For each row in the table, the distance from that row's val to the target value is the absolute value of the difference between val and the target value. The row with the smallest distance is the one you want. Hence, SET @target = 413; SELECT * FROM scotts_table ORDER BY ABS([EMAIL PROTECTED]) LIMIT 1; Of course, that's a full-table scan with a filesort, so it's not very efficient. We can improve on this, however, if we know the size of the largest gap. For example, if we know that the largest gap is 26, we can do the following: SET @target = 413; SET @range=26; SELECT * FROM scotts_table WHERE val BETWEEN (@target - @range) AND (@target + @range) ORDER BY ABS([EMAIL PROTECTED]) LIMIT 1; In this case, mysql can use the index on val (You do have an index on val, right?) to choose the few rows near the target value, before performing the filesort on just those few matching rows. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Records in front of and behind another record
OK, that's clear. Sorry about the bervity, ill clear this up below. Scott Haneda wrote: I am thinking UNION and two SQL queries would do this, how is UNION optimized, is it more or less the same as running two selects? Usually, but a UNION of what two queries? I won't comment on the efficiency of a query I haven't seen. Here is what I was thinking: (select zipcode FROM zipcodes_head_of_house WHERE zipcode = '94949' ORDER BY zipcode ASC LIMIT 1) UNION (select zipcode FROM zipcodes_head_of_house WHERE zipcode = '94949' ORDER BY zipcode DESC LIMIT 1) This seems to give me either one of two records, in which case, its pretty simple to find the closest one. Here is my table structure, there is a lot more auxiliary data to it, but these are the main bits that matter. describe zipcodes_head_of_house; ++---+--+-+--++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+--++ | id | int(11) | | PRI | NULL | auto_increment | | zipcode| char(5) | | MUL | || | latitude | double(12,6) | | | 0.00 || | longitude | double(12,6) | | | 0.00 || | created| timestamp(14) | YES | | NULL || ++---+--+-+--++ This can be done in one query. You didn't give any details, so I'll make them up. The table is named scotts_table, the numbers are in the column named val, and the target value is 413. I'll use user variables for clarity, but they aren't necessary. For each row in the table, the distance from that row's val to the target value is the absolute value of the difference between val and the target value. The row with the smallest distance is the one you want. Hence, SET @target = 413; SELECT * FROM scotts_table ORDER BY ABS([EMAIL PROTECTED]) LIMIT 1; select zipcode from zipcodes_head_of_house order by abs(zipcode-94999) limit 1; +-+ | zipcode | +-+ | 95001 | +-+ I know 94999 is not in the database, and I get back 95001, which should be the closest match, using my UNION to test it: mysql (select zipcode FROM - zipcodes_head_of_house - WHERE zipcode = '94999' ORDER BY zipcode -ASC LIMIT 1) - UNION - (select zipcode FROM - zipcodes_head_of_house - WHERE zipcode = '94999' ORDER BY zipcode - DESC LIMIT 1) - ; +-+ | zipcode | +-+ | 95001 | | 94979 | +-+ 2 rows in set (0.00 sec) And there you are, the 95001 is of course the closest one. I think this is it, this works well, and fast for me. Of course, that's a full-table scan with a filesort, so it's not very efficient. We can improve on this, however, if we know the size of the largest gap. For example, if we know that the largest gap is 26, we can do the following: SET @target = 413; SET @range=26; SELECT * FROM scotts_table WHERE val BETWEEN (@target - @range) AND (@target + @range) ORDER BY ABS([EMAIL PROTECTED]) LIMIT 1; I could probably figure it out, at some point, but right now, I have no idea what the largest gap is. In this case, mysql can use the index on val (You do have an index on val, right?) to choose the few rows near the target value, before performing the filesort on just those few matching rows. I am pretty sure I do, I will check though. thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
