Re: repercussions of reserved words
Hi! Peter Schoenster wrote: Hi, I installed an app which used option as a field. It worked fine in version 3.23 on my play box. I then moved the app to another box (4.0.15) and when I tried to dump it in (mysql -u x -px database dump_from_other_box) it balked at the use of option. I suspected reserved word and such was confirmed on the mysql site. So I moved the database directory from play box to live box. That worked. All was well. But I worry. Then, I went to the mysql site and read some more and saw a reason for those annoying backticks in phpmyadmin. I see that I could have done the original dump had I put option in backticks. So now I'm a bit confused why I should avoid reserved words (other than to avoid backticks which is enough for me but I'm curious). MySQL stores field names without backticks in FRM file, so it doesn't matter if the field otpion was created under 3.23 using word option without backticks, or under 4.0 using option with backticks. Will awful things happen to me in the future? As you're going to use option in backticks, no, awful things should not happen. Peter -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia ___/ www.mysql.com +7-912-856-80-21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Filling in data from already existing rows in table
I am parsing a file reading in information and adding it in, one row at a time, to a table (also storing filename and line number). Some of the entries in the file will simply consist of nothing more than a base item value. For these items, I want to look up this 'base item' (which should already be in the table) and fill in the missing values from it. For example, on my pass through the file, I would have (there are other values being copied besides name and id, I didn't include them here to shorten it): Itemnameid filename linenumber baseitem A itema someid somefile.txt1 (blank string - not NULL) B somefile.txt5 A I was thinking of doing some form of UPDATE with a nested select, but this is on 4.0xx MySQL, which doesn't yet support nested queries. Any suggestions? Adam Clauss [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: php temp table question (for mysql)
Larry Brown wrote: Does anyone know whether the use of persistent connections with php will allow a temp table created by a script to linger around and cause a problem with the next execution of the script when it tries to create the temp table again? You are right. There is a simple workaround though. You can use IF NOT EXISTS when creating your temp table: create temporary table if not exists t1 (a int); Also if it does present a problem with the next script execution trying to create the temp table again, if I drop the temp table at the end of the script will I still have problems if the script is run by two client in tandem? For instance two people connect, both hit the script at about the same time. One script creates the temp table and before it can drop the table the second script tries to create the table. Temporary tables are created under connection scope. There is no problem for two clients to use temporary tables with the same names. Physically, these tables are different. The second client can't see a temporary table created by the first client. Will it see the table created by the other script? Again the use of persistent connections would be a the heart of this I would think. -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia ___/ www.mysql.com +7-912-856-80-21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit Optimization??
Hi, That's good! :-) Are you saying that you need to have a WHERE clause on the first ... LIMIT 150, 20 query? Can you give an example of the full query, with the WHERE etc.? We'll see which columns can or should be added to an index. Also, can you show which indexes are already on the table? The last lines of SHOW CREATE TABLE table; with the KEY definitions is fine. Matt - Original Message - From: avenger Sent: Monday, October 27, 2003 10:24 PM Subject: Re: Limit Optimization?? good job!! it short my query time from 30 sec to 0.6 sec. IOW,now i can not use the 'where' 'order by' clause in the SELECT . can i need more indexs ? thx Matt. Matt W [EMAIL PROTECTED] wrote [EMAIL PROTECTED] Hi, Yes, MySQL stops searching for rows once the LIMIT is satisfied, as long as filesort isn't used for an ORDER BY. But your LIMIT 150, 20 will take much longer (assuming filesort isn't used) than, say, LIMIT 1000, 20. This is because it has to scan over 1.5M rows first. It's not really possible to just start at row 150. If you're not joining another table or something where it first needs to know how many rows from the table match, you can do something like this with 2 queries in your code. The first just scans the index which is much faster than scanning the data file. SELECT id FROM table ORDER BY id LIMIT 150, 20; Then take the first and last of those ids and run this query to get the other columns: SELECT * FROM table WHERE id BETWEEN @low_id AND @high_id ORDER BY id; Hope that helps. Matt - Original Message - From: avenger Sent: Monday, October 27, 2003 7:57 PM Subject: Limit Optimization?? Does mysql do any optimization for then one use `select ... limit x,y`? For example, I have table with 200 records and want to do page web interface to this table. When i use `select ... from table limit 150, 20 `, it will need more and more times (on my here is more than 60 sec). well,that is so slowly for the web interface. can any case make it quickly help,plz. Thx for all -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit Optimization??
It's my info publsih system My table structure : # # Table structure for table 'article' # CREATE TABLE article ( ArticleID int(11) NOT NULL auto_increment, ClassID int(11) NOT NULL default '0', Title varchar(100) NOT NULL default '', TitleState char(3) default '000', Digest text, Content mediumtext NOT NULL, Author varchar(20) default NULL, Today smallint(1) NOT NULL default '0', View int(6) NOT NULL default '0', Auditing smallint(1) default '0', PostUser varchar(20) default NULL, PostUserID int(11) NOT NULL default '0', AuditingUser varchar(20) default NULL, AuditingUserID int(11) NOT NULL default '0', CreatedTime int(11) default NULL, Template int(11) default NULL, PRIMARY KEY (ArticleID), KEY key1 (ClassID,Auditing,CreatedTime), KEY CreatedTime (CreatedTime), ) TYPE=MyISAM ; My query: SELECT ArticleID FROM article WHERE ClassID = 101 AND Auditing = 1 ORDER BY CreatedTime DESC LIMIT x , y I allreday created the key1 and the CreatedTime key. And it's even slowly now ... :( Avenger Matt W [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] Hi, That's good! :-) Are you saying that you need to have a WHERE clause on the first ... LIMIT 150, 20 query? Can you give an example of the full query, with the WHERE etc.? We'll see which columns can or should be added to an index. Also, can you show which indexes are already on the table? The last lines of SHOW CREATE TABLE table; with the KEY definitions is fine. Matt - Original Message - From: avenger Sent: Monday, October 27, 2003 10:24 PM Subject: Re: Limit Optimization?? good job!! it short my query time from 30 sec to 0.6 sec. IOW,now i can not use the 'where' 'order by' clause in the SELECT . can i need more indexs ? thx Matt. Matt W [EMAIL PROTECTED] wrote [EMAIL PROTECTED] Hi, Yes, MySQL stops searching for rows once the LIMIT is satisfied, as long as filesort isn't used for an ORDER BY. But your LIMIT 150, 20 will take much longer (assuming filesort isn't used) than, say, LIMIT 1000, 20. This is because it has to scan over 1.5M rows first. It's not really possible to just start at row 150. If you're not joining another table or something where it first needs to know how many rows from the table match, you can do something like this with 2 queries in your code. The first just scans the index which is much faster than scanning the data file. SELECT id FROM table ORDER BY id LIMIT 150, 20; Then take the first and last of those ids and run this query to get the other columns: SELECT * FROM table WHERE id BETWEEN @low_id AND @high_id ORDER BY id; Hope that helps. Matt - Original Message - From: avenger Sent: Monday, October 27, 2003 7:57 PM Subject: Limit Optimization?? Does mysql do any optimization for then one use `select ... limit x,y`? For example, I have table with 200 records and want to do page web interface to this table. When i use `select ... from table limit 150, 20 `, it will need more and more times (on my here is more than 60 sec). well,that is so slowly for the web interface. can any case make it quickly help,plz. Thx for all -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit Optimization??
Hi, OK, did you just create key1 and CreatedTime? If you didn't have them before and don't need them, you can remove them and just create this index for your query (you can definitely replace key1 with this): ALTER TABLE article ADD INDEX (ClassID, Auditing, CreatedTime, ArticleID); And then the query should only use the index for execution. Then you can of course run the second query to get all columns you want: SELECT * FROM article WHERE ArticleID IN (Comma seperated list of ArticleIDs from first query) ORDER BY CreatedTime; Hope that helps! Matt - Original Message - From: avenger Sent: Tuesday, October 28, 2003 1:37 AM Subject: Re: Limit Optimization?? It's my info publsih system My table structure : # # Table structure for table 'article' # CREATE TABLE article ( ArticleID int(11) NOT NULL auto_increment, ClassID int(11) NOT NULL default '0', Title varchar(100) NOT NULL default '', TitleState char(3) default '000', Digest text, Content mediumtext NOT NULL, Author varchar(20) default NULL, Today smallint(1) NOT NULL default '0', View int(6) NOT NULL default '0', Auditing smallint(1) default '0', PostUser varchar(20) default NULL, PostUserID int(11) NOT NULL default '0', AuditingUser varchar(20) default NULL, AuditingUserID int(11) NOT NULL default '0', CreatedTime int(11) default NULL, Template int(11) default NULL, PRIMARY KEY (ArticleID), KEY key1 (ClassID,Auditing,CreatedTime), KEY CreatedTime (CreatedTime), ) TYPE=MyISAM ; My query: SELECT ArticleID FROM article WHERE ClassID = 101 AND Auditing = 1 ORDER BY CreatedTime DESC LIMIT x , y I allreday created the key1 and the CreatedTime key. And it's even slowly now ... :( Avenger Matt W [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] Hi, That's good! :-) Are you saying that you need to have a WHERE clause on the first ... LIMIT 150, 20 query? Can you give an example of the full query, with the WHERE etc.? We'll see which columns can or should be added to an index. Also, can you show which indexes are already on the table? The last lines of SHOW CREATE TABLE table; with the KEY definitions is fine. Matt - Original Message - From: avenger Sent: Monday, October 27, 2003 10:24 PM Subject: Re: Limit Optimization?? good job!! it short my query time from 30 sec to 0.6 sec. IOW,now i can not use the 'where' 'order by' clause in the SELECT . can i need more indexs ? thx Matt. Matt W [EMAIL PROTECTED] wrote [EMAIL PROTECTED] Hi, Yes, MySQL stops searching for rows once the LIMIT is satisfied, as long as filesort isn't used for an ORDER BY. But your LIMIT 150, 20 will take much longer (assuming filesort isn't used) than, say, LIMIT 1000, 20. This is because it has to scan over 1.5M rows first. It's not really possible to just start at row 150. If you're not joining another table or something where it first needs to know how many rows from the table match, you can do something like this with 2 queries in your code. The first just scans the index which is much faster than scanning the data file. SELECT id FROM table ORDER BY id LIMIT 150, 20; Then take the first and last of those ids and run this query to get the other columns: SELECT * FROM table WHERE id BETWEEN @low_id AND @high_id ORDER BY id; Hope that helps. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
please help out
Dear all, Hi, i have a database called 'admin' and a table called 'user'. Inside table 'user', there are a lot of fields, i.e: name, email, race, state, and etc. There are totally 10 records. When I wrote the sql query 'select * from user where race='chinese' and state='US''. It shows me more than 1000 results. Now, how can I save only these 1000++ data into a file in my pc? Please help out. Thanks. best regards, Pey Ling
Re: please help out
Easy, use:'select * INTO OUTFILE full path and file name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY \n from user where race='chinese' and state='US'Nitin- Original Message - From: Pey Ling [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:51 PM Subject: please help out Dear all, Hi, i have a database called 'admin' and a table called 'user'. Inside table 'user', there are a lot of fields, i.e: name, email, race, state, and etc. There are totally 10 records. When I wrote the sql query 'select * from user where race='chinese' and state='US''. It shows me more than 1000 results. Now, how can I save only these 1000++ data into a file in my pc? Please help out. Thanks. best regards, Pey Ling -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Log file format?
If this is documented anywhere, I sure can't find it, so -- The query log fields are headed: Time, Id, Command, Argument. What is 'Id'? TIA! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alternate syntax for UNION?
2003-10-26 kl. 16.56 skrev Victor Spng Arthursson: Desperately need some way to get around the fact that UNION is not possible to use on mysql prior to version 4 Was just about to launch a site when it showed up that the hosting provider was running version 3. Help! Come on now - don't make me change to postgreSQL ;) /.v -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: php temp table question
Hi Larry, I was wondering a similar thing, but for JDBC using connection pooling. In your example you say that 2 people will hit the script at the same time; if they are using independent connections this should cause no problems since TEMPORARY tables are local to the connection so don't interfere. My worry is that when using connection pooling, if I create temporary tables without dropping them and then return the connection to the pool, if the connection then gets reused, the temporary table will still exist and cause problems. Clearly dropping the temporary table is needed, although I don't want to give the user in question DROP TABLES priveledge, and there is no such priveledge just for temporary tables :( I'm not sure how the database connection gets 'reset' without being terminated and then reconnecting. Thanks, Mike -Original Message- From: Larry Brown [mailto:[EMAIL PROTECTED] Sent: 27 October 2003 21:44 To: MySQL List Subject: RE: php temp table question Thanks, I got the answer from a php developer. In case anyone is wondering, according to him the table is dropped at the end of the script execution regardless of whether you use persistent connections or not. -Original Message- From: Larry Brown [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:04 PM To: MySQL List Subject: php temp table question Does anyone know whether the use of persistent connections with php will allow a temp table created by a script to linger around and cause a problem with the next execution of the script when it tries to create the temp table again? Also if it does present a problem with the next script execution trying to create the temp table again, if I drop the temp table at the end of the script will I still have problems if the script is run by two client in tandem? For instance two people connect, both hit the script at about the same time. One script creates the temp table and before it can drop the table the second script tries to create the table. Will it see the table created by the other script? Again the use of persistent connections would be a the heart of this I would think. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alternate syntax for UNION?
Hi Victor, Desperately need some way to get around the fact that UNION is not possible to use on mysql prior to version 4 Was just about to launch a site when it showed up that the hosting provider was running version 3. Help! Come on now - don't make me change to postgreSQL ;) :-) ... or Firebird :-) Anyway, perhaps you can get around it by using a MERGE table? (do note that my real-life MySQL knowledged is ... well, not a lot) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE FUNCTION problem
Matt, Thanks for your reply. It helped me to find out the reason of problem. Fortunately the reason was so simple: my /etc/ld.so.conf file contained path /usr/lib/mysql and I thought /sbin/ldconfig would build the cache so mysql will be able to see /usr/lib/mysql/ntp2timestamp.so, but it didn't. I have renamed my .so file to libntp2timestamp.so and it worked. Seems, ldconfig ignores libraries in untrusted directories which do not start with lib prefix. I not sure why it is done this way, but oh well... now it works perfectly. I was sure it should work with --with-mysqld-ldflags=-all-static configuration option used and seems I was right. Anyway thanks for your time and your help. Really appreciate it Best Regards, Matt W wrote: Hi George, I think the MySQL-Max RPM is dynamically linked (all -max binaries actually) if you want to give it a try. Hope that helps. Matt - Original Message - From: George Chelidze Sent: Monday, October 27, 2003 9:18 AM Subject: CREATE FUNCTION problem Hello, I have created new udf function which converts time from NTP format to timestamp. I compile it with the following command: gcc -Wall -shared -o ntp2timestamp.so ntp2timestamp.cc with no errors. Then I copy this file to /usr/local/mysql (libmysql* files are located here and /etc/ld.so.conf file contains this path as well) and execute the following under mysql: CREATE FUNCTION ntp2timestamp RETURNS STRING SONAME ntp2timestamp.so; and I get an error: ERROR 1126: Can't open shared library 'ntp2timestamp.so' (errno: 22 ntp2timestamp.so: cannot open shared object file: No such file o) MySQL is installed from RPM and I have found that it might be configured with --with-mysqld-ldflags=-all-static instead of --withmysqld-ldflags=-rdynamic and I dought this is the problem but I ahve also found the following sentence in manual: -- cut here -- For mysqld to be able to use UDF functions, you should con gure MySQL with --withmysqld- ldflags=-rdynamic The reason is that to on many platforms (including Linux) you can load a dynamic library (with dlopen()) from a static linked program, which you would get if you are using --with-mysqld-ldflags=-all-static If you want to Chapter 9: Extending MySQL 559 use an UDF that needs to access symbols from mysqld (like the methaphone example in `sql/udf_example.cc' that uses default_charset_info), you must link the program with -rdynamic (see man dlopen). -- cut here -- so is it nessesary to configure it with --with-mysqld-ldflags=-rdynamic? I'd like it to be installed from RPM rather source code, is there another workaround? Thanks in advance. Best Regards, -- George Chelidze -- George Chelidze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alternate syntax for UNION?
you can use temporary tables if need to work on the resultset else use two different queries. I think, that's the only option. Nitin - Original Message - From: Victor Spng Arthursson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 2:59 PM Subject: Re: alternate syntax for UNION? 2003-10-26 kl. 16.56 skrev Victor Spng Arthursson: Desperately need some way to get around the fact that UNION is not possible to use on mysql prior to version 4 Was just about to launch a site when it showed up that the hosting provider was running version 3. Help! Come on now - don't make me change to postgreSQL ;) /.v -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help out
Hi, how to write this :full path and file name? can i copy it to my local pc rather than the server? best regards, Pey Ling From: Nitin [EMAIL PROTECTED] To: Pey Ling [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 5:09 PM Subject: Re: please help out Easy, use:'select * INTO OUTFILE full path and file name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY \n from user where race='chinese' and state='US'Nitin- Original Message - From: Pey Ling [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:51 PM Subject: please help out Dear all, Hi, i have a database called 'admin' and a table called 'user'. Inside table 'user', there are a lot of fields, i.e: name, email, race, state, and etc. There are totally 10 records. When I wrote the sql query 'select * from user where race='chinese' and state='US''. It shows me more than 1000 results. Now, how can I save only these 1000++ data into a file in my pc? Please help out. Thanks. best regards, Pey Ling -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help out
no, it'll save it in the data directory of the database only, reason is mysql doesn't have permission to write to any other directory. full path and file name could be just file name like data.txt or full path of that directory like /var/lib/mysql/db_name/data.txt Nitin - Original Message - From: Pey Ling [EMAIL PROTECTED] To: Nitin [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 3:31 PM Subject: Re: please help out Hi, how to write this :full path and file name? can i copy it to my local pc rather than the server? best regards, Pey Ling From: Nitin [EMAIL PROTECTED] To: Pey Ling [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 5:09 PM Subject: Re: please help out Easy, use:'select * INTO OUTFILE full path and file name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY \n from user where race='chinese' and state='US'Nitin- Original Message - From: Pey Ling [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:51 PM Subject: please help out Dear all, Hi, i have a database called 'admin' and a table called 'user'. Inside table 'user', there are a lot of fields, i.e: name, email, race, state, and etc. There are totally 10 records. When I wrote the sql query 'select * from user where race='chinese' and state='US''. It shows me more than 1000 results. Now, how can I save only these 1000++ data into a file in my pc? Please help out. Thanks. best regards, Pey Ling -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: please help out
Now, how can I save only these 1000++ data into a file in my pc? Normaly you should get the data into a file with that something you sent the query to mysql with. What is that something in your case? Regards, TomH -- PROSOFT EDV-Lösungen GmbH Co. KG Geschäftsführer: Axel-Wilhelm Wegmann AG Regensburg HRA 6608 USt.183/68311 Verwaltung : 93053 Regensburg, Stadlerstraße 13 office : 93049 Regensburg, Ladehofstraße 28 www: http://www.proSoft-Edv.de email : [EMAIL PROTECTED] phone : +49 941 / 78 88 7 - 121 fax: +49 941 / 78 88 7 - 20 cellphone : +49 174 / 41 94 97 0 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: reproducible error 17
On Tue, 2003-10-28 at 00:15, Guilhem Bichot wrote: On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote: So the conclusion is: unfortunately, the symlink support in MySQL was not designed for synonyming, as far as DDL (Data Definition Language - CREATE TABLE / DROP TABLE / ALTER TABLE) commands are concerned. It was designed with the thought that symlinks are to be used to point to a *different* directory (another partition where there is more room, or another device to balance disk load). For DDL commands MySQL always expects a table to exist only once, i.e. to have only one name. Putting, in the database directory, a symlink and the real table means giving 2 names to one table... I will add a note about this into our manual soon. I understand this is is an inconvenience for you; you will be safe if you always do the DDL commands (ALTER TABLE, in your case) on the real table. It's ok to do DML commands (INSERT/DELETE/UPDATE/LOADDATA, which fortunately occur much more often than ALTER TABLE normally) on both tables indifferently. Sorry, I should have been more accurate in the last sentence. It's ok to do DML commands *always* on the real table OR *always* on the synonym table. If thread1 uses the real table's name, and thread 2 uses the synonym, the query cache can be fooled: - set global query_cache_size=100; - connection1: select * from tbl_; - connection2: insert into tbl values(1); - connection1: select * from tbl_; you don't see the inserted row! - connection1: flush tables (empties caches); select * from tbl_; you see the inserted row! Even if you disable the query cache, I am not sure if it's safe to use both names; there could be some other fooled caches in MySQL. Simply put, things go wild when the real name and the synonym are both used. Which impacts the interest of using synonyms (hum). And FLUSH TABLES is a remedy. I'll add this to the manual. Regards, Guilhem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment with FOREIGN KEY UPDATE CASCADE courses Lost connection to MySQL server
vinita vigine MURUGIAH [EMAIL PROTECTED] wrote: Hello, I'm using ver 4.0.12, checked for bugs in ver 4.0.12(http://bugs.mysql.com/search.php) but couldn't find this one. Thanks for report, but I wasn't able to repeat Lost connection error on v4.0.16. Many bugs were fixed since that time. Upgrade MySQL server to the 4.0.16. [skip] CREATE TABLE software ( softwareID CHAR(20) NOT NULL, softwareName CHAR(100), softwareVers CHAR(20), installedDate DATE, softwareSource BLOB, softwareNote BLOB, localMods BLOB, PRIMARY KEY (softwareID) ) TYPE=INNODB; CREATE TABLE software_machineOSs ( softwareID CHAR(20) NOT NULL, id INT(2) unsigned zerofill NOT NULL auto_increment, osName CHAR(20), osRevision CHAR(20), INDEX (softwareID), FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON DELETE CASCADE ON UPDATE CASCADE, KEY(id), PRIMARY KEY (softwareID, id) ) TYPE=INNODB; insert into software (softwareID,softwareName,softwareVers,installedDate,softwareSource) values (vim-1.1, vim, 1.1, NOW(), www.test.com.au); insert into software_machineOSs (softwareID,osName,osRevision) values (vim-1.1, sun, 8); mysql select * from software; ysql select * from software; ++--+--+---+-+--+---+ | softwareID | softwareName | softwareVers | installedDate | softwareSource | softwareNote | localMods | ++--+--+---+-+--+---+ | vim-1.1| vim | 1.1 | 2003-10-28| www.test.com.au | NULL | NULL | ++--+--+---+-+--+---+ 1 row in set (0.00 sec) mysql select * from software_machineOSs; +++++ | softwareID | id | osName | osRevision | +++++ | vim-1.1| 01 | sun| 8 | +++++ 1 row in set (0.00 sec) mysql update software set softwareID=vi-3 where softwareID=vim-1.1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from software; ++--+--+---+-+--+---+ | softwareID | softwareName | softwareVers | installedDate | softwareSource | softwareNote | localMods | ++--+--+---+-+--+---+ | vi-3 | vim | 1.1 | 2003-10-28| www.test.com.au | NULL | NULL | ++--+--+---+-+--+---+ 1 row in set (0.00 sec) mysql select * from software_machineOSs; ERROR 2013: Lost connection to MySQL server during query mysql select * from software_machineOSs; ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id:1 Current database: dept_db ERROR 2013: Lost connection to MySQL server during query -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bit of help? root@localhost denied
Hello, I've reinstalled my system (Mac OS 10.3 Server). In trying to get MySQL going again. I can run mysql_install_db, but I can't access the mysql db and I can not set a root user password. If I try to login: mysql -u root it says access denied to [EMAIL PROTECTED] If I simply type mysql (no quotes) I get into MySQL, but cannot affect the MySQL db. I seem to remember that perhaps the answer is to chown -R on MySQL's data files, but I'm not sure where/which those are! I have the following: /var/mysql/ (2 folders are in there 'test' and 'mysql', among a few other files -- that must be the mysql db?); I also have /usr/share/mysql/ (in here are the .cnf files, e.g., my-huge.cnf, et al). I do not see a folder called data anywhere, or I haven't found it. :-( HELP, please? Ted R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: reproducible error 17
On Tue, 2003-10-28 at 12:07, Guilhem Bichot wrote: On Tue, 2003-10-28 at 00:15, Guilhem Bichot wrote: On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote: So the conclusion is: unfortunately, the symlink support in MySQL was not designed for synonyming, as far as DDL (Data Definition Language - CREATE TABLE / DROP TABLE / ALTER TABLE) commands are concerned. It was designed with the thought that symlinks are to be used to point to a *different* directory (another partition where there is more room, or another device to balance disk load). For DDL commands MySQL always expects a table to exist only once, i.e. to have only one name. Putting, in the database directory, a symlink and the real table means giving 2 names to one table... I will add a note about this into our manual soon. I understand this is is an inconvenience for you; you will be safe if you always do the DDL commands (ALTER TABLE, in your case) on the real table. It's ok to do DML commands (INSERT/DELETE/UPDATE/LOADDATA, which fortunately occur much more often than ALTER TABLE normally) on both tables indifferently. Sorry, I should have been more accurate in the last sentence. It's ok to do DML commands *always* on the real table OR *always* on the synonym table. If thread1 uses the real table's name, and thread 2 uses the synonym, the query cache can be fooled: - set global query_cache_size=100; - connection1: select * from tbl_; - connection2: insert into tbl values(1); - connection1: select * from tbl_; you don't see the inserted row! - connection1: flush tables (empties caches); select * from tbl_; you see the inserted row! Even if you disable the query cache, I am not sure if it's safe to use both names; there could be some other fooled caches in MySQL. Simply put, things go wild when the real name and the synonym are both used. Which impacts the interest of using synonyms (hum). And FLUSH TABLES is a remedy. I'll add this to the manual. Added. You should be able to see it in our online manual www.mysql.com/doc (end of section Using symbolic links) in the next hours. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP TEMORARY TABLE
TEMPORARY TABLES are suppose to work this way. They are only there during a connection and then dropped when the connection to the database is closed. One thing they are great for is when you have temporary information that needs to be kept and worked with. One of my programs has to pull things from other tables and I put them into a temporary table where I can do things with this information easier than I could by putting them into variables. It makes sorts much easier and the information is only used to generate the pages that a client is viewing. Instead of having a table for every client that has to be deleted afterwards, I use a temporary table. You also don't have to have the table be a different name for each client that is connecting because only the connection that created it can see it. So if 5 people access my page at the same time and each have thier own population table, it is not shared so the information can/will be different for each of them. Before I started using temporary tables, I would have my script come up with a random name for the table. Problem was that if the client killed the connection before the table could be dropped, it would stay in the database until I manually dropped it. In my opinion, this was the biggest benefit of TEMPORARY TABLES. Steve At 02:58 PM 10/26/2003, you wrote: Hi there, I'm trying to restrict the access that a Tomcat server has to mysql 4.0.15-standard-log database server. Unfortunately some of the queries use temporary tables, so I've had to GRANT CREATE TEMPORARY TABLES to the user. However, since the server is using connection pooling, it also DROPS the temporary tables after the queries have completed. I'd really like to be able to GRANT DROP TEMPORARY TABLES, but this doesn't seem to exist, so I've got to GRANT DROP - which feels a less safe to me :( Is there a better way of doing this that someone has found before, or is there a way to grant DROPS of the temporary table? Many Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: php temp table question
At 03:04 PM 10/27/2003, you wrote: Does anyone know whether the use of persistent connections with php will allow a temp table created by a script to linger around and cause a problem with the next execution of the script when it tries to create the temp table again? It won't. If the client looses its connection, the temp table will disappear. Also if it does present a problem with the next script execution trying to create the temp table again, if I drop the temp table at the end of the script will I still have problems if the script is run by two client in tandem? It will drop itself if the client looses their connection. You can have many people using a temporary table called problems and the table will be different for each client. It is only used for that connection and can NOT be shared by other clients. Each has their own. For instance two people connect, both hit the script at about the same time. One script creates the temp table and before it can drop the table the second script tries to create the table. Will it see the table created by the other script? Neither peoples connection will ever see the others temporary table and so both people will have the same name for a temporary table, but they would have their own, not a shared table like a permanent one that is in the database. Again the use of persistent connections would be a the heart of this I would think. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to MySQL server on '[server]' (10061) - unix tcp/ip sockets
Ben Darlow [EMAIL PROTECTED] wrote: I've recently set up a new linux box and installed MySQL 4 (for the record, Knoppix/Debian and the 'unstable' MySQL 4 package). I've had no problems connecting using phpMyAdmin, but when I try to connect remotely using MySQL Control Center or SQLyog (from Windows) I get the same error (near enough) from both: SQLyog: --- SQLyog --- Error No. 2003 Can't connect to MySQL server on '[server]' (10061) --- MySQL Control Center: [server] ERROR 2003: Can't connect to MySQL server on '[server]' (10061) Searching through the mysql.com documentation led me to http://www.mysql.com/doc/en/Can_not_connect_to_server.html, which talks about unix and tcp/ip sockets. Running mysqladmin version gives me the following output: mysqladmin Ver 8.40 Distrib 4.0.13, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.13-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 2 hours 6 min 24 sec Threads: 3 Questions: 450 Slow queries: 0 Opens: 16 Flush tables: 1 Open tables: 5 Queries per second avg: 0.059 Since the documentation (I forget where) showed the version output showing the tcp port the server was running on (and mine doesn't), I am led to believe the server isn't running on a tcp/ip socket. From what I can tell though, my.cnf does have the necessary lines for doing this - port=3306 (as per default) is in there, uncommented-out. Can anyone suggest what I might need to change in my configuration to enable tcp/ip connections? All the search results I've seen so far appear only to relate to people running MySQL (server) under windows... Did you run MySQL server with --skip-networking option? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DROP TEMORARY TABLE
Hi Steve, Thanks for your reply. I agree that TEMPORARY TABLES are really great, and I'm using them in a similar way to your description, and I think I understand their purpose. My problem, that so far no one has been able to answer, is that I'm using connection pooling with the Tomcat server. As far as I understand, this means that you don't create a connection to the server to do some processing then close it. Instead you get a connection from a pool, use it, then return it to the pool, and all connections appear as the same user to MySQL (i.e. same user, password and host). So my worry is this scenario: TransactionA gets connection A from the pool. TransactionA creates a temporary table for some query. TransactionA is done, and returns the connection to the pool. TransactionB gets a connection from the pool, which just so happens to be connection A. TransactionB tries to create a temporary table with the same name as the one that already exists. ** BANG!! ** TransactionA and TransactionB were trying to use the same temporary table on the same connection. This is why I think there should be a DROP TEMPORARY TABLE priveledge so that I can let the Tomcat server drop the temporary tables, but without having to give it DROP TABLE priveledge which is a lot more worrying. Note I'm cross posting this into the Java list too, incase I'm wrong about my understanding of pooling and someone there can correct me :) Many Thanks, Mike -Original Message- From: Steve Buehler [mailto:[EMAIL PROTECTED] Sent: 28 October 2003 11:37 To: Michael McTernan; mysql Subject: Re: DROP TEMORARY TABLE TEMPORARY TABLES are suppose to work this way. They are only there during a connection and then dropped when the connection to the database is closed. One thing they are great for is when you have temporary information that needs to be kept and worked with. One of my programs has to pull things from other tables and I put them into a temporary table where I can do things with this information easier than I could by putting them into variables. It makes sorts much easier and the information is only used to generate the pages that a client is viewing. Instead of having a table for every client that has to be deleted afterwards, I use a temporary table. You also don't have to have the table be a different name for each client that is connecting because only the connection that created it can see it. So if 5 people access my page at the same time and each have thier own population table, it is not shared so the information can/will be different for each of them. Before I started using temporary tables, I would have my script come up with a random name for the table. Problem was that if the client killed the connection before the table could be dropped, it would stay in the database until I manually dropped it. In my opinion, this was the biggest benefit of TEMPORARY TABLES. Steve At 02:58 PM 10/26/2003, you wrote: Hi there, I'm trying to restrict the access that a Tomcat server has to mysql 4.0.15-standard-log database server. Unfortunately some of the queries use temporary tables, so I've had to GRANT CREATE TEMPORARY TABLES to the user. However, since the server is using connection pooling, it also DROPS the temporary tables after the queries have completed. I'd really like to be able to GRANT DROP TEMPORARY TABLES, but this doesn't seem to exist, so I've got to GRANT DROP - which feels a less safe to me :( Is there a better way of doing this that someone has found before, or is there a way to grant DROPS of the temporary table? Many Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User and permissions/grants - HELP!
i dont think there's any privilege called ALL PRIVILEGES. You need to say just ALL, like: GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY '12345678' WITH GRANT OPTION; GRANT ALL ON *.* TO newuser@% IDENTIFIED BY '12345678' WITH GRANT OPTION; Maybe you should read the documentation before saying such nonsense? There's no ALL, but of course the ALL PRIVILEGES. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bit of help? root@localhost denied
[EMAIL PROTECTED] wrote: I've reinstalled my system (Mac OS 10.3 Server). In trying to get MySQL going again. I can run mysql_install_db, but I can't access the mysql db and I can not set a root user password. If I try to login: mysql -u root it says access denied to [EMAIL PROTECTED] Do you use old MySQL data dir with old privilege tables? If so, mysql_install_db does nothing and you should use old root password. If I simply type mysql (no quotes) I get into MySQL, but cannot affect the MySQL db. I seem to remember that perhaps the answer is to chown -R on MySQL's data files, but I'm not sure where/which those are! I have the following: /var/mysql/ (2 folders are in there 'test' and 'mysql', among a few other files -- that must be the mysql db?); I also have /usr/share/mysql/ (in here are the .cnf files, e.g., my-huge.cnf, et al). I do not see a folder called data anywhere, or I haven't found it. :-( -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DROP TEMORARY TABLE
As far as I know, you aren't going to have a problem. But I am not familiar with connection pooling with Tomcat, so I can't say for sure on this subject. You might just have to do some testing to see if they share the temp table or not. Steve At 05:51 AM 10/28/2003, you wrote: Hi Steve, Thanks for your reply. I agree that TEMPORARY TABLES are really great, and I'm using them in a similar way to your description, and I think I understand their purpose. My problem, that so far no one has been able to answer, is that I'm using connection pooling with the Tomcat server. As far as I understand, this means that you don't create a connection to the server to do some processing then close it. Instead you get a connection from a pool, use it, then return it to the pool, and all connections appear as the same user to MySQL (i.e. same user, password and host). So my worry is this scenario: TransactionA gets connection A from the pool. TransactionA creates a temporary table for some query. TransactionA is done, and returns the connection to the pool. TransactionB gets a connection from the pool, which just so happens to be connection A. TransactionB tries to create a temporary table with the same name as the one that already exists. ** BANG!! ** TransactionA and TransactionB were trying to use the same temporary table on the same connection. This is why I think there should be a DROP TEMPORARY TABLE priveledge so that I can let the Tomcat server drop the temporary tables, but without having to give it DROP TABLE priveledge which is a lot more worrying. Note I'm cross posting this into the Java list too, incase I'm wrong about my understanding of pooling and someone there can correct me :) Many Thanks, Mike -Original Message- From: Steve Buehler [mailto:[EMAIL PROTECTED] Sent: 28 October 2003 11:37 To: Michael McTernan; mysql Subject: Re: DROP TEMORARY TABLE TEMPORARY TABLES are suppose to work this way. They are only there during a connection and then dropped when the connection to the database is closed. One thing they are great for is when you have temporary information that needs to be kept and worked with. One of my programs has to pull things from other tables and I put them into a temporary table where I can do things with this information easier than I could by putting them into variables. It makes sorts much easier and the information is only used to generate the pages that a client is viewing. Instead of having a table for every client that has to be deleted afterwards, I use a temporary table. You also don't have to have the table be a different name for each client that is connecting because only the connection that created it can see it. So if 5 people access my page at the same time and each have thier own population table, it is not shared so the information can/will be different for each of them. Before I started using temporary tables, I would have my script come up with a random name for the table. Problem was that if the client killed the connection before the table could be dropped, it would stay in the database until I manually dropped it. In my opinion, this was the biggest benefit of TEMPORARY TABLES. Steve At 02:58 PM 10/26/2003, you wrote: Hi there, I'm trying to restrict the access that a Tomcat server has to mysql 4.0.15-standard-log database server. Unfortunately some of the queries use temporary tables, so I've had to GRANT CREATE TEMPORARY TABLES to the user. However, since the server is using connection pooling, it also DROPS the temporary tables after the queries have completed. I'd really like to be able to GRANT DROP TEMPORARY TABLES, but this doesn't seem to exist, so I've got to GRANT DROP - which feels a less safe to me :( Is there a better way of doing this that someone has found before, or is there a way to grant DROPS of the temporary table? Many Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4 problem, more than max_user_connections
Hello all, We recently upgraded from mysql 3.23 to 4.0 just a week ago. It all went very well, and the performance gains have been simply incredible! But now the server has started to act strangely. Every now and then, usually every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem. Does anyone know of any solution to this? Regards, -- Henrik Skotth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User and permissions/grants - HELP!
I accept, that i was wrong, but you need to have a better look too. ALL is as good as ALL PRIVILEGES. - Original Message - From: Patrik Fimml [EMAIL PROTECTED] To: Nitin [EMAIL PROTECTED]; Victoria Reznichenko [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 6:23 PM Subject: Re: User and permissions/grants - HELP! i dont think there's any privilege called ALL PRIVILEGES. You need to say just ALL, like: GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY '12345678' WITH GRANT OPTION; GRANT ALL ON *.* TO newuser@% IDENTIFIED BY '12345678' WITH GRANT OPTION; Maybe you should read the documentation before saying such nonsense? There's no ALL, but of course the ALL PRIVILEGES. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why do indices crash
Hi can anybody tell me the most common reason WHY an index file get's corrupted? I have tested this on differernt versions of MySQL (3.23.37 - 4.1) on Linux and on Windows. It happens on tables containing A MEDIUMTEXT or LONGTEXT column, A fulltext index on these columns I am mostly working with a PHP CMS that uses a DHTML component. Meanwhile I did the following workaround: As soon as the error occurs, I send REPAIR TABLE TableName USE_FRM and redo the halted query. This works fine, but is somehow. %§$= yours Herwig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA warnings
mysql LOAD DATA LOCAL INFILE './2003-01/MKR_OCAK.txt' INTO TABLE quantis_mkr; Query OK, 271392 rows affected (4 min 2.95 sec) Records: 271392 Deleted: 0 Skipped: 0 Warnings: 61 How can I see those warnings? I checked the error log but nothing shows up there. From version 4.1.1 you can use SHOW WARNINGS command: http://www.mysql.com/doc/en/SHOW_WARNINGS.html What about earlier versions? I'm using 4.0.13 Can I see those warnings in 4.0.13? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreigner keys in MySQL?
Hello all, I'm joined to a research project and by now we need to specify a software to build a database. Although I don't have so much information about this kind of software, I've been considered MySQL a good option, as it's an open source database and this project has been developed in a public university. Nevertheless, I received the information that MySQL don't support relacional functions between tables of the same database. I'm not sure about this, and I'd like to confirm this information. Does MySQL allow relacionl functions between tables recognizing foreign keys? Excuse me for asking a so simple question, -- _ Juliana Gubert Ehrensperger Federal Univesity of Santa Catarina Brazil _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT CREATE TEMPORARY TABLES
Ok. Someone else was talking about this earlier in the list, but I didn't see the answer that I am looking for. I have just setup a new RedHat 9.0 server with MySQL 3.23.58. My program that worked on an older version of MySQL doesn't work on this one. The problem is that the temporary tables are not being created. How can I turn on the GRANT function so that each of the users that have databases can create temporary tables in their own databases ONLY. Can this be done with a GLOBAL type of grant? If so, what would be the Grant statement to turn this on? Or will I have to define this each time a user us setup? If so, what would be the Grant statement to turn this on this way? The docs did show a little about this, but not this specifically (that I found) and since I am new to this type of grant, I don't want to screw things up. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreigner keys in MySQL?
Hi, If you mean foreign key constraints, it's available with InnoDb tables under MySQL, as are atomic transactions (commit and rollback). I'm using this with MySQL4.0 and it works a treat :) Thanks, Mike -Original Message- From: Juliana Gubert Ehrensperger [mailto:[EMAIL PROTECTED] Sent: 28 October 2003 14:50 To: SQL List Subject: Foreigner keys in MySQL? Hello all, I'm joined to a research project and by now we need to specify a software to build a database. Although I don't have so much information about this kind of software, I've been considered MySQL a good option, as it's an open source database and this project has been developed in a public university. Nevertheless, I received the information that MySQL don't support relacional functions between tables of the same database. I'm not sure about this, and I'd like to confirm this information. Does MySQL allow relacionl functions between tables recognizing foreign keys? Excuse me for asking a so simple question, -- _ Juliana Gubert Ehrensperger Federal Univesity of Santa Catarina Brazil _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreigner keys in MySQL?
Yes it does. - Original Message - From: Juliana Gubert Ehrensperger To: SQL List Sent: Tuesday, October 28, 2003 8:50 AM Subject: Foreigner keys in MySQL? Hello all, I'm joined to a research project and by now we need to specify a software to build a database. Although I don't have so much information about this kind of software, I've been considered MySQL a good option, as it's an open source database and this project has been developed in a public university. Nevertheless, I received the information that MySQL don't support relacional functions between tables of the same database. I'm not sure about this, and I'd like to confirm this information. Does MySQL allow relacionl functions between tables recognizing foreign keys? Excuse me for asking a so simple question, -- _ Juliana Gubert Ehrensperger Federal Univesity of Santa Catarina Brazil _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Unable to Retrieve HTML Form Values(test1.html) from a PHP Page(test1.php)
Dear Sir Our Site is running on Linux/Apache/PHP/MySQL Combination. I am unable to retrieve the Values passed from HTML FORM(test1.html) in a PHP Page(test1.php) Is it something to do with the Web Server Settings or any thing else...? The code is as follows; Test1.html html head titleTest Page/title /head body form name=form1 method=Post action=test1.php input type=text name=text1 value= input type=submit value=Ok /form /body /html Test1.php html head titleTest Page/title /head body form name=form1 ?php echo $text1; ? /FORM /body /html After Submitting the form text1.html to text1.php by adding some text in textbox text1, i am unable to print the textbox value text1 in Text1.php page. Can you help me in this regard asap? Thanks Regards, Prashant S Akerkar.
RE: GRANT CREATE TEMPORARY TABLES
Thank You. I found out that I had to run mysql_fix_privilege_tables program. Thanks Steve At 08:07 AM 10/28/2003, you wrote: Hi there, I guess you are going to have to grant this at the database level, unfortunately for you. These privileges are stored in the mysql.db and mysql.host tables, or can be manipulated using something like the following, which is a little easier to try out: GRANT CREATE TEMPORARY TABLES ON my_db.* TO 'user' IDENTIFIED BY 'pass'; I'm doing this and it works fine :) I guess if you have multiple databases and users you could make a INSERT INTO ... SELECT statement to manipulate the mysql tables directly and then do a flush priveledges. Hope that helps, Mike -Original Message- From: Steve Buehler [mailto:[EMAIL PROTECTED] Sent: 28 October 2003 13:51 To: mysql Subject: GRANT CREATE TEMPORARY TABLES Ok. Someone else was talking about this earlier in the list, but I didn't see the answer that I am looking for. I have just setup a new RedHat 9.0 server with MySQL 3.23.58. My program that worked on an older version of MySQL doesn't work on this one. The problem is that the temporary tables are not being created. How can I turn on the GRANT function so that each of the users that have databases can create temporary tables in their own databases ONLY. Can this be done with a GLOBAL type of grant? If so, what would be the Grant statement to turn this on? Or will I have to define this each time a user us setup? If so, what would be the Grant statement to turn this on this way? The docs did show a little about this, but not this specifically (that I found) and since I am new to this type of grant, I don't want to screw things up. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to Retrieve HTML Form Values(test1.html) from a PHP Page(test1.php)
you need to use $_POST['test1'] http://www.php.net/register_globals php, by default has register_globals=off. for security reasons...so you'll need to use $_POST, $_GET, $_FILES, $_REQUEST etc. hth jeff Prashant A [EMAIL PROTECTED]To: [EMAIL PROTECTED] thnyou.com cc: Subject: Unable to Retrieve HTML Form Values(test1.html) from a PHP Page(test1.php) 10/28/2003 09:23 AM Dear Sir Our Site is running on Linux/Apache/PHP/MySQL Combination. I am unable to retrieve the Values passed from HTML FORM(test1.html) in a PHP Page(test1.php) Is it something to do with the Web Server Settings or any thing else...? The code is as follows; Test1.html html head titleTest Page/title /head body form name=form1 method=Post action=test1.php input type=text name=text1 value= input type=submit value=Ok /form /body /html Test1.php html head titleTest Page/title /head body form name=form1 ?php echo $text1; ? /FORM /body /html After Submitting the form text1.html to text1.php by adding some text in textbox text1, i am unable to print the textbox value text1 in Text1.php page. Can you help me in this regard asap? Thanks Regards, Prashant S Akerkar. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unable to Retrieve HTML Form Values(test1.html) from a PHP Page(test1.php)
-Original Message-
From: Prashant A [mailto:[EMAIL PROTECTED]
Our Site is running on Linux/Apache/PHP/MySQL Combination.
I am unable to retrieve the Values passed from HTML
FORM(test1.html) in a PHP Page(test1.php)
Is it something to do with the Web Server Settings or any thing
else...?
Register Globals are disabled by default since PHP 4.2.0.
You can read more on these at:
http://www.php.net/manual/en/security.registerglobals.php
?php
echo $text1;
?
something like this should work:
echo $_POST['text1'];
Of course you should validate that any external variables exist and contain
acceptable values.
if (isset($_POST['text1']) {
// check for valid values, etc.
}
- Barry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to Retrieve HTML Form Values(test1.html) from a PHP Page(test1.php)
Dear Prashant, This list is for MySQL questions, rather than PHP questions. Please try the PHP general mailing list ([EMAIL PROTECTED]). Cheers! -- Zak Greant MySQL AB Community Advocate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreigner keys in MySQL?
10/28/03 6:50:26 AM, Juliana Gubert Ehrensperger [EMAIL PROTECTED] wrote: Nevertheless, I received the information that MySQL don't support relacional functions between tables of the same database. I'm not sure about this, and I'd like to confirm this information. Does MySQL allow relacionl functions between tables recognizing foreign keys? Excuse me for asking a so simple question, If you are trying to determine whether MySQL is a relational database, the answer is yes. If you are trying to determine whether MySQL supports foreign keys, the answer is yes: Use table type InnoDB to get enforcement of foreign key constraints in MySQL. If you are trying to determine whether MySQL has a c api equivalent to Oracle's OCI (sometimes called relational functions), the answer is yes. See the manual (http://www.mysql.com/doc/en/index.html). If you need particular relational functions found in some other database's interface, you may want to check the relevant parts of MySQL's documentation. MySQL is not the same as Oracle -- it is its own full-featured, powerful relational database system. -bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert happens twice
Hey folks,
I am having a dreadful problem here, and I cannot get to the root of it. It
appears that every time I do an INSERT, the insert happens twice. I have
spent several days on Google, but with no luck.
Some background - I am using PHP and Smarty on a site. Here is one of the
tables in my database that is having the problem:
CREATE TABLE composers (
ComposerID bigint(20) unsigned NOT NULL auto_increment,
ComposerFname varchar(50) default NULL,
ComposerLname varchar(60) default NULL
DiscountID int(11) default NULL,
PRIMARY KEY (ComposerID)
)
So I pass some values in from a form, and I end up with this:
$sql = insert into composers(ComposerFname, ComposerLname) values('',
'Bach');
which then gets executed. My debug statements indicate that this is only
getting executed once. However, I end up with two new entries in my
composers table. I know I can solve this by making the fields unique, but
I'd have to do that for every table in the database, when what I'd really
like to do is find out why the insert is happening twice.
Any ideas?
-Erich-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert happens twice
I just recently helped someone else with a similar issue
is your PHP code (and therefore your insert) getting executed when the form is being
displayed to the user initially, and then again when the form is submitted?
-Original Message-
From: Erich C. Beyrent [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 28, 2003 10:19 AM
To: [EMAIL PROTECTED]
Subject: Insert happens twice
Hey folks,
I am having a dreadful problem here, and I cannot get to the
root of it. It
appears that every time I do an INSERT, the insert happens
twice. I have
spent several days on Google, but with no luck.
Some background - I am using PHP and Smarty on a site. Here
is one of the
tables in my database that is having the problem:
CREATE TABLE composers (
ComposerID bigint(20) unsigned NOT NULL auto_increment,
ComposerFname varchar(50) default NULL,
ComposerLname varchar(60) default NULL
DiscountID int(11) default NULL,
PRIMARY KEY (ComposerID)
)
So I pass some values in from a form, and I end up with this:
$sql = insert into composers(ComposerFname, ComposerLname) values('',
'Bach');
which then gets executed. My debug statements indicate that
this is only
getting executed once. However, I end up with two new entries in my
composers table. I know I can solve this by making the
fields unique, but
I'd have to do that for every table in the database, when
what I'd really
like to do is find out why the insert is happening twice.
Any ideas?
-Erich-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert happens twice
On Tuesday, Oct 28, 2003, at 15:19 Africa/Accra, Erich C. Beyrent wrote:
Hey folks,
I am having a dreadful problem here, and I cannot get to the root of
it. It
appears that every time I do an INSERT, the insert happens twice. I
have
spent several days on Google, but with no luck.
Some background - I am using PHP and Smarty on a site. Here is one of
the
tables in my database that is having the problem:
...
$sql = insert into composers(ComposerFname, ComposerLname) values('',
'Bach');
which then gets executed. My debug statements indicate that this is
only
getting executed once. However, I end up with two new entries in my
composers table. I know I can solve this by making the fields unique,
but
I'd have to do that for every table in the database, when what I'd
really
like to do is find out why the insert is happening twice.
Any ideas?
Test the query in the MySQL command line client to see if you
experience the same behavior outside of PHP.
Cheers!
--zak
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: I can't figure out what I thought would be a simple query..
Okay - I can't figure this out as a single sql statement. However Assuming there aren't likely to be duplicate rows you could do: create table temp2 (table definition) select max(endtime), non_unique_id temp1 group by non_unique_id; then you could select a.endtime, a.need_id, b.unique_id from table temp1 a, temp2 b where a.endtime=b.endtime and a.unique_id=b.unique_id; If there are duplicates I suspect you'd get away with distinct? Of course I'm sure there's a smarter way! Rgds Mike (--traitorous Oracle Ingres DBA) -Original Message- From: Larry Brown [mailto:[EMAIL PROTECTED] Sent: 27 October 2003 22:30 To: Jim Matzdorff; MySQL List Subject: RE: I can't figure out what I thought would be a simple query.. I'm interested to see what kind of solution is offered for this as I could use it myself. I'm having to do this programatically on an expternal script that selects distinct non_unique_id and the takes the result and loops through each one with sort by endtime desc limit 1 and then either do something with the result during the loop or simply create a seperate temp table to store them in. Not the most efficient if there is a way to get it as a query though. -Original Message- From: Jim Matzdorff [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 4:37 PM To: [EMAIL PROTECTED] Subject: I can't figure out what I thought would be a simple query.. All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- MySQL General Mailing List For list
Re: DROP TEMORARY TABLE
Michael McTernan wrote: My problem, that so far no one has been able to answer, is that I'm using connection pooling with the Tomcat server. TransactionA gets connection A from the pool. TransactionA creates a temporary table for some query. TransactionA is done, and returns the connection to the pool. TransactionB gets a connection from the pool, which just so happens to be connection A. TransactionB tries to create a temporary table with the same name as the one that already exists. ** BANG!! ** Yes, you're right -- as long as the container-managed connection is open, the original TEMPORARY table will persist across the sessions of different individuals. I just ran into this issue myself, and so far I've thought of 1) creating a *unique* temporary table using the SESSIONID, in a separate DB where the tomcat 'user' has DROP privileges, and use a method implementing HttpSessionBindingListener to drop that table when the session terminates; 2) just keeping the original ResultSet in memory and manipulating it there... Neither quite as graceful as using a real temporary table, so I'm also open to other suggestions :-) -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreigner keys in MySQL?
Nevertheless, I received the information that MySQL don't support relacional functions between tables of the same database. I'm not sure about this, and I'd like to confirm this information. Does MySQL allow relacionl functions between tables recognizing foreign keys? Excuse me for asking a so simple question, If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreigner keys in MySQL?
Hi Martijn, With respect, I must disagree with your assertion that MySQL is not a relational database. I did a Google on definition of a relational database and found a good definition at http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci212885,00.html. If you read this you will find that MySQL fits this definition nicely. John Griffin -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 11:11 AM To: [EMAIL PROTECTED] Subject: Re: Foreigner keys in MySQL? Nevertheless, I received the information that MySQL don't support relacional functions between tables of the same database. I'm not sure about this, and I'd like to confirm this information. Does MySQL allow relacionl functions between tables recognizing foreign keys? Excuse me for asking a so simple question, If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is MySQL Relational? (was: Foreigner keys in MySQL?)
10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
Hi, If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. First of all ... Relational Database Engine - not relational database. :-) (this may sound like nitpicking to you, but a database is NOT a database engine) I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. You are very right here. I for sure wouldn't buy Oracle if I wouldn't need it and MySQL is capable of lots of things - no doubt there. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? To become at least a bit relational, a database engine should be able to enforce integrity. Now, MySQL - by itself - cannot do that. Yes, InnoDB can do referential integrity - that's a good start, albeit not enough. Check constraints, anyone? Domains, anyone? Views and updateable views? And WHY do we have to care about table-types? Isn't this a physical thingy? And there's probably more - some of which I don't understand... As said before - MySQL is very suited for lots and lots of types of applications and is improving a lot. Heck, that's the reason why I will be supporting MySQL in Database Workbench :-) Oh, and John, SearchDatabase or Google isn't always right :-) http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci212885,00.html (actually, the SearchDatabase site has a lot of wrong stuff - XML? :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ancestry program
Try doing a web search for the program geneweb. It's done by someone in France but there are English versions as well. It's freeware. It's a web based genealogy program and has a built-in database. No need to reinvent the wheel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
a question... is any DB that complies to the Relational Algebra a Relational DB??? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003 [EMAIL PROTECTED] wrote: I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DROP TEMORARY TABLE
Hi there,
Cool, I'm not imagining things :)
1) creating a *unique* temporary table using the SESSIONID, in a
separate DB where the tomcat 'user' has DROP privileges, and use
a method implementing HttpSessionBindingListener to drop that
table when the session terminates;
I currently drop the temporary table after use, so that the connection gets
'cleaned' up for reuse. I've used the finally clause of Java to ensure this
gets done, like this:
Connection dbConnection = Pool.getConection();
try
{
/* Do some funky MySQL stuff here, using temp tables x,y,z */
return true;
}
finally
{
dbConnection.prepareStatment(DROP TABLES x,y,z;).executeUpdate;
/* Close method is overridden, so actually returns to the Pool */
dbConnection.close();
}
The problem for me is that I don't like giving the Tomcat user DROP
privilege for security reasons. Seems like a feature request is needed to
ask for a DROP TEMPORARY TABLE privilege to match the CREATE TEMPORARY TABLE
privilege.
Thanks,
Mike
-Original Message-
From: Hassan Schroeder [mailto:[EMAIL PROTECTED]
Sent: 28 October 2003 15:54
To: mysql
Cc: MySQL Java
Subject: Re: DROP TEMORARY TABLE
Michael McTernan wrote:
My problem, that so far no one has been able to answer, is that
I'm using
connection pooling with the Tomcat server.
TransactionA gets connection A from the pool.
TransactionA creates a temporary table for some query.
TransactionA is done, and returns the connection to the pool.
TransactionB gets a connection from the pool, which just so
happens to be
connection A.
TransactionB tries to create a temporary table with the same
name as the one
that already exists.
** BANG!! **
Yes, you're right -- as long as the container-managed connection is
open, the original TEMPORARY table will persist across the sessions
of different individuals.
I just ran into this issue myself, and so far I've thought of
1) creating a *unique* temporary table using the SESSIONID, in a
separate DB where the tomcat 'user' has DROP privileges, and use
a method implementing HttpSessionBindingListener to drop that
table when the session terminates;
2) just keeping the original ResultSet in memory and manipulating
it there...
Neither quite as graceful as using a real temporary table, so I'm
also open to other suggestions :-)
--
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com
dream. code.
--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with insert data
hi,
I'm using MySQL 4.0.13 and 4.0.14 (4.0.16 I will test tomorrow). And I have
problems with this query:
insert into user(class_id, retail_id, mandant_id, language_id,
user_data_id, nickname, login_name, password, status, creation_date,
last_login_date) values ('35F7A660096411D89BC0D1907F01', '1234', 2, 1,
null, null, '[EMAIL PROTECTED]', 'demo', 8, '2003-10-28 17:31:36', null)
but the same query with space after user
insert into user (class_id, retail_id, mandant_id, language_id,
user_data_id, nickname, login_name, password, status, creation_date,
last_login_date) values ('35F7A660096411D89BC0D1907F01', '1234', 2, 1,
null, null, '[EMAIL PROTECTED]', 'demo', 8, '2003-10-28 17:31:36', null)
works.
Regards,
Rafal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with Mail::Sendmail
Hello,
perhaps this questions is not directly MySql related but MySql is very
related with my question. I'm learning Paul Dubois MySql and Perl for the
web and I have a lot of problems make working the Mail::Sendmail module.
Here's the concerning script :
#!/usr/bin/perl -w
# testmail.pl - Send mail using the Mail::Sendmail module
use strict;
use Mail::Sendmail;
my $recipient = [EMAIL PROTECTED];
my $sender = [EMAIL PROTECTED]; #this is replaced with my personal
email
# Set up hash containing mail message information
my %mail = (
From= $sender,
To = $recipient,
Subject = I'm sending your mail,
Message = This is the message body.\n
);
sendmail (%mail) or die sendmail failure sending to $mail{To}: $!\n;
exit(0);
When I execute the script I always receive a warning and an error. The
warning tells that realy is not allowed and I understand this, this is
because my SMTP server needs authentication, but I'm unable to configure
Sendmail for this.
The error message is Bad file descriptor and nothing more. This message is
meanless for me, it doesn't make sense at all.
The sendmail.pm file is configured to have the name of my smtp and pop
servers, so I don't keept localhost, so I don't need an email server
installed.
# *** Configuration you may want to change ***
# You probably want to set your SMTP server here (unless you specify it in
# every script), and leave the rest as is. See pod documentation for details
%mailcfg = (
# List of SMTP servers:
'smtp'= [ qw(mail.messagingengine.com) ],#was localhost
#'smtp'= [ qw( mail.mydomain.com ) ], # example
'from'= '[EMAIL PROTECTED]', # default sender e-mail, used when
no From header in mail
'mime'= 1, # use MIME encoding by default
'retries' = 1, # number of retries on smtp connect failure
'delay' = 1, # delay in seconds between retries
'tz' = '', # only to override automatic detection
'port'= 25, # change it if you always use a non-standard port
'debug' = 0 # prints stuff to STDERR
);
# ***
Someone can help?
Raikonn
_
Add photos to your e-mail with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Mail::Sendmail
On Tue, 28 Oct 2003 14:13:45 +, Raiko Gonzales [EMAIL PROTECTED] wrote: perhaps this questions is not directly MySql related but MySql is very related with my question. I'm learning Paul Dubois MySql and Perl for the web and I have a lot of problems make working the Mail::Sendmail module. In fact, your question has absolutely nothing to do with MySQL, and this is the wrong place to get an answer to your question. For problems with sendmail, I recommend: news://comp.mail.sendmail (http://groups.google.com/groups?hl=enlr=ie=UTF-8group=comp.mail.sendmail) -bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Mail::Sendmail
At 2:13 PM + 10/28/03, Raiko Gonzales wrote:
Hello,
perhaps this questions is not directly MySql related but MySql is
very related with my question. I'm learning Paul Dubois MySql and
Perl for the web and I have a lot of problems make working the
Mail::Sendmail module.
Here's the concerning script :
#!/usr/bin/perl -w
# testmail.pl - Send mail using the Mail::Sendmail module
use strict;
use Mail::Sendmail;
my $recipient = [EMAIL PROTECTED];
my $sender = [EMAIL PROTECTED]; #this is replaced with my
personal email
# Set up hash containing mail message information
my %mail = (
From= $sender,
To = $recipient,
Subject = I'm sending your mail,
Message = This is the message body.\n
);
sendmail (%mail) or die sendmail failure sending to $mail{To}: $!\n;
exit(0);
When I execute the script I always receive a warning and an error.
The warning tells that realy is not allowed and I understand this,
this is because my SMTP server needs authentication, but I'm unable
to configure Sendmail for this.
The error message is Bad file descriptor and nothing more. This
message is meanless for me, it doesn't make sense at all.
The Bad file descriptor message is a consequence of the fact that sendmail
isn't allowing you to send mail. It is not meaningful in itself.
As far as I know Mail::Sendmail does not provide any special authentication
capabilities. If you cannot configure sendmail to accept mail from your
script, then you may be out of luck and will need to find some other method
of sending mail.
The sendmail.pm file is configured to have the name of my smtp and
pop servers, so I don't keept localhost, so I don't need an email
server installed.
# *** Configuration you may want to change ***
# You probably want to set your SMTP server here (unless you specify it in
# every script), and leave the rest as is. See pod documentation for details
%mailcfg = (
# List of SMTP servers:
'smtp'= [ qw(mail.messagingengine.com) ],#was localhost
#'smtp'= [ qw( mail.mydomain.com ) ], # example
'from'= '[EMAIL PROTECTED]', # default sender e-mail,
used when no From header in mail
'mime'= 1, # use MIME encoding by default
'retries' = 1, # number of retries on smtp connect failure
'delay' = 1, # delay in seconds between retries
'tz' = '', # only to override automatic detection
'port'= 25, # change it if you always use a non-standard port
'debug' = 0 # prints stuff to STDERR
);
# ***
Someone can help?
Raikonn
_
Add photos to your e-mail with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Mail::Sendmail
bluejack wrote: On Tue, 28 Oct 2003 14:13:45 +, Raiko Gonzales [EMAIL PROTECTED] wrote: perhaps this questions is not directly MySql related but MySql is very related with my question. I'm learning Paul Dubois MySql and Perl for the web and I have a lot of problems make working the Mail::Sendmail module. In fact, your question has absolutely nothing to do with MySQL, and this is the wrong place to get an answer to your question. For problems with sendmail, I recommend: news://comp.mail.sendmail (http://groups.google.com/groups?hl=enlr=ie=UTF-8group=comp.mail.sendmail) -bluejack For problems with Sendmail, I usually recomend : http://www.qmail.org :) -- By-Tor.com It's all about the Rush http://www.by-tor.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment with FOREIGN KEY UPDATE CASCADE courses Lost connection to MySQL server
Vinita, it is most probably this bug fixed in 4.1.14: Fixed a bug: if in a FOREIGN KEY with an UPDATE CASCADE clause the parent column was of a different internal storage length than the child column, then a cascaded update would make the column length wrong in the child table and corrupt the child table. Because of MySQL's 'silent column specification changes' a fixed-length CHAR column can change internally to a VARCHAR and cause this error. Below a test run with the latest 4.0 tree. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables [EMAIL PROTECTED]:~/mysql-4.0/client mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.17-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE software ( - softwareID CHAR(20) NOT NULL, - softwareName CHAR(100), - softwareVers CHAR(20), - installedDate DATE, - softwareSource BLOB, - softwareNote BLOB, - localMods BLOB, - PRIMARY KEY (softwareID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.04 sec) mysql mysql CREATE TABLE software_machineOSs ( - softwareID CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - osName CHAR(20), - osRevision CHAR(20), - INDEX (softwareID), - FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON - DELETE CASCADE ON UPDATE CASCADE, - KEY(id), - PRIMARY KEY (softwareID, id) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql select * from software; Empty set (0.03 sec) mysql insert into software - (softwareID,softwareName,softwareVers,installedDate,softwareSource) - values (vim-1.1, vim, 1.1, NOW(), - www.test.com.au); Query OK, 1 row affected (0.01 sec) mysql mysql insert into software_machineOSs (softwareID,osName,osRevision) values - (vim-1.1, sun, 8); Query OK, 1 row affected (0.00 sec) mysql mysql select * from software; ++--+--+---+ -+-- +---+ | softwareID | softwareName | softwareVers | installedDate | softwareSource | s oftwareNote | localMods | ++--+--+---+ -+-- +---+ | vim-1.1| vim | 1.1 | 2003-10-28| www.test.com.au | N ULL | NULL | ++--+--+---+ -+-- +---+ 1 row in set (0.00 sec) mysql select * from software_machineOSs; +++++ | softwareID | id | osName | osRevision | +++++ | vim-1.1| 01 | sun| 8 | +++++ 1 row in set (0.00 sec) mysql update software set softwareID=vi-3 where - softwareID=vim-1.1; Query OK, 1 row affected (1 min 14.30 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from software; ++--+--+---+ -+-- +---+ | softwareID | softwareName | softwareVers | installedDate | softwareSource | s oftwareNote | localMods | ++--+--+---+ -+-- +---+ | vi-3 | vim | 1.1 | 2003-10-28| www.test.com.au | N ULL | NULL | ++--+--+---+ -+-- +---+ 1 row in set (0.00 sec) mysql select * from software_machineOSs; +++++ | softwareID | id | osName | osRevision | +++++ | vi-3 | 01 | sun| 8 | +++++ 1 row in set (0.00 sec) mysql check table software; +---+---+--+--+ | Table | Op| Msg_type | Msg_text | +---+---+--+--+ | test.software | check | status | OK | +---+---+--+--+ 1 row in set (0.01 sec) mysql check table software_machineOSs; +--+---+--+--+ | Table| Op| Msg_type | Msg_text | +--+---+--+--+ | test.software_machineOSs | check | status | OK | +--+---+--+--+ 1 row in set (0.01 sec) mysql show create table software; +--+
RE: Hanging processes in MySQL 3.23.53
Can you check you keys_used status var. You may have run out of your key_buffer cache so things might take a much longer amount of time. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Dan Goodes [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 7:03 PM --To: Chris Nolan --Cc: [EMAIL PROTECTED] --Subject: Re: Hanging processes in MySQL 3.23.53 -- --Using Redhat linux 7.3 with an ext3 FS. -- --Incidentally, I've just manually restarted mysql (which drops all --in-progress processes), and it seems that the problem takes a while to --show (i.e. there's a period after a restart that things seem to go along --fine, then it all comes undone). I also should note that the database is --being written to almost-constantly (it's being used as an apache logger --process via mod_log_sql). -- ---dan -- -- --On Tue, 28 Oct 2003, Chris Nolan wrote: -- -- Which platform are you using? Which FS? -- -- Regards, -- -- Chris -- -- On Tue, 28 Oct 2003 01:14 pm, Dan Goodes wrote: -- Hi folks, -- -- I have a bit of a problem. I'm running 3.23.53 which I've compiled up --from -- source (because the RPMs are not an option for me). -- -- I have a process that does a fairly large select statement every 10 -- minutes - up until a few days ago it was all find and dandy. -- -- A few days ago I did a massive delete from one of the tables (getting --rid -- of a lot of old records), and since then things have gone awry. The --select -- statement seems to get stuck in the COPY TO tmp table stage, and -- starts to back up fairly heavily. Each of the cron-run processes gets --to -- this COPY TO TMP TABLE stage and locks up, which consumes all --available -- slots on the server and the whole things comes to a grinding halt. -- -- I've already run an optimize table on the table, and that got rid of --all -- the empty space freed up by the delete. -- -- Any ideas why, after the massive delete, things have started slowing --right -- down (or locking up entirely)? -- -- THanks for help. -- -- -Dan -- -- --Regards, -- --Dan Goodes : Systems Programmer : [EMAIL PROTECTED] -- --Help support PlanetMirror - Australia's largest Internet archive --by signing up for PlanetMirror Premium : http://planetmirror.com -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ancestry program
Think of a binary tree. Parent_id auto increment Child_id Details of the famly The head of the family has a child_id == parent_id All members of the family have different child_ids but the same parent_id Then you can do some really cool recursive fast searches. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Nitin [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 10:46 PM --To: [EMAIL PROTECTED] --Subject: Ancestry program -- --Hi all, -- --I'm developing a web based ancestry program. The user wants it to be --static, that means, it isn't for all to use, but his family. Better to --say, it'll contain only his family tree. -- --Now, I cant think of the proper db design, which will help any user to --find his or her relationship with any other person in the tree. Though, I --can design a simple database, where everything will have to be done --through queries and scripts, but I want those queries to keep as simple --as possible. -- --Any help will be appreciated, as I'm new to such a problem. -- --Thanx in advance --Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Change of root password
After long struggle I (A newbee to linux) was able to setup mysql4.0.16 and I don't know what I have done, I set the root password which I don't know. Now I can't access the mysql. Is there anyway of changing root password.? and how? I tried to delete the content of /usr/local/mysql/var/mysql and run mysql_install_db again thinking it will remove the password but no effect. -- Parminder Singh Chauhan [EMAIL PROTECTED]
[Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)]
---BeginMessage--- Well, my database should be able to check some entry data and decide if these data will be accepted or rejected in a table that records all the received data. For example: I'll have a table 1 that contains a list of all the equipments that can send information to the database. The table 2 that records the data sent should check whether the data is coming from a valid equipment or not. If the table1 just mention 'machine 1' and 'machine 2', some data coming from 'machine 3' should be rejected. Juliana bluejack wrote: 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] ---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Howto reduce size of MYISAM files after deleting records?
Hi. I delete a lot of records of one MyISAM table in MySQL 4.0.16. After that I execute mysqladmin refresh and now the size of the files of the table is the same than before. Is something wrong or is necessary doing something more to reduce the size of the files? Thanks in advance. Iago. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Change of root password
As ROOT on linux goto /etc Edit my.cnf Enter skip-grant-tables Restart the mysql server Change the password. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Parminder Singh Chauhan [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 10:34 AM --To: [EMAIL PROTECTED] --Subject: Change of root password -- --After long struggle I (A newbee to linux) was able to setup mysql4.0.16 --and I don't know what I have done, I set the root password which I don't --know. Now I can't access the mysql. --Is there anyway of changing root password.? and how? --I tried to delete the content of /usr/local/mysql/var/mysql and run --mysql_install_db again thinking it will remove the password but no --effect. --Parminder Singh Chauhan [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Howto reduce size of MYISAM files after deleting records?
Shutdown the mysql server Goto the datadir (/var/lib/mysql) Goto the db directory Type myisamchk -r -S -a *.MYI this will reclaim the bad blocks caused by the delete. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Iago Sineiro [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 9:52 AM --To: MySql Mail List --Subject: Howto reduce size of MYISAM files after deleting records? -- --Hi. -- --I delete a lot of records of one MyISAM table in MySQL 4.0.16. After that --I --execute mysqladmin refresh and now the size of the files of the table is --the --same than before. -- --Is something wrong or is necessary doing something more to reduce the --size --of the files? -- --Thanks in advance. -- --Iago. -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Change of root password
-Original Message- From: Parminder Singh Chauhan [mailto:[EMAIL PROTECTED] After long struggle I (A newbee to linux) was able to setup mysql4.0.16 and I don't know what I have done, I set the root password which I don't know. Now I can't access the mysql. Is there anyway of changing root password.? and how? Start mysqld with the --skip-grant-tables option. Change the password and then run 'mysqladmin flush-privileges' to make the change take effect. - Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Howto reduce size of MYISAM files after deleting records?
Is there a way to do this on a live running (i.e. production) server? -Original Message- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:49 PM To: 'Iago Sineiro'; 'MySql Mail List' Subject: RE: Howto reduce size of MYISAM files after deleting records? Shutdown the mysql server Goto the datadir (/var/lib/mysql) Goto the db directory Type myisamchk -r -S -a *.MYI this will reclaim the bad blocks caused by the delete. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Iago Sineiro [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 9:52 AM --To: MySql Mail List --Subject: Howto reduce size of MYISAM files after deleting records? -- --Hi. -- --I delete a lot of records of one MyISAM table in MySQL 4.0.16. After that --I --execute mysqladmin refresh and now the size of the files of the table is --the --same than before. -- --Is something wrong or is necessary doing something more to reduce the --size --of the files? -- --Thanks in advance. -- --Iago. -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Howto reduce size of MYISAM files after deleting records?
OPTIMIZE TABLE but... it will bring down your server if the table being optimize requires any sort of traffic. Depending on which mysql version your using OPTIMIZE TABLE will take longer since you can't allocate more memory as you can on the command line with myisamchk, unless you restart the server. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Dan Greene [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 10:59 AM --To: Dathan Vance Pattishall; Iago Sineiro; MySql Mail List --Subject: RE: Howto reduce size of MYISAM files after deleting records? -- --Is there a way to do this on a live running (i.e. production) server? -- -- -Original Message- -- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] -- Sent: Tuesday, October 28, 2003 1:49 PM -- To: 'Iago Sineiro'; 'MySql Mail List' -- Subject: RE: Howto reduce size of MYISAM files after deleting records? -- -- -- Shutdown the mysql server -- Goto the datadir (/var/lib/mysql) -- -- Goto the db directory -- Type -- myisamchk -r -S -a *.MYI this will reclaim the bad blocks -- caused by the -- delete. -- -- -- -- - Dathan Vance Pattishall -- - Sr. Programmer and mySQL DBA for FriendFinder Inc. -- - http://friendfinder.com/go/p40688 -- -- -- ---Original Message- -- --From: Iago Sineiro [mailto:[EMAIL PROTECTED] -- --Sent: Tuesday, October 28, 2003 9:52 AM -- --To: MySql Mail List -- --Subject: Howto reduce size of MYISAM files after deleting records? -- -- -- --Hi. -- -- -- --I delete a lot of records of one MyISAM table in MySQL -- 4.0.16. After -- that -- --I -- --execute mysqladmin refresh and now the size of the files -- of the table -- is -- --the -- --same than before. -- -- -- --Is something wrong or is necessary doing something more to -- reduce the -- --size -- --of the files? -- -- -- --Thanks in advance. -- -- -- --Iago. -- -- -- -- -- -- -- -- --MySQL General Mailing List -- --For list archives: http://lists.mysql.com/mysql -- --To unsubscribe: -- --http://lists.mysql.com/[EMAIL PROTECTED] -- -- -- -- -- -- -- MySQL General Mailing List -- For list archives: http://lists.mysql.com/mysql -- To unsubscribe: -- http://lists.mysql.com/[EMAIL PROTECTED] -- -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Howto reduce size of MYISAM files after deleting records?
On 28 Oct 2003 at 13:59, Dan Greene wrote: Is there a way to do this on a live running (i.e. production) server? http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ancestry program
well... when I do db design, I tend to start with the objects of my system. The one that comes to mind in your case is people. so you'll need a people table. well what are the details of a person? first_name Last_name Middle_name1 Middle_name2 Maiden_name [any other basic bio data] so you'll need those columns Well to keep track of each person, each one will need an ID... id's are usually numbers, so now you add a: person_id field. This field would likely have an auto_increment attribute to help number them for you ok... now that we have people, what else do we need? relationships between them well... in terms of human beings, everyone has one biological mother and one biological father, so we add in mother_id father_id leaving the values of these as null would be equivalent of being 'unknown' and we now have, data-wise, a system that can trace biological heritage, can handle siblings and half-siblings. Other ideas for objects: Marrages - this one would be tricky/interesting, as marrages can change over time, and people can have multiple marrages (although usually not two at a time, unless bigamy is allowed in your user's state/country). Strictly speaking, marrages are not necessary to trace heritage, but are good info... --From: Nitin [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 10:46 PM --To: [EMAIL PROTECTED] --Subject: Ancestry program -- --Hi all, -- --I'm developing a web based ancestry program. The user wants it to be --static, that means, it isn't for all to use, but his family. Better to --say, it'll contain only his family tree. -- --Now, I cant think of the proper db design, which will help any user to --find his or her relationship with any other person in the tree. Though, I --can design a simple database, where everything will have to be done --through queries and scripts, but I want those queries to keep as simple --as possible. -- --Any help will be appreciated, as I'm new to such a problem. -- --Thanx in advance --Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Almost beating Rushmore -- was: Really slow query (compared with Visual FoxPro)
Hi guys! Ok, I'm closer to beat Rushmore (VFP optimizer) now! After some reading about MySQL optimization techniques, here is the summary of what I've done: 1. Add a compound index to the table 2. Use EXPLAIN to check out the query (with GROUP BY on multiple fields) 3. Create the summary table And here's the detailed instructions: mysql alter table traf_oper add index (tel, telefb, rutaentran, rutasalien, minutos); Query OK, 5067215 rows affected (5 min 22.36 sec) Records: 5067215 Duplicates: 0 Warnings: 0 mysql explain select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4; ++-+---+---+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+---+---+--+-+--+-+-+ | 1 | SIMPLE | traf_oper | index | NULL | tel | 45 | NULL | 5067215 | Using index | ++-+---+---+---+--+-+--+-+-+ 1 row in set (0.03 sec) mysql create table grp_oper select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3326541 rows affected (33.81 sec) Records: 3326541 Duplicates: 0 Warnings: 0 Adding the times together it would take MySQL like 6 minutes (VFP does it in about 4 minutes). I still haven't tweaked some server variables (read_rnd_buffer_size, sort_buffer_size, max_join_size), but, as always, I'll keep trying :) Thanks in advance for your comments and suggestions, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreigner keys in MySQL?
Martijn Tonies wrote: Nevertheless, I received the information that MySQL don't support relacional functions between tables of the same database. I'm not sure about this, and I'd like to confirm this information. Does MySQL allow relacionl functions between tables recognizing foreign keys? Excuse me for asking a so simple question, If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. ehm ... i have no clue about DBMS, but ... http://www.mysql.com/doc/en/What-is.html MySQL is a relational database management system. A relational database stores data in separate tables rather than putting all the data in one big storeroom.[...] or take a look at addison-wesley / pearson education fundamentals of database systems third edition Ramez Elmasry, Shamkant Navathe eg. in the german edition: chapter 2.5 Klassifikation von DBMS p.58ff It's getting better, that's for sure. -- shrek-m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is MySQL Relational? (was: Foreigner keys in MySQL?)
so funtions that can be performed by relation algebra are a subset of the functions performed by a relational database? however isn't it required for a relational database to perform all the functions of relation algebra? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003, Jon Frisby wrote: No. Codd's rules defining what is a relational database are more specific than relational algebra can express. Essentially the requirements to be a relational database are a superset of the operations defined by relational algebra. -JF -Original Message- From: Saqib Ali [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 8:47 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Is MySQL Relational? (was: Foreigner keys in MySQL?) a question... is any DB that complies to the Relational Algebra a Relational DB??? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003 [EMAIL PROTECTED] wrote: I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
On Tue, 28 Oct 2003 11:56:54 -0800, Jon Frisby [EMAIL PROTECTED] wrote: MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. Some standard or another? You make it sound as if there is no definitive answer to the question of what constitutes a relational database. That is absolutely not true. We need only look to the I do enjoy these sorts of discussion, because they help me improve my own understanding of both the philosophies and the technologies in question. So thank you for your lengthy and informative reply. individual who created the concept of the relational database: E.F. Codd. Since he invented the concept of the relational database (way back in 1970), his word is definitive as to what constitutes a relational database and what does not. It is important to take a deep breath and realize that when someone asks Is MySQL a Relational Database they are probably *not* asking whether it comprehensively and successfully implements the inventor's requirements, however definitive. The probable question is, is it this *kind* of database (engine) or is it something else? I think we are all in agreement that MySQL does not perfectly implement the concept of a relational database, but that its inspiration is the relational model and not some other model. I think the original questioner should be clear on that now, as well. No existing RDBMS is even close to full compliance. Oracle would rate about 60% compliance. Others are lower except Ingres and FirstSQL which rate about 65% compliance. A statement like this pretty much nails the lid of the coffin down on the value of the inventor's definitive requirements. I believe in real world applications, rather than platonic ideals. However fabulous the original idea is, if *nobody* wants to implement it, then we might as well be talking about time travel or something. Pure speculation. By rule #2, MySQL (and most other RDBMSs) have already fallen down: Any RDBMS that allows you to define a table without a unique key of some sort fails rule #2. I actually think this is more controversial than your next example, because it leaves the power in the hands of the developers. Although the RDBMS doesn't force the database designer into correct behavior, it allows it. Rule #3 is somewhat more controversial. It requires at least two different kinds of null. Codd referred to them as A-mark, and I-mark: This is a far more interesting point because it is a feature that would be very useful, and which I have never seen implemented. I would sooner see RDBMS software implement this than conform to rule #2. So basically, MySQL complies with about 5 of the 13 basic requirements for a relational DB. And given that the most compliant RDBMS is only hitting two or three more points, I think we all need to loosen our ties and agree that while the distinction between 'relational database' in common parlance and in theory is both important and interesting, MySQL is a relational database engine. But thanks for all the links. Great to have these resources. -bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
Hi! For decades, people have debated in comp.databases.theory and elsewhere how a 'relational database' should be defined. Codd's original 1970 paper sketches the relational algebra as a query language of a relational database, though Codd is not very precise about what the query language exactly should be. The paper also mentions integrity constraints. Relations in the 1970 paper are defined as mathematical sets, that is, no duplicate rows in tables or any query results are allowed. Thus, no SQL database is Codd-1970-relational, because SQL allows duplicate rows. Codd's 12 rules, from about 1987, demand that a 'relational database' must satisfy several strict conditions. For example, any view 'theoretically updateable should be updateable with the data manipulation language'. No existing database is even close to being Codd-12-relational. On the other hand, for example, Elmasri and Navathe in their university textbook define a 'relational database' less strictly, and mention Microsoft Access and Oracle as examples of an 'RDBMS'. Thus, MySQL apparently is Elmasri-Navathe-relational, but not Codd-1970-relational. The same holds for DB2, Oracle, and MS SQL Server. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Saqib Ali [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, October 28, 2003 10:10 PM Subject: RE: Is MySQL Relational? (was: Foreigner keys in MySQL?) so funtions that can be performed by relation algebra are a subset of the functions performed by a relational database? however isn't it required for a relational database to perform all the functions of relation algebra? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003, Jon Frisby wrote: No. Codd's rules defining what is a relational database are more specific than relational algebra can express. Essentially the requirements to be a relational database are a superset of the operations defined by relational algebra. -JF -Original Message- From: Saqib Ali [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 8:47 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Is MySQL Relational? (was: Foreigner keys in MySQL?) a question... is any DB that complies to the Relational Algebra a Relational DB??? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003 [EMAIL PROTECTED] wrote: I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack
RE: Ancestry program
It has been a while since I have looked, but I believe the National Genealogical Society has a data model for family tree software. The following links are to the NGS and GEDCOM is the file format standard. I think it should be an easy conversion to a database structure. If you do something that exports the data, it should probably export in the GEDCOM format because that is what most software packages will import. http://www.ngsgenealogy.org/ http://www.gentech.org/ngsgentech/main/Home.asp GEDCOM seems to be the standard file format: http://www.gendex.com/gedcom55/55gctoc.htm Brad -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:49 PM To: Nitin; [EMAIL PROTECTED] Subject: RE: Ancestry program well... when I do db design, I tend to start with the objects of my system. The one that comes to mind in your case is people. so you'll need a people table. well what are the details of a person? first_name Last_name Middle_name1 Middle_name2 Maiden_name [any other basic bio data] so you'll need those columns Well to keep track of each person, each one will need an ID... id's are usually numbers, so now you add a: person_id field. This field would likely have an auto_increment attribute to help number them for you ok... now that we have people, what else do we need? relationships between them well... in terms of human beings, everyone has one biological mother and one biological father, so we add in mother_id father_id leaving the values of these as null would be equivalent of being 'unknown' and we now have, data-wise, a system that can trace biological heritage, can handle siblings and half-siblings. Other ideas for objects: Marrages - this one would be tricky/interesting, as marrages can change over time, and people can have multiple marrages (although usually not two at a time, unless bigamy is allowed in your user's state/country). Strictly speaking, marrages are not necessary to trace heritage, but are good info... --From: Nitin [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 10:46 PM --To: [EMAIL PROTECTED] --Subject: Ancestry program -- --Hi all, -- --I'm developing a web based ancestry program. The user wants it to be --static, that means, it isn't for all to use, but his family. Better to --say, it'll contain only his family tree. -- --Now, I cant think of the proper db design, which will help any user to --find his or her relationship with any other person in the tree. Though, I --can design a simple database, where everything will have to be done --through queries and scripts, but I want those queries to keep as simple --as possible. -- --Any help will be appreciated, as I'm new to such a problem. -- --Thanx in advance --Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is MySQL Relational? (was: Foreigner keys in MySQL?)
Begging to differ, no vendor that I'm aware of claims to be compliant with a paper or textbook. They tend to comply with an adopted standard such as: ANSI/ISO/IEC 9075-1(through 5):1999 ISO/IEC 9075-1(through 5):1999 Collectively known as SQL:1999. While I'd offer that MySQL *is* a relational database, (even though I'm very new to the environment); I think there's some ways to go before MySQL has core compatibility with SQL:1999. That's what's more important to me, the adoption of accepted standards. -- Michael Brando Senior Manager of Engineering Applied Biosystems 3833 North First Street San Jose, CA 95134-1701 -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:29 PM To: [EMAIL PROTECTED] Subject: Re: Is MySQL Relational? (was: Foreigner keys in MySQL?) Hi! For decades, people have debated in comp.databases.theory and elsewhere how a 'relational database' should be defined. Codd's original 1970 paper sketches the relational algebra as a query language of a relational database, though Codd is not very precise about what the query language exactly should be. The paper also mentions integrity constraints. Relations in the 1970 paper are defined as mathematical sets, that is, no duplicate rows in tables or any query results are allowed. Thus, no SQL database is Codd-1970-relational, because SQL allows duplicate rows. Codd's 12 rules, from about 1987, demand that a 'relational database' must satisfy several strict conditions. For example, any view 'theoretically updateable should be updateable with the data manipulation language'. No existing database is even close to being Codd-12-relational. On the other hand, for example, Elmasri and Navathe in their university textbook define a 'relational database' less strictly, and mention Microsoft Access and Oracle as examples of an 'RDBMS'. Thus, MySQL apparently is Elmasri-Navathe-relational, but not Codd-1970-relational. The same holds for DB2, Oracle, and MS SQL Server. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Saqib Ali [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, October 28, 2003 10:10 PM Subject: RE: Is MySQL Relational? (was: Foreigner keys in MySQL?) so funtions that can be performed by relation algebra are a subset of the functions performed by a relational database? however isn't it required for a relational database to perform all the functions of relation algebra? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003, Jon Frisby wrote: No. Codd's rules defining what is a relational database are more specific than relational algebra can express. Essentially the requirements to be a relational database are a superset of the operations defined by relational algebra. -JF -Original Message- From: Saqib Ali [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 8:47 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Is MySQL Relational? (was: Foreigner keys in MySQL?) a question... is any DB that complies to the Relational Algebra a Relational DB??? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003 [EMAIL PROTECTED] wrote: I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether
mysql 4.0.16/4.0.15a build fails on Mac OS X 10.3
source='sort.c' object='sort.o' libtool=no \ depfile='.deps/sort.Po' tmpdepfile='.deps/sort.TPo' \ depmode=gcc3 /bin/sh ../depcomp \ gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include -I../include -O3 -DDBUG_OFF -c `test -f sort.c || echo './'`sort.c /bin/sh ../libtool --mode=link gcc -O3 -DDBUG_OFF -o isamchk isamchk.o sort.o libnisam.a ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lz -lm mkdir .libs gcc -O3 -DDBUG_OFF -o isamchk isamchk.o sort.o libnisam.a ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lz -lm ld: warning multiple definitions of symbol _qsort ../mysys/libmysys.a(mf_qsort.o) definition of _qsort in section (__TEXT,__text) /usr/lib/libm.dylib(qsort.So) definition of _qsort ld: Undefined symbols: operator delete(void*) operator new(unsigned long) make[2]: *** [isamchk] Error 1 make[1]: *** [all-recursive] Error 1 make: *** [all] Error 2 Anyone else encountered this problem yet? This occurs both in Mac OS X 10.3 client and server. I don't know what changed to make it stop compiling, but it worked just fine in the last dev seed of Panther... If anyone can help figure this out, let me know what to do. TIA - Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with MySQL and latest release of Mac OX X (Panther)
Much to my surprise and chagrin, I'm having a problem using MySQL after upgrading to the latest release of Mac OS X this past weekend. As far as I can tell, the server seems to be running, but when I attempt to execute the mysql command it just hangs. Any ideas? Thanks, jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with insert data
Rafal Kedziorski wrote:
hi,
I'm using MySQL 4.0.13 and 4.0.14 (4.0.16 I will test tomorrow). And I
have problems with this query:
insert into user(class_id, retail_id, mandant_id, language_id,
user_data_id, nickname, login_name, password, status, creation_date,
last_login_date) values ('35F7A660096411D89BC0D1907F01', '1234',
2, 1, null, null, '[EMAIL PROTECTED]', 'demo', 8, '2003-10-28 17:31:36',
null)
This is incorrect.
but the same query with space after user
insert into user (class_id, retail_id, mandant_id, language_id,
user_data_id, nickname, login_name, password, status, creation_date,
last_login_date) values ('35F7A660096411D89BC0D1907F01', '1234',
2, 1, null, null, '[EMAIL PROTECTED]', 'demo', 8, '2003-10-28 17:31:36',
null)
works.
This is corerect.
Regards,
Rafal
user() is a function.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with MySQL and latest release of Mac OX X (Panther)
Can you post youre my.cnf options / your query in question / how long have you seen it hang / what status it is in / and your show status vars. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 1:06 PM --To: [EMAIL PROTECTED] --Subject: problem with MySQL and latest release of Mac OX X (Panther) -- --Much to my surprise and chagrin, I'm having a problem using MySQL after --upgrading to the latest release of Mac OS X this past weekend. As far as --I can tell, the server seems to be running, but when I attempt to execute --the mysql command it just hangs. Any ideas? -- --Thanks, -- --jim -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to define a required field in a table?
Is it possible to define required fields in a MySQL table? By that I mean if the row is updated and the field value is still null, the update for the row is rejected and an error is produced. The NOT NULL clause in the table definition simply converts NULL values to '' or zero for string and numeric fields. This only makes things worse because now it is harder to spot invalid data because the zeroes and '' blends in with valid data. So I need the update to fail so I can keep invalid data (NULL's) out of the table. Any idea on how to do this? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to define a required field in a table?
That's not how it works. The following demonstrates the feature I think you want. mysql test test create table testnull ( X varchar(10) not null ); Query OK, 0 rows affected (0.11 sec) test insert into testnull values ( null ); ERROR 1048: Column 'X' cannot be null test -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:50 PM To: [EMAIL PROTECTED] Subject: How to define a required field in a table? Is it possible to define required fields in a MySQL table? By that I mean if the row is updated and the field value is still null, the update for the row is rejected and an error is produced. The NOT NULL clause in the table definition simply converts NULL values to '' or zero for string and numeric fields. This only makes things worse because now it is harder to spot invalid data because the zeroes and '' blends in with valid data. So I need the update to fail so I can keep invalid data (NULL's) out of the table. Any idea on how to do this? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with MySQL and latest release of Mac OX X (Panther)
BTW, nothing's changed in my.cnf... .my.cnf --- [client] host=localhost user=jim password=password No query, I just entered 'mysql sampdb'. It hung indefinitely; I had to kill the window. Since I couldn't get into mysql I don't know the value of any variables. -Original Message- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 4:42 PM To: Anderson, James H [IT]; [EMAIL PROTECTED] Subject: RE: problem with MySQL and latest release of Mac OX X (Panther) Can you post you're my.cnf options / your query in question / how long have you seen it hang / what status it is in / and your show status vars. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 1:06 PM --To: [EMAIL PROTECTED] --Subject: problem with MySQL and latest release of Mac OX X (Panther) -- --Much to my surprise and chagrin, I'm having a problem using MySQL after --upgrading to the latest release of Mac OS X this past weekend. As far as --I can tell, the server seems to be running, but when I attempt to execute --the mysql command it just hangs. Any ideas? -- --Thanks, -- --jim -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to define a required field in a table?
On Tue, 28 Oct 2003 14:00:02 -0800, Kevin Fries [EMAIL PROTECTED] wrote: That's not how it works. The following demonstrates the feature I think you want. mysql test test create table testnull ( X varchar(10) not null ); Query OK, 0 rows affected (0.11 sec) test insert into testnull values ( null ); ERROR 1048: Column 'X' cannot be null test One other case: If there is a default value on the column, the default will be inserted instead of NULL. -bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to define a required field in a table?
At 03:49 PM 10/28/2003, you wrote: Is it possible to define required fields in a MySQL table? By that I mean if the row is updated and the field value is still null, the update for the row is rejected and an error is produced. The NOT NULL clause in the table definition simply converts NULL values to '' or zero for string and numeric fields. This only makes things worse because now it is harder to spot invalid data because the zeroes and '' blends in with valid data. So I need the update to fail so I can keep invalid data (NULL's) out of the table. Any idea on how to do this? TIA Mike Oops. I should point out that it is the Load Data Infile that is allowing the NULL values to be converted to '' or zero. The SQL Insert statement (if I had used it) will catch the NULL's and prevent the row with NULL from being entered. But not Load Data Infile. So is there a way to get the Load Data Infile to respect the Not Null definitions? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
so funtions that can be performed by relation algebra are a subset of the functions performed by a relational database? however isn't it required for a relational database to perform all the functions of relation algebra? Saqib, there are multiple relational algebras. No db fully implements Codd's specs. The degree to which a db like MySQL implement's Codd's rules and algebra is just one arbitrary measure of 'relationality'. We have to decide which features we want, and which we can do without. PB
RE: How to define a required field in a table?
At 04:00 PM 10/28/2003, you wrote: That's not how it works. The following demonstrates the feature I think you want. mysql test test create table testnull ( X varchar(10) not null ); Query OK, 0 rows affected (0.11 sec) test insert into testnull values ( null ); ERROR 1048: Column 'X' cannot be null test Kevin, Yes, that's what I thought too for the longest time. But it doesn't work that way when using Load Data Infile. This command will actually convert NULL's to an empty string or zero for the NOT NULL fields. I now get invalid data in my tables. Nasty. :-( The short term solution is to validate the data in the input file before it gets imported. I was hoping there was an option in Load Data Infile to prevent it from converting NULL's to '' or 0. I'd rather have invalid data rejected than converted. Mike -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:50 PM To: [EMAIL PROTECTED] Subject: How to define a required field in a table? Is it possible to define required fields in a MySQL table? By that I mean if the row is updated and the field value is still null, the update for the row is rejected and an error is produced. The NOT NULL clause in the table definition simply converts NULL values to '' or zero for string and numeric fields. This only makes things worse because now it is harder to spot invalid data because the zeroes and '' blends in with valid data. So I need the update to fail so I can keep invalid data (NULL's) out of the table. Any idea on how to do this? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ancestry program
Around Tue,Oct 28 2003, at 12:15, Nitin, wrote: Hi all, I'm developing a web based ancestry program. The user wants it to be static, that means, it isn't for all to use, but his family. Better to say, it'll contain only his family tree. Now, I cant think of the proper db design, which will help any user to find his or her relationship with any other person in the tree. Though, I can design a simple database, where everything will have to be done through queries and scripts, but I want those queries to keep as simple as possible. Any help will be appreciated, as I'm new to such a problem. There's a 'ged2db' utility that converts from GED format to a database. I believe it converts from GED to mysql, postgresql and other formats. -- Roger Morris [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexes and multi-column Primary Keys
Hi, I'm not sure I understand indexes properly.
In this EXPLAIN, I expected the Cls (Classes) table to be of type ref with
the key being ObjectID. I'm joining both identically as far as I can tell,
on one of the columns in the primary key, which is set to a key itself, but
Cls is joining on ALL. Why is it doing that?
Has it got something to do with the way my query is worded?
Thanks in advance,
Chris
=EXPLAIN===
+-++--+--+-+-+--+---
---+
| table | type | possible_keys| key | key_len | ref
| rows | Extra
|
+-++--+--+-+-+--+---
---+
| NPCSpl | index | PRIMARY | PRIMARY | 10 | NULL
| 6034 | Using index; Using
temporary; Using filesort |
| ObjNPCs | eq_ref | PRIMARY | PRIMARY | 8 |
const,NPCSpl.FileID,NPCSpl.ObjectID |1 | Using where; Using index
|
| NPCs| eq_ref | PRIMARY,ObjectID | PRIMARY | 6 |
ObjNPCs.FileID,ObjNPCs.ObjectID |1 |
|
| Spl | ref| PRIMARY,ObjectID | ObjectID | 4 | NPCSpl.ObjectID_Spell
| 15 |
|
| ObjSpl | eq_ref | PRIMARY | PRIMARY | 8 |
const,Spl.FileID,Spl.ObjectID |1 | Using where; Using index
|
| Cls | ALL| PRIMARY,ObjectID | NULL |NULL | NULL
| 84 | Using where
|
| ObjCls | eq_ref | PRIMARY | PRIMARY | 8 |
const,Cls.FileID,Cls.ObjectID |1 | Using where; Using index
|
+-++--+--+-+-+--+---
---+
=QUERY=
SELECT
NPCs.FileID,
NPCs.ObjectID,
NPCs.Name,
Spl.FileID as FileID_Spell,
Spl.ObjectID as ObjectID_Spell,
Spl.Name as Name_Spell
FROM NPCs
JOIN GroupedObjects ObjNPCs
ON
(
1=ObjNPCs.GroupID
AND
NPCs.FileID=ObjNPCs.FileID
AND
NPCs.ObjectID=ObjNPCs.ObjectID
)
JOIN NPCSpells NPCSpl
ON
(
ObjNPCs.FileID=NPCSpl.FileID
AND
ObjNPCs.ObjectID=NPCSpl.ObjectID
)
JOIN Spells Spl
ON
(
Spl.ObjectID=NPCSpl.ObjectID_Spell
)
INNER JOIN GroupedObjects ObjSpl
ON
(
1=ObjSpl.GroupID
AND
Spl.FileID=ObjSpl.FileID
AND
Spl.ObjectID=ObjSpl.ObjectID
)
JOIN Classes Cls
ON
(
Cls.ObjectID=NPCs.ObjectID_Class
)
INNER JOIN GroupedObjects ObjCls
ON
(
1=ObjCls.GroupID
AND
Cls.FileID=ObjCls.FileID
AND
Cls.ObjectID=ObjCls.ObjectID
)
WHERE
2048 NPCs.Services
OR
2048 Cls.Services
ORDER BY
NPCs.ObjectID;
=TABLES
CREATE TABLE `Spells` (
`FileID` smallint(5) unsigned NOT NULL default '0',
`ObjectID` int(10) unsigned NOT NULL default '0',
`Name` char(32) default NULL,
`SpellTypeID` tinyint(4) NOT NULL default '0',
`Cost` int(11) NOT NULL default '0',
`Flags`
set('0x1','0x2','0x4','0x8','0x10','0x20','0x40','0x80','0x100','0x200','0x400','0x800','0x1000','0x2000','0x4000','0x8000','0x1
','0x2','0x4','0x8','0x10','0x20','0x40','0x80','0x100','0x200','0x400','0x800','0x1
000','0x2000','0x4000','0x8000') NOT NULL default '',
PRIMARY KEY (`FileID`,`ObjectID`),
KEY `ObjectID` (`ObjectID`)
) TYPE=MyISAM COMMENT='Spells'
CREATE TABLE `Classes` (
`FileID` smallint(5) unsigned NOT NULL default '0',
`ObjectID` int(10) unsigned NOT NULL default '0',
`Name` varchar(32) NOT NULL default '',
`AttrID_Pri0` tinyint(4) NOT NULL default '0',
`AttrID_Pri1` tinyint(4) NOT NULL default '0',
`SpecID` tinyint(4) NOT NULL default '0',
`SkillID_Maj0` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj1` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj2` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj3` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj4` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min0` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min1` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min2` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min3` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min4` tinyint(3) unsigned NOT NULL default '0',
`Flags`
set('Playable','0x2','0x4','0x8','0x10','0x20','0x40','0x80','0x100','0x200','0x400','0x800','0x1000','0x2000','0x4000','0x8000','0x
1','0x2','0x4','0x8','0x10','0x20','0x40','0x80','0x100','0x200','0x400','0x800','0x
1000','0x2000','0x4000','0x8000') NOT NULL default '',
`Services`
RE: problem with MySQL and latest release of Mac OX X (Panther)
Could you look at youre hostname.err file and see if it says checking table? Also try mysql -A sampdb - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 2:03 PM --To: 'Dathan Vance Pattishall'; [EMAIL PROTECTED] --Subject: RE: problem with MySQL and latest release of Mac OX X (Panther) -- --BTW, nothing's changed in my.cnf... -- --.my.cnf - --[client] --host=localhost --user=jim --password=password -- --No query, I just entered 'mysql sampdb'. It hung indefinitely; I had to --kill --the window. --Since I couldn't get into mysql I don't know the value of any variables. -- -- ---Original Message- --From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 4:42 PM --To: Anderson, James H [IT]; [EMAIL PROTECTED] --Subject: RE: problem with MySQL and latest release of Mac OX X (Panther) -- -- --Can you post you're my.cnf options / your query in question / how long --have you seen it hang / what status it is in / and your show status --vars. -- -- -- --- Dathan Vance Pattishall -- - Sr. Programmer and mySQL DBA for FriendFinder Inc. -- - http://friendfinder.com/go/p40688 -- -- -Original Message- From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:06 PM To: [EMAIL PROTECTED] Subject: problem with MySQL and latest release of Mac OX X (Panther) Much to my surprise and chagrin, I'm having a problem using MySQL --after upgrading to the latest release of Mac OS X this past weekend. As far --as I can tell, the server seems to be running, but when I attempt to --execute the mysql command it just hangs. Any ideas? Thanks, jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with insert data
At 15:09 28.10.2003 -0600, gerald_clark wrote:
Rafal Kedziorski wrote:
hi,
I'm using MySQL 4.0.13 and 4.0.14 (4.0.16 I will test tomorrow). And I
have problems with this query:
insert into user(class_id, retail_id, mandant_id, language_id,
user_data_id, nickname, login_name, password, status, creation_date,
last_login_date) values ('35F7A660096411D89BC0D1907F01', '1234', 2,
1, null, null, '[EMAIL PROTECTED]', 'demo', 8, '2003-10-28 17:31:36', null)
This is incorrect.
This query will be generated by JBoss 3.2.2. It's also a JBoss problem?
but the same query with space after user
insert into user (class_id, retail_id, mandant_id, language_id,
user_data_id, nickname, login_name, password, status, creation_date,
last_login_date) values ('35F7A660096411D89BC0D1907F01', '1234', 2,
1, null, null, '[EMAIL PROTECTED]', 'demo', 8, '2003-10-28 17:31:36', null)
works.
This is corerect.
Regards,
Rafal
user() is a function.
o.k.
Rafal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Daylight Savings Time
Hello, We had a strange instance happen here with one of our MySQL servers. We have multiple MySQL installations, and one of the MySQL servers had a problem with daylight savings time. Basically, when we ran a select now(); MySQL reported time that was two hours ahead of the actual time. The system /bin/date command was correct however. We restarted the MySQL server and the time was corrected. I have looked at 'most' of our other servers, and none of them (so far) has had this issue. I am just wondering if anyone else has seen this problem? -James signature.asc Description: This is a digitally signed message part
RE: reproducible error 17
Thanks for the information. I to found a work around-a much uglier approach. For all ALTERs of a src table to work when synonyms of tables are present: perform the ALTER on the slave itself and add to skip-slave-errors=1060 This is a quick work around. Very very ugly. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Guilhem Bichot [mailto:[EMAIL PROTECTED] --Sent: Tuesday, October 28, 2003 3:32 AM --To: Dathan Vance Pattishall --Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; --[EMAIL PROTECTED] --Subject: RE: reproducible error 17 -- --On Tue, 2003-10-28 at 12:07, Guilhem Bichot wrote: -- On Tue, 2003-10-28 at 00:15, Guilhem Bichot wrote: -- On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote: -- -- So the conclusion is: unfortunately, the symlink support in MySQL was -- not designed for synonyming, as far as DDL (Data Definition --Language - -- CREATE TABLE / DROP TABLE / ALTER TABLE) commands are concerned. It --was -- designed with the thought that symlinks are to be used to point to a -- *different* directory (another partition where there is more room, or -- another device to balance disk load). For DDL commands MySQL always -- expects a table to exist only once, i.e. to have only one name. --Putting, -- in the database directory, a symlink and the real table means giving --2 -- names to one table... -- -- I will add a note about this into our manual soon. I understand this --is -- is an inconvenience for you; you will be safe if you always do the --DDL -- commands (ALTER TABLE, in your case) on the real table. It's ok to do -- DML commands (INSERT/DELETE/UPDATE/LOADDATA, which fortunately occur -- much more often than ALTER TABLE normally) on both tables --indifferently. -- -- Sorry, I should have been more accurate in the last sentence. -- It's ok to do DML commands *always* on the real table OR *always* on --the -- synonym table. -- If thread1 uses the real table's name, and thread 2 uses the synonym, -- the query cache can be fooled: -- - set global query_cache_size=100; -- - connection1: select * from tbl_; -- - connection2: insert into tbl values(1); -- - connection1: select * from tbl_; you don't see the inserted row! -- - connection1: flush tables (empties caches); select * from tbl_; you -- see the inserted row! -- -- Even if you disable the query cache, I am not sure if it's safe to use -- both names; there could be some other fooled caches in MySQL. -- -- Simply put, things go wild when the real name and the synonym are both -- used. Which impacts the interest of using synonyms (hum). And FLUSH -- TABLES is a remedy. -- -- I'll add this to the manual. -- --Added. You should be able to see it in our online manual --www.mysql.com/doc --(end of section Using symbolic links) in the next hours. -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indexes and multi-column Primary Keys
Hmmm... I fixed the problem but I still don't know what caused it. That same query now produces the below EXPLAIN result and now runs 70% faster. I ran OPTIMIZE TABLE on Spells and Classes, that made Spl rows 1, then I forced the Cls table to use the Object index and it worked. Now, I can't get it to reproduce the problem. The database was just created and populated, so it couldn't have been DB degradation. Any ideas? Chris +-++--+--+-+-+--+--- ---+ | table | type | possible_keys| key | key_len | ref | rows | Extra | +-++--+--+-+-+--+--- ---+ | NPCSpl | index | PRIMARY | PRIMARY | 10 | NULL | 6034 | Using index; Using temporary; Using filesort | | ObjNPCs | eq_ref | PRIMARY | PRIMARY | 8 | const,NPCSpl.FileID,NPCSpl.ObjectID |1 | Using where; Using index | | NPCs| eq_ref | PRIMARY,ObjectID | PRIMARY | 6 | ObjNPCs.FileID,ObjNPCs.ObjectID |1 | | | Spl | ref| PRIMARY,ObjectID | ObjectID | 4 | NPCSpl.ObjectID_Spell |1 | | | ObjSpl | eq_ref | PRIMARY | PRIMARY | 8 | const,Spl.FileID,Spl.ObjectID |1 | Using where; Using index | | Cls | ref| PRIMARY,ObjectID | ObjectID | 4 | NPCs.ObjectID_Class |1 | Using where | | ObjCls | eq_ref | PRIMARY | PRIMARY | 8 | const,Cls.FileID,Cls.ObjectID |1 | Using where; Using index | +-++--+--+-+-+--+--- ---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Daylight Savings Time
James, We had a strange instance happen here with one of our MySQL servers. We have multiple MySQL installations, and one of the MySQL servers had a problem with daylight savings time. Basically, when we ran a select now(); MySQL reported time that was two hours ahead of the actual time. The system /bin/date command was correct however. Were they all on the same operating system and version? DSL -- The Open Source Business Network in SA ...will be open soon! Watch this space. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Almost beating Rushmore -- was: Really slow query (compared with Visual FoxPro)
Héctor, Changing various buffer sizes can improve performance significantly, with key_buffer_size as the first thing to tune. Also, make sure you are using not debugging version of the server. Please also take a look in this section of the documentation for details: http://www.mysql.com/doc/en/Optimising_the_Server.html Héctor Villafuerte D. wrote: Hi guys! Ok, I'm closer to beat Rushmore (VFP optimizer) now! After some reading about MySQL optimization techniques, here is the summary of what I've done: 1. Add a compound index to the table 2. Use EXPLAIN to check out the query (with GROUP BY on multiple fields) 3. Create the summary table And here's the detailed instructions: mysql alter table traf_oper add index (tel, telefb, rutaentran, rutasalien, minutos); Query OK, 5067215 rows affected (5 min 22.36 sec) Records: 5067215 Duplicates: 0 Warnings: 0 mysql explain select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4; ++-+---+---+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+---+---+--+-+--+-+-+ | 1 | SIMPLE | traf_oper | index | NULL | tel | 45 | NULL | 5067215 | Using index | ++-+---+---+---+--+-+--+-+-+ 1 row in set (0.03 sec) mysql create table grp_oper select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3326541 rows affected (33.81 sec) Records: 3326541 Duplicates: 0 Warnings: 0 Adding the times together it would take MySQL like 6 minutes (VFP does it in about 4 minutes). I still haven't tweaked some server variables (read_rnd_buffer_size, sort_buffer_size, max_join_size), but, as always, I'll keep trying :) Thanks in advance for your comments and suggestions, Hector -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Barkov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Izhevsk, Russia ___/ www.mysql.com +7-912-856-80-21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
