Re[2]: Different default collation in each connection
Hi Jeremy, P.S2 I store data in one table which has no default collation set only UTF-8 charset. In this table I can have data in different collations. Every table does have a default collation either you specify it or it comes from the default collation for your database or the server default. You need to either: 1. set your table to use utf8_polish_ci or if you need more than one collation in your table 2. set the collation for the Polish columns to utf8_polish_ci. Otherwise you are probably using the default collation for utf8 which is either utf8_general_ci or utf8_unicode_ci I forget which. Collation _can_ be set for individual columns and it sounds like that is what you need to do. This is also important because each column index is collation specific and you need your columns to be indexed for the right collation. Ok, I thought that I will be misunderstood... I'll try to explain this in example below: I have table CREATE TABLE `product_descriptions` ( `id_product_description` mediumint(8) unsigned NOT NULL auto_increment, `id_product` mediumint(8) unsigned NOT NULL default '0', `lang` varchar(2) default NULL, `text_data` text, PRIMARY KEY (`id_product_description`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 in column `text_data` i have all texts in all languages (pl, en, ru etc.) So the problem is that this column is meant to store every collation for this language but in UTF-8. Now, when I get records from this table I should give database some info that I will use i.e. 'utf_8_polish_ci' collation on whole page - I don't need to change it on whole page (I use PHP by the way) This solution I use is very handy as when I need to add some language to my pages then I only add some info in application about what language this would be and... this should work. I can't change database structure on every language upgrade. Besides I would have for example 20 columns with different collations like: text_data_pl text_data_ru text_data_ro text_data_en text_data_... and this table is not the only one which stores information in different languages... So please consider this and please if you know tell me what collation_connection is for. -- Use the force - read the source Piotr Duszynski mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cast a value as datetime using Mysql prior to 4.0
Hi. In MySql 4.0 you can use the function CAST to cast a value as datetime: CAST(value AS DATETIME), but in versions of MySql you can't. How can I do this cast in other versions of MySql? Iago. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable top drop table, error 1051
Egor Egorov wrote: Bug filled: http://bugs.mysql.com/bug.php?id=5784 Thank you! Nice, thanks to you too ;-) regards, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auto indexing
Hi. I'm relatively new to the database systems. I've read from tutorials how to create indexes in tables. How can I refresh the created index of a table as I insert entries into it (in order to reflect the added entry in the index)? Any help would be very much appreciated. Thanks! Clinton Lopez ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto indexing
clinton lopez [EMAIL PROTECTED] wrote on 29/09/2004 09:54:04: Hi. I'm relatively new to the database systems. I've read from tutorials how to create indexes in tables. How can I refresh the created index of a table as I insert entries into it (in order to reflect the added entry in the index)? You don't - it all happens automatically. That is what databases are for. Once you have declared that there should be an index, it is the responsiblity of the database to ensure that the index is at all times a correct reflection of the stored data. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql logs too much
Does mysql 4.0 onwards log cached querries also or only direct non cached database access queries? i am getting enormous amount of log in the form of querries. ours is a databse driven website using mysql and php.thanks for the reply in advance Thanks Regards Kausalya Ramaswamy Scientific Officer - C Computer Centre, TIFR, Mumbai - 45. INDIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie installation question
sorry for spamming the list with this, i tried asking on the forums, but didn't receive a reply. i'm a complete newbie to mysql/php, but have programmed professionally (in C/C++/ASM) for about 9 years now. i originally installed mysql into C:/Program Files/mysql, before deleting and reinstalling in the default subdirectory, which meant some paths in the registry had been setup incorrectly. i changed these manually (2 of them located), and this fixed a few issues. however after reinstalling, when i do a mysqlshow, it shows only test in the list of available databases, and nothing i seem to change makes a difference. i edited the my.ini to the base+base/data directories, but that didn't change anything. i even tried changing the paths in my.ini to invalid paths, and that didn't change anything either (it still located test, and test only). i searched for a test subdirectory elsewhere on my hard drive, but failed to locate one. i then created a new database, which it did in the correct subdirectory, so since it seems to be writing to the correct subdirectory, why on earth doesn't it read from the directory as well? i have administrator access to the machine. any help is much appreciated, cheers.
CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
Hi there, I'm testing MySQL 5.0.1 a bit - and I noticed the following... When executing this: create view myview2 (t0) as select c1 from t It returns this when doing a SHOW CREATE VIEW myview2: CREATE VIEW test.myview2 AS select `test`.`t`.`c1` AS `t0` from `test`.`t` This is not at all what I entered. I dislike backticks unless I specify them. I don't want a database name included unless I specify it (from `test`.`t`). Can the annoying behaviour be changed or turned off? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ndb_mgmd
Hi I installed these packages on linux MySQL-client-4.1.5-0.i386.rpm MySQL-server-4.1.5-0.i386.rpm MySQL-Max-4.1.5-0.i386.rpm I want to run mysql in a cluster, however I cant find ndb_mgmd Anything I should have installed and didnt ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: merging of two tables using temp tables???
thanks for the response... however, the attempt at using the left join/where construct was what i had initially tried, with no luck. (which is not to say i was implementing it correctly!!) using the basic select * from/left join on /where seems to give a resulting table that only contains a subset of the two tables. i'd like more of a merge. a thought i had was to do a simple merge, and then somehow do an operation which would give me the rows that weren't in the initial merge, followed by a 3rd operation that would then join the 1st two results my gut tells me i'm making this too complex because i can't figure out how to properly/correctly accomplish the original merging process/function to combine the initial two tables thanks -bruce i have a situation where i create the following tables via two different select sql statements. +-+--+---+--+-+ | ID | type | user | ID | uID | +-+--+---+--+-+ | 40 |1 | admin | 157 | 40 | | 102 |1 | admin | 405 | 102 | | 257 |1 | admin | 1025 | 257 | | 267 |1 | admin | 1065 | 267 | | 379 |1 | admin | 1513 | 379 | +-+--+---+--+-+ 5 rows in set (0.00 sec) +--+--++ | ID | type | status | +--+--++ | 40 |1 | 0 | | 40 |2 | 0 | | 40 |3 | 0 | | 40 |4 | 0 | | 102 |1 | 0 | | 102 |2 | 0 | | 102 |3 | 0 | | 102 |4 | 0 | | 257 |1 | 0 | | 257 |2 | 0 | | 257 |3 | 0 | | 257 |4 | 0 | | 267 |1 | 0 | | 267 |2 | 0 | | 267 |3 | 0 | | 267 |4 | 0 | | 379 |1 | 0 | | 379 |2 | 0 | | 379 |3 | 0 | | 379 |4 | 0 | | 394 |1 | 0 | | 394 |2 | 0 | | 394 |3 | 0 | | 394 |4 | 0 | | 460 |1 | 0 | | 460 |2 | 0 | | 460 |3 | 0 | | 460 |4 | 0 | | 541 |1 | 0 | | 541 |2 | 0 | | 541 |3 | 0 | | 541 |4 | 0 | i'd like to be able to merge/combine the two tables so that i get +--+--++--+---+-+ | ID | type | status | user | ID| uID | +--+--++--+---+-+ | 40 |1 | 0 | | 40 |2 | 0 | . | 40 |3 | 0 | . | 40 |4 | 0 | | 102 |1 | 0 | | 102 |2 | 0 | | 102 |3 | 0 | | 102 |4 | 0 | with the appropriate information in the various columns/rows... i'm looking to be able to fill the resulting table with the information if it's present, or to have nulls/'0' where the information isn't available... i'd prefer to do this in mysql if possible, as my gut tells me the operation would be faster/more efficient in mysql, than if i coded this in php/perl... i believe that i's need to create a temp table based on each select, and then some how merge the two temp tables, and finally do a select on the resulting table to get the values i need... looking through google/mysql hasn't shed any light on this one... any ideas/thoughts/comments on how i can do this. -Original Message- From: Jacques Jocelyn [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 28, 2004 10:20 PM To: bruce; [EMAIL PROTECTED] Subject: Re: merging of two tables using temp tables??? Hello bruce, Wednesday, September 29, 2004, 6:57:34 AM, you wrote: b hi b i'd like to be able to merge/combine the two tables so that i get b +--+--++--+---+-+ b | ID | type | status | user | ID| uID | b +--+--++--+---+-+ b | 40 |1 | 0 | b | 40 |2 | 0 | . b | 40 |3 | 0 | . b | 40 |4 | 0 | ... b with the appropriate information in the various columns/rows... b i'm looking to be able to fill the resulting table with the information if b it's present, or to have nulls/'0' where the information isn't available... I was about to say it's easy ;-) then I saw your ps section :-o anyway, the idea I had may give a way to start : insert NEW_TABLE(ID,type,status,user,ID,uID) select ID,type,status,user,ID,uID from table1 left join table2 on ... where ... hope that helps. Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update a portion of text in a field
You'll get many results if you search for mysql update replace in google. You'd be using a combo of update-replace to do what you need. Aman Raheja Jacques Jocelyn wrote: Hello mysql, Something I have been thinking about without any clue on how I can achieve it. I know how to update a field with update table X set field1='My Text' where tableid = 1 Now, say I have in a table X, the field1 with the value : 'I have been searching that functionalities for several days' and I would like to replace 'functionalities' by 'functionality' Would anyone knows how to replace JUST one word or a part of a text in a field without using an external program ? Please advise, thanks Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
best way to restore remote database
Hi all, I have a bunch of tables I need to create on a remote database. The backup files are all in one folder. They are .frm,.myd and.myi files. 1. Do these files need to be on the remote server in order to be used as to create the table? 2. Looking at the user docs on myslq, it appears I need to retrore the tables individually. What's the command to restore all the tables at once? 3. Can I use control center to accomplish this, or deos it have to be done via the command line? Thanks! Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A run away query?
SELECT COUNT(DISTINCT( `cycle_flag`)) as numCycles FROM `crmReserve01`.`tblCDR` WHERE `cycle_flag` LIKE '___20__C_' and when I do an EXPLAIN ++---+---++-+--+ -+--+ | table | type | possible_keys | key| key_len | ref | rows | Extra| ++---+---++-+--+ -+--+ | tblCDR | index | NULL | cycle_flag | 11 | NULL | 3518750 | Using where; Using index | ++---+---++-+--+ -+--+ I have killed these queries, but the processlist shows | 229 | jblanchard | feynman:2017 | NULL | Killed | 393107 | Sending data | SELECT COUNT(DISTINCT( `cycle_flag`)) as numCycles FROM `crmReserve01`.`tblCDR` WHERE `cycle_flag` LIKE '___20__C_' | | 292 | jblanchard | feynman:2210 | NULL | Killed | 306673 | Sending data | SELECT COUNT(DISTINCT( `cycle_flag`)) as numCycles FROM `crmReserve01`.`tblCDR` WHERE `cycle_flag` LIKE '___20__C_' note the time! Have we done something with this query we shouldn't do? TIA! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: merging of two tables using temp tables???
If you post your two original SQL statements (the ones you use to build your example tables) I think I can help you to merge your results, possibly without the need for temporary tables. Also, what version of MySQL are you using? Shawn Green Database Administrator Unimin Corporation - Spruce Pine bruce [EMAIL PROTECTED] wrote on 09/29/2004 12:57:34 AM: hi i have a situation where i create the following tables via two different select sql statements. +-+--+---+--+-+ | ID | type | user | ID | uID | +-+--+---+--+-+ | 40 |1 | admin | 157 | 40 | | 102 |1 | admin | 405 | 102 | | 257 |1 | admin | 1025 | 257 | | 267 |1 | admin | 1065 | 267 | | 379 |1 | admin | 1513 | 379 | +-+--+---+--+-+ 5 rows in set (0.00 sec) +--+--++ | ID | type | status | +--+--++ | 40 |1 | 0 | | 40 |2 | 0 | | 40 |3 | 0 | | 40 |4 | 0 | | 102 |1 | 0 | | 102 |2 | 0 | | 102 |3 | 0 | | 102 |4 | 0 | | 257 |1 | 0 | | 257 |2 | 0 | | 257 |3 | 0 | | 257 |4 | 0 | | 267 |1 | 0 | | 267 |2 | 0 | | 267 |3 | 0 | | 267 |4 | 0 | | 379 |1 | 0 | | 379 |2 | 0 | | 379 |3 | 0 | | 379 |4 | 0 | | 394 |1 | 0 | | 394 |2 | 0 | | 394 |3 | 0 | | 394 |4 | 0 | | 460 |1 | 0 | | 460 |2 | 0 | | 460 |3 | 0 | | 460 |4 | 0 | | 541 |1 | 0 | | 541 |2 | 0 | | 541 |3 | 0 | | 541 |4 | 0 | i'd like to be able to merge/combine the two tables so that i get +--+--++--+---+-+ | ID | type | status | user | ID| uID | +--+--++--+---+-+ | 40 |1 | 0 | | 40 |2 | 0 | . | 40 |3 | 0 | . | 40 |4 | 0 | | 102 |1 | 0 | | 102 |2 | 0 | | 102 |3 | 0 | | 102 |4 | 0 | with the appropriate information in the various columns/rows... i'm looking to be able to fill the resulting table with the information if it's present, or to have nulls/'0' where the information isn't available... i'd prefer to do this in mysql if possible, as my gut tells me the operation would be faster/more efficient in mysql, than if i coded this in php/perl... i believe that i's need to create a temp table based on each select, and then some how merge the two temp tables, and finally do a select on the resulting table to get the values i need... looking through google/mysql hasn't shed any light on this one... any ideas/thoughts/comments on how i can do this. thanks... -bruce ps... the actual select sql used to create the 2 tbls are listed: select u4.username as user, u3.itemID as ID, u1.ID as uID from universityTBL as u1 left join university_urlTBL as u2 on u2.universityID = u1.ID right join parsefileTBL as p1 on p1.university_urlID = u2.ID left join user_rolesTBL as u3 on u3.itemID = u2.ID left join users as u4 on u3.userID = u4.user_id where u2.urltype = u3.itemType and u2.urltype = '1' and u3.process = '20' and (u1.ID='40' or u1.ID='102' or u1.ID='257' or u1.ID='267' or u1.ID='379' or u1.ID='394'); select u1.universityID as ID, u1.urltype as type, p1.start_status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID = p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' or u1.universityID='102' or u1.universityID='257' or u1.universityID='267' or u1.universityID='379' or u1.universityID='394' or u1.universityID='460' or u1.universityID='541' or u1.universityID='560' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
log_slave_updates without restarting
is there a way to turn on log_slave_updates on a server without having to restart it? -L Luke Crouch 918-461-5326 [EMAIL PROTECTED]
Re: newbie installation question
Machine (operating system) rights and database rights are two, very separate, permissions systems. MySQL maintains it's own access control lists, separate and distinct, from your operating system. You cannot see any other tables when you use your database tool because you are authenticating to the database using an underprivileged login. By default, a new installation (or a clean re-installation, like yours) of MySQL has an administrative login root that has no password. Try using that account next time you connect with mysqlshow and you will have full privileges. It is one of the first administrative tasks of a new installation is to reset the root password to something (anything) to close that security loophole. Here is some background reading on the issues: http://dev.mysql.com/doc/mysql/en/Post-installation.html http://dev.mysql.com/doc/mysql/en/Default_privileges.html http://dev.mysql.com/doc/mysql/en/Privilege_system.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Morten Pedersen [EMAIL PROTECTED] wrote on 09/29/2004 05:36:54 AM: sorry for spamming the list with this, i tried asking on the forums, but didn't receive a reply. i'm a complete newbie to mysql/php, but have programmed professionally (in C/C++/ASM) for about 9 years now. i originally installed mysql into C:/Program Files/mysql, before deleting and reinstalling in the default subdirectory, which meant some paths in the registry had been setup incorrectly. i changed these manually (2 of them located), and this fixed a few issues. however after reinstalling, when i do a mysqlshow, it shows only test in the list of available databases, and nothing i seem to change makes a difference. i edited the my.ini to the base+base/data directories, but that didn't change anything. i even tried changing the paths in my.ini to invalid paths, and that didn't change anything either (it still located test, and test only). i searched for a test subdirectory elsewhere on my hard drive, but failed to locate one. i then created a new database, which it did in the correct subdirectory, so since it seems to be writing to the correct subdirectory, why on earth doesn't it read from the directory as well? i have administrator access to the machine. any help is much appreciated, cheers.
Newbie question - Input limitations on mysql client?
Greetings, I posted this to the MySQL Newbie forum, but have not received a response. Any feedback would be appreciated. -tb Environment: mysql server v4.0.13 (Win2K) mysql client v5.0.0.0(Win2K) In attempting to (manually) insert records with some rather lengthy strings, I pasted the insert statements into the client window; I wound up getting mismatched quotes, although the original statements that I copied were properly quoted. After taking a closer look at what was happening, it appeared that the lines being pasted in were getting truncated at about 256 characters. I was able to eliminate most of the errors by splitting the insert statements across multiple lines (but wound up with newline characters mid-string in some of the values). Eventually, I wound up including the inserts in a perl script, which was successful Is the apparent line length limit a restriction imposed by the client? (And does it apply if you are piping or redirecting output from another process or a file?) Thanks in advance, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: merging of two tables using temp tables???
hey shawn!!! got your msg about helping speed up the mysql!!! thanks. i got to thinking that if the sql/interactions where the issue, why not rewrite the app to reduce the number of round trips to hit the db for a given page to be displayed.. the following sql statements are used to produce the two tables: tbl t1: +-+--+---+--+-+ | ID | type | user | ID | uID | +-+--+---+--+-+ | 40 |1 | admin | 157 | 40 | | 102 |1 | admin | 405 | 102 | | 257 |1 | admin | 1025 | 257 | | 267 |1 | admin | 1065 | 267 | | 379 |1 | admin | 1513 | 379 | +-+--+---+--+-+ 5 rows in set (0.00 sec) tbl t2: +--+--++ | ID | type | status | +--+--++ | 40 |1 | 0 | | 40 |2 | 0 | | 40 |3 | 0 | | 40 |4 | 0 | | 102 |1 | 0 | | 102 |2 | 0 | | 102 |3 | 0 | | 102 |4 | 0 | | 257 |1 | 0 | | 257 |2 | 0 | | 257 |3 | 0 | | 257 |4 | 0 | | 267 |1 | 0 | | 267 |2 | 0 | | 267 |3 | 0 | | 267 |4 | 0 | | 379 |1 | 0 | | 379 |2 | 0 | | 379 |3 | 0 | | 379 |4 | 0 | | 394 |1 | 0 | | 394 |2 | 0 | | 394 |3 | 0 | | 394 |4 | 0 | | 460 |1 | 0 | | 460 |2 | 0 | | 460 |3 | 0 | | 460 |4 | 0 | | 541 |1 | 0 | | 541 |2 | 0 | | 541 |3 | 0 | | 541 |4 | 0 | i'd like to be able to merge/combine the two tables so that i get +--+--++--+---+-+ | ID | type | status | user | ID| uID | +--+--++--+---+-+ | 40 |1 | 0 | | 40 |2 | 0 | . | 40 |3 | 0 | . | 40 |4 | 0 | | 102 |1 | 0 | | 102 |2 | 0 | | 102 |3 | 0 | | 102 |4 | 0 | sql t1: select u2.universityID as ID, u2.urltype as type, u4.username as user, u3.itemID as ID2, u1.ID as uID from universityTBL as u1 join university_urlTBL as u2 on u2.universityID = u1.ID join parsefileTBL as p1 on p1.university_urlID = u2.ID join user_rolesTBL as u3 on u3.itemID = u2.ID join users as u4 on u3.userID = u4.user_id where u2.urltype = u3.itemType and (u2.urltype = '1' or u2.urltype='3') and u3.process = '20' and (u1.ID='40' or u1.ID='102' or u1.ID='257' or u1.ID='267' or u1.ID='379' or u1.ID='394'); t2: select u1.universityID as ID, u1.urltype as type, p1.start_status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID = p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' or u1.universityID='102' or u1.universityID='257' or u1.universityID='267' or u1.universityID='379' or u1.universityID='394' or u1.universityID='460' or u1.universityID='541' or u1.universityID='560' -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 29, 2004 6:42 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: merging of two tables using temp tables??? If you post your two original SQL statements (the ones you use to build your example tables) I think I can help you to merge your results, possibly without the need for temporary tables. Also, what version of MySQL are you using? Shawn Green Database Administrator Unimin Corporation - Spruce Pine bruce [EMAIL PROTECTED] wrote on 09/29/2004 12:57:34 AM: hi i have a situation where i create the following tables via two different select sql statements. +-+--+---+--+-+ | ID | type | user | ID | uID | +-+--+---+--+-+ | 40 |1 | admin | 157 | 40 | | 102 |1 | admin | 405 | 102 | | 257 |1 | admin | 1025 | 257 | | 267 |1 | admin | 1065 | 267 | | 379 |1 | admin | 1513 | 379 | +-+--+---+--+-+ 5 rows in set (0.00 sec) +--+--++ | ID | type | status | +--+--++ | 40 |1 | 0 | | 40 |2 | 0 | | 40 |3 | 0 | | 40 |4 | 0 | | 102 |1 | 0 | | 102 |2 | 0 | | 102 |3 | 0 | | 102 |4 | 0 | | 257 |1 | 0 | | 257 |2 | 0 | | 257 |3 | 0 | | 257 |4 | 0 | | 267 |1 | 0 | | 267 |2 | 0 | | 267 |3 | 0 | | 267 |4 | 0 | | 379 |1 | 0 | | 379 |2 | 0 | | 379 |3 | 0 | | 379 |4 | 0 | | 394 |1 | 0 | | 394 |2 | 0 | | 394 |3 | 0 | | 394 |4 | 0 | | 460 |1 | 0 | | 460 |2 | 0 | | 460 |3 | 0 | | 460 |4 | 0 | | 541 |1 | 0 | | 541 |2 | 0 | | 541 |3 | 0 | | 541 |4 | 0 | i'd like to be able to merge/combine the two tables so that i get
InnoDB crash issue
This isn't a repeatable bug, but it is certainly a repeating one. We have issues on multiple machines running 4.0.20-Max-log with different data sets (in highly similar table structures) with InnoDB hanging and eventually crashing itself to get out of deadlock. Log is attached. There's only one InnoDB table in the database (rest are MyISAM). Its structure is: CREATE TABLE session_data ( sid varchar(32) NOT NULL default '', session_data mediumtext NOT NULL, http_host varchar(255) NOT NULL default '', user varchar(32) NOT NULL default '', stamp datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (sid), KEY http_host (http_host), KEY user (user) ) TYPE=InnoDB; -- Ian Gulliver Penguin Hosting Failure is not an option; it comes bundled with your Microsoft products. MySQL thread id 806227, query id 7467614 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='f697dfe1ccb2fddf0892d144a86d58bf' ---TRANSACTION 0 0, not started, process no 29469, OS thread id 1418817088 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 806226, query id 7467612 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='9200380a42dfd85e035865a845b61db2' ---TRANSACTION 0 0, not started, process no 29469, OS thread id 1422344512 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 806217, query id 7467599 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='4ee95161699670b944f62ff19a646270' ---TRANSACTION 0 0, not started, process no 29469, OS thread id 1420576192 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 806212, query id 7467587 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='1c60932c3eb0ef237397a295c6fd7b5d' ---TRANSACTION 0 0, not started, process no 29469, OS thread id 1413870528 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 806205, query id 7467575 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='a99ffb28fd5defe68eda15ab5bc9fb60' ---TRANSACTION 0 0, not started, process no 29469, OS thread id 1412495168 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 806201, query id 7467563 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='a5352dec66bfbcf1214a92f876661f6d' ---TRANSACTION 0 0, not started, process no 29469, OS thread id 1416821824 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 806193, query id 7467551 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='42129f31ba6456a9eb172948fb4ea3bd' ---TRANSACTION 0 0, not started, process no 29469, OS thread id 1439561920 waiting in InnoDB queue mysql tables in use 1, locked 0 MySQL thread id 806189, query id 7467539 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='cd924675587036968e63f7224f59e36f' ---TRANSACTION 0 6264628, ACTIVE 583 sec, process no 29469, OS thread id 149808 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 0 MySQL thread id 806164, query id 7467324 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='7e83e8ab037784d8193f7a0248b2990d' ---TRANSACTION 0 6264627, ACTIVE 586 sec, process no 29469, OS thread id 1414067008 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 0 MySQL thread id 806159, query id 7467312 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='37d046652a35c341c288dbc789f1d1c0' ---TRANSACTION 0 6264626, ACTIVE 590 sec, process no 29469, OS thread id 1416625344 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 0 MySQL thread id 806153, query id 7467300 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='7f511ae860c52a069fb457a8fb41b7fe' ---TRANSACTION 0 6264625, ACTIVE 590 sec, process no 29469, OS thread id 1401483712 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 0 MySQL thread id 806148, query id 7467288 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='3d548d965384247053f981e3cbad3323' ---TRANSACTION 0 6264624, ACTIVE 594 sec, process no 29469, OS thread id 1408770112 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 0 MySQL thread id 806144, query id 7467276 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='b044fd6bf5c596d76894df232b5df84a' ---TRANSACTION 0 6264623, ACTIVE 595 sec, process no 29469, OS thread id 1417214400 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 0 MySQL thread id 806140, query id 7467264 localhost downeast statistics SELECT session_data FROM session_data WHERE sid='9b291ae9fd1d351252dc6b9b94e6' ---TRANSACTION 0 6264622, ACTIVE 615 sec, process no 29469, OS thread id 1441723200
Re: merging of two tables using temp tables???
Bruce,
My first thought was the LEFT JOIN Jacques suggested. My second thought was
that it would be better to just create a single query that gets what you
want in the first place. So I looked a little closer, and now I'm confused.
At the end of your message, you include 2 queries that you say were used
to create the 2 tables, but the first table was definitely not created with
the first query, as it has 2 more columns then are selected in the first
query. (I see you've just posted a different 1st query in a follow-up
message, though it still doesn't quite match the output.)
There also seems to be a mismatch between the 2 queries. The first query
(replacing your multiple OR conditions with the easier to read IN) selects
rows with
universityTBL.ID IN ('40', '102', '257', '267', '379', '394');
but the second query selects rows with
universityID IN ('40', '102', '257', '267', '379', '394', '460', '541',
'560')
Why the 3 extra values in the second query?
I'm guessing that your goal is simply to add the status column to the
results of query 1. In that case, I'd expect that something like
SELECT u2.universityID as ID,
u2.urltype as type,
u4.username as user,
u3.itemID as ID2,
u1.ID as uID,
p1.start_status as status
FROM universityTBL as u1
LEFT JOIN university_urlTBL as u2 ON u2.universityID = u1.ID
RIGHT JOIN parsefileTBL as p1 ON p1.university_urlID = u2.ID
LEFT JOIN user_rolesTBL as u3 ON u3.itemID = u2.ID
LEFT JOIN users as u4 ON u3.userID = u4.user_id
JOIN latestParseStatusTBL as l1 ON p1.fileID = l1.itemID
WHERE u2.urltype = u3.itemType
AND u2.urltype IN (1, 3)
AND u3.process = 20
AND u1.ID IN (40, 102, 257, 267, 379, 394);
would do the trick. Note that I took out the quotes around the integers.
Your IDs are integers, not strings, right?
Michael
bruce wrote:
thanks for the response...
however, the attempt at using the left join/where construct was what i had
initially tried, with no luck. (which is not to say i was implementing it
correctly!!)
using the basic select * from/left join on /where seems to give a resulting
table that only contains a subset of the two tables.
i'd like more of a merge. a thought i had was to do a simple merge, and then
somehow do an operation which would give me the rows that weren't in the
initial merge, followed by a 3rd operation that would then join the 1st two
results
my gut tells me i'm making this too complex because i can't figure out how
to properly/correctly accomplish the original merging process/function to
combine the initial two tables
thanks
-bruce
i have a situation where i create the following tables via two different
select sql statements.
+-+--+---+--+-+
| ID | type | user | ID | uID |
+-+--+---+--+-+
| 40 |1 | admin | 157 | 40 |
| 102 |1 | admin | 405 | 102 |
| 257 |1 | admin | 1025 | 257 |
| 267 |1 | admin | 1065 | 267 |
| 379 |1 | admin | 1513 | 379 |
+-+--+---+--+-+
5 rows in set (0.00 sec)
+--+--++
| ID | type | status |
+--+--++
| 40 |1 | 0 |
| 40 |2 | 0 |
| 40 |3 | 0 |
| 40 |4 | 0 |
| 102 |1 | 0 |
| 102 |2 | 0 |
| 102 |3 | 0 |
| 102 |4 | 0 |
| 257 |1 | 0 |
| 257 |2 | 0 |
| 257 |3 | 0 |
| 257 |4 | 0 |
| 267 |1 | 0 |
| 267 |2 | 0 |
| 267 |3 | 0 |
| 267 |4 | 0 |
| 379 |1 | 0 |
| 379 |2 | 0 |
| 379 |3 | 0 |
| 379 |4 | 0 |
| 394 |1 | 0 |
| 394 |2 | 0 |
| 394 |3 | 0 |
| 394 |4 | 0 |
| 460 |1 | 0 |
| 460 |2 | 0 |
| 460 |3 | 0 |
| 460 |4 | 0 |
| 541 |1 | 0 |
| 541 |2 | 0 |
| 541 |3 | 0 |
| 541 |4 | 0 |
i'd like to be able to merge/combine the two tables so that i get
+--+--++--+---+-+
| ID | type | status | user | ID| uID |
+--+--++--+---+-+
| 40 |1 | 0 |
| 40 |2 | 0 | .
| 40 |3 | 0 | .
| 40 |4 | 0 |
| 102 |1 | 0 |
| 102 |2 | 0 |
| 102 |3 | 0 |
| 102 |4 | 0 |
with the appropriate information in the various columns/rows...
i'm looking to be able to fill the resulting table with the information if
it's present, or to have nulls/'0' where the information isn't available...
i'd prefer to do this in mysql if possible, as my gut tells me the operation
would be faster/more efficient in mysql, than if i coded this in php/perl...
i believe that i's need to create a temp table based on each select, and
then some how merge the two temp tables, and finally do a select on the
resulting table to get the values i need...
looking through google/mysql hasn't shed any light on this one...
any
importing data into mysql from oracle using a text file
Title: Message Hi, I tried with the spool option to get the data from the tables in the oracle. For this go to pl/sql editor, go to file menu, select spool, asks for a file name give the file name you want, later type the select command from which you want the data. and select spool of option from the file menu. eg;select * from alarm; After spooling i got the file alarm.LST as the attached (it will be there in C:/orant/bin). Save that file as .txt file. Now go to mysql prompt as a root user. 1) choose any of the default database by using the command eg: use test 2) create the table alarm using the samecolumns,data types (here data types may differ)as in oracle eg; mysql CREATE TABLE ALARM ( - ARRIVED DATE NULL, - DETECTED DATE NULL, - NAME VARCHAR(20) NULL, - TYPE INTEGER(1) NULL, - ALARMLEVEL INTEGER(1) NULL, - VERIFIED INTEGER(1) NULL, - DISCLOSED INTEGER(1) NULL, - CATEGORY_NUM INTEGER(1) NULL, - EVENTID INTEGER(5) NOT NULL, - REASON VARCHAR(60) NULL - ); 3) Use the LOAD DAT INFILE command for data to import from .txt file (copy the alarm.txt file into c:\mysql\data\test (if we use test database)) eg; mysql LOAD DATA INFILE 'alarm.txt' INTO TABLE alarm; Here the data is not inserting properly: Bcs 1) The data in the alarm.txt file should be like each colum data should be seperated by \t and each row should be separated by \n . and from spool the data is not coming in the desired format. I did not find any suitable command/option to get the spooling file with the desired delimiters. Colud any one suggest me here. 2) The date format is different in oracle and mysql. This also i took care externally. 3) Even i tried by formating the data in the required(see the attached alarm.txt for the format), getting the result as the following. Not inserting the data properly. mysql LOAD DATA INFILE 'alarm.txt' INTO TABLE alarm;Query OK, 1 row affected (0.00 sec)Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 mysql select * from alarm;++++--++--+---+--+-++| ARRIVED | DETECTED | NAME | TYPE | ALARMLEVEL | VERIFIED | DISCLOSED | CATEGORY_NUM | EVENTID | REASON|++++--++--+---+--+-++| 2004-09-27 | -00-00 | 2004-09-27 | 0 | 0 | 0 | 0 | 0 | 0 | |++++--++--+---+--+-++1 row in set (0.00 sec) mysql I tried with another command mysqlimport. but that command also seems to be work with the above format only. Please give me a solution for this. Is there any other way to do this from oracle. My aim is dumping the data from oracle to mysql. Thanks, Narasimha -Original Message- From: Praneesh Prakashan (WT01 - TELECOM SOLUTIONS) Sent: Tue 9/28/2004 9:48 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS); Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS) Cc: Deepak Nagarajan (WT01 - TELECOM SOLUTIONS) Subject: RE: Using XML with my Sql Hi Narasim, In the Pro* C files (dbids.pc, dbtutil.pc), synonyms are being used. regards, - Praneesh -Original Message-From: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Sent: Monday, September 27, 2004 10:07 PMTo: Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS)Cc: Praneesh Prakashan (WT01 - TELECOM SOLUTIONS); Deepak Nagarajan (WT01 - TELECOM SOLUTIONS)Subject: RE: Using XML with my Sql Hi, As per our discussion, You can create an alias or synonym for a MyISAM table by defining a MERGE table that maps to that single table. Synonym is an alias for any table, view or other object in database. May i know where in the code they used synonyms?. Attached i sthe document for merging tables. Thanks, Narasimha -Original Message- From: Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS) Sent: Fri 9/24/2004 7:44 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: Subject: FW: Using XML with my Sql XML Support for MySQL support -Original Message- From: Bishnu Prasad Panda (WT01 - TELECOM SOLUTIONS) Sent: Thursday, September 16, 2004 6:32 PM To: Deepak Nagarajan (WT01 - TELECOM SOLUTIONS); Jathish Maruthoormana Jayanthan
Indexing problem with UTF8 in 4.1.4?
Running 4.1.4 with a database that has a default encoding of UTF8 If we execute the following we get an error. CREATE TABLE idxbe_resident ( urn INT UNSIGNED NOT NULL, keyAddress_Part1 CHAR(5) BINARY NOT NULL, dataPerson_Name CHAR(60), dataAddress_Part1 CHAR(140), dataAddress_Part2 CHAR(128), INDEX (keyAddress_Part1, dataPerson_Name, dataAddress_Part1, dataAddress_Part2, urn)); 1071 (Specified key was too long; max key length is 1000 bytes) If we change the dataAddress_Part1 field to be 139 characters the error goes away. If we change it to be greater than 255 characters we get a different error 1170 (BLOB/TEXT column 'dataAddress_Part1' used in key specification without a key length) Can I have an explanation or are these both bugs? Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. **
RE: merging of two tables using temp tables???
thanks for the reply... and my bad on the diff between the sql. the actual sql had ~100 values. (i simply cut it to demonstrate what i'm trying to do!!) i took the two tables created by the initial sql statements and modified them so that they both had the same structure. i then added/deleted/etc.. untill i got to a table that gave me all the information i needed, but i need to figure out how to reorder it... the sql/query you provided is similar to what i had created when i 1st started. however, it only gives the actual join of the two tables. i'm really trying to get a 'merge' of the information between the two tables... here's what i have so far: table 1: mysql select * from t1; +-+--+---+--+-++ | ID | type | user | ID2 | uID | status | +-+--+---+--+-++ | 40 |1 | admin | 157 | 40 | NULL | | 102 |1 | admin | 405 | 102 | NULL | | 257 |1 | admin | 1025 | 257 | NULL | | 267 |1 | admin | 1065 | 267 | NULL | | 379 |1 | admin | 1513 | 379 | NULL | +-+--+---+--+-++ 5 rows in set (0.00 sec) table 2: mysql select * from t2; +--+--++--+--+--+ | ID | type | status | user | ID2 | uID | +--+--++--+--+--+ | 40 |1 | 0 | NULL | NULL | NULL | | 40 |2 | 0 | NULL | NULL | NULL | | 40 |3 | 0 | NULL | NULL | NULL | | 40 |4 | 0 | NULL | NULL | NULL | | 102 |1 | 0 | NULL | NULL | NULL | | 102 |2 | 0 | NULL | NULL | NULL | | 102 |3 | 0 | NULL | NULL | NULL | | 102 |4 | 0 | NULL | NULL | NULL | | 257 |1 | 0 | NULL | NULL | NULL | | 257 |2 | 0 | NULL | NULL | NULL | | 257 |3 | 0 | NULL | NULL | NULL | | 257 |4 | 0 | NULL | NULL | NULL | | 267 |1 | 0 | NULL | NULL | NULL | | 267 |2 | 0 | NULL | NULL | NULL | | 267 |3 | 0 | NULL | NULL | NULL | | 267 |4 | 0 | NULL | NULL | NULL | | 379 |1 | 0 | NULL | NULL | NULL | | 379 |2 | 0 | NULL | NULL | NULL | | 379 |3 | 0 | NULL | NULL | NULL | | 379 |4 | 0 | NULL | NULL | NULL | | 394 |1 | 0 | NULL | NULL | NULL | | 394 |2 | 0 | NULL | NULL | NULL | | 394 |3 | 0 | NULL | NULL | NULL | | 394 |4 | 0 | NULL | NULL | NULL | | 460 |1 | 0 | NULL | NULL | NULL | | 460 |2 | 0 | NULL | NULL | NULL | | 460 |3 | 0 | NULL | NULL | NULL | | 460 |4 | 0 | NULL | NULL | NULL | | 541 |1 | 0 | NULL | NULL | NULL | | 541 |2 | 0 | NULL | NULL | NULL | | 541 |3 | 0 | NULL | NULL | NULL | | 541 |4 | 0 | NULL | NULL | NULL | | 560 |1 | 0 | NULL | NULL | NULL | | 560 |2 | 0 | NULL | NULL | NULL | | 560 |3 | 0 | NULL | NULL | NULL | | 560 |4 | 0 | NULL | NULL | NULL | +--+--++--+--+--+ 36 rows in set (0.00 sec) table td: mysql select * from td; +--+--++--+--+--+ | ID | type | status | user | ID2 | uID | +--+--++--+--+--+ | 40 |2 | 0 | NULL | NULL | NULL | | 40 |3 | 0 | NULL | NULL | NULL | | 40 |4 | 0 | NULL | NULL | NULL | | 102 |2 | 0 | NULL | NULL | NULL | | 102 |3 | 0 | NULL | NULL | NULL | | 102 |4 | 0 | NULL | NULL | NULL | | 257 |2 | 0 | NULL | NULL | NULL | | 257 |3 | 0 | NULL | NULL | NULL | | 257 |4 | 0 | NULL | NULL | NULL | | 267 |2 | 0 | NULL | NULL | NULL | | 267 |3 | 0 | NULL | NULL | NULL | | 267 |4 | 0 | NULL | NULL | NULL | | 379 |2 | 0 | NULL | NULL | NULL | | 379 |3 | 0 | NULL | NULL | NULL | | 379 |4 | 0 | NULL | NULL | NULL | | 394 |1 | 0 | NULL | NULL | NULL | | 394 |2 | 0 | NULL | NULL | NULL | | 394 |3 | 0 | NULL | NULL | NULL | | 394 |4 | 0 | NULL | NULL | NULL | | 460 |1 | 0 | NULL | NULL | NULL | | 460 |2 | 0 | NULL | NULL | NULL | | 460 |3 | 0 | NULL | NULL | NULL | | 460 |4 | 0 | NULL | NULL | NULL | | 541 |1 | 0 | NULL | NULL | NULL | | 541 |2 | 0 | NULL | NULL | NULL | | 541 |3 | 0 | NULL | NULL | NULL | | 541 |4 | 0 | NULL | NULL | NULL | | 560 |1 | 0 | NULL | NULL | NULL | | 560 |2 | 0 | NULL | NULL | NULL | | 560 |3 | 0 | NULL | NULL | NULL | | 560 |4 | 0 | NULL | NULL | NULL | +--+--++--+--+--+ 31 rows in set (0.00 sec) mysql select * from ta;how to reorder +--+--++--+--+--+ | ID | type | status | user | ID2 | uID | +--+--++--+--+--+ | 40 |1 | admin | 157 | 40 | NULL | | 102 |1 | admin | 405 | 102 | NULL | | 257 |1 | admin |
Re: mysql logs too much
At 12:52 +0530 9/29/04, Kausalya Ramaswamy wrote: Does mysql 4.0 onwards log cached querries also or only direct non cached database access queries? i am getting enormous amount of log in the form of querries. ours is a databse driven website using mysql and php.thanks for the reply in advance If you mean the general query log that is enabled with the --log option, queries are written to this log as they are received, and before they are executed. This means that queries that can be served from the query cache are logged, because logging occurs before any determination is made whether or not they're in the cache. -- 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]
Maximum Key Buffer Size
Is there a maximum key buffer size that can be configured in mysql-4.0.20. I had the key buffer set to 4GB and when looking at key_reads vs. key_read_requests they were almost identical indicating that all reads were actually going to physical disk. I then double checked this by using Mysql Administrator and looking at the key efficiency and sure enough it was 0%. I then decreased the key_buffer to 2GB and it mysteriously started working again. Thx, Ian The information transmitted in this email is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this email in error, please contact the sender and permanently delete the email from any computer.
RE: merging of two tables using temp tables???
First, may I suggest a few changes to your original queries: select u2.universityID as ID, u2.urltype as type, u4.username as user, u3.itemID as ID2, u1.ID as uID from universityTBL as u1 join university_urlTBL as u2 on u2.universityID = u1.ID join parsefileTBL as p1 on p1.university_urlID = u2.ID join user_rolesTBL as u3 on u3.itemID = u2.ID and u2.urltype = u3.itemType join users as u4 on u3.userID = u4.user_id where u2.urltype IN (1,2) and u3.process = 20 and u1.ID IN (40,102,257,267,379,394); Changes: moved a conditional term linking u2 and u3 into the ON clause of the u3 JOIN Changed your OR lists into IN (,,) Removed quotes from around your numbers (They are not strings, don't quote them) select u1.universityID as ID , u1.urltype as type , p1.start_status as status FROM parsefileTBL as p1 INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID LEFT JOIN university_urlTBL as u1 on p1.university_urlID = u1.ID WHERE u1.universityID IN (40,102,257,267,379,394,460,541,560) Changes: Moved your outer join to the end of your join list and converted the RIGHT join to a LEFT join. Changed your OR list into an IN(,,,) Unquoted the numbers I question the logic of this last query. Because of the LEFT join, the WHERE condition is not applied until after the Cartesian product of (p1 IJ l1) LJ u1 is built as a virtual table. You eliminate all non-matching rows from u1 by looking for a non-null value in the results. This query should move faster written as select u1.universityID as ID , u1.urltype as type , p1.start_status as status FROM parsefileTBL as p1 INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID INNER JOIN university_urlTBL as u1 on p1.university_urlID = u1.ID and u1.universityID IN (40,102,257,267,379,394,460,541,560) Now, to address the JOIN of the two queries so that the results of query 1 are optionally matched with the rows of query 2 select u1.universityID as ID , u1.urltype as type , p1.start_status as status , u4.username as user , u3.itemID as ID2 , u1.ID as uID FROM university_urlTBL as u1 INNER JOIN parsefileTBL as p1 on p1.university_urlID = u1.ID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID LEFT join user_rolesTBL as u3 on u3.itemID = u2.ID and u2.urltype IN (1,2) and u2.urltype = u3.itemType and u3.process = 20 LEFT join users as u4 on u3.userID = u4.user_id WHERE u1.universityID IN (40,102,257,267,379,394,460,541,560) ORDER BY 1,2,3 Notes: 1) You already had the first two columns the same for each query so I kept those tables joined the same way. The 3rd and 4th columns were optional data so the tables they source from are LEFT JOINed 2) The ON condition of the LEFT JOIN of user_rolesTBL has two conditions (u2.urltype... and u3.process...) that you may have considered putting into the WHERE clause. They belong in the ON clause of the join because those are two of the conditions by which we decide which rows are joined, not which rows do we return as results of the query. 3) I added an order by clause so that the report will format as you suggested (listed by ID, type, status) Shawn Green Database Administrator Unimin Corporation - Spruce Pine bruce [EMAIL PROTECTED] wrote on 09/29/2004 10:05:49 AM: hey shawn!!! got your msg about helping speed up the mysql!!! thanks. i got to thinking that if the sql/interactions where the issue, why not rewrite the app to reduce the number of round trips to hit the db for a given page to be displayed.. the following sql statements are used to produce the two tables: --8--snip--8--- sql t1: select u2.universityID as ID, u2.urltype as type, u4.username as user, u3.itemID as ID2, u1.ID as uID from universityTBL as u1 join university_urlTBL as u2 on u2.universityID = u1.ID join parsefileTBL as p1 on p1.university_urlID = u2.ID join user_rolesTBL as u3 on u3.itemID = u2.ID join users as u4 on u3.userID = u4.user_id where u2.urltype = u3.itemType and (u2.urltype = '1' or u2.urltype='3') and u3.process = '20' and (u1.ID='40' or u1.ID='102' or u1.ID='257' or u1.ID='267' or u1.ID='379' or u1.ID='394'); t2: select u1.universityID as ID, u1.urltype as type, p1.start_status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID = p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' or u1.universityID='102' or u1.universityID='257' or u1.universityID='267' or u1.universityID='379' or u1.universityID='394' or u1.universityID='460' or u1.universityID='541' or u1.universityID='560' -Original Message- From: [EMAIL
Re: merging of two tables using temp tables???
bruce wrote: thanks for the reply... and my bad on the diff between the sql. the actual sql had ~100 values. (i simply cut it to demonstrate what i'm trying to do!!) i took the two tables created by the initial sql statements and modified them so that they both had the same structure. i then added/deleted/etc.. untill i got to a table that gave me all the information i needed, but i need to figure out how to reorder it... the sql/query you provided is similar to what i had created when i 1st started. however, it only gives the actual join of the two tables. i'm really trying to get a 'merge' of the information between the two tables... I don't understand what you mean. What is a merge? Do you simply want all the rows from one together with all the rows from two? That is, you want duplicate rows where part of the info is in each row? Something like +-+--+---+--+-++ | ID | type | user | ID2 | uID | status | +-+--+---+--+-++ | 40 |1 | admin | 157 | 40 | NULL | | 40 |1 | NULL | NULL | NULL| 0| and so on? I can't imagine why, but OK. here's what i have so far: snip mysql select * from ta;how to reorder +--+--++--+--+--+ | ID | type | status | user | ID2 | uID | +--+--++--+--+--+ | 40 |1 | admin | 157 | 40 | NULL | | 102 |1 | admin | 405 | 102 | NULL | | 257 |1 | admin | 1025 | 257 | NULL | | 267 |1 | admin | 1065 | 267 | NULL | | 379 |1 | admin | 1513 | 379 | NULL | | 40 |2 | 0 | NULL | NULL | NULL | | 40 |3 | 0 | NULL | NULL | NULL | | 40 |4 | 0 | NULL | NULL | NULL | snip if i could figure out how to reorder the table, to group all the 'ID' together, followed by the 'type'. Ordering is done with ORDER BY. To get the results in order by ID and type, you need SELECT * FROM ta ORDER BY ID, type; thanks -bruce Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Users Conference 2005 - Call for Papers is Open
Hi MySQL community, As some of you, I did a presentation at the UC2004 about Using MySQL in a Japanese environment - and avoiding common pitfalls, which can also be found online. http://www.be-known-online.com/mysql/ During the first 6 months I had so far 250 downloads, only for this single article on my homepage and probably many more on MySQLs homepage. http://www.mysql.com/news-and-events/users-conference/2004/ Thank you very much for anybody who showed interest. As most of you know, the call for papers just started. 1) I am thinking about continuing the national character / Unicode issues and would like to hear which issues you have, what bothers you most. I am already considering the following suggestion: a) exploring national Character Sets / Java environment issues (supported fonts functions by Sun, IBM etc..) The follow up title would probably be something like Using MySQL in a Japanese environment - x Please send any ideas, suggestions or any rant you might want to leave ;-) 2) If my time allows it, I am considering to apply for holding a half day workshop. I would naturally tend to expand the national character set issues, which will allow to explore the issues described with national characters more detailed. Anybody interested in this kind of topic ? Note: No need to reply if not, otherwise my mail server might crash ;-) -- Greetings from Tokyo Nils Valentin http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing problem with UTF8 in 4.1.4?
Kevin Cowley wrote: Running 4.1.4 with a database that has a default encoding of UTF8 If we execute the following we get an error. CREATE TABLE idxbe_resident ( urn INT UNSIGNED NOT NULL, keyAddress_Part1 CHAR(5) BINARY NOT NULL, dataPerson_Name CHAR(60), dataAddress_Part1 CHAR(140), dataAddress_Part2 CHAR(128), INDEX (keyAddress_Part1, dataPerson_Name, dataAddress_Part1, dataAddress_Part2, urn)); 1071 (Specified key was too long; max key length is 1000 bytes) If we change the dataAddress_Part1 field to be 139 characters the error goes away. Don'y know. If we change it to be greater than 255 characters we get a different error 1170 (BLOB/TEXT column 'dataAddress_Part1' used in key specification without a key length) VARCHAR() maximum length is 255. Greater than 255 is promoted to text. Can I have an explanation or are these both bugs? Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data into mysql from oracle using a text file
Can you post one or 2 sample lines from alarm.txt ? Regards, Andrey P.S. (i am not on [EMAIL PROTECTED] so add me to the CC:) [EMAIL PROTECTED] wrote: Hi, I tried with the spool option to get the data from the tables in the oracle. For this go to pl/sql editor, go to file menu, select spool, asks for a file name give the file name you want, later type the select command from which you want the data. and select spool of option from the file menu. eg; select * from alarm; After spooling i got the file alarm.LST as the attached (it will be there in C:/orant/bin). Save that file as .txt file. Now go to mysql prompt as a root user. 1) choose any of the default database by using the command eg: use test 2) create the table alarm using the same columns, data types (here data types may differ) as in oracle eg; mysql CREATE TABLE ALARM ( -ARRIVED DATE NULL, -DETECTED DATE NULL, -NAME VARCHAR(20) NULL, -TYPE INTEGER(1) NULL, -ALARMLEVEL INTEGER(1) NULL, -VERIFIED INTEGER(1) NULL, -DISCLOSEDINTEGER(1) NULL, -CATEGORY_NUM INTEGER(1) NULL, -EVENTID INTEGER(5) NOT NULL, -REASON VARCHAR(60) NULL - ); 3) Use the LOAD DAT INFILE command for data to import from .txt file (copy the alarm.txt file into c:\mysql\data\test (if we use test database)) eg; mysql LOAD DATA INFILE 'alarm.txt' INTO TABLE alarm; Here the data is not inserting properly: Bcs 1) The data in the alarm.txt file should be like each colum data should be seperated by \t and each row should be separated by \n . and from spool the data is not coming in the desired format. I did not find any suitable command/option to get the spooling file with the desired delimiters. Colud any one suggest me here. 2) The date format is different in oracle and mysql. This also i took care externally. 3) Even i tried by formating the data in the required (see the attached alarm.txt for the format), getting the result as the following . Not inserting the data properly. mysql LOAD DATA INFILE 'alarm.txt' INTO TABLE alarm; Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 mysql select * from alarm; ++++--++--+---+--+-+--- -+ | ARRIVED| DETECTED | NAME | TYPE | ALARMLEVEL | VERIFIED | DISCLOSED | CATEGORY_NUM | EVENTID | REASON | ++++--++--+---+--+-+--- -+ | 2004-09-27 | -00-00 | 2004-09-27 |0 | 0 |0 | 0 |0 | 0 | | ++++--++--+---+--+-+--- -+ 1 row in set (0.00 sec) mysql I tried with another command mysqlimport. but that command also seems to be work with the above format only. Please give me a solution for this. Is there any other way to do this from oracle. My aim is dumping the data from oracle to mysql. Thanks, Narasimha -Original Message- From: Praneesh Prakashan (WT01 - TELECOM SOLUTIONS) Sent: Tue 9/28/2004 9:48 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS); Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS) Cc: Deepak Nagarajan (WT01 - TELECOM SOLUTIONS) Subject: RE: Using XML with my Sql Hi Narasim, In the Pro* C files (dbids.pc, dbtutil.pc), synonyms are being used. regards, - Praneesh -Original Message- From: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Sent: Monday, September 27, 2004 10:07 PM To: Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS) Cc: Praneesh Prakashan (WT01 - TELECOM SOLUTIONS); Deepak Nagarajan (WT01 - TELECOM SOLUTIONS) Subject: RE: Using XML with my Sql Hi, As per our discussion, You can create an alias or synonym for a MyISAM table by defining a MERGE table that maps to that single table. Synonym is an alias for any table, view or other object in database. May i know where in the code they used synonyms?. Attached i sthe document for merging tables. Thanks, Narasimha -Original Message- From: Jathish Maruthoormana Jayanthan (WT01 - TELECOM SOLUTIONS) Sent: Fri 9/24/2004 7:44 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: Subject: FW: Using XML with my Sql XML Support for MySQL support -Original Message- From: Bishnu Prasad Panda (WT01 - TELECOM SOLUTIONS) Sent: Thursday, September 16, 2004 6:32 PM To: Deepak Nagarajan (WT01 - TELECOM SOLUTIONS); Jathish
weird kind of join
is there anyway to do a joint between a table that has codes like this 10004;XXX or DE;YYY with a table that has just the first part e.g 10004 or DE as the code There is no set length to the code , all I know is that it is the part before the semicolon. so, I can't say FROM table_a INNER JOIN table_b ON (table_a_code = left(table_b.code,2)) because I will only match the ones that have 2 character codes. Diana Castillo Global Reservas, S.L. C/Granvia 22 dcdo 4-dcha 28013 Madrid-Spain Tel : 00-34-913604039 Ext 216 Fax : 00-34-915228673 email: [EMAIL PROTECTED] Web : http://www.hotelkey.com http://www.destinia.com
RE: importing data into mysql from oracle using a text file
Title: Re: importing data into mysql from oracle using a text file The sample lines are like this in alarm.txt ARRIVED DETECTED NAME TYPE ALARMLEVEL V DISCLOSED CATEGORY_NUM EVENTID - - - -- - - - REASON 27-SEP-04 27-SEP-04 alaram 0 0 N 0 2 1 27-SEP-04 27-SEP-04 MiTel 0 0 N 0 2 2 The above lines i am not able to insert into mysql. After changing the format as below(as in the sample.txt)i am able to insert into mysql but not correclty. Couls you please help me in this. 2004-09-27\t2004-09-27\talaram\t0\t0\tN\t0\t2\t1\n thanks, narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intendedfor the exclusive use of the addressee(s) and may contain confidential or privileged information. Ifyou are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediatelyand destroy all copies of this message and any attachments. ARRIVED DETECTED NAME TYPE ALARMLEVEL V DISCLOSED CATEGORY_NUM EVENTID - - - -- - - - REASON 27-SEP-04 27-SEP-04 alaram 0 0 N 02 1 27-SEP-04 27-SEP-04 MiTel0 0 N 02 2 2004-09-27 \t 2004-09-27 \t alaram \t 0 \t 0 \t N \t 0 \t 2 \t 1 \n-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB backup + replication problem?
I've got an interesting (well, I think so anyway) problem with my replication. The slave chugs along just fine, then spits out: Query caused different errors on master and slave. Error on master: 'Can't execute the query because you have a conflicting read lock' (1223), Error on slave: 'no error' (0). Default database: 'mysql'. Query: 'BEGIN' I check the master binlog position, and discover this: /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 35294588 #040929 2:25:51 server id 1 log_pos 35294588 Query thread_id=7830089 exec_time=0 error_code=1223 use mysql; SET TIMESTAMP=1096449951; BEGIN; # at 35294629 #040929 2:25:44 server id 1 log_pos 35282293 Query thread_id=7830089 exec_time=0 error_code=0 SET TIMESTAMP=1096449944; INSERT INTO ibbackup_binlog_marker VALUES (1); # at 35294710 #040929 2:25:51 server id 1 log_pos 35294710 Query thread_id=7830089 exec_time=0 error_code=1223 SET TIMESTAMP=1096449951; COMMIT; I didn't see this prior to 4.0.21 (I was on 4.0.20), but it may or may not be related. This has happened a few times now, and always around the time that I finish an InnoDB backup. Anyone else seen this? Any ideas? Thanks, Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indexing problem with UTF8 in 4.1.4?
Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: 29 September 2004 17:29 To: Kevin Cowley Cc: [EMAIL PROTECTED] Subject: Re: Indexing problem with UTF8 in 4.1.4? Kevin Cowley wrote: Running 4.1.4 with a database that has a default encoding of UTF8 If we execute the following we get an error. CREATE TABLE idxbe_resident ( urn INT UNSIGNED NOT NULL, keyAddress_Part1 CHAR(5) BINARY NOT NULL, dataPerson_Name CHAR(60), dataAddress_Part1 CHAR(140), dataAddress_Part2 CHAR(128), INDEX (keyAddress_Part1, dataPerson_Name, dataAddress_Part1, dataAddress_Part2, urn)); 1071 (Specified key was too long; max key length is 1000 bytes) If we change the dataAddress_Part1 field to be 139 characters the error goes away. Don'y know. If I knew why I wouldn't be asking. Now by our reconing the key of the fields is 343 bytes, encoding in UTF8 makes that key 343 bytes Not 1000 since under utf8 each character is encode in 8 bits. If it is converting to utf16 internally then the key would be 328*2+5+4 which is not 1000 bytes. If we change it to be greater than 255 characters we get a different error 1170 (BLOB/TEXT column 'dataAddress_Part1' used in key specification without a key length) VARCHAR() maximum length is 255. Greater than 255 is promoted to text. We're not using VARCHAR unless MySQL is automatically translating CHAR to VARCHAR. Even so, not making the assumption key_length=field length for a field NOT declared as TEXT is somewhat poor. So my request stands. Can I have an explanation or are these both bugs? ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
full text search question
Hello,
I have a questions with limitations/restrictions that are around for
full text search.
I have a field with data like XY-11443;. and I need to find the
record. The original developer was using full text search and says that
all was working before the task switched hands.
The basic query is
select * from metadata where match(type) against ('+XY-11443' in boolean
mode);
This query spins through all of my records and gives no results.
However, if I remove the XY- and just do ('+11443' in boolean mode) I
get an immediate and correct result.
I believe there is something going on with the '-' in the string that is
causing trouble - like maybe a stop word or something - but can't find
exactly what is going on and more importantly HOW TO FIX IT
Any help would be awesome!
Laura
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: full text search question
Laura:
Perhaps the - is acting like a Boolean operator. What if you put
double quotes around your search phrase:
SELECT * FROM metadata WHERE MATCH( type ) AGAINST ( '+XY-11443' IN
BOOLEAN MODE );
Wes
On Wed, 29 Sep 2004 13:22:54 -0400, Laura Scott [EMAIL PROTECTED] wrote:
Hello,
I have a questions with limitations/restrictions that are around for
full text search.
I have a field with data like XY-11443;. and I need to find the
record. The original developer was using full text search and says that
all was working before the task switched hands.
The basic query is
select * from metadata where match(type) against ('+XY-11443' in boolean
mode);
This query spins through all of my records and gives no results.
However, if I remove the XY- and just do ('+11443' in boolean mode) I
get an immediate and correct result.
I believe there is something going on with the '-' in the string that is
causing trouble - like maybe a stop word or something - but can't find
exactly what is going on and more importantly HOW TO FIX IT
Any help would be awesome!
Laura
--
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: weird kind of join
try this (not tested):
FROM table_a
INNER JOIN table_b
ON table_b.code LIKE concat(table_a.code,';%')
or this:
FROM table_a
INNER JOIN table_b
ON table_b.code RLIKE concat('^',table_a.code,';')
http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html
http://dev.mysql.com/doc/mysql/en/Regexp.html
It's not going to be as quick as a direct lookup because of the CONCAT()
but at least we preserve the possibility of using an index for
table_b.code.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Diana Castillo [EMAIL PROTECTED] wrote on 09/29/2004 12:39:40 PM:
is there anyway to do a joint between a table that has codes like
this 10004;XXX or DE;YYY
with a table that has just the first part e.g 10004 or DE as the code
There is no set length to the code , all I know is that it is the
part before the semicolon.
so, I can't say
FROM table_a INNER JOIN table_b ON (table_a_code =
left(table_b.code,2))
because I will only match the ones that have 2 character codes.
Diana Castillo
Global Reservas, S.L.
C/Granvia 22 dcdo 4-dcha
28013 Madrid-Spain
Tel : 00-34-913604039 Ext 216
Fax : 00-34-915228673
email: [EMAIL PROTECTED]
Web : http://www.hotelkey.com
http://www.destinia.com
Help with PHP to MySQL db connection
Hola, MySQL wizards. I'm using LAMP on Fedora Core 2. Some kind soul produced a tutorial on the net and the first script, birthdays_create_database.php, I am trying to use yields an error in the browser of: Couldn't connect to MySQL Here is the script. --- htmlheadtitleBirthdays Create Database/title/head body ?php $dbhost = 'localhost'; $link = mysql_connect($dbhost); if (! $link) die(Couldn't connect to MySQL); //create database mysql_create_db(mydatabase)or die(Create Error: .mysql_error()); mysql_close($link); ? /body /html --- I can access all of my databases and tables with phpMyAdmin just fine, and I can access them at the command line. Of course I didn't know which user I was when was attempting to execute this php script through the browser, so I check my mysqld.log file and it claims I should be '[EMAIL PROTECTED]', so I setup this user in the mysql database. I tried adding $dbuser = 'nobody'; $dbpass = ''; to the script but to no avail even though I can access through the command prompt with mysql -u nobody -p with a null password. When I do a ps -elf | grep mysql the results show a --port=3306 as well as 10 connection PID's. My hello.php script works fine, as do other php scripts. When I list netstat -l | grep mysql* however, the number that gets displayed in the column after STREAM LISTENING and before /var/lib/mysql/mysql.sock is 999269. I was expecting it to be 3306? How do I troubleshoot this Cannot connect to MySQL error message? My mysqld.log looks normal other than a Warning: Asked for 196608 thread stack, but got 126976. The mysql.sock line shows port: 3306. Any ideas on what to check next? I'd at a loss. TIA Andrew L. in the Heartland (not the Hinterlands) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(if !update then insert) sequence - result Duplicate key :(
Hi!
Sorry for my ugly English.
I use mysql in my PHP script(for calculating INs and OUTs from site).
And I meet some strange thing there.
Example goal: store statistics for clicks on some links.
I use next algorithm:
---
$key='somekeyvalue';
dbquery(update sometable set count=count+1 where keyfield='$key');
if(mysql_affected_rows()1)
dbquery('insert into sometable set keyfield='$key', count=1');
---
First question: this is good method? Or I MUST use pre-query with
select count(...) from sometable where keyfield='$key' for detection:
exists needle record(for update) or not(for insert)???
Second... My script work on many different hosts with different
hardware, os(only Linux or FreeBSD), and different PHP and MySQL
version. It works fine excepting one thing... Sometime happens errors
like:
MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert into
sometable set keyfield='somekeyvalue', count=1
Why this happens? On some hosts this messageis very rare, but on others
- so often... I cant understand reason :(
My script use DB locks, so two clients cant make this situation(i
think).
Only one idea: sometime 'update' query dont work(or work, but dont
return good result), and 'insert' query started... But WHY?
Help me plz.
Thnx.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: **[SPAM]** Help with PHP to MySQL db connection
[snip] $dbhost = 'localhost'; [/snip] Try changing this to 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: weird kind of join
You might also try
FROM table_a
INNER JOIN table_b
ON table_b.code = substring_index(table_a.code,';',1)
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of the
delimiter delim. If count is positive, everything to the left of the final
delimiter (counting from the left) is returned. If count is negative,
everything to the right of the final delimiter (counting from the right) is
returned.
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
- 'www.mysql'
mysql SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
- 'mysql.com'
This function is multi-byte safe.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 29, 2004 12:35 PM
To: Diana Castillo
Cc: [EMAIL PROTECTED]
Subject: Re: weird kind of join
try this (not tested):
FROM table_a
INNER JOIN table_b
ON table_b.code LIKE concat(table_a.code,';%')
or this:
FROM table_a
INNER JOIN table_b
ON table_b.code RLIKE concat('^',table_a.code,';')
http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html
http://dev.mysql.com/doc/mysql/en/Regexp.html
It's not going to be as quick as a direct lookup because of the CONCAT()
but at least we preserve the possibility of using an index for
table_b.code.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Diana Castillo [EMAIL PROTECTED] wrote on 09/29/2004 12:39:40 PM:
is there anyway to do a joint between a table that has codes like
this 10004;XXX or DE;YYY
with a table that has just the first part e.g 10004 or DE as the code
There is no set length to the code , all I know is that it is the
part before the semicolon.
so, I can't say
FROM table_a INNER JOIN table_b ON (table_a_code =
left(table_b.code,2))
because I will only match the ones that have 2 character codes.
Diana Castillo
Global Reservas, S.L.
C/Granvia 22 dcdo 4-dcha
28013 Madrid-Spain
Tel : 00-34-913604039 Ext 216
Fax : 00-34-915228673
email: [EMAIL PROTECTED]
Web : http://www.hotelkey.com
http://www.destinia.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
display only populated fields
How do I go about only displaying fields that have data and are not empty or have NULL? It's a 112 column table so I only want to display the fields that have data. Thanks. select * from table where . - Do you Yahoo!? Yahoo! Mail is new and improved - Check it out!
Re: display only populated fields
If you are using something to run this query, what is it? What tool(s) are you using to retrieve/view the data? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Tracy Gems [EMAIL PROTECTED] wrote on 09/28/2004 05:59:08 PM: How do I go about only displaying fields that have data and are not empty or have NULL? It's a 112 column table so I only want to display the fields that have data. Thanks. select * from table where . - Do you Yahoo!? Yahoo! Mail is new and improved - Check it out!
mysqldump: Error 2013
Hello - Since recently switching from MyISAM to InnoDB tables in 4.1 on RH Linux I receive this error when attempting to dump some tables. mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `campaigns` at row: 14721 Any idea what could be causing this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: display only populated fields
- Original Message - From: Tracy Gems [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 28, 2004 5:59 PM Subject: display only populated fields How do I go about only displaying fields that have data and are not empty or have NULL? It's a 112 column table so I only want to display the fields that have data. Thanks. select * from table where . I don't think there's any way of doing that. I've been working with relational databases, mostly DB2, for over 20 years and have never seen anything like that - nor any need for it. I don't really see how it could work: since every row can be different from any other row and since a result set can only have a specified set of columns, what would you want to see if the 1st row had columns 1-10 non-blank and the remaining columns blank or null but the 2nd row had columns 1-10 BLANK or null and the remaining columns non-null? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (if !update then insert) sequence - result Duplicate key :(
Hi!
Sorry for my ugly English.
I use mysql in my PHP script(for calculating INs and OUTs from site).
And I meet some strange thing there.
Example goal: store statistics for clicks on some links.
I use next algorithm:
---
$key='somekeyvalue';
dbquery(update sometable set count=count+1 where keyfield='$key');
if(mysql_affected_rows()1)
dbquery('insert into sometable set keyfield='$key', count=1');
---
First question: this is good method? Or I MUST use pre-query with
select count(...) from sometable where keyfield='$key' for detection:
exists needle record(for update) or not(for insert)???
This method is fine. That is to say that I have seen it before in older
code. However, you might take alook at REPLACE:
http://dev.mysql.com/doc/mysql/en/REPLACE.html
And for MySQL 4.1.x you might take a look at INSERT ... ON DUPLICATE KEY
UPDATE
http://dev.mysql.com/doc/mysql/en/INSERT.html
Second... My script work on many different hosts with different
hardware, os(only Linux or FreeBSD), and different PHP and MySQL
version. It works fine excepting one thing... Sometime happens errors
like:
MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert into
sometable set keyfield='somekeyvalue', count=1
I would look at your logic. What is $key? Where does it come from? If
`keyfield` is a unique index then you cannot have duplicate values.
Why this happens? On some hosts this messageis very rare, but on others
- so often... I cant understand reason :(
My script use DB locks, so two clients cant make this situation(i
think).
Do a little research to find out if your locking is working.
Only one idea: sometime 'update' query dont work(or work, but dont
return good result), and 'insert' query started... But WHY?
Again, I would look at your logic and try to use REPLACE or INSERT ... ON
DUPLICATE KEY UPDATE.
Regards,
Jim Grill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with PHP to MySQL db connection
This is really just a PHP question, I think. Are MySQL and Apache running on the same machine? What version of MySQL are you running? Typically, you need a PHP mysql_connect() call including the MySQL host, username, and password. You only list the host above. With a default installation of MySQL, which gives you a root user with no password, you could connect like: $db = mysql_connect( localhost, root, ); but hopefully you've already given your root user a password, so put whatever password that was in there. Instead of creating the [EMAIL PROTECTED] user (I think that PHP will often run as nobody, perhaps Apache is set up that way also), create a user with a username/password and privileges that are sufficient for what you want to do. Then connect using that information. Wes On Wed, 29 Sep 2004 12:43:57 -0500, Andrew Lietzow [EMAIL PROTECTED] wrote: Hola, MySQL wizards. I'm using LAMP on Fedora Core 2. Some kind soul produced a tutorial on the net and the first script, birthdays_create_database.php, I am trying to use yields an error in the browser of: Couldn't connect to MySQL Here is the script. --- htmlheadtitleBirthdays Create Database/title/head body ?php $dbhost = 'localhost'; $link = mysql_connect($dbhost); if (! $link) die(Couldn't connect to MySQL); //create database mysql_create_db(mydatabase)or die(Create Error: .mysql_error()); mysql_close($link); ? /body /html --- I can access all of my databases and tables with phpMyAdmin just fine, and I can access them at the command line. Of course I didn't know which user I was when was attempting to execute this php script through the browser, so I check my mysqld.log file and it claims I should be '[EMAIL PROTECTED]', so I setup this user in the mysql database. I tried adding $dbuser = 'nobody'; $dbpass = ''; to the script but to no avail even though I can access through the command prompt with mysql -u nobody -p with a null password. When I do a ps -elf | grep mysql the results show a --port=3306 as well as 10 connection PID's. My hello.php script works fine, as do other php scripts. When I list netstat -l | grep mysql* however, the number that gets displayed in the column after STREAM LISTENING and before /var/lib/mysql/mysql.sock is 999269. I was expecting it to be 3306? How do I troubleshoot this Cannot connect to MySQL error message? My mysqld.log looks normal other than a Warning: Asked for 196608 thread stack, but got 126976. The mysql.sock line shows port: 3306. Any ideas on what to check next? I'd at a loss. TIA Andrew L. in the Heartland (not the Hinterlands) -- 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: (if !update then insert) sequence - result Duplicate key :(
Thanx for reply!
, 29.09.2004, 23:21, Jim Grill :
$key='somekeyvalue';
dbquery(update sometable set count=count+1 where keyfield='$key');
if(mysql_affected_rows()1)
dbquery('insert into sometable set keyfield='$key', count=1');
---
First question: this is good method? Or I MUST use pre-query with
select count(...) from sometable where keyfield='$key' for
detection:
exists needle record(for update) or not(for insert)???
This method is fine. That is to say that I have seen it before in
older
code. However, you might take alook at REPLACE:
http://dev.mysql.com/doc/mysql/en/REPLACE.html
If I understended this command right - its not for me.
There is I cant set different values for different cases(record exists
and not)...
And for MySQL 4.1.x you might take a look at INSERT ... ON DUPLICATE
KEY
UPDATE
http://dev.mysql.com/doc/mysql/en/INSERT.html
Not for me too, becouse my script MUST work fine on 3.x too.
Second... My script work on many different hosts with different
hardware, os(only Linux or FreeBSD), and different PHP and MySQL
version. It works fine excepting one thing... Sometime happens
errors
like:
MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert
into
sometable set keyfield='somekeyvalue', count=1
I would look at your logic. What is $key? Where does it come from? If
`keyfield` is a unique index then you cannot have duplicate values.
Yes. Keyfield is UNIQUE. For example we want track url hits in format:
...someurl.php?link=linkname1
// insert ... set link='linkname1', count=1
// or
// update ... set count=count+1 where link='linkname1'
...someurl.php?link=linkname2
// insert ... set link='linkname2', count=1
// or
// update ... set count=count+1 where link='linkname2'
...etc...
look: for insert count value=1, for update - increment...
can I use REPLACE in this situation? (without 'select count where
link='linkname...' :)))
Why this happens? On some hosts this messageis very rare, but on
others
- so often... I cant understand reason :(
My script use DB locks, so two clients cant make this situation(i
think).
Do a little research to find out if your locking is working.
its work while I testing, but I not sure that its work at moments when
my problem is happens... But track this moments is too hard for me,
becouse its happens not regulary and wihout any stable(visible) reasons
:(
Only one idea: sometime 'update' query dont work(or work, but dont
return good result), and 'insert' query started... But WHY?
Again, I would look at your logic and try to use REPLACE or INSERT ...
ON
DUPLICATE KEY UPDATE.
not for my clients :(
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing data into mysql from oracle using a text file
Hi, I did imported your data im my server but I had to do some changes to sample.txt. I have replaced in a text editor tab\t with empty string. tab is a real tab while \t is just a text. Additional change was to replace \n (which is text but not newline with empty string). The I did the following (before that I have created the table) : mysql load data local infile /home/andrey/Desktop/sample2.txt into table ALARM FIELDS ESCAPED BY '\\'; Query OK, 1 row affected, 1 warning (0.03 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 mysql show warnings; +-+--++ | Level | Code | Message| +-+--++ | Warning | 1261 | Row 1 doesn't contain data for all columns | +-+--++ 1 row in set (0.00 sec) mysql select * FROM ALARM; ++++--++--+---+--+-++ | ARRIVED| DETECTED | NAME | TYPE | ALARMLEVEL | VERIFIED | DISCLOSED | CATEGORY_NUM | EVENTID | REASON | ++++--++--+---+--+-++ | 2004-09-27 | 2004-09-27 | alaram |0 | 0 | NULL | 0 | 2 | 1 | NULL | ++++--++--+---+--+-++ 1 row in set (0.02 sec) You can see the warning since the number of fields was less the needed. Or maybe you wanted by having \n to express NULL? Last thing to do over sample.text is to mark all places where NULL should appear with \N . Hope this helps, Andrey [EMAIL PROTECTED] wrote: Tha sample lines are like this in alarm.txt ARRIVED DETECTED NAME TYPE ALARMLEVEL V DISCLOSED CATEGORY_NUM EVENTID - - - -- - - - REASON 27-SEP-04 27-SEP-04 alaram 0 0 N 02 1 27-SEP-04 27-SEP-04 MiTel0 0 N 02 2 The above lines i am not able to insert into mysql. After changing the format as below(as in the sample.txt) i am able to insert into mysql but not correclty. Couls you please help me in this. 2004-09-27 \t 2004-09-27 \t alaram \t 0 \t 0 \t N \t 0 \t 2 \t 1 \n thanks, narasimha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (if !update then insert) sequence - result Duplicate key :(
I have another technique for this kind of data merge that speeds things up
(a lot!) but it requires a third table to make it work
Please post the results of SHOW CREATE TABLE for both the source data
table and the destination data table and I will show you how it works.
Thanks,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Aleksander V. Dyomin [EMAIL PROTECTED] wrote on 09/29/2004 04:22:02 PM:
Thanx for reply!
, 29.09.2004, 23:21, Jim Grill :
$key='somekeyvalue';
dbquery(update sometable set count=count+1 where keyfield='$key');
if(mysql_affected_rows()1)
dbquery('insert into sometable set keyfield='$key', count=1');
---
First question: this is good method? Or I MUST use pre-query with
select count(...) from sometable where keyfield='$key' for
detection:
exists needle record(for update) or not(for insert)???
This method is fine. That is to say that I have seen it before in
older
code. However, you might take alook at REPLACE:
http://dev.mysql.com/doc/mysql/en/REPLACE.html
If I understended this command right - its not for me.
There is I cant set different values for different cases(record exists
and not)...
And for MySQL 4.1.x you might take a look at INSERT ... ON DUPLICATE
KEY
UPDATE
http://dev.mysql.com/doc/mysql/en/INSERT.html
Not for me too, becouse my script MUST work fine on 3.x too.
Second... My script work on many different hosts with different
hardware, os(only Linux or FreeBSD), and different PHP and MySQL
version. It works fine excepting one thing... Sometime happens
errors
like:
MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert
into
sometable set keyfield='somekeyvalue', count=1
I would look at your logic. What is $key? Where does it come from? If
`keyfield` is a unique index then you cannot have duplicate values.
Yes. Keyfield is UNIQUE. For example we want track url hits in format:
...someurl.php?link=linkname1
// insert ... set link='linkname1', count=1
// or
// update ... set count=count+1 where link='linkname1'
...someurl.php?link=linkname2
// insert ... set link='linkname2', count=1
// or
// update ... set count=count+1 where link='linkname2'
...etc...
look: for insert count value=1, for update - increment...
can I use REPLACE in this situation? (without 'select count where
link='linkname...' :)))
Why this happens? On some hosts this messageis very rare, but on
others
- so often... I cant understand reason :(
My script use DB locks, so two clients cant make this situation(i
think).
Do a little research to find out if your locking is working.
its work while I testing, but I not sure that its work at moments when
my problem is happens... But track this moments is too hard for me,
becouse its happens not regulary and wihout any stable(visible) reasons
:(
Only one idea: sometime 'update' query dont work(or work, but dont
return good result), and 'insert' query started... But WHY?
Again, I would look at your logic and try to use REPLACE or INSERT ...
ON
DUPLICATE KEY UPDATE.
not for my clients :(
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (if !update then insert) sequence - result Duplicate key :(
Aleksandr V. Dyomin wrote:
$key='somekeyvalue';
dbquery(update sometable set count=count+1 where keyfield='$key');
if(mysql_affected_rows()1)
dbquery('insert into sometable set keyfield='$key', count=1');
Another possibility would be
INSERT IGNORE INTO sometable SET keyfield = '$key', count = 0;
UPDATE sometable SET count = count + 1 WHERE keyfield = '$key';
--
Keith Ivey [EMAIL PROTECTED]
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter table from myisam to innodb in one go?
Hello, if you do an alter table, altering some indexes _and_ converting it to innodb, will mysql do the altering and converting in one pass, or will it first change the indexes and than start the innodb conversion? For example, with an myisam table: alter table my_table, drop index an_index, add index(some, columns), type=innodb; Any ideas? Thanks, Harmen -- The Moon is Waning Gibbous (98% of Full) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table is full
Hi, I am running 4.1.0-alpha on a linux machine. When I use mysqlimport to load a big data file (~7 Gb), I get an error mysqlimport: Error: The table 'mytable' is full, when using table: mytable. I guess the table is too big. Is there any solution for this problem? Thanks a million! Qunfeng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disk interference when running 2 databases
Hi, I am running 2 database on the same MySQL server on one machine. When running this configuration, I notice that the disk usage is very high. On other hand, If run just one workload, the disk usage is very low. To be certain, I ran 2 servers running 1 database each on different ports. I see the same results with config too. Just some numbers to clarify: I ran vmstat 1. The bi is ~4000 when running 2 databases while only ~500 when running 1 database. Questions: Does MySQL flush its cache or some internal memory everytime it executes a query for another database? Thanks, Gokul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table is full
On Wed, Sep 29, 2004 at 05:24:42PM -0500, Qunfeng wrote: Hi, I am running 4.1.0-alpha on a linux machine. When I use mysqlimport to load a big data file (~7 Gb), I get an error mysqlimport: Error: The table 'mytable' is full, when using table: mytable. I guess the table is too big. Is there any solution for this problem? Thanks a million! You didn't search the web for the answer before posting, did you? :-) I'll take a stab in the dark here: http://jeremy.zawodny.com/blog/archives/000796.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: importing data into mysql from oracle using a text file
Thanks, it is working for the mentioned format. Is there any method for importing directly the spooled file from oracle without changing the file format into the required format like using tab and newline. thanks, narasimha -Original Message- From: Andrey Hristov [mailto:[EMAIL PROTECTED] Sent: Thu 9/30/2004 2:01 AM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: [EMAIL PROTECTED] Subject: Re: importing data into mysql from oracle using a text file Hi, I did imported your data im my server but I had to do some changes to sample.txt. I have replaced in a text editor tab\t with empty string. tab is a real tab while \t is just a text. Additional change was to replace \n (which is text but not newline with empty string). The I did the following (before that I have created the table) : mysql load data local infile /home/andrey/Desktop/sample2.txt into table ALARM FIELDS ESCAPED BY '\\'; Query OK, 1 row affected, 1 warning (0.03 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 mysql show warnings; +-+--++ | Level | Code | Message| +-+--++ | Warning | 1261 | Row 1 doesn't contain data for all columns | +-+--++ 1 row in set (0.00 sec) mysql select * FROM ALARM; ++++--++--+---+--+-++ | ARRIVED| DETECTED | NAME | TYPE | ALARMLEVEL | VERIFIED | DISCLOSED | CATEGORY_NUM | EVENTID | REASON | ++++--++--+---+--+-++ | 2004-09-27 | 2004-09-27 | alaram |0 | 0 | NULL | 0 | 2 | 1 | NULL | ++++--++--+---+--+-++ 1 row in set (0.02 sec) You can see the warning since the number of fields was less the needed. Or maybe you wanted by having \n to express NULL? Last thing to do over sample.text is to mark all places where NULL should appear with \N . Hope this helps, Andrey [EMAIL PROTECTED] wrote: Tha sample lines are like this in alarm.txt ARRIVED DETECTED NAME TYPE ALARMLEVEL V DISCLOSED CATEGORY_NUM EVENTID - - - -- - - - REASON 27-SEP-04 27-SEP-04 alaram 0 0 N 0 2 1 27-SEP-04 27-SEP-04 MiTel0 0 N 0 2 2 The above lines i am not able to insert into mysql. After changing the format as below(as in the sample.txt) i am able to insert into mysql but not correclty. Couls you please help me in this. 2004-09-27 \t 2004-09-27 \t alaram \t 0 \t 0 \t N \t 0 \t 2 \t 1 \n thanks, narasimha 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.
Re: Newbie question - Input limitations on mysql client?
On Wed, 29 Sep 2004 10:05:29 -0400, Ted Byrne [EMAIL PROTECTED] wrote: Is the apparent line length limit a restriction imposed by the client? (And does it apply if you are piping or redirecting output from another process or a file?) It sounds like you should either be using a scripting language (like Perl or PHP), or using the command line tool mysqlimport or LOAD DATA INFILE from the mysql client. Any of those methods should allow you to insert long rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Different default collation in each connection
Ok, I thought that I will be misunderstood... I'll try to explain this in example below: I have table CREATE TABLE `product_descriptions` ( `id_product_description` mediumint(8) unsigned NOT NULL auto_increment, `id_product` mediumint(8) unsigned NOT NULL default '0', `lang` varchar(2) default NULL, `text_data` text, PRIMARY KEY (`id_product_description`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 in column `text_data` i have all texts in all languages (pl, en, ru etc.) So the problem is that this column is meant to store every collation for this language but in UTF-8. Now, when I get records from this table I should give database some info that I will use i.e. 'utf_8_polish_ci' collation on whole page - I don't need to change it on whole page (I use PHP by the way) This solution I use is very handy as when I need to add some language to my pages then I only add some info in application about what language this would be and... this should work. I can't change database structure on every language upgrade. Besides I would have for example 20 columns with different collations like: text_data_pl text_data_ru text_data_ro text_data_en text_data_... and this table is not the only one which stores information in different languages... So please consider this and please if you know tell me what collation_connection is for. -- Use the force - read the source Piotr Duszynski mailto:[EMAIL PROTECTED] Ok, now I think I understand better... from mysql manual: collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, it does not matter because columns have a higher collation precedence. http://dev.mysql.com/doc/mysql/en/Charset-connection.html If I understand this correctly this means that the _only_ time collation_connection is used is for an SQL statement like this: SELECT col1 FROM table1 WHERE stringliteral1 = stringliteral2; This isn't a very useful SQL query but it is legal and the only way to know how to compare the two string literals is to have a variable called collation_connection. When you are comparing, for example, a string literal to a column the collation of the column has a higher precedence than the collation_connection variable which is used for the string. Therefore the _only_ way to compare a string against a column without using the column's default collation is to explicitly specify which collation to use in the comparison with a COLLATE clause like this COLLATE utf8_polish_ci. It might be a little more work but you'll probably have to dynamically add a COLLATE clause to all your queries based on the language you want. best regards, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re:RE: Indexing problem with UTF8 in 4.1.4?
Now by our reconing the key of the fields is 343 bytes, encoding in UTF8 makes that key 343 bytes Not 1000 since under utf8 each character is encode in 8 bits. If it is converting to utf16 internally then the key would be 328*2+5+4 which is not 1000 bytes. If you only use the ascii range of utf8 then you are right that you are only _storing_ 1 byte per character. Mysql, however, must still _reserve_ 3 bytes for each character because other languages require more bytes per character (up to six but I believe mysql only supports 3 bytes for utf8 characters right now). So using your count of 343 * 3 = 1029. The index prefix lengths, by the way, are in characters not bytes. best regards, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (if !update then insert) sequence - result Duplicate key :(
, 30.09.2004, 00:50, Keith Ivey :
Aleksandr V. Dyomin wrote:
$key='somekeyvalue';
dbquery(update sometable set count=count+1 where keyfield='$key');
if(mysql_affected_rows()1)
dbquery('insert into sometable set keyfield='$key', count=1');
Another possibility would be
INSERT IGNORE INTO sometable SET keyfield = '$key', count = 0;
UPDATE sometable SET count = count + 1 WHERE keyfield = '$key';
Not too good method. There is TWO queries ALWAYS, but in my method 1
query for updates(if record exists, which is very often) and two query
for inserts(if record dont exists).
But one question: will this work faster then:
select count(...) as cnt from sometable where keyfield='$key';
...
if($row['cnt']0)
update...
else
insert...
In other words, which method(now 3 known methods) fastes?
1) select - (insert or update)
2) update - (insert if fail)
3) ignoring insert - update
???
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (if !update then insert) sequence - result Duplicate key :(
, 30.09.2004, 00:37, [EMAIL PROTECTED] :
I have another technique for this kind of data merge that speeds things up
(a lot!) but it requires a third table to make it work
Please post the results of SHOW CREATE TABLE for both the source data
table and the destination data table and I will show you how it works.
I dont have two tables - only one...
for example:
create table links (
link varchar(20) not null default '',
count int not null default 0,
primary key(link)
)
And I need count how many times each link is clickes... trivial...
But my main trouble is 'Duplicates'...
Another example... I have table:
create table ipsinouts (
ip int not null,
inout enum('in', 'out') not null,
sitefaceid int not null,
cnt tinyint not null,
primary key (inout, ip, sitefaceid)
)
'in' mean incoming to site, in this case sitedfaceid field mean
FACE(some page) ID. 'out' mean OUT to other site(traffic trade) and
sitefaceid mean SITE ID... ip is ip2long(...) result...
So, there is I have sometime duplicates for key, but I dont see
theoretical reasons for this errors :(((
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing Images In MySQL
I figured I'd post a follow-up to the discussion earlier this week on issues relating to storing images in MySQL. In my opinion. Whether you store images in MySQL ultimately depends on whether you can setup a caching accelerator (like Squid) between you and your visitors. Storing images in the database adds a minimum of around 28 ms of latency. Cacheing makes this a mute point since images are once again stored as files. The main benefit is that you can more easily manage a large number of images by storing them in the database. Most people, those on shared hosting services, aren't going to likely have access to a caching accelerator, so originally storing images as files is probably going to be the best approach. The most common approach that I've seen is to create some sort of directory hiearchy and divide images in groups of 1000. Two other points came up while I was playing around with this. People on dial-up accessing test pages didn't notice any difference in performance - their average ping times to the server were around 120ms. I'm guessing their connection latency helped to buffer the difference between the two approaches. Pulling images from the database usually involves a script with a parameter that indicates which image to display. For example, like this: img src='http://www.myhost.com/display_image.php?id=5' Locally installed firewalls (Zone Alarm, McAfee Internet Security, and Norton Internet Security) all blocked the display of images that were served with a script like this. Getting the images to display required renaming the script, using Apache forcetype commands, and manually parsing the URL to get the image id to display. In other words, the url ended up like this: img src='http://www.myhost.com/display_image/5' Anyone who sends me a request can get copies of the scripts and help on trying to set something up to duplicate what I did. Thanks to everyone who helped me with this - especially Dreamwerx. -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
