Server/Client connection compression
Hi, I saw that to enable server/client protocol compression I can start mysql with the -C option. Is there a configuration keyword to write in my.cnf to enable server/client protocol compression? Thanks Giorgio Zarrelli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Endianness
I am an experienced Oracle DBA, but new to MySQL. I have been tasked with moving a number of MySQL 4.1 servers from a Solaris 7 SPARC environment to Solaris 10 x86. The most significant difference between these environments is the endianness of the hardware. My question is - Which of the MySQL file formats are independent of endianness, so they can be copied between the two environments, and which are not. The categories of file I am interested in include: * MyISAM files * InnoDB files * Binary logs * mysqldump output -- Charles Jardine - Computing Service, University of Cambridge [EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1.19-beta has been released
Dear MySQL users, We are proud to present to you the MySQL Server 5.1.19 Beta release, a new Beta version of the popular open source database. Bear in mind that this is a beta release, and as any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. For production level systems using 5.0, pay attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ The MySQL 5.1.19 Beta release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing The following section lists the changes from version to version in the MySQL source code since the latest released version of MySQL 5.1, the MySQL 5.1.18-beta. It can also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-19.html Note: The following problem with NDB (cluster) was detected only after building this release: * Bug#28783 may occur during high load ( http://bugs.mysql.com/28783 ) The bug will manifest itself by a ndbd node missing heartbeats and consequently shutting down. The release should function well under normal operation and may be used for evaluation purposes, however is not suited for high load testing, as it is likely to hit this bug. If you wish to evaluate cluster in a high load situation, please use 5.1.18 or wait for 5.1.20. Functionality added or changed: * INSERT DELAYED statements for BLACKHOLE tables caused a server crash. The BLACKHOLE storage engine now supports INSERT DELAYED. (Bug#27998: http://bugs.mysql.com/27998) * The BLACKHOLE storage engine now supports LOCK TABLES and UNLOCK TABLES. (Bug#26241: http://bugs.mysql.com/26241) * The data type used for the VARIABLE_VALUE column of the following INFORMATION_SCHEMA tables has been changed to VARCHAR: + GLOBAL_STATUS + SESSION_STATUS + GLOBAL_VARIABLES + SESSION_VARIABLES For more information, see Section 22.24, The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables, Section 22.25, The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables, and Bug#26994: http://bugs.mysql.com/26994 Bugs fixed: * Security fix: Use of a view could allow a user to gain update privileges for tables in other databases. (Bug#27878: http://bugs.mysql.com/27878) * Security fix: UDFs are supposed to be loadable only from the plugin directory, but this restriction was not being enforced. (Bug#28341: http://bugs.mysql.com/28341) * NDB Cluster: When an API node sent more than 1024 signals in a single batch, NDB would process only the first 1024 of these, and then hang. (Bug#28443: http://bugs.mysql.com/28443) * NDB Cluster (Disk Data): DDL operations were not supported on a partially started cluster. (Bug#24631: http://bugs.mysql.com/24631) * NDB Cluster: A delay in obtaining AUTO_INCREMENT IDs could lead to excess temporary errors. (Bug#28410: http://bugs.mysql.com/28410) * NDB Cluster: Local checkpoint files related to dropped NDB tables were not removed. (Bug#28348: http://bugs.mysql.com/28348) * NDB Cluster: A failure to release internal resources following an error could lead to problems with single user mode. (Bug#25818: http://bugs.mysql.com/25818) * NDB Cluster: Multiple operations involving deletes followed by reads were not handled correctly. (Bug#28276: http://bugs.mysql.com/28276) Note: This issue could also affect MySQL Cluster Replication. * NDB Cluster (Disk Data): Extremely large inserts into Disk Data tables could lead to data node failure in some circumstances. (Bug#27942: http://bugs.mysql.com/27942) * NDB Cluster: Repeated insertion of data generated by mysqldump into NDB tables could eventually lead to failure of the cluster. (Bug#27437: http://bugs.mysql.com/27437) * NDB Cluster: Restarting a data node caused SQL nodes to log repeatedly and unnecessarily the status of the event buffer. (Bug#27292: http://bugs.mysql.com/27292) (This issue was known to occur in MySQL 5.1.16 and later only.) * NDB Cluster: ndb_mgmd failed silently when the cluster configuration file contained invalid [TCP] entries. (Bug#27207: http://bugs.mysql.com/27207) * NDB Cluster: ndb_connectstring did not appear in the output of SHOW VARIABLES. (Bug#26675: http://bugs.mysql.com/26675) * NDB Cluster (APIs): In a multi-operation transaction, a delete operation followed by the insertion of an implicit NULL failed to overwrite
error while doing a select
Hi All, We are using mysql version 5.0.40. We took a snapshot of the our database, and copied over the snapshot to anther machine and start mysql on the new snapshot. Its innodb engine when i treid to select from one of the tables i am getting this error, how do i fix this ,please help select count(*) from auth; ERROR 1033 (HY000): Incorrect information in file: './auth/auth.frm regards anandkl
Re: error while doing a select
Hi, Ananda Kumar wrote: Hi All, We are using mysql version 5.0.40. We took a snapshot of the our database, and copied over the snapshot to anther machine and start mysql on the new snapshot. Its innodb engine when i treid to select from one of the tables i am getting this error, how do i fix this ,please help select count(*) from auth; ERROR 1033 (HY000): Incorrect information in file: './auth/auth.frm You should read the entire manual chapter on InnoDB before going further: http://dev.mysql.com/doc/refman/5.0/en/innodb.html You should read the sections on errors, troubleshooting, and startup as many times as necessary. Make sure you understand InnoDB tablespaces, log files, and so on. Then I think you will not have any more troubles :-) Regards, Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server/Client connection compression
Hi, Giorgio Zarrelli wrote: Hi, I saw that to enable server/client protocol compression I can start mysql with the -C option. Is there a configuration keyword to write in my.cnf to enable server/client protocol compression? Yes. In general, most command-line options can be written into the options files (and dashes and underscores are interchangeable by the way, so you will see people referring to both option-name=val and option_name=val). For example, if I add a line compress to the [mysql] section in /home/baron/.my.cnf, and then connect and type 'status', I see a line in the output that says Protocol: Compressed That line is not there unless compression is enabled. I could add the same option to various sections in /etc/my.cnf as well; probably the best place to put it is in the [client] section. Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Find and Add Unmatched Records
I have a table of middle school students I want to update quarterly. I am only interested in finding and adding new students (not in finding and deleting students who have been dropped from the school district). I'm pretty sure I could put together a PHP script that would do this, but is there a MySQL way for one MySQL table (preceding quarter) to scan another MySQL table (current quarter) by primary key (student id number) and absorb the unmatched, new students? I'm thinking that if f I could bypass PHP, I could use MySQL triggers to automatically add new records to other, related tables.
RE: Find and Add Unmatched Records
The REPLACE staement might work for you... http://dev.mysql.com/doc/refman/4.1/en/replace.html... -Original Message- From: Kebbel, John [mailto:[EMAIL PROTECTED] Sent: 01 June 2007 15:31 To: mysql@lists.mysql.com Subject: Find and Add Unmatched Records I have a table of middle school students I want to update quarterly. I am only interested in finding and adding new students (not in finding and deleting students who have been dropped from the school district). I'm pretty sure I could put together a PHP script that would do this, but is there a MySQL way for one MySQL table (preceding quarter) to scan another MySQL table (current quarter) by primary key (student id number) and absorb the unmatched, new students? I'm thinking that if f I could bypass PHP, I could use MySQL triggers to automatically add new records to other, related tables. This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find and Add Unmatched Records
Hi John, Kebbel, John wrote: I have a table of middle school students I want to update quarterly. I am only interested in finding and adding new students (not in finding and deleting students who have been dropped from the school district). I'm pretty sure I could put together a PHP script that would do this, but is there a MySQL way for one MySQL table (preceding quarter) to scan another MySQL table (current quarter) by primary key (student id number) and absorb the unmatched, new students? I'm thinking that if f I could bypass PHP, I could use MySQL triggers to automatically add new records to other, related tables. It sounds to me like you want an exclusion join: insert into new_students(...) select current_quarter.* from current_quarter left outer join preceding_quarter using(student_id) where preceding_quarter.student_id is null; Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Find and Add Unmatched Records
Same situation, but let's say it's not preceding_quarter and current_quarter for the table names. To make the flow of records more clear, let's say the one I was calling preceding_quarter is now called keepers and the one I called current_quarter is now called pickThroughThenDelete. I'll delete the pickThroughThenDelete table after I've found and transferred the new students. Would this syntax work? insert into keepers select pickThroughThenDelete.* from pickThroughThenDelete left outer join keepers using(student_id) where keepers.student_id is null; drop table pickThroughThenDelete; -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, June 01, 2007 10:35 AM To: Kebbel, John Cc: mysql@lists.mysql.com Subject: Re: Find and Add Unmatched Records Hi John, Kebbel, John wrote: I have a table of middle school students I want to update quarterly. I am only interested in finding and adding new students (not in finding and deleting students who have been dropped from the school district). I'm pretty sure I could put together a PHP script that would do this, but is there a MySQL way for one MySQL table (preceding quarter) to scan another MySQL table (current quarter) by primary key (student id number) and absorb the unmatched, new students? I'm thinking that if f I could bypass PHP, I could use MySQL triggers to automatically add new records to other, related tables. It sounds to me like you want an exclusion join: insert into new_students(...) select current_quarter.* from current_quarter left outer join preceding_quarter using(student_id) where preceding_quarter.student_id is null; Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data migration Verification
Hi all, I have somebody creating a C# class to migrate data from one SQL database to another slightly differently structured SQL database. Please can anybody suggest ways to verrify that the data has migrated successfully, in whole and acurrate?? I feel a sample approach would not quite be substancial. I want to keep it seperate from the migration process itself (having my person write a verification script may also not work as he will be using the same thought processes and knowledge that he used for the migration) Free Software, scripts, utilities, packages, industry approaches?? Sorry Im no Tech wizzard, Any ideas appreciated. -- View this message in context: http://www.nabble.com/Data-migration-Verification-tf3852626.html#a10913962 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data migration Verification
Besides the sample approach, output data (a set you would output on a live system anyway) from both db setups and see if you can get the same output from both Olaf On 6/1/07 10:35 AM, paulizaz [EMAIL PROTECTED] wrote: Hi all, I have somebody creating a C# class to migrate data from one SQL database to another slightly differently structured SQL database. Please can anybody suggest ways to verrify that the data has migrated successfully, in whole and acurrate?? I feel a sample approach would not quite be substancial. I want to keep it seperate from the migration process itself (having my person write a verification script may also not work as he will be using the same thought processes and knowledge that he used for the migration) Free Software, scripts, utilities, packages, industry approaches?? Sorry Im no Tech wizzard, Any ideas appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find and Add Unmatched Records
Hi John, Yes, that looks right to me. Baron Kebbel, John wrote: Same situation, but let's say it's not preceding_quarter and current_quarter for the table names. To make the flow of records more clear, let's say the one I was calling preceding_quarter is now called keepers and the one I called current_quarter is now called pickThroughThenDelete. I'll delete the pickThroughThenDelete table after I've found and transferred the new students. Would this syntax work? insert into keepers select pickThroughThenDelete.* from pickThroughThenDelete left outer join keepers using(student_id) where keepers.student_id is null; drop table pickThroughThenDelete; -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, June 01, 2007 10:35 AM To: Kebbel, John Cc: mysql@lists.mysql.com Subject: Re: Find and Add Unmatched Records Hi John, Kebbel, John wrote: I have a table of middle school students I want to update quarterly. I am only interested in finding and adding new students (not in finding and deleting students who have been dropped from the school district). I'm pretty sure I could put together a PHP script that would do this, but is there a MySQL way for one MySQL table (preceding quarter) to scan another MySQL table (current quarter) by primary key (student id number) and absorb the unmatched, new students? I'm thinking that if f I could bypass PHP, I could use MySQL triggers to automatically add new records to other, related tables. It sounds to me like you want an exclusion join: insert into new_students(...) select current_quarter.* from current_quarter left outer join preceding_quarter using(student_id) where preceding_quarter.student_id is null; Baron -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exporting blob / text column with hyphen delimited text
Hi all! I have a table that contains a column (blob, text type, formatted as MEDIUMTEXT) with text hyphen (-) delimited, listing rooms by numbers, and I need to export or insert that data into another table, where all other info on the other columns remains the same, but I need the data on the blob field to be single listed another words, i have a table like this (example): School Rooms Your Hometown High School 1034-1035-1037-1039 My Hometown High School 208-178-1432-1728 I need it like this: School Rooms Your Hometown High School1034 Your Hometown High School1035 Your Hometown High School1037 Your Hometown High School1039 My Hometown High School 208 My Hometown High School 178 My Hometown High School 1432 My Hometown High School 1728 Suggestions? Thanks! -- View this message in context: http://www.nabble.com/Exporting-blob---text-column-with-hyphen-delimited-text-tf3854590.html#a10920903 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using the last_insert_id() function
Hi everyone,
I¹m trying to figure out the best sequence to enter data into my database
(the SQL code for the structure is included below). If I have a number of
tab delimited .txt files containing the data for the different tables, I
thought as a first step I could use the following code to populate the
garments table:
LOAD DATA INFILE 'garments.txt'
INTO TABLE garments
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r'
I also thought I could use the same code to insert the data into the
colours, sizes and categories tables. My problem starts when I have to try
to enter the correct garment_id from the garments table into the correct
foreign key fields in the garments_to_colour, garments_to_sizes and
garments_to_categories look up tables. Do I need to do this manually or can
I use the last_insert_id() function? But not sure if this would work since
I¹m not populating the garments table one row at a time I¹m inserting all
info in one go with LOAD DATA INFILE, as mentioned above.
Just wondered if someone could help solve this problem?
CREATE TABLE `garments` (
`garment_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier` varchar(30) NOT NULL,
`garment_type` varchar(30) NOT NULL,
`title` varchar(60) NOT NULL,
`code` varchar(20) NOT NULL,
`description` varchar(400) NOT NULL,
`extra_info` varchar(50) default NULL,
`image` enum('y','n') NOT NULL,
`swatch_image` enum('y','n') NOT NULL,
PRIMARY KEY (`garment_id`),
UNIQUE KEY `supplier` (`supplier`,`garment_type`, `description`,
`title`,`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `colours` (
`colour_id` smallint(5) unsigned NOT NULL auto_increment,
`colour` varchar(20) NOT NULL,
PRIMARY KEY (`colour_id`),
UNIQUE KEY `colour` (`colour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_colour` (
`garment_id` smallint(5) unsigned NOT NULL,
`colour_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`colour_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `sizes` (
`size_id` smallint(5) unsigned NOT NULL auto_increment,
`size` varchar(15) NOT NULL,
PRIMARY KEY (`size_id`),
UNIQUE KEY `size` (`size`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_size` (
`garment_id` smallint(5) unsigned NOT NULL,
`size_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`size_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `categories` (
`cat_id` smallint(5) unsigned NOT NULL auto_increment,
`category` varchar(30) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_category` (
`garment_id` smallint(5) unsigned NOT NULL,
`cat_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
