RE: Find the biggest blobs
Did you try: select blob_field from blob_table order by length(blob_field) DESC limit 1 Regards, Artem -Original Message- From: Roland Carlsson [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 01, 2005 10:02 AM To: mysql@lists.mysql.com Subject: Find the biggest blobs Hi! I've need to find the largest blobs in a table but I seem not to be able to figure out what it is. Could anyone please help me with this? Regards Roland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I Put a 'Literal' Value in a Report Column?
see 12.2 Control Flow functions of MySQL manual select name, if(adopted=0,'N','Y') from animal; -Original Message- From: Sue Cram [mailto:[EMAIL PROTECTED] Sent: Monday, February 07, 2005 2:58 AM To: mysql@lists.mysql.com Subject: How do I Put a 'Literal' Value in a Report Column? This is a pretty basic question, but I'm learning SQL from a book and it's very very frustrating! I'm writing a report listing animals from our shelter and whether they have been adopted. I am selecting the animal name field and a field called 'adopted' from a table called 'animal'. The values in 'adopted' are either: 1 (meaning yes, this animal was adopted) or 0 (no, this animal has not been adopted). So far I: SELECT Name, Adopted FROM animal. This prints a column of animal names and a column labeled Adopted with row after row of 0's and 1's. I want my output report to say 'Y' if 'adopted' = 1 or 'N' if adopted = 0. I've spent all day studying Insert Into, Update Where, If... Then, looking at SQL manuals (so far I've studied 3 of them), and trying to find an example in existing code. I get syntax errors for everything I try. This is a very simple thing that's done all the time -- but I can't figure out how in SQL. Can someone help me? Thanks, Sue Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB engine as default for an entire database
default-storage-engine=INNODB in [mysqld] section of the config file will make InnoDB default for a server. -Original Message- From: symbulos partners [mailto:[EMAIL PROTECTED] Sent: Friday, January 28, 2005 8:03 AM To: mysql@lists.mysql.com Subject: InnoDB engine as default for an entire database Dear friend, is it possible to select the InnoDB engine as default for an entire database? What we want is to create all InnoDB tables, without using the declaration type=innodb. -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: cascade on delete problem
Hello Scott, Make sure your tables are InnoDB type: CREATE TABLE table_name ( table_def ...) ENGINE=InnoDB; If you have default MyISAM tables, it won't work because they don't support foreign keys. -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 10:17 AM To: mysql@lists.mysql.com Subject: cascade on delete problem Hello, I apologize for a possible simple question, but I am having trouble with the below code. I have three simple tables. In short, menu_group has an id, that is referenced in the menu_group_rel. When a user deletes an id from the menu_group, I wanted the entry in menu_group_rel (data_id) to also be deleted. They act as one piece of data. I have gone through the docs, but when I delete a line from the menu_group, it does NOT delete the entry from the menu_group_rel? Does anyone see anything wrong with the following? Thanks, SQL: ### DROP DATABASE builder2; GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell' WITH GRANT OPTION; CREATE DATABASE builder2; use builder2; CREATE TABLE menu_sequence (id INT NOT NULL); insert into menu_sequence VALUES (0); CREATE TABLE MENU_GROUP ( id int NOT NULL, parent_id int NOT NULL DEFAULT '0', sort int, visible VARCHAR(1) NOT NULL DEFAULT 'T', ); ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 3, 1, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 4, 1, 'T'); CREATE TABLE MENU_TYPE ( id INT NOT NULL AUTO_INCREMENT, attribute_type varchar(200) NOT NULL, primary key (id) ); INSERT INTO MENU_TYPE (attribute_type) values ('jsp'), ('menu'), ('cat_name'); CREATE TABLE MENU_GROUP_REL ( menu_type varchar(200), data_id int NOT NULL, display_name varchar(250), link varchar(250), ); ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN KEY(data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE; INSERT INTO MENU_GROUP_REL values (1, 1, 'Company', 'companyInfo.jsp'); INSERT INTO MENU_GROUP_REL values (3, 2, 'Communities', 'communities'); INSERT INTO MENU_GROUP_REL values (1, 3, 'Contact', 'contact.jsp'); INSERT INTO MENU_GROUP_REL values (1, 4, 'Inventory Homes', 'invHomes.jsp'); INSERT INTO MENU_GROUP_REL values (1, 5, 'About Me', 'about.jsp'); INSERT INTO MENU_GROUP_REL values (1, 6, 'Customer Service', 'custService.jsp'); INSERT INTO MENU_GROUP_REL values (1, 7, 'Open Sunday', 'opensunday.jsp'); Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: drop table is written to bin-log, load table is NOT - why????
Hello Lutz, I was not aware of this behavior of the master server. Maybe somebody with more insight can explain. If the traffic volume is so important I would turn on compression on the master-slave connection to reduce network traffic. I think it is slave_compressed_protocol=1 option in the [mysqld] section of the MySQL configuration file. Regards, Artem -Original Message- From: Lutz Maibach [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 9:58 AM To: mysql Liste Subject: Re: drop table is written to bin-log, load table is NOT - why Hello Artem, thanks for your answer. For selective replication I would check startup options --replicate-do-* and --replicate-wild-*. I'm using replicate-do-db on the client but the problem is, that the master sends the sql-statements of ALL databases to the repl-client and the replicate-do-db-command only tells the client which sql-statements it should carry out and which statements it should drop without notice. In the actual case only 5% of the traffic is caused by the database which needs to be replicated so we would have to pay for 95% useless traffic if we wouldn't use daisy-chain Replication to filter the traffic. . Also you may need log-slave-update option on if you have daisy-chain replication. I'm using this option too but it seems to have no effect doing a load table ... from master. Greetings from Germany Lutz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cascade problem now error:
', 'custService.jsp'); INSERT INTO MENU_GROUP_REL values (1, 7, 'Open Sunday', 'opensunday.jsp'); -Original Message- From: Artem Koltsov [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 10:29 AM To: Scott Purcell; mysql@lists.mysql.com Subject: RE: cascade on delete problem Hello Scott, Make sure your tables are InnoDB type: CREATE TABLE table_name ( table_def ...) ENGINE=InnoDB; If you have default MyISAM tables, it won't work because they don't support foreign keys. -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 10:17 AM To: mysql@lists.mysql.com Subject: cascade on delete problem Hello, I apologize for a possible simple question, but I am having trouble with the below code. I have three simple tables. In short, menu_group has an id, that is referenced in the menu_group_rel. When a user deletes an id from the menu_group, I wanted the entry in menu_group_rel (data_id) to also be deleted. They act as one piece of data. I have gone through the docs, but when I delete a line from the menu_group, it does NOT delete the entry from the menu_group_rel? Does anyone see anything wrong with the following? Thanks, SQL: ### DROP DATABASE builder2; GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'spurcell' WITH GRANT OPTION; CREATE DATABASE builder2; use builder2; CREATE TABLE menu_sequence (id INT NOT NULL); insert into menu_sequence VALUES (0); CREATE TABLE MENU_GROUP ( id int NOT NULL, parent_id int NOT NULL DEFAULT '0', sort int, visible VARCHAR(1) NOT NULL DEFAULT 'T', ); ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP PRIMARY KEY(id); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 1, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 2, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 3, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 0, 4, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 1, 1, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 3, 1, 'T'); UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1); INSERT INTO MENU_GROUP values (last_insert_id(), 4, 1, 'T'); CREATE TABLE MENU_TYPE ( id INT NOT NULL AUTO_INCREMENT, attribute_type varchar(200) NOT NULL, primary key (id) ); INSERT INTO MENU_TYPE (attribute_type) values ('jsp'), ('menu'), ('cat_name'); CREATE TABLE MENU_GROUP_REL ( menu_type varchar(200), data_id int NOT NULL, display_name varchar(250), link varchar(250), ); ALTER TABLE MENU_GROUP ADD CONSTRAINT PK_MENU_GROUP_REL FOREIGN KEY(data_id) REFERENCES MENU_GROUP(id) ON DELETE CASCADE; INSERT INTO MENU_GROUP_REL values (1, 1, 'Company', 'companyInfo.jsp'); INSERT INTO MENU_GROUP_REL values (3, 2, 'Communities', 'communities'); INSERT INTO MENU_GROUP_REL values (1, 3, 'Contact', 'contact.jsp'); INSERT INTO MENU_GROUP_REL values (1, 4, 'Inventory Homes', 'invHomes.jsp'); INSERT INTO MENU_GROUP_REL values (1, 5, 'About Me', 'about.jsp'); INSERT INTO MENU_GROUP_REL values (1, 6, 'Customer Service', 'custService.jsp'); INSERT INTO MENU_GROUP_REL values (1, 7, 'Open Sunday', 'opensunday.jsp'); Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- 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] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you
RE: Installing MySql 4.1
I had similar problem after incomplete removal MySQL 4.0.x which left dead service named MySQL that points to non-existing path c:\mysql. I changed the windows service name for MySQL 4.1 to MySQL41, and it works fine. Also you can try to execute mysqld from command line to see any extra details: mysqld --console --standalone -Original Message- From: Darrell and Lynda Adams [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 4:34 PM To: mysql@lists.mysql.com Subject: Installing MySql 4.1 Trying to install MySql 4.1 on XP using the automated installer. Have tried to install both the windows essentials and windows X86 . Tried doing typical and custom versions. Also used the Instance Configuration both detail and standard. When I get to the dialog box with the execute button I receive the following error message: Cannot create windows service for MySql. Error:0. Also went into services and tried to start and received the error message Could not start MySql service on local computer. Error 3: The system cannot find the path specified. OBVIOUSLY I dont have something set. Any help appreciated. Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: drop table is written to bin-log, load table is NOT - why????
Hello Lutz, As far as I know, binlog records only DML and DDL statements, and LOAD TABLE FROM MASTER is not the one. For selective replication I would check startup options --replicate-do-* and --replicate-wild-*. See http://dev.mysql.com/doc/mysql/en/replication-options.html for details. Also you may need log-slave-update option on if you have daisy-chain replication. Regards, Artem -Original Message- From: Lutz Maibach [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 8:24 AM To: mysql Liste Subject: drop table is written to bin-log, load table is NOT - why Hello, I'm wondering why on a replication client (MySQL 4.0.23) a drop table XYZ is written to the mysql binlog while the following load table XYZ from master is not. This missing binlog-entry in the first repl. client causes a second replication client, which is replicating the first client, (sounds silly but this construction is necessary to filter the entries of a single database out of dozen hosted on the master and to replicate the logentries for this database only) to go out of sync cause it still holds the old values while the first client now is up to date. Help would be appreciated Lutz Maibach EasyCom GmbH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error in your SQL syntax
Try Query Browser ( http://dev.mysql.com/downloads/query-browser ) for building queries for MySQL. Regards, Artem -Original Message- From: Daniel Sousa [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 11:18 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: error in your SQL syntax Thanks, works fines. I use access because i don´t know a GUI tool that make SQL querys more easy. Thanks all again, Daniel Sousa - Original Message - From: [EMAIL PROTECTED] To: Daniel Sousa Cc: mysql@lists.mysql.com Sent: Wednesday, 26 January, 2005 14:57 Subject: Re: error in your SQL syntax Here is your original query, reformatted merely so that we humans can read it better: SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM languages INNER JOIN ( ( (specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories.categories_id )ON specials.products_id = products_to_categories.products_id ) INNER JOIN products ON specials.products_id = products.products_id ) INNER JOIN products_description ON specials.products_id = products_description.products_id ) ON languages.languages_id = products_description.language_id WHERE ( ( (categories.parent_id)=285 ) AND ( (languages.languages_id)=1 ) ) This query design stinks (reeks) of being autogenerated by M$ Access. The excessive use of parentheses when they aren't needed and the nested JOINs just complicate the query unnecessarily. May I suggest a simplification? SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM categories INNER JOIN products_to_categories ON products_to_categories.categories_id = categories.categories_id INNER JOIN products ON products.products_id = products_to_categories.products_id INNER JOIN specials ON specials.products_id = products.products_id INNER JOIN products_description ON products.products_id = products_description.products_id INNER JOIN languages ON products_description.language_id = languages.languages_id WHERE categories.parent_id=285 AND languages.languages_id=1; I have also noticed in my Windows command shell that it does not process extremely long lines in pastes from the clipboard well. If you copied that straight from Access to a MySQL prompt, it would have been just one long line of information and the DOS command processor would have eventually stopped taking input mid-query. I suspect that is what caused your otherwise acceptable (and I use that term loosely ;-) ) query to be invalid. The last third of it never made it into the MySQL CLI. When I break my queries into shorter lines (human friendly) and paste them into the MySQL command line interface (CLI), everything works just fine. Just copy the entire query (line breaks and all) onto the clipboard and paste it at the MySQL prompt (if that's how you are doing it) and see if it works now. Notepad is my best friend when working in the CLI. I compose and format long queries in Notepad then copy-paste into MySQL. I know it's doing it the hard way (yes, I have and do use the GUI tools too) but it's how I prefer to analyze certain issues. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel Sousa [EMAIL PROTECTED] wrote on 01/26/2005 06:45:32 AM: I have a problem. 1064 - You have an error in your SQL syntax near '(((specials INNER JOIN (products_to_categories INNER JOIN categories ON products' at line 2 SELECT DISTINCT specials.specials_id, products_to_categories. products_id, categories.parent_id, products_description. products_name, products.products_price, products. products_tax_class_id, products.products_image, specials. specials_new_products_price,
RE: Serious bug (or my foolishness) with alter table and InnoDB
Works fine on WinXP 4.1.8. Only generates warning: mysql show warnings; +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead | +-+--+--+ And here is the `child` table after execution: mysql show create table child; +---+ - | Table | Create Table +---+ - | child | CREATE TABLE `child` ( `id` int(11) NOT NULL default '0', `name` char(1) NOT NULL default '', PRIMARY KEY (`id`,`name`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`id`) REFERENCES `master` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---+ - -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 25, 2005 10:50 AM To: mysql@lists.mysql.com Subject: Serious bug (or my foolishness) with alter table and InnoDB Hello, I am running mysql 4.1.7 on Win2K. I have two tables: CREATE TABLE `child` ( `id` int(11) NOT NULL default '0', `name` char(1) NOT NULL default '', PRIMARY KEY (`id`,`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `master` ( `id` int(11) NOT NULL default '0', `name` char(10) NOT NULL default '', PRIMARY KEY (`id`,`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; If I execute the following statement: alter table child add foreign key (id) references master (id), type = innodb; the mysql server hangs and needs to be killed. After restarting the table child is also lost. Is this a known bug? Karam __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: adding a large file to a database....
Did you try to increase max_allowed_packet in server config to 5M, for example. The default size is 1M and it is less then the size of your file (1.5M). Artem -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: Monday, January 24, 2005 11:45 AM To: mysql@lists.mysql.com Cc: 'Mark Mchugh' Subject: RE: adding a large file to a database I have found that MySQL really doesn't like the rs.Update and rs.AddNew features like SQL and Access do. You are better off with the INSERT INTO tblname (`rs.Flds`) VALUES ('objitem') or the UPDATE tblname SET column1=value1,column2=value2,... statements with CONPUBS.EXECUTE. It's a pain to convert, but you will find that it writes faster to your MySQL database and you have less errors. Just a little more info... J.R. -Original Message- From: Mark Mchugh [mailto:[EMAIL PROTECTED] Sent: Monday, January 24, 2005 11:31 AM To: mysql list Subject: adding a large file to a database hi all, I am trying to add a large file to my database, and it does not seem to work? i am using the following code Dim mystream As ADODB.Stream Set mystream = New ADODB.Stream mystream.Type = adTypeBinary Set rs = New ADODB.Recordset rs.ActiveConnection = connMySQL rs.Open sqlstr, connMySQL, adOpenStatic, adLockOptimistic rs.AddNew mystream.Open mystream.LoadFromFile strFileName rs!file_name = CatDir rs!file_size = mystream.Size rs!File = mystream.Read rs.Update mystream.Close rs.Close when i try to add a file that is ( dont laugh) 1.5 megs or over, i get a message saying lost connection to mysql server during query can anybody help? thanks __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 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] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Undo function?
Hello, If you define table type as InnoDB, you can use transactions (see the link below). You will need set AUTOCOMMIT=0, and after you can issue COMMIT or ROLLBACK at the end of query or session to submit or cancel a transaction. I don't think you can use transactions for mysql system tables because they have to be MyISAM type, so the best solution for them will be backups. http://dev.mysql.com/doc/mysql/en/InnoDB_transaction_model.html -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 8:17 AM To: mysql@lists.mysql.com Subject: Undo function? Hi, I get very nervous when I log onto my database via SSH and type in queries manually. Take the following query for example: Delete FROM Users WHERE User_ID = 5; If I hit return before I start typing WHERE then things would be disastorous! Is there any type of undo function with MySQL? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sub query is extermely slow
Check ALTER statement in MySQL doc. It explains how to add/modify an index after a table has been created. -Original Message- From: sam wun [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 19, 2005 10:00 AM Cc: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Clint Edwards wrote: Sam, Can you create an index on transaction.date, then run your query again? If that is not better send me the output of 'explain query' again. This index may not be a good idea, depending on how many transaction are in the table on a specified date. May I ask how to add index to a field after a table is created? Thanks Sam Clint From: sam wun [EMAIL PROTECTED] To: Clint Edwards [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 22:05:58 +0800 Clint Edwards wrote: Sam, Can you send the following information: When was the last time 'analyze table table_name' (inventory, transaction, customer) was executed? Hi, here is the result of the analyze command: mysql analyze table inventory,transaction, customer; +--+-+--+--+ | Table| Op | Msg_type | Msg_text | +--+-+--+--+ | datacube.inventory | analyze | status | OK | | datacube.transaction | analyze | status | OK | | datacube.customer| analyze | status | OK | +--+-+--+--+ 3 rows in set (0.83 sec) OS: MySQL Version: Mysql 5.0 Available Ram: 254RAM Output from 'SHOW CREATE TABLE table_name' (inventory, transaction, and customer): Output from SHOW VARIABLES LIKE '%buffer%';: mysql SHOW CREATE TABLE inventory; +---+- --- -- -- -- -- ---+ | Table | Create Table | +---+- --- -- -- -- -- ---+ | inventory | CREATE TABLE `inventory` ( `prodcode` varchar(32) NOT NULL default '', `qty` decimal(9,2) NOT NULL default '0.00', `lastupdatedate` date NOT NULL default '-00-00', `prodname` varchar(32) default 'UNKNOWN', `basename` varchar(32) default 'UNKNOWN', `vendorname` varchar(50) default 'UNKNOWN', `cost` decimal(9,2) NOT NULL default '0.00', PRIMARY KEY (`prodcode`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+- --- -- -- -- -- ---+ 1 row in set (0.39 sec) mysql SHOW CREATE TABLE transaction; +-+--- --- -- -- -- -- -- -- -- -- --
RE: Replication Slave I/O Thread won't start on 4.1.8
- make sure log-bin is enabled on both master and slave (looks like it is not present in the slave config) - check replication account permissions on the master. I don't remember details, but you can find required permissions in the docs on mysql web site, or doc file in mysql installation directory. -Original Message- From: Frank Febbraro [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 18, 2005 9:41 AM To: mysql@lists.mysql.com Subject: Re: Replication Slave I/O Thread won't start on 4.1.8 One thing I left out is that we are running 4.1.8-Max-log not 4.1.8-standard-log Could that have anything to do with it? Thanks for any insight, Frank On Mon, 17 Jan 2005 15:55:06 -0500, Frank Febbraro [EMAIL PROTECTED] wrote: Hey all, I have setup replication in the past on 4.0.x servers so I figured I knew what I was doing...silly me. I reread all of the docs and best I can tell I followed them sufficiently. When I start my slave, the SQL Thread starts, but the I/O thread never starts, and thus the Master Thread never starts. There are no error messages in any logs, or the show slave status screen. Below are my relevant configurations. I am very sorry if I have left anything significant out. Thank you for any help, Frank MASTER CONFIG ~~ [EMAIL PROTECTED] cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock server-id=100 log-bin binlog-do-db=internal binlog-ignore-db=mysql [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ~~ mysql show master status\G *** 1. row *** File: server2-bin.04 Position: 874 Binlog_Do_DB: internal Binlog_Ignore_DB: mysql 1 row in set (0.00 sec) ~~ SLAVE CONFIG ~~ [EMAIL PROTECTED] cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock server-id=200 replicate-do-db=internal [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ~~ [EMAIL PROTECTED] cat /var/lib/mysql/master.info 14 server2-bin.03 79 cms.internal.org slave slavepass 3306 60 0 ~~ mysql show slave status \G *** 1. row *** Slave_IO_State: Master_Host: cms.internal.org Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: server2-bin.03 Read_Master_Log_Pos: 79 Relay_Log_File: server1-relay-bin.01 Relay_Log_Pos: 4 Relay_Master_Log_File: server2-bin.03 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: internal Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 79 Relay_Log_Space: 4 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Slave I/O Thread won't start on 4.1.8
- make sure log-bin is enabled on both master and slave (looks like it is not present in the slave config) Why is log-bin needed on the slave? I thought the master logs changes and the slave reads those changes and updates it's copy. Why should the slave also log changes it is making? There was no mention of this in the docs. You are right, log-bin is not required on a slave. I have it in my setup with log-slave-updates on for a chained replication where slave works as master as well. - check replication account permissions on the master. I don't remember details, but you can find required permissions in the docs on mysql web site, or doc file in mysql installation directory. Turns out when my hosting provider installed MySQL 4.1, it was actually an install over a 3.x data directory. The Priv tables were never updated, so I never had the appropriate REPLICATION SLAVE Priv, so the thread never started. It never did say why it did not start though. Check this one out http://dev.mysql.com/doc/mysql/en/Upgrading-grant-tables.html for priv table upgrade. Also you slave account may need more permissions if you want to use LOAD * FROM MASTER commands. Threads are now running, but data is not getting replicated. Need to figure that out now. Below are settings I have for both slave and master. Maybe you can try them in your setup. default-character-set=utf8 # time zone has to be the same on master and slave for correct replication (from 4.1) default-time-zone=GMT log_warnings log_slow_queries log-bin log-slave-updates slave_compressed_protocol=1 # skip illegal collation error slave-skip-errors=1267 thanks again. Frank Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Selecting appointment whose 3 minutes later then now()
select * from appointment a where a.adatetime now() - interval 3 minute; read user manual date/time functions. it's all there with examples. -Original Message- From: Prabu Subroto [mailto:prabusubroto;yahoo.com] Sent: Thursday, October 24, 2002 9:21 AM To: [EMAIL PROTECTED] Subject: Selecting appointment whose 3 minutes later then now() Dear my friend, I am using MySQL as database server. I still have problem now to select appointment time whose 3 minute difference (later) than now(). I can not use timestampdiff. Is there TimeStampDiff command in MySQL ? I tried this command but it didn't work : select * from appointment where date=curdate() and timestampdiff(2, time, concat(hour(now()),':',minute(now()) ); Thank you very much in advance. __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 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 and delete the material from any system and destroy any copies. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
null join bug variation?
Description: Probably it's variation of recent bug with null joins, but just in case I want to post it here because result depends on values inserted into tables, even if these values are not participating in join. After data insert with nulls the same query will produce different result after add index. How-To-Repeat: create table t1 (id int); #1 create table t2 (id int); #2 insert into t1 values (null), (1); #3 insert into t2 values (null), (1), (null), (2); #4 select * from t1; #5 select * from t2; #6 select * from t1, t2 where t1.id =3D t2.id; #7 alter table t2 add index iid (id); #8 select * from t1, t2 where t1.id =3D t2.id; #9 #here is output for the last 5 lines of the script #remarkably it doesn't join null if there is no (2) value inserted in line 4 mysql select * from t1; +--+ | id | +--+ | NULL | |1 | +--+ 2 rows in set (0.00 sec) mysql select * from t2; +--+ | id | +--+ | NULL | |1 | | NULL | |2 | +--+ 4 rows in set (0.00 sec) mysql select * from t1, t2 where t1.id =3D t2.id; +--+--+ | id | id | +--+--+ |1 |1 | +--+--+ 1 row in set (0.00 sec) mysql alter table t2 add index iid (id); Query OK, 4 rows affected (0.10 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql select * from t1, t2 where t1.id =3D t2.id; +--+--+ | id | id | +--+--+ | NULL | NULL | | NULL | NULL | |1 |1 | +--+--+ 3 rows in set (0.01 sec) mysql Fix: add not null conditions in where clause on one of join values, for ex: select * from t1, t2 where t1.id =3D t2.id and t1.id is not null; Synopsis: synopsis of the problem (one line) Submitter-Id: submitter ID Originator: your name Organization: your organization MySQL support: none Severity: non-critical Priority: low Category: mysqld, mysql client Class: sw-bug Release:Server version 4.0.4-beta-max-nt Exectutable: mysqld-nt Environment: P2 320MB RAM 6G HDD System:Win200 SP3 Compiler: VC++ 6.0 Architecture: i Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 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 and delete the material from any system and destroy any copies. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: DBI Connect Fails after 20,000 record inserts/updates
Do you connect/disconnect before/after every insert? -Original Message- From: Dallas Engelken [mailto:dallase;nmgi.com] Sent: Thursday, October 24, 2002 9:52 AM To: [EMAIL PROTECTED] Subject: DBI Connect Fails after 20,000 record inserts/updates I am trying to parse an email log database with about 900,000 records for records older than 2 days (ie 600,000 records). I make a dbi connection and select all records older than 2 days. It returns the rows. Now I step through each row with a while loop, and if the entry is a virus, i open a new dbi connect and store it in the virus table (update if it's already there). Then I disconnect that dbi call. Continuing on, I take each entry and update the overall_stats table, based on the date the email was received.. So if I receive 100,00 emails on 10-24-2002, then instead of having 100,000 log entries I condense it down to 1 entry in the stats table. Each entry in the overall_stats would be a single day, so that would mean I would have no more than 365 records there a year... month,day,year,total_emails,total_spam,total_virus,total_bytes After updating or inserting into the stats table, I close the dbi call, and process the next email log record. This goes on fine for about 19,000-21,000 records... then all of a sudden it dies.. Output of the perl script is here Updating Record 19700 in stats table Update _W32/Klez.H@mm to 1767 Virus table Update _W32/Klez.H@mm to 1768 Virus table Update _W32/Klez.H@mm to 1769 Virus table DBI-connect(logs:mail1:3306) failed: Can't connect to MySQL server on 'mail1' (99) at ./parse_sql line 123 Can't call method prepare on an undefined value at ./parse_sql line 124. Cannot connect to database Running this script over and over will produce different results on the record is stops on. line 122 creates the dbi connect, line 123 calls the prepare function on the query, which obviously fails because dbi thinks the database has gone away?? That's what I assume, but I cant figure out how to fix it. As soon as the script dies saying cant connect, I type mysql and get right into the db. I am sth-finish() and dbh-disconnect() on all my queries, except for the first one that pulls all 600,000 rows that i step through, so I dont see that would be a problem. Box is a dual p3, 2gig ram, 72gig SCSI RAID 5. Lookin for ideas here! Thanks for any help. Dallas Engelken NMGI - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 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 and delete the material from any system and destroy any copies. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Understanding MySQL column type SET
I have v.4.0.4b max nt, and I have the same result, even more: mysql INSERT INTO settest SET chain=A,C; ERROR 1062: Duplicate entry 'A,C' for key 1 mysql INSERT INTO settest SET chain=C,A; ERROR 1062: Duplicate entry 'A,C' for key 1 mysql but mysql SELECT * FROM settest WHERE FIND_IN_SET('C,A', chain)0; Empty set (0.00 sec) I guess it can be a bug. -Original Message- From: Lopez David E-r9374c [mailto:r9374c;motorola.com] Sent: Thursday, October 24, 2002 5:09 PM To: Mysql List (E-mail) Subject: Understanding MySQL column type SET Gurus I'm having trouble understanding the column type SET. To explain my problem, I'll create a table, populate it, and do selects. mysql CREATE TABLE settest( chain SET( A, B, C ) NOT NULL, UNIQUE INDEX ichain( chain ) ); mysql INSERT INTO settest SET chain=A,C; mysql SELECT * from settest; +---+ | chain | +---+ | A,C | +---+ mysql SELECT * FROM settest WHERE chain=A,C; +---+ | chain | +---+ | A,C | +---+ mysql SELECT * FROM settest WHERE chain=C,A; Empty set (0.00 sec) or mysql SELECT * FROM settest WHERE FIND_IN_SET(C,A, chain); Empty set (0.01 sec) In reading MySQL Reference Manual, this second select statement should work. But in version()=3.23.49-nt-log, it does not. In my understanding of set theory, if a SET has A,B,C A,C == C,A Can anyone tell me what I'm missing? BTW, for my application, I'm only interested in unique entries. --- David E Lopez - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 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 and delete the material from any system and destroy any copies. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: normalization question
Hi! I don't understand problem. Car can belong to only one Dealer, and one Dealer can have many Cars, therefore DealerID should be in Car table. Also Car can be only one Model, but there are many Cars the same Model. It looks quite normal to put ModelID into Car table, and I don't see any possible problems. Of course if there are any special requirements for the database, they have to be incorporated into the model. Am I missing your point? Artem -Original Message- From: [EMAIL PROTECTED] [mailto:speters;metromls.com] Sent: Monday, October 21, 2002 5:43 PM To: [EMAIL PROTECTED] Subject: normalization question I'm working on a design for a database that is leaving me with some normalization issues that I havent had to deal with before. For example, if im trying to store information about car dealerships I end up with the following situation: I want to store the cars in stock at each physical dealership, with certain attributes. So, I have a table Dealership as follows: Dealer_ID Name Address Etc. Then there is a Car_Make table (for things like Chevy, Hyundai): Car_Make_ID Make_Name ... And a Model table (for things like Elantra, Neon, etc): Model: Model_ID Car_Make_ID Model_Name ... And a table to describe the cars themselves: (one row per car) Car: VIN_number(vehicle identification number, what the DMV uses) Model_ID other attributes ... Now, to associate a specific car with a dealer, I want to put Dealer_ID in the Cars table, but then the Cars table has multiple parent tables, specifically Model Dealership I'm sure that this is far from normal, and will lead to programming problems, but i dont see what to do about it. Any advice would be greatly appreciated. thanks, sean peters [EMAIL PROTECTED] sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 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 and delete the material from any system and destroy any copies. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Insert default Date
You can use TIMESTAMP field for this. It's automatically assigned current date if you don't insert anything in this field type or insert null. -Original Message- From: Arthur [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 7:56 AM To: MYSQL Subject: Insert default Date Hello MYSQL, In access Now() as table field default sets the field when a new record is created. In SQL Server there is getdate() suser_sname() Is there an equivalent for MySQL? -- Best regards, Arthur mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 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 and delete the material from any system and destroy any copies. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help with Sum(), newbie
select sum(first) from example; should work. Artem -Original Message- From: Kevin [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 10:36 PM To: [EMAIL PROTECTED] Subject: Help with Sum(), newbie OK. Maybe I'm expecting too much of myself, but I can't figure out what I am doing wrong here: mysql select * from example; +---++ | first | second | +---++ | 1 | 2 | | 2 | 3 | +---++ 2 rows in set (0.00 sec) mysql select first, second, SUM(first) from example group by first; +---+++ | first | second | SUM(first) | +---+++ | 1 | 2 | 1 | | 2 | 3 | 2 | +---+++ 2 rows in set (0.00 sec) If it is not obvious, I want the sum of first, which, by my calculations, should be 3. What am I doing wrong? Thanks! --kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 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 and delete the material from any system and destroy any copies. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: NULL sometimes joins to NULL
It looks like a bug. I was able to repeat it and I had different results for the same join depending on when index was added and values of actual data in tables. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 6:33 PM To: [EMAIL PROTECTED] Subject: NULL sometimes joins to NULL Description: After adding a key to a nullable column, null values will successfully join through to null values in other tables. This only seems to happen when the index is added after the row contains null values. This affects both MyISAM and InnoDB table types and both binary versions 3.23.42 and 4.0.4. How-To-Repeat: Run the following queries. The first select will return 0 rows, which is correct. The second select will return a match - but all that was changed was the adding of an index. mysql create table foo (id int); Query OK, 0 rows affected (0.01 sec) mysql insert into foo values (null), (0); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql create table bar (id int); Query OK, 0 rows affected (0.00 sec) mysql insert into bar values (null); Query OK, 1 row affected (0.00 sec) mysql select * from foo, bar where foo.id = bar.id; Empty set (0.00 sec) mysql alter table foo add key id (id); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select * from foo, bar where foo.id = bar.id; +--+--+ | id | id | +--+--+ | NULL | NULL | +--+--+ 1 row in set (0.00 sec) mysql Fix: Submitter-Id: submitter ID Originator: Organization: MySQL support: none Synopsis: adding key makes null columns join Severity: Priority: Category: mysql Class: Release: mysql-4.0.4-beta (Source distribution) Environment: System: Linux db2 2.4.18-64GB-SMP #1 SMP Wed Mar 27 13:58:12 UTC 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs gcc version 2.95.3 20010315 (SuSE) Compilation info: CC='gcc' CFLAGS='-O6 -mpentiumpro -fomit-frame-pointer' CXX='gcc' CXXFLAGS='-O6 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1394238 Jul 12 08:29 /lib/libc.so.6 -rw-r--r--1 root root 25361424 Jul 12 06:58 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 23 2002 /usr/lib/libc.so Configure command: ./configure --prefix=/opt/mysql --enable-assembler --with-mysqld-ldflags=-all-static --disable-shared -with-extra-charsets=none 'CFLAGS=-O6 -mpentiumpro -fomit-frame-pointer' 'CXXFLAGS=-O6 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' CXX=gcc - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. 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 and delete the material from any system and destroy any copies. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php