Re: Default connection codepage
Hello. That is my my.ini snip (my.ini is located in MySQL base dir - is that correct?): On Windows, MySQL programs read startup options from the following files: WINDIR\my.ini C:\my.cnf Use --defaults-file=/path/ command line option to specify the exact location of my.ini file. Here is some more light on my problem: I am trying to set up default connection codepage for PHP scripts. I've look through source code of mysql extention of php, and hadn't found any php-side part of code where it was set client character set. It takes place at the libmysqlclient side. And in most cases it takes the server's character set (I've analyze behavior of scripts and source code of libmysqlclient). I think it is no way to specify the client character set in php script, except using SQL statements or mysql_options(). Denis Gerasimov [EMAIL PROTECTED] wrote: Hello, -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 21, 2004 1:29 PM To: [EMAIL PROTECTED] Subject: Re: Default connection codepage Hello. Put default_character_set='blah123' in your [client] and [mysql] sections of your config file. That is my my.ini snip (my.ini is located in MySQL base dir - is that correct?): [client] # what you suggested, seems to have no effect in my case default-character-set=cp1251 port=3306 [mysqld] port=3306 basedir=C:/Program Files/MySQL/MySQL Server 4.1/ datadir=E:/intranet/dbdata/ default-character-set=cp1251 default-storage-engine=INNODB ... Here is what I have now mysql status -- mysql Ver 14.7 Distrib 4.1.7, for Win95/Win98 (i32) Connection id: 1 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Using delimiter:; Server version: 4.1.7-nt Protocol version: 10 Connection: localhost via TCP/IP Server characterset:latin1 Db characterset:cp1251 Client characterset:cp1251 Conn. characterset:latin1 TCP port: 3306 Uptime: 4 sec Threads: 1 Questions: 3 Slow queries: 0 Opens: 11 Flush tables: 1 Open tables: 0 Queries per second avg: 0.750 -- Service installed as C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt --defaults-file=C:\Program Files\MySQL\MySQL Server 4.1\my.ini MySQL default-character-set setting in [client] section has no effect (at least in mysql utility and PHP) Here is some more light on my problem: I am trying to set up default connection codepage for PHP scripts. Configuration I use: MySQL 4.1.7 PHP 5.0.2 PEAR's DB + DB_DataObject (both latest) Does anybody have the same problem? Denis Gerasimov [EMAIL PROTECTED] wrote: Hello, Is there any way to change default MySQL _connection_ codepage? (e.g. by ini/cnf files setting, by startup parameters or some other way) Please, do not suggest running SET CHARACTER SET blah123 Thanks. Best regards, Denis Gerasimov Outsourcing Services Manager, VEKOS, Ltd. www.vekos.ru -- 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] Best regards, Denis Gerasimov Outsourcing Services Manager, VEKOS, Ltd. www.vekos.ru -- 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: Mysql upgrade problem 3.23 to 4.1
Hello.
The password hashing mechanism was updated in MySQL 4.1 to provide better
security and to reduce the risk of passwords being intercepted. Use
OLD_PASSWORD() instead of PASSWORD. See:
http://dev.mysql.com/doc/mysql/en/Password_hashing.html
Jerry Swanson [EMAIL PROTECTED] wrote:
I upgraded mysql mysql-3.23.58-1 to mysql4.1
In mysql-3.23 ( I have this field)
password | varbinary(45) | YES | | NULL
|
I inserted data into this field as password('pass').
I have php script that checks if login valid. mysql-3.23 and
mysql-4.1 have identical data.
It worked on mysql-3.23 but doesn't work on mysql4.1. Password don't match.
Any ideas why?
--
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: LOAD DATA INFILE
Goutham Thanks for your help. The problem in this case was line endings. I use an Apple g4 for web work on system 10.2. By default BBEdit uses macintosh line endings. MySQL does not recognize them. As soon as I changed the textfile format to unix line endings, it imported the data without any trouble at all. I take your point about mysql import and the nature of this command. The only reason I am using is that at the present time I do not have the time to reconfigure my server to make use of other ways of importing text. Thanks for your comments RB On 21/12/04 1:31 AM, Goutham S Mohan [EMAIL PROTECTED] wrote: Hi Rob, LOAD DATA INFILE is not a very verbose command. It doesn't spill out the exact root cause of the error. Forgive me, if I seem to be wrong for anybody who had a different opinion. mysqlimport is a wrapper around LOAD DATA INFILE with a lot of command line options. You might try your luck using the mysqlimport command which does the same for you. But I am not sure if it really helps you with more verbose output. The problem might be due to any of the following problems: - The data in the file needs to be in a format that mysql understands. With mysqlimport you can specify the column and record delimiters as required. - Escape characters need to be defined when you do a mysqlimport so that the data is not mistaken as a delimiter by mysql. - Also make sure that there are no foreign key violations in your target database. Regards, Goutham S Mohan --- Software Engineer, Hewlett Packard --- rob byrne [EMAIL PROTECTED] wrote: I am trying to load data from a text file into a table using the Load data infile statement. No matter how I change data types I seem only able to load in the first row of data into the MySQL table and no more. I have not been able to trach the source of this error or the cause of it? Does anyone know the cause so I can fix it? RB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use character_set_xxx in my.ini ?
Hello. Put this string into [client] section of my.ini. Zimoo [EMAIL PROTECTED] wrote: Hello Gleb, Sunday, December 19, 2004, 10:02:58 PM, you wrote: Also you can put in your [client] section of my.cnf something like: default_character_set=gbk I use my.ini in MySQL install directory, not my.cnf . My MySQL Server in windows services.msc is C:\Program Files\MySQL\bin\mysqld-max-nt --defaults-file=C:\Program Files\mysql\my.ini MySQL When I put default_character_set = gbk or default-character-set = gbk in [client], and use command C:\net start mysql Server could started, but mysql show me like: mysql show variables like char%; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | gbk| | character_set_results| latin1 | | character_set_server | gbk| | character_set_system | utf8 | | character_sets_dir | C:\Program Files\MySQL\share\charsets/ | +--++ 7 rows in set (0.00 sec) Why? -- 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: Regarding User creation and loading data
Hello. What error have you got? Send us compete command that you issued and the error message. Could any one of you help me in creating two users like First user with all dba privileges as root and the Second user with only dml operations. For root user use: grant all privileges on *.* to 'vasja'@'vasinhost' identified by 'vasinpass' with grant option; For dml user use something like this: grant select,insert,update,delete on test.* to 'dml'@'localhost' identified by 'v'; See: http://dev.mysql.com/doc/mysql/en/GRANT.html And in loading data using LOAD DATA INFILE, if I use the file full path, I am getting error. And if I put the file in mysql\bin it is executing. How to load a dta in a file which is located in some other directories. -- 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: Unique IDs
with implicit join : INSERT into table1(id, list of other columns) select m.newID, list of other columns from table2, tmpIDMAP where m.id = table2.id UPDATE table3, tmpIDMAP SET table3.parentID = m.newID where m.id = table3.parentID Andrew Mull wrote: Thank you. I'll have to review the SQL carefully as I haven't used inner joins in quite some time :) Quoting [EMAIL PROTECTED]: Create a temporary table that will act as a map between your old IDs and your new ones. That way you can re-use those values and change them from the old values to the new values. LOCK TABLE table1 WRITE, table2 WRITE, table3 WRITE; select @max_id:=max(id) FROM table1; CREATE TEMPORARY TABLE tmpIDMAP SELECT id, [EMAIL PROTECTED] as newID FROM table2 INSERT into table1(id, list of other columns) select m.newID, list of other columns from table2 INNER JOIN tmpIDMAP m ON m.id = table2.id UPDATE table3 INNER JOIN tmpIDMAP m ON m.id = table3.parentID SET table3.parentID = m.newID (repeat for other child tables) UNLOCK I don't have time to give a better explanation right now but if you write the list back, I can fill in the details later. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Andrew Mull [EMAIL PROTECTED] wrote on 12/21/2004 12:20:57 PM: You are correct in that I need to merge two tables. However, I'm not clear on how to update the FKs in the sub tables. Example, the main table (Person) has a sub table (Address). Person has a 1:N relationship with Address. So Address has the Person's ID as a FK. How do I update the FK in Address with the new ID assigned to Person that was created with the merge? Thanks for the help! Quoting Philippe Poelvoorde [EMAIL PROTECTED]: Andrew Mull wrote: I'm working on a rather large database with many cross-linked tables currently using auto increment IDs. The system is primarily a web based system, however, there will be times that the system will be run as a stand alone server...meaning no internet connection is available. The question arises that if someone enters information to the database on the website, while others are entering information on the local database, what is the best way to merge the data? I would imagine that we would run into many duplicate auto increment IDs. I'm sure that for one table insert, this would not be a problem as I could store the SQL statement in a text file without the ID specified, and run it as a batch process on the live server when we get connectivity. But I don't have a handle on how to update the sub tables that have a FK pointer. Any ideas? Thanks! -Andy I understand your question as the following: you want to merge 2 tables comming from different database in a single table. If this is not what you want, sorry :) I would do that : LOCK TABLE table1 WRITE, table2 WRITE select @max_id:=max(id) FROM table1; UPDATE table2 SET [EMAIL PROTECTED]; insert into table1(list of columns) select [list of columns] from table2 UNLOCK and then updating your FK within the update query. Keep in mind that I didn't try with InnoDb tables... (but works fine for MyIsam) -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regarding User creation and loading data
Hi, Thank you for your reply. When I log in as a DBA user and try to load data from a file, I am getting an error as Access Denied, so, here I want to know what type of permissions I have to give for a user to load data infile form a file. Thanks, Narasimha -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 1:35 AM To: mysql@lists.mysql.com Subject: Re: Regarding User creation and loading data Hello. What error have you got? Send us compete command that you issued and the error message. Could any one of you help me in creating two users like First user with all dba privileges as root and the Second user with only dml operations. For root user use: grant all privileges on *.* to 'vasja'@'vasinhost' identified by 'vasinpass' with grant option; For dml user use something like this: grant select,insert,update,delete on test.* to 'dml'@'localhost' identified by 'v'; See: http://dev.mysql.com/doc/mysql/en/GRANT.html And in loading data using LOAD DATA INFILE, if I use the file full path, I am getting error. And if I put the file in mysql\bin it is executing. How to load a dta in a file which is located in some other directories. -- 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] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
updates after restart MySQL 4.0.18
Hi everyone, Our server crashed the Two questions : 1) is it normal to have a dramatic amount of UPDATES after a MySQL restart after crash ? 2) how to get valuable information from the following? ; Crash from 20/12/04 at 20.10(?) from server.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=838860800 read_buffer_size=104853504 max_used_connections=63 max_connections=400 threads_connected=17 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3930556 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0xb478f518 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=0xb57c2c0c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80f7893 0x40094e48 0x4690f068 0x82302c1 0x819f373 0x815d4e2 0x8105657 0x810868d 0x8103321 0x8102eb8 0x810280d 0x4008edac 0x40254a8a 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 variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0xb507fe68 is invalid pointer thd-thread_id=5725700 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 041220 20:10:52 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 9 2043063838 InnoDB: Doing recovery: scanned up to log sequence number 9 2047101097 041220 20:10:53 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 041220 20:10:55 InnoDB: Flushing modified pages from the buffer pool... 041220 20:10:57 InnoDB: Started /var/lib/mysql/MySQL4.0.18/libexec/mysqld: ready for connections. Version: '4.0.18-log' socket: '/var/lib/mysql/MySQL4.0.18/var/mysql4.0.18.sock' port: 3306 Resolve stack : [EMAIL PROTECTED] ~mysql/tmp]# resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack 0x80f7893 handle_segfault + 399 0x40094e48 _end + 936804128 0x4690f068 _end + 1046356288 0x82302c1 lock_print_info + 1573 0x819f373 srv_sprintf_innodb_monitor + 507 0x815d4e2 _Z18innodb_show_statusP3THD + 138 0x8105657 _Z21mysql_execute_commandv + 5999 0x810868d _Z11mysql_parseP3THDPcj + 329 0x8103321 _Z16dispatch_command19enum_server_commandP3THDPcj + 1069 0x8102eb8 _Z10do_commandP3THD + 100 0x810280d handle_one_connection + 841 0x4008edac _end + 936779396 0x40254a8a _end + 938638178 [EMAIL PROTECTED] ~mysql/tmp]# Thanks in advance. -- Dilipan Sebastiampillai London -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL tweaking.
On Tue, 21 Dec 2004 15:16:02 -0700 Sasha Pachev [EMAIL PROTECTED] wrote: The hardware you have is just fine. But start thinking about a clustered solution. As the traffic increases, you eventually reach a point where no hardware is good enough on one box. Hi, Thankyou for your reply. I have the system load down a fair bit.Was something silly on my behalf. I had mysql logging all queries to disk. serious i/o loads. I have since stopped this and now system is seeing 100qps and only 1% aborted attempts. ( was 20qps and 20% Aborted queries. ) -- Regards Mick Pollard (lunix) BOFH Excuse of the day: Temporary Syntax Expiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore help! been going 2 weeks
Those are pretty much standard settings I had ours set almost exactly like that, and performance was worse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and innodb - set foreign_key_checks=0
Terence, - Original Message - From: Terence [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, December 22, 2004 5:58 AM Subject: mysqldump and innodb - set foreign_key_checks=0 Hi, After reading the docs I realise that in order to use mysqldump with innodb tables i need to include SET FOREIGN_KEY_CHECKS=0; at the top of my dump file. Is there anyway to do this when my slave starts up or some other way. The dump file is huge and it takes ages to open and put the line at the top. (In trying to setup replication I am trying all ways to reduce the downtime of the master while I get a snapshot onto the slave) I use ./bin/mysql -u root -p /tmp/dump.sql to get the contents into my slave. Something like this might work: ./bin/mysql -u root -p -e SET FOREIGN_KEY_CHECKS=0; /tmp/dump.sql in 4.1, mysqldump puts that SET FOREIGN_KEY_CHECKS=0; automatically to the dump file. Grateful for and ideas ... Thanks Best regards and Season's Greetings, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regarding User creation and loading data
use root user of mysql. u wont get any privilege restrictions with root user. other wise the user you are using should have file privilege to load data from file or to write to a file Thanks Anil -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 5:19 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: RE: Regarding User creation and loading data Hi, Thank you for your reply. When I log in as a DBA user and try to load data from a file, I am getting an error as Access Denied, so, here I want to know what type of permissions I have to give for a user to load data infile form a file. Thanks, Narasimha -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 1:35 AM To: mysql@lists.mysql.com Subject: Re: Regarding User creation and loading data Hello. What error have you got? Send us compete command that you issued and the error message. Could any one of you help me in creating two users like First user with all dba privileges as root and the Second user with only dml operations. For root user use: grant all privileges on *.* to 'vasja'@'vasinhost' identified by 'vasinpass' with grant option; For dml user use something like this: grant select,insert,update,delete on test.* to 'dml'@'localhost' identified by 'v'; See: http://dev.mysql.com/doc/mysql/en/GRANT.html And in loading data using LOAD DATA INFILE, if I use the file full path, I am getting error. And if I put the file in mysql\bin it is executing. How to load a dta in a file which is located in some other directories. -- 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] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- 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: Restore help! been going 2 weeks
ours is InnoDB. we are not getting any performance problems with the settings. it is working fine since last 1 Year. to my knowledge due to myisam type you are getting performance issue. Thanks Anil -Original Message- From: matt_lists [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 7:12 PM To: Anil Doppalapudi Cc: mysql@lists.mysql.com Subject: Re: Restore help! been going 2 weeks Those are pretty much standard settings I had ours set almost exactly like that, and performance was worse -- 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: [Fwd: Re: MUTIPLY function?]
Alex, you are correct. That particular formula cannot handle ANY non-positive number because the LOG() function is undefined for values less than or equal to zero. I just reviewed the archives and realized that this point has never been discussed before (I thought it had). Good catch. In the interest of giving credit where credit is due, here is Harald's original answer: http://lists.mysql.com/mysql/166172 Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alex croes [EMAIL PROTECTED] wrote on 12/21/2004 05:08:01 PM: Michael Stassen wrote: No, but Harold Fuchs gave the following solution to Shawn Green in an earlier thread on this topic http://lists.mysql.com/mysql/166184. EXP(SUM(LOG(COALESCE(*the field you want to multiply*,1))) which works because LOG(xy) = LOG(x) + LOG(y), and EXP(LOG(x)) = x. Hence, sum of the logs = log of the product, so exp of the sum of the logs = exp of the log of the product = the product. What if the field contains the value of 0, than it doesn't works. 3*0 = 0, but it returns 1. Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump and innodb - set foreign_key_checks=0
just write the below lines of code to a file in the location where your mysqldump file exists SET FOREIGN_KEY_CHECKS = 0; SOURCE dumpfile name; SET FOREIGN_KEY_CHECKS = 1; then execute the following command mysql filename Thanks Anil DBA -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 8:15 PM To: mysql@lists.mysql.com Subject: Re: mysqldump and innodb - set foreign_key_checks=0 Terence, - Original Message - From: Terence [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, December 22, 2004 5:58 AM Subject: mysqldump and innodb - set foreign_key_checks=0 Hi, After reading the docs I realise that in order to use mysqldump with innodb tables i need to include SET FOREIGN_KEY_CHECKS=0; at the top of my dump file. Is there anyway to do this when my slave starts up or some other way. The dump file is huge and it takes ages to open and put the line at the top. (In trying to setup replication I am trying all ways to reduce the downtime of the master while I get a snapshot onto the slave) I use ./bin/mysql -u root -p /tmp/dump.sql to get the contents into my slave. Something like this might work: ./bin/mysql -u root -p -e SET FOREIGN_KEY_CHECKS=0; /tmp/dump.sql in 4.1, mysqldump puts that SET FOREIGN_KEY_CHECKS=0; automatically to the dump file. Grateful for and ideas ... Thanks Best regards and Season's Greetings, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.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]
RE: myql command-line no warning
after executing command. use the below command to know the warnings it is throwing mysql show warnings; Thanks Anil -Original Message- From: Qunfeng [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 10:45 AM To: Paul DuBois; mysql@lists.mysql.com Subject: Re: myql command-line no warning :-) I see. Thanks! It w(not vv)orks!!! Qunfeng At 10:48 PM 12/21/2004, Paul DuBois wrote: At 20:44 -0600 12/21/04, Qunfeng wrote: Paul, Thanks for your reply. I tried mysql -p -w DB test.sql still no warning. I tried -vw it simply echo the sql statement, no warning. I didn't suggest using -w or -vw. I suggested -vv (two v's) or -vvv (three -v's). Qunfeng At 05:45 PM 12/21/2004, Paul DuBois wrote: At 17:43 -0600 12/21/04, Qunfeng wrote: Hi, I am using mysql 4.1 on linux. When I execute an mysql command (e.g., UPDATE statement) at the mysql prompt, it gives me warning if something is not running correctly. e.g., mysql update testTable set col='abcd' where col='ab'; Query OK, 0 rows affected (0.0 sec) Rows matched: 2 Changed: 0 Warnings: 2 However, if I put the same command into a file and run mysql command-line, no warning or any message was given. I have no idea if there is anything wrong or how many records got changed. $ mysql -p DB test.sql Is there any way I can show the warning and message? Try increasing the verbosity level, for example, with -vv or -vvv. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need more info about currently running queries
may be due to corrupted indexes it is taking long time. just re-org the table.if your table is InnoDB type then use below command to re-org it. mysql alter table table name type=InnoDB; based on size of the table it may execute for long time . for us it has solved performance issue. Thanks Anil -Original Message- From: Greg Fortune [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 1:34 AM To: mysql@lists.mysql.com Subject: Re: Need more info about currently running queries Yep, mysqladmin can give the same info. As noted in my original message, I need much more detailed info. The crux of the problem is that I need to run a query that could take several hours when it's using indexes correctly, but it is not working correctly so it takes longer than I've been willing to wait (about 14 hours). I need to make changes to indexes and run the query again, but I don't want to wait hours until I know whether the change was effective or not. I really need a way to find out how far the query has progressed after 15 minutes so I can calculate how much improvement the changes made. Greg On Tuesday 21 December 2004 02:25 am, Gleb Paharenko wrote: Hello. Using SHOW PROCESSLIST you can just check if your query running, or is waiting for some lock. For more info, you can run ps axm in shell and look for the thread state, but that's more related to the kernel stuff. See: http://dev.mysql.com/doc/mysql/en/SHOW_PROCESSLIST.html Greg Fortune [EMAIL PROTECTED] wrote: Is there anyway to get additional information about a query that is currently running? I've got some performance problems I'm trying to analyze while loading large data sets and I'm running a test query, but I don't have any idea how far the query has progressed. The test query is a count(some_field) with the JOINs that I need and I'd like to know how many rows it has counted or how many rows it has visited from each table, etc, etc, etc. I know ahead of time that the result should be about 2.6 million and am just trying to get timing data. Is there any way to get at this kind of info? Greg -- 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]
Error when reading back dump file.
Hi, I am using the cygwin version of the mysql client. When trying to read back my dump file into the database, I get this error [EMAIL PROTECTED]:/cygdrive/c/Program Files/mysql/data 23:15$ ../bin/mysql.exe -h 10.224.3.3 -u root -p testdb -e ./zztest.sql Enter password: ERROR 1064 at line 1: 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 './zztest.sql' at line 1 The file I am trying to read back contains the first few lines :- -- MySQL dump 10.7 -- -- Host: localhostDatabase: test_irt -- -- -- Server version 4.1.3b-beta-nt-log /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */; -- -- Table structure for table `tasklist` -- CREATE TABLE `tasklist` ( `id` int(10) unsigned NOT NULL auto_increment, `coreid` varchar(100) NOT NULL default '', `amtext` longtext NOT NULL, `amcolor` varchar(100) NOT NULL default '', `pmtext` longtext NOT NULL, `pmcolor` varchar(100) NOT NULL default '', `date` date NOT NULL default '-00-00', PRIMARY KEY (`id`,`coreid`,`date`) ); -- -- Dumping data for table `tasklist` -- INSERT INTO `tasklist` VALUES (67,'user','test','','test','','2004-12-22'); --- However when I log into client and issue :- mysql source ./zztest_irt.sql; Query OK, 0 rows affected (0.00 sec) it seem to work fine. Is there a syntax error when running the command? Thanx, Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore help! been going 2 weeks
Anil Doppalapudi wrote: ours is InnoDB. we are not getting any performance problems with the settings. it is working fine since last 1 Year. to my knowledge due to myisam type you are getting performance issue. Thanks Anil based on this email list, myisam is prefered for heavy query/index use, we do not insert records into these tables durring the day, only once per night no deletes from these tables all are optimized every saturday night they are just freakin huge, too big for mysql I think -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: duplicate key with back slash character in the key?
Does your code properly escape the backslash character as it writes to the database? IF you are NOT using 'cn=abc\\, def, ou=accounts' in your INSERT and UPDATE statements, you will get values that look like 'cn=abc, def, ou=accounts' in your data. Without actual data, actual statements, a few lines of sample data, and the results of SHOW CREATE TABLE tablename\G, it's very hard to diagnose this problem further. Let's also move this discussion to the General Discussion ( mysql@lists.mysql.com) list as this problem does not seem to be related to any issue related to the server itself (yet). If it turns out to be a bug there is a separate list for those, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Guofeng Zhang [EMAIL PROTECTED] wrote on 12/22/2004 05:21:04 AM: In our customer site, our application throws a duplicate key exception. Based on the log printed by our application, we find that the values of the primary key have the following pattern: cn=abc\, def, ou=accounts cn=abc\, ghi,ou=accounts Is the problem caused by these values We cannot know what cause the problem from our code, so we think that maybe the MySQL internal implementation has some constraints. We can not go to the customer site to do further test. Any help are appreciated very much. Thank you Guofeng
Re: Error when reading back dump file.
The -e parameter is intended to allow you to directly execute a SQL statement. In this case the SQL statement you tried to execute was ./zztest.sql which is not valid SQL. You need to use the redirector to tell mysql to use the file you name as an input source. 23:15$ ../bin/mysql.exe -h 10.224.3.3 -u root -p testdb ./zztest.sql mysql should use the content of ./zztest.sql as input (just as if you had been typing it into the console) and not as a statement to execute. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Song Ken Vern-E11804 [EMAIL PROTECTED] wrote on 12/22/2004 10:31:10 AM: Hi, I am using the cygwin version of the mysql client. When trying to read back my dump file into the database, I get this error [EMAIL PROTECTED]:/cygdrive/c/Program Files/mysql/data 23:15$ ../bin/mysql.exe -h 10.224.3.3 -u root -p testdb -e ./zztest.sql Enter password: ERROR 1064 at line 1: 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 './zztest.sql' at line 1 The file I am trying to read back contains the first few lines :- -- MySQL dump 10.7 -- -- Host: localhostDatabase: test_irt -- -- -- Server version 4.1.3b-beta-nt-log /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */; -- -- Table structure for table `tasklist` -- CREATE TABLE `tasklist` ( `id` int(10) unsigned NOT NULL auto_increment, `coreid` varchar(100) NOT NULL default '', `amtext` longtext NOT NULL, `amcolor` varchar(100) NOT NULL default '', `pmtext` longtext NOT NULL, `pmcolor` varchar(100) NOT NULL default '', `date` date NOT NULL default '-00-00', PRIMARY KEY (`id`,`coreid`,`date`) ); -- -- Dumping data for table `tasklist` -- INSERT INTO `tasklist` VALUES (67,'user','test','','test','','2004-12-22'); --- However when I log into client and issue :- mysql source ./zztest_irt.sql; Query OK, 0 rows affected (0.00 sec) it seem to work fine. Is there a syntax error when running the command? Thanx, Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
verbose
can anyone tell me about what verbose mode mean? is it Uppercase or Lower case matter or something with backtick? By the way what do backslash for in mysql query? Thanx guys __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext + order by strategy
Hi there,
Is there any common strategy (of using indexes)
to avoid filesorts while sorting searches by a field ?
Eg. I want fulltext search, then sort results by
some other table field.
For example .:
create table article (
id int not null primary key auto_increment,
sort1 int not null,
sort2 int not null,
description text not null,
FULLTEXT description (description)
)
explain select id from article where match (description) against
(''in boolean mode) order by sort1;
mysql mysql explain select id from article where match (description)
against ('' in boolean mode) order by sort1;
++-+-+--+---+-+-+--+--+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-+-+--+---+-+-+--+--+-+
| 1 | SIMPLE | article | fulltext | description | description
| 0 | |1 | Using where; Using filesort |
++-+-+--+---+-+-+--+--+-+
It doesn't matter whether I have index on sort1, filesort is always used.
Furthermore, I've noted that adding an index slows the query down
with each index added (suppose becuase of table index becomes bigger).
I come to a solution where I create a separate table (exact copy) with only
fulltext indexes, then query it with order by LEFT JOIN with original table,
eg:
SELECT article.id, article.description FROM search
LEFT join article
ON search.id = article.id
WHERE
MATCH (description) AGAINST ('some' IN BOOLEAN MODE)
ORDER BY search.sort1
LIMIT 0, 50;
Also, I intensively use table article for listing (w/o search)
items sorted by sort1 or sort2 fields, so in article table
indexes for sort1 and sort2 are absolutely necessary.
So currently I see only 2 solutions
1. add to the article table ndescription
field (with stemmed description), and fulltext index on it
2. create a separate table for this purpose.
I hope there is a 3rd variant, a good idea on how to
avoid filesort
Sincerely,
Aleksandr
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calculating User Ranks (SQL Query Question)
Im trying to come up with a more efficient method to do this. I have a table where people enter some info into the table. The more entries they add the more points they get. (1 point per entry). I would like to allow the users to be able to see where they stand rank wise with everyone else. Right now I basically do a SELECT count(1) as entries, user_id GROUP BY user_id ORDER BY entries DESC. Then loop through the results until I match their user_id and count how many times I go through the loop and that is how I can give them their ranking. It just seems like a big waste to loop through the results until I find their user_id. I was wondering if anyone could think of a better way to do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculating User Ranks (SQL Query Question)
Try this: CREATE TEMPORARY TABLE tmpRankings ( Rank int auto_increment, entries int, user_id int ) INSERT tmpRankings (points, user_id) SELECT count(1), user_id FROM sometablenamehere GROUP BY user_id ORDER BY entries DESC; This way the tmpRankings table contains an ordered, numbered list of all of your user_id's (in proper ranking order). If you change tmpRankings from a Temporary table to a permanent table you could rebuild it each time someone makes a new entry. That would save you the overhead of recomputing the entire table each time someone just wants to know their ranking but the data hasn't changed because nobody has made any entries since the last check. There are other tweaks you can do to save recomputes, too, but this is the general idea. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 12/22/2004 04:00:45 PM: Im trying to come up with a more efficient method to do this. I have a table where people enter some info into the table. The more entries they add the more points they get. (1 point per entry). I would like to allow the users to be able to see where they stand rank wise with everyone else. Right now I basically do a SELECT count(1) as entries, user_id GROUP BY user_id ORDER BY entries DESC. Then loop through the results until I match their user_id and count how many times I go through the loop and that is how I can give them their ranking. It just seems like a big waste to loop through the results until I find their user_id. I was wondering if anyone could think of a better way to do this. Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext + order by strategy
Since you are already splitting your data into fulltext columns and
other data I would keep that design. Others on the list have greatly
improved their performance by running their tables in this same fashion.
If by some chance your non-fulltext columns are all fixed width, there
is another speed boost just by changing your design.
I think you are on the right track to an optimal storage solution.
However, I would not duplicate all of your fields in your fulltext
table, only the Primary key and the actual FT data needs to be there. The
rest of the data is already on your non-FT table so no need to double up
on your storage.
Of course, I could suggest more concrete examples if you actually post the
table designs you have now (SHOW CREATE TABLE tablename\G) so that I could
refer to your tables by their real names and columns.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Aleksandr Guidrevitch [EMAIL PROTECTED] wrote on 12/22/2004 03:47:39 PM:
Hi there,
Is there any common strategy (of using indexes)
to avoid filesorts while sorting searches by a field ?
Eg. I want fulltext search, then sort results by
some other table field.
For example .:
create table article (
id int not null primary key auto_increment,
sort1 int not null,
sort2 int not null,
description text not null,
FULLTEXT description (description)
)
explain select id from article where match (description) against
(''in boolean mode) order by sort1;
mysql mysql explain select id from article where match (description)
against ('' in boolean mode) order by sort1;
++-+-+--+---
+-+-+--+--+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-+-+--+---
+-+-+--+--+-+
| 1 | SIMPLE | article | fulltext | description | description
| 0 | |1 | Using where; Using filesort |
++-+-+--+---
+-+-+--+--+-+
It doesn't matter whether I have index on sort1, filesort is always
used.
Furthermore, I've noted that adding an index slows the query down
with each index added (suppose becuase of table index becomes bigger).
I come to a solution where I create a separate table (exact copy) with
only
fulltext indexes, then query it with order by LEFT JOIN with original
table,
eg:
SELECT article.id, article.description FROM search
LEFT join article
ON search.id = article.id
WHERE
MATCH (description) AGAINST ('some' IN BOOLEAN MODE)
ORDER BY search.sort1
LIMIT 0, 50;
Also, I intensively use table article for listing (w/o search)
items sorted by sort1 or sort2 fields, so in article table
indexes for sort1 and sort2 are absolutely necessary.
So currently I see only 2 solutions
1. add to the article table ndescription
field (with stemmed description), and fulltext index on it
2. create a separate table for this purpose.
I hope there is a 3rd variant, a good idea on how to
avoid filesort
Sincerely,
Aleksandr
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: MUTIPLY function?]
[EMAIL PROTECTED] wrote: Michael Stassen wrote: EXP(SUM(LOG(COALESCE(*the field you want to multiply*,1))) which works because LOG(xy) = LOG(x) + LOG(y), and EXP(LOG(x)) = x. Hence, sum of the logs = log of the product, so exp of the sum of the logs = exp of the log of the product = the product. What if the field contains the value of 0, than it doesn't works. 3*0 = 0, but it returns 1. Alex, you are correct. That particular formula cannot handle ANY non-positive number because the LOG() function is undefined for values less than or equal to zero. I just reviewed the archives and realized that this point has never been discussed before (I thought it had). Good catch. That's way I reposted it, because the data I need to multiply has sometimes 0 in it. I already searched the archives and googled for it, but no answer found. I would save a lot of headache if I could save this problem. In the interest of giving credit where credit is due, here is Harald's original answer: http://lists.mysql.com/mysql/166172 Yes, a lot of credit to him. It helped me alot, and with me more people I guess. AC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CRITICAL BUG in 4.1: innodb_file_per_table can corrupt secondary indexes
Hi! http://bugs.mysql.com/bug.php?id=7496 A critical bug found: if one uses the 4.1 my.cnf option innodb_file_per_table to create tables, and some of the secondary index records are inserted to the InnoDB 'insert buffer', then after a normal mysqld shutdown InnoDB loses all those secondary index records! CHECK TABLE will print to the mysqld .err log that there are less records in the secondary index than in the clustered index. [EMAIL PROTECTED]:~/mysql-4.1/sql ./mysqld 041222 17:14:36 InnoDB: Started; log sequence number 0 128463738 041222 17:14:36 [Warning] mysql.user table is not updated to new password format ; Disabling new password usage until mysql_fix_privilege_tables is run ./mysqld: ready for connections. Version: '4.1.9-debug-log' socket: '/home/heikki/bugsocket' port: 3307 Source distribution Error: index `idx_symbole` of table `test/alex1` contains 185811 entries, should be 185956 Error: index `idx_symbole3` of table `test/alex1` contains 184246 entries, shoul d be 185956 The corruption does not occur after a mysqld crash. It only follows from a normal shutdown. Workarounds: 1) Only 'shut down' mysqld with killall -9 mysqld :). or 2) Before shutdown, let the server be idle, so that SHOW INNODB STATUS shows the 'main thread waiting for server activity'. Then a normal shutdown is safe. How to repeat: Do heavy random inserts to the secondary indexes of a big table. Then some of the inserts end up in the insert buffer. Shut down mysqld quickly. At the next startup the table is corrupt. Suggested fix: Will be fixed in 4.1.9. We let InnoDB do a 'crash-like' startup always. This is the worst InnoDB corruption bug in 3 years. I apologize that it slipped through tests. Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
last_insert_id() and load data infile
Hi, I am trying to find in the manual what select last_insert_id() will return after a load data infile command is executed. From my experiments, it returns the id of the FIRST record in the file, I would have expected the LAST, but that is not the case. Is that documented somewhere? Can I rely on this behavior to be consistent? Thanks, Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext + order by strategy
[EMAIL PROTECTED] wrote:
Since you are already splitting your data into fulltext columns and
other data I would keep that design. Others on the list have greatly
improved their performance by running their tables in this same
fashion. If by some chance your non-fulltext columns are all fixed
width, there is another speed boost just by changing your design.
Well, is there any possibility to make text column fixed width I don't
know about ??? ;-)
I would prefer to have one, since 12Kb is the top limit for my application.
I think you are on the right track to an optimal storage solution.
However, I would not duplicate all of your fields in your fulltext
table, only the Primary key and the actual FT data needs to be there.
The rest of the data is already on your non-FT table so no need to
double up on your storage.
Of course, I could suggest more concrete examples if you actually post
the table designs you have now (SHOW CREATE TABLE tablename\G) so that
I could refer to your tables by their real names and columns.
I haven't decided yet the table structure, but here is what I'm playing
with:
// title and description are stemmed title and description
// accordingly
DROP TABLE IF EXISTS search;
CREATE TABLE search (
lot_idint unsigned not null primary key,
category_id int unsigned not null,
current_price decimal(16,2) unsigned not null,
end_time int unsigned not null,
bid_count int unsigned not null,
title char(50) not null,
description text not null,
FULLTEXT ttitle (title),
FULLTEXT ttitle_description (title, description)
) TYPE = MyISAM COMMENT = Search table;
// ntitle and ndescription are stemmed title and description
// accordingly
DROP TABLE IF EXISTS lot;
CREATE TABLE lot (
id int unsigned not null primary key,
owner_id int unsigned not null default 0,
titlechar(50) not null,
description text not null,
ntitlechar(50) not null,
ndescription text not null,
current_pricedecimal(16,2) unsigned not null,
reserve_pricedecimal(16,2) unsigned not null,
buy_pricedecimal(16,2) unsigned not null,
quantity int unsigned not null,
sold int unsigned not null,
bid_countint unsigned not null,
start_time int unsigned not null,
end_time int unsigned not null,
city char(35) not null,
type_id int unsigned not null,
category_id int unsigned not null,
currency_id int unsigned not null,
country_id int unsigned not null,
state_id int unsigned not null,
delivery_id int unsigned not null,
enabled bool default 1 not null,
INDEX title (title),
INDEX current_price (current_price),
INDEX bid_count (bid_count),
INDEX end_time (end_time),
INDEX enabled (enabled),
FULLTEXT ttitle (ntitle),
FULLTEXT ttitle_description (ntitle, ndescription)
) TYPE = MyISAM COMMENT = Lots;
// this is a helper table
// to query all subcategories of a category
// in one query
DROP TABLE IF EXISTS category_map;
CREATE TABLE category_map (
parent_id int unsigned not null,
child_id int unsigned not null,
INDEX parent_id (parent_id),
INDEX child_id (child_id)
) TYPE = MyISAM COMMENT = Categories Map;
Here are the queries I'm playing with (search is performed against 50
000 rows).
The table is filled in with random data generated via use of wordlists.
The length of description field is not more of 12Kb but really close
1.
SELECT SQL_CALC_FOUND_ROWS lot.id, lot.title
FROM search
INNER JOIN lot
ON lot.id = search.lot_id
AND lot.enabled = 1
WHERE
MATCH(search.title, search.description) AGAINST ('someword' IN
BOOLEAN MODE)
ORDER BY search.end_time
LIMIT 0, 50
...
50 rows in set (4.22 sec)
mysql EXPLAIN SELECT SQL_CALC_FOUND_ROWS lot.id, lot.title FROM
search INNER JOIN lot ON lot.id = search.lot_id
AND lot.enabled = 1 WHERE MATCH(search.title, search.description)
AGAINST ('someword' IN BOOLEAN MODE) ORDER BY search.end_time LIMIT 0, 50;
++-++--+---++-++--+-+
| id | select_type | table | type |
possible_keys | key| key_len |
ref| rows | Extra |
MySQL inadvertently messing with the path in the Windows' registry?
Hello. After installing the latest GA (Generally Available) version, 4.1.8, of the MySQL Server [1] on my Windows XP SP2 system, I noticed something strange with the PATH environment variable. Upon opening a command prompt window, I could not directly execute any of the usual applications (ipconfig, net, etc.) which reside in the Windows' subdirectories. Examining the environment variables, I noticed that the special value %SystemRoot% was not being expanded to C:\Windows as it used to. Some googling revealed that this usually happens when some application inadvertently (or not) changes the type of the value Path under the key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment in the registry, from REG_EXPAND_SZ to REG_SZ. When this happens, the system does not expand the special values in the string and thus, path entries using them no longer work. I write to ask whether anybody else has also noticed this behavior on their systems, as I am still not completely sure it is caused by MySQL. I will post again, if I find any conclusive evidence. [1] http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-4.1.8-essential-win.msi/from/pick Regards, -- Ney André de Mello Zunino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copying DB and full-text search files from one server to another
Is it possible to copy the database files (both MyISAM and InnoDB) that contain tables, indices, and full-text indices from one MySQL server to another running/active MySQL server and start using them right away? I appreciate your help! Homam __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
simple subquery syntax not working!
I have read through quite a bit of the MySQL manual concerning subqueries and cannot seem to get even the simplist of queries to work! I have a table called PersonRole which is the many-to-many joint table between two upper tables, Person and Role. I have tested the two pieces of the query alone and they work fine. (e.g. if I enter a 5 after the equal sign, it works and if I just type the subquery alone, it returns the single number 5.) So, why does mysql give me a syntax error? --- SELECT personRoleDBNum FROM PersonRole WHERE roleDBNum = (SELECT roleDBNum FROM Role WHERE role = 'STAFF') ERROR 1064: 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 'SELECT roleDBNum FROM Role WHERE role = 'STAFF')' at line 2 --- I must be dumb or just really really confused. Any help would be greatly appreciated. Renee = Renee Henderson -- [EMAIL PROTECTED] (WA state) Computer Programmer / Internet Specialist When you were born, you cried and the world rejoiced. Live your life in such a way that when you die, the world cries, and you rejoice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple subquery syntax not working!
Make sure the subquery returns a scalar value. Either the roleDBNum column has to be unique or you need to apply an aggregate function to reduce to a single value. If the the subquery returns many values, use the IN operator instead of =. Disclaimier: I'm still new to MySQL, but I'm assuming it works the same way as SQL Server :) --- Renee Henderson [EMAIL PROTECTED] wrote: I have read through quite a bit of the MySQL manual concerning subqueries and cannot seem to get even the simplist of queries to work! I have a table called PersonRole which is the many-to-many joint table between two upper tables, Person and Role. I have tested the two pieces of the query alone and they work fine. (e.g. if I enter a 5 after the equal sign, it works and if I just type the subquery alone, it returns the single number 5.) So, why does mysql give me a syntax error? --- SELECT personRoleDBNum FROM PersonRole WHERE roleDBNum = (SELECT roleDBNum FROM Role WHERE role = 'STAFF') ERROR 1064: 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 'SELECT roleDBNum FROM Role WHERE role = 'STAFF')' at line 2 --- I must be dumb or just really really confused. Any help would be greatly appreciated. Renee = Renee Henderson -- [EMAIL PROTECTED] (WA state) Computer Programmer / Internet Specialist When you were born, you cried and the world rejoiced. Live your life in such a way that when you die, the world cries, and you rejoice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: simple subquery syntax not working!
Hi Renee, Check the version of your MySQL. The subqueries feature is available from MySQL 4.1 Hope this helps you. - Harish -Original Message- From: Renee Henderson [mailto:[EMAIL PROTECTED] Sent: Thursday, December 23, 2004 6:17 AM To: mysql@lists.mysql.com Subject: simple subquery syntax not working! I have read through quite a bit of the MySQL manual concerning subqueries and cannot seem to get even the simplist of queries to work! I have a table called PersonRole which is the many-to-many joint table between two upper tables, Person and Role. I have tested the two pieces of the query alone and they work fine. (e.g. if I enter a 5 after the equal sign, it works and if I just type the subquery alone, it returns the single number 5.) So, why does mysql give me a syntax error? --- SELECT personRoleDBNum FROM PersonRole WHERE roleDBNum = (SELECT roleDBNum FROM Role WHERE role = 'STAFF') ERROR 1064: 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 'SELECT roleDBNum FROM Role WHERE role = 'STAFF')' at line 2 --- I must be dumb or just really really confused. Any help would be greatly appreciated. Renee = Renee Henderson -- [EMAIL PROTECTED] (WA state) Computer Programmer / Internet Specialist When you were born, you cried and the world rejoiced. Live your life in such a way that when you die, the world cries, and you rejoice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL inadvertently messing with the path in the Windows' registry?
Ney André de Mello Zunino wrote: I will post again, if I find any conclusive evidence. I have just obtained that conclusive evidence. The MySQL installer is indeed messing up the type of the /Path/ value on the registry, changing it from REG_EXPAND_SZ to REG_SZ. The problem will only take place when you choose to add the /bin/ directory of MySQL to the path, during the installation. Do the developers read these messages as well? If not, where should I report the problem? Thank you, -- Ney André de Mello Zunino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
scanning 2 rows slow index fast 26GB MyISAM
I am experiencing extreme slowness performing a query in which 2 rows are returned hanging in the sending data status. Performing an index only query such as SELECT COUNT(*) is extremely quick so I know the only extra step is retrieving the data from the MYD. I am looking for thoughts on why this is slow and what can be done to speed it up. I find it unusual why it would take this long to simply grab 2 rows from the MYD. vmstat reports high reads and strace confirms pread()'s on the MYD file. The only abnormality is my table size MYD is 26 gig and my MYI is about 30 gig. Test system details, tests were performed with no load. System: Redhat Linux 2.4.28 Mysql: tested on versions 4.0.22 and latest 4.1 tree IDE (WD 2500 JB 8 mb buff) disk1 used for MYD (written contiguously at beginning of disk) IDE (WD 2500 JB 8 mb buff) disk2 used for MYI dual xeon 2.4ghz 1gb ddr266 mem Here are query tests detail below: mysql select count(*) from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date now() - interval 10 day; +--+ | count(*) | +--+ |2 | +--+ 1 row in set (0.06 sec) mysql select locationid from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date now() - interval 10 day; ++ | locationid | ++ | 47932 | | 29571 | ++ 2 rows in set (5 min 35.93 sec) mysql explain select locationid from matrix where accountid = 11 and wordid = 71 and position = 'Body' and date now() - interval 10 day; ++--+---+---+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+---+-+---+---+-+ | matrix | ref | myKey | myKey | 9 | const,const,const | 56909 | Using where | ++--+---+---+-+---+---+-+ CREATE TABLE `location` ( `LocationID` int(11) unsigned NOT NULL auto_increment, `ImapUID` int(11) unsigned NOT NULL default '0', `AccountID` int(11) unsigned NOT NULL default '0', `Date` timestamp(19) NOT NULL, `FromWho` tinyblob, `Subject` tinyblob, `SentTo` tinyblob, `mailbox` varchar(255) default NULL, `body` longblob, PRIMARY KEY (`LocationID`), KEY `myKey` (`LocationID`,`AccountID`,`Date`) ) TYPE=MyISAM MAX_ROWS=10 AVG_ROW_LENGTH=300 DATA DIRECTORY='/home/SQL/' INDEX DIRECTORY='/home/SQL/' Best regards, Bryan Heitman FuseMail Team -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access deined for user 'root@sales'
I'm running v 4.0.22 on Win XP 2003 Server with a client running XP Pro.
I have mysql running on 192.168.0.8 and I want to administer it from
192.168.0.202 (aka SALES).
While on 192.168.0.8 running mysql, I issued the following commands:
mysql GRANT ALL ON *.* TO 'root'@'192.168.0.22';
Query OK, 0 rows affected (0.02 sec)
mysql GRANT ALL ON *.* TO 'root'@'192.168.0.202';
Query OK, 0 rows affected (0.01 sec)
mysql GRANT ALL ON *.* TO 'root'@'SALES';
Query OK, 0 rows affected (0.00 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.49 sec)
mysql SET PASSWORD FOR 'root'@'SALES' = PASSWORD('xyzabc');
Query OK, 0 rows affected (0.12 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql GRANT ALL ON *.* TO 'root'@'SALES';
Query OK, 0 rows affected (0.00 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.09 sec)
mysql exit
Bye
However, I still get Error 1045: Access denied for user: '[EMAIL PROTECTED]'
(using password:YES) when using the command
Mysql -u root -p -h 192.168.0.8
What am I doing wrong? Incidentally, this is an internal network so there is
no firewall.
Thanks!
Siegfried
