Re: word count
Is there a simple wat to get a word count out of a field in mysql, or to at least split on and tell me how many items in that array. Well, you can count the number of spaces: SET @str = 'A four word sentence'; SELECT LENGTH( @str ) - LENGTH( REPLACE ( @str , ' ', '' ) ) +1; Maybe this is good enough? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Measuring query progress
On Thu, 7 Apr 2005, Gleb Paharenko wrote: Hello. If you're using an InnoDB tables you could monitor the progress with SHOW INNODB STATUS using the number of proceeded rows. See: http://dev.mysql.com/doc/mysql/en/innodb-monitor.html Thanks. I'll give it a shot. Mike Mike Muratet [EMAIL PROTECTED] wrote: Greetings I have a query that has been running for days on a otherwise unused mysql server. It's running on a reasonbly stout machine (dual Pentium III with 1GB of memory) and I tuned the server for big jobs. It's meeting all of the metrics in the documentation for a well-tuned system as near as I can tell. Is there some way I can compare the output from the 'explain' of the query with the output of 'show status' to estimate how far along the query is? There have been no other queries processed so that everything in the status variables is a result of this query. Thanks Mike -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.11 and 5.0.3 on Mac OS X 10.3
Hi!
Am Do, den 07.04.2005 schrieb Joerg Bruehe um 18:45:
[[...]]
The problem is with the HTML generation, [[...]]
It seems that in the HTML page generation this has not been changed in
one place. [[...]]
I just checked the 4.1 download page: It seems corrected now.
Aside: This problem is caused by Apple using different strings both
internally and externally: 'uname -sr' yields Darwin 7.8.0 whereas
customers call it Mac OS X 10.3.
I fear such events will happen again as long as internal ('uname -a')
and external (customer / marketing use) names are different:
We use 'uname' output to identify a binary and have (or at least: want)
to offer it under the name the customer uses.
Regards,
Jörg Brühe
--
Joerg Bruehe, Senior Production 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: CSV-to-SQL?
I didn't know the CSV table-type existed -- sounds really cool! As I understand it, it will save table data in a flat CSV file, but if I already have a CSV file, can I trick MySQL into thinking it's a table? That wasn't clear from the documentation. Richard On Apr 5, 2005, at 7:47 PM, Renato Golin wrote: You could use the CSV table type: http://dev.mysql.com/doc/mysql/en/csv-storage-engine.html Or use the LOAD DATA INFILE to import all data: http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html --rengolin --- Richard Miller [EMAIL PROTECTED] wrote: I have a dozen, very large CSV files that I would like to put into a MySQL database, with 1 table per file. Does anyone know of a PHP (or other) script that can read the first few lines of a CSV file and create an appropriate CREATE TABLE statement based on the data it finds? (Even better, it could import the file afterwards!) I'm not picky about data types here; I'd simply like to get this data into tables so I can work with it more easily. Thanks, Richard Miller -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld_multi at startup on Mac OS X not working
Greetings,
I have successfully configured mysqld_multi to have mysql 4.1.11 and
5.0.3 beta running on the same machine:
# mysqld_multi start
# exit
% mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld4 is running
MySQL server from group: mysqld5 is running
%
However, I can't get this to work at system startup time. Starting up
a single mysql server works fine, with the following
/Library/StartupItems/MySQL/MySQL script:
#!/bin/sh
. /etc/rc.common
if [ ${MYSQL:=-YES-} = -YES- ]; then
ConsoleMessage Starting MySQL database server
/usr/local/mysql/bin/mysqld_safe
fi
But if I change /usr/local/mysql/bin/mysqld_safe to
/usr/local/mysql/bin/mysqld_multi start , no servers start up. There
are also no error messages in the .err logs: the last item there is
the previous 'normal shutdown'.
Any ideas? I would think that there should be no difference between
executing mysqld_multi from a root shell and executing it at startup
time, but apparently it's not the same.
Thanks in advance,
Jan Pieter Kunst
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User connection history?
Hello. Analyzing the General Query Log could give such kind of information. See: http://dev.mysql.com/doc/mysql/en/query-log.html Dilshad Ali [EMAIL PROTECTED] wrote: Hi, Is there a way to determine when was the last time a MySQL user attempted to connect to the MySQL Database? -- 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: Problem with make command
Hello. Manual build of MySQL server is not usually a simple task. In most cases an official binaries are preferred. Use the latest release (4.1.11 now). Read the operating system specific notes: http://dev.mysql.com/doc/mysql/en/operating-system-specific-notes.html Dar$o Zapata Vivas [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: iso-8859-1, 23 lines --] After executing de command make I have the next error make: Fatal error: Don't know how to make target `ctype-big5.lo' Current working directory /2ndhd/MYSQL/one/mysql-4.0.21/libmysql_r *** Error code 1 make: Fatal error: Command failed for target `all-recursive' Current working directory /2ndhd/MYSQL/one/mysql-4.0.21 *** Error code 1 make: Fatal error: Command failed for target `all' Help me. Please. -- 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: Strange behavior
Hello. According to: http://dev.mysql.com/doc/mysql/en/show-processlist.html the temporary result set was larger than tmp_table_size and the thread is changing the temporary table from in-memory to disk-based format to save memory. I suggest you to play with the value of this variable which is now: tmp_table_size33554432 Also I think that upgrade to 4.1.11 might solve the problem. The page http://dev.mysql.com/doc/mysql/en/order-by-optimization.html says that MySQL 4.1 and up uses a new faster algorithm for optimization and 'ORDER BY' queries. And maybe some advices from there could be helpful for you. However, I don't see how it is related to replication, except that master makes a lot of requests to read binary logs to send the updates to the slave. Rafal Kedziorski [EMAIL PROTECTED] wrote: I get this: id: 52401 user: omk-write host: db: omk command: query time: 0 State: Copying to tmp table Info: select distinct m.media_id from category_tree c_tree, media_2_category m2c, media m, media_2_partner ... -- 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: Performance question
Hello. Usually the CHAR type is fastest, although it wastes more space. Hi all Suppose that I want to create a table with a column named DETAILS that will contain textual data. Performance-wise, does it matter if I represent this column with, say, a 200-char varchar or a larger type like text or mediumtext but be sure that only textual data smaller than 200 chars is going to be stored in it? Behrang Saeedzadeh [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: Out of memory, but plenty of swap space left
Hello. Does the problem remains if you're executing the query just after the 'FLUSH TABLES' statement. Jon Wagoner [EMAIL PROTECTED] wrote: Recently I've been getting error 1041 at times, which gives the message to update the ulimit or add swap space. =20 This is a dedicated MySQL server, so I have no ulimit set for MySQL. According to /proc/meminfo I have over 1GB of swap free. Mem: 2118533120 2067628032 509050880 19677184 1659768832 Swap: 1998733312 686882816 1311850496 MemTotal: 2068880 kB MemFree: 49712 kB MemShared: 0 kB Buffers: 19216 kB Cached:1320684 kB SwapCached: 300184 kB Active: 943996 kB Inactive: 1008424 kB HighTotal: 1179484 kB HighFree: 2044 kB LowTotal: 889396 kB LowFree: 47668 kB SwapTotal: 1951888 kB SwapFree: 1281104 kB The mysqld section of my.cnf contains the following: log-bin server-id =3D 106 basedir =3D /usr datadir =3D /var/lib/mysql tmpdir =3D /tmp language=3D /usr/share/mysql/english skip-locking set-variable=3D key_buffer=3D512M set-variable=3D max_allowed_packet=3D1G set-variable=3D table_cache=3D3072 set-variable=3D sort_buffer=3D2M set-variable=3D record_buffer=3D2M set-variable=3D thread_cache=3D8 set-variable=3D thread_concurrency=3D8 set-variable=3D myisam_sort_buffer_size=3D64M set-variable=3D thread_stack=3D128K set-variable=3D open_files_limit=3D8192 set-variable=3D tmp_table_size=3D50M max_tmp_tables =3D 100 innodb_data_home_dir =3D /var/lib/mysql/ innodb_data_file_path =3D ibdata1:10M:autoextend innodb_log_group_home_dir =3D /var/lib/mysql/ innodb_log_arch_dir =3D /var/lib/mysql/ set-variable =3D innodb_buffer_pool_size=3D384M set-variable =3D innodb_additional_mem_pool_size=3D20M set-variable =3D innodb_log_file_size=3D5M set-variable =3D innodb_log_buffer_size=3D8M innodb_flush_log_at_trx_commit=3D1 set-variable =3D innodb_lock_wait_timeout=3D50 long_query_time=3D30 query_cache_limit=3D1M query_cache_size=3D64M query_cache_type=3D1 max_connections=3D200 Does anyone have any suggestions as to why I'm getting out of memory errors? Do I have some of the settings wrong? If it matters, I have about 50GB worth of data, split between InnoDB and MyISAM tables. I last got the error updating records in one of the MyISAM tables which was about 1MB in size. -- 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: Cannot execute query - Can't find file: (error: 9)
Hello. See: http://dev.mysql.com/doc/mysql/en/table-cache.html Frank Bax [EMAIL PROTECTED] wrote: It seems the bad file number error was caused by MySQL trying to have more than 128 files open at one time. This was fixed by changing MySQL startup, but the fix would not have been needed if MySQL were to close open files are some period of no activity to a table - does MySQL ever close a file? If I create a table and run a select on it, then there is no access to that table for months (and MySQL is not shutdown or restarted), does MySQL still have the file open? Yes, I ran myisamchk and there are no problems with database. Frank At 02:36 PM 4/6/05, V. M. Brasseur wrote: According to perror: perror 9 Error code 9: Bad file number This is an operating system error code: http://dev.mysql.com/doc/mysql/en/operating-system-error-codes.html Out of curiosity, have you tried running myisamchk or some CHECK TABLE commands yet? http://dev.mysql.com/doc/mysql/en/table-maintenance.html Cheers, --V Frank Bax wrote: At 10:07 AM 4/5/05, Frank Bax wrote: At 04:27 PM 4/4/05, Frank Bax wrote: Cannot execute query. snip my SQL statement Can't find file: './donor/list_lst.frm' (errno: 9) - - I got the same error last week on a different table. Today I notice that there is a table in another database on same system producing the same error. I attempted to access mysql cli, but it just locked up after entering password. Start/stop mysql and mysql cli at least started but issued errors about some tables even before I entered a command. I decided to reboot and the problem goes away (for a while). MySQL 4.0.20 - OpenBSD 3.6 Forgot to mention a couple of things: 1) The file that mysql complains about does exist. # ls -ltr donor/list*.frm -rw-rw 1 _mysql _mysql 8694 Jan 15 09:43 donor/list_lst.frm 2) When problem recurs (as it did on both databases this morning), the same file in each database is affected each time error appears. 3) In both databases (on same system) it is frm files in the error message. Since OpenBSD 2.8, there is a default limit of 128 open files for daemon processes. Add --open-files-limit=2048 to mysql startup. http://dev.mysql.com/doc/mysql/en/openbsd-2-8.html The suggested changes to /etc/login.conf were not necessary on my system. Does MySQL ever close the file(s) associated with table(s), or once open do they stay open until shutdown? Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
char field length get 2 times in GB2312
Hi, After SET CHARACTER SET GB2312 , create table a1 (b1 char(6)) charset=gb2312, the char field width in 2 times. select * from a1 123456 12 Should be 123456 server : win98/linux mysql4.1.11 client : windows ,bcc55,libmysql.dll Regards Shuming Wang
Re: Strange behavior
At 22:13 07.04.2005, Gleb Paharenko wrote: Hello. According to: http://dev.mysql.com/doc/mysql/en/show-processlist.html the temporary result set was larger than tmp_table_size and the thread But we get max. 10.000 long values in out result set. is changing the temporary table from in-memory to disk-based format to save memory. I suggest you to play with the value of this variable which is now: tmp_table_size33554432 I try to change this value. Also I think that upgrade to 4.1.11 might solve the problem. The page I was thinking already about this. http://dev.mysql.com/doc/mysql/en/order-by-optimization.html says that MySQL 4.1 and up uses a new faster algorithm for optimization and 'ORDER BY' queries. And maybe some advices from there could be helpful for you. However, I don't see how it is related to replication, except that master makes a lot of requests to read binary logs to send the updates to the slave. Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HELP WITH A DATE QUERY USING 4.0.17
Hi, I have a table called Bookings which holds start times and end times for appointments, these are held in Booking_Start_Date and Booking_End_Date. I have a page on my site that runs a query to produce a grid to show availiability per day for the next ten days for each user of the system. Users work 8.5 hours a day and the query shows how many hours available the user has on that day: SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours FROM Bookings B WHERE B.User_ID = '610' AND NOT ( '2005-04-08' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2005-04-08' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) The problem here is I have to do this query to produce a result for each cell(date) and then for each user so 100 users = 1000 queries to load the page! Is there a way to produce the result so that I only need one query per user so it groups the result by day for the next ten days? Thanks for your help Here is the table definition: mysql desc Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Completion_Date | date| YES | | NULL|| | Booking_Mileage | int(5) | YES | | NULL|| | Booking_Status | varchar(15) | | | Other || | Additional_Notes| text| YES | | NULL|| +-+-+--+-+-++ 23 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CSV-to-SQL?
On Friday 08 April 2005 06:14, Richard Miller wrote: I didn't know the CSV table-type existed -- sounds really cool! As I understand it, it will save table data in a flat CSV file, but if I already have a CSV file, can I trick MySQL into thinking it's a table? That wasn't clear from the documentation. Hi Richard, never tryied but if you have a good CSV file and create the table manually, stop the server, copy your CSV to the one mysql created and start the server back, it should work fine... worth a shot! ;) --rengolin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems upgrading from 4.0 to 4.1
Hello Sergei On Thu, Apr 07, 2005 at 03:14:10PM +0200, Sergei Golubchik wrote: I've just run into deep troubles while upgrading from 4.0.24 to 4.1.10a using precomiled Debian packages on Debian Woody although I read the comments regarding upgrading on dev.mysql.com. After starting the new server and running mysqlcheck -r -v -A, I experienced the following problems on about 15% of my tables (seemed to be quite random, i.e. not only the biggest or most used ones): Christian, do you have any of that tables ? Can you upload one of them (preferably, the smallest one) to our ftp ? I've uploaded the file into your write-only ftp.mysql.com:/pub/mysql/upload/ directory as Christian_Hammers_Tablecorruption_40to41.tar.gz The included table is correct according to a CHECK TABLE with MySQL-4.0.24 but reproducible gets emptied during a REPAIR TABLE on MySQL-4.1.10a. bye, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 ch@westend.comD-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior
At 22:13 07.04.2005, Gleb Paharenko wrote: Hello. According to: http://dev.mysql.com/doc/mysql/en/show-processlist.html the temporary result set was larger than tmp_table_size and the thread is changing the temporary table from in-memory to disk-based format to save memory. I suggest you to play with the value of this variable which is now: tmp_table_size33554432 after set this value 2 times bigger, we have the old speed. thanks for help! Best Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlmanager - 5.0.3-0
Hiya. Is there any way to define the pid file for the mysqlmanager? --pid-file=/var/run/mysqld/mysqlmanager The only way i've found so far is to modify the init file that comes with the RPM. Not something I'd like to do : ) Thanks! -- Sasha Dolgy [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slightly Off Topic - MySQL Administrator
As a newbie on Linux (FC3), I have (evidently) done something stupid and
lost part of the Administrator application.
When first installed, it was fine. I then treid to change the path on the
restore page, assuming that it was to point to where backups would be
stored. Whatever was entered there (I don't honestly remember what it was)
has resulted in the following: whenever the application is up and running,
if I click on 'Restore' to go to that page, the whole app just disappears.
I've used what I believe to be the normal method of uninstalling ('rpm
-e') and the 'rpm -V' thereafter reports the pacjkage as not installed. If
I then reinstall, I still get the same problem of a disappearing
MySQLAdministrator when I click for Restore.
Eveidently there's a config file somewhere with that (obviously incorrect)
path in it, but it's not being destroyed by the 'rpm -e'.
Does anyone out there happen to know what that is? I've looked at the
archives for the MySQL-GUI and find no reference to this...
Cheers
Terry Riley
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Out of memory, but plenty of swap space left
Heikki, Yes, I'm running MySQL on 32-bit Linux. I think maybe something had just gotten corrupted. MySQL restarted itself yesterday, with the following in the error log: 050407 16:24:49 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=536870912 read_buffer_size=2093056 max_used_connections=201 max_connections=200 threads_connected=49 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1342686 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x59d064a0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... frame pointer (ebp) is NULL, did you compile with -fomit-frame-pointer? Aborting backtrace! Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x59f907f8 is invalid pointer thd-thread_id=68571 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 050407 16:24:51 InnoDB: Database was not shut down normally! Unless you tell me different, I'll just plan on upgrading to 4.1.11 (I'm still running 4.1.8) -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, April 08, 2005 12:24 AM To: mysql@lists.mysql.com Subject: Re: Out of memory, but plenty of swap space left John, are you running on a 32-bit computer? Then, normally, the process size is limited to 2 GB. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Jon Wagoner [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, April 07, 2005 7:31 PM Subject: Out of memory, but plenty of swap space left Recently I've been getting error 1041 at times, which gives the message to update the ulimit or add swap space. =20 This is a dedicated MySQL server, so I have no ulimit set for MySQL. According to /proc/meminfo I have over 1GB of swap free. Mem: 2118533120 2067628032 509050880 19677184 1659768832 Swap: 1998733312 686882816 1311850496 MemTotal: 2068880 kB MemFree: 49712 kB MemShared: 0 kB Buffers: 19216 kB Cached:1320684 kB SwapCached: 300184 kB Active: 943996 kB Inactive: 1008424 kB HighTotal: 1179484 kB HighFree: 2044 kB LowTotal: 889396 kB LowFree: 47668 kB SwapTotal: 1951888 kB SwapFree: 1281104 kB The mysqld section of my.cnf contains the following: log-bin server-id =3D 106 basedir =3D /usr datadir =3D /var/lib/mysql tmpdir =3D /tmp language=3D /usr/share/mysql/english skip-locking set-variable=3D key_buffer=3D512M set-variable=3D max_allowed_packet=3D1G set-variable=3D table_cache=3D3072 set-variable=3D sort_buffer=3D2M set-variable=3D record_buffer=3D2M set-variable=3D thread_cache=3D8 set-variable=3D thread_concurrency=3D8 set-variable=3D myisam_sort_buffer_size=3D64M set-variable=3D thread_stack=3D128K set-variable=3D open_files_limit=3D8192 set-variable=3D tmp_table_size=3D50M max_tmp_tables =3D 100 innodb_data_home_dir =3D /var/lib/mysql/ innodb_data_file_path =3D ibdata1:10M:autoextend innodb_log_group_home_dir =3D /var/lib/mysql/ innodb_log_arch_dir =3D /var/lib/mysql/ set-variable =3D innodb_buffer_pool_size=3D384M set-variable =3D innodb_additional_mem_pool_size=3D20M set-variable =3D innodb_log_file_size=3D5M set-variable =3D innodb_log_buffer_size=3D8M innodb_flush_log_at_trx_commit=3D1 set-variable =3D innodb_lock_wait_timeout=3D50 long_query_time=3D30 query_cache_limit=3D1M query_cache_size=3D64M query_cache_type=3D1 max_connections=3D200 Does anyone have any suggestions as to why I'm getting out of memory errors? Do I have some of the settings wrong? If it matters, I have about 50GB worth of data, split between InnoDB and MyISAM tables. I last got the error updating records in one of the MyISAM tables which was about 1MB in size. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To
re: calling a stored procedure from another stored procedure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a stored procedure called assignItem. I would like to call this from another stored procedure. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCVqEoikQgpVn8xrARAhSTAJ9OL4gL+xTK8e6lmuKt4Uook6V2sgCeMiBN EOVEjJ5gm8Qg4TjuWl7GNcU= =5GgG -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
occasional error 2013, no clue
hi, we are running a forum with about 600 to 700 people online at the same time plus a shop and other database relying services. because this was to much for our single mysql server we are running a master-slave setup now, both of version 4.0.23. now we get occasional 2013 errors from our forum, lost connection to mysql server during query. we didn't get these on our old setup. the old setup was slow but worked without errors. the appearance of these errors did not start just in time when the new setup was online, the errors started to occur some days ago. they occur only on insert and update statements, so they are produced by our master, and they occur absolutely by chance. everything is okay most of the time, but now and then it occurs. sometimes just one error message, sometimes 1000 of it at once, serveral times every day. our forum delivers us the error messages by email, all other services on our site don't. because everythings fine the most of the time i think our forum is fully compatible with the php and mysql version we are using and the code is okay too. now i'm asking myself if the mysql server may be configured wrong for our needs, especially regarding memory management. on my web search i came across lots of articles dealing with max_allowed_packet or thread_stack. in the server log we get a warning that mysql gets a smaller thread_stack than requested, but most of the answers i found on the web were like ignore it. the machine is a suse linux kernel 2.6.10. i pasted the parts of our mysql config about which i think that they may be important. help on this and explanations of possible causes is very very much appreciated. best regards, oliver # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 3072 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M log-slow-queries= /var/log/mysql-slow.log set-variable= long_query_time=10 set-variable = thread_stack=150K connect_timeout=60 wait_timeout=60 interactive-timeout=60 set-variable= max_connections=2000 thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems with 4.1.11 on Solaris
I just installed the 32-bit SPARC Solaris package (mysql-standard-4.1.11-sun-solaris2.9-sparc.pkg.gz) in made a symbolic link to the install directory as /usr/local/mysql . I am having the following problems: 1) when I start mysql with the included mysql.server script, I get the following error: ./mysql.server start Starting MySQL ./mysql.server: log_success_msg: not found ... there is no such file /lib/lsb/init-functions, so it should get aliased, but don't know why it isn't working. 2) (much more serious) a core file appears in the mysql root directory. 'file core' gives: core: ELF 32-bit MSB core file SPARC Version 1, from 'my_print_defaul' (note the lack of t)... yet the server starts correctly. When I run mysql_print_defaults manually, it's fine. 3) (er, this is since 4.1.10) Given there are no pre or post scripts in the pkg file, can you start putting the version number in the PKG value or at least set the MAXINST to more than on in the pkginfo file? That way when there is a new version, we can install a new version without having to pkgrm the old one (or, like I've done, pkgtrans the new package and make these changes manually) ... thanks, adam a -- Adam Arrowood :: http://www.gatech.edu/adam :: adam AT oit.gatech.edu Office of Information Technology/OE/CS :: (404) 894-0372 Georgia Institute of Technology, Atlanta, GA USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: set type and normalize
On Thu, 7 Apr 2005, Brent Baisley wrote:
Thank you Brent.
As mentioned in the manual you specified, there are limits to how many
items you can have in a set, among other issues. My biggest issue
against using sets is that you need alter the table structure whenever
you want to change the set. Usually I would want the user to be able
to add, modify or delete list items. You do NOT want a user being able
to alter the table structure.
The three table layout gives you basically unlimited scalability,
easier and quicker modification, dynamic sorting, more flexible
searching and no character restrictions.
I can see the unlimited scalability, quicker modification, and no
character restrictions. Dynamic sorting? Do you mean there's a
sort that the three table layout can do that can't be done with the
set type? Or a search?
I'm sorry to be so dense, but can you give me an example?
Thank you again.
Marilyn Davis
On Apr 7, 2005, at 5:20 PM, Marilyn Davis wrote:
Hi MySQL people.
I'm trying to understand the pros and cons of the set column type.
Here is an excerpt from the article:
http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html
Why You Shouldn't Use SET
Third, using a set means your data is not normalized. In our above
example, we are tracking a person's interests for a hypothetical
dating site. In a normalized schema, there should be three tables: one
for the person, one for all possible interests, and one that links a
person to their particular interests.
The above example is:
CREATE TABLE set_test(
rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
myset SET('Travel','Sports','Dancing','Fine Dining')
);
I guess I'm not understanding the point here. What practical
advantage is there to creating three tables instead of using a set
column type?
Thank you for any help.
Marilyn Davis
--
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: set type and normalize
On Apr 7, 2005, at 5:20 PM, Marilyn Davis wrote:
Hi MySQL people.
I'm trying to understand the pros and cons of the set column type.
Here is an excerpt from the article:
http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html
Why You Shouldn't Use SET
Third, using a set means your data is not normalized. In our above
example, we are tracking a person's interests for a hypothetical
dating site. In a normalized schema, there should be three tables: one
for the person, one for all possible interests, and one that links a
person to their particular interests.
The above example is:
CREATE TABLE set_test(
rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
myset SET('Travel','Sports','Dancing','Fine Dining')
);
I guess I'm not understanding the point here. What practical
advantage is there to creating three tables instead of using a set
column type?
Thank you for any help.
Marilyn Davis
A slightly more realistic example might be
CREATE TABLE member
( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(30),
interests SET('Travel','Sports','Dancing','Fine Dining')
);
with some rows like
id nameinterests
1 Joe Travel, Sports
2 Sue Dancing, Fine Dining
The 3 table alternative (the normalized version) would be
CREATE TABLE member
( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(30),
INDEX (name)
);
CREATE TABLE interest
( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
category CHAR(30),
INDEX (category)
);
CREATE TABLE member_interests
( member_id INT UNSIGNED NOT NULL,
interest_id INT UNSIGNED NOT NULL,
PRIMARY KEY (member_id, interest_id),
INDEX (interest_id)
);
Some rows:
member
id name
1 Joe
2 Sue
interest
id category
1 Travel
2 Fine Dining
3 Dancing
4 Sports
member_interests
member_id interest_id
1 1
1 4
2 2
2 3
Despite the apparent increase in complexity of the 3-table version, it
has a number of advantages. Here are a few:
* Retrieving the list of interests
SET:
DESCRIBE member interests;
3 TABLES:
SELECT category FROM interest;
In the SET case, you have to parse the output to find the interests,
while in the 3 TABLES case you simply get a list of interests.
* Adding a new interest
SET:
ALTER TABLE member CHANGE interests interests
SET('Travel','Sports','Dancing','Fine Dining', 'Literature');
3 TABLES:
INSERT INTO interest (category) VALUES ('Literature');
In the SET case, you have to change the table structure! Mysql makes a
temporary table with the new column definition, copies all the data
from the member table to this temporary table, then replaces member
with the temporary table. You cannot edit (INSERT, DELETE, UPDATE) the
member table till this is done. (See
http://dev.mysql.com/doc/mysql/en/alter-table.html) Also, while
mysql should correctly convert the interests data to the new column
definition, you should make a backup first, just in case. In the 3
TABLE case, on the other hand, this is a simple, quick insert.
* Finding members with a particular interest:
SET:
SELECT id, name FROM member WHERE FIND_IN_SET('Sports', interests);
3 TABLES:
SELECT m.id, m.name
FROM member m
JOIN member_interest mi ON m.id = mi.member_id
JOIN interest i ON mi.interest_id = i.id
WHERE i.category = 'Sports';
In the SET case, the query *looks* simpler, but mysql cannot use an
index on member.interests to narrow the search. That is, mysql has to
examine every single row of the member table (a full table scan),
looking at the value in the interests column to see if 'Sports' is in
there. That's slow, and it gets worse as the table grows. In the 3
TABLE case, however, the index on interest.category will be used to
find precisely those rows with the correct interest, without looking at
any other rows. That's fast, and scales better as the table grows.
If your table is small and the load on your server is low, the SET type
may work fine. If this is a table of a 100 or so friends and
relatives, for example, to be used by you (and perhaps your spouse),
the limitations of the SET type may not be a problem. As the table
gets larger and/or the number of users goes up, however, those
limitations will kill you.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UDF request?
On Thu, 7 Apr 2005, Sean Nolan wrote: I think you'll find you can do what you want with a cross join. A cross join will join every row from the first table with every row from the second table. It will not randomly do this, so you'd have to be creative in randomly selecting rows in the WHERE clause. Here's how you could do this with your data, pardon the highly original and very scientific randomization, but it is just an example :-) Perhaps I don't understand your randomization (because I really don't understand it ;), but I don't think a CROSS join does the trick, because I want to randomly pick the same rows from either side of the join that I would have gotten with an INNER JOIN using the 1:1 primary key between the two tables. This is analagous to sampling without replacement. If I do a cross join and then just restrict the number of rows returned (hey! I just worked out what you are doing below) I don't necessarily get the same 'marginals' (or to randomly pick the same rows from either side of the join) . This is analagous to sampling with replacement. Do you see what I mean? SELECT Dept, Gender, AVG(Spending) FROM table_one a CROSS JOIN table_two b WHERE (a.Person * b.Person) % 4 = 3 GROUP BY Dept, Gender; Sean - Original Message -- Hi, I have a request for a UDF that I would find really useful. I am duplicating this question on the community list as well as the MySQL list, because I am not sure where best to make this kind of request (see the previous post http://lists.mysql.com/community/97). I think the following syntax would be really cool... SELECT a.*, b.* FROM table_one a RANDOM JOIN -- -- :) table_two b USING (ID) ; Lets say table_one and table_two have a primary key called ID in common (although of course that isn't necessary). The idea is that the RANDOM JOIN would first calculate the INNER JOIN, then scramble the 'links' (or rows) between the two tables. The result would be the same number of rows overall, the same number of distinct a.ID's and b.ID's, but those a.ID's and b.ID's would be randomly associated with the marginals given by the correct inner join. Hopefully that makes sense. I think this function would be really useful for statistical analysis of scientific data within MySQL (using randomized versions of the associations within the tables). Not sure if the above syntax is optimal, because I would like to be able to do things like this... table_one Dept Person Gender A 1 M A 2 F A 3 M B 4 F B 5 M B 6 F table_two DeptPerson Spending A 1 10 A 2 20 A 3 30 B 4 40 B 5 50 B 6 60 SELECT Dept, Gender, AVG(Spending) FROM table_one a INNER JOIN table_two b USING (Dept,Person) GROUP BY Dept, Gender ; With the above query (which I hope is sensible) I would like to keep the departments fixed, and randomize the genders of the people in the departments (keeping the number of each sex in each department the same). So we could INNER JOIN using Dept and then RANDOM JOIN (as described above) using Person - all in one 'JOIN'. All else being the same this should randomize the Gender, but keep the marginals. I guess this is overly complex given the problem, and it actually raises more questions instantly (about statistical analysis), but the function is basic (I hope), and like all SQL it is the simplicity that allows you to build complex and robust statements. Does the random join make any sense? Is this UDF material? Any feedback is welcome, Dan. Thread UDF request? - Dan Bolser, April 1 2005 12:10am _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF Request AGGLOM()
Who can I prod about setting up a UDF repo at MySQL. I think 'they' should do this ;) http://lists.mysql.com/community/97 Anyway I am posting this request to 'community' because I still don't know the appropriate place to post UDF related stuff. This is anoter (potentially crazy) idea for a UDF that I would find very usefull in my research... AGGLOM - Simple agglomerative clustering for MySQL ... The UDF would work on any NUMBER column, and return the number of 'clusters' using agglomerative clustering with a certain threshold as an input. Agglomerative clustering merges any two numbers that are within the 'threshold', and replaces those numbers with the average of the two. The clustering proceedes smallest 'gap' first, and stops when no two numbers are within the threshold. The result would be the number (or perhaps the values) of the remaining clusters. Syntax (suggested) AGGLOM(THRESH,expr (returning a number)) For example Table1 C1 C2 A 1 A 2 A 3 A 4 A 5 A 6 A 7 B 10 B 11 B 12 B 56 B 57 B 58 B 99 B 101 SELECT C1, AGGLOM(C2,1) AS C3 FROM Table1 GROUP BY C1; C1 C3 A 4 B 6 SELECT C1, AGGLOM(C2,2) AS C3 FROM Table1 GROUP BY C1; C1 C3 A 3 B 3 SELECT C1, AGGLOM(C2,3) AS C3 Table1 GROUP BY C1; C1 C3 A 2 B 3 SELECT C1, AGGLOM(C2,4) AS C3 Table1 GROUP BY C1; C1 C3 A 1 B 3 SELECT C1, AGGLOM(C2,50) AS C3 Table1 GROUP BY C1; C1 C3 A 1 B 1 Remember, merge numbers with the smallest difference first, and replace each pair with the average of the two. Recalculate the differences for the new number, and repeat until no distance is smaller than the threshold. This is a usefull clustering 'hack' to see if a distribution is bi-modal or multi modal for example. It is very quick to calculate using a hash table, and could be a great function to add. Is this idea as crazy as I think it might be? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Warnings level
Hi, I got back warnings, event when started with mysql -v -v, I don't have the Warnings appearing on the console... Do I really have to insert SHOW WARNINGS; in the dump file after each statements ??? On Apr 7, 2005 6:10 PM, Mister Jack [EMAIL PROTECTED] wrote: Hi ! Thanks, for your answer. Indeed, I didn't think about the verbose option. I tried it and used tee to log data. Well so far I haven't got anymore Warnings (strange though...) :-) On Apr 5, 2005 9:50 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Mister Jack [EMAIL PROTECTED] wrote on 04/05/2005 01:38:00 PM: Hi, I've got a dump file from my main DB (MySQL 4.1.10a), when I load it with source backup.sql I can see some warnings around. Is there any way to log those warnings, or to stop on warnings ? (show warnings only show warnings for the _last_ query if there is any). Thanks for your help You can see more details if you launch your client with one of the verbosity options. Use a -v, -v -v, or -v -v -v to increase the verbosity of your client. For more client options (at a shell prompt): mysql --help 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: UDF request?
Dan Bolser [EMAIL PROTECTED] wrote on 04/08/2005 12:41:35 PM: On Thu, 7 Apr 2005, Sean Nolan wrote: I think you'll find you can do what you want with a cross join. A cross join will join every row from the first table with every row from the second table. It will not randomly do this, so you'd have to be creative in randomly selecting rows in the WHERE clause. Here's how you could do this with your data, pardon the highly original and very scientific randomization, but it is just an example :-) Perhaps I don't understand your randomization (because I really don't understand it ;), but I don't think a CROSS join does the trick, because I want to randomly pick the same rows from either side of the join that I would have gotten with an INNER JOIN using the 1:1 primary key between the two tables. This is analagous to sampling without replacement. If I do a cross join and then just restrict the number of rows returned (hey! I just worked out what you are doing below) I don't necessarily get the same 'marginals' (or to randomly pick the same rows from either side of the join) . This is analagous to sampling with replacement. Do you see what I mean? SELECT Dept, Gender, AVG(Spending) FROM table_one a CROSS JOIN table_two b WHERE (a.Person * b.Person) % 4 = 3 GROUP BY Dept, Gender; Sean - Original Message -- Hi, I have a request for a UDF that I would find really useful. I am duplicating this question on the community list as well as the MySQL list, because I am not sure where best to make this kind of request (see the previous post http://lists.mysql.com/community/97). I think the following syntax would be really cool... SELECT a.*, b.* FROM table_one a RANDOM JOIN -- -- :) table_two b USING (ID) ; Lets say table_one and table_two have a primary key called ID in common (although of course that isn't necessary). The idea is that the RANDOM JOIN would first calculate the INNER JOIN, then scramble the 'links' (or rows) between the two tables. The result would be the same number of rows overall, the same number of distinct a.ID's and b.ID's, but those a.ID's and b.ID's would be randomly associated with the marginals given by the correct inner join. Hopefully that makes sense. I think this function would be really useful for statistical analysis of scientific data within MySQL (using randomized versions of the associations within the tables). Not sure if the above syntax is optimal, because I would like to be able to do things like this... table_one Dept Person Gender A 1 M A 2 F A 3 M B 4 F B 5 M B 6 F table_two DeptPerson Spending A 1 10 A 2 20 A 3 30 B 4 40 B 5 50 B 6 60 SELECT Dept, Gender, AVG(Spending) FROM table_one a INNER JOIN table_two b USING (Dept,Person) GROUP BY Dept, Gender ; With the above query (which I hope is sensible) I would like to keep the departments fixed, and randomize the genders of the people in the departments (keeping the number of each sex in each department the same). So we could INNER JOIN using Dept and then RANDOM JOIN (as described above) using Person - all in one 'JOIN'. All else being the same this should randomize the Gender, but keep the marginals. I guess this is overly complex given the problem, and it actually raises more questions instantly (about statistical analysis), but the function is basic (I hope), and like all SQL it is the simplicity that allows you to build complex and robust statements. Does the random join make any sense? Is this UDF material? Any feedback is welcome, Dan. Thread UDF request? - Dan Bolser, April 1 2005 12:10am _ Don?t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ I think I get it. It sounds to me like you want to take a random sampling (possibly to include the entire set) and analyze each sample. You can return the results of a query in random order if you use the RAND() function: http://dev.mysql.com/doc/mysql/en/mathematical-functions.html quote from site As of MySQL 3.23, you can retrieve rows in random order like this: mysql SELECT * FROM tbl_name ORDER BY RAND(); ORDER BY RAND() combined with LIMIT is useful for selecting a random sample of a set of rows: mysql SELECT * FROM table1, table2 WHERE a=b AND cd - ORDER BY RAND() LIMIT 1000; end quote So if you want to randomly pick 1900 rows of data from a query you would say (you need to fill in the blanks with your query): SELECT ... FROM ... WHERE ... ORDER BY RAND() LIMIT 1900; As to randomly matching rows from one table with corresponding rows from a second table, that's just an illusion. For any two
Boolean searches
When using the Boolean search capabilities, I am finding it terribly slow. Since I am sure MySql uses this on their own site. What version of MySql is the fastest at this 5.0? I am using the PHPMySearch and I really think he did fair job on the crawler part. I built an XML converter for the results so I can now crawl websites and output XML its just the search on even 5000 rows (20mb of data) is very slow. Indexing seems to done properly to support this. I played with tweaking the character settings of the FULLTEXT search took it to 5 tried it at 3 no luck. I just think it should be faster than it is. The default indexes are. URL UNIQUE 5451 URL expiresFlag INDEX 1 expiresFlag title FULLTEXT 1 title keywords body_1 FULLTEXT 1 body_1 body_2 Maybe his PHP is querying the database wrongly or is out of date. I am running 4.1.8 Mysql What would be a good example on how to query the db for bring back ranked results based on what it found? ThanksDonny LairsonPresident29 GunMuse LaneP.O. box 166Lakewood NM 88254http://www.gunmuse.com469 228 2183
slave out of sync
i have replication going between 2 boxes. the master crashed a few days ago, and this morning i noticed that the slaves slave thread errored out. the binary log and offset had changed on the master and the slave couldn't sync up. i got the slave up and running again by changing the log file and offset in the slave. but now i am left with out of sync data. how do i get these back in sync, and how do i aviod this in the future? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does such a JOIN exist that can create a pivot table?
Question: I frequently would like to summarize the results of my query in
heiarchical layout also
known as a Pivot table.
Here is an example of what I would like output. NULL will be printed as a space
when output.
sum of broken| source of | qty |reseller of |qty |customer with |qty
by
bolts |broken bolts|regional |broken bolts|reseller |broken bolts
|customer
-
100 | NULL |NULL |NULL| NULL|NULL |NULL
(100/NULL) | US| 75 |NULL| NULL|NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ACME| 35 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Barney Rubble |20
(100/NULL) | US| 75 |NULL| NULL|NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ACME| 35 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Betty Rubble |5
(100/NULL) | US| 75 |NULL| NULL|NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ABLE| 25 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ABLE/NULL) |(25/NULL)|Sponge Bobble |20
(100/NULL) | MEXICO| 15 |NULL| NULL|NULL |NULL
(100/NULL) | (MEX/NULL) |(15/NULL) |TIPPY | 12 |NULL |NULL
(100/NULL) | (MEX/NULL) |(15/NULL) |(ACME/NULL) |(12/NULL)|Juan Pepe |7
100 * * * * * *
* US 75 * * * *
* * * ACME 35 * *
* * * * * Barney Ruble 20
Where * represents NULL or a Primary Key.
How does one build a pivot table?
from tables such as:
factory_parts table
::{
part no,
plant,
qty_manufactured
plant name
}
reseller_parts table
::{
part no
plant
qty received
cost
reseller name
reseller id
}
customer_parts table
::{
reseller id
part no
plant
qty sold
qty recvd
customer id
customer name
}
Ooops now the light bulb comes on
I would do:
select factory_parts.plant name,
reseller_parts.reseller_name
customer_parts.customer_name,
customer_parts.qty_recvd
from factory_parts,reseller_parts,customer_parts
where customer_parts.part_no == 'broken_bolt' AND
( customer_parts.part_no == reseller_parts.part.no AND
customer_parts.part_no == factory_parts.part.no )
Now the question becomes how does one construct the aggregate columns
representing the sum of bolts produced by the company,made at the plant,
shipped to the reseller and sold to the customer,
then join those aggregate columns? Any suggestions?
Thank you,
Raymond
Re: Warnings level
Hi, sorry for the noise, it may be useful for someone else : cat backup20050408.sql | sed 's/INSERT/SHOW WARNINGS;INSERT/' | sed 's/CREATE/SHOW WARNINGS;CREATE/' | sed 's/DROP/SHOW WARNINGS;DROP/' backup.sql then use the backup.sql thus created. So I got :Invalid TIMESTAMP value in column 'timestamp' at row 5407; (I still have to figure this out :) On Apr 8, 2005 6:08 PM, Mister Jack [EMAIL PROTECTED] wrote: Hi, I got back warnings, event when started with mysql -v -v, I don't have the Warnings appearing on the console... Do I really have to insert SHOW WARNINGS; in the dump file after each statements ??? On Apr 7, 2005 6:10 PM, Mister Jack [EMAIL PROTECTED] wrote: Hi ! Thanks, for your answer. Indeed, I didn't think about the verbose option. I tried it and used tee to log data. Well so far I haven't got anymore Warnings (strange though...) :-) On Apr 5, 2005 9:50 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Mister Jack [EMAIL PROTECTED] wrote on 04/05/2005 01:38:00 PM: Hi, I've got a dump file from my main DB (MySQL 4.1.10a), when I load it with source backup.sql I can see some warnings around. Is there any way to log those warnings, or to stop on warnings ? (show warnings only show warnings for the _last_ query if there is any). Thanks for your help You can see more details if you launch your client with one of the verbosity options. Use a -v, -v -v, or -v -v -v to increase the verbosity of your client. For more client options (at a shell prompt): mysql --help 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]
WARNING!!!! abuser on this list?
When I opened the message send by: [EMAIL PROTECTED] subject: Boolean searches a pop up box appeared stating Connect to 70.84.29.164 Web host manager Username mymachine/administrator password: Has anybody encountered this problem when you cliked on his email Should we FLAME that guy? In the mean time I am sending an email to [EMAIL PROTECTED] owning that IP address. Here is the headers of his email: Received: (qmail 7362 invoked by uid 109); 8 Apr 2005 17:13:25 - Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:mysql@lists.mysql.com List-Archive: http://lists.mysql.com/mysql/182353 Delivered-To: mailing list mysql@lists.mysql.com Received: (qmail 7312 invoked from network); 8 Apr 2005 17:13:24 - Received-SPF: pass (lists.mysql.com: local policy) Reply-To: [EMAIL PROTECTED] From: [EMAIL PROTECTED] To: Mysql mysql@lists.mysql.com Subject: Boolean searches Date: Fri, 8 Apr 2005 11:13:13 -0600 Message-ID: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: multipart/related; boundary==_NextPart_000_0098_01C53C2B.F4751020 X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-PopBeforeSMTPSenders: [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],gunmuse,[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],redmoon,[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - pistol.gunmuse.us X-AntiAbuse: Original Domain - lists.mysql.com X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - gunmuse.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Checked: Checked X-Spam-Checker-Version: SpamAssassin 3.0.2 (2004-11-16) on c.spam X-Spam-Status: No, score=1.1 required=5.0 tests=DNS_FROM_AHBL_RHSBL, HTML_MESSAGE,HTML_TAG_EXIST_TBODY,NORMAL_HTTP_TO_IP,NO_REAL_NAME, WEIRD_PORT autolearn=disabled version=3.0.2 X-Spam-Level: * x-html thanks Laurie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WARNING!!!! abuser on this list?
Are you sure that your mail client did not reply by accident? 164.29.84.70.in-addr.arpa. 86400 IN PTR mail.gunmuse.com On Fri, Apr 08, 2005 at 10:52:20AM -0700, l'[EMAIL PROTECTED] wrote: When I opened the message send by: [EMAIL PROTECTED] subject: Boolean searches a pop up box appeared stating Connect to 70.84.29.164 Web host manager Username mymachine/administrator password: Has anybody encountered this problem when you cliked on his email Should we FLAME that guy? In the mean time I am sending an email to [EMAIL PROTECTED] owning that IP address. Here is the headers of his email: Received: (qmail 7362 invoked by uid 109); 8 Apr 2005 17:13:25 - Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:mysql@lists.mysql.com List-Archive: http://lists.mysql.com/mysql/182353 Delivered-To: mailing list mysql@lists.mysql.com Received: (qmail 7312 invoked from network); 8 Apr 2005 17:13:24 - Received-SPF: pass (lists.mysql.com: local policy) Reply-To: [EMAIL PROTECTED] From: [EMAIL PROTECTED] To: Mysql mysql@lists.mysql.com Subject: Boolean searches Date: Fri, 8 Apr 2005 11:13:13 -0600 Message-ID: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: multipart/related; boundary==_NextPart_000_0098_01C53C2B.F4751020 X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-PopBeforeSMTPSenders: [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],gunmuse,[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],redmoon,[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - pistol.gunmuse.us X-AntiAbuse: Original Domain - lists.mysql.com X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - gunmuse.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Checked: Checked X-Spam-Checker-Version: SpamAssassin 3.0.2 (2004-11-16) on c.spam X-Spam-Status: No, score=1.1 required=5.0 tests=DNS_FROM_AHBL_RHSBL, HTML_MESSAGE,HTML_TAG_EXIST_TBODY,NORMAL_HTTP_TO_IP,NO_REAL_NAME, WEIRD_PORT autolearn=disabled version=3.0.2 X-Spam-Level: * x-html thanks Laurie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] !DSPAM:4256c5722811710595229! -- Dale Fay Merit ISS/RADB www.merit.edu www.radb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WARNING!!!! abuser on this list?
On Friday 08 April 2005 18:52, l'[EMAIL PROTECTED] wrote: When I opened the message send by: [EMAIL PROTECTED] subject: Boolean searches a pop up box appeared stating Connect to 70.84.29.164 Web host manager If you bothered to check the source of the HTML mail (why does your mail client trust HTML mail?), you would see that he copied and pasted what appears to be phpmyadmin output into Outlook. Outlook, when in HTML compose mode, will paste src URLs etc for graphics from a web page. In other words, relax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WARNING!!!! abuser on this list?
l'[EMAIL PROTECTED] [EMAIL PROTECTED] wrote on 04/08/2005 01:52:20 PM: When I opened the message send by: [EMAIL PROTECTED] subject: Boolean searches a pop up box appeared stating Connect to 70.84.29.164 Web host manager Username mymachine/administrator password: Has anybody encountered this problem when you cliked on his email Should we FLAME that guy? In the mean time I am sending an email to [EMAIL PROTECTED] owning that IP address. Here is the headers of his email: Received: (qmail 7362 invoked by uid 109); 8 Apr 2005 17:13:25 - Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:mysql@lists.mysql.com List-Archive: http://lists.mysql.com/mysql/182353 Delivered-To: mailing list mysql@lists.mysql.com Received: (qmail 7312 invoked from network); 8 Apr 2005 17:13:24 - Received-SPF: pass (lists.mysql.com: local policy) Reply-To: [EMAIL PROTECTED] From: [EMAIL PROTECTED] To: Mysql mysql@lists.mysql.com Subject: Boolean searches Date: Fri, 8 Apr 2005 11:13:13 -0600 Message-ID: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: multipart/related; boundary==_NextPart_000_0098_01C53C2B.F4751020 X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-PopBeforeSMTPSenders: [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED], [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] com,[EMAIL PROTECTED],gunmuse,[EMAIL PROTECTED], [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED], [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] com,[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED], redmoon,[EMAIL PROTECTED],[EMAIL PROTECTED], [EMAIL PROTECTED] X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - pistol.gunmuse.us X-AntiAbuse: Original Domain - lists.mysql.com X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - gunmuse.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Checked: Checked X-Spam-Checker-Version: SpamAssassin 3.0.2 (2004-11-16) on c.spam X-Spam-Status: No, score=1.1 required=5.0 tests=DNS_FROM_AHBL_RHSBL, HTML_MESSAGE,HTML_TAG_EXIST_TBODY,NORMAL_HTTP_TO_IP,NO_REAL_NAME, WEIRD_PORT autolearn=disabled version=3.0.2 X-Spam-Level: * x-html thanks Laurie I saw the authentication request you mentioned but I thought that was only because he had CUT AND PASTED some live HTML into his message (I have no idea what site it was from) but whichever site it was wanted me to authenticate before I could get something to resolve one of the links he included in his post (maybe a graphic). I just cancelled the dialog and deleted the message. FOR FUTURE REFERENCE - PUTTING LIVE HTML INTO YOUR POSTS MAY GET YOU FLAMED. Please be careful, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Does such a JOIN exist that can create a pivot table?
I think what you are talking about could be called a 'crosstabulation' or
a crosstab.
Their are some tutorials about making cross-tabs using perl. I have used
them a lot, and they are really great.
I tend to stack up lots of IF statemens...
Table1
month person sex sales
1 a m 10
1 b f 20
1 c m 30
2 a m 40
2 b f 50
2 c m 60
select
month,
sum(if(sex='m',sales,0)) as male_sales,
sum(if(sex='f',sales,0)) as female_sales,
sum(sales) as total
from
Table1
group by
month;
This would give (I think)...
month male_sales female_salestotal
1 40 20 60
2 100 50 150
You could easily add a...
count(distinct(if(sex='m',person,NULL))) as total_men,
count(distinct(if(sex='f',person,NULL))) as total_women,
to create average sales for men and women, or anything else you want.
Does that look right?
On Fri, 8 Apr 2005, Jacob, Raymond A Jr wrote:
Question: I frequently would like to summarize the results of my query in
heiarchical layout also
known as a Pivot table.
Here is an example of what I would like output. NULL will be printed as a
space
when output.
sum of broken| source of | qty |reseller of |qty |customer with
|qty by
bolts |broken bolts|regional |broken bolts|reseller |broken bolts
|customer
-
100 | NULL |NULL |NULL| NULL|NULL
|NULL
(100/NULL) | US| 75 |NULL| NULL|NULL
|NULL
(100/NULL) | (US/NULL) |(75/NULL) |ACME| 35 |NULL
|NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Barney Rubble |20
(100/NULL) | US| 75 |NULL| NULL|NULL
|NULL
(100/NULL) | (US/NULL) |(75/NULL) |ACME| 35 |NULL
|NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Betty Rubble |5
(100/NULL) | US| 75 |NULL| NULL|NULL
|NULL
(100/NULL) | (US/NULL) |(75/NULL) |ABLE| 25 |NULL
|NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ABLE/NULL) |(25/NULL)|Sponge Bobble |20
(100/NULL) | MEXICO| 15 |NULL| NULL|NULL
|NULL
(100/NULL) | (MEX/NULL) |(15/NULL) |TIPPY | 12 |NULL
|NULL
(100/NULL) | (MEX/NULL) |(15/NULL) |(ACME/NULL) |(12/NULL)|Juan Pepe |7
100 * * * * * *
* US 75 * * * *
* * * ACME 35 * *
* * * * * Barney Ruble 20
Where * represents NULL or a Primary Key.
How does one build a pivot table?
from tables such as:
factory_parts table
::{
part no,
plant,
qty_manufactured
plant name
}
reseller_parts table
::{
part no
plant
qty received
cost
reseller name
reseller id
}
customer_parts table
::{
reseller id
part no
plant
qty sold
qty recvd
customer id
customer name
}
Ooops now the light bulb comes on
I would do:
select factory_parts.plant name,
reseller_parts.reseller_name
customer_parts.customer_name,
customer_parts.qty_recvd
from factory_parts,reseller_parts,customer_parts
where customer_parts.part_no == 'broken_bolt' AND
( customer_parts.part_no == reseller_parts.part.no AND
customer_parts.part_no == factory_parts.part.no )
Now the question becomes how does one construct the aggregate columns
representing the sum of bolts produced by the company,made at the plant,
shipped to the reseller and sold to the customer,
then join those aggregate columns? Any suggestions?
Thank you,
Raymond
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does such a JOIN exist that can create a pivot table?
Raymond,
Can you bend one or more of the pivot table examples at http://www.artfulsoftware.com/queries.php
to your requirement?
PB
-
Jacob, Raymond A Jr wrote:
Question: I frequently would like to summarize the results of my query in heiarchical layout also
known as a Pivot table.
Here is an example of what I would like output. NULL will be printed as a space
when output.
sum of broken| source of | qty |reseller of |qty |customer with |qty by
bolts |broken bolts|regional |broken bolts|reseller |broken bolts |customer
-
100 | NULL |NULL |NULL| NULL|NULL |NULL
(100/NULL) | US| 75 |NULL| NULL|NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ACME| 35 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Barney Rubble |20
(100/NULL) | US| 75 |NULL| NULL|NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ACME| 35 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ACME/NULL) |(35/NULL)|Betty Rubble |5
(100/NULL) | US| 75 |NULL| NULL|NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |ABLE| 25 |NULL |NULL
(100/NULL) | (US/NULL) |(75/NULL) |(ABLE/NULL) |(25/NULL)|Sponge Bobble |20
(100/NULL) | MEXICO| 15 |NULL| NULL|NULL |NULL
(100/NULL) | (MEX/NULL) |(15/NULL) |TIPPY | 12 |NULL |NULL
(100/NULL) | (MEX/NULL) |(15/NULL) |(ACME/NULL) |(12/NULL)|Juan Pepe |7
100 * * * * * *
* US 75 * * * *
* * * ACME 35 * *
* * * * * Barney Ruble 20
Where * represents NULL or a Primary Key.
How does one build a pivot table?
from tables such as:
factory_parts table
::{
part no,
plant,
qty_manufactured
plant name
}
reseller_parts table
::{
part no
plant
qty received
cost
reseller name
reseller id
}
customer_parts table
::{
reseller id
part no
plant
qty sold
qty recvd
customer id
customer name
}
Ooops now the light bulb comes on
I would do:
select factory_parts.plant name,
reseller_parts.reseller_name
customer_parts.customer_name,
customer_parts.qty_recvd
from factory_parts,reseller_parts,customer_parts
where customer_parts.part_no == 'broken_bolt' AND
( customer_parts.part_no == reseller_parts.part.no AND
customer_parts.part_no == factory_parts.part.no )
Now the question becomes how does one construct the aggregate columns
representing the sum of bolts produced by the company,made at the plant,
shipped to the reseller and sold to the customer,
then join those aggregate columns? Any suggestions?
Thank you,
Raymond
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: set type and normalize
I may have been reaching a little on the dynamic sorting since some of
what I had in mind entails adding more data to the categories. But at a
minimum, you would be able to sort ascending or descending and possibly
use an index on the sort depending on the query. Depending on how you
join your tables in the query, you would be able to replicate the main
data so it appears under each category it is assigned to. Think of
sorting/grouping.
You would be able to sort on the second word in each category if you
wanted. You could also added more data to your categories, like a sort
order field or a weighting field for relevance sorting. Since you are
actually using a database table, you are not just stuck with using a
word, you can link other data to the category and use it for
calculations or sorting. You could even create subcategories, and sort
on category/subcategory.
On Apr 8, 2005, at 12:25 PM, Marilyn Davis wrote:
On Thu, 7 Apr 2005, Brent Baisley wrote:
Thank you Brent.
As mentioned in the manual you specified, there are limits to how many
items you can have in a set, among other issues. My biggest issue
against using sets is that you need alter the table structure whenever
you want to change the set. Usually I would want the user to be able
to add, modify or delete list items. You do NOT want a user being able
to alter the table structure.
The three table layout gives you basically unlimited scalability,
easier and quicker modification, dynamic sorting, more flexible
searching and no character restrictions.
I can see the unlimited scalability, quicker modification, and no
character restrictions. Dynamic sorting? Do you mean there's a
sort that the three table layout can do that can't be done with the
set type? Or a search?
I'm sorry to be so dense, but can you give me an example?
Thank you again.
Marilyn Davis
On Apr 7, 2005, at 5:20 PM, Marilyn Davis wrote:
Hi MySQL people.
I'm trying to understand the pros and cons of the set column type.
Here is an excerpt from the article:
http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html
Why You Shouldn't Use SET
Third, using a set means your data is not normalized. In our above
example, we are tracking a person's interests for a hypothetical
dating site. In a normalized schema, there should be three tables:
one
for the person, one for all possible interests, and one that links a
person to their particular interests.
The above example is:
CREATE TABLE set_test(
rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
myset SET('Travel','Sports','Dancing','Fine Dining')
);
I guess I'm not understanding the point here. What practical
advantage is there to creating three tables instead of using a set
column type?
Thank you for any help.
Marilyn Davis
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WARNING!!!! abuser on this list?
URL UNIQUE 5451 URL expiresFlag INDEX 1 expiresFlag title FULLTEXT 1 title keywords body_1 FULLTEXT 1 body_1 body_2 Whoops on the html copy, calm down there guy. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: l'[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, April 08, 2005 11:52 AM To: mysql@lists.mysql.com Subject: WARNING abuser on this list? When I opened the message send by: [EMAIL PROTECTED] subject: Boolean searches a pop up box appeared stating Connect to 70.84.29.164 Web host manager Username mymachine/administrator password: Has anybody encountered this problem when you cliked on his email Should we FLAME that guy? In the mean time I am sending an email to [EMAIL PROTECTED] owning that IP address. Here is the headers of his email: Received: (qmail 7362 invoked by uid 109); 8 Apr 2005 17:13:25 - Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:mysql@lists.mysql.com List-Archive: http://lists.mysql.com/mysql/182353 Delivered-To: mailing list mysql@lists.mysql.com Received: (qmail 7312 invoked from network); 8 Apr 2005 17:13:24 - Received-SPF: pass (lists.mysql.com: local policy) Reply-To: [EMAIL PROTECTED] From: [EMAIL PROTECTED] To: Mysql mysql@lists.mysql.com Subject: Boolean searches Date: Fri, 8 Apr 2005 11:13:13 -0600 Message-ID: [EMAIL PROTECTED] MIME-Version: 1.0 Content-Type: multipart/related; boundary==_NextPart_000_0098_01C53C2B.F4751020 X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-PopBeforeSMTPSenders: [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],dealerfin [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] .com,gunmuse,[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] e.com,[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],paymen [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] ,redmoon,[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] gunsales.com X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - pistol.gunmuse.us X-AntiAbuse: Original Domain - lists.mysql.com X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - gunmuse.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Checked: Checked X-Spam-Checker-Version: SpamAssassin 3.0.2 (2004-11-16) on c.spam X-Spam-Status: No, score=1.1 required=5.0 tests=DNS_FROM_AHBL_RHSBL, HTML_MESSAGE,HTML_TAG_EXIST_TBODY,NORMAL_HTTP_TO_IP,NO_REAL_NAME, WEIRD_PORT autolearn=disabled version=3.0.2 X-Spam-Level: * x-html thanks Laurie -- 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: Boolean searches
MY first post had html in it where I posted my indexin that I am using. URL UNIQUE 5451 URL expiresFlag INDEX 1 expiresFlag title FULLTEXT 1 title keywords body_1 FULLTEXT 1 body_1 body_2 Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, April 08, 2005 11:13 AM To: Mysql Subject: Boolean searches When using the Boolean search capabilities, I am finding it terribly slow. Since I am sure MySql uses this on their own site. What version of MySql is the fastest at this 5.0? I am using the PHPMySearch and I really think he did fair job on the crawler part. I built an XML converter for the results so I can now crawl websites and output XML its just the search on even 5000 rows (20mb of data) is very slow. Indexing seems to done properly to support this. I played with tweaking the character settings of the FULLTEXT search took it to 5 tried it at 3 no luck. I just think it should be faster than it is. The default indexes are. Maybe his PHP is querying the database wrongly or is out of date. I am running 4.1.8 Mysql What would be a good example on how to query the db for bring back ranked results based on what it found? Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183
Thank you-regarding: Does such a JOIN exist that can create a pivot table?
Does such a JOIN exist that can create a pivot table? Thank you: 182361 by: Dan Bolser 182362 by: Peter Brawley Now I must go into my cave and meditate on these queries: Ommm,Ommm,...Ommm :-) raymond
mysql_query Looping
Hi, just got intro to the list and i hope I can find some help here. I'm trying to figure out the true syntax of a project im working on, basicly theirs a menu on my site that has several catagoies and in those catagories are secondary, and some times 3rd level and 4th level sub lists, i want to do a on click open up and ive got a good script for on click fold out lists but its the SQL syntax loops im not sure how to configure so im trying to figure out at this point how to make a nested list of the items. Then I'll worry about colasping them. heres the psudo code im useing as my guide for my layout Quote ADMIN SIDE Category Tool We would need to set a order of display for the category, and where its bound, (main catagoies would be bound to Main Menu, while subs would be bound to their master). Code wise we need something like if Master = Main Menu display these entries order by Order of display for the main link and then to show the sub links we would need a nested loop to the effect recursive if master = Thisentry[x] display these entries order by Order of display 1. Beauty and Health 1.1. Cosmetics 1.2. Diet Nutrition 1.3. Fashion 1.4. Fitness 2. Computer/Electronics 2.1. Software 2.2. Hardware 2.3. Internet 2.4. Photography 2.5. Wireless 2.6. Audio 2.7. Video 3. Home and Child Care 3.1. Art and Decoration 3.2. Career/Work 3.3. Eating And Dinning 3.4. Education 3.5. Gifts 3.6. Household 3.6.1. Bedding 3.6.2. Flooring 3.6.3. Furniture 3.6.4. Houseware/Appliances 3.6.5. Gaardening 3.6.6. Tools 3.7. Pet 3.8. Staffing Services 4. On The Go 4.1. Autos 4.2. Planes 4.3. Travel Getaways 4.4. Yacts 5. Recreation 5.1. Movies 5.2. Music 5.3. TV 5.4. Radio 5.5. Reading 5.6. Games 6. Services 6.1. Business 6.2. Charities 6.3. Finance 6.4. Insurance 6.5. Legal 6.6. Medical 6.7. Real Estate 7. Shopping (Todays Special and Hot Stuff sub areas) 7.1. Home 7.2. Apparel and Accessories 7.3. Beauty and Health 7.4. Books, Movies Music 7.5. Computing and Office 7.6. Gifts, Flowers Gourmet 7.7. Jewelry Watches 7.8. Sports Outdoors 7.9. Toys, Kids Baby 7.10. A-Z Store Directory 8. Sports 8.1. Clothing and Gear 8.2. Equipment 9. On The Runway this is pre-coding im basicly working on my design documentation i know what i want in psudo-code and am trying to figure out what i need to do in real code to get there. Basicly i know how i need to set up the DB but am unsure on just how precisly to properly display that data in a format similar to the one above where's some titles are sub section of a link or sub sections of a sub section something to the effect of code: Link1 --Sublink1 --Sublink2 --Sublink3 --Sublink4 Sub-sublink1 Sub-sublink2 Sub-sublink3 --Sublink5 --Sublink6 Link2 Link3 Link4 --Sublink1 --Sublink2 Basicly a PHP version of http://advertisementworld.com/main-home.htm the flash link list there im not to worried about the links folding out at this point though. I just want to find a way to get them to auto arrange in the right sequence. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Thank you-regarding: Does such a JOIN exist that can create a pivot table?
On Fri, 8 Apr 2005, Jacob, Raymond A Jr wrote: Does such a JOIN exist that can create a pivot table? Thank you: 182361 by: Dan Bolser 182362 by: Peter Brawley Now I must go into my cave and meditate on these queries: Ommm,Ommm,...Ommm :-) Great! Its a really interesting idea to come up with syntax for a single 'pivot table' query. It is certainly a usefull tool. Any suggested syntax? (I now see pivot table == crosstabulation query, cheers Pete!). Dan. raymond -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1064: You have an error in your SQL syntax....
I am a newbie here. I created a simple table defined as: create table test ( testID int unsigned not null auto_increment, testName varchar(128) not null, primary key (testID) ) type = MyISAM; Now, I filled out test table, and looking for the testName with max characters. The following caused the same error of: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select max(length(testName)) from test)' at line 1 select testName from test where length(testName) = (select max(length(testName)) from test); Then I copied a simple line from MySQL book: select * from president where birth = (select min(birth) from president); and adapted to my table with: select * from test where testName = (select min(testName) from test); and executed it with exactly the same error result. MySQL version I am using is: 4.0.21-standard Please help me why I get this error. Thank you Chuzo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql_query Looping
We overcame that problem to infinite levels with our Blog Software. Instead of loops we use Anchor Points in the url to tell the navigation where it was at all times. This allows for a more dynamic navigation system as you can have Nav trees that not Expand with more subcatorgies but also collapse back into a single point. Example http://mydomain.com/CAT1/CAT2/CAT3/CAT4/ARTICLE1.html Now You have also a tree like http://mydomain.com/CAT1/CAT4/Article1.html http://mydomain.com/CAT1/CAT3/Article1.html There is no duplication of data but Say a privacy policy or Contact info page that needs to be everywhere doesn't need multiple links in the db to make it happen because we are looking at two anchor points. CAT1 and Article1.html What's in the middle doesn't matter because the its not on that page at that time. (YET IT IS IN THE URL) This means you can build freely pages without regard to where they need to go or how to move them later. As for your Expanding TREE (Like DHTML or JavaScript) for the next level down to give it a windows appearance. That would simply be a matter of calling the next anchor points on mouse over and showing the display Some javascript(DHTML) and CSS styling). Its a very complicated Nav system but it allows us to be so versatile when we decide to use third party programs we can create a Nav structure within our domain's website that will run it entirely all from a clean interface no coding required. Just input urls and hit enter. Here's some links to our software its free to use but not GPL as we have done some ground breaking stuff with it. You may not want to try and reinvent the wheel when all you need to do is create a CSS style sheet on our system to do a mouse over menu. http://www.firebasesoftware.com/firebase_downloads/firebase2.0_client_linux. zip http://www.firebasesoftware.com/firebase_downloads/firebase2.0_client_window s.zip Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: Celebrity Institute [mailto:[EMAIL PROTECTED] Sent: Friday, April 08, 2005 1:32 PM To: mysql@lists.mysql.com Subject: mysql_query Looping Hi, just got intro to the list and i hope I can find some help here. I'm trying to figure out the true syntax of a project im working on, basicly theirs a menu on my site that has several catagoies and in those catagories are secondary, and some times 3rd level and 4th level sub lists, i want to do a on click open up and ive got a good script for on click fold out lists but its the SQL syntax loops im not sure how to configure so im trying to figure out at this point how to make a nested list of the items. Then I'll worry about colasping them. heres the psudo code im useing as my guide for my layout Quote ADMIN SIDE Category Tool We would need to set a order of display for the category, and where its bound, (main catagoies would be bound to Main Menu, while subs would be bound to their master). Code wise we need something like if Master = Main Menu display these entries order by Order of display for the main link and then to show the sub links we would need a nested loop to the effect recursive if master = Thisentry[x] display these entries order by Order of display 1. Beauty and Health 1.1. Cosmetics 1.2. Diet Nutrition 1.3. Fashion 1.4. Fitness 2. Computer/Electronics 2.1. Software 2.2. Hardware 2.3. Internet 2.4. Photography 2.5. Wireless 2.6. Audio 2.7. Video 3. Home and Child Care 3.1. Art and Decoration 3.2. Career/Work 3.3. Eating And Dinning 3.4. Education 3.5. Gifts 3.6. Household 3.6.1. Bedding 3.6.2. Flooring 3.6.3. Furniture 3.6.4. Houseware/Appliances 3.6.5. Gaardening 3.6.6. Tools 3.7. Pet 3.8. Staffing Services 4. On The Go 4.1. Autos 4.2. Planes 4.3. Travel Getaways 4.4. Yacts 5. Recreation 5.1. Movies 5.2. Music 5.3. TV 5.4. Radio 5.5. Reading 5.6. Games 6. Services 6.1. Business 6.2. Charities 6.3. Finance 6.4. Insurance 6.5. Legal 6.6. Medical 6.7. Real Estate 7. Shopping (Todays Special and Hot Stuff sub areas) 7.1. Home 7.2. Apparel and Accessories 7.3. Beauty and Health 7.4. Books, Movies Music 7.5. Computing and Office 7.6. Gifts, Flowers Gourmet 7.7. Jewelry Watches 7.8. Sports Outdoors 7.9. Toys, Kids Baby 7.10. A-Z Store Directory 8. Sports 8.1. Clothing and Gear 8.2. Equipment 9. On The Runway this is pre-coding im basicly working on my design documentation i know what i want in psudo-code and am trying to figure out what i need to do in real code to get there. Basicly i know how i need to set up the DB but am unsure on just how precisly to properly display that data in a format similar to the one above where's some titles are sub section of a link or sub sections of a sub section something to the effect of code:
Re: ERROR 1064: You have an error in your SQL syntax....
Chuzo, SELECT MAX(LENGTH(...)) FROM ... ought to work. SQL doesn't allow aggregate funcs like MAX() in the WHERE clause. Use HAVING(). For nested queries like SELECT ... WHERE colvalue=(SELECT...) you need version 4.1 or later. Peter Brawley http://www.artfulsoftware.com - Chuzo Okuda wrote: I am a newbie here. I created a simple table defined as: create table test ( testID int unsigned not null auto_increment, testName varchar(128) not null, primary key (testID) ) type = MyISAM; Now, I filled out test table, and looking for the testName with max characters. The following caused the same error of: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select max(length(testName)) from test)' at line 1 select testName from test where length(testName) = (select max(length(testName)) from test); Then I copied a simple line from MySQL book: select * from president where birth = (select min(birth) from president); and adapted to my table with: select * from test where testName = (select min(testName) from test); and executed it with exactly the same error result. MySQL version I am using is: 4.0.21-standard Please help me why I get this error. Thank you Chuzo -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: GWAVA Sender Notification (Spam)
This is what I call WAY OVER REACTING. This member turned my email into the spam report immediately without thinking. Now I am recieving these. ThanksDonny LairsonPresident29 GunMuse LaneP.O. box 166Lakewood NM 88254http://www.gunmuse.com469 228 2183 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Friday, April 08, 2005 12:51 PMSubject: GWAVA Sender Notification (Spam) A message sent by you was blocked by GWAVA - Content protection for Novell GroupWise. The message was blocked for the following reason(s): Spam The message contained the following information: Subject: RE: WARNING abuser on this list? From: [EMAIL PROTECTED] Recipient(s): [No To Addresses] [No Cc Addresses] [EMAIL PROTECTED], [EMAIL PROTECTED] The following information details the events that prevented delivery of this message: Event Details Spam The message was identified as potential spam
stored procedure
I'm trying to pass a value into a stored procedure to dynamically set which column(s) to sort by. The following code doesn't work. The query executes but ignores my order by parameter. I assume there is a simple answer to this that I'm just missing. create procedure sp_equipment_find ( IN L_ORDER_BY MEDIUMTEXT ) BEGIN SELECT * from mytable ORDER BY @L_ORDER_BY; END$ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: GWAVA Sender Notification (Spam)
[EMAIL PROTECTED] wrote: This is what I call WAY OVER REACTING. *This member turned my email into the spam report immediately without thinking. Now I am recieving these.* I doubt that there's any connection. It's impossible to tell for sure, but the message was probably rejected because of the capital letters and multiple exclamation points in the subject line -- which means the person who posted the original complaining message probably got the same sort of bounce. -- 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: FW: GWAVA Sender Notification (Spam)
Yes I received the same email about being a spammer. Somebody on the list told me that our email headers get rewritten and sometimes you get this email about being a spammer. Laurie At 02:29 PM 4/8/2005, Keith Ivey wrote: [EMAIL PROTECTED] wrote: This is what I call WAY OVER REACTING. *This member turned my email into the spam report immediately without thinking. Now I am recieving these.* I doubt that there's any connection. It's impossible to tell for sure, but the message was probably rejected because of the capital letters and multiple exclamation points in the subject line -- which means the person who posted the original complaining message probably got the same sort of bounce. -- 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: UDF request?
On Fri, 8 Apr 2005 [EMAIL PROTECTED] wrote: Dan Bolser [EMAIL PROTECTED] wrote on 04/08/2005 12:41:35 PM: On Thu, 7 Apr 2005, Sean Nolan wrote: I think you'll find you can do what you want with a cross join. A cross join will join every row from the first table with every row from the second table. It will not randomly do this, so you'd have to be creative in randomly selecting rows in the WHERE clause. Here's how you could do this with your data, pardon the highly original and very scientific randomization, but it is just an example :-) Perhaps I don't understand your randomization (because I really don't understand it ;), but I don't think a CROSS join does the trick, because I want to randomly pick the same rows from either side of the join that I would have gotten with an INNER JOIN using the 1:1 primary key between the two tables. This is analagous to sampling without replacement. If I do a cross join and then just restrict the number of rows returned (hey! I just worked out what you are doing below) I don't necessarily get the same 'marginals' (or to randomly pick the same rows from either side of the join) . This is analagous to sampling with replacement. Do you see what I mean? SELECT Dept, Gender, AVG(Spending) FROM table_one a CROSS JOIN table_two b WHERE (a.Person * b.Person) % 4 = 3 GROUP BY Dept, Gender; Sean - Original Message -- Hi, I have a request for a UDF that I would find really useful. I am duplicating this question on the community list as well as the MySQL list, because I am not sure where best to make this kind of request (see the previous post http://lists.mysql.com/community/97). I think the following syntax would be really cool... SELECT a.*, b.* FROM table_one a RANDOM JOIN -- -- :) table_two b USING (ID) ; Lets say table_one and table_two have a primary key called ID in common (although of course that isn't necessary). The idea is that the RANDOM JOIN would first calculate the INNER JOIN, then scramble the 'links' (or rows) between the two tables. The result would be the same number of rows overall, the same number of distinct a.ID's and b.ID's, but those a.ID's and b.ID's would be randomly associated with the marginals given by the correct inner join. Hopefully that makes sense. I think this function would be really useful for statistical analysis of scientific data within MySQL (using randomized versions of the associations within the tables). Not sure if the above syntax is optimal, because I would like to be able to do things like this... table_one Dept Person Gender A 1 M A 2 F A 3 M B 4 F B 5 M B 6 F table_two DeptPerson Spending A 1 10 A 2 20 A 3 30 B 4 40 B 5 50 B 6 60 SELECT Dept, Gender, AVG(Spending) FROM table_one a INNER JOIN table_two b USING (Dept,Person) GROUP BY Dept, Gender ; With the above query (which I hope is sensible) I would like to keep the departments fixed, and randomize the genders of the people in the departments (keeping the number of each sex in each department the same). So we could INNER JOIN using Dept and then RANDOM JOIN (as described above) using Person - all in one 'JOIN'. All else being the same this should randomize the Gender, but keep the marginals. I guess this is overly complex given the problem, and it actually raises more questions instantly (about statistical analysis), but the function is basic (I hope), and like all SQL it is the simplicity that allows you to build complex and robust statements. Does the random join make any sense? Is this UDF material? Any feedback is welcome, Dan. Thread UDF request? - Dan Bolser, April 1 2005 12:10am _ Don?t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ I think I get it. It sounds to me like you want to take a random sampling (possibly to include the entire set) and analyze each sample. You can return the results of a query in random order if you use the RAND() function: http://dev.mysql.com/doc/mysql/en/mathematical-functions.html quote from site As of MySQL 3.23, you can retrieve rows in random order like this: mysql SELECT * FROM tbl_name ORDER BY RAND(); ORDER BY RAND() combined with LIMIT is useful for selecting a random sample of a set of rows: mysql SELECT * FROM table1, table2 WHERE a=b AND cd - ORDER BY RAND() LIMIT 1000; end quote So if you want to randomly pick 1900 rows of data from a query you would say (you need to fill in the blanks with your query): SELECT ... FROM ... WHERE ... ORDER BY RAND() LIMIT 1900; As to randomly matching rows from one table with corresponding rows from a second
Upgrade to 5.0.3-beta and stored procedures
Greetings, I wanted to see if anyone else has found this problematic, or if I just missed something. I upgraded from 5.0.2 to 5.0.3-beta on my Red Hat AS3 platform. It appears everything was fine till I went to execute my stored procedures, which were owned by root at the console, since that is the only local db access, all remote access is controlled via strong users/passwords. Running as the local db root account I get results I expect. I am getting no results back from any other id attempting the SP execution from something like MySQL Query Browser. I did notice two new items, a procs_priv table and that you can set DEFINER/INVOKER setting on the SP. However, none of these have fixed my problem. Also, I had to add the line manually to the procs_priv table to allow my user to execute the SP. Am I missing something somewhere? Has the process to create and subsequently invoke the SP changed in 5.0.3? David Norman Wells Fargo Services This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.
Re: stored procedure
At 17:15 -0400 4/8/05, Paul Beer wrote: I'm trying to pass a value into a stored procedure to dynamically set which column(s) to sort by. The following code doesn't work. The query executes but ignores my order by parameter. I assume there is a simple answer to this that I'm just missing. Yes. Unfortunately, the simple answer is that you can't do it. Parameters are for data values, not column names. (You'll encounter a similar problem in many database APIs if you try to use a parameter in a prepared statement for anything but a data value.) create procedure sp_equipment_find ( IN L_ORDER_BY MEDIUMTEXT ) BEGIN SELECT * from mytable ORDER BY @L_ORDER_BY; END$ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: set type and normalize
On Fri, 8 Apr 2005, Brent Baisley wrote:
I may have been reaching a little on the dynamic sorting since some of
what I had in mind entails adding more data to the categories. But at a
I see. Thank you.
minimum, you would be able to sort ascending or descending and possibly
use an index on the sort depending on the query. Depending on how you
join your tables in the query, you would be able to replicate the main
data so it appears under each category it is assigned to. Think of
sorting/grouping.
You would be able to sort on the second word in each category if you
wanted. You could also added more data to your categories, like a sort
order field or a weighting field for relevance sorting. Since you are
actually using a database table, you are not just stuck with using a
word, you can link other data to the category and use it for
calculations or sorting. You could even create subcategories, and sort
on category/subcategory.
Yes, very powerful and flexible. But I think it's overkill for my
situation.
And thank you Michael! I made hard-copy of your great explanation and
studied it hard.
I'm feeling more confident about going ahead with the set type for my
table.
I have been using an enum for the 5 possible values we have in a
field, each one flagging a different process. But very occasionally,
two values might be appropriate, and some other considerations must be
taken in account to choose the process on the fly.
So I'm upgrading from enum to set on the field.
Users never see or know about the elements of the SET and certainly
never can add one. It's nothing interesting like dancing and
traveling.
So, I'm feeling pretty confident that it's the right thing for us.
Thank you for the great help! I mean *really* great help.
Marilyn Davis
On Fri, 8 Apr 2005, Michael Stassen wrote:
On Apr 7, 2005, at 5:20 PM, Marilyn Davis wrote:
Hi MySQL people.
I'm trying to understand the pros and cons of the set column type.
Here is an excerpt from the article:
http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html
Why You Shouldn't Use SET
Third, using a set means your data is not normalized. In our above
example, we are tracking a person's interests for a hypothetical
dating site. In a normalized schema, there should be three tables: one
for the person, one for all possible interests, and one that links a
person to their particular interests.
The above example is:
CREATE TABLE set_test(
rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
myset SET('Travel','Sports','Dancing','Fine Dining')
);
I guess I'm not understanding the point here. What practical
advantage is there to creating three tables instead of using a set
column type?
Thank you for any help.
Marilyn Davis
A slightly more realistic example might be
CREATE TABLE member
( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(30),
interests SET('Travel','Sports','Dancing','Fine Dining')
);
with some rows like
id nameinterests
1 Joe Travel, Sports
2 Sue Dancing, Fine Dining
The 3 table alternative (the normalized version) would be
CREATE TABLE member
( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(30),
INDEX (name)
);
CREATE TABLE interest
( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
category CHAR(30),
INDEX (category)
);
CREATE TABLE member_interests
( member_id INT UNSIGNED NOT NULL,
interest_id INT UNSIGNED NOT NULL,
PRIMARY KEY (member_id, interest_id),
INDEX (interest_id)
);
Some rows:
member
id name
1 Joe
2 Sue
interest
id category
1 Travel
2 Fine Dining
3 Dancing
4 Sports
member_interests
member_id interest_id
1 1
1 4
2 2
2 3
Despite the apparent increase in complexity of the 3-table version, it
has a number of advantages. Here are a few:
* Retrieving the list of interests
SET:
DESCRIBE member interests;
3 TABLES:
SELECT category FROM interest;
In the SET case, you have to parse the output to find the interests,
while in the 3 TABLES case you simply get a list of interests.
* Adding a new interest
SET:
ALTER TABLE member CHANGE interests interests
SET('Travel','Sports','Dancing','Fine Dining', 'Literature');
3 TABLES:
INSERT INTO interest (category) VALUES ('Literature');
In the SET case, you have to change the table structure! Mysql makes a
temporary table with the new column definition, copies all the data
from the member table to this temporary table, then replaces member
with the temporary table. You cannot edit (INSERT, DELETE, UPDATE) the
member table till this is done. (See
