migrating 4.0 to 5.0
hi, i've still serious trouble in migrating databases createted with 4.0 to 5.0. the problems is still the charset. i'm connecting to mysql with php and when i try to use the 5.0 db german special chars are messed up. afaik 4.0 uses latin charset and 5.0 utf8 by default. can some one please give me a hint how to migrate ? i think i'm just too stupid :-) TIA matthias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: migrating 4.0 to 5.0
On Mon, Mar 05, 2007 at 08:30:13AM +0100, Matthias Henze wrote: hi, i've still serious trouble in migrating databases createted with 4.0 to 5.0. the problems is still the charset. i'm connecting to mysql with php and when i try to use the 5.0 db german special chars are messed up. afaik 4.0 uses latin charset and 5.0 utf8 by default. can some one please give me a hint how to migrate ? i think i'm just too stupid :-) Hi... convert your mysql-dumpl.sql or whatever with iconv to utf-8. Greetings Mario -- - | havelsoft.com - Ihr Service Partner für Open Source | | Tel: 033876-21 966 | | Notruf: 0173-277 33 60 | | http://www.havelsoft.com| | | | Inhaber: Mario Günterberg | | Mützlitzer Strasse 19 | | 14715 Märkisch Luch | - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help me optimize this ALL
sql: SELECT * FROM table_one INNER JOIN table_one table_one1 ON table_one1.column_one = table_one.column_two INNER JOIN table_one table_one2 ON table_one2.column_one = table_one.column_three explain: *** 1. row *** id: 1 select_type: SIMPLE table: table_one type: ALL possible_keys: idx_column_two,idx_column_three key: NULL key_len: NULL ref: NULL rows: 2037 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: table_one1 type: ref possible_keys: idx_column_one key: idx_column_one key_len: 5 ref: table_one.column_two rows: 1 Extra: Using where *** 3. row *** id: 1 select_type: SIMPLE table: table_one2 type: ref possible_keys: idx_column_one key: idx_column_one key_len: 5 ref: table_one.column_three rows: 1 Extra: Using where Can I optimize this ALL on table one? thanks!
Re: migrating 4.0 to 5.0
Hi, i've still serious trouble in migrating databases createted with 4.0 to 5.0. the problems is still the charset. i'm connecting to mysql with php and when i try to use the 5.0 db german special chars are messed up. afaik 4.0 uses latin charset and 5.0 utf8 by default. can some one please give me a hint how to migrate ? i think i'm just too stupid :-) Why not set your database to latin instead? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transaction/locking confusion
Hi, I have a simple PHP function that initialises a process definition. To prevent it happening more than once, I'm wrapping it in a transaction, however, it doesn't seem to be working and I get multiple initialisations. In pseudocode: BEGIN; UPDATE process SET status = 'ready' WHERE id = 123 AND status = 'init' LIMIT 1; ...do other stuff including some INSERTs if other stuff is OK: COMMIT; else ROLLBACK; If I have two simultaneous processes running this script, somehow they are both able to initialise. I guess that if the overlapping transactions are isolated, then both see the process in the 'init' status and that the 'other stuff' part takes long enough that it's reasonably likely to happen. I was under the impression that the UPDATE inside the transaction would lock the row and prevent the later query from succeeding, but it seems that's not how it works. How can I prevent this situation? Do I need to lock the row explicitly? Why doesn't the transaction provide sufficient isolation? Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [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: migrating 4.0 to 5.0
Am Montag, 5. März 2007 14:35 schrieb Martijn Tonies: Hi, i've still serious trouble in migrating databases createted with 4.0 to 5.0. the problems is still the charset. i'm connecting to mysql with php and when i try to use the 5.0 db german special chars are messed up. afaik 4.0 uses latin charset and 5.0 utf8 by default. can some one please give me a hint how to migrate ? i think i'm just too stupid :-) Why not set your database to latin instead? tried this with out success - how to do it right ? reagrdless what my.cnf is set to e.g. phpmyadmin reports to me that the db charset is UTF8. when i look at the tables they show up as latin1 but my own php apps do not who german special chars as they used to ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does casing of Table names matter on Linux
On 3/5/07, abhishek jain [EMAIL PROTECTED] wrote: On 3/1/07, abhishek jain [EMAIL PROTECTED] wrote: On 2/28/07, Ow Mun Heng [EMAIL PROTECTED] wrote: On Wed, 2007-02-28 at 15:05 +0530, abhishek jain wrote: Hi Friends, I have been developing one application over Windows and now i want to move/port that into linux, i want to know will the casing of the table name matter on linux ie if i have table name as tab1 and i execute query like select * from TAb1 ,will it make an effect. I have found that this is not a problem on windows but it is on linux, i want a sol. for that a i can not change all the table names as in some places it is in small case and in other places it is in capital case. Yes.. There is a difference. Casing Matters in *nix. However there is an option for turning if off. # http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html lower_case_table_names = 1 Hi Friends, Thanks for the mails. Now i have made a mistake.In mine code i have somwhere capital case and in other place the small case for the table names. I cannot change the case in mine code and cannot resist the change to the Linux . What is the remedy now i mean can something be done now to make linux be case insensitive, Pl. help me, Thanks, Abhishek jain So friends , If anyone know abt the sol. pl. let me know Thanks, Abhishek jain
Re: Does casing of Table names matter on Linux
abhishek jain wrote: On 3/5/07, abhishek jain [EMAIL PROTECTED] wrote: On 3/1/07, abhishek jain [EMAIL PROTECTED] wrote: On 2/28/07, Ow Mun Heng [EMAIL PROTECTED] wrote: On Wed, 2007-02-28 at 15:05 +0530, abhishek jain wrote: Hi Friends, I have been developing one application over Windows and now i want to move/port that into linux, i want to know will the casing of the table name matter on linux ie if i have table name as tab1 and i execute query like select * from TAb1 ,will it make an effect. I have found that this is not a problem on windows but it is on linux, i want a sol. for that a i can not change all the table names as in some places it is in small case and in other places it is in capital case. Yes.. There is a difference. Casing Matters in *nix. However there is an option for turning if off. # http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html lower_case_table_names = 1 Hi Friends, Thanks for the mails. Now i have made a mistake.In mine code i have somwhere capital case and in other place the small case for the table names. I cannot change the case in mine code and cannot resist the change to the Linux . What is the remedy now i mean can something be done now to make linux be case insensitive, Pl. help me, Thanks, Abhishek jain So friends , If anyone know abt the sol. pl. let me know Thanks, Abhishek jain The solution is in the link given above! -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Specific mysql Trigger question
mysql ver 5.0.26 standard Can a trigger call a procedure that returns a result set? I am trying to get around this error msg. ERROR 1415 (0A000): Not allowed to return a result set from a trigger Thanks -winn Below is a more concise description of my issue and logic pattern when a record is updated a trigger and procedure must be fired off. if statements if the NEW.total_amount donated by contact_id is less then the current amount and the donation_id is not equal to the current max donation_id then do nothing to the max_donation table. done if the NEW.total_amount donated by contact_id is equal to the current amount donated and the donation id is not equal to current max donation then insert NEW.donation_id and contact_id into the max_donation table done if the NEW.total_amount donated is greater then the current max donation and the donation id does not equal the current max donation_id then update the max_donation table with the NEW.donation_id and amount done if the NEW.total_amount donated is less then the current max donation and the donation_id is equal to the current donation_id in the max donation table then run a procedure to populate with the right information not done so i need a procedure i can call from an after_update_trigger that will, when called search though the donation_table for all donations made by contact_id and return donation_id(s) of all records that equal the max donation. For example if contact_id makes 5 donations equal to 500.00 i need the procedure to store all of the donation_id's = $500.00 for entry into the max_donation table. Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: migrating 4.0 to 5.0
i've still serious trouble in migrating databases createted with 4.0 to 5.0. the problems is still the charset. i'm connecting to mysql with php and when i try to use the 5.0 db german special chars are messed up. afaik 4.0 uses latin charset and 5.0 utf8 by default. can some one please give me a hint how to migrate ? i think i'm just too stupid :-) Why not set your database to latin instead? tried this with out success - how to do it right ? reagrdless what my.cnf is set to e.g. phpmyadmin reports to me that the db charset is UTF8. when i look at the tables they show up as latin1 but my own php apps do not who german special chars as they used to ... If the characterset for your tables is latin, then this should be OK. But do you also specify the connection/client characterset? If your server defaults to utf8, setting it after connecting might be required. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help me optimize this ALL
No, because you have no WHERE condition. wangxu wrote: sql: SELECT * FROM table_one INNER JOIN table_one table_one1 ON table_one1.column_one = table_one.column_two INNER JOIN table_one table_one2 ON table_one2.column_one = table_one.column_three explain: *** 1. row *** id: 1 select_type: SIMPLE table: table_one type: ALL possible_keys: idx_column_two,idx_column_three key: NULL key_len: NULL ref: NULL rows: 2037 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: table_one1 type: ref possible_keys: idx_column_one key: idx_column_one key_len: 5 ref: table_one.column_two rows: 1 Extra: Using where *** 3. row *** id: 1 select_type: SIMPLE table: table_one2 type: ref possible_keys: idx_column_one key: idx_column_one key_len: 5 ref: table_one.column_three rows: 1 Extra: Using where Can I optimize this ALL on table one? thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transaction/locking confusion
Hi, I have a simple PHP function that initialises a process definition. To prevent it happening more than once, I'm wrapping it in a transaction, however, it doesn't seem to be working and I get multiple initialisations. In pseudocode: BEGIN; UPDATE process SET status = 'ready' WHERE id = 123 AND status = 'init' LIMIT 1; ...do other stuff including some INSERTs if other stuff is OK: COMMIT; else ROLLBACK; If I have two simultaneous processes running this script, somehow they are both able to initialise. I guess that if the overlapping transactions are isolated, then both see the process in the 'init' status and that the 'other stuff' part takes long enough that it's reasonably likely to happen. I was under the impression that the UPDATE inside the transaction would lock the row and prevent the later query from succeeding, but it seems that's not how it works. How can I prevent this situation? Do I need to lock the row explicitly? Why doesn't the transaction provide sufficient isolation? Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [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: List of ports which should be opened for IPTABLES
When it comes to clustering.. doesn't matter if it is MySQL, MS SQL Server, MS Exchange or a big bad Beowulf Cluster you need an independent switch for the cluster. Here is why, There is so much traffic to and from the nodes, and on the network that it will slow down your network, and the cluster. A cluster has more communication on it than just serving your data, it has management information as well. Our typical setup for a cluster (OS and Job independent) is usually a 10/100/1000 switch, noting fancy like a managed switch but quality (not putting in a $40 switch). We also do not allow switches to be over 75% used, if a 12 port switch has more than 9 ports used we replace it with a 24 port. Typically we also use a rack to house all the clustered boxes with nothing else not required for the cluster in there. The exception is when it is only 2 boxes in the cluster then we just rack them next to each other and instead of a switch we use a fiber connection between the two boxes. We set up a lot of clusters for various Operating systems and servers and these are our general rules of set up. These simple rules have never let us down when it comes to clustering. When cluster traffic is running outside the cluster you are asking for network performance issues.
Re: Replication performance questions
On Fri, Mar 02, 2007 at 12:57:25AM -0800, Gary W. Smith wrote: Pdns? Close! bind-dlz Anyway, did you enable the slow query logging? That still might give you an idea if something is running slow. But I also forgot to ask earlier, what is running slow, the inserts or the selects during the inserts? I've been trying to determine if there's a way to enable slow query logging at runtime. I've made the changes to the config file, but restarting the database for this stuff is a little dicey and with the stakes being DNS, I'd rather be conservative! set long_query_time=1; doesnt' seem to have created the slow query file, so I'm guessing this means a restart? -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help me optimize this ALL
thank you - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, March 06, 2007 4:29 AM Subject: Re: help me optimize this ALL No, because you have no WHERE condition. wangxu wrote: sql: SELECT * FROM table_one INNER JOIN table_one table_one1 ON table_one1.column_one = table_one.column_two INNER JOIN table_one table_one2 ON table_one2.column_one = table_one.column_three explain: *** 1. row *** id: 1 select_type: SIMPLE table: table_one type: ALL possible_keys: idx_column_two,idx_column_three key: NULL key_len: NULL ref: NULL rows: 2037 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: table_one1 type: ref possible_keys: idx_column_one key: idx_column_one key_len: 5 ref: table_one.column_two rows: 1 Extra: Using where *** 3. row *** id: 1 select_type: SIMPLE table: table_one2 type: ref possible_keys: idx_column_one key: idx_column_one key_len: 5 ref: table_one.column_three rows: 1 Extra: Using where Can I optimize this ALL on table one? thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
*.MYI was corrupted with OS crash when lots of rows were inserting
Hi all, when I insert many records into a myisam type table, at the same time, unplugged the power cable, the os crash, I found several *.MYI file was corrupted, but can use repair or myisamchk to repair those table. Is there any way to avoid this happen? my env: OS---Linux-2.6.9smp for i386 HWIntel Xeon 3.0Ghz, 4G Ram, 120G SATA HD MySQL-5.0.27 thanks! - Mp3疯狂搜-新歌热歌高速下
Google like search string to be implemented
Hi Friends, I am having a database with varchar(255) columns named title, extra_info1,extra_info2,extra_info3 . I want to search all these columns with a search string given to me via a form ,I am using PERL, the string will be like +abhishek jain -abcd this should be exact As you can see the word/words prefixed with + should be there somewhere in the above columns in database ,with - should not be there and within they should be exact phrase. I want a sol. for this, Pl. help me how to make a search queruy will it be in regex or simople like and not like , i will be having about 1 lac records in the database. Thanks, Abhishek jain
