Re: this listserv function...?
Jim Winstead wrote: This is addressed in the FAQ for the mailing lists. http://lists.mysql.com/faq.php#replyto Jim Winstead MySQL Inc. Times and technology change. Take a look at the changes to MySQL server and the internet in general since 2000. That's all I'm going to say as there are far too many other arguments against this method of delivery for a mailing list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (瑞星提示-此邮件可能是垃圾邮件)Re: insert utf8 character in Linux commind-line tool
On Tuesday 20 December 2005 03:10, wangxu wrote: Are there two option? How to use them? These are just two options which you will have to use when ./configure'ing the source distribution if you are using a binary distribution of mysql try downloading the source distribution... then read http://dev.mysql.com/doc/refman/4.1/en/installing-source.html and add the two options to the configure. I can't operate utf8 characters within command-line in linux operating system. Mysql doesn't support? --without-libedit –with-readline=/usr/include/readline -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: invalidation rollback?!
Hi everybody! Martijn Tonies wrote: I execute follow procedure: --- create procedure test_transation_rollback() begin start transaction; create table t_34d (c1 int,c2 char(30)); rollback; end After execute this procedure,i find the table t_34d in my schema.Why? Metadata transactions are not supported in MySQL. Right. Most systems call it DML (data manipulation language: insert, update, select, and delete commands) versus DDL (data definition language: create/alter/drop table/view, grant/revoke, ... commands), but the result is the same: Typically, transactions only cover DML commands, whereas for DDL often an implicit autocommit applies. Some systems even implicitly commit DML commands when executing a following DDL. As a consequence, the recommendation is not to try to mix DML and DDL within one transaction, and to issue a commit immediately after any DDL command. In this way, effects on all systems should be identical. Every other style of application programming may be specific to one DBMS, and requires detailed checking against the manual. HTH, Jörg Brühe -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with installation mysql 5.0.16
Good Day ! When installing mysql 5.0.16 on FreeBSD 5.3, after unpacking of the archive and attempt to execute скрипт: scripts/mysql_install_db the system gives the mistake: my_print_defaults: not found Could not find help 'fill_help_tables.sql' in ./support-files or inside .. -- in than problem? Is it Beforehand thanked -- mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication: why does this query fail on the slave?
Dear list, Replication (between two 4.1.10 servers, Solaris) fails on the following query with a 1064 error (parse error), but there are no reserved words in it, and when I run the query from the command line on the master no errors occur and the table is created. (binlog) /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 30963303 #051220 10:51:00 server id 30 log_pos 30963303 Query thread_id=17311 exec_time=0 error_code=0 use sip; SET TIMESTAMP=1135072260; DROP TABLE IF EXISTS `tbl_ou`; CREATE TABLE `tbl_ou` ( `intId` int(11) NOT NULL auto_increment, `vchName` varchar(100) NOT NULL default '', `vchDescription` varchar(255) default NULL, `intParent` int(11) default NULL, `intType` int(11) NOT NULL default '0', `tinStatus` tinyint(3) NOT NULL default '1', PRIMARY KEY (`intId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Orginisational Units'; # # Dumping data for table tbl_ou # [snip] Output from SHOW SLAVE STATUS: [...] Last_Errno: 1064 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; CREATE TABLE `tbl_ou` ( `intId` int(11) NOT NULL auto_increment, `vchNa' at line 1' on query. Default database: 'sip'. Query: 'DROP TABLE IF EXISTS `tbl_ou`; CREATE TABLE `tbl_ou` ( `intId` int(11) NOT NULL auto_increment, `vchName` varchar(100) NOT NULL default '', `vchDescription` varchar(255) default NULL, `intParent` int(11) default NULL, `intType` int(11) NOT NULL default '0', `tinStatus` tinyint(3) NOT NULL default '1', PRIMARY KEY (`intId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Orginisational Units'; Where to look, how to fix? It looks like a bug to me, if so I'll add it to the buglist. Kind regards, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem in starting Mysqld
Hi, I've a freshly installed linux Box (Fedora Core 4) and I'm trying to install a little mysql server. I've download the following linux RPM and installed the via rpm -Uvh: MySQL-client-5.0.17-0.glibc23.i386.rpm MySQL-server-5.0.17-0.glibc23.i386.rpm If i launch the server via the following command it run ok: [EMAIL PROTECTED] ~]# mysqld_safe [1] 3906 [EMAIL PROTECTED] ~]# Starting mysqld daemon with databases from /var/lib/mysql If I try to launch it via /etc/init.d/mysql it gives me an OK but after a few seconds the daemon stop: [EMAIL PROTECTED] ~]# /etc/init.d/mysql start Starting MySQL [ OK ] [EMAIL PROTECTED] ~]# ps -fe |grep -i mysql mysql 4718 1 0 11:47 pts/000:00:00 /usr/sbin/mysqlmanager --user=mysql --pid-file=/var/lib/mysql/mysqlmanager-localhost.pid mysql 4851 1 0 11:49 pts/000:00:00 /usr/sbin/mysqlmanager --user=mysql --pid-file=/var/lib/mysql/mysqlmanager-localhost.pid root 4863 2654 0 11:49 pts/000:00:00 grep -i mysql [EMAIL PROTECTED] ~]# ps -fe |grep -i mysql mysql 4718 1 0 11:47 pts/000:00:00 /usr/sbin/mysqlmanager --user=mysql --pid-file=/var/lib/mysql/mysqlmanager-localhost.pid root 4867 2654 0 11:49 pts/000:00:00 grep -i mysql I've tried to configure the startup script to use mysqld_safe instead of mysqlmanager: use_mysqld_safe=1 [EMAIL PROTECTED] ~]# /etc/init.d/mysql start Starting MySQL... [FAILED] and in the localhost.err i find: 051220 11:44:25 mysqld started 051220 11:44:28 mysqld ended What am I doing wrong ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow sorting
I have a table that contains around 400,000 simple names. It's displaying a subset of them (perhaps 5,000) them a page at a time in a web interface, sorted by name, so I have a query like this: SELECT * FROM names WHERE account=123 ORDER BY lastname, firstname LIMIT 0,30 That takes 11 seconds to run. Without the order by it takes 0.13 sec. I have simple indexes on both first name and last name (they are sometimes searched separately). It strikes me that this is really very slow - it really doesn't have much to sort. I tied doing an explain, and though I could see that it was using the indexes, it was also saying use where, use temporary, use filesort. Why is it falling back to these methods? How can I make this faster? A DBA friend recommended using clustered indexes - does MySQL have such things? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow sorting
Hi Marcus, Do you have the output of the EXPLAIN? I've found the number of rows processed to be very instructional. If you could get this number down, it may be well worth putting an index on the account column and re-running the EXPLAIN to see how many rows are being processed. I recently put an index on a table that was doing a filesort around 50 times for each web page. It was processing 58800 records each sort x 50 made for a very slow web page. I put an index in place, it still does the filesort but is only processing 154 rows each time and this is probably being done in memory as it is so tiny. Would be worth a punt. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Marcus Bointon [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 December 2005 9:55 PM To: mysql@lists.mysql.com Subject: Slow sorting I have a table that contains around 400,000 simple names. It's displaying a subset of them (perhaps 5,000) them a page at a time in a web interface, sorted by name, so I have a query like this: SELECT * FROM names WHERE account=123 ORDER BY lastname, firstname LIMIT 0,30 That takes 11 seconds to run. Without the order by it takes 0.13 sec. I have simple indexes on both first name and last name (they are sometimes searched separately). It strikes me that this is really very slow - it really doesn't have much to sort. I tied doing an explain, and though I could see that it was using the indexes, it was also saying use where, use temporary, use filesort. Why is it falling back to these methods? How can I make this faster? A DBA friend recommended using clustered indexes - does MySQL have such things? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- 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: Slow sorting
-Original Message- From: Marcus Bointon [EMAIL PROTECTED] [SNIPPED] That takes 11 seconds to run. Without the order by it takes 0.13 sec. I have simple indexes on both first name and last name (they are sometimes searched separately). It strikes me that this is really very slow - it really doesn't have much to sort. I tied doing an explain, and though I could see that it was using the indexes, it was also saying use where, use temporary, use filesort. Why is it falling back to these methods? How can I make this faster? Think you will find, should you study the query carefully, that in fact poor MySQL needs to query and actually sort all 400,000 records (was it not for the account id), before it can give you your batch of 30. If MySQL does not have enough ram allocated, may this even entail disk swapping for a NxMb table. This is of course the case because MySQL can not possibly give you the top 30, without first having to use the pertinent index to sort all of them. Indexes on the name and surname may also be a deathshot rather than a blessing, as these indexes would be almost as big as the original table. So instead of simply loading and sorting through one file, MySQL now has to do it with two equally big files. Dare I suggest the following: 1. Remove your name and surname indexes. 2. Ensure you have an index on the account column. 3. Insert a new composite column into the table which is of fixed width (CHAR) and at most 4/5 characters wide. Now populate this column with the first 2/3 characters of the surname and first 2 of the name, index this column and rather sort by it. (You can obviously change the containing data's permutation as you like, ex. first 4 of the surname, etc. 4. Also ensure MySQL has enough ram allocated (see show variables) such that it can load the complete table index in RAM (if possible, even the table's data), so that no disk/virtual mem swapping takes place. I think you will find MySQL much snappier with this source data, as it will first filter the data quickly by account and ordering of the subset should be very quick using only a 4 char column index. Chances are also good that if the first 4 chars of a surname match, the surnames are most likely identical (granted, the odd one will be sorted below rather than above it's actual position), but in the end is it a balance between speed and the odd mis-ordered record. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication: why does this query fail on the slave?
Hi, Disregard my previous post. It seems the developer is doing stuff that creates duplicate key errors, which seem to propagate to the slave, in stead of being ignored. -- Martijn -Original Message- From: Martijn van den Burg [mailto:[EMAIL PROTECTED] Sent: dinsdag 20 december 2005 11:38 To: mysql@lists.mysql.com Subject: Replication: why does this query fail on the slave? Dear list, Replication (between two 4.1.10 servers, Solaris) fails on the following query with a 1064 error (parse error), but there are no reserved words in it, and when I run the query from the command line on the master no errors occur and the table is created. (binlog) /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 30963303 #051220 10:51:00 server id 30 log_pos 30963303 Query thread_id=17311 exec_time=0 error_code=0 use sip; SET TIMESTAMP=1135072260; DROP TABLE IF EXISTS `tbl_ou`; CREATE TABLE `tbl_ou` ( `intId` int(11) NOT NULL auto_increment, `vchName` varchar(100) NOT NULL default '', `vchDescription` varchar(255) default NULL, `intParent` int(11) default NULL, `intType` int(11) NOT NULL default '0', `tinStatus` tinyint(3) NOT NULL default '1', PRIMARY KEY (`intId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Orginisational Units'; # # Dumping data for table tbl_ou # [snip] Output from SHOW SLAVE STATUS: [...] Last_Errno: 1064 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; CREATE TABLE `tbl_ou` ( `intId` int(11) NOT NULL auto_increment, `vchNa' at line 1' on query. Default database: 'sip'. Query: 'DROP TABLE IF EXISTS `tbl_ou`; CREATE TABLE `tbl_ou` ( `intId` int(11) NOT NULL auto_increment, `vchName` varchar(100) NOT NULL default '', `vchDescription` varchar(255) default NULL, `intParent` int(11) default NULL, `intType` int(11) NOT NULL default '0', `tinStatus` tinyint(3) NOT NULL default '1', PRIMARY KEY (`intId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Orginisational Units'; Where to look, how to fix? It looks like a bug to me, if so I'll add it to the buglist. Kind regards, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect key file for table '%-.64s'
On 12/17/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. and slave. Error on master: 'Incorrect key file for table '%-.64s Really, the name of the table is strange enough. Is it possible that you have non-latin symbols in table name, which are displayed incorrectly on your terminal? The tables names are using latin carachters without exception, i checked the tables but no error was there. I have to SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; to skip THAT query. Thanks for the help. Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date index question
Hello People, I have some indexing problem on using the fieldtype 'date' as restriction in a query. I use MySQL Server version: 4.0.20 The table I have consists of roughly over 200.000 rows about 37 fields and it looks sort of like this: mysql desc the_table; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | theID | int(11) | | PRI | NULL| auto_increment | | contract | varchar(20) | | MUL | || ... some varchars ... | rate1 | double(10,2) | | | 0.00 || ... twenty other doubles ... | routing| varchar(100) | | | || | cNotes | text | | | || | tValidfrom | date | | MUL | -00-00 || | tValidto | date | | MUL | -00-00 || | iStatus| int(11) | | | 0 || | iEnteredby | int(11) | | | 0 || | tEntered | datetime | | | -00-00 00:00:00 || | iUpdatedby | int(11) | | | 0 || | tUpdated | datetime | | | -00-00 00:00:00 || ++--+--+-+-++ 37 rows in set (0.00 sec) I then run this to create the index I want. mysql CREATE INDEX IX_test ON the_table(tValidfrom,tValidto); Query OK, 204657 rows affected (1 min 6.08 sec) Records: 204657 Duplicates: 0 Warnings: 0 The created index shows this info: Table: the_table Non_unique: 1 Key_name: IX_test Seq_in_index: 1 Column_name: tValidfrom Collation: A Cardinality: 75 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Table: the_table Non_unique: 1 Key_name: IX_test Seq_in_index: 2 Column_name: tValidto Collation: A Cardinality: 131 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 27 rows in set (0.00 sec) So now I want to take advantage of this index, but my query is still slow: mysql SELECT DISTINCT cOrigin from the_table WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now(); | USBDA | | USIND | | USHSV | +-+ 402 rows in set (2.80 sec) Then I do an explain to try to find out if it is using my index. mysql explain SELECT DISTINCT cOrigin from the_table WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now() \G; *** 1. row *** table: the_table type: ALL possible_keys: IX_test key: NULL key_len: NULL ref: NULL rows: 204657 Extra: Using where; Using temporary 1 row in set (0.00 sec) So it's not even using my index =( And if I force it: mysql SELECT DISTINCT cOrigin from the_table FORCE INDEX (IX_test) WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now(); . . | IDPJG | | JPSHI | | INICD | | CNJIU | | USHSV | +-+ 402 rows in set (4.27 sec) It is even slower. I have done a check table, analyze table etc. If you have any suggestions please let me know, thanks for your precious time! Best Regards, Mattias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert utf8 character in Linux commind-line tool
Hello. Please, provide the output of this statement: show variables like '%char%'; I import utf8 character in terminal commind-line is well.So in my word,there aren't error in my terminal settings. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem in starting Mysqld
Hello. Are you able to start mysqld without using /etc/init.d/mysql, manually invoking mysqld_safe or mysqld? From my experience, SELinux can cause different problems, check if you have it enabled. Follow general recommendations from: http://dev.mysql.com/doc/refman/5.0/en/starting-server.html dogbert wrote: Hi, I've a freshly installed linux Box (Fedora Core 4) and I'm trying to install a little mysql server. I've download the following linux RPM and installed the via rpm -Uvh: MySQL-client-5.0.17-0.glibc23.i386.rpm MySQL-server-5.0.17-0.glibc23.i386.rpm If i launch the server via the following command it run ok: [EMAIL PROTECTED] ~]# mysqld_safe [1] 3906 [EMAIL PROTECTED] ~]# Starting mysqld daemon with databases from /var/lib/mysql If I try to launch it via /etc/init.d/mysql it gives me an OK but after a few seconds the daemon stop: [EMAIL PROTECTED] ~]# /etc/init.d/mysql start Starting MySQL [ OK ] [EMAIL PROTECTED] ~]# ps -fe |grep -i mysql mysql 4718 1 0 11:47 pts/000:00:00 /usr/sbin/mysqlmanager --user=mysql --pid-file=/var/lib/mysql/mysqlmanager-localhost.pid mysql 4851 1 0 11:49 pts/000:00:00 /usr/sbin/mysqlmanager --user=mysql --pid-file=/var/lib/mysql/mysqlmanager-localhost.pid root 4863 2654 0 11:49 pts/000:00:00 grep -i mysql [EMAIL PROTECTED] ~]# ps -fe |grep -i mysql mysql 4718 1 0 11:47 pts/000:00:00 /usr/sbin/mysqlmanager --user=mysql --pid-file=/var/lib/mysql/mysqlmanager-localhost.pid root 4867 2654 0 11:49 pts/000:00:00 grep -i mysql I've tried to configure the startup script to use mysqld_safe instead of mysqlmanager: use_mysqld_safe=1 [EMAIL PROTECTED] ~]# /etc/init.d/mysql start Starting MySQL... [FAILED] and in the localhost.err i find: 051220 11:44:25 mysqld started 051220 11:44:28 mysqld ended What am I doing wrong ? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: locating ibdata1 and *.ibd files in different directories.
Hello. Please, next time answer to the list as well. As far as I know, you can't specify the location of ibd files, they're stored in the database directory, however, you can use symbolic links for databases to have them in another place. all databases? So the question is if I can locate the ibdata1 file somewhere else. Have you tried just to change the value of innodb_home_dir to the new location, and move there ibdata1 file? ibd files. BUT, the ibdata1 file is still 7 gig and being If you want do decrease the size of ibdata1 file, you should dump all your InnoDB tables, stop the server, remove all existing tablespace files, configure a new tablespace, restart the server, import the dump files. In such a way you'll move all your tables which are in ibdata1 tablespace to may ibd files in databases. See: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html I don't understand. I want some ibd files in a different dir. Not in a dir below the home dir. Actually what is the 'home' dir? The root of all databases? So the question is if I can locate the ibdata1 file somewhere else. Reason being that *after* my ibdata1 file was about 7Gig, I changed to a per file tablespace, and mysql copied all the data into individual ibd files. BUT, the ibdata1 file is still 7 gig and being searched/seeked/written to. I want to move this file to another partition (Linux) in order to split the disk load and free up some disk space. Thank you much; Nathan Gross wrote: Hi; Mysql 4x on Linux Fc4. In /var/lib/mysql there is an ibdata1 file. Also in this directory I also have various database folders, with *.ibd data files. I would like to locate the ibdata1 somewhere else, say, /usr/extraData/mysql. I am confused as how to set *innodb_data_file_path* as well as *innodb_data_home_dir*. Please help. Thanks; -nat -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication: why does this query fail on the slave?
Hello. That seems like a bug for me. Please, check if the problem still exists if you're using 4.1.16 master and slave. Martijn van den Burg wrote: Dear list, Replication (between two 4.1.10 servers, Solaris) fails on the following query with a 1064 error (parse error), but there are no reserved words in it, and when I run the query from the command line on the master no errors occur and the table is created. (binlog) /*!40019 SET @@session.max_insert_delayed_threads=3D0*/; # at 30963303 #051220 10:51:00 server id 30 log_pos 30963303 Query thread_id=3D17311 exec_time=3D0 error_code=3D0 use sip; SET TIMESTAMP=3D1135072260; DROP TABLE IF EXISTS `tbl_ou`; CREATE TABLE `tbl_ou` ( `intId` int(11) NOT NULL auto_increment, `vchName` varchar(100) NOT NULL default '', `vchDescription` varchar(255) default NULL, `intParent` int(11) default NULL, `intType` int(11) NOT NULL default '0', `tinStatus` tinyint(3) NOT NULL default '1', PRIMARY KEY (`intId`) ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1 COMMENT=3D'Orginisational Unit= s'; # # Dumping data for table tbl_ou # [snip] Output from SHOW SLAVE STATUS: [...] Last_Errno: 1064 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; CREATE TABLE `tbl_ou` ( `intId` int(11) NOT NULL auto_increment, `vchNa' at line 1' on query. Default database: 'sip'. Query: 'DROP TABLE IF EXISTS `tbl_ou`; CREATE TABLE `tbl_ou` ( `intId` int(11) NOT NULL auto_increment, `vchName` varchar(100) NOT NULL default '', `vchDescription` varchar(255) default NULL, `intParent` int(11) default NULL, `intType` int(11) NOT NULL default '0', `tinStatus` tinyint(3) NOT NULL default '1', PRIMARY KEY (`intId`) ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1 COMMENT=3D'Orginisational Unit= s'; Where to look, how to fix? It looks like a bug to me, if so I'll add it to the buglist. Kind regards, Martijn -- =0D The information contained in this communication and any attachments is co= nfidential and may be privileged, and is for the sole use of the intended= recipient(s). Any unauthorized review, use, disclosure or distribution i= s prohibited. If you are not the intended recipient, please notify the se= nder immediately by replying to this message and destroy all copies of th= is message and any attachments. ASML is neither liable for the proper and= complete transmission of the information contained in this communication= , nor for any delay in its receipt. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with installation mysql 5.0.16
Hello. The recommended way to install MySQL on FreeBSD is to use ports collection. my_print_defaults: not found Are you using binary distribution? Put the path to directory which contains my_print_defaults to you $PATH variable, and check if the problem still exists. Êàëàí÷èí À. wrote: Good Day ! When installing mysql 5.0.16 on FreeBSD 5.3, after unpacking of the archive= and attempt to execute =F1=EA=F0=E8=EF=F2: scripts/mysql_install_db=20 the system gives the mistake: my_print_defaults: not found Could not find help 'fill_help_tables.sql' in ./support-files or inside .. -- in than problem? Is it Beforehand thanked --=20 mailto:[EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem in starting Mysqld
Gleb Paharenko wrote: Hello. Are you able to start mysqld without using /etc/init.d/mysql, manually invoking mysqld_safe or mysqld? From my experience, SELinux can cause different problems, check if you have it enabled. Follow general recommendations from: http://dev.mysql.com/doc/refman/5.0/en/starting-server.html I can launch it without a problem with mysqld_safe or with mysqld: /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/localhost.pid --skip-locking --socket=/var/lib/mysql/mysql.sock maybe it's really a problem about SELinux. Yesterday I've update the following library because I had a problem during the setup process of Mathlab: selinux-policy-targeted-1.27.1-2.16.noarch.rpm Now I'm going to put SELinux in permissive mode to see what happen. Thanks Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regarding MysqlConnectionPoolDataSource
Hi Mark - 1. Can you deploy the MysqlConnectionPoolDataSource with JBoss 3.2.5 using J/Connector 3.0.15 (using MySQL 4.0.21)? 2. If so - is the documentation 1.4.3. Using Connector/J with JBoss for the mysql-ds.xml enough using the driver-class tag or do you have to add a tag that provides an explicit reference to the MysqlConnectionPoolDataSource method? 3. Am I correct to assume that once you made the correct reference in mysql-ds.xml to using MysqlConnectionPoolDataSource - JBoss takes it from there and manages the connection pooling with every getConnection() and free() method calls? That is - you never have to import and/or make references to the MysqlConnectionPoolDataSource method in your EJB? 4. Last, when using the MysqlConnectionPoolDataSource method - does the application server automatically terminates the EJB database session as part of the connection pool recycling? I am a beginner using J/Connector and sorry if my questions are a bit un-initiated. If you can direct me to any JBoss-MySQL connection pooling deployment - I will be most grateful. Thank you. Noga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding MysqlConnectionPoolDataSource
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Noga Woronoff wrote: Hi Mark - 1. Can you deploy the MysqlConnectionPoolDataSource with JBoss 3.2.5 using J/Connector 3.0.15 (using MySQL 4.0.21)? Noga, Sure, but it's not required, and generally the JBoss folks seem to prefer using driver-based dispensers for connections with their connection pool implementation since they already have classes that map SQLExceptions to errors that can determine whether or not a connection is stale (which is basically one of the major features of a ConnectionPoolDataSource). Remember, ConnectionPoolDataSources are _not_ connection pools. They provide connections to some implementation of a connection pool. 2. If so - is the documentation 1.4.3. Using Connector/J with JBoss for the mysql-ds.xml enough using the driver-class tag or do you have to add a tag that provides an explicit reference to the MysqlConnectionPoolDataSource method? See my answer to #1. 3. Am I correct to assume that once you made the correct reference in mysql-ds.xml to using MysqlConnectionPoolDataSource - JBoss takes it from there and manages the connection pooling with every getConnection() and free() method calls? That is - you never have to import and/or make references to the MysqlConnectionPoolDataSource method in your EJB? You should never refer to concrete implementations of any of the JDBC APIs in your container-managed code such as EJBs. You always code to the interface, i.e. javax.sql.DataSource. Otherwise your code won't be portable to other JDBC implementations. 4. Last, when using the MysqlConnectionPoolDataSource method - does the application server automatically terminates the EJB database session as part of the connection pool recycling? Depends on the connection pool and how it's configured. Generally the logical session is terminated by the container, eventually the connection pool will terminate the physical session if some idle timeout value is reached, or if the physical session has been determined to be stale. -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDqBaQtvXNTca6JD8RAui6AKCl2RNquhnfvao9jfaGVQ0nnq7kNACeP9RP oa+feaD00tvBWLneNf3yAaE= =VGGx -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CPU 100% + crashes ...
That was the problem.
Thanks a lot Heikki.
I guess my next move would be to persuade upgrading to MySQL 5.
Dilipan
Heikki Tuuri wrote:
Dilipan,
indeed, there was a race in the printing of thd-query in 4.0.18. But
the problem was probably fixed in May 2004.
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
5.0.16, ha_innodb.cc:
...
if ((s = thd-query)) {
/* 3100 is chosen because currently 3000 is the maximum
max_query_len we ever give this. */
charbuf[3100];
uintlen;
/* If buf is too small, we dynamically allocate storage
in this. */
char* dyn_str = NULL;
/* Points to buf or dyn_str. */
char* str = buf;
if (max_query_len == 0)
{
/* ADDITIONAL SAFETY: the default is to print at
most 300 chars to reduce the probability of a
seg fault if there is a race in
thd-query_length in MySQL; after May 14, 2004
probably no race any more, but better be
safe */
max_query_len = 300;
}
len = min(thd-query_length, max_query_len);
if (len (sizeof(buf) - 1))
{
dyn_str = my_malloc(len + 1, MYF(0));
str = dyn_str;
}
/* Use strmake to reduce the timeframe for a race,
compared to fwrite() */
len = (uint) (strmake(str, s, len) - str);
putc('\n', f);
fwrite(str, 1, len, f);
if (dyn_str)
{
my_free(dyn_str, MYF(0));
}
...
- Original Message - From: Heikki Tuuri
[EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Friday, December 16, 2005 3:46 PM
Subject: Re: CPU 100% + crashes ...
Dilipan,
thank you.
I think there indeed is a slight unprotected access in:
0x8158a17 innobase_mysql_print_thd + 471
We will investigate if it has been fixed in 4.1.xx.
Again, SHOW INNODB STATUS\G shows a very light load inside InnoDB. You
would get more informative output if you would wait 20 seconds before
printing each SHOW INNODB STATUS\G. Now it says:
Per second averages calculated from the last 0 seconds
But the high load hardly can be inside InnoDB. Please post SHOW
PROCESSLIST during typical high load.
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
Dilipan Sebastiampillai wrote:
can you please post the complete .err log that also contains
information about the crashes. If there are stack traces, please
resolve them.
-
.err
-
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this
binary
or one of the libraries it was linked against is corrupt, improperly
built,
or misconfigured. This error can also be caused by malfunctioning
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.
key_buffer_size=8388608
read_buffer_size=520192
max_used_connections=148
max_connections=1000
threads_connected=21
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 1540184 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x6f405da0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x1978f88, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x80f7893
0x6b4d96
0x82ab930
0x8158a17
0x82302c1
0x819f373
0x815d4e2
0x8105657
0x810868d
0x8103321
0x8102eb8
0x810280d
0x6afe51
0x51e06a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so
please do
resolve it
Trying to get some
Re: Re-REQUEST: proper way to export with the --tab function???
Bruce Carey wrote: - my problem is that when a text field, with html in it, it makes it into several hundred recs Well, that's strange -- I use mysqldump to dump/xfer/load records with fields containing HTML regularly, using the mysqldump defaults, and have no problems. Have you tried the default settings? If so and you're seeing this problem, let's see some sample records that are failing. -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regarding MysqlConnectionPoolDataSource
Hi Mark - Regarding: Depends on the connection pool and how it's configured. Generally the logical session is terminated by the container, eventually the connection pool will terminate the physical session if some idle timeout value is reached, or if the physical session has been determined to be stale. I need the logical session to terminate physically as part of the connection pooling recycling. My admin program contains several applications. During one user session I may run several applications. My suspicion is that when I exit one and start another - the first database session is physically still active while the second application has started. This is the problem I wrote to Heikki Tuuri about when it was evident that the FOREIGN_KEY_CHECKS was still set to 0. My question regarding your response: JBoss is most likely using a connection pool, which means that the connection doesn't physically close, which also means that the value for SET FOREIGN KEY CHECKS isn't reset to its default value automatically. If you can use MysqlConnectionPoolDataSource with JBoss' connection pool, the MySQL JDBC driver will reset connection state when the logical connection is closed. If you don't use our ConnectionPoolDataSource, your application is responsible for setting session-level variables that affect server behavior to appropriate values before returning them to the connection pool. How do I use the MysqlConnectionPoolDataSource with JBoss' connection pool??? How do I make sure that every EJB is associated with one database session only? In other words how do I use your ConnectionPoolDataSource to ensure that the MySQL JDBC driver will reset connection state when the logical connection is closed? Thank you much in advance - Noga -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 20, 2005 9:35 AM To: Noga Woronoff Cc: mysql@lists.mysql.com Subject: Re: Regarding MysqlConnectionPoolDataSource -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Noga Woronoff wrote: Hi Mark - 1. Can you deploy the MysqlConnectionPoolDataSource with JBoss 3.2.5 using J/Connector 3.0.15 (using MySQL 4.0.21)? Noga, Sure, but it's not required, and generally the JBoss folks seem to prefer using driver-based dispensers for connections with their connection pool implementation since they already have classes that map SQLExceptions to errors that can determine whether or not a connection is stale (which is basically one of the major features of a ConnectionPoolDataSource). Remember, ConnectionPoolDataSources are _not_ connection pools. They provide connections to some implementation of a connection pool. 2. If so - is the documentation 1.4.3. Using Connector/J with JBoss for the mysql-ds.xml enough using the driver-class tag or do you have to add a tag that provides an explicit reference to the MysqlConnectionPoolDataSource method? See my answer to #1. 3. Am I correct to assume that once you made the correct reference in mysql-ds.xml to using MysqlConnectionPoolDataSource - JBoss takes it from there and manages the connection pooling with every getConnection() and free() method calls? That is - you never have to import and/or make references to the MysqlConnectionPoolDataSource method in your EJB? You should never refer to concrete implementations of any of the JDBC APIs in your container-managed code such as EJBs. You always code to the interface, i.e. javax.sql.DataSource. Otherwise your code won't be portable to other JDBC implementations. 4. Last, when using the MysqlConnectionPoolDataSource method - does the application server automatically terminates the EJB database session as part of the connection pool recycling? Depends on the connection pool and how it's configured. Generally the logical session is terminated by the container, eventually the connection pool will terminate the physical session if some idle timeout value is reached, or if the physical session has been determined to be stale. -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDqBaQtvXNTca6JD8RAui6AKCl2RNquhnfvao9jfaGVQ0nnq7kNACeP9RP oa+feaD00tvBWLneNf3yAaE= =VGGx -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: locating ibdata1 and *.ibd files in different directories.
On 12/20/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Please, next time answer to the list as well. Sorry. I didn't realize Gmail's 'reply' didn't go to the list. First time I noticed the 'reply to all' option in Gmail. Thanks. As far as I know, you can't specify the location of ibd files, they're stored in the database directory, however, you can use symbolic links for databases to have them in another place. symbolic links! Thats a neat solution. Question: when you say symbolic links for databases do you mean links to ibd files, ibdata1 file, either, or something else? all databases? So the question is if I can locate the ibdata1 file somewhere else. Have you tried just to change the value of innodb_home_dir to the new location, and move there ibdata1 file? And leave the original subdirs(databases) in the original place? I can try. This means though, that the absolute db paths are coded into the ibdata file. ibd files. BUT, the ibdata1 file is still 7 gig and being If you want do decrease the size of ibdata1 file, you should dump all your InnoDB tables, stop the server, remove all existing tablespace files, configure a new tablespace, restart the server, import the dump files. In such a way you'll move all your tables which are in ibdata1 tablespace to may ibd files in databases. See: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html Is mysql smart enough not to use my indices when importing until after the import, or should I de-activate my indices until after the import? Also, is there a formula of what I can expect the size of the dumped files to be? Thank you much! -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: locating ibdata1 and *.ibd files in different directories.
Hello. symbolic links! Thats a neat solution. Question: when you say symbolic links for databases do you mean links to ibd files, ibdata1 file, either, or something else? I've meant symbolic links for databases. See: http://dev.mysql.com/doc/refman/5.0/en/symbolic-links.html Is mysql smart enough not to use my indices when importing until after the import, or should I de-activate my indices until after the import? mysqldump from 5.0.17 distribution sets FOREIGN_KEY_CHECKS to 0; for your version you can check this by yourself. Also, is there a formula of what I can expect the size of the dumped files to be? For a pity, I don't know any formula, even approximate. Nathan Gross wrote: On 12/20/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Please, next time answer to the list as well. Sorry. I didn't realize Gmail's 'reply' didn't go to the list. First time I noticed the 'reply to all' option in Gmail. Thanks. As far as I know, you can't specify the location of ibd files, they're s= tored in the database directory, however, you can use symbolic links for databases to = have them in another place. symbolic links! Thats a neat solution. Question: when you say symbolic links for databases do you mean links to ibd files, ibdata1 file, either, or something else? all databases? So the question is if I can locate the ibdata1 file somew= here else. Have you tried just to change the value of innodb_home_dir to the new location, and move there ibdata1 file? And leave the original subdirs(databases) in the original place? I can try. This means though, that the absolute db paths are coded into the ibdata file. ibd files. BUT, the ibdata1 file is still 7 gig and being If you want do decrease the size of ibdata1 file, you should dump all your InnoDB tables, stop the server, remove all existing tablespace files, configure a new tablespace, restart the server, import the dump files. In such a way you'll move all your tables which are in ibdata1 tablespace to may ibd files in databases. See: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html Is mysql smart enough not to use my indices when importing until after the import, or should I de-activate my indices until after the import? Also, is there a formula of what I can expect the size of the dumped files to be? Thank you much! -nat -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow sorting
On 20/12/2005, Marcus Bointon wrote: I have a table that contains around 400,000 simple names. It's displaying a subset of them (perhaps 5,000) them a page at a time in a web interface, sorted by name, so I have a query like this: SELECT * FROM names WHERE account=123 ORDER BY lastname, firstname LIMIT 0,30 That takes 11 seconds to run. Without the order by it takes 0.13 sec. I have simple indexes on both first name and last name (they are sometimes searched separately). It strikes me that this is really very slow - it really doesn't have much to sort. I tied doing an explain, and though I could see that it was using the indexes, it was also saying use where, use temporary, use filesort. Why is it falling back to these methods? How can I make this faster? To avoid the filesort, you need a composite index on (account, lastname, firstname) instead of separate indexes on account, lastname and fistname. -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Re: locating ibdata1 and *.ibd files in different directories. - Bayesian Filter detected spam
The following takes a little effort, but it should get you close to dump
file size.
On 5.0.x you can use Information_schema.columns to get average row
length for MyISAM tables. Take that plus the punctutation {~35 + 3* #
cols for insert per row if you enclose your columns in 's} in the
insert statements generated by mysqldump times the number of rows and
that will give you the size of the MyISAM tables.
For INNODB use
mysql select avg(length(concat( col1, col2,...))) AS Avg_Len, count(*)
- from table ;
+--+--+
| Avg_Len | count(*) |
+--+--+
| 107.5588 | 3514429 |
+--+--+
1 row in set (1 min 1.31 sec)
I would also use Information_schema.columns to get the column names so I
would not have to type them.
-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 20, 2005 12:24 PM
To: mysql@lists.mysql.com
Subject: [SPAM] - Re: locating ibdata1 and *.ibd files in different
directories. - Bayesian Filter detected spam
Hello.
symbolic links! Thats a neat solution. Question: when you say
symbolic links for databases do you mean links to ibd files, ibdata1
file, either, or something else?
I've meant symbolic links for databases. See:
http://dev.mysql.com/doc/refman/5.0/en/symbolic-links.html
Is mysql smart enough not to use my indices when importing until after
the import, or should I de-activate my indices until after the import?
mysqldump from 5.0.17 distribution sets FOREIGN_KEY_CHECKS to 0; for
your version you can check this by yourself.
Also, is there a formula of what I can expect the size of the dumped
files to be?
For a pity, I don't know any formula, even approximate.
Nathan Gross wrote:
On 12/20/05, Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
Please, next time answer to the list as well.
Sorry. I didn't realize Gmail's 'reply' didn't go to the list. First
time I noticed the 'reply to all' option in Gmail. Thanks.
As far as I know, you can't specify the location of ibd files,
they're s=
tored in the
database directory, however, you can use symbolic links for databases
to =
have
them in another place.
symbolic links! Thats a neat solution. Question: when you say
symbolic links for databases do you mean links to ibd files, ibdata1
file, either, or something else?
all databases? So the question is if I can locate the ibdata1 file
somew=
here else.
Have you tried just to change the value of innodb_home_dir to the new
location, and move there ibdata1 file?
And leave the original subdirs(databases) in the original place? I can
try. This means though, that the absolute db paths are coded into the
ibdata file.
ibd files. BUT, the ibdata1 file is still 7 gig and being
If you want do decrease the size of ibdata1 file, you should dump all
your InnoDB tables, stop the server, remove all existing tablespace
files, configure a new tablespace, restart the server, import the dump
files. In such a way you'll move all your tables which are in ibdata1
tablespace to may ibd files in databases. See:
http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html
Is mysql smart enough not to use my indices when importing until after
the import, or should I de-activate my indices until after the import?
Also, is there a formula of what I can expect the size of the dumped
files to be?
Thank you much!
-nat
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.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]
Cleaning illegal characters from varchar field
Hi; [Mysql 4x] In a table where I get data from another program, I have many records (about 1000) that have illegal chars in a [unique] indexed varchar field. I would like to clean the illegal characters out and leave the rest of the data intact. 1. Is there a utility to do this? 1b) Or an UPDATE command? Problem is that I do not even know which characters are illegal. For the future, I can have my Java program check before the inserts. 2. What are the illegal characters? Thank you. -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ALTER TABLE on InnoDB -- behind the scenes?
Confused about how ALTER TABLE foo ADD col2... operates. On an otherwise idle server, iostat 1 shows the disk being written to at a rate of 15MB/sec (its capacity is about 50MB/sec), but the ibdata-autoextend file only grows at a rate of 500kb/sec. 12GB table (w/ 1.5GB index) took 6 hours to complete, which suggests the actual table rewriting did happen at 500kB/sec. Something wrong here? What else was going on? MySQL 4.1.10a RHEL4.1
merge table: speed benefits?
Hi all. I'm working on splitting a 5G myisam fact table into separate parts, and putting a merge table on top. his will definitely help with table management, but I am hoping that a merge table might help me with query speed: (a) The docs say that spreading the underlying tables across different disks can make queries faster. I don't quite understand how this will work in a normal query: if I do a SUM(amount) over the entire table, will it be quicker if the table is spread across different disks? I don't see how mysql can avoid doing this sequentially. (b) I also thought that if I do a series of queries, via the merge table, which all go to a single underlying table, then that would be quicker than the same queries to the original monster table: because the index and data caches would just be cacheing for a single table. iis this true?/i. On reflection I thought that the index and data caches only cache *parts* of tables, not entire tables, so it will make no difference. (c) Finally: in theory the optimiser could use two indexes: use index A to decide which tables to look at, then use index B within those tables to find the rows that meet another condition. This would be an advantage over a non-merged table. But I don't think Mysql can do this, can it? So should I expect any speed benefits to splitting my fact table? Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cleaning illegal characters from varchar field
Nathan Gross [EMAIL PROTECTED] wrote on 12/20/2005 05:34:58 PM: Hi; [Mysql 4x] In a table where I get data from another program, I have many records (about 1000) that have illegal chars in a [unique] indexed varchar field. I would like to clean the illegal characters out and leave the rest of the data intact. 1. Is there a utility to do this? 1b) Or an UPDATE command? Problem is that I do not even know which characters are illegal. For the future, I can have my Java program check before the inserts. 2. What are the illegal characters? Thank you. -nat If you don't know what is illegal, how do you know you have illegal characters? Are you getting some kind of error message or warning? You have to give us the same information you have in order for us to make an informed decision. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Do I need to tweak my server variables for this SELECT statement?
Hi, For the first time, I'm working with a really large database. I have 1 SQL statement that brings my server to it's knees. This setup is currently on my home development PC, and not in production. The server is running apache, samba, and mysql under gentoo linux. I'm the only user, so there is no vitually load on the server. The server has 1 Gig of ram. I've got 2 tables, one that holds a list of product, the other holds a list of categories that the product is associated with. My SELECT statment just grabs 10 products that are associated with a specific category. The product table has 650,000 rows and the category table has 8,150,000 rows. My SELECT statement is: SELECT pn_pricecompare_catprod.category, pn_pricecompare_catprod.asin, pn_pricecompare_product.title, pn_pricecompare_product.prod_id, pn_pricecompare_product.image_small, pn_pricecompare_product.brand, pn_pricecompare_product.manufacturer, pn_pricecompare_product.mpn, pn_pricecompare_product.model, pn_pricecompare_product.artist, pn_pricecompare_product.author, pn_pricecompare_product.binding, pn_pricecompare_product.label, pn_pricecompare_product.audiencerating, pn_pricecompare_product.studio, pn_pricecompare_product.releasedate, pn_pricecompare_product.numberofpages, pn_pricecompare_product.pubdate, pn_pricecompare_product.publisher, pn_pricecompare_product.searchindex, pn_pricecompare_product.lowest_price, pn_pricecompare_product.num_merchants FROM pn_pricecompare_catprod, pn_pricecompare_product WHERE ((pn_pricecompare_catprod.category = '283155') AND (pn_pricecompare_catprod.asin = pn_pricecompare_product.asin)) ORDER BY pn_pricecompare_product.salesrank ASC LIMIT 0,10 Sometimes this takes 10 minutes to execute. When this occurs, I can hear the hard drive thrashing. If I do an EXPLAIN, I get: table typepossible_keys keykey_len ref rowsExtra pn_pricecompare_catprod ref PRIMARY,asin PRIMARY 4 const 355416 Using where; Using index; Using temporary; Using f... pn_pricecompare_product eq_ref asin asin 10 pn_pricecompare_catprod.asin 1 When the query executes, and I check the processes, I see Copying to tmp table on disk After googling for this statement I found several pages that indicate I might have to tweak the my.cnf file. I checked my my.cnf file, and it's just the default file. I found the example huge, large, medium, and small .cnf files and plan on using them to try to optimize my my.cnf file. Other than trial and error, I really don't know what I'm doing. Here is a link to my current server variables: http://retailretreat.com/mysql/server_variables.php.htm Here is a link to my current my.cnf file: http://retailretreat.com/mysql/my.cnf.txt Here is a link to my proposed my.cnf file. I'm not sure if there are any errors or mistakes in the file. http://retailretreat.com/mysql/my-new.cnf.txt Am I right that I need to tweak the my.cnf file? I think I optimized my tables with INDEXes correctly already. What should I look for first? Thanks, Grant __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: insert utf8 character in Linux commind-line tool
No effect. I use chinese in konsole no problem but in mysql commind-line.So in my word, this problom maybe happen in mysql commind-line setup. - Original Message - From: 古雷 [EMAIL PROTECTED] To: wangxu [EMAIL PROTECTED]; Andreas Streichardt [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, December 20, 2005 11:47 AM Subject: Re: insert utf8 character in Linux commind-line tool export LANG=zh_CN.UTF-8 - Original Message - From: wangxu [EMAIL PROTECTED] To: Andreas Streichardt [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, December 20, 2005 10:10 AM Subject:Re: insert utf8 character in Linux commind-line tool Are there two option? How to use them? - Original Message - From: Andreas Streichardt [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, December 20, 2005 3:55 AM Subject: : insert utf8 character in Linux commind-line tool On Monday 19 December 2005 08:47, wangxu wrote: I can't operate utf8 characters within command-line in linux operating system. Mysql doesn't support? --without-libedit –with-readline=/usr/include/readline that fixed it for me Kind Regards, Andreas Streichardt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (瑞星提示-此邮件可能是垃圾 邮件)Re: (鐟炴槦鎻愮ず-姝ら偖浠 跺彲鑳芥槸鍨冨溇閭欢)Re: insert ut f8 character in Linux commind-li ne tool
Are you mean this problom only disposed in compile ? - Original Message - From: Andreas Steichardt [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, December 20, 2005 4:34 PM Subject: (瑞星提示-此邮件可能是垃圾邮件)Re: (鐟炴槦鎻愮ず-姝ら偖浠跺彲鑳芥槸鍨冨溇閭欢)Re: insert utf8 character in Linux commind-line tool On Tuesday 20 December 2005 03:10, wangxu wrote: Are there two option? How to use them? These are just two options which you will have to use when ./configure'ing the source distribution if you are using a binary distribution of mysql try downloading the source distribution... then read http://dev.mysql.com/doc/refman/4.1/en/installing-source.html and add the two options to the configure. I can't operate utf8 characters within command-line in linux operating system. Mysql doesn't support? --without-libedit –with-readline=/usr/include/readline -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert utf8 character in Linux commind-line tool
Follow is the result. +--+-+ | Variable_name| Value | +--+-+ | character_set_client | utf8| | character_set_connection | utf8| | character_set_database | utf8| | character_set_results| utf8| | character_set_server | utf8| | character_set_system | utf8| I I use chinese in konsole no problem but in mysql commind-line.So in my word, this problom maybe happen in mysql commind-line setup. - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, December 20, 2005 8:02 PM insert utf8 character in Linux commind-line tool Hello. Please, provide the output of this statement: show variables like '%char%'; I import utf8 character in terminal commind-line is well.So in my word,there aren't error in my terminal settings. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
