Re: maximum size of mysql server-id field
It's 32 bit. I'm not sure if it's signed or not but chances are you don't need more than 2 billion ids :) Richard Lynch wrote: On Wed, May 25, 2005 11:59 pm, David said: Does anyone know what the range for server-id can be? Is it a 16 bit number? 32 bit? I can't seem to find it in the documentation or via google. I did dogpile.com for my.cnf format server-id and found: http://mysqld.active-venture.com/Replication_Options.html which claims it's a 32-bit non-negative. range from 1 to 2^32 - 1 I dunno if they are authoritative or not, mind you... 32-bit that would have been my first guess, fwiw... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB to MyISAM
Nevertheless, foreign key constraints belong in the database, not in your application... If you have foreign keys (your wording), you need foreign key constraints. Period. Plain and simple. No discussion :-) How about a log database? We log every phone call to a calls table which currently has 27 million records. We log every event that happens on every phone call to an events table which currently has 240 million records, which are linked to their calls via a foreign key. Inserts into the calls and especially the events table happen up to 120 times per second. If these inserts back up, our telephony service stalls and people hang up and cancel service. Every time we insert an event into the events table, we do not want the database engine to see that it has a foreign key and select on the calls table for that foreign key. It simply makes no sense. First, events wouldn't be posted if we hadn't already done a successful insert a half of a second ago to calls and gotten the insert id. Second, even if we did, maintaining quality of service is far more important than the possibility that events could be inserted with an invalid foreign key. In matters of logging, that's typically not a critical issue, and in this application, it is certainly not the priority. On the other hand, in a billing log, it may be essential. There are different solutions to different applications. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query problem
Hi, Yes the server dies during the queries from JDBC. Regarding the client parameters, what if the client doesn't have mySQL installed? Amir Hello. Does SHOW STATUS executed from JDBC client return increased values of this parameters? Could your server die during queries from JDBC (check the error log)? Sometimes you should increase interactive_timeout as well. A lot of variables usually could be changed using SET statement. See: http://dev.mysql.com/doc/mysql/en/system-variables.html Sometimes when querying mySQL 4.1.11 on Linux machine I get the error Lost connection to MySQL server during query. Here are the symptoms 1.When running the query from the server it returns OK 2.When running the query from another machine using the mySQL query browser it returns OK 3.When running the query from another machine from Java using mySQL JDBC it fails with Lost connection to... 4.When running the query from another machine using software like EMS it fails with Lost connection to... 5.The error is always after 4.7 seconds and only in heavy queries (not only SELECT, even DELETES ) The problem is probably not in the communication parameters, like max_allowed_packet or connection_timeout on the server because I increased then Does anyone know how can I control those parameters from JDBC? Amir Shay [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com
ERROR 1030 (HY000): Got error 127 from storage engine
Hi, I am running MySQL 4.1.8-Max-log on two Linux machine (machine A and machine B). In the past, I do a simple scp to transfer all the database table file from one machine (A) to the other (B). After the transferring, the table on B works well. However, I am encountering a problem with transferring a big table (with 11 million record). The table in A works but seems to be corrupted after transferrring to B. When querying on the table at B, I got Got error 127 from storage engine. I tried myisamchk -r tablename but I still got the same msg. Any idea? Thanks in advance! Qunfeng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie desperately seeking clues
Folks, I am EW to the list, and MYsql. I am totally adrift, and looking for some good reading for a raw beginner. PLEASE post some linke for my use. I remain, Kirk Bailey think - | THE BOX | - kniht Defend free speech on the internet. http://www.eff.org/ ! THE WORST THING ABOUT CENSORSHIP IS THA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Just a test
Mikhail Berman
Re: problem when running mysql server
qin lei wrote: I have instal mysql in /usr/local/mysql. When I run the server. The message showed on the screen is as follows: [EMAIL PROTECTED] bin]# ./safe_mysqld [1] 5122 [EMAIL PROTECTED] bin]# Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 050526 18:05:14 mysqld ended I think the server is closed automatically. I check the mysqld.log. It says: 050526 17:46:09 mysqld started Warning: Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line 050526 17:46:09 InnoDB: Started 050526 17:46:09 /usr/local/mysql/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050526 17:46:09 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050526 17:46:09 Aborting 050526 17:46:09 InnoDB: Starting shutdown... 050526 17:46:11 InnoDB: Shutdown completed 050526 17:46:11 /usr/local/mysql/bin/mysqld: Shutdown Complete 050526 17:46:11 mysqld ended What is the problem? Error 13 is a permissions problem. The user 'mysql' does not have permission to read the files. Make sure mysql owns the database directory and all its contents. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANN]: DBACentral for MySQL v1.6 has been released
Hello, The new version of DBACentral for MySQL has been released. DBACentral v1.6 presents powerful report building functionality, query-based forms, reports and web pages, lookup fields showing multiple column values, and other improvements Download the new version of DBACentral for MySQL right now at: http://microolap.com/products/database/mydbacentral/download/?r1=mailr2=central16 Full list of current changes: [+] Comprehensive report engine is implemented. Now you are able to construct powerful data reports from tables or queries with the support of data bands, scripts in four available syntaxes, dialog pages, images, etc. You can create reports in several simple ways: with an auto-report tool, useful wizard or a rich featured designer. Generated reports can be viewed within DBACentral, exported into BMP, JPEG, PDF, RTF, HTML, Excel and other popular formats, or printed. [+] The long-awaited ability to show all columns of a lookup field list right in the data grid or in the form lookup combo-box is added. [+] Now you can use DBACentral queries or custom SQL for building forms and PHP web pages. The Auto Form, Auto Page, and Auto Report tools are also available for profile queries. [+] The size and position of each DBACentral window now is being remembered and restored on next open. [+] The ability to edit data in the autoincrement fields is added. Check the Allow Edit Autoincrement Fields option at the Tables/Queries page of the Options dialog. [*] Table Designer: now field length automatically changes for new fields to match the selected data type. [*] Data import from MS Access is slightly improved. [*] Some improvements to the bookmark system are implemented. [*] Several changes to the web page generation engine are made. [-] Fixed errors occurred with latest versions of MySQL server. [-] The ENUM values, consisting of several words, are processed correctly now. [-] A bug with importing relationships from other profile is fixed. [-] The bug relating to converting VARCHAR field to FULLTEXT is fixed. [-] The error, occasionally occurred on switching between the data grid segments is fixed. [-] Problems with launching PHP Page Designer on WindowsXP with some security hotfixes are eliminated. [-] A nasty bug occured after applying a filter to the form is fixed [-] The incorrect forcing of Save As dialog is fixed. [-] An error occurred while exporting data to Excel is fixed. --- Best regards, MicroOLAP Technologies LTD www.microolap.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Test - please ignore
Just a test - thanks There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No Longer Receiving Emails
Hi I have not received anything from this list for a couple of days. I just tried to re-register, and that did not help. If anyone reads this, please reply to ME so I can determine if the problem is with my account or with the list. Thanks Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL preg_split functionality?
I'm trying to replicate this PHP behavior in a MySQL stored procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and the string is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the string
char by char and build a new one, it's just less elegant. Thanks in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to optimize this query
Well, it seems you've helped solve my problem, Shawn...here we go: first off, sorry about the text wrapping and the sloppy sql...I do get lazy sometimes...I am a programmer :P you asked how fast this runs: SELECT ind_first_name , ind_last_name , fam_phone , max(iat_date) max_date FROM tms_individual_account_transactions , tms_families , tms_individuals WHERE fam_key = ind_fam_key and ind_key = iat_ind_key and iat_date = '2000-01-01' GROUP BY ind_key ORDER BY ind_last_name, ind_first_name; when I remove the group by and order by...to do that, I also have to remove the max function, like so: SELECT ind_first_name , ind_last_name , fam_phone FROM tms_individual_account_transactions , tms_families , tms_individuals WHERE fam_key = ind_fam_key and ind_key = iat_ind_key and iat_date = '2000-01-01'; nice and quick result, about 3 seconds... now, this query: SELECT ind_first_name , ind_last_name , fam_phone , max(iat_date) max_date FROM tms_individual_account_transactions , tms_families , tms_individuals WHERE fam_key = ind_fam_key and ind_key = iat_ind_key and iat_date = '2000-01-01' GROUP BY 2,1,3; takes over 120 seconds...some improvement, but not much... now, in reading your suggestion to create the temp table, I realized that my previous explain plan that I posted wasn't using the index on the iat_date field; rather, it was using the index on the iat_ind_key field; so, I wondered what would happen if I forced using the index_iat_date index...like so: select ind_first_name, ind_last_name, fam_phone, max(iat_date) max_date from tms_individual_account_transactions force index (index_iat_date), tms_families, tms_individuals where fam_key = ind_fam_key and ind_key = iat_ind_key and iat_date = '2005-01-01' group by ind_key order by ind_last_name, ind_first_name and now my query that was taking 120+ seconds, was now taking around 5 seconds... and an explain plan to prove it: +++---++-++++ | table | type | possible_keys | key| key_len | ref| rows | Extra | +++---++-++++ | tms_individual_account_transactions| range | index_iat_date| index_iat_date| 3 | [NULL] | 49695 | Using where; Using temporary; Using filesort| | tms_individuals| eq_ref | PRIMARY,index_ind_fam_key| PRIMARY| 4 | tms_individual_account_transactions.iat_ind_key| 1 || | tms_families| eq_ref | PRIMARY | PRIMARY| 4 | tms_individuals.ind_fam_key| 1 || +++---++-++++ now, I'm not a DBA or a MySQL guru, so I won't try to comprehend why it works so much better...I'm more of a tinker until it works kind of a guy...maybe this whole scenario of mine might be handy for MySQL optimizer developers to take note of? Anyway, I appreciate your comments/suggestions, Shawn...it got me to have a second look at what I was doing and try some different things...exactly what I needed! Thanks! Kevin [EMAIL PROTECTED] wrote: Kevin Weslowski [EMAIL PROTECTED] wrote on 05/26/2005 02:20:46 AM: Hi, I've got a query that I believe should be giving me better performance than it is, since I've put indexes on all the appropriate columns...I'll include as much info as I can for anyone wishing to help me optimize the query/tables/indicies...thanks in advance for any assistance... the query: select ind_first_name, ind_last_name, fam_phone, max(iat_date) max_date from tms_individual_account_transactions, tms_families, tms_individuals where fam_key = ind_fam_key and ind_key = iat_ind_key and iat_date = '2000-01-01' group by ind_key order by ind_last_name, ind_first_name; it takes ~150 seconds on a PIII 667 MHz, with 640MB RAM...and returns ~700 rows... MySQL server version: 4.0.18 tms_families: ~1000 records tms_individuals: ~1700 records tms_individual_account_transactions: ~10 records the explain plan: +++---++-+ +++ | table | type | possible_keys | key| key_len | ref| rows | Extra | +++---++-+ +++ | tms_families| ALL| PRIMARY | [NULL] | [NULL] | [NULL] | 993 | Using temporary; Using filesort| | tms_individuals| ref| PRIMARY,index_ind_fam_key| index_ind_fam_key| 4 | tms_families.fam_key| 1 || | tms_individual_account_transactions| ref| index_iat_ind_key,index_iat_date| index_iat_ind_key| 4 | tms_individuals.ind_key| 79 |
Re: ERROR 1030 (HY000): Got error 127 from storage engine
Did you shut down mysqld or read lock before copying the table? Qunfeng wrote: Hi, I am running MySQL 4.1.8-Max-log on two Linux machine (machine A and machine B). In the past, I do a simple scp to transfer all the database table file from one machine (A) to the other (B). After the transferring, the table on B works well. However, I am encountering a problem with transferring a big table (with 11 million record). The table in A works but seems to be corrupted after transferrring to B. When querying on the table at B, I got Got error 127 from storage engine. I tried myisamchk -r tablename but I still got the same msg. Any idea? Thanks in advance! Qunfeng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment trouble (not the usual check || alter table solution, though)
On 5/16/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, with a similar structure, you can have : mysql select * from users where uid =262140; ++--+ | uid| nickname | ++--+ | 262140 | text | | 262141 | text | | 262142 | text | | 262143 | text | | 262144 | text | ++--+ 5 rows in set (0.00 sec) it's surprising that you can insert NULL in a primary key auto_increment. second, with only 1 values, if the auto_increment reached 2147483647 this means that you have an intensive delete, or the auto_increment had been altered. you can create table toto like users, and insert data again into toto. This will reincremente between 1 and 10xxx. Then rename toto to users. Mathias, Unfortunately, this didn't work out. I created, by hand, a table with the exact same definition although with a different name (newusers). Then, using a dump from the users table, I restored the data into the new table. Right after restoring the data, a show table status like 'newusers' shows that the auto_increment value is 2147483647. This could lead to three paths: the problem would be somewhere in the data, in the restore procedure or in the mysql engine itself. I've analyzed the data by hand (all the ~10900 lines), and I am sure that there isn't a single uid above 10900. Does this make any sense? Thanks in advance for any leads, Ricardo Oliveira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0 - 4.1 undocumented incompatible change w/float?
I've already opened a support ticket with MySQL on this issue, but
thought someone here might know the answer, too:
Hi there,
I'm trying to make sure I'm not doing something stupid. I'm trying to
migrate from 4.0 to 4.1, but I'm hitting a snag. I can't find anything
in the docs to describe what I'm seeing.
MySQL's docs seem to suggest that a column with 'float(5,2)' specifies
as display width of 5 and a decimal precision of 2. I assumed that the
'display width' worked like the other numeric types and would allow
storing larger values.
On 3.32 and 4.0, this was the case. I was able to store large numbers in
this float:
mysql explain testFloat;
+--+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+-+--+-+-+---+
| Floating | float(5,2) unsigned | | | 0.00 | |
+--+-+--+-+-+---+
1 row in set (0.02 sec)
mysql insert into testFloat values ('134567.12344321');
Query OK, 1 row affected (0.00 sec)
mysql select * from testFloat;
+---+
| Floating |
+---+
| 134567.12 |
+---+
1 row in set (0.00 sec)
After upgrading to 4.1, however, this functionality seems partially broken:
mysql explain testFloat;
+--+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+-+--+-+-+---+
| Floating | float(5,2) unsigned | | | 0.00 | |
+--+-+--+-+-+---+
1 row in set (0.00 sec)
mysql insert into testFloat values ('134567.12344321');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select * from testFloat;
+--+
| Floating |
+--+
| 999.99 |
+--+
1 row in set (0.00 sec)
I say partially broken, because my existing 4.0 data is fine. In fact, I
can change an InnoDB table to MyISAM and then back to InnoDB using
'alter table' and it retains the proper values.
FYI, this is on both 4.1.11 RPM distro from MySQL and 4.1.10a-1 from Red
Hat Enterprise 4. Occurs on both x86 and x86_64, RH9 and RHEL4
respectively.
Any ideas? Is this really an incompatible change that just hasn't been
documented? Am I missing something obvious? I have a lot of tables I
expected to behave the same way as they did in prior releases...
Don
smime.p7s
Description: S/MIME Cryptographic Signature
4.0 - 4.1 undocumented incompatible change w/float?
I've already opened a support ticket with MySQL on this issue, but
thought someone here might know the answer, too:
Hi there,
I'm trying to make sure I'm not doing something stupid. I'm trying to
migrate from 4.0 to 4.1, but I'm hitting a snag. I can't find anything
in the docs to describe what I'm seeing.
MySQL's docs seem to suggest that a column with 'float(5,2)' specifies
as display width of 5 and a decimal precision of 2. I assumed that the
'display width' worked like the other numeric types and would allow
storing larger values.
On 3.32 and 4.0, this was the case. I was able to store large numbers in
this float:
mysql explain testFloat;
+--+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+-+--+-+-+---+
| Floating | float(5,2) unsigned | | | 0.00 | |
+--+-+--+-+-+---+
1 row in set (0.02 sec)
mysql insert into testFloat values ('134567.12344321');
Query OK, 1 row affected (0.00 sec)
mysql select * from testFloat;
+---+
| Floating |
+---+
| 134567.12 |
+---+
1 row in set (0.00 sec)
After upgrading to 4.1, however, this functionality seems partially broken:
mysql explain testFloat;
+--+-+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+--+-+--+-+-+---+
| Floating | float(5,2) unsigned | | | 0.00 | |
+--+-+--+-+-+---+
1 row in set (0.00 sec)
mysql insert into testFloat values ('134567234798129348571234.12344321');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select * from testFloat;
+--+
| Floating |
+--+
| 999.99 |
+--+
1 row in set (0.00 sec)
I say partially broken, because my existing 4.0 data is fine. In fact, I
can change an InnoDB table to MyISAM and then back to InnoDB using
'alter table' and it retains the proper values.
FYI, this is on both 4.1.11 RPM distro from MySQL and 4.1.10a-1 from Red
Hat Enterprise 4. Occurs on both x86 and x86_64, RH9 and RHEL4
respectively.
Any ideas? Is this really an incompatible change that just hasn't been
documented? Am I missing something obvious? I have a lot of tables I
expected to behave the same way as they did in prior releases...
Don
smime.p7s
Description: S/MIME Cryptographic Signature
Select MAX(column1,column2)
Can I select the maximum value across multiple columns? ie, I'd like to select the highest value of buyCost AND sellCost in a table...where buy and sell are two different columns in the same table. i actually have 4 comparisons to run, and don't want to have to execute 4 queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: won't start on fedora core 3--kernel errors?
I want it to be on the list for feature requests. The final solution which helped was adding mysqld_disable_trans=1 to /etc/selinux/targeted/booleans and reboot. Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Disable or change policies of SELinux. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restarting mysql server
Hello. It looks like the path is wrong. Find out the correct value from /etc/init.d/mysql file and put it in the configuration file. Hi, I check my.cnf configure file. I find Pid-file path is /var/run/mysqld/mysqld.pid, but I couldn't find mysqld directory in /var/run and couldn't find mysqld.pid either. So, maybe default path in configure file is wrong? Ying Sun [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how can I install mysql without administrator priviledge
Hello. --default-file=/home/jesse/mysql/my-large.cnf to run the server but it ^ There's 's' at the end, use --defaults-file. I have tried to use ./mysqld_safe --default-file=/home/jesse/mysql/my-large.cnf to run the server but it doesn't work. The output is like this: [EMAIL PROTECTED] bin]$ ./mysqld_safe --default-file=/home/jesse/mysql/my-large.cnf my_print_defaults: unrecognized option `--loose-verbose' qin lei [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
combining rows
Hello list, I've got a query problem with MySQL 4._0_. A specific SELECT will give me a result set with two columns, an ID and an information. Now I want to group all results by their ID. Speak: In the result there should be only one row per ID but the informations from the different rows shall be summarized within a single field. I hope that was understandable enough... Searching the manual I found GROUP_CONCAT which seems to do what I want. But this is only available for MySQL 4._1_. And for now I can't update. Is there any other possibility to get that to work? I want to keep all the logic within the SQL statement since it is just one within a huge selection of queries. A performance penalty shouldn't be a problem, since this whole query will only be called once a day at 3 or 4 AM to generate a file. Thank you Marcel PS: a simple example I will try to show it with a university database example. I have a table with all lectures, a table with all students and a table which creates a relation hearing between the two. I need a table where I have exactly one row for each lecture and all students within a column. SELECT hearing.lecture_id, students.name FROM hearing LEFT JOIN students ON hearing.student_id = students.id; That gives me the needed lecture/student combination (ok, I even need the lecture name instead of the id, but that's just a JOIN more). Hoehere Mathematik| Guenter Beckstein Hoehere Mathematik| Edmund Stoiber Hoehere Mathematik| Gerhard Schroeder Diskrete Strukturen | Britney Spears Diskrete Strukturen | Anastasia Diskrete Strukturen | Backstreet Boys But I still have a row for each combination. When I change the query to include a GROUP BY like this SELECT hearing.lecture_id, students.name FROM hearing LEFT JOIN students ON hearing.student_id = students.id GROUP BY hearing.lecture_id; I will get only one row per lecture (good) but also only with a single student in the name-field - the first one (bad). Hoehere Mathematik| Guenter Beckstein Diskrete Strukturen | Britney Spears instead of Hoehere Mathematik| Guenter Beckstein, Edmund Stoiber, Gerhard Schroeder Diskrete Strukturen | Britney Spears, Anastasia, Backstreet Boys which is what I need -- Marcel Meyer | Netzwerk- und Rechnerorganisation | Fachschaft Mathematik/Physik/Informatik | Technische Universität München -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1030 (HY000): Got error 127 from storage engine
No, I didn't shut down mysqld or read lock. Would you please elaborate it a little more on why? Thanks a lot! Qunfeng At 01:05 PM 5/27/2005, Eric Bergen wrote: Did you shut down mysqld or read lock before copying the table? Qunfeng wrote: Hi, I am running MySQL 4.1.8-Max-log on two Linux machine (machine A and machine B). In the past, I do a simple scp to transfer all the database table file from one machine (A) to the other (B). After the transferring, the table on B works well. However, I am encountering a problem with transferring a big table (with 11 million record). The table in A works but seems to be corrupted after transferrring to B. When querying on the table at B, I got Got error 127 from storage engine. I tried myisamchk -r tablename but I still got the same msg. Any idea? Thanks in advance! Qunfeng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: No Longer Receiving Emails
I saw this on this list, so it is getting there just fine. -Original Message- From: Tucker, Gabriel [mailto:[EMAIL PROTECTED] Sent: Friday, May 27, 2005 10:03 AM To: Mysql General (E-mail) Subject: No Longer Receiving Emails Hi I have not received anything from this list for a couple of days. I just tried to re-register, and that did not help. If anyone reads this, please reply to ME so I can determine if the problem is with my account or with the list. Thanks Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Preg_split functionality
Here's what I came up with in case anyone else needs a quick fix. A regular expression replace would've been nicer, but, you do what you gotta do... CREATE PROCEDURE `test`(`par` varchar(60)) BEGIN DECLARE nChars INT DEFAULT CHAR_LENGTH(par); DECLARE nCounter INT DEFAULT 1; DECLARE sPattern VARCHAR(300) DEFAULT ''; DECLARE sRegEx VARCHAR(60) DEFAULT '[^a-zA-Z0-9]*'; -- pad the regex pattern on each side of every character in the search string WHILE nCounter = nChars DO SET sPattern = CONCAT(sPattern, sRegEx, SUBSTR(par, nCounter, 1)); SET nCounter = nCounter + 1; END WHILE; -- add the pattern to the end of the string too SET sPattern = CONCAT(sPattern, sRegEx); END| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Longer Receiving Emails
Works Rob Tucker, Gabriel wrote: Hi I have not received anything from this list for a couple of days. I just tried to re-register, and that did not help. If anyone reads this, please reply to ME so I can determine if the problem is with my account or with the list. Thanks Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using MySQL to store/archive code/html etc?
Hi, Today I designed a fairly cute error404.php page, which I'd like to reuse in future. Currently I rely on remembering that I created such a page for say ACME Carpets. Now and again my memory fails to remember which site I developed a piece of HTML,PHP,Perl,Javascript for and I spend more time going thru CD backups etc than it would take to start again. I've postulated using MySql as a store for such pages. but is a database suitable for storing, searching for and retrieving such information.??? Your eminent opinions and experiences desired!! -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to optimize this query
Kevin Weslowski [EMAIL PROTECTED] wrote on 05/26/2005 02:20:46 AM: Hi, I've got a query that I believe should be giving me better performance than it is, since I've put indexes on all the appropriate columns...I'll include as much info as I can for anyone wishing to help me optimize the query/tables/indicies...thanks in advance for any assistance... the query: select ind_first_name, ind_last_name, fam_phone, max(iat_date) max_date from tms_individual_account_transactions, tms_families, tms_individuals where fam_key = ind_fam_key and ind_key = iat_ind_key and iat_date = '2000-01-01' group by ind_key order by ind_last_name, ind_first_name; it takes ~150 seconds on a PIII 667 MHz, with 640MB RAM...and returns ~700 rows... MySQL server version: 4.0.18 tms_families: ~1000 records tms_individuals: ~1700 records tms_individual_account_transactions: ~10 records the explain plan: +++---++-+ +++ | table | type | possible_keys | key| key_len | ref| rows | Extra | +++---++-+ +++ | tms_families| ALL| PRIMARY | [NULL] | [NULL] | [NULL] | 993 | Using temporary; Using filesort| | tms_individuals| ref| PRIMARY,index_ind_fam_key| index_ind_fam_key| 4 | tms_families.fam_key| 1 || | tms_individual_account_transactions| ref| index_iat_ind_key,index_iat_date| index_iat_ind_key| 4 | tms_individuals.ind_key| 79 | Using where| +++---++-+ +++ now, I know what's killing the query is the max() and group by, but I still think I should get better performance?? now, the table definitions (sorry for the overkill, but there's referential integrity I needed to maintain with extraneous tables): CREATE TABLE `tms_marriage_statuses` ( `mst_key` int(11) unsigned NOT NULL default '0', `mst_description` varchar(15) NOT NULL default '', PRIMARY KEY (`mst_key`), KEY `unique_mst_description` (`mst_description`) ) TYPE=InnoDB; CREATE TABLE `tms_families` ( `fam_key` int(11) unsigned NOT NULL default '0', `fam_name` varchar(50) NOT NULL default '', `fam_sortname` varchar(25) NOT NULL default '', `fam_address` varchar(100) default '', `fam_city` varchar(50) default '', `fam_prov_state` char(2) default '', `fam_postal_zip_code` varchar(10) default '', `fam_phone` varchar(14) default '', `fam_update_user` varchar(15) default '', `fam_update_date` timestamp(14) NOT NULL, `fam_cmts_family_no` int(11) unsigned default '0', `fam_admin_comments` varchar(255) default NULL, PRIMARY KEY (`fam_key`), UNIQUE KEY `unique_fam_name` (`fam_name`), KEY `index_fam_cmts_family_no` (`fam_cmts_family_no`) ) TYPE=InnoDB; CREATE TABLE `tms_individuals` ( `ind_key` int(11) unsigned NOT NULL default '0', `ind_fam_key` int(11) unsigned NOT NULL default '0', `ind_last_name` varchar(30) NOT NULL default '', `ind_first_name` varchar(20) NOT NULL default '', `ind_middle_name` varchar(20) default NULL, `ind_name_title` varchar(6) default NULL, `ind_name_suffix` char(3) default NULL, `ind_marriage_status` int(11) unsigned NOT NULL default '0', `ind_sex` char(1) NOT NULL default '', `ind_join_date` date default NULL, `ind_birth_date` date default NULL, `ind_envelope_number` varchar(10) default NULL, `ind_update_user` varchar(15) default NULL, `ind_update_date` timestamp(14) NOT NULL, `ind_admin_comments` varchar(255) default NULL, `ind_cmts_member_no` int(11) unsigned default NULL, `ind_deceased` char(1) NOT NULL default 'N', `ind_member` char(1) NOT NULL default 'Y', PRIMARY KEY (`ind_key`), KEY `index_ind_fam_key` (`ind_fam_key`), KEY `index_ind_marriage_status` (`ind_marriage_status`), KEY `index_ind_cmts_member_no` (`ind_cmts_member_no`), CONSTRAINT `tms_individuals_ibfk_1` FOREIGN KEY (`ind_fam_key`) REFERENCES `tms_families` (`fam_key`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tms_individuals_ibfk_2` FOREIGN KEY (`ind_marriage_status`) REFERENCES `tms_marriage_statuses` (`mst_key`) ) TYPE=InnoDB; CREATE TABLE `tms_accounts` ( `acc_key` int(11) unsigned NOT NULL default '0', `acc_number` int(11) unsigned NOT NULL default '0', `acc_description` varchar(50) NOT NULL default '', `acc_update_user` varchar(15) default '', `acc_update_date` timestamp(14) NOT NULL, PRIMARY KEY (`acc_key`), UNIQUE KEY `unique_acc_number` (`acc_number`) ) TYPE=InnoDB; CREATE TABLE `tms_individual_account_transactions` ( `iat_key` int(11) unsigned NOT NULL default '0', `iat_ind_key` int(11) unsigned NOT
Re: MySQL preg_split functionality?
I have a hard time figuring out when you would use such a function. I do
not believe you will be able to duplicate this behavior without
constructing your own UDF or by writing a stored procedure. BTW, why *do*
you want this function?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 01:30:35
PM:
I'm trying to replicate this PHP behavior in a MySQL stored procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and the string is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the string
char by char and build a new one, it's just less elegant. Thanks in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select MAX(column1,column2)
Scott Klarenbach wrote: Can I select the maximum value across multiple columns? You want the GREATEST() function: http://dev.mysql.com/doc/mysql/en/comparison-operators.html -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: No Longer Receiving Emails
Thanks - now I am getting emails from the list.. Gabe -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Friday, May 27, 2005 2:39 PM To: Tucker, Gabriel Subject: Re: No Longer Receiving Emails You are sending messages without error, it would appear. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Migration Tool - who wrote it?
This isn't exactly the right spot, but I can't find any info on the MySQL web site. Anyone know who maintains the MySQL Migration Tool (or who is developing it, as it is currently Apha)? It does not support Orace 8i (9i and 10g only), but I've looked through the source code, and it's not a huge change to add the 8i functionality. I'm willing to do it if I can find out who to submit the changes to David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cumulative Totals
Just in case you did not follow this suggestion, if you are using 4.0.x this is very simple. I was looking for this: set @total:=0; select f1,f2,...,@total:[EMAIL PROTECTED] as Total from table where ...; is pretty simple. On Wed, 25 May 2005, Dan Bolser wrote: On Wed, 25 May 2005, Russell Horn wrote: I have a pretty simple table with a list of payments, not much more than: paymentID | amount | paymentDate 1 | 123| 2005-01-10 2 | 77 | 2005-01-13 3 | 45 | 2005-02-16 4 | 13 | 2005-02-17 I can get totals per month using a query like: SELECT SUM(amount) , DATE_FORMAT( `paymentDate` , '%Y-%m' ) FROM `payments` GROUP BY DATE_FORMAT( payments . date , '%Y-%m' ) That would give me: amount | paymentDate 200| 2005-01 58 | 2005-02 Is there any way to get a running cumulative total directly from mysql? Something like: amount | paymentDate 200| 2005-01 258| 2005-02 http://dev.mysql.com/doc/mysql/en/variables.html :D Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select MAX(column1,column2)
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/26/2005 04:25:22 PM: Can I select the maximum value across multiple columns? ie, I'd like to select the highest value of buyCost AND sellCost in a table...where buy and sell are two different columns in the same table. i actually have 4 comparisons to run, and don't want to have to execute 4 queries. Short answer: YES Long answer: Let's imagine you are running a message board and you wanted to know some statistics about how busy your board is. Continue to imagine that you store each message as a row in a table called messages: CREATE TABLE messages ( ID int unsigned auto_increment primary key , from_user_id int unsigned , to_user_id int unsigned , msgdate datetime , msgsize int unsigned , msg TEXT ); To get some absolute information about messages (all messages as one group): SELECT min(msg_date) as firstmsg , max(msg_date) as lastmsg , min(msg_size) as smallestmsg , max(msg_size) as longestmsg , avg(msg_size) as avgmsg , sum(msg_size) as totalmsg , count(msg_size) as msgcount FROM messages GROUP BY from_user_id; To get the same information but break it down by the user sending the message: SELECT from_user_id , min(msg_date) as firstmsg , max(msg_date) as lastmsg , min(msg_size) as smallestmsg , max(msg_size) as longestmsg , avg(msg_size) as avgmsg , sum(msg_size) as totalmsg , count(msg_size) as msgcount FROM messages GROUP BY from_user_id; Were these the types of queries you were interested in? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: newbie desperately seeking clues
If you're a newbie to MySQL, you'll certainly benefit from reading the documentation at http://dev.mysql.com/doc/. If you're new to database design, there's a pretty good normalization tutorial at http://www.informit.com/articles/article.asp?p=30885rl=1 Rhino - Original Message - From: Kirk Bailey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, May 26, 2005 12:28 PM Subject: newbie desperately seeking clues Folks, I am EW to the list, and MYsql. I am totally adrift, and looking for some good reading for a raw beginner. PLEASE post some linke for my use. I remain, Kirk Bailey think - | THE BOX | - kniht Defend free speech on the internet. http://www.eff.org/ ! THE WORST THING ABOUT CENSORSHIP IS THA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.322 / Virus Database: 267.0.0 - Release Date: 27/05/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.322 / Virus Database: 267.0.0 - Release Date: 27/05/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL to store/archive code/html etc?
news [EMAIL PROTECTED] wrote on 05/27/2005 02:46:09 PM: Hi, Today I designed a fairly cute error404.php page, which I'd like to reuse in future. Currently I rely on remembering that I created such a page for say ACME Carpets. Now and again my memory fails to remember which site I developed a piece of HTML,PHP,Perl,Javascript for and I spend more time going thru CD backups etc than it would take to start again. I've postulated using MySql as a store for such pages. but is a database suitable for storing, searching for and retrieving such information.??? Your eminent opinions and experiences desired!! -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips It is possible to do just what you propose. You could even FT index your code so that you could find certain key phrases (assuming that the keyphrase you want is at least 4 (or 3) characters long and does not appear in over 50% of the documents. HOWEVER, it is more proper to store files in a file system and just store the files path information in a database (along with other information like who it was for, who wrote it, etc.) Once you start doing that, though, you might as well take that last itty-bitty step and move into a full version-control system. There are several out there, many of them are free. Not only do they keep your code well organized (and protected, usually) but it allows you to do all sorts of interesting things (like reverting to previous versions). That way if you modified the same file 5 times for 5 different clients, you wouldn't have to have 5 copies floating around. Your VCS software would allow you to regress your current version to any prior version. In my humble opinion, that's the appropriate class of tool for the task you mention. Some sample names of VCS software: BitKeeper, Visual Source Safe. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: No Longer Receiving Emails
It's possible that Gabe's mail spool is full, and he is not receiving mail - including our responses. Gabriel - if you are receiving any messages directly sent to you - please respond to the list to rule that out. If no responses are made to any of either directly or through the list, then he isn't receiving mail at all for some reason, if not a full spool. Jay Blanchard wrote: I saw this on this list, so it is getting there just fine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL preg_split functionality?
I'm working on a set of UDFs for preg functions.
[EMAIL PROTECTED] wrote:
I have a hard time figuring out when you would use such a function. I do
not believe you will be able to duplicate this behavior without
constructing your own UDF or by writing a stored procedure. BTW, why *do*
you want this function?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 01:30:35
PM:
I'm trying to replicate this PHP behavior in a MySQL stored procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and the string is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the string
char by char and build a new one, it's just less elegant. Thanks in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
--
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: ERROR 1030 (HY000): Got error 127 from storage engine
If you are copying a file that mysql is writing to sections of the file are going to be from different points in time so when that all gets put backtogether in one file on the other end it's going to be corrupt. Qunfeng wrote: No, I didn't shut down mysqld or read lock. Would you please elaborate it a little more on why? Thanks a lot! Qunfeng At 01:05 PM 5/27/2005, Eric Bergen wrote: Did you shut down mysqld or read lock before copying the table? Qunfeng wrote: Hi, I am running MySQL 4.1.8-Max-log on two Linux machine (machine A and machine B). In the past, I do a simple scp to transfer all the database table file from one machine (A) to the other (B). After the transferring, the table on B works well. However, I am encountering a problem with transferring a big table (with 11 million record). The table in A works but seems to be corrupted after transferrring to B. When querying on the table at B, I got Got error 127 from storage engine. I tried myisamchk -r tablename but I still got the same msg. Any idea? Thanks in advance! Qunfeng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL to store/archive code/html etc?
At 02:46 PM 5/27/05, zzapper wrote: Today I designed a fairly cute error404.php page, which I'd like to reuse in future. Currently I rely on remembering that I created such a page for say ACME Carpets. Now and again my memory fails to remember which site I developed a piece of HTML,PHP,Perl,Javascript for and I spend more time going thru CD backups etc than it would take to start again. I've postulated using MySql as a store for such pages. but is a database suitable for storing, searching for and retrieving such information.??? No. Store all the files on a hard disk and use 'grep' to search for what your looking for - it'll be much easier to maintain. If you can't remember to keep all you code in one place, what are the chances you are going to remember to insert it into a database? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Longer Receiving Emails
I stopped receiving email most of yesterday as well - it's still catching up today. I think there was a hiccup in the list. David Cummings, Shawn (GNAPs) wrote: It's possible that Gabe's mail spool is full, and he is not receiving mail - including our responses. Gabriel - if you are receiving any messages directly sent to you - please respond to the list to rule that out. If no responses are made to any of either directly or through the list, then he isn't receiving mail at all for some reason, if not a full spool. Jay Blanchard wrote: I saw this on this list, so it is getting there just fine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Longer Receiving Emails
lists.mysql.com was down temporarily due to a faulty disk. Mails to and from the lists should be working now. When you notice a disruption like this, it is best if you simply check http://lists.mysql.com/ to verify if there are mails in the archive that you have not yet received, and just be patient -- when you send these test emails, they get queued up and delivered to the thousands of list subscribers when the lists come back online. *Do not send test emails to the list.* If you believe you are experiencing trouble receiving mails from the list, please contact the list administrators at [EMAIL PROTECTED] Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1030 (HY000): Got error 127 from storage engine
But while I copied all the table files from A (testing machine) to machine B (real server), no data was writing into A's tables.Are you saying that I have to shut down the mysqld on machine A or activate WRITE lock to A before copying over to machine B? Qunfeng At 02:52 PM 5/27/2005, Eric Bergen wrote: If you are copying a file that mysql is writing to sections of the file are going to be from different points in time so when that all gets put backtogether in one file on the other end it's going to be corrupt. Qunfeng wrote: No, I didn't shut down mysqld or read lock. Would you please elaborate it a little more on why? Thanks a lot! Qunfeng At 01:05 PM 5/27/2005, Eric Bergen wrote: Did you shut down mysqld or read lock before copying the table? Qunfeng wrote: Hi, I am running MySQL 4.1.8-Max-log on two Linux machine (machine A and machine B). In the past, I do a simple scp to transfer all the database table file from one machine (A) to the other (B). After the transferring, the table on B works well. However, I am encountering a problem with transferring a big table (with 11 million record). The table in A works but seems to be corrupted after transferrring to B. When querying on the table at B, I got Got error 127 from storage engine. I tried myisamchk -r tablename but I still got the same msg. Any idea? Thanks in advance! Qunfeng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select MAX(column1,column2)
[snip] Can I select the maximum value across multiple columns? ie, I'd like to select the highest value of buyCost AND sellCost in a table...where buy and sell are two different columns in the same table. i actually have 4 comparisons to run, and don't want to have to execute 4 queries. [/snip] Sounds like an easy test, doesn't it? You will have to use the following syntax select max(foo), max(bar) from table keep in mind that foo and bar may not be in the same original tuple -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner workings of a JOIN
At http://dev.mysql.com/doc/mysql/en/explain.html the paragraph starting with EXPLAIN returns a row of information for each table used in the SELECT statement explains the process briefly. In general MySQL reads/finds rows while also satisfying other conditions. In your example below, if there's an index on A.lastname, MySQL may use it to find all rows WHERE A.lastname = 'doe'. For each A.id of these rows, the corresponding B row is read, probably by using an index on B.id. So if there were 3 'doe's, MySQL would read A, B, A, B, A, B (presuming a 1-to-1 relationship between A.id and B.id). If there were no index on A.lastname, MySQL would probably table scan A. When it hit a row WHERE lastname = 'doe', it would use that row's id to read the corresponding B row. Therefore MySQL has to read 1M A rows because a 'doe' row could be at the very end, verses reading 3 A rows with an index on A.lastname. (Alternatively, MySQL might table scan B and for every B.id read the corresponding A.id row keeping only those where A.lastname = 'doe' also.) If A.id and B.id were a 1-to-N relationship, it changes things again since for every 'doe' A.id row, there could be N many B rows. The read sequence could then be like A, B, B, B, A, B, B, etc. In either case MySQL does not create a cross-product of the tables. It may read a cross-product's worth of rows if there are no indexes anywhere so it has to scan all tables. But in general MySQL reads only what it has to, filters by the given conditions, and builds the final result set as it goes. -Daniel On Wed, 2005-05-25 at 21:01 -0700, James Tu wrote: What does MySQL do internally when you perform a LEFT JOIN? Let's say you have two tables: Table A has 1,000,000 rows Table B has 5,000 rows When you perform the following LEFT JOIN: Select A.*, B.* FROM A, B WHERE A.lastname = 'doe' AND A.id http://A.id = B.id http://B.id What does MySQL do internally? Does it first create some sort of CROSS JOIN with the two tables (resulting in a 5,000,000,000 row table) and then finding the matching rows based on the WHERE clause? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL preg_split functionality?
Ya, it is a little too specific...here's why I need it.
I have a client that wants to search for part numbers in his DB. The
problem is, they come into his DB from external sources, with all
sorts of special characters in them...
So, he has fields like
field_one!,
fi--eld 2,
@fi#eld__3xxx
etc
but, he wants to do a search for 'fieldone' and return the first one,
'field2' returns the second, etc...basically disregard all non-alphas
padding every character in the search string.
On 5/27/05, Eric Bergen [EMAIL PROTECTED] wrote:
I'm working on a set of UDFs for preg functions.
[EMAIL PROTECTED] wrote:
I have a hard time figuring out when you would use such a function. I do
not believe you will be able to duplicate this behavior without
constructing your own UDF or by writing a stored procedure. BTW, why *do*
you want this function?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 01:30:35
PM:
I'm trying to replicate this PHP behavior in a MySQL stored procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and the string is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the string
char by char and build a new one, it's just less elegant. Thanks in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
--
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: Select MAX(column1,column2)
Thanks Keith. It didn't quite work as expected, but it helps me a lot none the less. The Documentation says it returns the max value, so select greatest(1, 2, 3, 4) will return 4. But, across multiple column names, it returns all the values in one column, not just the greatest one...so select greatest(fieldone, fieldtwo, fieldthree, fieldfour) from table returns 10 12 14 29 6 3 444 etc...from all 4 of those fields. So, by going Select MAX(GREATEST(fieldone, fieldtwo, fieldthree)) I can accomplish exactly what I've been banging my head against a wall for...thanks. On 5/27/05, Keith Ivey [EMAIL PROTECTED] wrote: Scott Klarenbach wrote: Can I select the maximum value across multiple columns? You want the GREATEST() function: http://dev.mysql.com/doc/mysql/en/comparison-operators.html -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL preg_split functionality?
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 04:38:40
PM:
Ya, it is a little too specific...here's why I need it.
I have a client that wants to search for part numbers in his DB. The
problem is, they come into his DB from external sources, with all
sorts of special characters in them...
So, he has fields like
field_one!,
fi--eld 2,
@fi#eld__3xxx
etc
but, he wants to do a search for 'fieldone' and return the first one,
'field2' returns the second, etc...basically disregard all non-alphas
padding every character in the search string.
On 5/27/05, Eric Bergen [EMAIL PROTECTED] wrote:
I'm working on a set of UDFs for preg functions.
[EMAIL PROTECTED] wrote:
I have a hard time figuring out when you would use such a function. I
do
not believe you will be able to duplicate this behavior without
constructing your own UDF or by writing a stored procedure. BTW, why
*do*
you want this function?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005
01:30:35
PM:
I'm trying to replicate this PHP behavior in a MySQL stored
procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and the string
is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the string
char by char and build a new one, it's just less elegant. Thanks in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
Thank you very much. I find this whole padding process very
counterintuitive. I have a few minor questions, if you don't mind. How is
'xFxIxExLxDx1x' easier to search than 'FIELD1'? Would you, could you
please explain the theory behind why and when this kind of padding should
be done? What problem does it solve and how is it a solution to that
problem? This is completely baffling to me and I thought I had seen a lot
of weird data before :-)
However, It seems to me that this kind of data manipulation (cleanup)
needs to happen BEFORE the data enters the databse. What data import
tool/process is your client using? Can you not change the import process
to scrub the data and does it not have a better facility to interleave
padding into a string than a MySQL stored procedure or UDF?
Thanks for you patience!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
how to print warnings from mysqlimport...debug_options ??
I can't seem to get ahold of the 6 warnings being generated when I import a csv file. My ~/.my.cnf file says this: [client] fields-terminated-by=, fields-enclosed-by=\ #debug=log.txt #debug=d,info,error,query,general,where:1,load.trace debug=warning,load.trace Nothing I do in the 'debug' param seems to actually output any information. Here's my command: mysqlimport -v -h [host] [database] [table_and_file_name].csv -u [username] -p Here's my version info: mysqlimport Ver 3.4 Distrib 4.0.18, for pc-linux (i686) Here's my output to STDOUT/STDERR: Connecting to [hostname] Selecting database [database] Loading data from LOCAL file: [table_and_file_name.csv into [table_name] [database].[table_name]: Records: 3 Deleted: 0 Skipped: 0 Warnings: 6 Disconnecting from [hostname] I want to see those 6 warnings. I've tried command-line '--debug=foo' as well. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new Resources site http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query performance...two table design options
James Tu wrote: Hi: Let's say I want to store the following information. Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) In general 'age' is a bad column, because you need to know what year the data was entered to calculate the current age. It is often better to store year of birth or date of birth. This may not be relevant to your application, I just wanted to mention it. Date - DATETIME Activity - VARCHAR(100) Data - TEXT I would be basing my queries on all columns _except_ the Data column. I.e. I would be using WHERE's with all except the Data column. You are not telling us how much data you are planning to maintain. How big will the Data column be, on average, and how many rows/persons are we talking about? Hundreds, thousands or millions? My question is...which design would perform better? (Design A) Put all in one table...index all the columns that I will use WHERE with. -TABLE_ALL- Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) Date - DATETIME Activity - VARCHAR(100) Data - TEXT Indices - Unique ID, First Name, Last Name, Age, Date, Activity You will probably not need to index all columns. If you have few rows, you don't need indexes at all, except for the primary key on the unique ID. A primary key automatically works as an index. I would probably start with only the primary key, and add indexes only when I find that some queries are too slow. SELECT First_Name, Last_Name, Data FROM TABLE_ALL WHERE Activity = 'draw' AND Age 24; (Design B) Put the Data in its own separate table. -TABLE_A- Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) Date - DATETIME Activity - VARCHAR(100) Data_ID - INT(10) Indices - Unique ID, First Name, Last Name, Age, Date, Activity -TABLE_B- Data_ID - INT(10) Data - TEXT Index - Data_ID This will be faster if your Data column is relatively big (several K on average, I don't know. depends on your HW, of course). I would suggest using the unique ID from TABLE_A as a primary key in TABLE_B, and drop Data_ID from TABLE_A. If there are millions of rows I would normalize these tables to the extreme, something like this: Person: P_Id,Born FName: FN_Id,FirstName LName: LN_Id,LastName FN_P: FN_Id,P_id LN_P: LN_Id,P_id Activity: A_Id,Activity Act_P: A_id,P_Id Data:P_Id,Data FN_P and LN_P are so-called link tables, linking names to persons in a many-to-many relation. Even further normalization would have been achieved with an additional counter column. It would be used in these tables to maintain the order of the names when a person have multiple first names or last names, so that you would have one FName row for each unique name, Mary Jane would be split in Mary and Jane. You could query this schema like this: SELECT FirstName,LastName,Data FROM Person,FName,LName,Data,Activity,FN_P,LN_P,Act_P WHERE Person.P_Id = Data.P_Id AND Person.P_Id = FN_P.P_Id AND Person.P_Id = LN_P.P_Id AND Person.P_Id = Act_P.P_Id AND FName.FN_Id = FN_P.FN_Id AND LName.LN_Id = LN_P.LN_Id AND Activity.A_Id = Act_P.A_Id AND Activity = 'draw' and Born year(now()) - 24 ...or with more explicit formulated joins, like this: SELECT FirstName,LastName,Data FROM Person NATURAL JOIN Act_P NATURAL JOIN Activity INNER JOIN FN_P ON FN_P.P_Id=Person.P_Id NATURAL JOIN FName INNER JOIN LN_P ON LN_P.P_Id=Person.P_Id NATURAL JOIN LName, LEFT JOIN Data ON Data.P_Id = Person.P_Id WHERE Activity = 'draw' and Born year(now()) - 24 The NATURAL JOINS are joins based on columns with the same name in the two joined tables, see the manual. The LEFT JOIN is used in this case because some Persons may not have a corresponding row in the Data table, in this case the Data column of the result table will contain NULL. If you used an INNER join in place of the LEFT join in this case, Persons without a Data record would be omitted from the result. SELECT TABLE_A.First_Name, TABLE_A.Last_Name, TABLE_B.Data FROM TABLE_A, TABLE_B WHERE Activity = 'draw' AND Age 24 AND TABLE_A.Data_ID = TABLE_B.Data_ID; (Aside: Would this query give me the same results as the above query?) Yes, I think so, if all rows in TABLE_A have a corresponding row in TABLE_B. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner workings of a JOIN
James Tu wrote: What does MySQL do internally when you perform a LEFT JOIN? You can read about it here: URL: http://dev.mysql.com/doc/mysql/en/left-join-optimization.html If you need more details, you could read the source... Let's say you have two tables: Table A has 1,000,000 rows Table B has 5,000 rows When you perform the following LEFT JOIN: Select A.*, B.* FROM A, B WHERE A.lastname = 'doe' AND A.id http://A.id = B.id http://B.id eh... that's not a LEFT JOIN...? What does MySQL do internally? Does it first create some sort of CROSS JOIN with the two tables (resulting in a 5,000,000,000 row table) and then finding the matching rows based on the WHERE clause? If there is no index on A.lastname and B.id, probably yes... Use the EXPLAIN SELECT command to see how MySQL plan to solve the query. URL: http://dev.mysql.com/doc/mysql/en/explain.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select MAX(column1,column2)
Hi all, what is max ? it's the first row when we sort data in descending order. so select col1,col2,col3,col4 ... from table order by concat(col1,col2,col3,col4 ... ) desc LIMIt 1; should be silar to what is needed. I say should :o) Mathias Selon Scott Klarenbach [EMAIL PROTECTED]: Thanks Keith. It didn't quite work as expected, but it helps me a lot none the less. The Documentation says it returns the max value, so select greatest(1, 2, 3, 4) will return 4. But, across multiple column names, it returns all the values in one column, not just the greatest one...so select greatest(fieldone, fieldtwo, fieldthree, fieldfour) from table returns 10 12 14 29 6 3 444 etc...from all 4 of those fields. So, by going Select MAX(GREATEST(fieldone, fieldtwo, fieldthree)) I can accomplish exactly what I've been banging my head against a wall for...thanks. On 5/27/05, Keith Ivey [EMAIL PROTECTED] wrote: Scott Klarenbach wrote: Can I select the maximum value across multiple columns? You want the GREATEST() function: http://dev.mysql.com/doc/mysql/en/comparison-operators.html -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- 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: Select MAX(column1,column2)
[EMAIL PROTECTED] wrote: Hi all, what is max ? it's the first row when we sort data in descending order. so select col1,col2,col3,col4 ... from table order by concat(col1,col2,col3,col4 ... ) desc LIMIt 1; should be silar to what is needed. I say should :o) That would only work if the greatest values for col2, col3, col4, etc., all occurred in the same row with the greatest value for col1, and if all the values for col1 had the same number of digits (and the same for col2, col3, etc.). Consider this table: 10 2 3 5 4 8 1 12 7 Your query would give 5, 4, 8 (because 548 as a string is greater than 1023 or 1127), but he wants 10, 12, 8. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Migration Tool - who wrote it?
Hi, On May 26, 2005, at 5:03 PM, David Griffiths wrote: This isn't exactly the right spot, but I can't find any info on the MySQL web site. Anyone know who maintains the MySQL Migration Tool (or who is developing it, as it is currently Apha)? MySQL AB is actively developing it (hence why it is on our website). The GUI team is incharge of the development of this particular project. It does not support Orace 8i (9i and 10g only), but I've looked through the source code, and it's not a huge change to add the 8i functionality. I'm willing to do it if I can find out who to submit the changes to Supposedly the next release (might be the one that happened today, not sure) is supposed to now support Oracle 8.1.7 according to: http://forums.mysql.com/read.php?104,24637,27601#msg-27601 For future contact I would recommend the [EMAIL PROTECTED] mailing list (or even the web forum at http://forums.mysql.com/list.php?104 which is very active). Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select MAX(column1,column2)
Hi Keith, yes concat makes an associative lost for max. But if we split the desc on all the columns, it works : mysql select * from numbers - order by a desc,b desc,c desc - limit 1; +--+--+--+ | a| b| c| +--+--+--+ | 10 |2 |3 | +--+--+--+ 1 row in set (0.00 sec) it's a real desc ordering. Thanks Mathias Selon Keith Ivey [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: Hi all, what is max ? it's the first row when we sort data in descending order. so select col1,col2,col3,col4 ... from table order by concat(col1,col2,col3,col4 ... ) desc LIMIt 1; should be silar to what is needed. I say should :o) That would only work if the greatest values for col2, col3, col4, etc., all occurred in the same row with the greatest value for col1, and if all the values for col1 had the same number of digits (and the same for col2, col3, etc.). Consider this table: 10 2 3 5 4 8 1 12 7 Your query would give 5, 4, 8 (because 548 as a string is greater than 1023 or 1127), but he wants 10, 12, 8. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select MAX(column1,column2)
I forgot : 10, 12, 8 is not a row !!! Mathias Selon [EMAIL PROTECTED]: Hi Keith, yes concat makes an associative lost for max. But if we split the desc on all the columns, it works : mysql select * from numbers - order by a desc,b desc,c desc - limit 1; +--+--+--+ | a| b| c| +--+--+--+ | 10 |2 |3 | +--+--+--+ 1 row in set (0.00 sec) it's a real desc ordering. Thanks Mathias Selon Keith Ivey [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: Hi all, what is max ? it's the first row when we sort data in descending order. so select col1,col2,col3,col4 ... from table order by concat(col1,col2,col3,col4 ... ) desc LIMIt 1; should be silar to what is needed. I say should :o) That would only work if the greatest values for col2, col3, col4, etc., all occurred in the same row with the greatest value for col1, and if all the values for col1 had the same number of digits (and the same for col2, col3, etc.). Consider this table: 10 2 3 5 4 8 1 12 7 Your query would give 5, 4, 8 (because 548 as a string is greater than 1023 or 1127), but he wants 10, 12, 8. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select MAX(column1,column2)
select greatest(max(col1), max(col2), max(col3), max(col4)) from table works the best, as Keith pointed toward initially. Remember, I forgot to mention that I wanted the greatest for the whole table, not just for each rowso, 10, 12, 8 is not what I wanted...out of 10 2 3 5 4 8 1 12 7 i want 12. thanks again. On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I forgot : 10, 12, 8 is not a row !!! Mathias Selon [EMAIL PROTECTED]: Hi Keith, yes concat makes an associative lost for max. But if we split the desc on all the columns, it works : mysql select * from numbers - order by a desc,b desc,c desc - limit 1; +--+--+--+ | a| b| c| +--+--+--+ | 10 |2 |3 | +--+--+--+ 1 row in set (0.00 sec) it's a real desc ordering. Thanks Mathias Selon Keith Ivey [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: Hi all, what is max ? it's the first row when we sort data in descending order. so select col1,col2,col3,col4 ... from table order by concat(col1,col2,col3,col4 ... ) desc LIMIt 1; should be silar to what is needed. I say should :o) That would only work if the greatest values for col2, col3, col4, etc., all occurred in the same row with the greatest value for col1, and if all the values for col1 had the same number of digits (and the same for col2, col3, etc.). Consider this table: 10 2 3 5 4 8 1 12 7 Your query would give 5, 4, 8 (because 548 as a string is greater than 1023 or 1127), but he wants 10, 12, 8. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL preg_split functionality?
-- How is 'xFxIxExLxDx1x' easier to search than 'FIELD1'?
It's not easier. It is; however, accurate for the purpose at hand.
FIELD1 isn't completely inclusive...it would miss FIELD-1...
-- However, It seems to me that this kind of data manipulation
(cleanup) needs to happen BEFORE the data enters the databse.
Sometimes FIELD-1 is the ACTUAL data, with no erroneus
characters...and sometimes it is FIELD1 with an erroneous (or
unwanted) - character before the 1)...so cleaning the data would
actually be corrupting some of it.
I need to keep the data in it's original form, but also allow for
querying without worrying about the special characters inside the
column.
FYI, these are part numbers off of electronic components, many of them
coming from China...so, a Cisco part may have an MPN of RX321, or
RX321-TR...either is valid. Now, the corresponding Chinese part
number for the first one, may come back as RX32-1...which is out of my
control.
Cleaning the data would be the wrong approach, because it would
actually invalidate the second Cisco part number, which MEANT to
include the special characters.
For this reason, the user wants to be able to search for 'RX321' and
'RX321TR' respectively, and not worry about whether the data is
erroneous or valid; just to basically ignore all the characters and
let a human decide what they want.
If you have a more elegant solution, I'm all ears :-).
On 5/27/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005 04:38:40
PM:
Ya, it is a little too specific...here's why I need it.
I have a client that wants to search for part numbers in his DB. The
problem is, they come into his DB from external sources, with all
sorts of special characters in them...
So, he has fields like
field_one!,
fi--eld 2,
@fi#eld__3xxx
etc
but, he wants to do a search for 'fieldone' and return the first one,
'field2' returns the second, etc...basically disregard all non-alphas
padding every character in the search string.
On 5/27/05, Eric Bergen [EMAIL PROTECTED] wrote:
I'm working on a set of UDFs for preg functions.
[EMAIL PROTECTED] wrote:
I have a hard time figuring out when you would use such a function. I
do
not believe you will be able to duplicate this behavior without
constructing your own UDF or by writing a stored procedure. BTW, why
*do*
you want this function?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Klarenbach [EMAIL PROTECTED] wrote on 05/27/2005
01:30:35
PM:
I'm trying to replicate this PHP behavior in a MySQL stored procedure.
The purpose is to pad every character of the string with a pad
character. For example, if the pad character is 'x' and the string is
'STRING', the result is 'xSxTxRxIxNxGx'.
Here is the PHP code if it helps. I'd like to use a regular
expression to replace, but I guess I could loop through the string
char by char and build a new one, it's just less elegant. Thanks in
advance.
PHP:
-
$regPattern = implode('x', preg_split('//', STRING, -1,
PREG_SPLIT_NO_EMPTY));
Thank you very much. I find this whole padding process very
counterintuitive. I have a few minor questions, if you don't mind. How is
'xFxIxExLxDx1x' easier to search than 'FIELD1'? Would you, could you please
explain the theory behind why and when this kind of padding should be done?
What problem does it solve and how is it a solution to that problem? This
is completely baffling to me and I thought I had seen a lot of weird data
before :-)
However, It seems to me that this kind of data manipulation (cleanup) needs
to happen BEFORE the data enters the databse. What data import tool/process
is your client using? Can you not change the import process to scrub the
data and does it not have a better facility to interleave padding into a
string than a MySQL stored procedure or UDF?
Thanks for you patience!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Longer Receiving Emails
On Fri, May 27, 2005 at 08:48:38PM +0200, Rob Cochrane wrote: Works Rob Tucker, Gabriel wrote: Hi I have not received anything from this list for a couple of days. I just tried to re-register, and that did not help. If anyone reads this, please reply to ME so I can determine if the problem is with my account or with the list. Thanks Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Check your spam restrictions with your ISP. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
performance on single column index with few distinct values
Hi list, I have run into problems on a master table for our helpdesk. We have the following table: ticket_id (int) - autoincrement (indexed) master_id (int) (indexed) Master ID is used to distinguish multiple helpdesks. In this table there are 100k records, but only 10 distinct master_id's. For example: ticket_id master_id 1 1 2 1 3 2 4 2 5 3 ... ... When trying to do pagination I use the following SQL: SELECT ticket_id FROM my_table WHERE master_id = '1' ORDER BY ticket_id DESC LIMIT 0,10 The problem is that there are 20k records where master_id = 1, so the lookup is pretty slow especially when I start joining other tables. When joining other tables the query gets slower and slower, I guess because the lookups on joining tables result in fewer rows being joined when using EXPLAIN. SELECT * FROM helpdesk_tickets ht, helpdesk_category_master hcm, helpdesk_sub_category_master hscm WHERE ht.master_id = '1' AND ht.category_id = hcm.category_id AND ht.sub_category_id = hscm.sub_category_id ORDER BY ticket_id DESC LIMIT 0,10 I have thought of options such as using temporary tables to just grab the last 10 tickets and then do an IN query, however I need to display totals, so that would require me to run the query again. My questions are: 1) Is there any point to having an index on a column with so few unique values? 2) Would it make more sense to have multiple master tables for each helpdesk? Such as: helpdesk_tickets_1 helpdesk_tickets_2 helpdesk_tickets_3 etc. and then using a session value to query the table? 3) Any other tips or advice? (I notice my query time doubles from 100k rows to 150k rows) Thanks for any help... Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
