Got an error reading communication packets
Should this worry me: 060331 10:34:24 [Warning] Aborted connection 244161 to db: 'cyrus' user: 'cyrus' host: `debpro' (Got an error reading communication packets) /Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fultext search issues
From: Gabriel PREDA [EMAIL PROTECTED]
You ought to use the *Boolean Full-Text Searches.*
You would then do a:
SELECT title, Comment FROM table_name WHERE MATCH (Comment) AGAINST ('+foo
+bar' IN BOOLEAN MODE);
This way the rows that contain both words have higher relevance... those
that have only one... will have lower relevance.
I thought that using a + char before a word will match *only* those
records that contain that word.
(And using - before words, will find only those records that don't contain
the specified words.)
And using the words with no special signs before, I thought it will give a
higher precedence to the records which contain more searched words.
And I also thought that the rows are not sorted automaticly when searching
in boolean mode.
So I usually search using:
select id, title, match(body) against('word') as rank from table_name where
match(body) against('word' in boolean mode) order by rank;
The search doesn't work slower (or much slower) because it uses twice the
match, the search is made in boolean mode so the +, -, *, , , ,
characters can be used, and the results are sorted.
But is there a better way?
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AlterTable Structure Across Multiple DBs
ALTER TABLE requires a table name for the current database.
You can specify:
ALTER TABLE db_name.tbl_name MODIFY col_name ...
or
mysql alter table test1.t1 modify test1.t1.set1 varchar(30);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
but you cannot modify a table in a different database to the
one you first specify after TABLE db_name:
mysql alter table test1.t1 modify test2.t1.set1 varchar(30);
ERROR 1102 (42000): Incorrect database name 'test2'
.
.
.
mysql show create table t1 \G
*** 1. row
Table: t1
Create Table: CREATE TABLE `t1` (
`ID` int(11) NOT NULL auto_increment,
`set1` set('this','is','today') default NULL,
`col2` char(20) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql
mysql alter table t1 modify set1 varchar(30);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql show create table t1 \G
*** 1. row
Table: t1
Create Table: CREATE TABLE `t1` (
`ID` int(11) NOT NULL auto_increment,
`set1` varchar(30) default NULL,
`col2` char(20) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
It should not take you too long to use the command history
to recall the alter table ... statement, edit it, and work your
way through the databases like that.
Another way is to write a script that will parse the
database names in the data directory, and then generate the
SQL code to perfom the multiple ALTER TABLE statements.
The script could then replace the db_name to be changed on
each iteration.
To be safe you could make copies of your databases, and
perform the ALTER TABLE statements away from your live data
directory.
When you are happy with the modifications, then copy the
altered databases back to your live data directory.
Personally I'd feel alot safer altering one database table
at a time - just in case errors start appearing.
You need to be carefull that you do not loose any multiple
values in your set, as varchar will only hold one value at
a time.
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Thu, 30 Mar 2006, Jason Dimberg wrote:
To: mysql@lists.mysql.com
From: Jason Dimberg [EMAIL PROTECTED]
Subject: AlterTable Structure Across Multiple DBs
I have about 25 databases with the same structure and
occasionally need to update the table structure. For
example, I recently found a mistake in a field that was of
type SET and needed to be VARCHAR. I will now need to
edit each table. Is there an easy method to alter table
structure across multiple dbs as opposed to editing each
one individually?
In retrospect I should have combined them into one db and
may consider doing that.
All dbs start with 'pm_' and have identically named tables
MySQL 5.0.18
Windows 2003
Thank you,
--
*Jason Dimberg*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete Duplicates
Rich [EMAIL PROTECTED] wrote on 03/30/2006 09:11:56 PM: Hi there. Any quick way of killing duplicate records? Cheers Yes. Some ways involve subqueries, others temporary tables. What version are you on? What are your table definition(s) (use SHOW CREATE TABLE to dump the defs)? How do your define duplicate for the table(s) you are having problems with? More details will get you better answers ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: TIMESTAMP field not automatically updating last_updated field
Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. It's true. No Update = No change of timestamp! -- 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]
Table Type For PK/FK
I have been reading the definitive guide to MySQL5, and I am not finding a answer to a question. I am designing a project in which I am going to have primary keys, and foreign keys. In order to keep things proper, I am going to have delete on cascades on my foreign keys, so when the primary key is deleted, the data model is kept clean. As I have been reading, it appears that the InnoDB Storage Engine supports PK/FK relationships. And that the MyISAM does not. But I cannot verify that. Also, when I use replication, it appears that both engines, require a different replication technique? But what if I mix both types of engines, how do I replicate that? Sorry this is a bit of a broad post, but these two questions will help me figure my needs for the design. Thanks, Scott
Re: Table Type For PK/FK
Hi Scott, Indeed, only the BDB and InnoDB storage engines support referential integrity. If you accidentally create the table as MyISAM, there is no error, though -- the constraints serve as a comment. Replication is storage-engine independent, so you shouldn't have a problem with that. Basically it takes the commands from the binary (update,delete,insert,replace) logs and applies them to the slave. -Sheeri On 3/31/06, Scott Purcell [EMAIL PROTECTED] wrote: I have been reading the definitive guide to MySQL5, and I am not finding a answer to a question. I am designing a project in which I am going to have primary keys, and foreign keys. In order to keep things proper, I am going to have delete on cascades on my foreign keys, so when the primary key is deleted, the data model is kept clean. As I have been reading, it appears that the InnoDB Storage Engine supports PK/FK relationships. And that the MyISAM does not. But I cannot verify that. Also, when I use replication, it appears that both engines, require a different replication technique? But what if I mix both types of engines, how do I replicate that? Sorry this is a bit of a broad post, but these two questions will help me figure my needs for the design. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Type For PK/FK
As I have been reading, it appears that the InnoDB Storage Engine supports PK/FK relationships. And that the MyISAM does not. But I cannot verify that. That is correct. Martijn Tonies Database Workbench - development tool for MySQL and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Type For PK/FK
Hello Sheeri, Indeed, only the BDB and InnoDB storage engines support referential integrity. If you accidentally create the table as MyISAM, there is no error, though -- the constraints serve as a comment. No error? A comment? What use is that? If you want FKs, having the FKs as a comment is useless. Instead, re-create the table as of the InnoDB type. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Type For PK/FK
I didn't write the codebase for MySQL, so it's pointless to tell me that it's useless to be able to create a foreign key on a MyISAM table. I agree that it's useless, however, it's possible, which is why I put it in there -- as a caveat. The use is that apparently in future versions MyISAM will support foreign key constraints. It's a comment because it still shows up in SHOW CREATE TABLE and such. Like I said, I didn't design MySQL -- I just use it and was warning that it's possible to create a table. I've seen the dreaded Error number 150 way too many times, and sometimes it's because I forgot the engine=innodb part of the CREATE TABLE statement. -Sheeri On 3/31/06, Martijn Tonies [EMAIL PROTECTED] wrote: Hello Sheeri, Indeed, only the BDB and InnoDB storage engines support referential integrity. If you accidentally create the table as MyISAM, there is no error, though -- the constraints serve as a comment. No error? A comment? What use is that? If you want FKs, having the FKs as a comment is useless. Instead, re-create the table as of the InnoDB type. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- 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: stunningly slow query
Are your logs and data on the same partition? That's a bad idea for recovering from a blown part of the disk, but we also saw that one of our databases would crash when there were lots of inserts/updates/replaces -- other databases, which had the same version of MySQL and operating system, had the logs and data on a separate partition, and they did not crash. We posited that there was just too much disk seeking going on, things would get slow, etc. -Sheeri On 3/30/06, Christopher A. Kantarjiev [EMAIL PROTECTED] wrote: Mike Wexler wrote: It doesn't really answer your question, but have you tried INSERT DELAYED as a work around? We've not had a lot of luck with this in the past, but it's worth a try. Also the updated status is strange, because that generally indicates that its looking for the record to be updated, but since the record is new, there is no record to be updated. Could it be checking for duplicates? Not that it should be this slow, but you might try ALTER TABLE xxx DISABLE KEYS and see how that effect performance. At least it will tell you whether the problem is in updating the keys, or something else. It's certainly checking for duplicates. There are 10034461 records in the link_area table at the moment, and 514408715 in trimble.old_crumb. -- 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]
Database backup Problem
I recently migrated all of our mysql databases from 4.1.X to 5.0.X. Everything is working properly except my backups. Here is the command that I am using: mysqldump -u user -ppassword --opt -A -F --delete-master-logs | gzip $BACKUPFILE`date +%m%d%y`.sql.gz This worked on the old database server, but now I'm getting the following error: mysqldump: mysqldump: Couldn't execute 'RESET MASTER': Binlog closed, cannot RESET MASTER (1186) Any insight on what I'm doing wrong would be great! Thanks, Cody -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there anyway to return an array?
So, in theory this should be pretty straight forward to do right? Well
I'm new to UDF's, so how mysql is passing the data to the UDF is a bit of
a mystery. I'm hoping someone can help me understand this.
I'm selecting data from a BLOB field like this:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
You can see that I'm only taking a portion of the string, but it is still
in raw form.
Now I would like to pass it to my UDF function called toDoubleArray, to
convert each 8 byte section to a double.
I call the function like this: (you can ignore conv_param)
SELECT toDoubleArray(fdata,%1.3E,conv_param) INTO fdata_string;
Well, I get a Lost Connection when the function is called.
Here is the UDF:
my_bool toDoubleArray_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
if (args-arg_count != 3)
{
strcpy(message,Wrong arguments to toDouble: should be
toDoubleArray(blob));
return 1;
}
initid-max_length = strlen(args-args[0])/8 * 128;
return 0;
}
char * toDoubleArray(UDF_INIT *initid, UDF_ARGS *args, char *result,
unsigned long *length,char *is_null,char *error)
{
int curr_buf_ptr =0; /* current length of FloatString */
char * data = args-args[0]; /* just to make it easier to reference the
string */
char * f = args-args[1]; /* get the format string */
char format[15];
sprintf(format,%s,,f); /* put a comma at the end of format for CSV
format */
for(int i=0;istrlen(data);i +=8){
sprintf(result + curr_buf_ptr,format,*((double *)(data +i)));
curr_buf_ptr = strlen(result);
}
result[curr_buf_ptr -1] = '\0';
*length = strlen(result);
return result;
}
This should return a comma delimited list of double values in a string
format (ascii representation).
Like I said, I'm new to UDF's so it is likely I'm not aware of conventions
to follow that are well known to others.
Any help would be great.
David Godsey
David Godsey wrote:
I know, I know, sounds like something that should be done in the
presentation layer, howerver if possible, I would like to provide
common
data presentation to multiple presentation layers (written in different
languages).
So is there anyway to return an array in mysql?
Your aware your doing something stupid and want to do it anyway :-(
Why not return the values from your user defined mysql function as a
(properly quoted) ,comma seperated list. Since almost every application
language now has a standard csv file handling library it should be easy
to use across diverse display technologies.
Urrgh
Nigel
Accomplishing the impossible means only that the boss will add it to your
regular duties.
David Godsey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database backup Problem
Just checking if this user has RELOAD PRIVILEGES, as it is needed for the --master-data option which is automatically enabled with --delete-master-logs , does the error duplicate on subsequent attempts ?? Kishore Jalleda http://kjalleda.googlepages.com/projects On 3/31/06, Cody Holland [EMAIL PROTECTED] wrote: I recently migrated all of our mysql databases from 4.1.X to 5.0.X. Everything is working properly except my backups. Here is the command that I am using: mysqldump -u user -ppassword --opt -A -F --delete-master-logs | gzip $BACKUPFILE`date +%m%d%y`.sql.gz This worked on the old database server, but now I'm getting the following error: mysqldump: mysqldump: Couldn't execute 'RESET MASTER': Binlog closed, cannot RESET MASTER (1186) Any insight on what I'm doing wrong would be great! Thanks, Cody -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance problems.
As others have suggested , turn your slow query log on in my.cnf , and set your long-query_time, and you can view your slow queries in the *.log file in your data dir, and then try to optimize them, you could also try mytop ( http://jeremy.zawodny.com/mysql/mytop/) , and check your queries in real time..., also check SHOW FULL PROCESSLIST to see what state the query's are in . Kishore Jalleda http://kjalleda.googlepages.com/projects On 3/29/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259mysql/ 3055mysql/snort 2184mysql/snort_archive 1546mysql/snort_archive/data.MYD 1546mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74 mysql/snort_archive/event.MYD 74 mysql/snort/event.MYD 42 mysql/snort_archive/data.MYI 42 mysql/snort/data.MYI 40 mysql/snort_archive/icmphdr.MYI 40 mysql/snort/icmphdr.MYI 35 mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:4416:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328
Problems with importing the british pound (£) an d euro (€) sign
Folks, I have a mysql 5.0 db with the following char sets: mysql show variables like '%char%'; +--+--+ | Variable_name| Value| +--+--+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | C:\MySQL\share\charsets\ | +--+--+ I have a column that stores currency symbols - two of which are the British pound (£) and euro (€) sign. I can I export via mysqldump no with problem - the £ sign appears in the export file OK, although the euro is converted into an odd looking set of chars But when I import using the command line client like this: mysql -u user -ppwd dbname datadump.sql, the £ sign and euro sign get changed into £ and € respectively. I have tried forcing the encoding by using mysql --default-character-set=latin1 -u user -ppwd dbname datadump.sql but this produces the same results. Any ideas? TIA - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Type For PK/FK
From the 5.0.18 manual: The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB storage engine, which implements ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...). See Section 14.2.6.4, FOREIGN KEY Constraints. For other storage engines, the clauses are parsed but ignored. The CHECK clause is parsed but ignored by all storage engines. See Section 13.1.5, CREATE TABLE Syntax. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references. See Section 1.9.5, MySQL Differences from Standard SQL. You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. You must use separate statements. InnoDB supports the use of ALTER TABLE to drop foreign keys: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. You must use separate statements. For more information, see Section 14.2.6.4, FOREIGN KEY Constraints. Regards Keith ;-) In theory, theory and practice are the same; in practice they are not. On Fri, 31 Mar 2006, sheeri kritzer wrote: To: Martijn Tonies [EMAIL PROTECTED] From: sheeri kritzer [EMAIL PROTECTED] Subject: Re: Table Type For PK/FK I didn't write the codebase for MySQL, so it's pointless to tell me that it's useless to be able to create a foreign key on a MyISAM table. I agree that it's useless, however, it's possible, which is why I put it in there -- as a caveat. The use is that apparently in future versions MyISAM will support foreign key constraints. It's a comment because it still shows up in SHOW CREATE TABLE and such. Like I said, I didn't design MySQL -- I just use it and was warning that it's possible to create a table. I've seen the dreaded Error number 150 way too many times, and sometimes it's because I forgot the engine=innodb part of the CREATE TABLE statement. -Sheeri On 3/31/06, Martijn Tonies [EMAIL PROTECTED] wrote: Hello Sheeri, Indeed, only the BDB and InnoDB storage engines support referential integrity. If you accidentally create the table as MyISAM, there is no error, though -- the constraints serve as a comment. No error? A comment? What use is that? If you want FKs, having the FKs as a comment is useless. Instead, re-create the table as of the InnoDB type. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there anyway to return an array?
Never mind, I was being dumb. I wasn't using the args-lengths array for
binary data, so it was dying on a strlen(args-args[0]) which makes sense.
Thanks anyway.
David Godsey
So, in theory this should be pretty straight forward to do right? Well
I'm new to UDF's, so how mysql is passing the data to the UDF is a bit of
a mystery. I'm hoping someone can help me understand this.
I'm selecting data from a BLOB field like this:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
You can see that I'm only taking a portion of the string, but it is still
in raw form.
Now I would like to pass it to my UDF function called toDoubleArray, to
convert each 8 byte section to a double.
I call the function like this: (you can ignore conv_param)
SELECT toDoubleArray(fdata,%1.3E,conv_param) INTO fdata_string;
Well, I get a Lost Connection when the function is called.
Here is the UDF:
my_bool toDoubleArray_init(UDF_INIT *initid, UDF_ARGS *args, char
*message)
{
if (args-arg_count != 3)
{
strcpy(message,Wrong arguments to toDouble: should be
toDoubleArray(blob));
return 1;
}
initid-max_length = strlen(args-args[0])/8 * 128;
return 0;
}
char * toDoubleArray(UDF_INIT *initid, UDF_ARGS *args, char *result,
unsigned long *length,char *is_null,char *error)
{
int curr_buf_ptr =0; /* current length of FloatString */
char * data = args-args[0]; /* just to make it easier to reference the
string */
char * f = args-args[1]; /* get the format string */
char format[15];
sprintf(format,%s,,f); /* put a comma at the end of format for CSV
format */
for(int i=0;istrlen(data);i +=8){
sprintf(result + curr_buf_ptr,format,*((double *)(data +i)));
curr_buf_ptr = strlen(result);
}
result[curr_buf_ptr -1] = '\0';
*length = strlen(result);
return result;
}
This should return a comma delimited list of double values in a string
format (ascii representation).
Like I said, I'm new to UDF's so it is likely I'm not aware of conventions
to follow that are well known to others.
Any help would be great.
David Godsey
David Godsey wrote:
I know, I know, sounds like something that should be done in the
presentation layer, howerver if possible, I would like to provide
common
data presentation to multiple presentation layers (written in
different
languages).
So is there anyway to return an array in mysql?
Your aware your doing something stupid and want to do it anyway :-(
Why not return the values from your user defined mysql function as a
(properly quoted) ,comma seperated list. Since almost every application
language now has a standard csv file handling library it should be easy
to use across diverse display technologies.
Urrgh
Nigel
Accomplishing the impossible means only that the boss will add it to your
regular duties.
David Godsey
Accomplishing the impossible means only that the boss will add it to your
regular duties.
David Godsey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot restart service MySQL
Please help, I can't start my server!! I was running a query, and it seemed to be hanging. After waiting about 15 minutes, I finally did a 'CTRL+BREAK' to abort the process. This happened about 3 times, and finally I decided to restart the MySQL service to see if that would help. Well, when I did that, the service wouldn't stop. Now when I go into the Services manager in Windows, the MySQL service is listed as 'Stopping,' and all the control buttons (stop, start, restart) are all grayed out. The problem is, the service is NOT stopping and I can't restart it doing a 'mysqld --console' either. What do I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is port forwarded connection taken as local?
Hello everyone, What I'm after is trying to figure out a way to centrally and remotely managing (e.g. on server1) our MySQL servers (server2 is an example) on different machines. Right now, these MySQL servers are all set up to only accept logons from localhost. My questions: 1. server1 has to be able to connect to server2 directly via SSH, right? 2. On server2, does 'grant ...to [EMAIL PROTECTED] identified by ...' have to be done? I've been looking through the relevent threads regarding how to make port forwarding for MySQL work. I saw one thread mentioned that port forwarded MySQL connection was taken as a local connection. But my own testing cannot agree with that. I'm confused. Here is what I did: On server1: server1% ssh -2 -l myusername -N -L 3307:server2:3306 server2 server1% mysql -P 3307 (in another term window) ERROR 1045 (28000): Access denied for user 'myusername'@'localhost' (using password: YES) But on server2, the following command works fine. server2% mysql -h localhost -u myusername -p I'd appreciate if anybody would shed some light. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot restart service MySQL
In Windows, you have 3 alternatives: 1 - wait untill it stops the service (can take very long time). 2 - restart the server (your users might cry a bit). 3 - Try to kill the task using Task Manager (this might not work, depending on the service). /Johan Sara Woglom wrote: Please help, I can't start my server!! I was running a query, and it seemed to be hanging. After waiting about 15 minutes, I finally did a 'CTRL+BREAK' to abort the process. This happened about 3 times, and finally I decided to restart the MySQL service to see if that would help. Well, when I did that, the service wouldn't stop. Now when I go into the Services manager in Windows, the MySQL service is listed as 'Stopping,' and all the control buttons (stop, start, restart) are all grayed out. The problem is, the service is NOT stopping and I can't restart it doing a 'mysqld --console' either. What do I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to merge my tables?
Hi, I hope this is the right forum for this question. If not, I'm happy to get some tip on where to post this. My problem: I have like 20 tables of data and need to merge these, making a selection, (and dump it into a text file) to import into a reporting tool. The tables is like salesrep, value of sales, number of customers, and som series of codes. If I do a JOIN, I only get the salesreps that exist in that specific table, but new people are added and some have left the company. How can this be done?? A short example of the data: Table_1998: empl_id | sales98 | customers98 | etc98 | ... 1001 | 12659 | 123 | | ... 1002 | 103674 | 597 | hued | ... 1003 | 23589 | 314 | hjeoir| ... Table_1999: empl_id | sales99 | customers99 | etc99 | ... 1001 | 35678 | 213 | dwrer | ... 1002 | 125795 | 603 | freui | ... 1003 | 45678 | 343 | hfiwu | ... 1004 |8753 | 96 | poijo | ... Table_2000: empl_id | sales00 | customers00 | etc00 | ... 1001 | 97361 | 526 | urhfn | ... 1003 | 98716 | 649 | jdwoh | ... 1004 | 15872 | 147 | oijnm | ... Now I try to get the customersXX columns for every emloyee from these tables. What I would like to see in my result: empl_id | customers98 | customers99 | customers00 | ... 1001 | 123 | 213 | 526 | ... 1002 | 597 | 603 |NULL | ... 1003 | 314 | 343 | 649 | ... 1004 |NULL | 96 | 147 | ... I've tried everything and I'm out of clues. Can it be done?? If so, how??? Any help/tips are very welcome!! /Johan, Uppsala - Sweden -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot restart service MySQL
Sorry, I should have been more specific. I tried 'mysqladmin -u root shutdown -p' and that did not work. The service is hung up in a pending state and I cannot control it. It is Net Error 2189: 'The service could not be controlled in its present state.' There has to be some way I can fix this without bouncing the server, because I really can't do that. -Original Message- From: Ariel Sánchez Mora [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 4:10 PM To: Sara Woglom Subject: RE: Cannot restart service MySQL Have you tried Command line: mysqladmin -u root shutdown -p Without the . If it has a password, it will ask for it. If it doesn't, remove the -p. If that doesn't work, tell the mailing list what you did, any error messages you may get, and they'll tell you what the problem is. I'm not a MySQL guru so I would restart the machine (but I imagine you can't do that?) and try starting it again, or make a new install. It may be your computer is overloaded at the moment, or some program doesn't let your server exit. Good luck! ariel -Mensaje original- De: Sara Woglom [mailto:[EMAIL PROTECTED] Enviado el: viernes, 31 de marzo de 2006 15:05 Para: MySQL List Asunto: Cannot restart service MySQL Please help, I can't start my server!! I was running a query, and it seemed to be hanging. After waiting about 15 minutes, I finally did a 'CTRL+BREAK' to abort the process. This happened about 3 times, and finally I decided to restart the MySQL service to see if that would help. Well, when I did that, the service wouldn't stop. Now when I go into the Services manager in Windows, the MySQL service is listed as 'Stopping,' and all the control buttons (stop, start, restart) are all grayed out. The problem is, the service is NOT stopping and I can't restart it doing a 'mysqld --console' either. What do I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is port forwarded connection taken as local?
Bing Du wrote: Hello everyone, What I'm after is trying to figure out a way to centrally and remotely managing (e.g. on server1) our MySQL servers (server2 is an example) on different machines. Right now, these MySQL servers are all set up to only accept logons from localhost. My questions: 1. server1 has to be able to connect to server2 directly via SSH, right? 2. On server2, does 'grant ...to [EMAIL PROTECTED] identified by ...' have to be done? I've been looking through the relevent threads regarding how to make port forwarding for MySQL work. I saw one thread mentioned that port forwarded MySQL connection was taken as a local connection. But my own testing cannot agree with that. I'm confused. Here is what I did: On server1: server1% ssh -2 -l myusername -N -L 3307:server2:3306 server2 server1% mysql -P 3307 (in another term window) ERROR 1045 (28000): Access denied for user 'myusername'@'localhost' (using password: YES) You need to grant permissions to 'myusername'@'localhost' as indicated in the line above. But on server2, the following command works fine. server2% mysql -h localhost -u myusername -p I'd appreciate if anybody would shed some light. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to merge my tables?
Do you have a table that has *all* your employees ids (empl_id)? Shaunak Kashyap Senior Web Developer WPT Enterprises, Inc. 5700 Wilshire Blvd., Suite 350 Los Angeles, CA 90036 Direct: 323.330.9870 Main: 323.330.9900 www.worldpokertour.com Confidentiality Notice: This e-mail transmission (and/or the attachments accompanying) it may contain confidential information belonging to the sender which is protected. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking of any action in reliance on the contents of this information is prohibited. If you have received this transmission in error, please notify the sender by reply e-mail and destroy all copies of this transmission. -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 1:13 PM To: mysql@lists.mysql.com Subject: How to merge my tables? Hi, I hope this is the right forum for this question. If not, I'm happy to get some tip on where to post this. My problem: I have like 20 tables of data and need to merge these, making a selection, (and dump it into a text file) to import into a reporting tool. The tables is like salesrep, value of sales, number of customers, and som series of codes. If I do a JOIN, I only get the salesreps that exist in that specific table, but new people are added and some have left the company. How can this be done?? A short example of the data: Table_1998: empl_id | sales98 | customers98 | etc98 | ... 1001 | 12659 | 123 | | ... 1002 | 103674 | 597 | hued | ... 1003 | 23589 | 314 | hjeoir| ... Table_1999: empl_id | sales99 | customers99 | etc99 | ... 1001 | 35678 | 213 | dwrer | ... 1002 | 125795 | 603 | freui | ... 1003 | 45678 | 343 | hfiwu | ... 1004 |8753 | 96 | poijo | ... Table_2000: empl_id | sales00 | customers00 | etc00 | ... 1001 | 97361 | 526 | urhfn | ... 1003 | 98716 | 649 | jdwoh | ... 1004 | 15872 | 147 | oijnm | ... Now I try to get the customersXX columns for every emloyee from these tables. What I would like to see in my result: empl_id | customers98 | customers99 | customers00 | ... 1001 | 123 | 213 | 526 | ... 1002 | 597 | 603 |NULL | ... 1003 | 314 | 343 | 649 | ... 1004 |NULL | 96 | 147 | ... I've tried everything and I'm out of clues. Can it be done?? If so, how??? Any help/tips are very welcome!! /Johan, Uppsala - Sweden -- 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: How to merge my tables?
No, I don't have that. There's about 5-10% change in employees ids from every year, and no one took any interest in this system before I got it in my lap... Would it help to have one?? Shaunak Kashyap wrote: Do you have a table that has *all* your employees ids (empl_id)? Shaunak Kashyap Senior Web Developer WPT Enterprises, Inc. 5700 Wilshire Blvd., Suite 350 Los Angeles, CA 90036 Direct: 323.330.9870 Main: 323.330.9900 www.worldpokertour.com Confidentiality Notice: This e-mail transmission (and/or the attachments accompanying) it may contain confidential information belonging to the sender which is protected. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking of any action in reliance on the contents of this information is prohibited. If you have received this transmission in error, please notify the sender by reply e-mail and destroy all copies of this transmission. -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 1:13 PM To: mysql@lists.mysql.com Subject: How to merge my tables? Hi, I hope this is the right forum for this question. If not, I'm happy to get some tip on where to post this. My problem: I have like 20 tables of data and need to merge these, making a selection, (and dump it into a text file) to import into a reporting tool. The tables is like salesrep, value of sales, number of customers, and som series of codes. If I do a JOIN, I only get the salesreps that exist in that specific table, but new people are added and some have left the company. How can this be done?? A short example of the data: Table_1998: empl_id | sales98 | customers98 | etc98 | ... 1001 | 12659 | 123 | | ... 1002 | 103674 | 597 | hued | ... 1003 | 23589 | 314 | hjeoir| ... Table_1999: empl_id | sales99 | customers99 | etc99 | ... 1001 | 35678 | 213 | dwrer | ... 1002 | 125795 | 603 | freui | ... 1003 | 45678 | 343 | hfiwu | ... 1004 |8753 | 96 | poijo | ... Table_2000: empl_id | sales00 | customers00 | etc00 | ... 1001 | 97361 | 526 | urhfn | ... 1003 | 98716 | 649 | jdwoh | ... 1004 | 15872 | 147 | oijnm | ... Now I try to get the customersXX columns for every emloyee from these tables. What I would like to see in my result: empl_id | customers98 | customers99 | customers00 | ... 1001 | 123 | 213 | 526 | ... 1002 | 597 | 603 |NULL | ... 1003 | 314 | 343 | 649 | ... 1004 |NULL | 96 | 147 | ... I've tried everything and I'm out of clues. Can it be done?? If so, how??? Any help/tips are very welcome!! /Johan, Uppsala - Sweden -- 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: stunningly slow query
Are your logs and data on the same partition? That's a bad idea for recovering from a blown part of the disk, but we also saw that one of our databases would crash when there were lots of inserts/updates/replaces -- other databases, which had the same version of MySQL and operating system, had the logs and data on a separate partition, and they did not crash. It's a MyISAM table. Are there separate logs files? If so, where? I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on separate drives. We're investigating a possible MERGE organization. I'll report back if we learn anything new. Thanks, chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to merge my tables?
OK. No problem. We can hopefully still make this work with a temporary table. The SQL will look something like this: CREATE TEMPORARY TABLE tmp_sales_rep SELECT empl_id FROM Table_1998 UNION SELECT empl_id FROM Table_1999 UNION SELECT empl_id FROM Table_2000 SELECT t.empl_id, t98.sales98, t99.sales99, t00.sales00 FROM tmp_sales_rep AS t LEFT JOIN Table_1998 AS t98 ON t.empl_id = t98.empl_id LEFT JOIN Table_1999 AS t99 ON t.empl_id = t99.empl_id LEFT JOIN Table_2000 AS t00 ON t.empl_id = t00.empl_id Hope that helps, Shaunak Kashyap Senior Web Developer WPT Enterprises, Inc. 5700 Wilshire Blvd., Suite 350 Los Angeles, CA 90036 Direct: 323.330.9870 Main: 323.330.9900 www.worldpokertour.com Confidentiality Notice: This e-mail transmission (and/or the attachments accompanying) it may contain confidential information belonging to the sender which is protected. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking of any action in reliance on the contents of this information is prohibited. If you have received this transmission in error, please notify the sender by reply e-mail and destroy all copies of this transmission. -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 2:07 PM To: mysql@lists.mysql.com Subject: Re: How to merge my tables? No, I don't have that. There's about 5-10% change in employees ids from every year, and no one took any interest in this system before I got it in my lap... Would it help to have one?? Shaunak Kashyap wrote: Do you have a table that has *all* your employees ids (empl_id)? Shaunak Kashyap Senior Web Developer WPT Enterprises, Inc. 5700 Wilshire Blvd., Suite 350 Los Angeles, CA 90036 Direct: 323.330.9870 Main: 323.330.9900 www.worldpokertour.com Confidentiality Notice: This e-mail transmission (and/or the attachments accompanying) it may contain confidential information belonging to the sender which is protected. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking of any action in reliance on the contents of this information is prohibited. If you have received this transmission in error, please notify the sender by reply e-mail and destroy all copies of this transmission. -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 1:13 PM To: mysql@lists.mysql.com Subject: How to merge my tables? Hi, I hope this is the right forum for this question. If not, I'm happy to get some tip on where to post this. My problem: I have like 20 tables of data and need to merge these, making a selection, (and dump it into a text file) to import into a reporting tool. The tables is like salesrep, value of sales, number of customers, and som series of codes. If I do a JOIN, I only get the salesreps that exist in that specific table, but new people are added and some have left the company. How can this be done?? A short example of the data: Table_1998: empl_id | sales98 | customers98 | etc98 | ... 1001 | 12659 | 123 | | ... 1002 | 103674 | 597 | hued | ... 1003 | 23589 | 314 | hjeoir| ... Table_1999: empl_id | sales99 | customers99 | etc99 | ... 1001 | 35678 | 213 | dwrer | ... 1002 | 125795 | 603 | freui | ... 1003 | 45678 | 343 | hfiwu | ... 1004 |8753 | 96 | poijo | ... Table_2000: empl_id | sales00 | customers00 | etc00 | ... 1001 | 97361 | 526 | urhfn | ... 1003 | 98716 | 649 | jdwoh | ... 1004 | 15872 | 147 | oijnm | ... Now I try to get the customersXX columns for every emloyee from these tables. What I would like to see in my result: empl_id | customers98 | customers99 | customers00 | ... 1001 | 123 | 213 | 526 | ... 1002 | 597 | 603 |NULL | ... 1003 | 314 | 343 | 649 | ... 1004 |NULL | 96 | 147 | ... I've tried everything and I'm out of clues. Can it be done?? If so, how??? Any help/tips are very welcome!! /Johan, Uppsala - Sweden -- 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: How to merge my tables?
I LOVE YOU!!! Great way of doing it. Never thought of creating that extra table and match against it... You really saved my day!! Now I just have to get rid of those rows containing nothing but NULL values (if I select years 2000 ... 2003 empl_id 1002 generates a row with all columns = NULL). I'll look in to it tomorrow, it's in the midddle of the night here and soon my backup system will start = no access to my server... Thanx again, this really got my out of my mindlock. Regards, /Johan Shaunak Kashyap wrote: OK. No problem. We can hopefully still make this work with a temporary table. The SQL will look something like this: CREATE TEMPORARY TABLE tmp_sales_rep SELECT empl_id FROM Table_1998 UNION SELECT empl_id FROM Table_1999 UNION SELECT empl_id FROM Table_2000 SELECT t.empl_id, t98.sales98, t99.sales99, t00.sales00 FROM tmp_sales_rep AS t LEFT JOIN Table_1998 AS t98 ON t.empl_id = t98.empl_id LEFT JOIN Table_1999 AS t99 ON t.empl_id = t99.empl_id LEFT JOIN Table_2000 AS t00 ON t.empl_id = t00.empl_id Hope that helps, Shaunak Kashyap Senior Web Developer WPT Enterprises, Inc. 5700 Wilshire Blvd., Suite 350 Los Angeles, CA 90036 Direct: 323.330.9870 Main: 323.330.9900 www.worldpokertour.com Confidentiality Notice: This e-mail transmission (and/or the attachments accompanying) it may contain confidential information belonging to the sender which is protected. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking of any action in reliance on the contents of this information is prohibited. If you have received this transmission in error, please notify the sender by reply e-mail and destroy all copies of this transmission. -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 2:07 PM To: mysql@lists.mysql.com Subject: Re: How to merge my tables? No, I don't have that. There's about 5-10% change in employees ids from every year, and no one took any interest in this system before I got it in my lap... Would it help to have one?? Shaunak Kashyap wrote: Do you have a table that has *all* your employees ids (empl_id)? Shaunak Kashyap Senior Web Developer WPT Enterprises, Inc. 5700 Wilshire Blvd., Suite 350 Los Angeles, CA 90036 Direct: 323.330.9870 Main: 323.330.9900 www.worldpokertour.com Confidentiality Notice: This e-mail transmission (and/or the attachments accompanying) it may contain confidential information belonging to the sender which is protected. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking of any action in reliance on the contents of this information is prohibited. If you have received this transmission in error, please notify the sender by reply e-mail and destroy all copies of this transmission. -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 1:13 PM To: mysql@lists.mysql.com Subject: How to merge my tables? Hi, I hope this is the right forum for this question. If not, I'm happy to get some tip on where to post this. My problem: I have like 20 tables of data and need to merge these, making a selection, (and dump it into a text file) to import into a reporting tool. The tables is like salesrep, value of sales, number of customers, and som series of codes. If I do a JOIN, I only get the salesreps that exist in that specific table, but new people are added and some have left the company. How can this be done?? A short example of the data: Table_1998: empl_id | sales98 | customers98 | etc98 | ... 1001 | 12659 | 123 | | ... 1002 | 103674 | 597 | hued | ... 1003 | 23589 | 314 | hjeoir| ... Table_1999: empl_id | sales99 | customers99 | etc99 | ... 1001 | 35678 | 213 | dwrer | ... 1002 | 125795 | 603 | freui | ... 1003 | 45678 | 343 | hfiwu | ... 1004 |8753 | 96 | poijo | ... Table_2000: empl_id | sales00 | customers00 | etc00 | ... 1001 | 97361 | 526 | urhfn | ... 1003 | 98716 | 649 | jdwoh | ... 1004 | 15872 | 147 | oijnm | ... Now I try to get the customersXX columns for every emloyee from these tables. What I would like to see in my result: empl_id | customers98 | customers99 | customers00 | ... 1001 | 123 | 213 | 526 | ... 1002 | 597 | 603 |NULL | ... 1003 | 314 | 343 | 649 | ... 1004 |NULL | 96 | 147 | ... I've tried everything and I'm out of clues. Can it be done?? If so, how??? Any help/tips are very welcome!! /Johan, Uppsala - Sweden -- MySQL General Mailing List For list
Update or insert with a single SQL statement?
I have a really simple two-column database: domain_name (primary key) timestamp I'm trying to keep track of the referrer of every visit to a web site, and I'm looking for a single SQL statement (since my ISP limits the total number of calls I can make in a day) that will either insert a new record if the referring domain is not already in there, or simply update the timestamp if the referring domain is already in there. Possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update or insert with a single SQL statement?
On 3/31/06, Brian Dunning [EMAIL PROTECTED] wrote: I have a really simple two-column database: domain_name (primary key) timestamp I'm trying to keep track of the referrer of every visit to a web site, and I'm looking for a single SQL statement (since my ISP limits the total number of calls I can make in a day) that will either insert a new record if the referring domain is not already in there, or simply update the timestamp if the referring domain is already in there. Possible? REPLACE INTO http://dev.mysql.com/doc/refman/5.0/en/replace.html -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Update or insert with a single SQL statement?
Or the more standard INSERT... ON DUPLICATE KEY UPDATE http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Shaunak Kashyap Senior Web Developer WPT Enterprises, Inc. 5700 Wilshire Blvd., Suite 350 Los Angeles, CA 90036 Direct: 323.330.9870 Main: 323.330.9900 www.worldpokertour.com Confidentiality Notice: This e-mail transmission (and/or the attachments accompanying) it may contain confidential information belonging to the sender which is protected. The information is intended only for the use of the intended recipient. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking of any action in reliance on the contents of this information is prohibited. If you have received this transmission in error, please notify the sender by reply e-mail and destroy all copies of this transmission. -Original Message- From: Greg Donald [mailto:[EMAIL PROTECTED] Sent: Friday, March 31, 2006 2:38 PM To: mysql@lists.mysql.com Subject: Re: Update or insert with a single SQL statement? On 3/31/06, Brian Dunning [EMAIL PROTECTED] wrote: I have a really simple two-column database: domain_name (primary key) timestamp I'm trying to keep track of the referrer of every visit to a web site, and I'm looking for a single SQL statement (since my ISP limits the total number of calls I can make in a day) that will either insert a new record if the referring domain is not already in there, or simply update the timestamp if the referring domain is already in there. Possible? REPLACE INTO http://dev.mysql.com/doc/refman/5.0/en/replace.html -- Greg Donald Zend Certified Engineer MySQL Core Certification http://destiney.com/ -- 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: TIMESTAMP field not automatically updating last_updated field
Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. Yes, I understand that one concept. I have seen it before If you do an update on a record but the actually values that you are passing in the statement are the exact values as were there before, no update to the timestamp field is made because none of the records values actually changed But no, that is not my situation. I've tested it and I am actually changing the values in the table (of course not specifying a new value for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update. What disturbes me is that it works fine in one particular table but all the others it works. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
Ferindo Middleton Jr wrote: Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. Yes, I understand that one concept. I have seen it before If you do an update on a record but the actually values that you are passing in the statement are the exact values as were there before, no update to the timestamp field is made because none of the records values actually changed But no, that is not my situation. I've tested it and I am actually changing the values in the table (of course not specifying a new value for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update. What disturbes me is that it works fine in one particular table but all the others it works. Ferindo I'm running 5.0.19-nt. I haven't had a chance to test it but should it make any difference if I say: last_updated TIMESTAMP, than if I say all this: last_updatedTIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, I think this may be the difference in why some tables are auto incrementing and others aren't. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tuning a Server with 10,000 databases
I have been offering free database hosting for over 4 years and I've been doing it on a shoestring.My last MySQL server was a generic 1GHz system with 256MB RAM running Redhat 9. The performance was surprisingly good because the query loads were not typically high. One persistent problem was the initial connection times. On that old system if I had less than approx 10,000 separate databases then the connection times were fast, and on the order of 1 second or so. If I had more than 10,000 databases this dramatically changed the connection times to well over 15 seconds or more. I always attributed this connection lag to a problem with the filesystem and the large number of directories. The old server had RH9 and ext3 with no htree support which I was told could help with this problem. I recently bought a new 2.4 GHz system with 1GB of RAM and installed Fedora 4 with ext3 and htree support. All new hardware, faster drives, more RAM and updated software. I thought I was golden!Well, I have 14,000 databases on this new system and it is as slow as the old 1GHz system. The tuning articles I've read, and the sample my-*.cnf files that ship with the tarball appear to apply to the more typical installation of a single huge database rather than thousands of individual dbs. Can anyone offer any suggestions? Thanks, Gary Huntress -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with 10,000 databases
At 09:27 PM 3/31/2006, you wrote: I have been offering free database hosting for over 4 years and I've been doing it on a shoestring.My last MySQL server was a generic 1GHz system with 256MB RAM running Redhat 9. The performance was surprisingly good because the query loads were not typically high. One persistent problem was the initial connection times. On that old system if I had less than approx 10,000 separate databases then the connection times were fast, and on the order of 1 second or so. If I had more than 10,000 databases this dramatically changed the connection times to well over 15 seconds or more. I always attributed this connection lag to a problem with the filesystem and the large number of directories. The old server had RH9 and ext3 with no htree support which I was told could help with this problem. I recently bought a new 2.4 GHz system with 1GB of RAM and installed Fedora 4 with ext3 and htree support. All new hardware, faster drives, more RAM and updated software. I thought I was golden!Well, I have 14,000 databases on this new system and it is as slow as the old 1GHz system. The tuning articles I've read, and the sample my-*.cnf files that ship with the tarball appear to apply to the more typical installation of a single huge database rather than thousands of individual dbs. Can anyone offer any suggestions? Thanks, Gary Huntress Gary, Just a guess, but could the problem be the 14,000 directories you have to store the 14,000 databases? The problem could be the OS directory structure. Putting the data into fewer databases will likely solve the problem or perhaps move half of the directories to another drive. 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: stunningly slow query
At 04:14 PM 3/31/2006, Chris Kantarjiev wrote: Are your logs and data on the same partition? That's a bad idea for recovering from a blown part of the disk, but we also saw that one of our databases would crash when there were lots of inserts/updates/replaces -- other databases, which had the same version of MySQL and operating system, had the logs and data on a separate partition, and they did not crash. It's a MyISAM table. Are there separate logs files? If so, where? I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on separate drives. We're investigating a possible MERGE organization. I'll report back if we learn anything new. Thanks, chris Chris, The problem with Load Data is the larger the table, the slower it gets because it has to keep updating the index during the loading process. If you use Load Data on an empty table is will be considerably faster because the indexes are built after all the data has been loaded. You could try removing the unique/primary index from the table you are using Load Data on just as a test to see if it speeds up. Also you can break the Load Data up into smaller number of rows, say files of 1000 rows each and try that. 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: stunningly slow query
Quote If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes. This feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier. /Quote Can you post your show create table tbl_name statement for these tables that involve slow queries? Do you have alot of indexes on these slow queries? If so, would using the above help? (may have been mentioned already) ALTER TABLE tbl_name DISABLE KEYS; your slow insert or update query here; ALTER TABLE tbl_name ENABLE KEYS; HTH Keith more - On Fri, 31 Mar 2006, Chris Kantarjiev wrote: To: [EMAIL PROTECTED], mysql@lists.mysql.com From: Chris Kantarjiev [EMAIL PROTECTED] Subject: Re: stunningly slow query Are your logs and data on the same partition? That's a bad idea for recovering from a blown part of the disk, but we also saw that one of our databases would crash when there were lots of inserts/updates/replaces -- other databases, which had the same version of MySQL and operating system, had the logs and data on a separate partition, and they did not crash. It's a MyISAM table. Are there separate logs files? If so, where? I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on separate drives. Log files usually default to the mysql data directory, eg. /var/lib/mysql/ Putting the database files on seperate drives may slow things down alot too - unless others know better. .frm is the database definition file. .MYI is the index file, and .MYD is the data file. There is one each of these files for each myisam table in the database. I may be wrong, but I would have thought it better if these are all together on the same disk and partition for each table in the database? We're investigating a possible MERGE organization. I'll report back if we learn anything new. Thanks, chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with 10,000 databases
mos wrote: At 09:27 PM 3/31/2006, you wrote: I have been offering free database hosting for over 4 years and I've been doing it on a shoestring.My last MySQL server was a generic 1GHz system with 256MB RAM running Redhat 9. The performance was surprisingly good because the query loads were not typically high. One persistent problem was the initial connection times. On that old system if I had less than approx 10,000 separate databases then the connection times were fast, and on the order of 1 second or so. If I had more than 10,000 databases this dramatically changed the connection times to well over 15 seconds or more. I always attributed this connection lag to a problem with the filesystem and the large number of directories. The old server had RH9 and ext3 with no htree support which I was told could help with this problem. I recently bought a new 2.4 GHz system with 1GB of RAM and installed Fedora 4 with ext3 and htree support. All new hardware, faster drives, more RAM and updated software. I thought I was golden! Well, I have 14,000 databases on this new system and it is as slow as the old 1GHz system. The tuning articles I've read, and the sample my-*.cnf files that ship with the tarball appear to apply to the more typical installation of a single huge database rather than thousands of individual dbs. Can anyone offer any suggestions? Thanks, Gary Huntress Gary, Just a guess, but could the problem be the 14,000 directories you have to store the 14,000 databases? The problem could be the OS directory structure. Putting the data into fewer databases will likely solve the problem or perhaps move half of the directories to another drive. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi Gary, I think that Mike may have hit the nail on the head. I've a few unix directories with multiple thousand files and they do become a bit of a problem to manage speedwise. Perhaps, as Mike has suggested, place half of them on another drive. The other option could be to run multiple instances of MySQL, each having a different port number (this could be based on username or something similar) eg. A-D port 3306, E-H 3307, etc. and reducing the number of dbs per instance (server) that way. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~how to add a new innodb data file~
Mohammed, http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html If your last data file was defined with the keyword autoextend, the procedure for reconfiguring the tablespace must take into account the size to which the last data file has grown. Obtain the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify the rounded size explicitly in innodb_data_file_path. Then you can add another data file. Remember that only the last data file in the innodb_data_file_path can be specified as auto-extending. The correct way to get the size of a file is the 'ls -l' command. I think the 'du' command measures the physical size required for the file on the disk, and it can differ from the size of the file as seen by mysqld. 463470592 / (1024 * 1024) = 442. InnoDB measures the file size in MB, where MB is defined as 1024 * 1024 bytes. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Mohammed Abdul Azeem [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 30, 2006 10:03 AM Subject: ~how to add a new innodb data file~ Hi, Iam running a mysql server 5.0.15 over Redhat linux es4. My disk space has exhausted. so i need to add a new ibdata file to my /etc/my.cnf configuration. I followed the following procedure to do so. 1. I checked the ibdata1 file size. when i do a du -sh ibdata1 , i get the size to be 443M when i do a ls -ltr ibdata1 , i get the size to be 463470592 bytes 2. I edited my /etc/my.cnf to add the following: innodb_data_file_path = /mysql-system/mysql/data/ibdata1:443M;/mysql- system2/ibdata2:50M:autoextend i got the following error: 060330 01:48:42 mysqld started InnoDB: Error: data file /mysql-system/mysql/data/ibdata1 is of a different size InnoDB: 28288 pages (rounded down to MB) InnoDB: than specified in the .cnf file 28416 pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 060330 1:48:42 [Note] /mysql-system/mysql/bin/mysqld: ready for connections. Version: '5.0.15-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) 3. Then i tried adding the value from 'ls -ltr ibdata1' which is 463470592 bytes. I rounded the same to 464M . but still got the same error. Can anyone help me out on how to go about the same. Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- 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]
