problem with mutated vowels (öäü)
Hello, I've a problem with mutated vowels like öäü by using the mysql client. Nothing happens if I type ö,ä or ü. The input seems to be ignored. OS is Solaris 9 Sparc 32 Bit. MySQL Version 5.0.45 (I tried the source and binary version.) By typing in the shell (csh, ksh) or using the vi there is no problem. Is there a simple solution like setting a LANG variable or changing a character set? Thanks. Regards, Spiker -- Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query performance plain/text versus AES_DECRYPT(): LIKE %..%
I put it here: http://pro.datisstom.nl/tests/bench.tar.bz2 The encryption isn't really a *real* security measure, except for when somebody is stupid enough to install phpMyAdmin or anything equivalent and try to get personal data. The problem is the password needs to be anywhere on the application-server and if you're in, you're in. But it's a request and I'm happy to oblige. Even if it only stops them for 1 minute (which could be enough). Regards, John -- / Humanique / Webstrategie en ontwikkeling / http://www.humanique.com/ - Humanique zoekt een ervaren Web-ontwikkelaar (PHP). Bekijk de vacature op http://www.humanique.com/ - Jeremy Cole wrote: Hi John, Your attachment for the php code got stripped somewhere. Can you post it somewhere (http preferable)? In either case it's going to result in a full table scan, so they are actually both a bad strategy long term, but they should in theory perform as you would expect, with with encryption being slightly slower. Have you tried with longer strings? What is your customer's fear with having the data in plain text? Presumably in order to use this in your application, you will have the AES password stored in your application, and it will end up in logs (such as the slow query log) quite frequently. I would think your data can be safer and your security more effective by setting some policies which are less intrusive into the actual workings of the data, such as encrypting backups and setting system-level policies. Regards, Jeremy John Kraal wrote: Dear you, I've been working on encrypting some data for a customer. They want their personal/sensitive information encrypted in the database, but they want to be able to search it too, through the application. So we've been thinking a bit, and just started trying and benchmarking some solutions we thought up. The next one really got my attention, I created a table with 4 fields: 1. id (primary/auto_increment, not really interesting) 2. field, with encrypted data 3. md5sum (it has no special use, we benched it though.) 4. line, always containing three words (the same three as encrypted) When we started querying the table for random words (from lipsum.com), it seems that searching in the encrypted fields was _lots_ faster. Results below: 1.000 queries per field: ~$ php -q searchtest.php Control test (plain/text LIKE %..%):1.409699s Decrypt test (AES_DECRYPT() LIKE %..%): 1.226069s done 1.000.000 queries per field: ~$ php -q searchtest.php Control test (plain/text LIKE %..%):155.059671s Decrypt test (AES_DECRYPT() LIKE %..%): 137.003216s done Actually, the only thing I could think of to say was: Well, at least it's consistent. I've attached all the files I used for this test. Edit db.inc.php (add some more lipsum if you want), execute fill.php, and then have fun with bench.php. Does any of you know why this is, how come, etc? I'm just very curious. Regards, John Kraal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table crashes
[EMAIL PROTECTED] wrote: Can someone help me out on this..I need a detailed document Dude, you need to be specific. Why tables in MySQL crash is very broad topic. which engine you are referring to ? Tables can crash due to various reasons and they have different recovery/repair procedures. good application + DB design/descent HW sizing and great monitoring will help reduce the crashes If you do some more research on this topic you can figure out some reasons and some pointers to avoid failures. Regards, Ranjeet Walunj Hi Friends, I need a document on why tables crash in MYSQL and what are the repair methods? I would also like to know if there are any preventive measures to avoid table crashes? Regards, Amarnath Shivashankar SQL Database Management 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 proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql not starting
this has been a problem to me from mysql 3.1 version now in 4.1 also the same problem during the instant configuration wizard the last screen which has start service does not turn green and mysql doesnt start ive tried removing mysql and reinstalling does this problem has anything to do with port or what is it please help? rao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and AUTO_INCREMENT; is it safe?
On 10/24/07, Eric Frazier [EMAIL PROTECTED] wrote: js wrote: Hi list, Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. Let's say there are two server, A and B. A replicates its data to B, the slave. A and B has a table that looks like(column 'id' is auto_increment field) id value 1 a 2 b 3 c 4 d If After delete from table where id = 4 and restart mysqld on server B, insert into table (value) values(e) is executed on server A. In this case, because A's internal counter is 4, table on A would be 1 a 2 b 3 c 5 e But B's would be different because restarting mysqld flushed InnoDB's internal counter. 1 a 2 b 3 c 4 e Is this correct? or MySQL is smart enough to handle this problem? Thanks. [1]http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html http://dev.mysql.com/doc/refman/5.0/en/faqs-general.html See 28.1.5 But there are more reasons to avoid auto-increment in mysql. I haven't run into the problem above, but I have had such problems when restoring backups. Make your data make sense, a mindless counting number just to make a table unique doesn't every make any sense. Session ids, timestamps, combinations of fields all make much better primary keys and it is safer overall to implement a counter function in your app than to trust mysql's js wrote: Thank you for your reply. But I couldn't under stand how --auto-increment-increment and --auto-increment-offset helps me avoid my problem. Could you please explain? Restarting the server doesn't reset autoinc.. But that can happen when you restore a backup, I don't remember what to avoid of the top of my head, but look into mysqldump and do some tests. Best way to understand But, you can avoid any problem with autoinc by just not using it. If you must use it for replication it is quite safe to use it if you are only replicating to a slave write only, so the slave is not also another master(you are not doing inserts/updates on the slave as well), or if you need to replicate in a circle use auto-increment-increment etc. I think it is not a bad idea to use these even if your slave is just a slave. Bottom line, if you are designing a DB, for max safety avoid autoinc entirely. It will save you headaches for a little extra work to start. This is one area where MySQL still deserves some jeering because Postgress had this figured out a long time ago with proper sequences that are a lot easier to mange. With all of the features and cool stuff MySQL has added in the last few years, I don't get why they haven't fixed autoinc or added a true sequence type. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with mutated vowels (öäü)
Hello, I found the solution by my own: set meta-flag on set convert-meta off set output-meta on Regards, Spiker -- GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and AUTO_INCREMENT; is it safe?
Hello, On Oct 23, 2007, at 11:23 AM, js wrote: Hi list, Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. Let's say there are two server, A and B. A replicates its data to B, the slave. A and B has a table that looks like(column 'id' is auto_increment field) cut Is this correct? or MySQL is smart enough to handle this problem? The binary logs in MySQL store the generated auto_increment id and use that instead of generating a new value on the slave. If you run mysqlbinlog on a binary log, you will see an output similar to: # at 728 #071024 10:53:54 server id 1 end_log_pos 28Intvar SET INSERT_ID=3/*!*/; # at 756 #071024 10:53:54 server id 1 end_log_pos 124 Query thread_id=3 exec_timSET TIMESTAMP=1193237634/*!*/; insert into ib_test values (NULL)/*!*/; The SET INSERT_ID functionality will cause the next INSERT to use that value for the auto_increment regardless of what it would have generated. Regards, Harrison -- Harrison C. Fisk, Principal Support Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication still stopping...
A couple of thoughts. Do you have slaves with duplicated server IDs? That seems most likely to me. Nope. I've got one master, and one slave. The server ID is set to 1 on the master, and it's set to 2 on the slave. If that's not it, is the max_packet_size mismatched on the master and slave? I don't find max_packet_size in the My.ini file on either server, and when I do a show variables on both, max_packet_size is not listed on either of them. Can you connect to the master and view the binary log event at the position it's trying to read, with SHOW BINLOG EVENTS? That's where things get squirley. The position it reports always seems to be incorrect. For instance, when this was happening previously, I know that it had made it to a later position in the log. However, when replication stopped, it reported a position earlier in the file. This one, for instance, reports position 195. the Nearest one I have starts at position 98 and ends at position 1032. This is an update statement. If my logic is not flawed, I'm thinking that I should follow starting at 98 out until I get to position 195. When I do that, I come to: RegOpenDate = '2007-11-05 00:00:00', which is part of the udpate statement. This appears normal to me. I've checked, and it is a DateTime field, and it is exactly the same on both the master and slave. Can you use the mysqlbinlog tool to verify that the binary log isn't corrupted on the master? I've dumped the log to a text file. What, exactly, should I look for? The only suspicious thing I see is the first entry: # at 4 #071020 15:45:34 server id 1 end_log_pos 98Start: binlog v 4, server v 5.0.17-nt-log created 071020 15:45:34 at startup # Warning: this binlog was not closed properly. Most probably mysqld crashed writing it. ROLLBACK; Don't know why it would do this. However, I set the master_log_pos to 98 before re-starting the slave after re-setting it last time. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] Memory Usage on Windows? Re: Replication still stopping...
as i can see you are running mysql on windows. If i start my db server (5.0.45/innodb/win2k) the server uses about ~80K handles (as seen in taskmgr) and memory usage increases around 1g. Taskmgr.exe says that there is some swapping (the box has only 1gb ram). The DB itself is small (~50mb or so). My Question is, did you have the same things on your box? Did you have performace issues which resultes from the memory usage? I can't even keep it running for longer that 24 hours, and I don't know why I haven't even started looking into memory issues or performance. When it is runnning, as a test, I change a record on the master, and I notice that almost immediately, the same change is made on the slave. Works perfectly for a few hours, then it just stops working. It almost appears to be a network related issue, but I can't seem to track it down. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication still stopping...
Jesse wrote: A couple of thoughts. Do you have slaves with duplicated server IDs? That seems most likely to me. Nope. I've got one master, and one slave. The server ID is set to 1 on the master, and it's set to 2 on the slave. If that's not it, is the max_packet_size mismatched on the master and slave? I don't find max_packet_size in the My.ini file on either server, and when I do a show variables on both, max_packet_size is not listed on either of them. Whoops, I got the name wrong: mysql show variables like '%packet%'; ++--+ | Variable_name | Value| ++--+ | max_allowed_packet | 16776192 | ++--+ 1 row in set (0.00 sec) Can you connect to the master and view the binary log event at the position it's trying to read, with SHOW BINLOG EVENTS? That's where things get squirley. The position it reports always seems to be incorrect. For instance, when this was happening previously, I know that it had made it to a later position in the log. However, when replication stopped, it reported a position earlier in the file. This one, for instance, reports position 195. the Nearest one I have starts at position 98 and ends at position 1032. This is an update statement. If my logic is not flawed, I'm thinking that I should follow starting at 98 out until I get to position 195. When I do that, I come to: RegOpenDate = '2007-11-05 00:00:00', which is part of the udpate statement. This appears normal to me. I've checked, and it is a DateTime field, and it is exactly the same on both the master and slave. That's strange. I'm not sure I understand what's happening there. Check the packet size and let's come back to this if that's not the problem. Can you use the mysqlbinlog tool to verify that the binary log isn't corrupted on the master? I've dumped the log to a text file. What, exactly, should I look for? The only suspicious thing I see is the first entry: # at 4 #071020 15:45:34 server id 1 end_log_pos 98Start: binlog v 4, server v 5.0.17-nt-log created 071020 15:45:34 at startup # Warning: this binlog was not closed properly. Most probably mysqld crashed writing it. ROLLBACK; That's fine --it just means the log is still open. (It is still open, right?) If you run this on a log other than the newest one, you shouldn't see that. If there was corruption, the mysqlbinlog tool would have crashed. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Left outer joins, where clause and table_names
Hi, I have the following sql request: SELECT projects.`id` AS t0_r0, projects.`name` AS t0_r1, projects.`abbreviated_name` AS t0_r2, projects.`producer` AS t0_r3, projects.`tel_1` AS t0_r4, projects.`tel_2` AS t0_r5, projects.`recital` AS t0_r6, projects.`completed_flag` AS t0_r7, projects.`completed_at` AS t0_r8, projects.`created_at` AS t0_r9, projects.`update_at` AS t0_r10, materials.`id` AS t1_r0, materials.`created_at` AS t1_r1, materials.`work_id` AS t1_r2, materials.`work_type` AS t1_r3, materials.`comment` AS t1_r4, materials.`user_name` AS t1_r5, materials.`user_id` AS t1_r6, materials.`material_file_id` AS t1_r7, materials.`tag_id` AS t1_r8, tags.`id` AS t2_r0, tags.`name` AS t2_r1, tags.`project_id` AS t2_r2, uploaded_files.`id` AS t3_r0, uploaded_files.`size` AS t3_r1, uploaded_files.`content_type` AS t3_r2, uploaded_files.`filename` AS t3_r3, uploaded_files.`height` AS t3_r4, uploaded_files.`width` AS t3_r5, uploaded_files.`parent_id` AS t3_r6, uploaded_files.`thumbnail` AS t3_r7, forums.`id` AS t4_r0, forums.`name` AS t4_r1, forums.`description` AS t4_r2, forums.`topics_count` AS t4_r3, forums.`posts_count` AS t4_r4, forums.`position` AS t4_r5, forums.`description_html` AS t4_r6, forums.`work_id` AS t4_r7, forums.`work_type` AS t4_r8, posts.`id` AS t5_r0, posts.`user_id` AS t5_r1, posts.`topic_id` AS t5_r2, posts.`body` AS t5_r3, posts.`created_at` AS t5_r4, posts.`updated_at` AS t5_r5, posts.`forum_id` AS t5_r6, posts.`body_html` AS t5_r7, posts.`material_file_id` AS t5_r8, topics.`id` AS t6_r0, topics.`forum_id` AS t6_r1, topics.`user_id` AS t6_r2, topics.`subject` AS t6_r3, topics.`created_at` AS t6_r4, topics.`updated_at` AS t6_r5, topics.`hits` AS t6_r6, topics.`sticky` AS t6_r7, topics.`posts_count` AS t6_r8, topics.`replied_at` AS t6_r9, topics.`replied_by` AS t6_r10, topics.`last_post_id` AS t6_r11, topics.`tag_id` AS t6_r12, tags_topics.`id` AS t7_r0, tags_topics.`name` AS t7_r1, tags_topics.`project_id` AS t7_r2, readerships.`id` AS t8_r0, readerships.`user_id` AS t8_r1, readerships.`topic_id` AS t8_r2, readerships.`read` AS t8_r3, roles.`id` AS t9_r0, roles.`name` AS t9_r1, roles.`authorizable_type` AS t9_r2, roles.`authorizable_id` AS t9_r3, roles.`created_at` AS t9_r4, roles.`updated_at` AS t9_r5, users.`id` AS t10_r0, users.`login` AS t10_r1, users.`crypted_password` AS t10_r2, users.`salt` AS t10_r3, users.`family_name` AS t10_r4, users.`first_name` AS t10_r5, users.`affiliation` AS t10_r6, users.`tel` AS t10_r7, users.`email` AS t10_r8, users.`note` AS t10_r9, users.`active` AS t10_r10, users.`days_display_unit` AS t10_r11, users.`user_icon_id` AS t10_r12, users.`remember_token` AS t10_r13, users.`remember_token_expires_at` AS t10_r14, users.`position` AS t10_r15, users.`posts_count` AS t10_r16, users.`last_seen_at` AS t10_r17, users.`created_at` AS t10_r18, users.`updated_at` AS t10_r19, titles.`id` AS t11_r0, titles.`project_id` AS t11_r1, titles.`name` AS t11_r2, titles.`oa_date` AS t11_r3, titles.`oa_hour` AS t11_r4, titles.`oa_minute` AS t11_r5, titles.`slip_number` AS t11_r6, titles.`note` AS t11_r7, titles.`director` AS t11_r8, titles.`director_tel_1` AS t11_r9, titles.`director_tel_2` AS t11_r10, titles.`in_charge` AS t11_r11, titles.`in_charge_tel_1` AS t11_r12, titles.`in_charge_tel_2` AS t11_r13, titles.`recital` AS t11_r14, titles.`completed_flag` AS t11_r15, titles.`completed_at` AS t11_r16, titles.`position` AS t11_r17, titles.`created_at` AS t11_r18, titles.`updated_at` AS t11_r19, materials_titles.`id` AS t12_r0, materials_titles.`created_at` AS t12_r1, materials_titles.`work_id` AS t12_r2, materials_titles.`work_type` AS t12_r3, materials_titles.`comment` AS t12_r4, materials_titles.`user_name` AS t12_r5, materials_titles.`user_id` AS t12_r6, materials_titles.`material_file_id` AS t12_r7, materials_titles.`tag_id` AS t12_r8, tags_materials.`id` AS t13_r0, tags_materials.`name` AS t13_r1, tags_materials.`project_id` AS t13_r2, material_files_materials.`id` AS t14_r0, material_files_materials.`size` AS t14_r1, material_files_materials.`content_type` AS t14_r2, material_files_materials.`filename` AS t14_r3, material_files_materials.`height` AS t14_r4, material_files_materials.`width` AS t14_r5, material_files_materials.`parent_id` AS t14_r6, material_files_materials.`thumbnail` AS t14_r7, forums_titles.`id` AS t15_r0, forums_titles.`name` AS t15_r1, forums_titles.`description` AS t15_r2, forums_titles.`topics_count` AS t15_r3, forums_titles.`posts_count` AS t15_r4, forums_titles.`position` AS t15_r5, forums_titles.`description_html` AS t15_r6, forums_titles.`work_id` AS t15_r7, forums_titles.`work_type` AS t15_r8, posts_forums.`id` AS t16_r0, posts_forums.`user_id` AS t16_r1, posts_forums.`topic_id` AS t16_r2, posts_forums.`body` AS t16_r3, posts_forums.`created_at` AS t16_r4, posts_forums.`updated_at` AS t16_r5, posts_forums.`forum_id` AS t16_r6, posts_forums.`body_html` AS t16_r7, posts_forums.`material_file_id` AS t16_r8, topics_posts.`id` AS t17_r0,
Rows inserted into a table producing errno: 13
Hi I am running mysql version 4.0.27 on Red Hat Enterprise Linux 3 i had a myisam table with .frm, .MYD and .MYI files with no permissions for user mysql. I inserted several rows without getting any error message, but when i restarted mysqld, everything disapered and i get error message /usr/sbin/mysqld: Can't find file: './siwebes_siweb05/Imagenes_Chunks.frm' (errno: 13) when i select. Looking into log files i see that this message was already there. Is there any temporary file where my data could be stored? Any help or suggestions anyone can offer is greatly appreciated! Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left outer joins, where clause and table_names
tom wang wrote: Hi, I have the following sql request: [snipped, for the sake of the children] As you can see I have two left outerjoins involving the readerships table: LEFT OUTER JOIN readerships ON readerships.topic_id = topics.id and LEFT OUTER JOIN readerships readerships_topics ON readerships_topics.topic_id = topics_posts.id I'll take your word for it! and I have a condition on both of those tables in my where clause: WHERE (readerships.read != '1' OR readerships_topics.read != '1') Not that I'm going to pretend to understand the goal of the query, but couldn't you do: LEFT OUTER JOIN readerships ON readerships.topic_id = topics.id AND readerships.read != '1' AND LEFT OUTER JOIN readerships readerships_topics ON readerships_topics.topic_id = topics_posts.id AND readerships_topics.read != '1' Now what I was wondering is: is there a way to write something like : WHERE *.read != '1' Which would match all tables with a read column? No, you cannott use the asterisk selector in a WHERE condition. If not is there a way to match all readerships table in my where clause? so for example WHERE readerships.read != '1' where readerships.read would match both readerships and readerships_topic? readerships.read matches readerships.read, nothing more. I can't just the left outer join part as it's autogenerated but I can change the WHERE clause Autogenerated by what, exactly? Does it run at all the way it is? That is quite a monster SELECT statement. In fact, if it doesn't run now, perhaps all it requires is a good jolt of electricity. I'm not good a sql syntax, but I would love to learn Are you reasonably certain that your SELECT requires all that? Could your application maybe be adjusted so that you could break that up a little? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table crashes
The manual is your friend for everything, besides any additional book like Paul Dubois (cool book). But, in order to help you, we need more information about the crash: errors, version, table type, hardware, logs, etc. in that way we can offer solutions to your specific issue. feel free to send us more details and we'll be happy to help. Carlos [EMAIL PROTECTED] wrote: Can someone help me out on this..I need a detailed document Regards, Amarnath Shivashankar SQL Database Management | GSMC | Wipro Infotech | Mysore | Toll free: 1800-345-5656 | Spirit of Wipro : Intensity to Win | Act with Sensitivity | Unyielding Integrity From: Amarnath Shivashankar (WI01 - Services) Sent: Tuesday, October 23, 2007 11:40 AM To: 'mysql@lists.mysql.com' Subject: Table crashes Hi Friends, I need a document on why tables crash in MYSQL and what are the repair methods? I would also like to know if there are any preventive measures to avoid table crashes? Regards, Amarnath Shivashankar SQL Database Management 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 proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Concat alternative
Currently I am running a concat statement to combine a field with a user name and domain to create and email address. In testing it looks like running the concat is a very slow command to run. The select statement currently looks like this. select concat(user,'@',domain),servername,port from database where concat(user,'@',domain)='[EMAIL PROTECTED]';
Re: Concat alternative
On 10/24/07, Gerard [EMAIL PROTECTED] wrote:
Currently I am running a concat statement to combine a field with a user
name and domain to create and email address. In testing it looks like
running the concat is a very slow command to run. The select statement
currently looks like this.
select concat(user,'@',domain),servername,port from database where
concat(user,'@',domain)='[EMAIL PROTECTED]';
That query will be very slow because mysql will have to examine each
row. You would be far better served to do something like
select concat(user,'@',domain),servername,port
from database
where
user = substring('[EMAIL PROTECTED]',0,LOCATE('@','[EMAIL PROTECTED]'))
AND
domain = substring('[EMAIL PROTECTED]',LOCATE('@','[EMAIL PROTECTED]'))
or something like that, or even better split it outside mysql if possible.
--
Rob Wultsch
(480)223-2566
[EMAIL PROTECTED] (email/google im)
wultsch (aim)
[EMAIL PROTECTED] (msn)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query performance plain/text versus AES_DECRYPT(): LIKE %..%
Hi John, OK, no conspiracy here. Here is your problem: 25 $qry = sprintf(SELECT id, line FROM `encryptietest` WHERE AES_DECRYPT(`field`, '%') LIKE '%%%s%%', $enckey, $word); You are missing the s in %s for your first string argument, which causes the query to be syntactically incorrect and fail. So your AES test is only testing how quickly you can query with a syntax error. :) After adding the s, the results I get are: ([EMAIL PROTECTED]) [~/datisstom/bench]$ php -q bench.php Control test (plain/text LIKE %..%):1.383749s Decrypt test (AES_DECRYPT() LIKE %..%): 1.441944s done Nonetheless, I'd still argue that this entire concept is doomed to terrible performance anyway. Regards, Jeremy John Kraal wrote: I put it here: http://pro.datisstom.nl/tests/bench.tar.bz2 The encryption isn't really a *real* security measure, except for when somebody is stupid enough to install phpMyAdmin or anything equivalent and try to get personal data. The problem is the password needs to be anywhere on the application-server and if you're in, you're in. But it's a request and I'm happy to oblige. Even if it only stops them for 1 minute (which could be enough). Regards, John -- / Humanique / Webstrategie en ontwikkeling / http://www.humanique.com/ - Humanique zoekt een ervaren Web-ontwikkelaar (PHP). Bekijk de vacature op http://www.humanique.com/ - Jeremy Cole wrote: Hi John, Your attachment for the php code got stripped somewhere. Can you post it somewhere (http preferable)? In either case it's going to result in a full table scan, so they are actually both a bad strategy long term, but they should in theory perform as you would expect, with with encryption being slightly slower. Have you tried with longer strings? What is your customer's fear with having the data in plain text? Presumably in order to use this in your application, you will have the AES password stored in your application, and it will end up in logs (such as the slow query log) quite frequently. I would think your data can be safer and your security more effective by setting some policies which are less intrusive into the actual workings of the data, such as encrypting backups and setting system-level policies. Regards, Jeremy John Kraal wrote: Dear you, I've been working on encrypting some data for a customer. They want their personal/sensitive information encrypted in the database, but they want to be able to search it too, through the application. So we've been thinking a bit, and just started trying and benchmarking some solutions we thought up. The next one really got my attention, I created a table with 4 fields: 1. id (primary/auto_increment, not really interesting) 2. field, with encrypted data 3. md5sum (it has no special use, we benched it though.) 4. line, always containing three words (the same three as encrypted) When we started querying the table for random words (from lipsum.com), it seems that searching in the encrypted fields was _lots_ faster. Results below: 1.000 queries per field: ~$ php -q searchtest.php Control test (plain/text LIKE %..%):1.409699s Decrypt test (AES_DECRYPT() LIKE %..%): 1.226069s done 1.000.000 queries per field: ~$ php -q searchtest.php Control test (plain/text LIKE %..%):155.059671s Decrypt test (AES_DECRYPT() LIKE %..%): 137.003216s done Actually, the only thing I could think of to say was: Well, at least it's consistent. I've attached all the files I used for this test. Edit db.inc.php (add some more lipsum if you want), execute fill.php, and then have fun with bench.php. Does any of you know why this is, how come, etc? I'm just very curious. Regards, John Kraal -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: K.I.S.S.? Re: Left outer joins, where clause and table_names
tom wang schrieb: Hi, I have the following sql request: SELECT projects.`id` AS t0_r0, projects.`name` AS [..endless sql..] Hi Tom, did you understand that query (in lets say 3 months) if you need to fix a bug? If not it maybe better to simplify that. regards -ralf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table type for high number of insert/delete operations
I have an application which will be inserting and then deleting many thousands of rows per hour within a single table. It essentially queues and then handles requests from a series of processes, deleting the requests after they've been dealt with. Our MySQL 5.0.45 server is set up to use InnoDB tables by default, in a single tablespace. Would MyISAM tables be a better fit for this type of application? The database server is used for other applications so the impact of this application on the others is a concern we have. Also, in terms of speed or server load, would it be better to mark records deleted and then periodically (say once an hour) run a delete query, or would this approach not make a difference? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat alternative
Gerard wrote: Currently I am running a concat statement to combine a field with a user name and domain to create and email address. In testing it looks like running the concat is a very slow command to run. The select statement currently looks like this. select concat(user,'@',domain),servername,port from database where concat(user,'@',domain)='[EMAIL PROTECTED]'; Why do CONCAT() twice? Couldn't you just do: WHERE user = 'username' AND domain = 'domain.com' Or am i missing something? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : Re: Left outer joins, where clause and table_names
Hi,
First, sorry, I kind of messed of with copy and
pasting (it's been a long day) and forgot to strip all
the useless part (for the sake of explaining my
problem) between select and from...
SELECT * FROM projects LEFT OUTER JOIN forums ON
forums.work_id = projects.id AND forums.work_type =
'Project' LEFT OUTER JOIN posts ON posts.forum_id =
forums.id LEFT OUTER JOIN topics ON topics.id =
posts.topic_id LEFT OUTER JOIN readerships ON
readerships.topic_id = topics.id LEFT OUTER JOIN
titles ON titles.project_id = projects.id LEFT OUTER
JOIN forums forums_titles ON forums_titles.work_id =
titles.id AND forums_titles.work_type = 'Title' LEFT
OUTER JOIN posts posts_forums ON posts_forums.forum_id
= forums_titles.id LEFT OUTER JOIN topics topics_posts
ON topics_posts.id = posts_forums.topic_id LEFT OUTER
JOIN readerships readerships_topics ON
readerships_topics.topic_id = topics_posts.id LEFT
OUTER JOIN forums forums_projects ON
forums_projects.work_id = projects.id AND
forums_projects.work_type = 'Project' LEFT OUTER JOIN
titles titles_projects ON titles_projects.project_id =
projects.id WHERE ((readerships.read != '1' OR
readerships_topics.read != '1'))
I guess I should have given more background
I can't just the left outer join part as it's
autogenerated but I can change the WHERE clause
Autogenerated by what, exactly? Does it run at all
the way it is? That
is quite a monster SELECT statement. In fact, if it
doesn't run now,
perhaps all it requires is a good jolt of
electricity.
I'm using ActiveRecord with ruby on rails, which
generated this query with from:
Project.find(:all,{:conditions=[(readerships.read !=
'1' OR readerships_topics.read != '1')], :order=nil,
:include={:forum={:posts={:topic=:readerships}}},
{:titles={:forum={:posts={:topic=:readerships}
I'm not good a sql syntax, but I would love to
learn
Are you reasonably certain that your SELECT requires
all that? Could
your application maybe be adjusted so that you could
break that up a little?
The problem I have is that I have a projects that is
linked to a forum table (itself linked with posts and
topics) and a titles table that is also linked to a
forum table (etc...)
I need to know which titles (don't ask me for the
name, that's the what happen when a manager designs
the database schema based on what the customer wants
:-( ) and which projects have posts that have been
read by the user.
Thanks
Thomas
_
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE : Re: Left outer joins, where clause and table_names
tom wang wrote:
Hi,
First, sorry, I kind of messed of with copy and
pasting (it's been a long day) and forgot to strip all
the useless part (for the sake of explaining my
problem) between select and from...
SELECT * FROM projects LEFT OUTER JOIN forums ON
forums.work_id = projects.id AND forums.work_type =
'Project' LEFT OUTER JOIN posts ON posts.forum_id =
forums.id LEFT OUTER JOIN topics ON topics.id =
posts.topic_id LEFT OUTER JOIN readerships ON
readerships.topic_id = topics.id LEFT OUTER JOIN
titles ON titles.project_id = projects.id LEFT OUTER
JOIN forums forums_titles ON forums_titles.work_id =
titles.id AND forums_titles.work_type = 'Title' LEFT
OUTER JOIN posts posts_forums ON posts_forums.forum_id
= forums_titles.id LEFT OUTER JOIN topics topics_posts
ON topics_posts.id = posts_forums.topic_id LEFT OUTER
JOIN readerships readerships_topics ON
readerships_topics.topic_id = topics_posts.id LEFT
OUTER JOIN forums forums_projects ON
forums_projects.work_id = projects.id AND
forums_projects.work_type = 'Project' LEFT OUTER JOIN
titles titles_projects ON titles_projects.project_id =
projects.id WHERE ((readerships.read != '1' OR
readerships_topics.read != '1'))
That's still, um ... a bit difficult to follow. First rule of SQL: line
breaks are allowed. Especially when posting queries in an email.
I'm using ActiveRecord with ruby on rails, which
generated this query with from:
Project.find(:all,{:conditions=[(readerships.read !=
'1' OR readerships_topics.read != '1')], :order=nil,
:include={:forum={:posts={:topic=:readerships}}},
{:titles={:forum={:posts={:topic=:readerships}
I've been waiting for a sign to show me that giving RoR a pass was the
correct thing to do. Now, i think i know ;-)
The problem I have is that I have a projects that is
linked to a forum table (itself linked with posts and
topics) and a titles table that is also linked to a
forum table (etc...)
I need to know which titles (don't ask me for the
name, that's the what happen when a manager designs
the database schema based on what the customer wants
:-( ) and which projects have posts that have been
read by the user.
The first thing that (i think) i see is that you're not selecting
anything at all from titles:
SELECT * FROM projects
I'm not sure i understand your schema enough to help. Are you sure you
need a separate titles table, for instance? And you shouldn't be able to
use the alias readerships_topics in the WHERE clause. Come to think of
it, your WHERE clause makes no sense at all because you're selecting
from projects. If you want to test readerships.read it should go in the
ON clause of that particular join:
LEFT OUTER JOIN readerships
ON readerships.topic_id = topics.id
AND readerships.read != '1'
Though i'm unsure whether or not the test on topics.id would work here,
either.
brian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : Re: RE : Re: Left outer joins, where clause and table_names
--- [EMAIL PROTECTED] a écrit :
tom wang wrote:
Hi,
First, sorry, I kind of messed of with copy and
pasting (it's been a long day) and forgot to strip
all
the useless part (for the sake of explaining my
problem) between select and from...
SELECT * FROM projects LEFT OUTER JOIN forums ON
forums.work_id = projects.id AND forums.work_type
=
'Project' LEFT OUTER JOIN posts ON posts.forum_id
=
forums.id LEFT OUTER JOIN topics ON topics.id =
posts.topic_id LEFT OUTER JOIN readerships ON
readerships.topic_id = topics.id LEFT OUTER JOIN
titles ON titles.project_id = projects.id LEFT
OUTER
JOIN forums forums_titles ON forums_titles.work_id
=
titles.id AND forums_titles.work_type = 'Title'
LEFT
OUTER JOIN posts posts_forums ON
posts_forums.forum_id
= forums_titles.id LEFT OUTER JOIN topics
topics_posts
ON topics_posts.id = posts_forums.topic_id LEFT
OUTER
JOIN readerships readerships_topics ON
readerships_topics.topic_id = topics_posts.id LEFT
OUTER JOIN forums forums_projects ON
forums_projects.work_id = projects.id AND
forums_projects.work_type = 'Project' LEFT OUTER
JOIN
titles titles_projects ON
titles_projects.project_id =
projects.id WHERE ((readerships.read != '1' OR
readerships_topics.read != '1'))
That's still, um ... a bit difficult to follow.
First rule of SQL: line
breaks are allowed. Especially when posting queries
in an email.
Sorry, I should have thought of that
I'm using ActiveRecord with ruby on rails, which
generated this query with from:
Project.find(:all,{:conditions=[(readerships.read
!=
'1' OR readerships_topics.read != '1')],
:order=nil,
:include={:forum={:posts={:topic=:readerships}}},
{:titles={:forum={:posts={:topic=:readerships}
I've been waiting for a sign to show me that giving
RoR a pass was the
correct thing to do. Now, i think i know ;-)
I guess someone better than me with mysql and rails
would have done something much cleaner though But
rails make simple things simpler and things out of the
ordinary harder.
The problem I have is that I have a projects that
is
linked to a forum table (itself linked with posts
and
topics) and a titles table that is also linked to
a
forum table (etc...)
I need to know which titles (don't ask me for the
name, that's the what happen when a manager
designs
the database schema based on what the customer
wants
:-( ) and which projects have posts that have
been
read by the user.
The first thing that (i think) i see is that you're
not selecting
anything at all from titles:
SELECT * FROM projects
I'm not sure i understand your schema enough to
help. Are you sure you
need a separate titles table, for instance?
Well the name is rather misleading... the database
schema was created by my japanese coworker and is not
at all used for titles but used to store different
parts of the project (when I asked why it was
named title, the answer was that it's client
requirement ;-) )
it seems that the titles table is pulled out through
this in the LEFT OUTER JOIN
titles titles_projects ON titles_projects.project_id
= projects.id
but I guess it would be more correct to put it inside
the from clause...
And you
shouldn't be able to
use the alias readerships_topics in the WHERE
clause. Come to think of
it, your WHERE clause makes no sense at all because
you're selecting
from projects. If you want to test readerships.read
it should go in the
ON clause of that particular join:
LEFT OUTER JOIN readerships
ON readerships.topic_id = topics.id
AND readerships.read != '1'
Though i'm unsure whether or not the test on
topics.id would work here,
either.
I guess I need to find a way to ask rails to change my
left outer join
I also have another question more or less related
How could I check that I either have
readerships.read = '0' AND readerships.user_id = '5'
or
no record with readerships.user_id = '5'
?
Thanks for your help
Thomas
_
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create Your Own Ftuure
http://ii-x.nm.ru - 5000$ mnothly! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Boolean searches on InnoDB tables?
At 02:54 AM 3/4/2006, Daevid Vincent wrote:
I just discovered this:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
Which states:
They can work even without a FULLTEXT index, although a search executed in
this fashion would be quite slow.
But then I'm kicked in the nuts because:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Full-text indexes can be used only with MyISAM tables
When I try a query on an InnoDB table:
SELECT * FROM categories WHERE MATCH (name) AGAINST ('+ELECTRONICS' IN
BOOLEAN MODE);
I get:
Error Code : 1214
The used table type doesn't support FULLTEXT indexes
So, what is the deal? Am I missing something?
And if I can't use boolean searches on InnoDB tables with mySQL 5.0.18,
Then WHEN will I be able to?
In the mean time, what is the best way to generate this equivallent
functionality via PHP or some other mySQL 5 sanctioned way? I've seen
several different examples on the web, but don't know which to commit to.
Daevid,
Get yourself an ice-pack and visit http://www.sphinxsearch.com/.
They have a free full text search add-on for MySQL that works with InnoDb
and MyISAM tables. You'll feel better in the morning. :)
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
