resources for tuning
Hi, We just upgraded our server's RAM from 1 GB to 5 GB - I'd like to now make some adjustments in my.cnf to better tune my server to this new memory amount. I was looking in the MySQL 4.1.21 source dir's support-files at the example my.cnf files provided with the distribution, but these seem horribly out-dated. (The my-large.cnf assumes you have a whopping 512 mb of memory). I was thinking of modelling my.cnf after the provided my-innodb-heavy.cnf file, but I am worried these files might be out-dated so I figured I'd ping the mailing list for other resources for tuning? Thanks, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Plus (oracle) and SQL (mysql)
On Mon, 31 Jul 2006 06:26:01 -0700 (PDT) Phong Nguyen [EMAIL PROTECTED] wrote: My question is if we decide to switch server 1 to server 2 (oracle database to mysql) or server 2 to server1 (mysql to oracle). Then, what happen to sqlplus (oracle) and sql (mysql) using with application. You can say what are # between sqlplus (oracle) and sql (mysql) Well, sqlplus is just an Oracle client program that lets you run SQL queries against the Oracle db. I think what you are really after is what the differences between Oracle SQL and MySQL SQL are. A couple things come to mind: 1.) For CREATE TABLE, Oracle uses VARCHAR2, MySQL has VARCHAR 2.) For MySQL you need to create your tables as InnoDB tables if you are using transactions or foriegn keys (which I hope you are for a production application) 3.) MySQL does not support Oracle's notion of Sequences - in MySQL you do have AUTO_INCREMENT columns, but they are not as robust as sequences. 4.) MySQL does not support CHECK constraints inside CREATE TABLE clauses. I'm sure there are others, but as long as your application uses pretty generic SQL and you are not getting into Oracle-specific stuff you should be ok. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Status of OUTER JOIN bug 1591?
This is referencing an old thread in the mailing list: http://archives.neohapsis.com/archives/mysql/2004-q3/4484.html I'm trying to write a justification for upgrading to MySQL 5.0 and I know it fixed some issues with LEFT/RIGHT joins. In particular I thought it fixed bugs 1591, 1677 and 3765. But bug 1591 is still listed as To be fixed later. Is that correct? If it is is there a different bug against how MySQL 4 handled OUTER JOINS which was fixed in MySQL 5 that I can reference in my justification letter? Thanks, Josh Trutwin FWIW - here is an off-list conversation with another list member regarding this issue: Josh Trutwin [EMAIL PROTECTED] wrote on 12/20/2005 05:45:04 PM: Josh Trutwin wrote: [EMAIL PROTECTED] wrote: Josh Trutwin [EMAIL PROTECTED] wrote on 09/20/2004 10:41:46 PM: On Mon, 20 Sep 2004 10:25:16 -0400 [EMAIL PROTECTED] wrote: I think you missed my point. I think the 5.0.1 behavior was correct and the others are wrong. There is a known bug (or two) about mixing outer joins and inner joins and it looks like it may be fixed. IF you want to see all of the students THAT TABLE (students) needs to be on the LEFT side of a LEFT JOIN or the RIGHT side of a RIGHT JOIN. That's what the directions mean Interesting - do you have a link to more information on this bug? http://bugs.mysql.com/1677 and http://bugs.mysql.com/1591 and http://bugs.mysql.com/3765 Shawn, - a while back you helped me debug some MySQL Join queries that didn't work after upgrading from 4.0 to 5.0 alpha. The bugs are referenced in the links above. Do you know why these bugs are still listed as To be fixed later in the bug database? I am trying to draft a document for persuading someone to support MySQL 5.0 in their software and wanted to use this example as a major fix made in the database server. Thread reference: http://archives.neohapsis.com/archives/mysql/2004-q3/4484.html Thanks, Josh I don't know why they are not fixed. It may be that they are but nobody has gone back to test the new algorithms against them. Mixing left joins and right joins are problematic anyway. There also isn't a FULL OUTER JOIN, yet either. I was trying to find a workaround for the lack of FULL OUTER JOIN when I tried a query that included both LEFT and RIGHT joins and noticed that it didn't quite mesh. That's how I found out about the bugs I told you about. There have been 16 sets of changes since 5.0.1 so any one of them may have fixed it but the regression tests just haven't picked it up. http://dev.mysql.com/doc/refman/5.0/en/news.html 99.9% of the queries out there do not mix LEFT and RIGHT joins and there are other ways to work around the lack of a FULL OUTER JOIN so that probably explains why the fixes are low on the priority list. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reason for Auto-increment primary keys?
Kenneth Wagner wrote: Speed. Especially where related files are concerned. Foreign keys. Links on integer fields are faster, smaller and more efficient. Keys remain smaller and faster. This in my mind is one of the biggest reasons to use an AUTO_INCREMENT column as a primary key when other columns would work. If you have a table that will act as a parent in a parent/child relationship and you've identified a composite (more than one column) PK as: col1 VARCHAR(25) col2 VARCHAR(30) Then the child table would need to have a copy of both columns posted to setup a composite foriegn key: CREATE TABLE child ( child_id INT AUTO_INCREMENT, col1 VARCHAR(25) NOT NULL, col2 VARCHAR(30) NOT NULL, INDEX fk_ind (col1, col2), FOREIGN KEY (col1, col2) REFERENCES parent(col1,col2) ON DELETE... PRIMARY KEY (child_id) ) So not only are you making a more complex index on the parent table by using two character columns you are also posting two columns into the child table(s) whenever you want to use this as a parent table. And with MySQL you generally have to make another INDEX on the FK columns as well as shown above. (I've never understood why this isn't automatic) In this case you have to decide whether or not it's good to maintain the uniqueness constraint on the parent table columns if you add an AUTO_INCREMENT column by doing something like: CREATE TABLE parent ( parent_id INT AUTO_INCREMENT, col1 VARCHAR(25) NOT NULL, col2 VARCHAR(30) NOT NULL, some_other_col VARCHAR(200) NULL, UNIQUE (col1, col2), PRIMARY KEY (parent_id) ); The UNIQUE constraint will still create an index on the text columns so you will still need to consider space/performance issues but at least your child tables only need to post a copy of the INT column parent_id. In my mind it's always good to use UNIQUE in these cases so your real primary keys are in your table structure to prevent getting bad data. My $0.02 Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Next alpha release?
I don't think I remember a time when MySQL only had stable production releases available for download (at least not off the website) since v3. Any idea if the next alpha is on the way? Thanks, 5.0 looks great, I've been using it for over a year with very few problems. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign key support in MyISAM
On Fri, 30 Sep 2005 13:08:31 -0700 Jacek Becla [EMAIL PROTECTED] wrote: Hi, I had asked similar question few days ago, and then checked with the developers as no one was able to answer on this mailing list. I was told it is very likely we'll get it in 5.2. Thanks - now that 5.0 is release candidate I wonder how soon it will be before 5.1 alpha is released? Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences Between ORACLE SQL PLus and MYSQL SQL
On Fri, 16 Sep 2005 10:32:23 -0400 [EMAIL PROTECTED] wrote: You asked basically two questions: a) what are the differences between ORACLE SQL plus and MYSQL? snip depends on how they are with MySQL already. One well-known point on how MySQL and Oracle differ is in how to define a JOIN in a SQL statement. MySQL works best if you use the explicit [INNER|LEFT|RIGHT] JOIN ... ON ... format. AFAIK, Oracle does not support this syntax. All these questions are also dependant on which version of which software you are using. In Oracle 8i the above is certainly true, you have to use the doofy (+) syntax for outer joins, etc. In Oracle 10g though the more verbose syntax is supported: http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_10002.htm#sthref7225 You can do LEFT INNER, RIGHT INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, etc. using the same syntax as MySQL. I'm not sure but some versions of MySQL also had different results from Oracle depending on how you defined the join condition in an OUTER join. So what version of MySQL you choose also has consequences on portability (e.g. sub-queries, views, etc) There are enough little differences between all DBMS's that make porting a real challenge. Bottom line, you're going to have to do a LOT of research and testing. I would seriously consider looking at a database abstraction layer. In ColdFusion I have no idea what is available, but for PhP you'd want to look at something like ADOBD (http://adodb.sourceforge.net) or PDO/MDB/Metabase, etc. Good luck, Josh -- 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]
Re: Seriously.. When are we going to get subqueries?!
On Wed, 8 Jun 2005 21:57:25 -0600 George Sexton [EMAIL PROTECTED] wrote: I think MySQL has a little ways to go yet before I would subjectively call it best. ok. I posted twice to the list with questions about porting my application that runs on (SQL Server, Oracle, PostgreSQL, Sybase SQL Anywhere, MS Access, and DB2) to MySQL. No one on the mysql list, or the internals list responded to my pretty basic issues: 1)Why can't I declare a datetime field with DEFAULT NOW() http://dev.mysql.com/doc/mysql/en/create-table.html The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column as of MySQL 4.1.2. See Section 11.3.1.2, _TIMESTAMP Properties as of MySQL 4.1_. snip For date and time types other than TIMESTAMP, the default is the appropriate ``zero'' value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. See Section 11.3, _Date and Time Types_. Looks like a policy decision, not a missing feature? Why does the TIMESTAMP column not meet your needs? 2)Since the SQL standard states that identifiers are not case sensitive, how can I use the DB without case sensitivity, when I don't have authority to change the system wide lowercase setting? I wouldn't have authority to change the setting in a hosted environment. Only thing I would suggest is to work with your hosting admin to see if they would be willing to change this system-wide setting since there is no per-user control over this. If this is something you cannot live with then choose a different RDBMS. I have to say, MySQL still looks like a tinker-toy to me. ignoring troll bait Good luck, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On Thu, 09 Jun 2005 14:28:56 +0100 Gordan Bobic [EMAIL PROTECTED] wrote: My understanding was the timestamp fields were only set when the record is created. They are not changed when the record is modified. http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html The first TIMESTAMP column in table row automatically is updated to the current timestamp when the value of any other column in the row is changed, unless the TIMESTAMP column explicitly is assigned a value other than NULL. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.6-beta has been released
On Tue, 31 May 2005 18:01:48 -0500 Matt Wagner [EMAIL PROTECTED] wrote: snip This is the third published Beta release in the 5.0 series. All attention will now be focused on fixing bugs and stabilizing 5.0 for later production release. Just curious - was there a 5.0.5-beta release? The last beta I had was 5.0.4 unless I missed a release announcement... Thanks, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Nevermind!] MySQL 5.0.6-beta has been released
On Wed, 1 Jun 2005 10:40:10 -0500 Josh Trutwin [EMAIL PROTECTED] wrote: On Tue, 31 May 2005 18:01:48 -0500 Matt Wagner [EMAIL PROTECTED] wrote: snip This is the third published Beta release in the 5.0 series. All attention will now be focused on fixing bugs and stabilizing 5.0 for later production release. Just curious - was there a 5.0.5-beta release? The last beta I had was 5.0.4 unless I missed a release announcement... Just saw this in the release: Changes in release 5.0.5 (not released): Sorry for wasting bandwidth... Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Export from Access
On Tue, 17 May 2005 17:17:31 +0100 S.D.Price [EMAIL PROTECTED] wrote: Hi, can anyone explain how I would export a database created in Access to MySQL using PHPMyAdmin - I can't seem to import the data as csv or txt. Acess should allow exporting to CSV. Otherwise you can skip phpMyAdmin and just use ODBC - check out MyODBC on mysql.com. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_fix_privilege_tables error
On Fri, 22 Apr 2005 22:44:44 +0300 Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Make a bug or feature report at http://bugs.mysql.com. Already did - 10098 - it was recently closed, guess it was already fixed in 5.0.5. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_fix_privilege_tables error
Would it be possible to add:
ENGINE=MyISAM
To all the CREATE TABLE statements in the mysql_fix_privilege_tables
script? The server (tested with 5.0.3 and 5.0.4) crashes when
creating/altering these tables if the following is in /etc/my.cnf:
default-table-type=innodb
I had to drop all the new tables and added ENGINE=MyISAM to the CREATE
TABLE statements, re-ran the script and it worked fine.
Here is an example crash report:
050422 9:19:43InnoDB: Assertion failure in thread 245771 in file
../include/data0type.ic line 466
InnoDB: Failing assertion: type-len % type-mbmaxlen == 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this
binary
or one of the libraries it was linked against is corrupt, improperly
built,
or misconfigured. This error can also be caused by malfunctioning
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is
definitely wrong
and this may fail.
key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size +
sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x8ab7a70
Attempting backtrace. You can use the following information to find
out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbe5f3938, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8180bef
0xb7e48c85
0x8295abc
0x829492a
0x829286c
0x82af069
0x82ae30c
0x82c5f99
0x823754f
0x8233119
0x8227b3f
0x8210552
0x823cb76
0x823f164
0x8196522
0x819d604
0x8194278
0x8193d85
0x8193192
0xb7e4354e
0xb7d71b8a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html
and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please
do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x8ae2ae0 = ALTER TABLE time_zone
MODIFY Use_leap_seconds enum('Y','N') COLLATE utf8_general_ci
DEFAULT 'N' NOT NULL
thd-thread_id=6
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 0
Josh
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.4-beta has been released
On Mon, 18 Apr 2005 13:28:24 +0200 Joerg Bruehe [EMAIL PROTECTED] wrote: Hi, A new version of MySQL Community Edition 5.0.4-beta Open Source database management system has been released. This version now includes support for Stored Procedures, Triggers, Views and many other features. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up-to-date at this point. If you cannot find this version on a particular mirror, please try again later or choose another download site. snip The mirrors seem to have links to the source tarball, but I've downloaded 5 different files and each time I get: # tar zxvf mysql-5.0.4-beta.tar.gz gzip: stdin: not in gzip format tar: Child returned status 1 tar: Error exit delayed from previous errors Something amiss or am I losing it? Thanks, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.4-beta has been released
On Mon, 18 Apr 2005 18:04:46 +0200 Joerg Bruehe [EMAIL PROTECTED] wrote: snip Are you sure it did not get damaged during transfer, or by your browser? All I can recommend is to try another mirror. I'm using elinks text browser, which has worked great for this in the past. I tried about 4 different mirrors. I'll keep at it and see if I get a better download. Thx, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL vs PostgreSQL
On Tue, 15 Mar 2005 18:49:38 +0900 ninjajs [EMAIL PROTECTED] wrote: What do you think about MySQL vs PostgreSQL ? Both are great products and have their ups and downs. On a MySQL list you will not get an un-biases answer to this question. If you really want to know what people on the MySQL list think of PG, search the archives (as already recommended). If you also want to know what PG folks think of MySQL, they have list archives as well. Both are easy enough to install (with MySQL being slightly easier on the newbie scale) that you can just install them both, and evaluate for yourself. Have fun, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and PHP
On Wed, 29 Dec 2004 10:42:45 -0500 GH [EMAIL PROTECTED] wrote: On Windows... Which do i install first? PHP or MySQL? Apache. :) It doesn't really matter but I'd suggest MySQL first as I think there is one file you need to copy from the MySQL installation to the C:\Windows (or whatever systemroot directory you use) before the PhP MySQL extension will work. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] MySQL and PHP
On Wed, 29 Dec 2004 12:09:51 -0500 [EMAIL PROTECTED] wrote: snip Of course, the WAMP alternative is always there, Apache is FREE and comes with PHP. snip FWIW, the download of apache from the Apache website (http://httpd.apache.org/) does NOT include PhP, you still have to download and install php as a module, but you don't have to do anything special to Apache to use it with PhP/Perl/whatever beyond editing the config file to load the correct module. Here's another nice product that has all the WAMP software bundled together. http://www.sokkit.net/pragmacms/index.php?layout=maincslot_1=2 Anyway, this is now OT for a MySQL list Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql-4.1.8 library name bug
Yes, this has been reported on this list a couple times already. Really baffling how this one made it out of QA. Josh On Thu, 23 Dec 2004 21:43:27 +0200 (EET) Andrey Kotrekhov [EMAIL PROTECTED] wrote: SQL Hello, All! IMHO this the bug in 4.1.8 to create library shared libraries without .so suffix. After this any programs linked with static libraries not dynamic, because of convention lib*.so.[0-9] in shared libraries names. ldconfig doesn't see new libraries at all too. This bug in 4.1.8 4.1.7 compiled on the same PC at the same time with .so suffix in library names. Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport ÔÅÌ. +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: libmysqlclient name library wrong
On Fri, 17 Dec 2004 16:19:25 -0200 (BRST) [EMAIL PROTECTED] wrote: Description: Mysql 4.1.8 installs libmysqlclient as libmysqlclient.14.0.0 instead of libmysqlclient.so.14.0.0 which is the correct for Linux. I wish you were about 1/2 hour faster, I just finished rebuilding everything that links against mysql (php, perl dbd, courier imap, etc) because of this. I thought maybe the library version number just bumped up one. Erg, now I'll have to do that all again when 4.1.9 fixes this... Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FOREIGN_KEY CHECKS
On Wed, 8 Dec 2004 08:21:54 +0200 Heikki Tuuri [EMAIL PROTECTED] wrote: dumps do contain that setting in 4.1.7. Below is a start of a dump file. [EMAIL PROTECTED]:~/mysql-4.1/client ./mysqldump test dump [EMAIL PROTECTED]:~/mysql-4.1/client cat dump | more -- MySQL dump 10.9 -- -- Host: localhostDatabase: test -- -- -- Server version 4.1.8-debug-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */; Great, thanks for the info! Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FOREIGN_KEY CHECKS
On Tue, 07 Dec 2004 16:51:10 -0700 Titus [EMAIL PROTECTED] wrote: On page 822 of the pdf copy of the documentation that I have, it says: To make it easier to reload dump files for tables that have foreign key relationships, mysql automatically includes a statement in the dump output to set FOREIGN_ KEY_CHECKS to 0 as of MySQL 4.1.1. I'm using MySQL 4.1.7 and the dumps do not appear to include this line automatically. I am having to add it manually. What am I overlooking? I THINK you have to add it manually to your dump file. Personally I wish MySQL would add this as an option to mysqldump as I can never remember the syntax to this command when I do an import with InnoDB tables. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OUTER JOIN bug in 5.0.1?
On Tue, 21 Sep 2004 08:57:21 -0400 [EMAIL PROTECTED] wrote: snip There are up to three layers of record filtering that happen during any query. First is the JOIN filtering. That is where the ON conditions are used with the table declarations to build a virtual table that consists of all columns from each of the participating tables and each combination of rows that meets the ON conditions. If table A has 5 rows and table B has 50 rows and the ON conditions force a match of at most 2 records from table B to each record in table A, the virtual table will have at most 10 rows (not the 250 that would be generated without the ON conditions). Second to be applied is the rest of the WHERE clause that could not be applied during the ON determinations. This is especially true with queries that contain OUTER JOINS. If it didn't happen in this order, we couldn't do an outer join of two tables and look for a null result in the outer table to determine non-matching rows. The third set of filters to be applied comes from the HAVING clause. HAVING conditions are applied after every other portion of the query has been analyzed except for the LIMIT clause. That is why HAVING works on the results of GROUP BY aggregate functions and can use column aliases declared in the SELECT clause. Yes, this much I grasp as far as the ordering of filtering. Cartesian Product, then JOIN, Then WHERE. When you put a condition into the ON clause of a JOIN, it is going to be applied to the formation of the virtual table which gets computed _before_ the entire WHERE clause is applied. Under many conditions, some WHERE conditions can also be applied to table JOINs along with the ON restrictions. Luckily, the query optimizer handles that for us. Perhaps another example would help. I've been trying to re-write another join query that's designed to produce an attendance record for each student detailing which classes they've attended and which they've missed. Each occurance of a student (PK tech_id) attending a class (PK c_id) is recorded in an intersection table class_attneded (tech_id, c_id PK). The absense of a record in this table indicates the student missed the class. So if tech_id 123123 was at classes 1 and 3, there would be records in the class_attended table: tech_id - class_id 1231231 1231233 The query that worked pre 5.0.1 is: SELECT * FROM student s INNER JOIN enrollment e ON e.tech_id = s.tech_id INNER JOIN class_attended ca ON ca.tech_id = s.tech_id RIGHT JOIN class c ON c.c_id = ca.c_id WHERE s.tech_id = '253542' AND c.term_id = '4' AND c.class_date NOW() ORDER BY week_no; In my mind I see this as get all the class records (the table on the right side of the RIGHT JOIN) and if you can match up the class_attended, enrollment and student information do so, otherwise set those values to NULL. In the example above this yeilds 3 rows - the for for class id 2 has NULLs in the other table data. I cannot seem to figure out the 5.0.1 equivalent because I seem to have learned this the wrong way. I tried this, starting with the class table since those are the rows that I want to have displayed regardless of matches. SELECT * FROM class c LEFT JOIN class_attended ca ON c.c_id = ca.c_id INNER JOIN student s ON ca.tech_id = s.tech_id AND ca.tech_id = '253542' INNER JOIN enrollment e ON e.tech_id = s.tech_id WHERE c.term_id = '4' AND c.class_date NOW() ORDER BY week_no; This acts like an INNER JOIN though, I can only get two rows. I've tried mucking around with it, but I just cannot get the outer row for class id = 2 to show. This was so much easier with the bug! :) Thanks for your help, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OUTER JOIN bug in 5.0.1?
On Tue, 21 Sep 2004 10:49:31 -0400 [EMAIL PROTECTED] wrote: Hmmm. you want to see a student, all of the classes they are enrolled in and how many times they attended? I understand the relationships between the student, class, and class_attended tables (that's all related to attendance and class scheduling) but I do not understand the relationship between student and class. Is that the enrollment table? Does enrollment have a class id field on it? Are there other tables I do not know about that can tell you if a student is _supposed_ to be in a class? If enrollment does relate a student to a class, I propose the following query Not quite. The class is probably causing confusion becuase I didn't explain what this application was used for. I teach a single course named ICS311 and this is my gradebook for only this course. I teach the course in many terms (summer, fall, spring, etc). Each class is a single meeting for the course. For example, class number 1 is on 9/24/04, class number 2 is on 10/5/04 etc. Class is a poor choice of words since I only care about this single course. If it helps, I have an ERD here: http://trutwins.homeip.net/gradebook.png For now ignore all the assignment and login stuff. I don't list FK's in ERD's, if you cannot derive them from the model they are: Enrollment.tech_id references Student.tech_id Enrollment.term_id references Term.term_id Class_attended.tech_id references Student.tech_id Class_attended.c_id references Class.c_id (week_no in ERD) Class.term_id references Term.term_id week_no in the ERD is the class Id since there is only one class per week. Week 1, week 2, etc. Enrollment is used because it remembers which students are enrolled in the current term for the attendance record I'm trying to create. I also use it in this query because the withdrawl date might come into play. So what I'm trying to do is display which classes (meetings/whatever) a student has attended and which they have not been at for my ICS311 course. Here's what kind of results I'd like: Student Id: 123123 Name: Josh class_id attended 1 1 2 NULL 3 1 4 1 5 NULL 6 1 So this particular student missed classes 2 and 5 because they did not have a record in the class_attended table, which would have these records: class_id tech_id 1 123123 3 123123 4 123123 6 123123 Hope that helps, let me know if more details would help. SELECT s.tech_id, s.name, c.c_id, c.name, count(ca.id) as days_present, count(c.c_id) as classes_held FROM student s INNER JOIN enrollment e on e.tech_id = s.tech_id INNER JOIN class c on c.c_id = e.c_id LEFT JOIN class_attended ca on ca.c_id = c.c_ID WHERE s.tech_ID = 253542 AND c.term_id = 4 AND c.class_date NOW() GROUP BY 1,2,3,4 Based on the description above this isn't quite what I need. I don't need to GROUP at all, just get the right OUTER JOIN clause to do this. I think we are close. Agreed, many thanks for your persistance in helping with this! Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OUTER JOIN bug in 5.0.1?
On Mon, 20 Sep 2004 09:33:56 -0400 [EMAIL PROTECTED] wrote: Sounds like your 4.0.20 may be the buggy installation... let me see if I can explain. Except this is a 5.0.1 installation. The query worked as is in 4.0.20 (and it also worked in 5.0.0), only after playing with 5.0.1 did the results suddenly change. Let's analyze your FROM clause and imagine there is no WHERE clause, for the moment: FROM student s INNER JOIN enrollment e ON e.tech_id = s.tech_id INNER JOIN submitted_assignment sa ON sa.tech_id = s.tech_id RIGHT JOIN assignment a ON a.a_id = sa.a_id The tables student, enrollment, and submitted_assignment are all INNER JOINed to each other. You won't get any records back from those tables UNLESS they satisfy the ON statements that match records between them respectively. That batch of records is RIGHT JOINed to assignment. That means that all of the records from assignment are returned and the other 3 tables just became optional data. Their data will be added to the internal working table only if they meet the ON condition of the RIGHT JOIN. Here is a logically equivalent way of reformatting your original FROM clause FROM assignment a LEFT JOIN submitted_assignment sa ON a.a_id = sa.a_id INNER JOIN student s on sa.tech_id = s.tech_id INNER JOIN enrollment e on e.tech_ID = sa.tech_ID Yes, I tried re-arranging things like this, and as it is above I think it's more readable, but I was unable to get any results that resembled an outer join. Unfortunately I cannot test this out at the moment due to other issues. Thanks for your help though, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OUTER JOIN bug in 5.0.1?
On Mon, 20 Sep 2004 10:25:16 -0400 [EMAIL PROTECTED] wrote: I think you missed my point. I think the 5.0.1 behavior was correct and the others are wrong. There is a known bug (or two) about mixing outer joins and inner joins and it looks like it may be fixed. IF you want to see all of the students THAT TABLE (students) needs to be on the LEFT side of a LEFT JOIN or the RIGHT side of a RIGHT JOIN. That's what the directions mean Interesting - do you have a link to more information on this bug? SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted FROM student s INNER JOIN enrollment e ON e.tech_id = s.tech_id AND e.term_id = '3' LEFT JOIN submitted_assignment sa ON sa.tech_id = s.tech_id LEFT JOIN assignment a ON a.a_id = sa.a_id AND a.a_id = '100' ORDER BY s.full_name; I also moved the clause AND a.a_id = '100' into the ON portion of the LEFT JOIN. That way you can see who did and didn't get that assignment. If you describe what you WANT to see, I can help you write the query to get it. What I think I wrote for you will be all students where term_ID=3 and what grades they got on assignment 100. But i think you may get some duplicate rows of blank scores. Does assignment relate to student, perhaps with a tech_id or student_id field? That fixes one dilemma by setting up the following query SELECT s.tech_id, s.full_name, if(isnull(a.a_ID),'no','yes') as assigned_100, sa.points_awarded, sa.date_submitted FROM student s INNER JOIN enrollment e ON e.tech_id = s.tech_id AND e.term_id = '3' LEFT JOIN assignment a ON a.student_ID = s.student_ID AND a.a_id = '100' LEFT JOIN submitted_assignment sa ON sa.tech_id = s.tech_id AND a.a_id = sa.a_id ORDER BY s.full_name; This is what I eventually used: SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted FROM student s INNER JOIN enrollment e ON e.tech_id = s.tech_id LEFT JOIN submitted_assignment sa ON sa.tech_id = s.tech_id AND sa.a_id = '$a_id' LEFT JOIN assignment a ON a.a_id = sa.a_id WHERE e.term_id = '3' ORDER BY s.full_name; It didn't seem to work with the AND sa.a_id = '$a_id' in the assignment join condition - but this works. I don't understand why that doesn't filter the right rows if it's in the WHERE clause, I usually define my JOIN condition solely on the column(s) being joined together and any additional filtering gets done in the WHERE clause. Man, I thought I had a good handle on OUTER JOINs. Erg. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OUTER JOIN bug in 5.0.1?
Is there a known bug with outer joins in MySQL 5.0.1? I tried the following query on 5.0.1: SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted FROM student s INNER JOIN enrollment e ON e.tech_id = s.tech_id INNER JOIN submitted_assignment sa ON sa.tech_id = s.tech_id RIGHT JOIN assignment a ON a.a_id = sa.a_id WHERE a.a_id = '100' AND e.term_id = '3' ORDER BY s.full_name; None of the outer results are present. At first I thought the query was wrong, but if I run the same exact query using the same tables (from a mysqldump) on a 4.0.20 server I get the expected results including student's who have not yet submitted the assignment. Running on SuSE linux 5.0.1 compiled from source. Any more details I can provide? I have to imagine that something like this has already been found. Is the 5.0.1 snapshot on the products download page updated frequently? If so I guess I could try to download and install again. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: html in a text field - good practice?
On Wed, 18 Aug 2004 11:37:03 -0400
leegold [EMAIL PROTECTED] wrote:
Question I have wondered about: Is it a good practice to put html in
a text field, then (eg. via php) when the marked-up text renders in
a user's browser it's good looking html. If not, then I'd just
sandwitch field content in a p/p when it's rendered. Though,
seems like it would mess-up fulltext searching in a marked-up text
field(?). Thanks. Lee G.
I never cared for it, but if you HAVE to, my recommendation is to do something like
this:
$clean_html = htmlentities($dirty_html, ENT_QUOTES);
mysql_query(INSERT INTO table (html_field) VALUES ('$clean_html'));
Then when you need to display the HTML, after pulling the data from the database use:
$html = mysql_entity_decode($html_from_db, ENT_QUOTES); //requires php 4.3.0
The htmlentities converts characters like quotes, , , etc. to nice text the database
won't have any problems storing and prevents SQL injection attacks (it's a good idea
to use htmlentities on ANY text field you take from an untrusted source and insert
into a database)
I would also strip out script tags and research cross site scripting prevention
which you are in danger of having problems with if you blindly store submitted HTML
from the Internet such as on in a bulletin board app.
http://www.php.net/htmlentities
http://www.php.net/html_entity_decode
Josh
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
On Tue, 10 Aug 2004 23:40:39 +0200 Jochem van Dieten [EMAIL PROTECTED] wrote: On Tue, 10 Aug 2004 16:49:26 -0400, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Well, since you admitted to being a newbie, I would suggest that you learn with MySQL. It supports several types of data storage (memory only, ISAM, full-relational) and both transacted and non-transacted execution models. That's just about everything you could want a database to do. No, not really: http://sql-info.de/mysql/gotchas.html Good link - thanks. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
On Tue, 10 Aug 2004 23:34:49 +0200 Jochem van Dieten [EMAIL PROTECTED] wrote: On Tue, 10 Aug 2004 16:00:12 -0500, Josh Trutwin wrote: One area where MySQL beat Postgres is in Windows installation. Installing postgres on Windohs is like pulling your fingernails off slowly. It is more like following the manual. Not hard, you just have to take it one step at a time. I should point out that I haven't used the compile-from-source method that's in the documentation, which like you said looks pretty simple. I can get postgres running in a cygwin env pretty easilly, but getting it to automatically start/stop as a service is usually the painful part. I've used this as a bonus exercise for students in a dbms course I teach and their conclusion is the same. I think this complaint will fall away soon though with PG 8.x. FWIW, PG on *nix is just the opposite, very clean and simple install. MySQL's command line interface and programming API also are nicer for newer users. Why in the world do I need to remember to type \d to show my tables? Why in the world do I need to remember SHOW TABLES? Why can't the standard information schema work? :-) Ah, because SHOW TABLES and exit or quit makes sense and is easy to remember vs. \d \q \whatever. I like the \d because once you know it it's dang quick to type, but it's nice to have the longer format as well. Whatever, to each their own... Command line interface and programming API are pretty much irrelevant if you are using ColdFusion. It is all abstracted out behind a JDBC API (ColdFusion is still at JDC 2), which in turn is behind ColdFusion's API, so unless you go the way of the Java ninja you can't even reach it. Ok. Command line interface and programming API are never truely irrelevent IMHO. :) I said it before, I like em both, use em both and have rubs with both of em. Is one better than the other? Yes and No. Thanks, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] PostgreSQL / MySQL Data Dictionary
On Wed, 11 Aug 2004 17:20:45 +0200 Jochem van Dieten [EMAIL PROTECTED] wrote: SHOW TABLES does not make sense. How are you going to join the output of SHOW TABLES against the output of SHOW COLUMNS and SHOW INDEXES? SELECT * FROM INFORMATION_SCHEMA.TABLES makes sense. And as for easy remembering: I prefer to remember just one standard, instead of the idiosyncracies of each product. Yes, a queryable (sp?) set of dictionary tables/views would be nice for doing this. The MySQL set of SHOW commands is pretty painful for any serious development. Does PostgreSQL have a set of information schema tables to query against like Oracle does (e.g. SELECT table_name FROM user_tables)? I noticed this from a quick google search: http://gborg.postgresql.org/project/orapgsqlviews/projdisplay.php Is something similar planned for MySQL in the future? Doesn't appear to be from: http://dev.mysql.com/doc/mysql/en/Roadmap.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Data Dictionary (INFORMATION_SCHEMA)
On Wed, 11 Aug 2004 12:09:34 -0700 Jim Winstead [EMAIL PROTECTED] wrote: On Wed, Aug 11, 2004 at 07:03:18PM +0200, Jochem van Dieten wrote: MySQL strives to ful SQL standard compliance (ISO/IEC 9075), so an INFORMATION_SCHEMA must be planned for someday. However, that day does not appear to be in the near future. INFORMATION_SCHEMA will be supported in MySQL 5.0. A developer (Sergey Gluhov) is currently working on the implementation. The 'New Features Planned for 5.0' section of the manual will be updated soon to mention it. Excellent, do you know anything else about it? Will it be in the next 5.0 alpha release? I'm very curious to try it out. Thx, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between PostgreSQL and MySQL
On Tue, 10 Aug 2004 16:45:29 -0400 Brad Tilley [EMAIL PROTECTED] wrote: No need for flames. I think the two are converging. One area where MySQL beat Postgres is in Windows installation. Installing postgres on Windohs is like pulling your fingernails off slowly. I hear they are close to full Windows support though in the 8.x branch. MySQL's command line interface and programming API also are nicer for newer users. Why in the world do I need to remember to type \d to show my tables? That said though, I do like both. Oracle is nice too. :) It'll be really interesting to compare pg and mysql in a couple years... Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Foreign Keys
On Thu, 22 Jul 2004 12:14:58 -0400 Roy Harrell [EMAIL PROTECTED] wrote: Can a child table have multiple foreign key references linking its records to two or more parent tables? yes - something like: CREATE TABLE child ( p1_id INT, p2_id INT, INDEX p1_id_ind (p1_id), INDEX p2_id_ind (p2_id), FOREIGN KEY (p1_id) REFERENCES parent1(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (p2_id) REFERENCES parent2(id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (p1_id, p2_id) ); Can a parent table also be a child table? That is, can a parent table have a foreign key that links it to another table? Yes - consider the somewhat nonesensical scenario: CREATE TABLE parent1 ( id INT NOT NULL PRIMARY KEY ); CREATE TABLE parent2 ( id INT NOT NULL PRIMARY KEY ); CREATE TABLE child ( p1_id INT, p2_id INT, INDEX p1_id_ind (p1_id), INDEX p2_id_ind (p2_id), FOREIGN KEY (p1_id) REFERENCES parent1(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (p2_id) REFERENCES parent2(id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (p1_id, p2_id) ); CREATE TABLE grandchild ( p1_id INT, p2_id INT, name VARCHAR(100), INDEX p1_p2_id_ind (p1_id, p2_id), FOREIGN KEY (p1_id, p2_id) REFERENCES child(p1_id, p2_id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (p1_id, p2_id) ); Note that foreign keys can contain multiple columns (as shown above) if the parent table has a multi-column (composite) primary key. I setting up my tables as INNODB types. Good. All my create tables should have had the ENGINE=InnoDB but I'm lazy. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: When is mysql 4.1.x , production?
On Mon, 12 Jul 2004 12:05:53 + Ben David, Tomer [EMAIL PROTECTED] wrote: when is mysql 4.1.x going to be released for production? When it is ready I'd guess. :) If history is a predictor though I would expect 4.1.x to go though 5-7 more beta releases though. Help the developers out by testing it in your enviornment. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: iptables and mysql...
On Fri, 2 Jul 2004 06:34:39 -0700 bruce [EMAIL PROTECTED] wrote: i'm investigating what needs to be done to allow mysql on a server to be used remotely by client machines. each machine is running iptables. so i'm wondering what has to be in the iptables for the machine being used as the mysql server, as well as the client machines that will be communicating with the mysql box... Assuming tcp communications in mysqld are not disabled and grant permissions are setup to allow remote connections, you should just be able to just allow your firewall to accept port 3306 (the default MySQL port) I use this on my FORWARD chain as I NAT it - you'll probably use it on the INPUT chain if you are running the firewall on a local machine: $IPTABLES -A FORWARD -p tcp -j ACCEPT --dport 3306 -m state --state NEW The --state NEW is used in conjunction with: $IPTABLES -A FORWARD -m state --state ESTABLISHED,RELATED -j ACCEPT which says to accept all connections that have already been established. $IPTABLES is just a var with the path to the iptables command. also, how would i test that the iptable/mysql configuration setup is working properly... Best way to test is to actually try it I guess. Find a remote computer and see if your iptables rules allows you to connect to mysql. If you are able to connect to mysql and input your username/password but get a permission denied or something then most likely your GRANTs are incorrect. Make sure connections are still denied by your firewall on ports you have closed, turn on logging, etc. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers or stored procedures
On Mon, 28 Jun 2004 07:11:04 -0300 Carlos J Souza [EMAIL PROTECTED] wrote: hello all What is more important? triggers or stored procedures. I think that triggers they are a lot more important than stored proc. because stored procs they can be implemented in the front end application. In the version 5 should be implemented triggers instead of stored procedures. What do you usually call with a trigger though? A stored procedure. MySQL seems to have done just fine without these for many years. I say if they can get these features in there without losing the characteristics that make MySQL so appealing, great, otherwise forget it. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNION with INTO OUTFILE and ORDER BY
Noticed something interesting about UNION and INTO OUTFILE If I do this: (SELECT a FROM b) UNION (SELECT a INTO OUTFILE 'out.txt' FROM c); The query executes - no results printed to the screen but rather saved to the out.txt file, as intended. But if I do: (SELECT a FROM b) UNION (SELECT a INTO OUTFILE 'out.txt' FROM c) ORDER BY a; The query works but the results only print out to screen and do not get dumped into out.txt. Actually nothing gets dumped to out.txt. moving the INTO OUTFILE is invalid syntax: (SELECT a FROM b) UNION (SELECT a FROM c) INTO OUTFILE 'out.txt' ORDER BY a; Is this normal/intentional? MySQL 4.0.20 on Debian Linux. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Views Syntax for MySQL 5
On Tue, 22 Jun 2004 16:34:44 +0100 Nic Skitt [Real Productions] [EMAIL PROTECTED] wrote: Hi All, I have noticed a lack of comments re Views. Is it 5.0 or 5.1 that we will have Views capability? I had hoped it was in the most up to date public development release of 5. Unless I am getting the Views syntax wrong I assume its not there. If it is not already packaged in V5.0 then will the syntax be the standard SQL view syntax? The online manual is your friend: http://dev.mysql.com/doc/mysql/en/index.html First hit for searching on views: http://dev.mysql.com/doc/mysql/en/ANSI_diff_Views.html Sounds like it will make it into the 5.0 branch, but has not yet. Another good link: http://dev.mysql.com/doc/mysql/en/Roadmap.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBD or InnoDB or not?
On Thu, 17 Jun 2004 09:01:57 -0500 Scott Johnson [EMAIL PROTECTED] wrote: Hi Every one, I'm back to using Mysql after being away doing too many Microsoft job. hahaha I'm installing my first MySQL in about five years and I'm perplexed with the the added formats of batabase table. What are the pro's and con's of the DBD and InnoDB formats over the origional Binary? I assume you mean BDB not DBD. In a nutshell, these two table types add support for foreign keys and ACID compliant transactions. I am not sure of the major difference between BDB and InnoDB, InnoDB seem to be the more popular choice. Just as a note, I'm trying to setup a small company with DBmail and MySQL. So file blobs and back logs of saved data will be the norm. I'm also going to be storing faxes as well. Chance are for this type of application you will just want to use the default MyISAM table handler which does not have Transactions and Foreign Keys, but would provide more speed for this type of database usage. You should probably read or at least glance through all of Chapter 15 on the online manual: http://dev.mysql.com/doc/mysql/en/Table_types.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Binary logfiles eating all my disk space
On Thu, 10 Jun 2004 15:55:31 +0300 Dobromir Velev [EMAIL PROTECTED] wrote: I've made a simple perl script that uses the show master logs and purge master logs to 'mysql.???' queries to remove all but the last seven logs and it works perfectly for me. Would you be willing to share your perl script? Perhaps offlist if you are concerned about everyone seeing it? Thanks, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE...WHERE ... IN (SELECT...)
On Wed, 9 Jun 2004 18:25:11 +0200 Markus Källander [EMAIL PROTECTED] wrote: Hi all, Can someone tell me what is wrong with this statement? UPDATE nummer SET gold = '1' WHERE anr IN (SELECT no FROM gull); I use MySQL 3.23.49. ^^^ 4.1 or higher is required for sub-selects. http://dev.mysql.com/doc/mysql/en/Subqueries.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DELETE from more than one table
On Mon, 07 Jun 2004 12:36:47 + shaun thornburgh [EMAIL PROTECTED] wrote: Hi, Is it possible to DELETE from more than one table in one command? Thanks for your help DROP TABLE IF EXISTS table1, table2, table3; http://dev.mysql.com/doc/mysql/en/DROP_TABLE.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: URGENT !! InnoDB Constraint problem !
On Sun, 6 Jun 2004 15:48:37 +0200 Dimitri Bosiers [EMAIL PROTECTED] wrote: snip INSERT INTO pages(hidden,name,page_number,data,is_section_root,layout_id,group_ id) VALUES(1,'nieuwe pagina',100,'',2,1,@LID); this fails with a ERROR 1216: Cannot add a child row: a foreign key constraint fails Run: SHOW INNODB STATUS; And post the results in the Latest Foreign Key Error section. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery help...
On Sat, 5 Jun 2004 21:10:42 -0600 Daniel Isenhower [EMAIL PROTECTED] wrote: First off, I assume you are using a version of mysql able to handle sub-queries. 4.1 or 5.0 (4.0.xx does NOT support sub-queries) Ugh... I feel dumb :) I'm using 4.0 No worries, there are too many versions of MySQL to choose from... FWIW, this is an easy query with a JOIN: SELECT id FROM work w INNER JOIN client_list cl ON cl.id = w.client_id WHERE cl.name = 'Some Company'; Just in case you are using mysql 4.0 or earlier... Thanks very much! This is what I need :) Also, while I'm at it, any book recommendations for getting to know MySQL better? I've always liked the reference manual, but it's essentially a reprint of the online documentation, which is excellent. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery help...
On Sat, 5 Jun 2004 19:48:44 -0600 Daniel Isenhower [EMAIL PROTECTED] wrote: This is my first email to the list, so be nice ;-) Welcome, this is a very helpful list... I'm having some difficulty with a subquery that I'm trying to do, and was wondering if anyone here can shed some light on the issue... First off, I assume you are using a version of mysql able to handle sub-queries. 4.1 or 5.0 (4.0.xx does NOT support sub-queries) This query returns a result as expected: SELECT id FROM client_list WHERE name=Some Company (the id returned here is 3) This query also returns a result as expected: SELECT id FROM work WHERE client_id='3' ORDER BY id DESC; Does anyone know why this one doesn't return any results? SELECT id FROM work WHERE client_id='(SELECT id FROM client_list WHERE name=Some Company)' ORDER BY id DESC; Don't use quotes, it's looking for a client_id that is literally the stuff inside your quotes. I'm guessing that'll never be the case. :) Try: SELECT id FROM work WHERE client_id = (SELECT id FROM client_list WHERE name=Some Company) ORDER BY id DESC; FWIW, this is an easy query with a JOIN: SELECT id FROM work w INNER JOIN client_list cl ON cl.id = w.client_id WHERE cl.name = 'Some Company'; Just in case you are using mysql 4.0 or earlier... Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for FK constraints only after the transaction commited ????
On Fri, 4 Jun 2004 09:39:44 -0300 (ART)
Leandro Melo [EMAIL PROTECTED] wrote:
Hi,
i`m using mysql in a j2ee application with JBoss.
For best design practices i cannot add the
foreign-keys to the tables in the same insert as i
add the table data. Basically, i`ll add it a few
milliseconds latter (in the same transaction), but
anyway it`s not in the same insert.
This doesn't sound like best design practices. What exactly are you trying to do?
Why not:
START TRANSACTION;
INSERT INTO table (nonfk_col1, nonfk_col2) VALUES ('x', 'y');
UPDATE table SET fk_col3 = fk_values WHERE whatever;
COMMIT;
Then, i had to configure my foreign-keys to be allow
null, something that i really don`t want to do.
Allowing foreign key columns to be NULL is a perfectly acceptable and reasonable
practice depending on your data model.
So, is there a way to only check for the FKs
constraint at the end of the transaction, and not at
the rigth moment of the insert
You could enable and disable foreign keys in your transaction but this seems to be
hacking a solution onto a design problem.
Josh
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
On Thu, 3 Jun 2004 13:06:54 -0700 Chris Dietzler [EMAIL PROTECTED] wrote: Running 4.0.18 I am trying to run a query where the query gets the offer_ID of a certain customer from the offer table and displays in the results the offer_Name associated with the offer_ID. Right now the way the query is working it displays all the offers in the offer table regardless of the offer_ID assigned to the customer. Any thoughts? SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c, assets a , offers o WHERE c.cst_SiteID = a.asset_SiteID AND cst_Name LIKE 'z%' snip Global rule for joins - if you have N tables (or table aliases) involed a JOIN, you need N-1 JOIN conditions in your WHERE clause, or using INNER JOIN clauses. In your query cst_Name LIKE 'z%' is NOT a join condition, it's just a filter. Nothing actually joins your offers table in the above query. Try: SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c, assets a , offers o WHERE c.cst_SiteID = a.asset_SiteID AND c.cst_Offer = o.offer_ID AND cst_Name LIKE 'z%'; Or in preferable INNER JOIN syntax which makes it easier to forget a JOIN condition: SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c INNER JOIN assets a ON c.cst_SiteID = a.asset_SiteID INNER JOIN offers o ON c.cst_Offer = o.offer_ID WHERE cst_Name LIKE 'z%'; Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
On Thu, 3 Jun 2004 15:22:36 -0500 Josh Trutwin [EMAIL PROTECTED] wrote: Or in preferable INNER JOIN syntax which makes it easier to forget a JOIN condition: Oops - I meant harder to forget not easier to forget. Doh. SELECT c.cst_SiteID, c.cst_IDC, a.asset_ID, o.offer_ID, o.offer_Name FROM customers c INNER JOIN assets a ON c.cst_SiteID = a.asset_SiteID INNER JOIN offers o ON c.cst_Offer = o.offer_ID WHERE cst_Name LIKE 'z%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a SELECT Statement
On Thu, 3 Jun 2004 22:36:55 +0100 Andrew Dixon [EMAIL PROTECTED] wrote: Hi Everyone. I have the following SELECT statement the get information from two tables. The statement only works when it finds a value for the image_archive.circuit value in the circuits table. However, not all entries in the database has a value in the this field, some are set to 0 as the circuit for that image was not known or the image was not a circuit. When you have an unknown value, the best value to use in a relational database instead of 0 or an empty string or anything else is NULL, that's what NULL was actually created for (well, one of the reasons anyway). Then your query would turn into a fairly simple outer join. FROM image_archive ia LEFT OUTER JOIN circuits c ON ia.id = c.circuit WHERE ia.id = 109 SELECT image_archive.filename, image_archive.year, image_archive.month, image_archive.driver_forename, image_archive.driver_surname, image_archive.team, image_archive.event, circuits.name as circuit_name, image_archive.description, image_archive.title, image_archive.membership_no FROM image_archive, circuits WHERE image_archive.id = 109 AND circuits.id = image_archive.circuit Maybe tack on: OR image_archive.circuit = 0? How can I modify the statement to allow it to return a record when the image_archive.circuit value is 0, but to return the circuit name when the value is greater than 0. At the moment when the circuit value is 0 no records are returned even though I know the rest of the information is in the image_archive table. Hope that makes sense. Thanks in advances for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subscription
On Thu, 03 Jun 2004 14:56:23 -0500 Tom Murdock [EMAIL PROTECTED] wrote: Good evening, I would like to subscribe to MySQL mailing list. Thank you. Your wish appears to have been granted. :) Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1146 Table mysql.proc doesn't exist
On Tue, 1 Jun 2004 14:50:11 +0200 Bungarz, Kai [EMAIL PROTECTED] wrote: Hi! I'm using mysql-max-nt, Version 5, running on Windows 2000. While inserting rows into a table from a perl script, i get error 1146:table mysql.proc doesn't exist. The application ran without any error with mysql, version 4. Any ideas? Yes, if you are running version 5.0 - a development release, make sure you CAREFULLY read the documentation so you know what you are getting into. You need to fix the grant tables: http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Encrypting Data???
On Tue, 18 May 2004 10:38:25 -0400 Thomas Trutt [EMAIL PROTECTED] wrote: Hello everyone, Is there a way to have the data in a table encrypted so that the MYD file can't be opened with a text editor and read??? I need to encrypt or some how make illegible numeric, date, text, and vchar fields.. Any idea??? http://dev.mysql.com/doc/mysql/en/Encryption_functions.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.18 vs 4.1.1
On Sun, 16 May 2004 13:51:29 -0700 Ron Gilbert [EMAIL PROTECTED] wrote: I am going to upgrade from 3.23 and was wondering if 4.1.1 is stable enough? This is just for some personal websites, nothing mission critical, but on the other hand, I don't want to deal with endless problems. The reason that I'd like to go to 4.1.1 is for sub-selects, otherwise I'd stick to 4.0.18. What I would really like is go to 5.0 (for stored procedures), is 5.0 stable enough for casual use? I've been using 5.0.0 for some sites and use it for an RDBMS class, nothing mission critical mind you, but it's worked very well in my opinion (running on SuSE Linux). There were a couple upgrade issues which are covered in the documentation, but nothing serious. YMMV Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.18 vs 4.1.1
On Sun, 16 May 2004 16:02:06 -0700 Ron Gilbert [EMAIL PROTECTED] wrote: If I switch to 5.0, are there any issues with PHP? Will my 4.2.2 version of PHP work just fine with 5.0? I also have a Windows C++ program that talked to mysql over the Internet via the C API, will it still work after the 5.0 (or the 4.1.1) upgrade? I'm not doing anything fancy with it. On Linux it works ok to use PhP 4.x (or PhP 5.x) with MySQL 5.0, on Windows I just could not get the two to talk to each other. I think it works ok with 4.1.1 though. MySQL 5.0 is a Zip package, not a nice Install Shield though so you should be comfortable installing that way before attempting to upgrade. If you are so gung-ho to upgrade MySQL to an alpha product, maybe you should also considering upgrading php to at least 4.3.6 or even 5.0rc2 as there have been many security fixes since 4.2. The best advice I have for you is to get a test box, install what you want and hammer it for a while and see if it works ok. The MySQL/PhP developers I'm sure will at least appreciate the testing. Other than whats in the docs, are there other issues to think about when upgrading from 3.23? Can I go straight from 3.23 to 5.0? According to the docs, it just seem to be the GRANT tables. Definately the GRANT tables, also if you use the PASSWORD function there have been some changes that might make it incompatible with previous versions. Other than that I think I was fine. You need to run a few scripts I think to setup the stored procedure tables and some help tables. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default search directory for SOURCE command?
Hi,
I teach a class using Oracle and MySQL and I store a few things like creating tables
for student labs in SQL files. I have them load these files by typing:
SOURCE filename.sql;
from the MySQL prompt.
A nice feature in Oracle is that you can set a default path where the client looks for
these scripts using: setenv SQLPATH ${HOME}/orascripts, then you don't need to type
the full path fo the SQL script file and it works from whatever directory you started
the client from.
Is there something similar in MySQL? I'm trying to keep things simple so the MySQL
SOURCE command will find these SQL scripts from whereever the student started MySQL
from. And yes I know that this can be done from outside the MySQL client using input
redirection, but I think students find the SOURCE command simpler.
Also, any idea why SOURCE filename.sql; works in MySQL 5.0.0 and \. filename.sql; does
not?
Thx,
Josh
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default search directory for SOURCE command?
On Sat, 15 May 2004 14:38:52 -0500 Paul DuBois [EMAIL PROTECTED] wrote: snip Is there something similar in MySQL? No. Dang - oh well. :) snip Also, any idea why SOURCE filename.sql; works in MySQL 5.0.0 and \. filename.sql; does not? Leave out the trailing semicolon for short-form commands. Thanks, documentation on mysql.com should probably be fixed then: http://dev.mysql.com/doc/mysql/en/Batch_mode.html http://dev.mysql.com/doc/mysql/en/Batch_Commands.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exists
On Fri, 14 May 2004 14:00:46 +0100 (BST) A Z [EMAIL PROTECTED] wrote: Hi, How can I write this query? Insert into table1 (field1) select (a.field1) from table2 a where a.field1 not exists (select c.field1 from table1 c) http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB + Foreign Keys
On Thu, 13 May 2004 10:34:37 -0700 (PDT) David Blomstrom [EMAIL PROTECTED] wrote: I thought that only InnoDB tables could be joined - and only if they had foreign keys. But it sounds like any kind of table can be joined, and it doesn't need a foreign key. Exactly, you can do a join with any two or more tables (even the same table joined to itself actually) and on any columns. It's just a matter of whether or not the join makes any sense, for example: SELECT * FROM t1, t2 WHERE t1.city = t2.golfer_id Doesn't make much sense to do this, but the SQL will parse and attempt to execute anyway. Can someone explain InnoDB, MyISAM and foreign keys in plain English? If I understand correctly, foreign keys simply help ensure integrity. To put it another way, they help weed out errors. InnoDB and MyISAM are table types, or table engines is now the preferred terminology. Different storage implementations basically. MyISAM is so much faster because it doesn't have to deal with the overhead of transactions and foreign keys. An important aspect of foreign keys is the referential action (ON DELETE CASCADE, ON UPDATE SET NULL, etc) which allow you to have the database take care of cascading actions when a parent row is deleted without having to worry about having an application programmer do it and make a coding error or forget to and leave orphaned rows. So if you have a student parent table and an enrollment child table, you can set it to delete rows in the enrollment table for student 123 if student 123 is deleted form the student table (no orphaned rows in enrollment when a student is deleted). You can also set up referential actions to prevent deleting rows from a parent if there are rows in a child table (ON DELETE RESTRICT), it all depends on your situation. Foreign Keys also requires that any rows inserted into the the child table MUST have a value that matches in the parent table. So for the student/enrollment table, if you attempt to insert a row into enrollment for studentId 342, the only way that query will work is if there is indeed a student with studentId 342 in the student table. This is the referential integrity part of Foreign Keys. Foreign keys provide a real link between tables to implement an actual relationship between two tables, or even a table to itself. (remember this is a relational database afterall). Without that foreign key, the relationship is only implied and might not be known to anyone unless there is an entity-relationship diagram for the project. This comes in handy when trying to reverse engineer a data model from an existing db application. Data models should always come first though, but we're only human! For example, when I import data, I often get errors - something about violations of foreign key restraints. When I examine my original tables, I often find discrepancies - like eu* rather than eu in a row for Turkey, which lies partly in Europe AND Asia. I've considered the possibility of creating foreign keys for quality control, then deleting them after I'm finished so I can tweak my table - like adding * to certain elements. However, it sounds like it's very difficult to delete foreign keys. I tried it in MySQL-Front or SQLyog and was presented with an 11-step process, or something like that. ALTER TABLE mytable DROP FOREIGN KEY fk_symbol; (SHOW CREATE TABLE will reveal the fk_symbol) But why even put the foreign key in there in the first place if you're just going to violate it later? If you do that, you should have a eu* in the parent table. My understanding is that MyISAM tables are faster than InnoDB tables, but the latter offer row locking, though I don't really understand what that is. Yes. In addition to foreign keys, InnoDB offers transaction support, which is absolutely critical when dealing with larger OLTP applications. Speed does suffer though because all this Foreign Key / Transaction stuff takes lots of overhead. Putting it all together, what combination would you recommend for my project - a series of tables with data on the world's nations, states, counties and natural regions? My biggest table at present (Nations) has about 250 rows and half a dozen columns, but I'll be creating perhaps a dozen accessory tables, with data on area, population, economics, government, etc. With rows in the hundreds, InnoDB vs. MyISAM speed should be negligable. Make sure you create Indexes on your foreign key columns and you should be ok. (Indexes will speed up your queries, foreign key columns are almost always the columns used in a join condition, hence the need for an index) I'm also planning a series of tables focusing on animals - common and scientific names, diet, habitat, etc. For both of these projects, I think foreign keys would be a good choice for quality control, which would, in turn, require the use of InnoDB tables. Am I right? Yes, once you understand how they
Re: MyISAM transactions
On Fri, 14 May 2004 04:38:56 +0200 Lorderon [EMAIL PROTECTED] wrote: Will MyISAM support transactions in the future versions? Is it possible? Not at the moment, I think 5.1 will support foreign keys in MyISAM, but not sure about transactions... Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary Key
On Mon, 10 May 2004 11:15:25 -0300 Ronan Lucio [EMAIL PROTECTED] wrote: Is the Primary Key Column mandatory? Supposing: If I have two tables: Clients and Cars, and a third table Clients_R_Cars, that is a relationship between Clients and Cars. I only need to know what cars the clients have. So, I just need to two columns CliCar_ClientsID and CliCar_CarsID, the will be my index keys. Even thus do I need to create a Primary Key Column CliCar_ID? It is not mandatory to have a primary key, but you will have to handle duplicate rows in your front end program. Having primary keys is generally a good idea. In the situation that you are talking about, you can have a composite primary key (a primary key with more than one colunm) in your Clients_R_Cars table that is (Client_ID, Car_ID). This says that in your Clients_R_Cars table no two rows can have the same Client_Id AND Car_ID. Not sure if this is what you want or not. CREATE TABLE Clients_R_Cars ( Client_ID INT NOT NULL, Car_ID INT NOT NULL, PRIMARY KEY (Client_ID, Car_ID) ); If you really wanted to do this the relational way you would use InnoDB (or BDB) and use foreign keys as well. CREATE TABLE Clients_R_Cars ( Client_ID INT NOT NULL, Car_ID INT NOT NULL, INDEX client_id_ind (Client_ID), INDEX car_id_ind (Car_ID), FOREIGN KEY (Client_Id) REFERENCES Client(Client_Id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (Car_Id) REFERENCES Car(Car_Id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (Client_ID, Car_ID) ) TYPE=InnoDB; Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup strategy
On Tue, 04 May 2004 14:44:26 -0700 Ron Gilbert [EMAIL PROTECTED] wrote: Is there a better way to be doing this given the huge amount of binary data I have? You may wish to also look into replication, which is a cinch to setup with MySQL. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Html and mysql..
On Wed, 5 May 2004 09:18:01 -0700 (PDT) My Sql [EMAIL PROTECTED] wrote: Hi all, I have got one serious doubt. Can we access mysql database from the front end html. If it all it is possible, we have to right in javascript. ofcourse we can write it using JDBC(provided we have a servlet engine integrated with a webserver). My question is can we access mysql database from the front end html using javacript. I would be really glad, if some one can help me in this regard. Javascript is a client-side language, the code is executed by the user's browser. It has no way to connect to the database server and run queries so you need to use a server-side programming language like Java (JDBC), PhP, Perl, etc. Tomcat is a decent servlet engine with a nice price tag (free) and PhP/Perl are pretty easy to get working with an Apache server. If you've never done this before, I'd advise to use PhP as it has the smallest learning curve in my opnion. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Use of AVG in where clause
On Wed, 5 May 2004 19:12:00 +0200 Julien Martin [EMAIL PROTECTED] wrote: Hello, I have a sql query as follows: ** SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID WHERE AVG(DB_GRADE) 2 GROUP BY ES.DB_ESTABLISHMENT_ID ; ** and I get the following error: ERROR : Invalid use of group function Can anyone tell me how to use a function in the where clause? Filtering rows based on the results of aggregate functions is a job for the HAVING clause, try: SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID GROUP BY ES.DB_ESTABLISHMENT_ID HAVING AVG(DB_GRADE) 2; Note that HAVING should be AFTER the GROUP BY, not before it. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AVG function in order by clause
On Wed, 5 May 2004 19:41:32 +0200 Julien Martin [EMAIL PROTECTED] wrote: Hello, Thanks a lot for the replies. I have changed the query as follows: ** SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID GROUP BY ES.DB_ESTABLISHMENT_ID HAVING AVG(DB_GRADE) 2 ORDER BY AVG(GRADE) ** Now I am having a problem with the order by clause. How can I have the AVG function in the ORDER BY clause or how can I sort by average grade? I THINK you need to alias your column to do this: SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) AS AVG_GRADE FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID GROUP BY ES.DB_ESTABLISHMENT_ID HAVING AVG(DB_GRADE) 2 ORDER BY AVG_GRADE ^ Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB - Foreign Key - Error 150.
On Tue, 4 May 2004 09:46:27 -0400 Tucker, Gabriel [EMAIL PROTECTED] wrote: Hello All: I am having a problem with both V4.0.16 and 4.0.18. Let me explain: I have a database with two InnoDB tables in v4.0.16. I backup up this database every night using the following command: mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin/mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I went to restore it the other day and I got this error: ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 150) Try looking over the output of SHOW INNODB STATUS; there might be some helpful messages in there. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB - Foreign Key - Error 150.
On Tue, 4 May 2004 11:01:59 -0400 Tucker, Gabriel [EMAIL PROTECTED] wrote: Luciano I am confused... As far as I can tell, the set foreign_key_checks=0; is used with the load data infile command. Actually, I think that it is used for all operations on the DBMS, not just load data infile, INSERTS, UPDATES, DELETES as well. This is usually what I do when I have a dump created with mysqldump and need to re-create the tables. What would be nicer is if mysqldump had some smarts to dump things in the right order so this would not be an issue. Or course, the logic to do that would be kind of complex, always having to dump the weakest tables (those with foreign keys) first. :) I am not using this command to restore the database. The mysqldump command creates a file with the data and schema. I restore it to a new instance that just has the mysql database using: unix$ mysql --port=port --socket=socket -p archive.sql So, I am not sure where I would insert this line nor if it would work. Should I insert it in the archive.sql from the previous example? Can I use the load data infile to restore the file I generated? Also, in the mysqldump command I used the --disable-keys command, believing this would correct the problem. Do you know why it does not? This disables the keys during each INSERT block, but once the INSERT's are done, then it tries to enable the keys, so you will still get problems. --disable-keys is meant more as an optimization. The recommendation to set foreign_key_checks=0 at the start of the dump file, then set foreign_key_checks=1 at the end of the dump file should work just fine, has for me anyway. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
On Mon, 3 May 2004 12:39:48 -0500 Boyd E. Hemphill [EMAIL PROTECTED] wrote: My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? I believe that the relation database model specifies that the order of rows is not important in the resulting relation produced from a relational operation (Select, Project, Union, Join, etc.). Whether or not you actually get the same order on the same SELECT query run multiple times depends on the DBMS you are using. I think different DBMS's may do things in idle time to optimize table layout and perhaps re-order the data, I'm not sure about MySQL. Conceptually as an end-user, you should never assume that the DBMS will always return your data in the same order regardless of how/where it is physically stored, that's the job of the ORDER BY clause in a SELECT query. The DBMS is supposed to abstract away the details of the physical storage implementation, so when order is important, every query should have an ORDER BY to guaruntee the sort order, especially if you want to port the application. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
On Mon, 03 May 2004 13:21:56 -0500 Bob Ramsey [EMAIL PROTECTED] wrote: Ah, but the ordering is not random. As your example has it, the results are in the order that the entries were inserted into the table. There is an explanation for the order of the returned data. Conceptually, row order truely is random and meaningless in an RDBMS. In a multi-user system who cares who originally inserted the data and in what order? Just because it behaves that way now, in future versions of MySQL the developers may decide to do something completely different and change the default way rows are pulled without an ORDER BY and they certainly have that right. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers
On Fri, 30 Apr 2004 17:04:57 +0100 joe collins [EMAIL PROTECTED] wrote: Are there any plans to have triggers introduced into MySQL? version 5.1 - probably a ways off with 5.0 still alpha. http://dev.mysql.com/doc/mysql/en/ANSI_diff_Triggers.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Command for getting back an auto_increment field?
On Thu, 29 Apr 2004 12:27:46 -0300 (ART) Leandro Melo [EMAIL PROTECTED] wrote: Hi, i got a table wich its pk is an auto_increment field. I have 10 elements in this table, wich makes the pk_id field = 10. I inserted incorrectly anoter row in this table (the 11th) and imediately deleted it. Although, i'd like that the next time i insert a row in this table, it's index be still 11 (not 12 as mysql would do by default). Is there any fast command i can make to correct the table situation? ALTER TABLE your_table AUTO_INCREMENT = 11; Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transfer to a file
On Thu, 29 Apr 2004 16:05:56 -0400 (EDT) [EMAIL PROTECTED] wrote: hi, i have a huge database and i want to transfer the data to a file. can i tranfer the result of a query to a file for eg. if i say select * from tablename; it selects all the tables. but the mysql window is small to see all the records when the database has many records.can i send these records and save it in a file? http://dev.mysql.com/doc/mysql/en/SELECT.html Search for INTO OUTFILE SELECT * INTO OUTFILE '/home/me/data.txt' FROM mytable; (Note, you may have to deal with OS permission issues and you need the FILE MySQL privilege) Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JDBC ResultSet exception
On Thu, 29 Apr 2004 23:59:05 +0200 Bjoern Wuest [EMAIL PROTECTED] wrote: Dear All I work on a small application with database support. For development I use the MySQL database which is just doing fine. During testing I experienced a, in my opinion, strange bug. I have the following source code: [snip] You might have better luck with the MySQL/Java mailing list: http://lists.mysql.com/list.php?list=javasub=1 I think it's a fairly high traffic list with more Java gurus than this list. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL to store email
Does anyone know where I can read more on how to redirect incoming email (via Sendmail) into MySQL for a given email address? I understand that I can use [EMAIL PROTECTED] | /path/mysql ... in the /etc/aliases file to do this, but I would like to see if anyone has had success with this. I also would like to know if this is not as simple as I may think. I haven't used it myself, but you may want to check out: http://www.dbmail.org/ Josh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql 4 production quality
hi there i was wondering if mysql 4 will become production quality soon ? i would like to convince work to start using it, so i can start using the transactions, sub selects and query cache I don't think MySQL 4.0 has sub-selects, that's 4.1. I've been using 4.0 on two different ISPs for a few months without incident, but the apps are not very complicated... Josh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL IMAP Server
MySQL, My first post, please go easy if this is the wrong pleace! I'm trying to find an IMAP server which used MySQL so that my Mozilla Email client can file it's email a little faster. Has anybody heard of such a device? I just finished configuring courier-imap to use a MySQL table to authenticate against. Are you looking for authentication/configuration information stored in MySQL or the actual messages themselves? Josh Regards, Ben - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql, PHP and Javascript
Hi All
Is it possible to mix javascript and PHP in the same script??
Client-side Javascript can be sent to the user along with your html.
For Example:
echo (HTMLHEAD\n);
echo (SCRIPT TYPE=\text/javascript\\n);
echo (!--\n;
echo (function hello() {\n);
echo ( alert('Hi');\n);
echo (}\n);
echo (// --\n);
echo (/SCRIPT\n);
and so on...
The client is then sent:
HTMLHEAD
SCRIPT TYPE=\text/javascript\
!--
function hello() {
alear('Hi');
}
// --
/SCRIPT
Is it possible to use JavaScript calls on the server side? Don't think
so, but there is a Java extension to PhP that may help you if that's what
you're after: http://www.php.net/manual/en/ref.java.php
HTH,
Josh
I have a number of web based apps written in PHP/MySql and while they
are functionally pretty good they are aesthetically garbage. I'll like
to pretty up the interfaces with rollovers etc, but haven't got time to
learn JS properly especially if I have to completly re-write the
functionality.
Any help greatly appreciated
Steve
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Core beta test question
Anybody seen a general page or study guide for the beta test yet? There used to be a section on the MySQL site (http://www.mysql.com/training/candguide/sec-core.htm) but its 404 now and taking the test here in a couple days. Looked around on the site but the information doesn't seemed to around at all. Wouldn't mind having some idea what to expect. Peter, Your URL is just a little off: http://www.mysql.com/certification/candguide/sec-core.html http://www.mysql.com/certification/ is the home page for certification related topics. Good luck on the exam, I'm taking it too in a few days... (sql, query) Josh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Reg. Transaction support
Hello All, I would like to know the some details regarding the transaction support in Mysql Version 4. May i know where can i find the related stuffs. Transactions are supported in the current 3.23.53 release of MySQL-Max and MySQL 4 (Pro version I think) with the InnoDB table type. You will need to read up on the InnoDB table type (see http://www.mysql/doc). Basically you will need to create a my.cnf file to create some space for your InnoDB tables, then create tables in mysql with: CREATE TABLE mytable () TYPE=InnoDB; Then you can use transactions: BEGIN; INSERT INTO mytable ... ; UPDATE mytabe ... ; DELETE FROM mytable ... ; COMMIT; or ROLLBACK; www.mysql.com/doc and search for InnoDB or transaction! Have fun, Josh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Howto check whether really innoDB is used
Hi folks, I just upgraded to mysql-max-3.23.52-1 because I realized that the standard mysql does not support transactions. I still have a transaction problem with many clients reading the same data (every read causes an update on one column so that the read value should be unique in the whole cluster) in a cluster even with setting transaction isolation to TRANSACTION_SERIALIZABLE. How can I check that really innoDB is used as I read that this table will be used as MyISAM when mysql is not supporting it? Try: SHOW TABLE STATUS FROM yourDBName; InnoDB should be listed as the table type. This is also handy for seeing how much space is being used... Josh Has anyone a guess on this? I am spending loads of time on this problem... Thnx, Lutz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Features comparison
Dear Sir, Madam, I was trying to make a comparison on you website between the mysql server, acces 2000, microsoft sql server and interbase. Crash-me however does not return any information, it does not work. Is it possible that you can provide me with this information? I hope to hear from you. I was also looking at crash-me.php today, seems like this script hasn't been maintained as the version of mysql it compares other software to is 3.23.39. Anyone still working on this? I imagine it's quite a headache to maintain... Josh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there Examples and Documents on joining tables?
Is there a document that compiles examples on different ways of joining two or more tables? I found a few on http://www.mysql.com/doc/en/JOIN.html but they do not cover joining more than two tables. Thanks for any suggestions. I wish there were more examples as well! I use this for multi-table joins: SELECT table1.bunch, table2.of, table3.stuff FROM table1 INNER JOIN table2 ON table2.some_id INNER JOIN table3 ON table3.some_id WHERE table1.some_id = table2.some_id AND table1.some_id = table3.some_id; Not sure if this the best way or not, but it works... Anyone with more experience care to contribute? Are there optimum ways to join multiple tables? Josh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there Examples and Documents on joining tables?
Josh Trutwin wrote:
SELECT table1.bunch, table2.of, table3.stuff FROM table1
INNER JOIN table2 ON table2.some_id
INNER JOIN table3 ON table3.some_id
WHERE table1.some_id = table2.some_id AND table1.some_id =
table3.some_id;
Your where is sort of in the wrong place ...
SELECT table1.bunch, table2.of, table3.stuff FROM table1
INNER JOIN table2 ON table2.some_id = table1.some_id
INNER JOIN table3 ON table3.some_id = table1.some_id;
... or, for a more concrete example (if a tad contrived) ...
Odd, the example I typed was from a working, albeit slow example. Maybe
that's why the performance of that query is so dang slow.
Thanks for the examples, this is great stuff for my archives. Looks like
I have some work to do...
Josh
SELECT PhoneNumbers.Number as FaxNumber FROM Customers
LEFT JOIN CustomerPhoneNumbers ON CustomerPhoneNumbers.CustomerID =
Customers.ID
LEFT JOIN PhoneNumbers ON PhoneNumbers.ID =
CustomerPhoneNumbers.PhoneNumberID
WHERE CustomerPhoneNumbers.Type = 'fax' AND Customers.ID = '443';
It would operate on data such as: Customers: { ID = 443 },
CustomerPhoneNumbers { CustomerID = 443, PhoneNumbersID = 272, Type =
'fax'; CustomerID = 443, PhoneNumbersID = 273, Type = 'phone' },
PhoneNumbers { ID = '272', Number = 555-1212; ID = '273', Number =
555-1213 }
PS, I actually use this table structure for phone numbers, with great
success.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multi-table Join (WAS: Is there Examples and Documents on joining tables?)
Here is the query that I had in a PhP script that joins a bunch of tables (I'll try to make this look readable). If you're curious, it's for a motorcross site (www.springcreekmx.com): SELECT standings.rank, race.race_date, class.class_name, racer.racer_name, cycle.cycle_desc, race_event.racer_id, race_event.points_earned, race_event.racing_number, race_event.overall_rank, race_event.round, standings.total_points FROM race_event INNER JOIN racer ON racer.racer_id INNER JOIN standings ON standings.racer_id INNER JOIN race ON race.round INNER JOIN class ON class.class_id INNER JOIN cycle ON cycle.cycle_id WHERE race_event.racing_cycle = cycle.cycle_id AND race_event.class_id = class.class_id AND race.round = race_event.round AND race.series_id = race_event.series_id AND race.class_id = race_event.class_id AND standings.racer_id = racer.racer_id AND standings.class_id = race_event.class_id AND standings.series_id = race_event.series_id AND race_event.racer_id = racer.racer_id AND race_event.class_id = '2' ORDER BY rank, race_date; This syntax actually works, from the posts it sounds like I might be getting away with one. So, from the posts regarding table joins, this would be correctly written as: SELECT standings.rank, race.race_date, class.class_name, racer.racer_name, cycle.cycle_desc, race_event.racer_id, race_event.points_earned, race_event.racing_number, race_event.overall_rank, race_event.round, standings.total_points FROM race_event INNER JOIN racer ON racer.racer_id = race_event.racer_id INNER JOIN standings ON standings.racer_id = racer.racer_id AND standings.class_id = race_event.class_id AND standings.series_id = race_event.series_id INNER JOIN race ON race.round = race_event.round AND race.series_id = race_event.series_id AND race.class_id = race_event.class_id INNER JOIN class ON class.class_id = race_event.class_id INNER JOIN cycle ON cycle.cycle_id = race_event.racing_cycle WHERE race_event.class_id = '$class_search' ORDER BY rank, race_date; Looks much nicer, doesn't seem to be a dramatic increase in performance, but I think it makes more sense this way. Does this look ok? The size of tables (smallest to largest) is series, cycle, class, race, racer, standings, race_event. I re-ordered the INNER JOINS to join on the smallest table like so: SELECT standings.rank, race.race_date, class.class_name, racer.racer_name, cycle.cycle_desc, race_event.racer_id, race_event.points_earned, race_event.racing_number, race_event.overall_rank, race_event.round, standings.total_points FROM series INNER JOIN cycle ON cycle.cycle_id = race_event.racing_cycle INNER JOIN class ON class.class_id = race_event.class_id INNER JOIN race ON race.round = race_event.round AND race.series_id = race_event.series_id AND race.class_id = race_event.class_id INNER JOIN racer ON racer.racer_id = race_event.racer_id INNER JOIN standings ON standings.racer_id = racer.racer_id AND standings.class_id = race_event.class_id AND standings.series_id = race_event.series_id INNER JOIN race_event ON series.series_id = race_event.series_id WHERE race_event.class_id = '$class_search' ORDER BY rank, race_date The first query averages about 0.085 seconds from the mysql prompt, the second about 0.075 seconds and the 3rd 0.065 seconds. Thanks for some great advice, this has been bugging me for a while! Josh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: default date time insertion in the database
Hi,
can anyone tell me how can i set the default datetime as current
datetime in the mysql. so that every time i insert a record in the table
current datetime automaticall inserted.
Thanks in advance.
From MySQL manual section 6.5.3:
Default values must be constants. This means, for example, that you
cannot set the default for a date column to be the value of a function
such as NOW() or CURRENT_DATE.
You have a couple options though, you can use a TIMESTAMP field which will
be set to the current date/time when the data is inserted. But if the
data changes, the timestamp field is updated again, so this will only work
if you can be certain that you will not be updating the data after
insertion.
The other option is to use a DATETIME type column and use the following
insert query:
INSERT INTO mytable (col1, col2, ... , date_inserted) VALUES ('col1data',
'col2data', ... , NOW());
Josh
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: No my.cnf file on Linux?
I use mysql 3.23.4 max on linux and it have worked just fine. Now I want to use Transactions so I thougt that I should use the my.cnf to configure for that. Trouble is there is no my.cnf file on my computer. Do I have to create the file myself ? or is the InnoDB settings configured right by default? I had to create this file from scratch. The InnoDB section of the manual was a good starting point. I also want to say that I had to update my rc.d script as well to find the config file, not sure though Josh / Petrus Lindberg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MyISAM or InnoDB
We are moving a databse over from Oracle to MySQL 4.0.3 and I was wondering if I should use MyISAM or InnoDb. The databse currently has 800-900 updates and inserts done to it in a day along with about 1000 selects. Would I run in to any table locking problems with MyISAM? If you want to support transactions and/or foriegn key constraints then you have to go InnoDB. As for locking problems, I would guess not, but I'll leave that to more experienced list members. Josh Thanks in advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Basic SQL join question
SELECT * FROM Projects INNER JOIN KeywordLink ON Projects.Id = KeywordLink.Pid INNER JOIN Keywords KeywordLink.Kid = Keywords.Id WHERE Keyword LIKE '%historical%' AND Keyword like '%scenic%'; Out of curiousity, does the order matter? I have a JOIN with about 6 tables, some are very small, some are quite large. I know with LEFT JOIN if I switched the order of the tables around, the queries could speed up or slow down dramatically. Is the same true with INNER JOIN? Still trying to completely grasp JOINs, getting closer though... Also, shouldn't the second INNER JOIN have an ON keyword? Josh 1. Note the single quotes. 2. You can place the join logic in the WHERE clause but I prefer the clarity obtained by keeping it in JOIN clauses and using the WHERE only to contain the include if logic. hth, Arthur -Original Message- From: Adam Randall [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 21, 2002 5:18 AM To: [EMAIL PROTECTED] Subject: Basic SQL join question Okay, I've thought about this every way I can conceive of, but I cannot figure out the sql query logic involved in joining three tables together. Here is what I am trying to do: I have three tables: keywords keywordlink projects keywords has these fields: id keyword keywordlink has these fields: id pid kid projects has a lot of fields, but it's primary key is ID What keywords holds is the keywords used in the various different tables in the database. keywordlink associates a project with several keywords: example keywords: id | keyword 1 | landscape 2 | historical 3 | scenic example keywordlink: id | pid | kid 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 example projects: id | name 1 | example 2 | extra Now, what I am trying to do is basically search the keywords database for keyword names, and then return a list of project names that are associated with those keywords. If the keywords were stored in the projects database, this is basically what I would want it to do (assume all the keywords are stored in a field called keywords in the projects table): SELECT * FROM projects where keywords like %historical% and keywords like %scenic%; This would return to me the projects that have historical and scenic in the keywords field. Now, how do I do this same operation with it broken out like I have above. The reason I am not storing the keywords in the projects table is that it would be quite a chore in changing the keywords in the system if I did that (modify one keyword, modify all the projects, etc). Anyone have any words of advice for me? Adam. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: granting does not give the correct privileges
Is this an error in Mysql? I do the follwing command from a shell grant insert on mydb.* to username@localhost identified by user 'pwd'; When I check the permissions the insert_priv field on user table of mysql db is set to 'N'. This always gave me a lot of confusion at first. If you select * from user, you are seeing the user privs, which I interpret to mean that if you have a Y for insert you can insert globally into ANY table. You'll want to check the db table instead which shows privs based on database name and username. Josh Why is that? I can change the privileges by giving an update command. Any ideas?, Nestor :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fw: Query to update column length
Hi all, I am writing an application that requires my application to possibly update the length of a char field while data is being imported into the db. First, can I update the size/type of a column using UPDATE? Would someone be willing to give me an example? You will want to look at the ALTER TABLE syntax. If you want to change your field from CHAR(10) to CHAR(20) the syntax is: ALTER TABLE mytable MODIFY mycolumn CHAR(20); You may also need to tack on NOT NULL and/or PRIMARY KEY or other modifiers depending on your column as appropriate. Section 6.5.4 of the docs, one I have bookmarked! Josh thank you Shaun filter: mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
