ALTER TRIGGER in MySQL
Hello, I was reading the docs on Triggers at: http://dev.mysql.com/doc/mysql/en/using-triggers.html Looks like there is no support for Alter Trigger. So if I want to change a trigger, I have to first drop the existing one and recreate another? Am I correct? Karam Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL vs Plone/Zope/Python
I've been experimenting with databases, both Drupal and Plone. Plone is a can of worms, but it's intriguing, partly because it's so different. It sounds like they replace LAMP with something completely different, though it's a complete mystery to me at present. I wondered if anyone on this list has had experience with Plone and could explain how their system compares to PHP/MySQL. I'll be working with animal kingdom data - child-parent relationships and recursive arrays. I don't want to abandon PHP and MySQL for something totally new after I've put so much time and effort into them. But it might be worthwhile for this particular site if Plone offers some major advantage. It's also my understanding that PHP and MySQL can still be used with Plone, though not be default. So, do you know if Plone offers any significant advantages over MySQL, or is it just comparing apples and oranges? Thanks. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with datetime values and MyODBC driver
Thanks Dwi for the tip, but unfortunately this solution is possible only in applications which connect only to MySQL and I am already using it in those applications but now I need to connect to MySQL or to MS Access (or to other DB engine) with one query using only different connection string (and different ODBC drivers). And thus I must set query parameters to their correct data types and ODBC driver should do the conversions. And that is what is not working. I will probably fill a bug report and will see. Dusan - Original Message - From: Dwi Putra L To: Dušan Pavlica Sent: Friday, August 05, 2005 6:34 AM Subject: Re: Problem with datetime values and MyODBC driver Dušan Pavlica wrote: Hello, I'm working in C++ Builder 6.0 and I'm trying to create application which could connect through ADO components to different databases using ODBC drivers. If I create TADOQuery object which has datetime parametr and I fill this parametr with valid datetime value then I can see in MYSQL's query log that only date portion is used. But if I use same query object to connect to MS Access then data in MS Access are OK. Win XP, MyODBC 3.51.11, MySQL 4.1.9 Does anybody have any idea how to solve this problem? Thanks in advance Dusan Pavlica I use delphi 7 and I have same problem with you MySQL save and search datetime data as string. My solution is to change datetime data into string with format : 2005-08-05 (year-month-day) -- var str_year, str_month, str_day, str_date : string; year, month, day : word; DecodeDate(Now,year,month,day); str_year := inttostr(year); str_month := inttostr(month); if Length(str_month) 2 then str_month := '0' + str_month; str_day := inttostr(day); if Length(str_day) 2 then str_day := '0' + str_day; str_date := str_year + '-' + str_month + '-' + str_day; --- The same thing when I want to conduct a query, which using date as parameters. I change certain date into string. _dwi.
Re: Creating new username password
OK, I think that worked. I didn't get any error messages at least. :) Thanks. --- Eugene Kosov [EMAIL PROTECTED] wrote: David Blomstrom wrote: What's the easiest way to create a username and password for a new database? It's been so long since I created my original database, I forgot how. You can do it with a query like this: GRANT USAGE ON database_name.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password'; Replace USAGE in query above with priveleges set you want for your new account. See http://dev.mysql.com/doc/mysql/en/grant.html for more info. Regards, Eugene Kosov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
difference mysql_query and mysql_real_query
need help... what is the different between mysql_query and mysql_real_query, is it about speed or stable? and how to use mysql_use_result ?
Re: Count two kinds of related records?
Brian Dunning wrote: I'm searching a table of people who own properties, and I want to also include the total count of related properties, and the count of related properties whose (status is 'Active' and approval is 'Active'). I've got: select accounts.name, count(properties.property_id) as totalcount from accounts, properties where accounts.account_id=properties.account_id group by accounts.account_id; Works fine. Now I just need to figure out how to add that second count of property records meeting the two conditions. Anyone? I think query below should help you. SELECT accounts.name, COUNT(properties.property_id) AS totalcount, SUM(IF(status='Active' AND 'approval='Active', 1, 0)) FROM accounts LEFT JOIN properties USING(account_id) GROUP BY accounts.account_id; Regards, Eugene Kosov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DROP VIEW Syntax
Hello, In the MySQL Docs at http://dev.mysql.com/doc/mysql/en/drop-view.html, it says: RESTRICT and CASCADE, if given, are parsed and ignored. I am not sure if I understood this? Regards, karam __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: difference mysql_query and mysql_real_query
Afivi Andri S wrote: need help... what is the different between mysql_query and mysql_real_query, is it about speed or stable? header are int mysql_query(MYSQL *mysql, const char *query) int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length) and mysql_query is in fact : mysql_real_query(mysql, query, strlen(query)); -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database equivalent to NorthWind for M$ SQL
In article [EMAIL PROTECTED], Scott Hamm [EMAIL PROTECTED] writes: Ok. Now that I got NorthWind into my MySQL 5.0.9 database and also am running M$ SQL database server at where I work, this might be a good opportunity for me to learn how to mirgate from M$ SQL database to MySQL using Northwind and to see the difference between them. Is there anything that I should pay close attention to, in order to make the most smooth mirgation? When I migrated several databases from M$ SQL to MySQL, some dates was flushed to 0 values. This is a MySQL gotcha. See for example http://sql-info.de/mysql/gotchas.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP VIEW Syntax
Hi, In the MySQL Docs at http://dev.mysql.com/doc/mysql/en/drop-view.html, it says: RESTRICT and CASCADE, if given, are parsed and ignored. I am not sure if I understood this? Easy, you can specify restrict or cascade, but MySQL does absolutely nothing with it. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.23a mysqld defunct
Arek H wrote: Hi Im running a stock install of Slackware 10.1 with the default setup of mysql. Whats happening is when I hit the reload button several times on a page that uses php and accesses mysql I get mysqld defunct and zombies start showing when viewing top. I also get this when I run a web based apache bench on my server with the Requests set to 25 and Concurrency set to 5. I checked my domains error logs and there is nothing there. I looked in the error log located at /var/lib/mysql/ano.err and here are the last few lines of it. Im new to linux so this is the only error log I could find for mysql . Memory status: Non-mmapped space allocated from system: 5204056 Number of free chunks: 15 Number of fastbin blocks:0 Number of mmapped regions: 11 Space in mmapped regions:22401024 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 4901384 Total free space:302672 Top-most, releasable space: 131176 Estimated memory (with thread stack):27605080 050731 15:57:12 mysqld ended 050731 16:35:00 mysqld started 050731 16:35:02 InnoDB: Started /usr/libexec/mysqld: ready for connections. Version: '4.0.23a' socket: '/var/run/mysql/mysql.sock' port: 0 Source distribution 050731 16:40:59 /usr/libexec/mysqld: Normal shutdown 050731 16:40:59 InnoDB: Starting shutdown... 050731 16:41:00 InnoDB: Shutdown completed 050731 16:41:00 /usr/libexec/mysqld: Shutdown Complete 050731 16:41:00 mysqld ended 050731 16:42:17 mysqld started 050731 16:42:19 InnoDB: Started /usr/libexec/mysqld: ready for connections. Version: '4.0.23a' socket: '/var/run/mysql/mysql.sock' port: 0 Source distribution Also here is what top shows 20797 root 12 0 1060 1060 824 R 1.3 0.2 0:18.60 top 20993 mysql 11 0 000 Z 1.0 0.0 0:00.03 mysqld defunct 20999 mysql 10 0 15612 15m 3020 S 0.6 3.1 0:00.02 mysqld 21007 mysql 11 0 000 Z 0.6 0.0 0:00.02 mysqld defunct 21017 mysql 13 0 15632 15m 3020 S 0.6 3.1 0:00.02 mysqld 1000 mysql 17 0 15592 15m 3020 R 0.3 3.1 0:01.89 mysqld 20991 mysql 9 0 000 Z 0.3 0.0 0:00.01 mysqld defunct 21013 mysql 10 0 15632 15m 3020 S 0.3 3.1 0:00.01 mysqld 21015 mysql 11 0 15632 15m 3020 S 0.3 3.1 0:00.01 mysqld 1 root 8 0 228 228 200 S 0.0 0.0 0:04.86 init 2 root 9 0 000 S 0.0 0.0 0:00.05 keventd 3 root 19 19 000 S 0.0 0.0 0:00.00 ksoftirqd_CPU0 4 root 9 0 000 S 0.0 0.0 0:00.00 kswapd 5 root 9 0 000 S 0.0 0.0 0:00.00 bdflush 6 root 9 0 000 S 0.0 0.0 0:00.02 kupdated 10 root -1 -20 000 S 0.0 0.0 0:00.00 mdrecoveryd 11 root 9 0 000 S 0.0 0.0 0:00.06 kjournald 44 root 9 0 000 S 0.0 0.0 0:00.16 kjournald 45 root 9 0 000 S 0.0 0.0 0:00.26 kjournald Thanks Arek Post here the result of a ps ajxf, but it seems to me that you didn't started properly mysql. You should start MySQL with mysqld_safe, not with mysqld. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.23a mysqld defunct
Nuno Pereira wrote: Post here the result of a ps ajxf, but it seems to me that you didn't started properly mysql. You should start MySQL with mysqld_safe, not with mysqld. Here it is PPID PID PGID SID TTY TPGID STAT UID TIME COMMAND 0 1 0 0 ? -1 S0 0:04 init [3] 1 2 1 1 ? -1 S0 0:00 [keventd] 1 3 1 1 ? -1 SN 0 0:00 [ksoftirqd_CPU0] 1 4 1 1 ? -1 S0 0:00 [kswapd] 1 5 1 1 ? -1 S0 0:00 [bdflush] 1 6 1 1 ? -1 S0 0:00 [kupdated] 110 1 1 ? -1 S 0 0:00 [mdrecoveryd] 111 1 1 ? -1 S0 0:00 [kjournald] 144 1 1 ? -1 S0 0:00 [kjournald] 145 1 1 ? -1 S0 0:00 [kjournald] 146 1 1 ? -1 S0 0:00 [kjournald] 147 1 1 ? -1 S0 0:00 [kjournald] 148 1 1 ? -1 S0 0:00 [kjournald] 1696969 ? -1 Ss 0 0:02 /usr/sbin/syslogd 1727272 ? -1 Ss 0 0:00 /usr/sbin/klogd -c 3 -x 1 132 132 132 ? -1 Ss 0 0:00 udevd 1 233 1 1 ? -1 S0 0:00 [khubd] 1 696 696 696 ? -1 Ss 0 0:00 /sbin/dhcpcd -d -t 10 eth0 1 943 943 943 ? -1 Ss 0 0:00 /usr/sbin/inetd 1 947 947 947 ? -1 Ss 0 0:06 /usr/sbin/sshd 947 16144 16144 16144 ? -1 Ss 0 0:00 \_ sshd: arek [priv] 16144 16147 16144 16144 ? -1 S 1000 0:06 | \_ sshd: [EMAIL PROTECTED]/0 16147 16148 16148 16148 pts/021532 Ss1000 0:00 | \_ -bash 16148 16881 16881 16148 pts/021532 S0 0:00 | \_ bash 16881 21532 21532 16148 pts/021532 R+ 0 0:00 | \_ ps ajxf 947 21108 21108 21108 ? -1 Ss 0 0:00 \_ sshd: arek [priv] 21108 2 21108 21108 ? -1 S 1000 0:00 \_ sshd: [EMAIL PROTECTED]/1 2 21112 21112 21112 pts/121132 Ss1000 0:00 \_ -bash 21112 21123 21123 21112 pts/121132 S0 0:00 \_ bash 21123 21132 21132 21112 pts/121132 S+ 0 0:00 \_ tail -f syslog 1 958 957 957 ? -1 S0 0:00 /usr/sbin/crond -l10 1 960 960 960 ? -1 Ss 2 0:00 /usr/sbin/atd -b 15 -l 1 1 963 963 963 ? -1 Ss 0 0:00 sendmail: accepting connections 1 966 966 966 ? -1 Ss 25 0:00 sendmail: Queue [EMAIL PROTECTED]:25:00 for /var/spool/clientmqueue 1 9726161 ? -1 S0 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var 972 9996161 ? -1 S 27 0:02 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --use 999 10006161 ? -1 S 27 0:01 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql - 1000 10016161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10026161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10036161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10046161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10056161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10066161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10076161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10086161 ? -1 S 27 0:02 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1 976 976 976 ? -1 Ss 0 0:00 /usr/sbin/gpm -m /dev/mouse -t ps2 1 1010 1010 1010 tty1 1010 Ss+ 0 0:00 -bash 1 1011 1011 1011 tty2 1011 Ss+ 0 0:00 /sbin/agetty 38400 tty2 linux 1 1012 1012 1012 tty3 1012 Ss+ 0 0:00 /sbin/agetty 38400 tty3 linux 1 1013 1013 1013 tty4 1013 Ss+ 0 0:00 /sbin/agetty 38400 tty4 linux 1 1014 1014 1014 tty5 1014 Ss+ 0 0:00 /sbin/agetty 38400 tty5 linux 1 1015 1015 1015 tty6 1015 Ss+ 0 0:00 /sbin/agetty 38400 tty6 linux 1 17378 17378 17378 ? -1 Ss 0 0:00 /usr/sbin/httpd 17378 17379 17378 17378 ? -1 S 99 0:27 \_ /usr/sbin/httpd 17378
Re: MySQL 4.0.23a mysqld defunct
Arek H wrote: Nuno Pereira wrote: Post here the result of a ps ajxf, but it seems to me that you didn't started properly mysql. You should start MySQL with mysqld_safe, not with mysqld. Here it is PPID PID PGID SID TTY TPGID STAT UID TIME COMMAND 0 1 0 0 ? -1 S0 0:04 init [3] 1 2 1 1 ? -1 S0 0:00 [keventd] 1 3 1 1 ? -1 SN 0 0:00 [ksoftirqd_CPU0] 1 4 1 1 ? -1 S0 0:00 [kswapd] 1 5 1 1 ? -1 S0 0:00 [bdflush] 1 6 1 1 ? -1 S0 0:00 [kupdated] 110 1 1 ? -1 S 0 0:00 [mdrecoveryd] 111 1 1 ? -1 S0 0:00 [kjournald] 144 1 1 ? -1 S0 0:00 [kjournald] 145 1 1 ? -1 S0 0:00 [kjournald] 146 1 1 ? -1 S0 0:00 [kjournald] 147 1 1 ? -1 S0 0:00 [kjournald] 148 1 1 ? -1 S0 0:00 [kjournald] 1696969 ? -1 Ss 0 0:02 /usr/sbin/syslogd 1727272 ? -1 Ss 0 0:00 /usr/sbin/klogd -c 3 -x 1 132 132 132 ? -1 Ss 0 0:00 udevd 1 233 1 1 ? -1 S0 0:00 [khubd] 1 696 696 696 ? -1 Ss 0 0:00 /sbin/dhcpcd -d -t 10 eth0 1 943 943 943 ? -1 Ss 0 0:00 /usr/sbin/inetd 1 947 947 947 ? -1 Ss 0 0:06 /usr/sbin/sshd 947 16144 16144 16144 ? -1 Ss 0 0:00 \_ sshd: arek [priv] 16144 16147 16144 16144 ? -1 S 1000 0:06 | \_ sshd: [EMAIL PROTECTED]/0 16147 16148 16148 16148 pts/021532 Ss1000 0:00 | \_ -bash 16148 16881 16881 16148 pts/021532 S0 0:00 | \_ bash 16881 21532 21532 16148 pts/021532 R+ 0 0:00 | \_ ps ajxf 947 21108 21108 21108 ? -1 Ss 0 0:00 \_ sshd: arek [priv] 21108 2 21108 21108 ? -1 S 1000 0:00 \_ sshd: [EMAIL PROTECTED]/1 2 21112 21112 21112 pts/121132 Ss1000 0:00 \_ -bash 21112 21123 21123 21112 pts/121132 S0 0:00 \_ bash 21123 21132 21132 21112 pts/121132 S+ 0 0:00 \_ tail -f syslog 1 958 957 957 ? -1 S0 0:00 /usr/sbin/crond -l10 1 960 960 960 ? -1 Ss 2 0:00 /usr/sbin/atd -b 15 -l 1 1 963 963 963 ? -1 Ss 0 0:00 sendmail: accepting connections 1 966 966 966 ? -1 Ss 25 0:00 sendmail: Queue [EMAIL PROTECTED]:25:00 for /var/spool/clientmqueue 1 9726161 ? -1 S0 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var 972 9996161 ? -1 S 27 0:02 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --use 999 10006161 ? -1 S 27 0:01 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql - 1000 10016161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10026161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10036161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10046161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10056161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10066161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10076161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10086161 ? -1 S 27 0:02 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1 976 976 976 ? -1 Ss 0 0:00 /usr/sbin/gpm -m /dev/mouse -t ps2 1 1010 1010 1010 tty1 1010 Ss+ 0 0:00 -bash 1 1011 1011 1011 tty2 1011 Ss+ 0 0:00 /sbin/agetty 38400 tty2 linux 1 1012 1012 1012 tty3 1012 Ss+ 0 0:00 /sbin/agetty 38400 tty3 linux 1 1013 1013 1013 tty4 1013 Ss+ 0 0:00 /sbin/agetty 38400 tty4 linux 1 1014 1014 1014 tty5 1014 Ss+ 0 0:00 /sbin/agetty 38400 tty5 linux 1 1015 1015 1015 tty6 1015 Ss+ 0 0:00 /sbin/agetty 38400 tty6 linux 1 17378 17378 17378 ? -1 Ss 0 0:00 /usr/sbin/httpd 17378 17379 17378 17378 ? -1 S 99 0:27 \_
Re: MySQL 4.0.23a mysqld defunct
Nuno Pereira wrote: You don't have any Defunct MySQL process in this case. Try to replicate the case when you got the defunct processes, and post the output like this, in tree, so we can see how lost hist child. Ok managed to capture it. 0 1 0 0 ? -1 S0 0:04 init [3] 1 2 1 1 ? -1 S0 0:00 [keventd] 1 3 1 1 ? -1 RN 0 0:00 [ksoftirqd_CPU0] 1 4 1 1 ? -1 S0 0:00 [kswapd] 1 5 1 1 ? -1 S0 0:00 [bdflush] 1 6 1 1 ? -1 S0 0:00 [kupdated] 110 1 1 ? -1 S 0 0:00 [mdrecoveryd] 111 1 1 ? -1 S0 0:00 [kjournald] 144 1 1 ? -1 S0 0:00 [kjournald] 145 1 1 ? -1 S0 0:00 [kjournald] 146 1 1 ? -1 S0 0:00 [kjournald] 147 1 1 ? -1 S0 0:00 [kjournald] 148 1 1 ? -1 S0 0:00 [kjournald] 1696969 ? -1 Ss 0 0:02 /usr/sbin/syslogd 1727272 ? -1 Ss 0 0:00 /usr/sbin/klogd -c 3 -x 1 132 132 132 ? -1 Ss 0 0:00 udevd 1 233 1 1 ? -1 S0 0:00 [khubd] 1 696 696 696 ? -1 Ss 0 0:00 /sbin/dhcpcd -d -t 10 eth0 1 943 943 943 ? -1 Ss 0 0:00 /usr/sbin/inetd 1 947 947 947 ? -1 Ss 0 0:06 /usr/sbin/sshd 947 16144 16144 16144 ? -1 Ss 0 0:00 \_ sshd: arek [priv] 16144 16147 16144 16144 ? -1 S 1000 0:07 | \_ sshd: [EMAIL PROTECTED]/0 16147 16148 16148 16148 pts/06 Ss1000 0:00 | \_ -bash 16148 16881 16881 16148 pts/06 S0 0:01 | \_ bash 16881 6 6 16148 pts/06 R+ 0 0:00 | \_ ps ajxf 947 21108 21108 21108 ? -1 Ss 0 0:00 \_ sshd: arek [priv] 21108 2 21108 21108 ? -1 S 1000 0:00 \_ sshd: [EMAIL PROTECTED]/1 2 21112 21112 21112 pts/121573 Ss1000 0:00 \_ -bash 21112 21123 21123 21112 pts/121573 S0 0:00 \_ bash 21123 21573 21573 21112 pts/121573 S+ 0 0:03 \_ top 1 958 957 957 ? -1 S0 0:00 /usr/sbin/crond -l10 1 960 960 960 ? -1 Ss 2 0:00 /usr/sbin/atd -b 15 -l 1 1 963 963 963 ? -1 Ss 0 0:00 sendmail: rejecting connections on daemon MSA: load average: 12 1 966 966 966 ? -1 Ss 25 0:00 sendmail: Queue [EMAIL PROTECTED]:25:00 for /var/spool/clientmqueue 1 9726161 ? -1 S0 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var 972 9996161 ? -1 S 27 0:02 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --use 999 10006161 ? -1 R 27 0:02 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql - 1000 10016161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10026161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10036161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10046161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10056161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10066161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10076161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 10086161 ? -1 S 27 0:02 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 222196161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 06161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 16161 ? -1 Z 27 0:00 \_ [mysqld] defunct 1000 26161 ? -1 S 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 36161 ? -1 Z 27 0:00 \_ [mysqld] defunct 1000 46161 ? -1 R 27 0:00 \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mys 1000 56161 ? -1 R 27 0:00
MESSAGE COULD NOT BE DELIVERED
The message was not delivered due to the following reason(s): Your message was not delivered because the destination server was not reachable within the allowed queue period. The amount of time a message is queued before it is returned depends on local configura- tion parameters. Most likely there is a network problem that prevented delivery, but it is also possible that the computer is turned off, or does not have a mail system running right now. Your message was not delivered within 1 days: Host 154.162.34.121 is not responding. The following recipients did not receive this message: mysql@lists.mysql.com Please reply to [EMAIL PROTECTED] if you feel this message to be in error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: True randominess
On 8/4/05, Scott Gifford [EMAIL PROTECTED] wrote: Pat Adams [EMAIL PROTECTED] writes: On Thu, 2005-08-04 at 14:44 -0500, 2wsxdr5 wrote: There are also several places that you can get a reasonably random number for the seed from your machine. The amount of free disk space, unless that doesn't change much on your machine. The amount of free RAM, (up time mod cpu usage). Any number of things could be used that are not very predictable, if at all. But again, those aren't truely random. They're random-enough for the average web applications. The original poster, if memory serves, asked if it was possible to get true random numbers from MySQL. True random numbers can't be predicted even if I know everything about your system. Because computers are predictable beasts, the random number generators that they used are constrained by the hardware limits. /dev/random is a source of some genuine entropy on many Unix-like operating systems. It uses variations in system timings that are believed to be truly random. It's not good for a large volume of output, but it's a good seed. You could probably incorporate access to it or its friend /dev/urandom as a UDF: http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/Adding_UDF.html EGD (Entropy Gathering Daemon) is an option for other Unix-like systems: http://egd.sourceforge.net/ or you can use a Lava Lamp: http://www.lavarnd.org/index.html I'm sure Windows has some way to do this, too. Many systems also have an onboard random number generator which you should be able to access through an OS driver. ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thanks for the wonderful sources! -- Power to people, Linux is here.
Show Constraint_type
Good morning All, What Mysql command show us CONSTRAINT_TYPE value as UNIQUE, PRIMARY KEY, or FOREIGN KEY of and schema? Thanks.Nguyen. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show Constraint_type
Nguyen, Phong [EMAIL PROTECTED] wrote on 08/05/2005 08:26:58 AM: Good morning All, What Mysql command show us CONSTRAINT_TYPE value as UNIQUE, PRIMARY KEY, or FOREIGN KEY of and schema? Thanks.Nguyen. I believe you are looking for the SHOW CREATE TABLE command. If I am using the MySQL CLI, I prefer the output formatted with the \G terminator instead of the ; terminator like this SHOW CREATE TABLE tablename\G Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: How to delete log files? Was: RE: Where did my disk space go?
Hello. About deleting the InnoDB log files see: http://dev.mysql.com/doc/mysql/en/adding-and-removing.html Now what about this my.cnf file? As I recall, this file lives in the top Program options could be passed in different ways. You can add your specific options (for example InnoDB parameters) in configuration file. Create it from one of the templates if it doesn't exist. See: http://dev.mysql.com/doc/mysql/en/program-options.html http://dev.mysql.com/doc/mysql/en/option-files.html Siegfried Heintze [EMAIL PROTECTED] wrote: David (and anyone else): Here is the directory of my data directory. I don't see hundreds of mega bytes here, but deleting ib_logfile* and ibdata1 would sure help. Which of these files I can delete without loosing any data from my database? drwxr-xr-x 1 Administrator mkpasswd0 Jul 30 23:07 hotjobs -rw-r--r-- 1 Administrator mkpasswd25,088 Mar 4 18:05 ib_arch_log_00 -rw-r--r-- 1 Administrator mkpasswd 5,242,880 Aug 3 17:52 ib_logfile0 -rw-r--r-- 1 Administrator mkpasswd 5,242,880 Mar 4 18:05 ib_logfile1 -rw-r--r-- 1 Administrator mkpasswd 10,485,760 May 3 15:47 ibdata1 drwxr-xr-x 1 Administrator mkpasswd0 Mar 4 18:02 mysql -rw-r--r-- 1 Administrator mkpasswd53,127 Aug 3 17:52 sales.err drwxr-xr-x 1 Administrator mkpasswd0 Mar 4 18:02 test Now what about this my.cnf file? As I recall, this file lives in the top level directory. I have no such file. I found these files in the mysql directory: my-huge.cnf my-innodb-heavy-4G.cnf my-large.cnf my-medium.cnf my-small.cnf I don't recall editing any of these. Should I be editing them? Thanks, Siegfried -Original Message- From: David Logan [mailto:[EMAIL PROTECTED] Sent: Thursday, August 04, 2005 3:33 PM To: Siegfried Heintze Cc: mysql@lists.mysql.com Subject: RE: Where did my disk space go? Hi Siegfried, In the mysql data directory, -rw-rw1 mysqlmysql 358975 Mar 10 14:28 aaudbasa01.log -rw-rw1 mysqlmysql 25088 Feb 15 08:08 ib_arch_log_00 -rw-rw1 mysqlmysql 5242880 Jun 22 11:20 ib_logfile0 -rw-rw1 mysqlmysql 5242880 Feb 15 08:08 ib_logfile1 The above files maybe in slightly different places, it depends on whether you are using the InnoDB engine or the MyISAM one. The InnoDB logging files are the ib_logfiles. You could also check the my.cnf for any form of logging being switched on, in particular binary logging. You could check for this as well. This will be in your my.cnf file as # Replication Master Server (default) # binary logging is required for replication log_bin If so, you could be filling up your disk quite easily Regards Thank you David and Sebastion, I am not doing this in a transaction (at least, I did not do anything special to start a transaction) and I have no need for a transaction. How do I check the log files you two suggest? Thanks, Siegfried -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 03, 2005 8:10 PM To: Siegfried Heintze; mysql@lists.mysql.com Subject: RE: Where did my disk space go? Hi Siegfried, I would check your transaction logs. Are you doing this as one giant transaction? The system may be filling up the logs just in case you need to rollback. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Siegfried Heintze [mailto:[EMAIL PROTECTED] Sent: Thursday, 4 August 2005 10:00 AM To: mysql@lists.mysql.com Subject: Where did my disk space go? I've been using Perl 8.4+ (ActiveState) on WinXP. My program runs for many ( 20) hours issuing SQL UPDATE and DELETE commands. The update commands should not be increasing the storage requirements, I'm just updating integer values. I've noticed several times now that I run out of disk space. I started with a gigabyte free. Last time, I aborted the program, compressed my disk, retrieved much lost disk space and started again. Now I tried that again: no luck. I rebooted and recompressed again. I'm still out of disk space. It seems that MySQL just keeps using more and more disk space. How can I retrieve my lost disks pace? Thanks, Siegfried -- 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] -- MySQL General Mailing List For list archives:
Re: ALTER TRIGGER in MySQL
Hello. I think - yes. There are no plans to implement ALTER TRIGGER command currently. See: http://bugs.mysql.com/bug.php?id=10994 Karam Chand [EMAIL PROTECTED] wrote: Hello, I was reading the docs on Triggers at: http://dev.mysql.com/doc/mysql/en/using-triggers.html Looks like there is no support for Alter Trigger. So if I want to change a trigger, I have to first drop the existing one and recreate another? Am I correct? Karam Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- 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: Show Constraint_type
Hello. I think you should use the SHOW statements: http://dev.mysql.com/doc/mysql/en/show-create-table.html http://dev.mysql.com/doc/mysql/en/show-index.html Nguyen, Phong [EMAIL PROTECTED] wrote: Good morning All, What Mysql command show us CONSTRAINT_TYPE value as UNIQUE, PRIMARY KEY, or FOREIGN KEY of and schema? Thanks.Nguyen. -- 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: MySQL 4.0.23a mysqld defunct
Hello. Are there any error messages and stack trace before 'Memory status' line in the error log? Arek H [EMAIL PROTECTED] wrote: Hi Im running a stock install of Slackware 10.1 with the default setup of mysql. Whats happening is when I hit the reload button several times on a page that uses php and accesses mysql I get mysqld defunct and zombies start showing when viewing top. I also get this when I run a web based apache bench on my server with the Requests set to 25 and Concurrency set to 5. I checked my domains error logs and there is nothing there. I looked in the error log located at /var/lib/mysql/ano.err and here are the last few lines of it. Im new to linux so this is the only error log I could find for mysql . Memory status: Non-mmapped space allocated from system: 5204056 Number of free chunks: 15 Number of fastbin blocks:0 Number of mmapped regions: 11 Space in mmapped regions:22401024 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 4901384 Total free space:302672 Top-most, releasable space: 131176 Estimated memory (with thread stack):27605080 050731 15:57:12 mysqld ended 050731 16:35:00 mysqld started 050731 16:35:02 InnoDB: Started /usr/libexec/mysqld: ready for connections. Version: '4.0.23a' socket: '/var/run/mysql/mysql.sock' port: 0 Source distribution 050731 16:40:59 /usr/libexec/mysqld: Normal shutdown 050731 16:40:59 InnoDB: Starting shutdown... 050731 16:41:00 InnoDB: Shutdown completed 050731 16:41:00 /usr/libexec/mysqld: Shutdown Complete 050731 16:41:00 mysqld ended 050731 16:42:17 mysqld started 050731 16:42:19 InnoDB: Started /usr/libexec/mysqld: ready for connections. Version: '4.0.23a' socket: '/var/run/mysql/mysql.sock' port: 0 Source distribution Also here is what top shows 20797 root 12 0 1060 1060 824 R 1.3 0.2 0:18.60 top 20993 mysql 11 0 000 Z 1.0 0.0 0:00.03 mysqld defunct 20999 mysql 10 0 15612 15m 3020 S 0.6 3.1 0:00.02 mysqld 21007 mysql 11 0 000 Z 0.6 0.0 0:00.02 mysqld defunct 21017 mysql 13 0 15632 15m 3020 S 0.6 3.1 0:00.02 mysqld 1000 mysql 17 0 15592 15m 3020 R 0.3 3.1 0:01.89 mysqld 20991 mysql 9 0 000 Z 0.3 0.0 0:00.01 mysqld defunct 21013 mysql 10 0 15632 15m 3020 S 0.3 3.1 0:00.01 mysqld 21015 mysql 11 0 15632 15m 3020 S 0.3 3.1 0:00.01 mysqld 1 root 8 0 228 228 200 S 0.0 0.0 0:04.86 init 2 root 9 0 000 S 0.0 0.0 0:00.05 keventd 3 root 19 19 000 S 0.0 0.0 0:00.00 ksoftirqd_CPU0 4 root 9 0 000 S 0.0 0.0 0:00.00 kswapd 5 root 9 0 000 S 0.0 0.0 0:00.00 bdflush 6 root 9 0 000 S 0.0 0.0 0:00.02 kupdated 10 root -1 -20 000 S 0.0 0.0 0:00.00 mdrecoveryd 11 root 9 0 000 S 0.0 0.0 0:00.06 kjournald 44 root 9 0 000 S 0.0 0.0 0:00.16 kjournald 45 root 9 0 000 S 0.0 0.0 0:00.26 kjournald Thanks Arek -- 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: Count two kinds of related records?
Hello. What do you think about this: SELECT a.name, COUNT(p.property_id) AS totalcount, SUM( IF(p.status = 'Active' AND p.approval = 'Active', 1, 0)) AS CCOUNT FROM accounts a, properties p WHERE a.account_id = p.account_id GROUP BY a.account_id; The data in tables: mysql select * from accounts; ++--+ | account_id | name | ++--+ | 1 | a1 | | 2 | a2 | ++--+ mysql select * from properties; ++-++--+ | account_id | property_id | status | approval | ++-++--+ | 1 | 1 | Active | Active | | 1 | 2 | Active | Active | | 1 | 3 | not-Active | Active | | 2 | 3 | not-Active | Active | ++-++--+ In the results of the query we see 2 'Active' properties which has a1, as it is in the properties table: mysql SELECT a.name, COUNT(p.property_id) AS totalcount, SUM( IF(p.status = 'Active' AND p.approval = 'Active', 1, 0)) AS CCOUNT FROM accounts a, properties p WHERE a.account_id = p.account_id GROUP BY a.account_id; +--+++ | name | totalcount | CCOUNT | +--+++ | a1 | 3 | 2 | | a2 | 1 | 0 | +--+++ Brian Dunning [EMAIL PROTECTED] wrote: I'm searching a table of people who own properties, and I want to also include the total count of related properties, and the count of related properties whose (status is 'Active' and approval is 'Active'). I've got: select accounts.name, count(properties.property_id) as totalcount from accounts, properties where accounts.account_id=properties.account_id group by accounts.account_id; Works fine. Now I just need to figure out how to add that second count of property records meeting the two conditions. Anyone? -- 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: explain not explaining long running query?
Hello. I have a query that is taking days to complete (not good). If I change Really, not good. What does SHOW PROCESSLIST report about the thread of this query? David Sparks [EMAIL PROTECTED] wrote: I have a query that is taking days to complete (not good). If I change the query so that it selects less rows it runs fast. I ran an explain on both queries and it didn't give any hints as to why the one query is taking days to run. In fact explain knows how many rows each query will examine. Please help explain this behavior to me. Thanks, ds The output of running the queries: mysql select count(*) from msgs where message_id 112000 and message_id 112001; +--+ | count(*) | +--+ |6 | +--+ 1 row in set (0.00 sec) mysql select count(*) from msgs where message_id 112000 and message_id 112111; (running for 2 days now) -%- The output of explain on both queries: mysql explain select count(*) from msgs where message_id 112000 and message_id 112111\G *** 1. row *** id: 1 select_type: SIMPLE table: msgs type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 580 Extra: Using where; Using index 1 row in set (0.00 sec) mysql explain select count(*) from msgs where message_id 112000 and message_id 112001\G *** 1. row *** id: 1 select_type: SIMPLE table: msgs type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 5 Extra: Using where; Using index 1 row in set (0.00 sec) -% The table description: mysql describe messages\G *** 1. row *** Field: message_id Type: double(15,5) unsigned Null: Key: PRI Default: 0.0 Extra: *** 2. row *** Field: abc1 Type: int(10) unsigned Null: Key: Default: 0 Extra: *** 3. row *** Field: r_datetime Type: datetime Null: YES Key: Default: -00-00 00:00:00 Extra: *** 4. row *** Field: abc2 Type: int(10) unsigned Null: YES Key: MUL Default: 0 Extra: *** 5. row *** Field: abc3 Type: int(10) unsigned Null: YES Key: MUL Default: 0 Extra: *** 6. row *** Field: abc4 Type: varchar(255) Null: YES Key: Default: Extra: *** 7. row *** Field: abc5 Type: float Null: YES Key: MUL Default: 0 Extra: *** 8. row *** Field: abc6 Type: int(10) unsigned Null: Key: MUL Default: 0 Extra: *** 9. row *** Field: abc7 Type: int(10) unsigned Null: YES Key: MUL Default: 0 Extra: *** 10. row *** Field: abc8 Type: int(10) unsigned Null: YES Key: MUL Default: 0 Extra: *** 11. row *** Field: abc9 Type: int(10) unsigned Null: YES Key: MUL Default: 0 Extra: *** 12. row *** Field: abc10 Type: int(10) unsigned Null: Key: Default: 0 Extra: *** 13. row *** Field: abc11 Type: int(10) unsigned Null: Key: Default: 0 Extra: 13 rows in set (0.00 sec) -- 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: indexes
Hello. Right, I agree with you for MyISAM tables, however, in my opinion, for InnoDB indexes should be created before populating the table (the link below related to LOAD DATA INFILE statement, but I think, the same applies for INSERT as well): http://lists.mysql.com/mysql/181445 Scott Noyes [EMAIL PROTECTED] wrote: Some sections of the manual seem to indicate that it's better to create indexes after data population, rather than before. See section 7.2.14, Speed of INSERT Statements. The general procedure there is to load the data using LOAD DATA INFILE, and then use myisamchk. This creates the index tree in memory before writing it to disk, which is much faster because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced. (ibid) On 8/4/05, Michael Stassen [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: Question on V5.0.9 and SHOW command
Hello.
I'm using MySQL 5.0.10 and perl-DBD-mysql-2.90.06-alt1 (from ALT Linux).
I didn't find such weird behavior. Can you send the code of your
program. Here is the example of mine which works fine:
#!/usr/bin/perl
#
use strict;
use DBI;
use User::pwent;
use POSIX;
my ($dbh,$sth,$sql,@row,$numFields,$i);
$dbh = DBI-connect
(DBI:mysql:test:localhost;mysql_read_default_file=/home/gleb/mysqls/mysql-debug-5.0.10-beta-linux-i686/my.cnf;.
mysql_read_default_group=client,
root,,
{RaiseError = 1})
or die connecting : $DBI::errstr\n;
$sth = $dbh-prepare(SHOW FIELDS FROM vch);
$sth-execute;
$numFields = $sth-{'NUM_OF_FIELDS'};
my $names = $sth-{'NAME'};
while((@row) = $sth-fetchrow_array () ) {
for($i=0; $i $numFields; $i++)
{
print $$names[$i].: .$row[$i].\n;
}
}
$sth-finish;
$dbh-disconnect;
And the output:
Field: a
Type: varchar(40)
Null: YES
Key:
Default:
Extra:
netscape.net wrote:
Hello,
I have a strange issue. When I issue the command SHOW COLUMNS from TABLE
XYZ I get the usual output.The key field is blank because there is no key on
the field. However, when I issue the command from a PERL script the the
DBI::DBD it returns MUL in the key field. There is no index associated with
this column either it's a straight VARCHAR(40) field.
Anyone have any ideas?
Regards,
George
__
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register
Netscape. Just the Net You Need.
New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to determine right value for max_allowed_packet?
Hello. Are you replicating BLOB or TEXT fields? I think the maximum packet size is correlated with the size of data which is stored in that fields. Sid Lane [EMAIL PROTECTED] wrote: all, I just finshed hosing down a minor (that could have been FAR worse) fire where replication failed with an: Error reading packet from server: Packet too large - increase max_allowed_packet on this server in my error log. I bumped it up from 1M to 4M, restarted mysql (as well as dependant web servers) and replication went back on its merry way and all appears to be well again. my ? is: how do I know how to size this parameter? master slave had been set to the same value (1M). we replicate a fair amount of data (~1GB of binlog/2hr) and have not had any replication errors in the several months since we implimented it so this is a little surprising. FWIW, we're running 4.0.18 on SuSE 9.1. before anyone says it: yes, we're planning on upgrading to 4.1.x but I have to finish an Oracle 10 upgrade 1st. any help would be greatly appreciated... thanks! -- 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: binary log long format
Hello.
I think these lines from sql/mysqld.cc from 4.1.13 distribution shows
that --log-long-format doesn't affect the behavior of MySQL:
{log-long-format, '0',
Log some extra information to update log. Please note that this
option is deprecated; see --log-short-format option.,
0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
^^^
User names are written to the general log.
Jerry Bonner [EMAIL PROTECTED] wrote:
The current documentation states :
Log some extra information to the log files (update log, binary update
log, and slow queries log, whatever log has been activated). For example,
username and timestamp are logged for queries. Before MySQL 4.1, if you
are using --log-slow-queries and --log-long-format, queries that are not
using indexes also are logged to the slow query log. --log-long-format is
deprecated as of MySQL version 4.1, when --log-short-format was
introduced. (Long log format is the default setting since version 4.1.)
Also note that starting with MySQL 4.1, the
--log-queries-not-using-indexes option is available for the purpose of
logging queries that do not use indexes to the slow query log.
I'm using 4.1.13, however, I don't see username or host information for
binary logs, just slow query logs. Am I missing something?
Jerry Bonner
Systems Engineer
CP Telecom
[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]
Re: Problem with datetime values and MyODBC driver
Hello. I was unable to find similar bug in bugs database, but there were several problems with datetime. Some of them were solved by choosing different versions of software. Check if your problem exists on MySQL 4.1.13. Maybe ODBC tracing help you to localize the problem. If you have a reproducible test case you can report a bug. Du$an Pavlica [EMAIL PROTECTED] wrote: Hello, I'm working in C++ Builder 6.0 and I'm trying to create application which could connect through ADO components to different databases using ODBC drivers. If I create TADOQuery object which has datetime parametr and I fill this parametr with valid datetime value then I can see in MYSQL's query log that only date portion is used. But if I use same query object to connect to MS Access then data in MS Access are OK. Win XP, MyODBC 3.51.11, MySQL 4.1.9 Does anybody have any idea how to solve this problem? Thanks in advance Dusan Pavlica -- 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: MySQL vs Plone/Zope/Python
On Thu, 4 Aug 2005 23:36:01 -0700 (PDT) David Blomstrom [EMAIL PROTECTED] wrote: I wondered if anyone on this list has had experience with Plone and could explain how their system compares to PHP/MySQL. I'll be working with animal kingdom data - child-parent relationships and recursive arrays. I wouldn't compare Plone and MySQL. I believe Zope (and hence Plone's) underlying database technology is something called ZODB, you might want to look into that. FWIW, I got caught up in the Zope/Plone bandwagon a year or more ago and it just didn't stick. There are things I find intriguing as well about Zope/Plone, but I've had much better success just installing a Mambo/Drupal site to get a quick CMS. I'm curious to see if Zope 3 makes Zope an attractive product again, but for now I'll just stick with PhP. Not that I think Python is a bad language for web apps, I'm actually starting to like Python more and more. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem in Fulltext Search
Hi,
We have a table containing more than 15 million rows of data, can anybody
please help in this problem of fulltext search described below.
The following query is giving a good result in terms of query time.
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
when I want to search for more that one word or phrase like food, locomotive,
water tank then the query is not working up to the desired level. The following
query is taking not less than 90 to 100 seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('water' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
I have also tried with another version of the same previous query which is also
not giving a desired performance. The following query is taking not less than
90 to 100 seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food locomotive water tank' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
In these cases, I think, I am not getting the advantage of full text search,
because still it is taking a lot of time to execute the query. Can I optimise
the database to few step forward such that the query time can be reduced for
multiple words of phrases, please let us know can the query be optimized to
provide a better performance?
Regards,
Suryya
Re: binary log long format
I realize that.
The documentation states that username and timestamp,
by default, are logged to (update log, binary update log, and slow queries
log, whatever log has been activated). But apparently it isn't.
I was hoping that there was a way to get that information in the binary
log, but it appears more likely the documentation is wrong.
~jerry
On Fri, 5 Aug 2005, Gleb Paharenko wrote:
Hello.
I think these lines from sql/mysqld.cc from 4.1.13 distribution shows
that --log-long-format doesn't affect the behavior of MySQL:
{log-long-format, '0',
Log some extra information to update log. Please note that this
option is deprecated; see --log-short-format option.,
0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
^^^
User names are written to the general log.
Jerry Bonner [EMAIL PROTECTED] wrote:
The current documentation states :
Log some extra information to the log files (update log, binary update
log, and slow queries log, whatever log has been activated). For example,
username and timestamp are logged for queries. Before MySQL 4.1, if you
are using --log-slow-queries and --log-long-format, queries that are not
using indexes also are logged to the slow query log. --log-long-format is
deprecated as of MySQL version 4.1, when --log-short-format was
introduced. (Long log format is the default setting since version 4.1.)
Also note that starting with MySQL 4.1, the
--log-queries-not-using-indexes option is available for the purpose of
logging queries that do not use indexes to the slow query log.
I'm using 4.1.13, however, I don't see username or host information for
binary logs, just slow query logs. Am I missing something?
Jerry Bonner
Systems Engineer
CP Telecom
[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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexes
Sebastian wrote: is it ok to add index after the tables and data are already built, or is it better to create the index before data gets inserted? Michael Stassen wrote: It's probably better to create the indexes up front (assuming you know in advance which ones will be needed), but I think that's a moot point if your table already exists and is full of data. There's no sense starting from scratch when you can simply add the index with ALTER TABLE yourtablename ADD INDEX name (item, type); Scott Noyes wrote: Some sections of the manual seem to indicate that it's better to create indexes after data population, rather than before. See section 7.2.14, Speed of INSERT Statements http://dev.mysql.com/doc/mysql/en/insert-speed.html. The general procedure there is to load the data using LOAD DATA INFILE, and then use myisamchk. This creates the index tree in memory before writing it to disk, which is much faster because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced. (ibid) Sort of. First, this doesn't apply to InnoDB tables. Thus, if your table is MyISAM, and you intend to create the table and fill it with data all at once, it is faster to create the table with indexes, disable them, load the data, then enable the indexes, as described on the page you referenced. Note also that you don't need myisamchk if you use LOAD DATA INFILE to populate an empty table, or you can use ALTER TABLE tablename DISABLE KEYS before inserting and ALTER TABLE tablename ENABLE KEYS after. This trick is also good for bulk inserts later. The basic idea is that building/updating an index is a cost best paid as few times as possible. Best case is to update the index all at once after all data has been inserted. Worst case is to use single row inserts, updating the index after each. The preceding only applies to MyISAM tables which can be filled when created, or where later inserts can be done in bulk. If your table grows as customers use it, however, it's not very helpful, as it makes no sense to disable/enable keys around a single row insert. I intended my reply to be specific to the original poster's issue, existing full table with slow queries, rather than as general advice. I meant that adding indexes in the beginning is better (planning ahead) than adding them down the road when queries are slowing because there aren't any indexes (reacting). I did not mean to make a statement about speed of creating pre filled tables. I agree I was unclear. It also seems I misunderstood the situation, because Sebastian wrote: i am merging about 3 tables (each about 10-15k rows) into one table, so that is why i asked about when i should create the index (before or after) since i will be moving things around. since i have 3 tables which all are similar it is a pain because i have to repeat a lot of code in the app... this way i can just fetch the rows i need by specifying the item and type for the apps. so it's a new table with no data yet which i will dump into from other tables. In that case, Scott's advice is apt. This will go fastest if you follow the directions in the manual page he cites. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem in Fulltext Search
Suryya Ghosh [EMAIL PROTECTED] wrote on 08/05/2005 09:50:27
AM:
Hi,
We have a table containing more than 15 million rows of data, can
anybody please help in this problem of fulltext search described below.
The following query is giving a good result in terms of query time.
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
when I want to search for more that one word or phrase like food,
locomotive, water tank then the query is not working up to the
desired level. The following query is taking not less than 90 to 100
seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('water' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
I have also tried with another version of the same previous query
which is also not giving a desired performance. The following query
is taking not less than 90 to 100 seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food locomotive water tank' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
In these cases, I think, I am not getting the advantage of full text
search, because still it is taking a lot of time to execute the
query. Can I optimise the database to few step forward such that the
query time can be reduced for multiple words of phrases, please let
us know can the query be optimized to provide a better performance?
Regards,
Suryya
One technique to speed up OR condition searches, tested again earlier this
week with reported success, is to split your OR-ed conditions into
UNION-ed queries like this:
(select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
UNION
(select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
UNION
(select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('water' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
ORDER BY field2 DESC LIMIT 500;
This will not provide any kind of relevancy like your second test query
would but it will give you the top 500 records in descending field2 order
that contain any of the search words in field1. Since each part of the
UNION is a simple search, the FT index lookup will be faster so if you can
running just one query in about .5 seconds, the whole thing should execute
in about 3 seconds (to allow extra time for merging, deduplication, and
re-ordering of the final assembled sets of rows)
If you would like to favor records that have more search word hits
(relevancy) then you will need a different query, more like your second
test query.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Can not connect to Mysql server with Number 2003
All, In a few minutes ago, I can connect to mysql. Log out and can not login with error Mysql error Number 2003 can't connect to Mysql server.. Any idea, please help! Thank you..Nguyen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can not connect to Mysql server with Number 2003
Nguyen, Phong wrote: All, In a few minutes ago, I can connect to mysql. Log out and can not login with error Mysql error Number 2003 can't connect to Mysql server.. Check this: http://dev.mysql.com/doc/mysql/en/access-denied.html It is a good idea, for now, to check the error logs, there should be an explanation about how the server crashed Any idea, please help! Thank you..Nguyen -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count two kinds of related records?
That's exactly what I'm looking for, thanks Eugene. :) On Aug 5, 2005, at 12:46 AM, Eugene Kosov wrote: Brian Dunning wrote: I'm searching a table of people who own properties, and I want to also include the total count of related properties, and the count of related properties whose (status is 'Active' and approval is 'Active'). I've got: select accounts.name, count(properties.property_id) as totalcount from accounts, properties where accounts.account_id=properties.account_id group by accounts.account_id; Works fine. Now I just need to figure out how to add that second count of property records meeting the two conditions. Anyone? I think query below should help you. SELECT accounts.name, COUNT(properties.property_id) AS totalcount, SUM(IF(status='Active' AND 'approval='Active', 1, 0)) FROM accounts LEFT JOIN properties USING(account_id) GROUP BY accounts.account_id; Regards, Eugene Kosov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Combining tables
I'm having difficulty writing a query as follows. I'm unsure if I need a subquery, a union or if this isn't actually possible without using temporary tables. I have two tables for members. Table 1 (members) Table 2 (payments) +--++ +-+--+ | memno| group | | memno | payment_type | +--++ +-+--+ |1 | a | | 1 | cash | |2 | b | | 2 | cash | |3 | a | | 3 | creditcard | |4 | a | | 4 | check| |5 | c | | 5 | creditcard | ... ... I'd like a query that returns the total number in each group, together with the number paying by credit card. Obviously I can build two queries, and use a temporary table, but is there a way to get a table like that below in a single query? +--++--+ | group| members | pay_by_card | +--++--+ |a | 5| 3 | |b | 26 | 18 | ... This will be using MySQL 5 if that helps Thanks! Russell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Combining tables
Is this what you're after? SELECT COUNT(*) AS members, SUM(IF(payment_type = 'creditcard', 1, 0)) AS pay_by_card FROM members JOIN payments USING (memno) GROUP BY group; On 8/5/05, Russell Horn [EMAIL PROTECTED] wrote: I'm having difficulty writing a query as follows. I'm unsure if I need a subquery, a union or if this isn't actually possible without using temporary tables. I have two tables for members. Table 1 (members) Table 2 (payments) +--++ +-+--+ | memno| group | | memno | payment_type | +--++ +-+--+ |1 | a | | 1 | cash | |2 | b | | 2 | cash | |3 | a | | 3 | creditcard | |4 | a | | 4 | check| |5 | c | | 5 | creditcard | ... ... I'd like a query that returns the total number in each group, together with the number paying by credit card. Obviously I can build two queries, and use a temporary table, but is there a way to get a table like that below in a single query? +--++--+ | group| members | pay_by_card | +--++--+ |a | 5| 3 | |b | 26 | 18 | ... This will be using MySQL 5 if that helps Thanks! Russell -- 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]
Problem detected this morning.
What is this all about? McAfee caught this problem. It was in \data as ofiqa.dll I couldn't delete it until I stopped the MySQL service. 8/5/2005 8:03:39 AM Deleted NT AUTHORITY\SYSTEM mysqld-nt.exe D:\mysql\data\ofiqa.exe W32/Sdbot.worm.gen.by (Virus) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Combining tables
Russell Horn wrote: I'm having difficulty writing a query as follows. I'm unsure if I need a subquery, a union or if this isn't actually possible without using temporary tables. I have two tables for members. Table 1 (members) Table 2 (payments) +--++ +-+--+ | memno| group | | memno | payment_type | +--++ +-+--+ |1 | a | | 1 | cash | |2 | b | | 2 | cash | |3 | a | | 3 | creditcard | |4 | a | | 4 | check| |5 | c | | 5 | creditcard | .. ... I'd like a query that returns the total number in each group, together with the number paying by credit card. Obviously I can build two queries, and use a temporary table, but is there a way to get a table like that below in a single query? +--++--+ | group| members | pay_by_card | +--++--+ |a | 5| 3 | |b | 26 | 18 | .. something like this should do it : SELECT group, count(*) as members, sum( IF(payment_type='creditcard',1,0)) as pay_by_card FROM members, payments WHERE members.memno=payments.memno -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Combining tables
Thanks for the two incredibly fast responses, they were perfect - problem solved. Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full Table Scan when using Fulltext Match Clause and OR clause together
Hi all.
I have a question I was wondering if someone might be able to help with:
I have a small table containing a full text index on a title and
description fields. Everything was going great and the full text index
was performing well against 25 items (So farm, but set to rise). IE:
mysql explain SELECT ID FROM RESOURCE WHERE MATCH(TITLE) AGAINST
('fred' in Boolean mode);
++-+--+--+---+---+-+--+--+-+
| id | select_type | table| type | possible_keys | key
| key_len | ref | rows | Extra |
++-+--+--+---+---+-+--+--+-+
| 1 | SIMPLE | RESOURCE | fulltext | res_title_idx | res_title_idx
| 0 | |1 | Using where |
++-+--+--+---+---+-+--+--+-+
1 row in set (0.00 sec)
Great!
My main table joins to a subject headings table using a more traditional
string match. For example, I would like to be able to look for all
resources where title or subject heading is fred. I noticed that when I
add in this secondary OR clause, performance really drops off (Up to 3
seconds). I simplified the query and got things down to the following
example which combines a fulltext match with a lookup on primary key:
(Here's the PK just for completeness)
mysql explain SELECT ID FROM RESOURCE WHERE ID = 1;
++-+--+---+---+-+-+---+--+--+
| id | select_type | table| type | possible_keys | key |
key_len | ref | rows | Extra|
++-+--+---+---+-+-+---+--+--+
| 1 | SIMPLE | RESOURCE | const | PRIMARY | PRIMARY |
8 | const |1 | Using where; Using index |
++-+--+---+---+-+-+---+--+--+
mysql explain SELECT ID FROM RESOURCE WHERE MATCH(TITLE) AGAINST
('fred' in Boolean mode) OR ID = 1;
++-+--+--+---+--+-+--+--+-+
| id | select_type | table| type | possible_keys | key | key_len |
ref | rows | Extra |
++-+--+--+---+--+-+--+--+-+
| 1 | SIMPLE | RESOURCE | ALL | PRIMARY | NULL |NULL |
NULL |0 | Using where |
++-+--+--+---+--+-+--+--+-+
1 row in set (0.00 sec)
Do I read this correctly in that type=ALL indicates a full table scan? I
had hoped that the query plan would be a merge of the keys located by
the fulltext query and the keys located by the primary key lookup of
ID=1. If I rewrite this query using a UNION, things start to work well
again, but alas my SQL is auto generated, and it's hard to tune things
like that.
Anyone have any feelings about this.. is it behaving correctly and my
expectations or wrong or might it indicate a minor feature?
Kindest Regards and Many thanks for your time!
Ian.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and HIPAA Compliance?
Hi, I'm taking a database class and we are given open ended discussion questions each week. The question this week is: Will MySQL take away market share from popular DBMSs? Will your comments change if you are told that MySQL is not HIPPAA compliant? I have been using MySQL for well over 3 years, and other databases for well over 10 years (professionally, as a coderbout time I took a class eh?) and I do think I have an informed opinion for the first part. I'm weak in the area of HIPAA compliance though. I know it basically centers around privacy. I know it covers things like adequate logging, encrypted connections, etc, but it also seems to include a lot of EDI interoperability. Now that seems to be something that should be handled at an application level and MySQL shouldn't be penalized because of this. From the searching I have done, it appears that MSSQL for example offers this mandatory feature via their Biztalk server (to handle all the EDI) There are all manner of sites that will discuss HIPAA compliance for a fee. Is anyone here familiar with this that could provide a reference or a simple summary. It seems an interesting and important topic that I thought the list might be interested. Regards, Gary Huntress -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to determine right value for max_allowed_packet?
Hello. I've never heard that big values of max_allowed_packed had produced problems. So usually putting it to big enough values shouldn't break anything in most cases. Please, next time send a copy of your message to the list, more experienced users can give a good advice. we are replicating some text columns (though they are a comparitievly small % of our DML volume). that thought had crossed my mind (Oracle used to blame the ORA-00600s AQ kept coughing up on CLOBs). I guess it's certainly possible we had a high water text value come through yesterday. so does the max_allowed_packet need to be big enough to accomodate a row, a DML statement or a transaction (they're in innodb tables)? -- 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 in Fulltext Search
Hello.
The common solution for similar issues which appeared on the
list not recently was modifying the query to use UNIONs. For
example:
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
can be changed to:
(SELECT field1, field2
FROM tblMerge
WHERE
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
UNION
(SELECT field1, field2
FROM tblMerge
WHERE
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
ORDER BY field2 DESC LIMIT 500
Check if the query with UNION faster than with OR.
Suryya Ghosh [EMAIL PROTECTED] wrote:
Hi,
We have a table containing more than 15 million rows of data, can anybody
please help in
this problem of fulltext search described below.
The following query is giving a good result in terms of query time.
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
when I want to search for more that one word or phrase like food, locomotive,
water tank
then the query is not working up to the desired level. The following query is
taking not
less than 90 to 100 seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('water' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
I have also tried with another version of the same previous query which is
also not giving
a desired performance. The following query is taking not less than 90 to 100
seconds that
is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food locomotive water tank' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
In these cases, I think, I am not getting the advantage of full text search,
because still
it is taking a lot of time to execute the query. Can I optimise the database
to few step
forward such that the query time can be reduced for multiple words of phrases,
please let
us know can the query be optimized to provide a better performance?
Regards,
Suryya
--
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: Can not connect to Mysql server with Number 2003
Hello. See: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html Nguyen, Phong [EMAIL PROTECTED] wrote: All, In a few minutes ago, I can connect to mysql. Log out and can not login with error Mysql error Number 2003 can't connect to Mysql server.. Any idea, please help! Thank you..Nguyen -- 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: binary log long format
Hello. If documentation isn't clear, you may want to report a bug at: http://bugs.mysql.com Jerry Bonner [EMAIL PROTECTED] wrote: I realize that. The documentation states that username and timestamp, by default, are logged to (update log, binary update log, and slow queries log, whatever log has been activated). But apparently it isn't. I was hoping that there was a way to get that information in the binary log, but it appears more likely the documentation is wrong. ~jerry On Fri, 5 Aug 2005, Gleb Paharenko 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: MySQL and HIPAA Compliance?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: Hi, I'm taking a database class and we are given open ended discussion questions each week. The question this week is: Will MySQL take away market share from popular DBMSs? Will your comments change if you are told that MySQL is not HIPPAA compliant? I'm not a HIPPA consultant, nor a lawyer, but have worked in the healthcare industry in the past (before I joined MySQL), so I have _some _ experience, but don't take this as a legal consultation ;) As far as I know, a _Database_Product_ can't be declared HIPPA-compliant, although features _in_ a database can help ease the route to compliance. Medical Records Software in combination with an organization and its processes have to be compliant (and it's based on process just as much as product). For example, besides containing regulations concerning techology for software that deals with artifacts that fall under HIPPA (but does not mandate _which_ technology to use), there are regulations about _physical_ security (i.e. who has access to the file cabinet, the server room, the fax machine, etc), administrative safeguards, as well as codifcation standards (ICD9's and the like, as well as other Portability issues, which is one of the Ps in HIPPA) and any number of regulations that are outside the scope of database and/or middleware software. I have been using MySQL for well over 3 years, and other databases for well over 10 years (professionally, as a coderbout time I took a class eh?) and I do think I have an informed opinion for the first part. I'm weak in the area of HIPAA compliance though. I know it basically centers around privacy. I know it covers things like adequate logging, encrypted connections, etc, but it also seems to include a lot of EDI interoperability. Now that seems to be something that should be handled at an application level and MySQL shouldn't be penalized because of this. From the searching I have done, it appears that MSSQL for example offers this mandatory feature via their Biztalk server (to handle all the EDI) There are all manner of sites that will discuss HIPAA compliance for a fee. Is anyone here familiar with this that could provide a reference or a simple summary. It seems an interesting and important topic that I thought the list might be interested. Try Health and Human Service's Website http://aspe.hhs.gov/admnsimp/bannertx.htm -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC852RtvXNTca6JD8RApRyAKCr7GD00dQ/E/we7uH44eoWKPUuQQCff7O5 fODyR4aoEm4A2JVYSSM+84o= =6Aup -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and HIPAA Compliance?
What a great discussion thread! Gary, I currently use MySQL as part of a HIPAA compliant system for the integration of web-based apps with Patient Care information. HIPAA (Health Information Portability and Accountability Act) is a set of standards set by the US Government to protect people's private health information. Although created in 1996, only recently (April 2005) has it started to really affect the way that health care organizations really needed to worry about it's IT implications. So, let's get to quick and dirty bottom of this: Is MySQL compliant -- NO. Neither is any other RDBMS on the market today. The way that data is stored is not at the issue. It's the way that data is collected that is at the heart of the RDBMS part of HIPAA. You are correct that the Electronic Data Interchange (EDI) and application layers are responsible for the encryption of the data. As long as the data is in a locked, windowless, office (hopefully a server room) with adequate security features (like physical security) and the system is locked with passwords and has virus protection, then the data is HIPAA compliant. What HIPAA was truly designed for was not the storage of data but the PORTABILITY of data. How is the data accessed and who sees it? Data needs to be accessed over a secure connection, either via SSL or other encryption standards (AES, WEP, TLS, etc.) and must have a strong password (minimum 8 characters, alphanumeric, and special characters) in order to decrypt it. That being said, only certain individuals are even allowed to access that data, set up via Active Directory, directory shares, or by access lists. With the web based applications that I use, you must either be inside the network or access it via a 128-bit encrypted VPN. Not only does the data transmission need to be encrypted, but it also needs to be adequately logged as to who sees it, what they were looking at, how long they were there, and what their purpose was. Now the systems that I designed/use were designed with HIPAA in mind, so, save 1 or 2 tables, everything is in integers. Printing out an entire table of data and leaving it in the cafeteria is not an issue as you would see nothing but numbers. Without having the database schema in hand to reference what all the numbers mean, you won't be able to determine anything. Now, I am just a computer jockey with ump-teen years experience under my belt, so don't take what I have written here to be law. However, being the in healthcare field and designing databases with HIPAA compliance in mind has been a huge help. There are lots of sites out there with much more information than I have in my head, so I would seek those out as well. One site, http://www.wpc-edi.com/hipaa/ has all the data that you need straight from the ass's (oops, horse's) mouth of the US Gov't. I have helped in the authoring of a few papers on HIPAA compliance and computers in the healthcare industry that, if this would be of interest to anyone, are available. Please email me directly, as putting them on the list would be unwanted propaganda. I hope this sparks some more discussion from 'the group'. Sincerely, J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, August 05, 2005 12:56 PM To: mysql@lists.mysql.com Subject: MySQL and HIPAA Compliance? Hi, I'm taking a database class and we are given open ended discussion questions each week. The question this week is: Will MySQL take away market share from popular DBMSs? Will your comments change if you are told that MySQL is not HIPPAA compliant? I have been using MySQL for well over 3 years, and other databases for well over 10 years (professionally, as a coderbout time I took a class eh?) and I do think I have an informed opinion for the first part. I'm weak in the area of HIPAA compliance though. I know it basically centers around privacy. I know it covers things like adequate logging, encrypted connections, etc, but it also seems to include a lot of EDI interoperability. Now that seems to be something that should be handled at an application level and MySQL shouldn't be penalized because of this. From the searching I have done, it appears that MSSQL for example offers this mandatory feature via their Biztalk server (to handle all the EDI) There are all manner of sites that will discuss HIPAA compliance for a fee. Is anyone here familiar with this that could provide a reference or a simple summary. It seems an interesting and important topic that I thought the list might be interested. Regards, Gary Huntress -- 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]
Number of SQL Queries curiosity
We have a relatively small web site running 4.0.20 on a Dell 2850 running RedHat. We rarely see any SQL statements visible when we run SHOW PROCESSLIST and typically use 30 - 40 concurrent connections. The Number of SQL Queries graph in MySQL Administrator usually is in the 0 to 10 range with occaisional spikes to 40 - 50. Lately I have been noticing the Max value in the Number of SQL Queries setting at 2,000 +. This happens maybe once or twice a day and I have only been looking at the display when it happened 1 time. There does not seem to be any unusal difference in the rest of the graphs, page hits on the web site, network traffic etc. Nothing seems to be impacted when this activity occurs. I just don't understand what could cause this kind of activity on the server with our web site usage profile. We do have some people using access through ODBC, but I have not been able to recreate the event. Does anyone have any ideas on what could cause this? Shouild I be concerned?
RE: MySQL and HIPAA Compliance?
I just completed designing a site for a dentist that had to be HIPAA compliant. To see how I structured it take a look at www.brianwilliamsdds.com. Keith D. Holler Owner/Senior Network Engineer AZCAPPY Network Services www.azcappy.com 623-931-0809 623-321-8177 Fax -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: Friday, August 05, 2005 10:29 AM To: mysql@lists.mysql.com Subject: RE: MySQL and HIPAA Compliance? What a great discussion thread! Gary, I currently use MySQL as part of a HIPAA compliant system for the integration of web-based apps with Patient Care information. HIPAA (Health Information Portability and Accountability Act) is a set of standards set by the US Government to protect people's private health information. Although created in 1996, only recently (April 2005) has it started to really affect the way that health care organizations really needed to worry about it's IT implications. So, let's get to quick and dirty bottom of this: Is MySQL compliant -- NO. Neither is any other RDBMS on the market today. The way that data is stored is not at the issue. It's the way that data is collected that is at the heart of the RDBMS part of HIPAA. You are correct that the Electronic Data Interchange (EDI) and application layers are responsible for the encryption of the data. As long as the data is in a locked, windowless, office (hopefully a server room) with adequate security features (like physical security) and the system is locked with passwords and has virus protection, then the data is HIPAA compliant. What HIPAA was truly designed for was not the storage of data but the PORTABILITY of data. How is the data accessed and who sees it? Data needs to be accessed over a secure connection, either via SSL or other encryption standards (AES, WEP, TLS, etc.) and must have a strong password (minimum 8 characters, alphanumeric, and special characters) in order to decrypt it. That being said, only certain individuals are even allowed to access that data, set up via Active Directory, directory shares, or by access lists. With the web based applications that I use, you must either be inside the network or access it via a 128-bit encrypted VPN. Not only does the data transmission need to be encrypted, but it also needs to be adequately logged as to who sees it, what they were looking at, how long they were there, and what their purpose was. Now the systems that I designed/use were designed with HIPAA in mind, so, save 1 or 2 tables, everything is in integers. Printing out an entire table of data and leaving it in the cafeteria is not an issue as you would see nothing but numbers. Without having the database schema in hand to reference what all the numbers mean, you won't be able to determine anything. Now, I am just a computer jockey with ump-teen years experience under my belt, so don't take what I have written here to be law. However, being the in healthcare field and designing databases with HIPAA compliance in mind has been a huge help. There are lots of sites out there with much more information than I have in my head, so I would seek those out as well. One site, http://www.wpc-edi.com/hipaa/ has all the data that you need straight from the ass's (oops, horse's) mouth of the US Gov't. I have helped in the authoring of a few papers on HIPAA compliance and computers in the healthcare industry that, if this would be of interest to anyone, are available. Please email me directly, as putting them on the list would be unwanted propaganda. I hope this sparks some more discussion from 'the group'. Sincerely, J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, August 05, 2005 12:56 PM To: mysql@lists.mysql.com Subject: MySQL and HIPAA Compliance? Hi, I'm taking a database class and we are given open ended discussion questions each week. The question this week is: Will MySQL take away market share from popular DBMSs? Will your comments change if you are told that MySQL is not HIPPAA compliant? I have been using MySQL for well over 3 years, and other databases for well over 10 years (professionally, as a coderbout time I took a class eh?) and I do think I have an informed opinion for the first part. I'm weak in the area of HIPAA compliance though. I know it basically centers around privacy. I know it covers things like adequate logging, encrypted connections, etc, but it also seems to include a lot of EDI interoperability. Now that seems to be something that should be handled at an application level and MySQL shouldn't be penalized because of this. From the searching I have done, it appears that MSSQL for example offers this mandatory feature via their Biztalk server (to handle all the EDI) There are all manner of sites that will discuss HIPAA compliance for a fee. Is anyone here familiar with this that could provide a reference or a simple summary. It seems an interesting and important topic
top one row
I have a simple table col1 col2col3 A 2 3 A 100 70 A 100080 B20 90 B7080 To select the top one row for each unique value of col1 select distinct on (col1), col1, col2, col3 from table order by col1, col2 desc, col3 desc; What I want is A 1000 80 B 70 80 How do you do it in mysql? Kemin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: top one row
You're looking for the rows containing the group-wise maximum. There's an explanation in the manual, section 3.6.4, http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html On 8/5/05, Kemin Zhou [EMAIL PROTECTED] wrote: I have a simple table col1 col2col3 A 2 3 A 100 70 A 100080 B20 90 B7080 To select the top one row for each unique value of col1 select distinct on (col1), col1, col2, col3 from table order by col1, col2 desc, col3 desc; What I want is A 1000 80 B 70 80 How do you do it in mysql? Kemin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and HIPAA Compliance?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mark Matthews wrote: [snip] For example, besides containing regulations concerning techology for software that deals with artifacts that fall under HIPPA (but does not mandate _which_ technology to use), there are regulations about _physical_ security (i.e. who has access to the file cabinet, the server room, the fax machine, etc), administrative safeguards, as well as codifcation standards (ICD9's and the like, as well as other Portability issues, which is one of the Ps in HIPPA) and any number [snip] Sorry, that should've been _the_ P in _HIPAA_ ;) Finally managed to dig up the technology requirements from HHS at http://www.cms.hhs.gov/hipaa/hipaa2/regulations/security/nprm/sec14.asp And of course, given these, a solution built on top of MySQL _can_ be HIPAA-compliant (given that you meet the non-technology-related regulations as well, which are probably harder to deal with). Of course, these requirements apply to the system as a _whole_, but MySQL by itself also meets the requirements. Requirement -- Access control (The following implementation feature must be implemented: Procedure for emergency access. In addition, at least one of the following three implementation features must be implemented: Context-based access, Role-based access, User-based access. The use of Encryption is optional). There's emergency-based access, as well as user-based access in MySQL. One can also, if they so desire use encryption. Audit controls You can enable MySQL to create audit logs, with recording every query issued by any user the time at which that query happened. Authorization control (At least one of the listed implementation features must be implemented). Role-based access. User-based access. MySQL has User-based access. Data Authentication (HIPAA's definition is The corroboration that data has not been altered or destroyed in an unauthorized manner. Examples of how data corroboration may be assured include the use of a check sum, double keying, a message authentication code, or digital signature.) MySQL has cryptographic hashing functionality which meets this requirement. Entity authentication (The following implementation features must be implemented: Automatic logoff, Unique user identification. In addition, at least one of the other listed implementation features must be implemented). Automatic logoff. Biometric. Password. PIN. Telephone callback. Token. Unique user identification. MySQL has automatic log-off capabilities (connection timeouts), as well as unique user identification, and has passwords. -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC86TDtvXNTca6JD8RAsvJAJ9Mhl9tiXFzpoZmocmVRmXXrxUClQCfZH2D wXzjU3u4oAhicyenewPE2Z8= =QGJH -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: top one row
[snip] I have a simple table col1 col2col3 A 2 3 A 100 70 A 100080 B20 90 B7080 To select the top one row for each unique value of col1 select distinct on (col1), col1, col2, col3 from table order by col1, col2 desc, col3 desc; What I want is A 1000 80 B 70 80 How do you do it in mysql? [/snip] The same way you do it inother SQL's. SELECT MAX(col2) FROM table GROUP BY col1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and HIPAA Compliance?
On Fri, Aug 05, 2005 at 01:29:19PM -0400, J.R. Bullington wrote: The way that data is stored is not at the issue. It's the way that data is collected that is at the heart of the RDBMS part of HIPAA. I once interned for a major vendor of HIPAA-compliant hospital IT solutions, doing software quality assurance on their internal products. I was responsible for testing two of the most HIPAA-relevant pieces of inftastructure in the whole architechture: 1) User authentication: HIPPA compliance requires that someone be explicity authorized to view data before they can view it. We had a reasonably sophisticated token system that was implemented for every API. In addition to a function's parameters, it also needed it's security token. That security token was based on the user that had logged in and what they were attempting to view. Security tokens would be assigned to users and stored in an isolated database (we had Oracle, MS SQL, and DB2 modules, though there's no reason MySQL couldn't be supported). A security administrator would use a tool to assign the proper permissions to their users. This would literally define what APIs the user had access to (similar to Java's security policy, but with a database backend and even finer grained control). 2) Audit logging: Every time an authenticated user retrieved some piece of data, we had to create an audit record that could prove they did it. This is the Accountability part of HIPAA. An auditing administrator would configure the auditing properties, which gets stored in another isolated database. Anytime an authenticated user does something that the auditing administrator has decided is significant - boom, a record gets written to record the fact. You'll notice that in both cases, the HIPPA-ness is implemented on a higher level than the actual database. Like JR said, as long as the server is secured, there's no problem with that implementation. Having said that, we all laughed at the idea of running our software in a production environment on anything but a DB2 mainframe. These two pieces are quite a heavy load on the databases - just imagine how many audit records can be generated at a large busy hospital! We had a test load that could easily bog down our database servers. Of course it was contrived, and our testing budget prevented us from having serious hardware to test with, but it always boggled my mind to think of all the audit records that would be stored somewhere (and probably never looked at ::-). Now the systems that I designed/use were designed with HIPAA in mind, so, save 1 or 2 tables, everything is in integers. Printing out an entire table of data and leaving it in the cafeteria is not an issue as you would see nothing but numbers. Without having the database schema in hand to reference what all the numbers mean, you won't be able to determine anything. That's a clever idea! But didn't you have to store personal information at somepoint? I guess you could do a clever encoding scheme to map a name and address to a very large integer, but that seems... not much better. How did you handle that issue? -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: top one row
[snip] The same way you do it inother SQL's. SELECT MAX(col2) FROM table GROUP BY col1; [/snip] Oops; SELECT col1, MAX(col2), col3 FROM table GROUP BY col1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Querying value of lower_case_table_names
Hi, Is there any way to check value of of 'lower_case_table_names'? I need to an equivalent of (MS Sql Server statement) exec sp_server_info @attribute_id=16 Thank you, Sunil Vishwas
Re: top one row
SELECT col1, MAX(col2), col3 FROM table GROUP BY col1; Because col3 is not part of the GROUP BY clause, this query will not guarantee that the col3 returned is associated with the col2 returned. In other words, running this query on the table containing col1 / col2 / col3 1 11 1 23 may return 1 21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: top one row
[snip] SELECT col1, MAX(col2), col3 FROM table GROUP BY col1; Because col3 is not part of the GROUP BY clause, this query will not guarantee that the col3 returned is associated with the col2 returned. In other words, running this query on the table containing col1 / col2 / col3 1 11 1 23 may return 1 21 [/snip] I have tested this several times and never got those kind of results, do you have some docs? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: top one row
mysql SELECT VERSION(); +---+ | VERSION() | +---+ | 4.1.7-nt | +---+ 1 row in set (0.00 sec) mysql CREATE TABLE test (col1 INT, col2 INT, col3 INT); Query OK, 0 rows affected (0.08 sec) mysql INSERT INTO test VALUES (1, 1, 1), (1, 2, 3); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT * FROM test; +--+--+--+ | col1 | col2 | col3 | +--+--+--+ |1 |1 |1 | |1 |2 |3 | +--+--+--+ 2 rows in set (0.00 sec) mysql SELECT col1, MAX(col2), col3 FROM test GROUP BY col1; +--+---+--+ | col1 | MAX(col2) | col3 | +--+---+--+ |1 | 2 |1 | +--+---+--+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT
Hello list, I'm getting the distinct 'params' columns from a table with this query SELECT DISTINCT params FROM table; but this gets the rows with distinct 'params' cols, but I want to know from which row each params correspond, like this (which is not correct) SELECT id, DISTINCT params FROM table; and the query based in a recent post doesn't work select distinct on (params), params, id from table order by params, id I checked the syntax and the later isn't correct. Any ideas? -- Nuno Pereira Estagiário Carclasse - Comércio Automóveis, S.A. Lugar Sr. dos Perdões - Ribeirão (Famalicão) Telf.: 252 330 550 - Tlm: 965 215 076 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT
Nuno Pereira [EMAIL PROTECTED] wrote on 08/05/2005 02:04:35 PM: Hello list, I'm getting the distinct 'params' columns from a table with this query SELECT DISTINCT params FROM table; but this gets the rows with distinct 'params' cols, but I want to know from which row each params correspond, like this (which is not correct) SELECT id, DISTINCT params FROM table; and the query based in a recent post doesn't work select distinct on (params), params, id from table order by params, id I checked the syntax and the later isn't correct. Any ideas? -- Nuno Pereira Estagiário Carclasse - Comércio Automóveis, S.A. Lugar Sr. dos Perdões - Ribeirão (Famalicão) Telf.: 252 330 550 - Tlm: 965 215 076 email: [EMAIL PROTECTED] Depending on what version server you are running, the GROUP_CONCAT() function may be an option for you: SELECT params, GROUP_CONCAT(ID) FROM table GROUP BY params; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: explain not explaining long running query?
Hi David, all! David Sparks wrote: I have a query that is taking days to complete (not good). If I change the query so that it selects less rows it runs fast. I fear I have no decisive hint for this (sorry), but still ... [[...]] mysql select count(*) from msgs where message_id 112000 and message_id 112111; [[...]] Have you considered using BETWEEN? IMNSHO, this will both improve the readability of your SQL statement and at the same time help the optimizer (and avoid typing errors!). Yes, logically it is equivalent, but in C you also write len++; or len+= 1; and not len= len + 1; Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT
Here's one (not very clean, but it works) way to do it: SELECT id, params FROM table GROUP BY params; The trouble is, how do you know which id should come with it? If you table is id param 1 1 2 1 should the query return 1, 1 or 2, 1 ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and HIPAA Compliance?
Now see, if I gave that away, I wouldn't have interested you, now would I? As with all databases, you need to collect some kind of identifying information. That's what the save 1 or 2 tables statement was about. Take this simple system for example. Table 1 ... UserID UserNameFirst_Name Last_Name 1 logmeon J.R.Bullington Table 2 ... ID1 UserID SitePassword 1 1 145 12345 Table 3 ... SiteID SiteNameSiteLoc 145 HomeSomePlace USA And so on and so forth... Out of the information above, let's print some... If I printed 2 and 3, what could you do with it? Nothing, since the FK is not the same as PK. Now, we database people know what we could do, but not the layman. If I printed 1 and 3, what could you do with it? Nothing, no references... If I printed 1 and 2, a little more could be done, because you have username and password, but the rest of the data is unrecognizable. Now here is the real fun... Take away the table names, add 5000 records to each table, and shuffle them. That's the typical database size in a medium-sized clinic. Since you wouldn't actually print all 5000 records onto paper, you would only print what you wanted to see, how could you figure it out? Rhetorical question as you couldn't without the database schema in hand to relate back to. Only 1 table out of the ubound(Table(x)) that I could have created (only 3 shown) have personally identifiable data in them. No one but the administrator and/or data entry person(s) has/ve access to that one table. Hence... HIPAA compliance. Welcome to my world... J.R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, August 05, 2005 1:46 PM To: J.R. Bullington Cc: mysql@lists.mysql.com Subject: Re: MySQL and HIPAA Compliance? Now the systems that I designed/use were designed with HIPAA in mind, so, save 1 or 2 tables, everything is in integers. Printing out an entire table of data and leaving it in the cafeteria is not an issue as you would see nothing but numbers. Without having the database schema in hand to reference what all the numbers mean, you won't be able to determine anything. That's a clever idea! But didn't you have to store personal information at somepoint? I guess you could do a clever encoding scheme to map a name and address to a very large integer, but that seems... not much better. How did you handle that issue? -- Ross Vandegrift [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT
[EMAIL PROTECTED] wrote: Depending on what version server you are running, the GROUP_CONCAT() function may be an option for you: SELECT params, GROUP_CONCAT(ID) FROM table GROUP BY params; I'm using 4.1.10a-standard-log Server version, and this is just what I wanted. Thanks. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: top one row
This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; +---+--+ + | Continent | Country | Population | +---+--+ + | Asia | China| 1277558000 | | North America | United States| 278357000 | | South America | Brazil | 170115000 | | Europe| Russian Federation | 146934000 | | Africa| Nigeria | 111506000 | | Oceania | Australia| 18886000 | | Antarctica| South Georgia and the South Sandwich Islands | 0 | +---+--+ It looks ugly but what you have to do is tie the data you want together and let the max work on the collection and then split it back out again in the display. So in your case SELECT col1, LEFT(MAX(CONCAT(LPAD(col2,10,'0'),col3)),10) + 0 AS col2, MID((MAX(CONCAT(LPAD(col2,10,'0'),col3)),11,50) + 0 As col3 FROM sample GROUP BY col1 -Original Message- From: Kemin Zhou [mailto:[EMAIL PROTECTED] Sent: Friday, August 05, 2005 12:38 PM To: mysql@lists.mysql.com Subject: top one row I have a simple table col1 col2col3 A 2 3 A 100 70 A 100080 B20 90 B7080 To select the top one row for each unique value of col1 select distinct on (col1), col1, col2, col3 from table order by col1, col2 desc, col3 desc; What I want is A 1000 80 B 70 80 How do you do it in mysql? Kemin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT
Scott Noyes wrote: Here's one (not very clean, but it works) way to do it: SELECT id, params FROM table GROUP BY params; The trouble is, how do you know which id should come with it? If you table is id param 1 1 2 1 should the query return 1, 1 or 2, 1 ? This is not really what I want, the reply from Shawn Green is just what I want. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: explain not explaining long running query?
Joerg Bruehe [EMAIL PROTECTED] wrote on 08/05/2005 02:08:35 PM: Hi David, all! David Sparks wrote: I have a query that is taking days to complete (not good). If I change the query so that it selects less rows it runs fast. I fear I have no decisive hint for this (sorry), but still ... [[...]] mysql select count(*) from msgs where message_id 112000 and message_id 112111; [[...]] Have you considered using BETWEEN? IMNSHO, this will both improve the readability of your SQL statement and at the same time help the optimizer (and avoid typing errors!). Yes, logically it is equivalent, but in C you also write len++; or len+= 1; and not len= len + 1; Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 Actually, no, he cannot substitute BETWEEN as BETWEEN is _inclusive_ of the boundary conditions (a closed interval). In this case he does not wasnt values equal to his boundaries (an open interval) WHERE X BETWEEN A and B Is equivalent to WHERE X=A and X=B His original WHERE clause was comparing XA and XB (no equality). The two are close but not the same. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
UPDATE WITH row
Following the SELECT DISTINCT topic, I have another question. I tryed to UPDATE all the tables, replacing one column in every tables with the contents of one specific row. I tried this, but the syntax is incorrect: UPDATE int_contxtd_details SET params=(SELECT params FROM int_contxtd_details WHERE id=35) WHERE id!=35; Can anyone help? -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE WITH row
Nuno Pereira [EMAIL PROTECTED] wrote on 08/05/2005 02:41:45 PM: Following the SELECT DISTINCT topic, I have another question. I tryed to UPDATE all the tables, replacing one column in every tables with the contents of one specific row. I tried this, but the syntax is incorrect: UPDATE int_contxtd_details SET params=(SELECT params FROM int_contxtd_details WHERE id=35) WHERE id!=35; Can anyone help? -- Nuno Pereira I think you want to use a variable... SELECT @NewValue := params FROM int_contxtd_details WHERE id=35; UPDATE int_contxtd_details SET params = @NewValue; In this case, even if you overwrite the value where id=35, you will be giving back the same value it started with so no harm. Running the UPDATE statement without a WHERE clause will be faster because it won't have to check ID values for every row to make sure it's OK to update that row. Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS. The other way I could think to write this query was with a self-join but I don't think it would be as fast as using the variable.
Re: explain not explaining long running query?
Hi Shawn, all! [EMAIL PROTECTED] wrote: Joerg Bruehe [EMAIL PROTECTED] wrote on 08/05/2005 02:08:35 PM: [[...]] Have you considered using BETWEEN? [[...]] Actually, no, he cannot substitute BETWEEN as BETWEEN is _inclusive_ of the boundary conditions (a closed interval). In this case he does not wasnt values equal to his boundaries (an open interval) WHERE X BETWEEN A and B Is equivalent to WHERE X=A and X=B His original WHERE clause was comparing XA and XB (no equality). The two are close but not the same. Shame on me that I missed that - sorry! Good that you spotted it. (I know between is inclusive, I just did not check the comparison operators used.) Still, I would consider to replace X A AND X B by X BETWEEN (A+1) AND (B-1) for the reasons I gave in my original post (provided we talk about integral values). Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: explain not explaining long running query?
On Fri, 5 Aug 2005, Joerg Bruehe wrote: Hi Shawn, all! Still, I would consider to replace X A AND X B by X BETWEEN (A+1) AND (B-1) for the reasons I gave in my original post (provided we talk about integral values). the field is a DOUBLE, so you can't could this also explain why so slow? -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE WITH row
[EMAIL PROTECTED] wrote: Nuno Pereira [EMAIL PROTECTED] wrote on 08/05/2005 02:41:45 PM: Following the SELECT DISTINCT topic, I have another question. I tryed to UPDATE all the tables, replacing one column in every tables with the contents of one specific row. I tried this, but the syntax is incorrect: UPDATE int_contxtd_details SET params=(SELECT params FROM int_contxtd_details WHERE id=35) WHERE id!=35; Can anyone help? -- Nuno Pereira I think you want to use a variable... SELECT @NewValue := params FROM int_contxtd_details WHERE id=35; UPDATE int_contxtd_details SET params = @NewValue; It works. I remembered to use the variable, SET @right_params=SELECT @NewValue := params FROM int_contxtd_details WHERE id=35; but id didn't work, because the syntax was incorrect. In this case, even if you overwrite the value where id=35, you will be giving back the same value it started with so no harm. Running the UPDATE statement without a WHERE clause will be faster because it won't have to check ID values for every row to make sure it's OK to update that row. Make sense? Yes, thanks. PS. The other way I could think to write this query was with a self-join but I don't think it would be as fast as using the variable. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.23a mysqld defunct
Gleb Paharenko wrote: Hello. Are there any error messages and stack trace before 'Memory status' line in the error log? This is what it shows Status information: Current dir: /var/lib/mysql/ Running threads: 0 Stack size: 196608 Current locks: key_cache status: blocks used:11 not flushed: 0 w_requests: 0 writes: 0 r_requests: 0 reads: 0 handler status: read_key:0 read_next: 0 read_rnd 0 read_first: 0 write: 0 delete 0 update: 0 Table status: Opened tables: 0 Open tables:0 Open files: 0 Open streams: 0 Alarm status: Active alarms: 0 Max used alarms: 1 Next alarm time: 0 050731 15:57:09 /usr/libexec/mysqld: Normal shutdown Regards Arek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple database backup solution
I'm in need of a hotbackup solution that supports multiple instances on the the same server. Currently, I'm dumping the databases nightly and would like to know if anyone know of an alternative. I've tested innodb, but have not had any success getting it to work with more than one group(mysqld[GNR]) in my my.cnf file. Any advise would be greatly appreciated. Thanks in advance. *** CL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions in Java - JDBC
Hello, Can anyone tell me what to do in order to use transactions on a java application? Is there any howto regarding this issu? Thanks, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNION - different result when statements interchanged
I have a UNION whose statements when interchanged gives a different result. I can understand the change in the order of the rows, but how is it that 'picture' gets the correct value in (a) but not in (b)? Here are the two queries and their results: (a) (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE te.person_id = u.uId AND te.tId IN (1) LIMIT 5) UNION (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE te.person_id = a.person_id AND te.tId IN (1) LIMIT 5); last_name picture Kapoor avataar02.png from table u Manni 0 from table u Office of Technology Assessment 0 from table a Queue Readers 0 from table a Milnes 0 from table a (b) (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE te.person_id = a.person_id AND te.tId IN (1) LIMIT 5) UNION (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE te.person_id = u.uId AND te.tId IN (1) LIMIT 5); last_name picture Office of Technology Assessment 0 from table a Queue Readers 0 from table a Milnes 0 from table a Kapoor 0 from table u Manni 0 from table u Thanks for your help, Nishi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 questions on Auto Increment fields
Hello all, I have two question on Auto Increment fields: 1) For what I see on the MySQL manual you can have an Auto Increment not null Field as the primary key. Say that you have a table with only two columns the first being an Auto_Increment. How do I write an INSERT or REPLACE SQL statement to insert data on this table? Can I write it so that it will only insert a value for the second column or do I have to specify NULL on the column on my statement so that MySQL would fill it up for me. 2) After the INSERT or REPLACE statement runs, can I call something (hopefully on the same statement) that would return the value filled on the Auto Increment field? Thanks in advance, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNION - different result when statements interchanged
From http://dev.mysql.com/doc/mysql/en/union.html : Before MySQL 4.1.1, a limitation of UNION is that only the values from the first SELECT are used to determine result column types and lengths. This could result in value truncation if, for example, the first SELECT retrieves shorter values than the second SELECT: You can either update to version 4.1.1 or later, when the problem was solved, or make sure that the first SELECT retrieves columns that are wide enough and character types. On 8/5/05, Kapoor, Nishikant [EMAIL PROTECTED] wrote: I have a UNION whose statements when interchanged gives a different result. I can understand the change in the order of the rows, but how is it that 'picture' gets the correct value in (a) but not in (b)? Here are the two queries and their results: (a) (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE te.person_id = u.uId AND te.tId IN (1) LIMIT 5) UNION (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE te.person_id = a.person_id AND te.tId IN (1) LIMIT 5); last_name picture Kapoor avataar02.png from table u Manni 0 from table u Office of Technology Assessment 0 from table a Queue Readers 0 from table a Milnes 0 from table a (b) (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE te.person_id = a.person_id AND te.tId IN (1) LIMIT 5) UNION (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE te.person_id = u.uId AND te.tId IN (1) LIMIT 5); last_name picture Office of Technology Assessment 0 from table a Queue Readers 0 from table a Milnes 0 from table a Kapoor 0 from table u Manni 0 from table u Thanks for your help, Nishi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 questions on Auto Increment fields
1) For what I see on the MySQL manual you can have an Auto Increment not null Field as the primary key. Say that you have a table with only two columns the first being an Auto_Increment. How do I write an INSERT or REPLACE SQL statement to insert data on this table? Can I write it so that it will only insert a value for the second column or do I have to specify NULL on the column on my statement so that MySQL would fill it up for me. The following all work exactly the same, assuming the setup you have described. INSERT INTO theTable (theSecondField) VALUES (theSecondValue); INSERT INTO theTable (theAutoField, theSecondField) VALUES (NULL, theSecondValue); INSERT INTO theTable VALUES (NULL, theSecondValue); 2) After the INSERT or REPLACE statement runs, can I call something (hopefully on the same statement) that would return the value filled on the Auto Increment field? SELECT LAST_INSERT_ID(); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
olap
Does OLAP work with MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
