Re: mySQL in Hebrew/my.cnf
Noamn [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I asked about a week ago how to get mySQL to index correctly in Hebrew, and the best answer that I received was to define a my.cnf file as follows [mysqld] set-variable = default-character-set = hebrew I created the file /etc/my.cnf using the root account, stopped the mysql daemon then restarted. The daemon failed immediately. I tried this a few more times, then reluctantly came to the conclusion that there is something wrong with the /etc/my.cnf file, so I deleted it and successfully started the daemon. Is there something special which I need to define regarding the file's permissions? check in the /usr/local/share/mysql directory that you got the Hebrew charset definition file - hebrew.conf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COBOL Syntax of calling MySQL's C API
When I change the value of fMySQLSocket to NULL i.e., 05 C-cMySQLSocketpic x(8). 88 fMySQLSocket value NULL. The compiler shows the following error; 219 E NULL initial value invalid for 'NULL' So how can I assign NULL to this condition name variable? suggest me pls... ragards Arun. --- Patrick Sherrill [EMAIL PROTECTED] wrote: BTW the error also indicates your client is trying to connect to the localhost i.e. the same machine you are connecting from. Try using the IP address of the server instead of the servername. This should also be a string (eg. 123.123.123.123). You most likely have a parameter/data type problem. The parameters being passed from your COBOL program, are not getting to the 'C' routine as the correct data types or parameters (remember NULL is not zero). Pat... - Original Message - From: Arunachalam [EMAIL PROTECTED] To: Patrick Sherrill [EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 4:22 AM Subject: Re: COBOL Syntax of calling MySQL's C API hi, From COBOL I have tried to Connect to MySQL using the C API functions given by MySQL. Finally I struck up with the error during runtime as Can't connect to MySQL server on localhost (10061) I have a doubt Is, we have to start explicitly MySQL in the server machine? i.e., mysqld. If so with out start the mysqld in the server I can able to connect to the server using MySQLCC, is an utility tool provided by MySQL and MySQLExplorer is a free utility tool provided by ToolMagic softwares. More than this I can able to connect to MySQL server and fetch the data using simple C++ program using C API of MySQL. I believe, no one do start the MySQL in my server. I am running my application in Windows2000 machine (client), myserver is at Linux machine (server). So what I can to do?. Arun. --- Patrick Sherrill [EMAIL PROTECTED] wrote: Being COBOL illiterate, I may not be able to help. Two questions come to mind in reviewing the parameters you are passing. Are the parameters by reference passed as char-like pointers and the parameters by content passed as integers? If they are, then the only issue I see as a possibility is that 'fMySQLSocket' should be passed as NULL not 0. Also make sure mysqld is running on 'myserver'. I hope this helps... Pat... BTW replies to the list usually yield better results. - Original Message - From: Arunachalam [EMAIL PROTECTED] To: Patrick Sherrill [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 8:44 AM Subject: COBOL Syntax of calling MySQL's C API Hello Patrick, The actual calling routines in C is; MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag) I have declared the variables with values are; 05 C-cMySQLHostName pic x(9). 88 fMySQLHostName value 'myserver'. 05 C-cMySQLUserIdpic x(4). 88 fMySQLUserId value 'arun'. 05 C-cMySQLPassword pic x(4). 88 fMySQLPassword value 'arun'. 05 C-cMySQLDBNamepic x(6). 88 fMySQLDBName value 'MyDBMS'. 05 C-cMySQLPort pic x(4). 88 fMySQLPort value '3306'. 05 C-cMySQLSocketpic x(8). 88 fMySQLSocket value '0'. 05 C-cMySQLFlag pic x(8). 88 fMySQLFlag value '0'. It's equivalent COBOL coding for the C coding syntax is; CALL C_mysql_real_connect using by reference E-ptrSQLConnect by reference C-cMySQLHostName by reference C-cMySQLUserId by reference C-cMySQLPassword by reference C-cMySQLDBName by content C-cMySQLPort by reference C-cMySQLSocket by content C-cMySQLFlag giving E-ptrSQLEnv I have tested that before reaching this Calling portion all the variables hold the values what I have set earlier. but after execution it stores the error as it's value (i.e., Can't connect to MySQL server on localhost (10061) ) and the pointer variable E_ptrSQLConnect hold value 0. Before invoking this Call I have invoked the C_mysql_init funtion and get the pointer value in E_ptrSQLConnect. I could't get where is the possibility of error occurence exist... :( Arun. --- Patrick Sherrill [EMAIL PROTECTED] wrote: What is the syntax of your call to connect to the MySQL server? It seems as though no server parameters are being passed, thereby defaulting to the local machine which apparently has no mysql server running. Pat...
Rollback
Hi, I posted this question in MySQL mailing list and got no reply. The basic problem is that I have committed the transaction and then replicated to another DB. Now I want to rollback the committed transaction. Is there a way to rollback to a particular point. This requirement is very similar to rolling back using save points. I guess an option would be to backup database before changes and restore it if the user is not satisfied with the changes he has made. One transaction in my application would affect 6-8 tables with at least 50 - 100 records getting inserted/updated or deleted. Please advice PS : Wish you all a very Happy New Year Karthikeyan B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Time series
FIRST() and LAST() are not available (yet, I'll keep hoping) but you can mirror their functionality using TEMP tables, and you could probably do it with SubQueries (4.1+). There are options out there to get around them. The experience I've had with with temp tables has been quite good, though I've only used it on a 100,000 row table. It can get a little hairy writing the queries though. Chris -Original Message- From: Schulman, Michael [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 7:14 PM To: 'Fredrick Bartlett'; [EMAIL PROTECTED] Subject: RE: Time series As far as I know min(price) and max(price) will return the lowest and higest price, not the first and last in the group. Again I know first and last break the paradaigm of SQL's bucket mentality but it is crucial to doing timeseries analysis. And timeseries aggregation as the query I gave is trying to do. Thanks again, Mike -Original Message- From: Fredrick Bartlett [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 10:12 PM To: Schulman, Michael; [EMAIL PROTECTED] Subject: Re: Time series Is Hour a DateTime? If so, will this work... SELECT ticker, DATE_FORMAT(Hour,'%H' ), min(price), max(price) from pricedata order by DATE_FORMAT(Hour,'%H' ) group by DATE_FORMAT(Hour,'%H' ) - Original Message - From: Schulman, Michael [EMAIL PROTECTED] To: 'Fredrick Bartlett' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 01, 2004 6:59 PM Subject: RE: Time series That only returns one number.. what we are really looking for is something like SELECT ticker, hour, first(price), last(price) from pricedata group by hour Sorry for the confusion. Thanks, Mike -Original Message- From: Fredrick Bartlett [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 9:57 PM To: Schulman, Michael; [EMAIL PROTECTED] Subject: Re: Time series Hmmm... First: select * from table1 order by field1 asc limit 1 Last: select * from table1 order by field1 desc limit 1 - Original Message - From: Schulman, Michael [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 01, 2004 6:47 PM Subject: Time series Hi, I work for a large financial instituition. We are currently evaluating databases to store intraday stock data. These are large tables with 40 million rows per day. We've done some initial testing with MySQL and have been extremely impressed with its speed and ease of use. I know that it goes agains the SQL standard but adding a FIRST,LAST aggregate function along with some other time series functions would allow mysql to compete with just about any timeseries database, and open up mysql to a huge market of financial firms. I know my firm would most likely purchase it. Has anyone developed anyhting like this as an add on? Thanks, Mike -- This message is intended only for the personal and confidential use of the designated recipient(s) named above. If you are not the intended recipient of this message you are hereby notified that any review, dissemination, distribution or copying of this message is strictly prohibited. This communication is for information purposes only and should not be regarded as an offer to sell or as a solicitation of an offer to buy any financial product, an official confirmation of any transaction, or as an official statement of Lehman Brothers. Email transmission cannot be guaranteed to be secure or error-free. Therefore, we do not represent that this information is complete or accurate and it should not be relied upon as such. All information is subject to change without notice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message is intended only for the personal and confidential use of the designated recipient(s) named above. If you are not the intended recipient of this message you are hereby notified that any review, dissemination, distribution or copying of this message is strictly prohibited. This communication is for information purposes only and should not be regarded as an offer to sell or as a solicitation of an offer to buy any financial product, an official confirmation of any transaction, or as an official statement of Lehman Brothers. Email transmission cannot be guaranteed to be secure or error-free. Therefore, we do not represent that this information is complete or accurate and it should not be relied upon as such. All information is subject to change without notice. -- This message is intended only for the personal and confidential use of the designated recipient(s) named above. If you
RE: Rollback
I'm fairly sure there is *no* way to do it. COMMIT says 'I want this data in the database' not 'I think I want this data in the database' Chris -Original Message- From: karthikeyan.balasubramanian [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 11:37 PM To: [EMAIL PROTECTED] Subject: Rollback Hi, I posted this question in MySQL mailing list and got no reply. The basic problem is that I have committed the transaction and then replicated to another DB. Now I want to rollback the committed transaction. Is there a way to rollback to a particular point. This requirement is very similar to rolling back using save points. I guess an option would be to backup database before changes and restore it if the user is not satisfied with the changes he has made. One transaction in my application would affect 6-8 tables with at least 50 - 100 records getting inserted/updated or deleted. Please advice PS : Wish you all a very Happy New Year Karthikeyan B -- 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: Newbie: need form to input records View report
If you want to build something like a web form you will need to use HTML and a scripting language. A common solution to do what you are asking is to use PHP. You will need to be familiar with PHP to go further. Go to http://www.php.net to get a hold of the basics. There is no way to write a walkthrough for your problem here as it would be quite long :o). It would also be repititive since there are millions of sites that provide such walkthroughs. Go to www.sitepoint.com, which is a web dev portal. My favortite. They have tons of usefull articles and step by step walkthroughs. From a big picture point of view what you need to do is use PHP to generate HTML dynamically and handle application logic, database interactivity. Use the MySQL database as your data store [obviously ;)]. PHP is an excellent choice as it works very well with MySQL. If this sounds like greek then don't worry too much. Head to sitepoint.com and go to the php section and start learning. It is an excellent resource. PHP.net is a good place too although you might want to go to sitepoint first. Best of luck, Arjun Quoting Troy T. Hall [EMAIL PROTECTED]: I know this sounds stupid but I'm totally lost. I've created a MySQL DB whose purpose is to track customers who have not gotten a newspaper. I've created all the necessary fields, and have managed to learn how to add/delete/modify the records in mysqlcc, but what I want is to have a predesigned form like you'd find on a webpage where you simply enter the complaints and hit submit or whatever, and it responds with the complaint ID # ( ComplaintID is an autoincrement field in the db ) Then I need to be able to print a report in a nicely labeled/readable format showing all entries made that day where the chargeable field is not List (Chargeable is an enum consisting of yes, no, list). Will someone please be kind enough to point me in the right direction. I can't seem to get my head around this concept. Troy oh, if it matters, I'm using Linux Mandrake 9.2 with the Gnome 2.0 GDM. I also have qtDesigner, OO, Screem. -- 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: Rollback
Hi Chris, Thank you for you quick reply. Is there any alternative way to get back to the old state of the database? Looking forward for your response. Karthikeyan B - Original Message - From: Chris [EMAIL PROTECTED] To: karthikeyan.balasubramanian [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 02, 2004 1:33 PM Subject: RE: Rollback I'm fairly sure there is *no* way to do it. COMMIT says 'I want this data in the database' not 'I think I want this data in the database' Chris -Original Message- From: karthikeyan.balasubramanian [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 11:37 PM To: [EMAIL PROTECTED] Subject: Rollback Hi, I posted this question in MySQL mailing list and got no reply. The basic problem is that I have committed the transaction and then replicated to another DB. Now I want to rollback the committed transaction. Is there a way to rollback to a particular point. This requirement is very similar to rolling back using save points. I guess an option would be to backup database before changes and restore it if the user is not satisfied with the changes he has made. One transaction in my application would affect 6-8 tables with at least 50 - 100 records getting inserted/updated or deleted. Please advice PS : Wish you all a very Happy New Year Karthikeyan B -- 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: Newbie: need form to input records View report
Troy T. Hall wrote: I know this sounds stupid but I'm totally lost. I've created a MySQL DB whose purpose is to track customers who have not gotten a newspaper. I've created all the necessary fields, and have managed to learn how to add/delete/modify the records in mysqlcc, but what I want is to have a predesigned form like you'd find on a webpage where you simply enter the complaints and hit submit or whatever, and it responds with the complaint ID # ( ComplaintID is an autoincrement field in the db ) Then I need to be able to print a report in a nicely labeled/readable format showing all entries made that day where the chargeable field is not List (Chargeable is an enum consisting of yes, no, list). Will someone please be kind enough to point me in the right direction. I can't seem to get my head around this concept. Troy oh, if it matters, I'm using Linux Mandrake 9.2 with the Gnome 2.0 GDM. I also have qtDesigner, OO, Screem. Have you looked at RT ( http://http://bestpractical.com/rt/ ) ? We are using it to receive customer complains. When the customer sends an email they get a Complain ID and there are lot of other things as optional The best part is it is free and and have excellent mailing list with supports. It requires Perl/MySQL/Apache+mod_perl1 Very easy to manage and excellent tool -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 pgp0.pgp Description: PGP signature
Re: Primary Key
Hi, I saw an example of creating tables (see below). I wonder what the primary key (user_name, role_name) in the table user_roles means? Does it mean that both user_name and role_name are the primary key of the user_roles table? How does a table have two primary keys? create table users ( user_name varchar(15) not null primary key, user_pass varchar(15) not null ); create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, primary key (user_name, role_name) ); A table cannot have two primary keys, only zero or one. primary key (user_name, role_name) This primary key is a compound primary key - a constraint for multiple columns. This means that every combination of values needs to be unique. These are valid (user, role): martijn, admin caroline, admin caroline, poweruser As you can see, you, as a user, can have multiple roles. However, you cannot enter such a row twice: (invalid): caroline, poweruser caroline, poweruser With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL 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]
Multiple Roles
In case that a user has multiple roles; for example, John Dole is both author and editor, 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Roles
Caroline Jen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] In case that a user has multiple roles; for example, John Dole is both author and editor, 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); for the second option, you can use VARCHAR for roles_names, only make sure that you have enough space to define there all combinations of roles. you can also use BLOB for it (VARCHAR is up to 255 chars length). CREATE TABLE user_roles ( user_nameVARCHAR(15) NOT NULL, roles_names VARCHAR(31) NOT NULL ); roles_names is of length 31 cuz the comma is also a char. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Roles
Hi, In case that a user has multiple roles; for example, John Dole is both author and editor, 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); for the second option, you can use VARCHAR for roles_names, only make sure that you have enough space to define there all combinations of roles. you can also use BLOB for it (VARCHAR is up to 255 chars length). CREATE TABLE user_roles ( user_nameVARCHAR(15) NOT NULL, roles_names VARCHAR(31) NOT NULL ); roles_names is of length 31 cuz the comma is also a char. I would advise against this one. First of all: it breaks normal table design. Second: if you add more roles, you need to adjust your metadata (because of (1)). Third: you will run into problems when doing queries. Having a compound primary key which has multiple rows in the table for each role is the normal design. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL 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: dropping multiple tables with one command question
tom poe [EMAIL PROTECTED] wrote: What's the command for dropping multiple tables? Example: 12 tables that all start with: phpbb_ What? Well, the install isn't going as planned. I am having to reinstall as I try to work out script path (I think) or some other piece of info that isn't entered correctly, yet. Anyway, DROP TABLE tablename; is one table. I want to drop all the tables without typing them, but using DROP TABLE phpbb*; doesn't work. You can't use wildcards in the DROP TABLE command. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: dropping multiple tables with one command question
fredag 02. januar 2004, 12:11, skrev Egor Egorov: tom poe [EMAIL PROTECTED] wrote: What's the command for dropping multiple tables? Example: 12 tables that all start with: phpbb_ What? Well, the install isn't going as planned. I am having to reinstall as I try to work out script path (I think) or some other piece of info that isn't entered correctly, yet. Anyway, DROP TABLE tablename; is one table. I want to drop all the tables without typing them, but using DROP TABLE phpbb*; doesn't work. You can't use wildcards in the DROP TABLE command. But you can do DROP table1; DROP table2; DROP table3; etc.. Arne -- Arne K. Haaje | www.drlinux.no Bregneveien 9 | 1825 Tomter | M: 92 88 44 66 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dropping multiple tables with one command question
Hi Tom, Look at the docs: http://www.mysql.com/doc/en/DROP_TABLE.html Happy New Year and best regards, Mikhail. - Original Message - From: tom poe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 01, 2004 8:33 AM Subject: dropping multiple tables with one command question Happy New Year!!! What's the command for dropping multiple tables? Example: 12 tables that all start with: phpbb_ What? Well, the install isn't going as planned. I am having to reinstall as I try to work out script path (I think) or some other piece of info that isn't entered correctly, yet. Anyway, DROP TABLE tablename; is one table. I want to drop all the tables without typing them, but using DROP TABLE phpbb*; doesn't work. Any help appreciated. Happy New Year, Tom -- 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: dropping multiple tables with one command question
But you can do DROP table1; DROP table2; DROP table3; etc.. You can do even better: drop table table1, table2, table3; :) Best regards, Mikhail. - Original Message - From: Arne K. Haaje [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 02, 2004 12:29 PM Subject: Re: dropping multiple tables with one command question fredag 02. januar 2004, 12:11, skrev Egor Egorov: tom poe [EMAIL PROTECTED] wrote: What's the command for dropping multiple tables? Example: 12 tables that all start with: phpbb_ What? Well, the install isn't going as planned. I am having to reinstall as I try to work out script path (I think) or some other piece of info that isn't entered correctly, yet. Anyway, DROP TABLE tablename; is one table. I want to drop all the tables without typing them, but using DROP TABLE phpbb*; doesn't work. You can't use wildcards in the DROP TABLE command. But you can do DROP table1; DROP table2; DROP table3; etc.. Arne -- Arne K. Haaje | www.drlinux.no Bregneveien 9 | 1825 Tomter | M: 92 88 44 66 -- 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: Time series
http://www.mysql.com/doc/en/example-Maximum-column-group-row.html might help you do what you're looking for. On Thu, 1 Jan 2004, Schulman, Michael wrote: As far as I know min(price) and max(price) will return the lowest and higest price, not the first and last in the group. Again I know first and last break the paradaigm of SQL's bucket mentality but it is crucial to doing timeseries analysis. And timeseries aggregation as the query I gave is trying to do. Thanks again, Mike -Original Message- From: Fredrick Bartlett [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 10:12 PM To: Schulman, Michael; [EMAIL PROTECTED] Subject: Re: Time series Is Hour a DateTime? If so, will this work... SELECT ticker, DATE_FORMAT(Hour,'%H' ), min(price), max(price) from pricedata order by DATE_FORMAT(Hour,'%H' ) group by DATE_FORMAT(Hour,'%H' ) - Original Message - From: Schulman, Michael [EMAIL PROTECTED] To: 'Fredrick Bartlett' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 01, 2004 6:59 PM Subject: RE: Time series That only returns one number.. what we are really looking for is something like SELECT ticker, hour, first(price), last(price) from pricedata group by hour Sorry for the confusion. Thanks, Mike -Original Message- From: Fredrick Bartlett [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 9:57 PM To: Schulman, Michael; [EMAIL PROTECTED] Subject: Re: Time series Hmmm... First: select * from table1 order by field1 asc limit 1 Last: select * from table1 order by field1 desc limit 1 - Original Message - From: Schulman, Michael [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 01, 2004 6:47 PM Subject: Time series Hi, I work for a large financial instituition. We are currently evaluating databases to store intraday stock data. These are large tables with 40 million rows per day. We've done some initial testing with MySQL and have been extremely impressed with its speed and ease of use. I know that it goes agains the SQL standard but adding a FIRST,LAST aggregate function along with some other time series functions would allow mysql to compete with just about any timeseries database, and open up mysql to a huge market of financial firms. I know my firm would most likely purchase it. Has anyone developed anyhting like this as an add on? Thanks, Mike -- This message is intended only for the personal and confidential use of the designated recipient(s) named above. If you are not the intended recipient of this message you are hereby notified that any review, dissemination, distribution or copying of this message is strictly prohibited. This communication is for information purposes only and should not be regarded as an offer to sell or as a solicitation of an offer to buy any financial product, an official confirmation of any transaction, or as an official statement of Lehman Brothers. Email transmission cannot be guaranteed to be secure or error-free. Therefore, we do not represent that this information is complete or accurate and it should not be relied upon as such. All information is subject to change without notice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message is intended only for the personal and confidential use of the designated recipient(s) named above. If you are not the intended recipient of this message you are hereby notified that any review, dissemination, distribution or copying of this message is strictly prohibited. This communication is for information purposes only and should not be regarded as an offer to sell or as a solicitation of an offer to buy any financial product, an official confirmation of any transaction, or as an official statement of Lehman Brothers. Email transmission cannot be guaranteed to be secure or error-free. Therefore, we do not represent that this information is complete or accurate and it should not be relied upon as such. All information is subject to change without notice. -- This message is intended only for the personal and confidential use of the designated recipient(s) named above. If you are not the intended recipient of this message you are hereby notified that any review, dissemination, distribution or copying of this message is strictly prohibited. This communication is for information purposes only and should not be regarded as an offer to sell or as a solicitation of an offer
Re: Rollback
Am Fr, den 02.01.2004 schrieb karthikeyan.balasubramanian um 09:28: Is there any alternative way to get back to the old state of the database? The only way I can think of is to dump the respective tables (e.g. every night) and re-import them when needed. Note that this can't be done by mysql automatically because - as Chris just said - COMMIT means to commit /now/ and irreversibly. First step: tell this to your users ;) Greetings Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With Creating Table
On Wed, 31 Dec 2003 19:19:32 -0800 (PST), Caroline Jen [EMAIL PROTECTED] wrote: Hi, I tried to create a table. This table has seventeen fields. My create table syntax gets too long and I was only able to specify 5 fields at the mysql prompt in the DOS window (DOS does not accept a command beyond certain length). How do I put the rest 12 fields in the table I just created? mysqlCREATE TABLE message_thread (thread_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, message_receiver VARCHAR(79) NOT NULL, message_sender VARCHAR(79) NOT NULL, article_title VARCHAR(255) NOT NULL, last_post_member_name VARCHAR(79) NOT NULL); Caroline, Maybe you should be putting long sql statements into an external script eg $ mysql -D mydb -u test -ptest create_table.sql zzapper (vim cygwin zsh) -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Roles
On Fri, 2 Jan 2004, Caroline Jen wrote: In case that a user has multiple roles; for example, John Dole is both author and editor, 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); If the roles will not be very dynamic and could be hardcoded you might be able to use the SET datatype which is described here: http://www.mysql.com/doc/en/SET.html If you will add/update/change/delete roles often, then you should go for one of the other methods suggested instead, but if the roles are static, this might work better for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.x Vs 4.x
What version is more quick? MySQL 5.x or 4.x Innodb or MyIsam tables? Regards for all Carlos Souza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unauthenticated user mystery
Hi all, I've been unable to find a single reference to the thread status of unauthenticated user on any mysql documentation - from time to time I get this status when connecting from one particular host. The real problem is that whilst in this state the requesting host appears to those accessing the database from it to be hung for a few minutes before being correctly connected. The only way I've found to get around it is to 'mysqladmin reload' the database (which provides a temporary solution). The database is accessed by multiple hosts, yet it is only this one that has difficulties. The version of mysql I'm using is described as Ver 10.4 Distrib 3.23.12c-alpha, for sun-solaris2.7 (sparc) Unfortunately upgrading to a newer version is not a quick option as the database is used to record statistics from multiple devices at five minute intervals as well as being used by customers to view their statistics and is over 50GB in size.
MySQL and Crystal Reports?
Has anyone used Crystal Reports successfully with MySQL? I tried the ODBC driver but it was rejected with a message that the Professional version was needed. Eldon Ziegler Atlantic Software, Inc. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback
You can add a version field to each row. Then add a seperate table with info with a list of the versions and a flag for deleted. Queries would look for each record that has the highest version number thats not deleted. Having a lot undo/redo info can get kind of complicated, especialy with multiple end users playing with it and chains of dependant changes. If the info can be modeled as documents this is frequenetly done with CVS. On Fri, 2 Jan 2004 13:06:36 +0530, karthikeyan.balasubramanian [EMAIL PROTECTED] said: Hi, I posted this question in MySQL mailing list and got no reply. The basic problem is that I have committed the transaction and then replicated to another DB. Now I want to rollback the committed transaction. Is there a way to rollback to a particular point. This requirement is very similar to rolling back using save points. I guess an option would be to backup database before changes and restore it if the user is not satisfied with the changes he has made. One transaction in my application would affect 6-8 tables with at least 50 - 100 records getting inserted/updated or deleted. Please advice PS : Wish you all a very Happy New Year Karthikeyan B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Crystal Reports?
On 2 Jan 2004, at 12:26, Eldon Ziegler wrote: Has anyone used Crystal Reports successfully with MySQL? I tried the ODBC driver but it was rejected with a message that the Professional version was needed. The issue might be with Crystal Reports itself and nothing to do with MySQL. Are you using the Standard edition? http://www.crystaldecisions.com/products/crystalreports/editions.asp ... indicates you can only use ODBC with Crystal Reports starting with the Professional edition. HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Wait Timeout
Randy, that is expected: http://www.innodb.com/ibman.php#Locks_set_by_statements If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete which requires checking of the constraint condition sets shared record level locks on the records it looks at to check the constraint. Also in the case where the constraint fails, InnoDB sets these locks. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html .. From: Randy Chrismon ([EMAIL PROTECTED]) Subject: Re: Lock Wait Timeout View: Complete Thread (2 articles) Original Format Newsgroups: mailing.database.myodbc Date: 2003-12-30 11:12:12 PST Well now, this is interesting. The agent mentione previously ran flawlessly for a couple of weeks. The day I left for Christmas vacation, I started getting the lock wait timeout error. The difference? I altered the table to include a foreign key constraint. Not sure how this could possibly be the issue but when I dropped the foreign key constraint, the agent worked perfectly. I'm still at a loss as to why this problem happened. I'm using INNODB tables in MySQL 4.0.16. It looks like the one record which timed out was being updated and the referenced record and field values existed. In addition, I had already updated 26,000 records so I can't figure out what's wrong with this one particular record. Any thoughts appreciated. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: revoke and set password syntax
Chris W [EMAIL PROTECTED] wrote: I am having trouble revoking the rights of the anonymous user or setting a password for that user. Can some one help. I can just do an update or delete but I want to do it with revoke and set password. What exactly problems do you have? Show your REVOKE and SET PASSWORD statements. -- 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: innodb defragmentation question
Franky, you apparently have just the PRIMARY KEY defined on the table and no secondary indexes. I guess the fragmentation comes because you delete rows from in the middle with respect to the primary key ordering? Or do you also insert in random order? Anyway, you cannot do anything to prevent fragmentation. Below the average length of a row in 50 bytes. It cannot yet be terribly fragmented, because the minimum length for an InnoDB row is about 20 bytes. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html From: Franky Van Liedekerke ([EMAIL PROTECTED]) Subject: Re: innodb defragmentation question View: Complete Thread (4 articles) Original Format Newsgroups: mailing.database.myodbc Date: 2003-12-31 00:33:47 PST the problem is that, if it happens again, I get a file of 900 MB, which gets kinda big ... In order to rectify the situation after that, I'll need to dump all innodb tables, drop them and reinsert them. This would takes hours, and in the meantime the application running on top of it would be down ... Anyway, here's the output of show table status and show innodb status for the corresponding tables. Maybe you can tell me how to check if defrag is even needed? | history | InnoDB | Fixed | 6132057 | 50 | 310378496 |NULL |0 | 0 | NULL | NULL| NULL| NULL || InnoDB free: 8192 kB | users_groups| InnoDB | Fixed | 0 | 0 | 16384 |NULL |0 | 0 |NULL | NULL| NULL| NULL || InnoDB free: 8192 kB | | usrgrp | InnoDB | Dynamic| 7 | 2340 | 16384 |NULL |16384 | 0 | 8 | NULL| NULL| NULL || InnoDB free: 8192 kB And the show innodb status: = 031231 9:31:03 INNODB MONITOR OUTPUT = Per second averages calculated from the last 23 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 269898, signal count 269639 Mutex spin waits 164682, rounds 1645099, OS waits 81402 RW-shared spins 345770, OS waits 172804; RW-excl spins 15688, OS waits 15679 TRANSACTIONS Trx id counter 0 7556158 Purge done for trx's n:o 0 2341232 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 150765 MySQL thread id 150754, query id 42886888 localhost root SHOW INNODB STATUS ---TRANSACTION 0 7556157, not started, OS thread id 55 MySQL thread id 44, query id 42886887 localhost root FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 107685 OS file reads, 11603482 OS file writes, 6685465 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 8.09 writes/s, 4.26 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 144, node heap has 1 buffer(s) 0.96 hash searches/s, 3.35 non-hash searches/s --- LOG --- Log sequence number 0 395581663 Log flushed up to 0 395581663 Last checkpoint at 0 395580831 0 pending log writes, 0 pending chkp writes 6292464 log i/o's done, 4.00 log i/o's/second -- -- BUFFER POOL AND MEMORY -- Total memory allocated 17823008; in additional pool allocated 719232 Buffer pool size 512 Free buffers 0 Database pages 511 Modified db pages 14 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 110330, created 17200, written 6223975 0.00 reads/s, 0.00 creates/s, 4.30 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 10, state: sleeping Number of rows inserted 6116574, updated 0, deleted 9399, read 15517877 3.91 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.74 reads/s END OF INNODB MONITOR OUTPUT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Shutdown error
Every shutdown MySQL I found error as this shutdown failed; error: 'Access denied for user: '@localhost' (Using password: NO)' I tried to find solution of this error, but I didn't find it. Because of that I need solution of this error. Please give me the solution. Regard's Muliadi
Temporary tables rights
I'll be fighting with rights over tmp tables time ago, basically I want give full temporary tables management but notforotherall tables, including select right. The only way to do that work was insert in tables_priv for each user a "user/tmp_table_name" record granting full privileges. There is a problem with this because 'TMP%' as table_name doesn't work in tables_priv, you must insert many rows as many different temporary tables names you think use. The question is: There is a way to improve tmp rights management? I think this is a weak side of mysql. Thnks! Alejandro _ IncrediMail - El Email ha evolucionado finalmente - Haga clic aquí
Broadcast to search available MySQL Server in Network?
Question: Is there a way to find available MySQL Server via broadcast in the network using Win-sockets? I have some data and want to send it to the SQL server. The user should see all available SQL server in a list, can chosen one (don't care MSSQL or MySQL) and the program will store the data automatically in the right way to the database. Here is the abbreviated sample to find all MSSQL servers available in the network (written in Borland Delphi (Win32)). How looks the code (IP-address and port) for MySQL? procedure ShowMSSQLServer; const Address='255.255.255.255'; begin //create a socket DBSocket:=socket(AF_INET,SOCK_DGRAM,IPPROTO_UDP); //declare call back routine WSAAsyncSelect(DBSocket,self.Handle,CM_MSSQLCallBack,FD_READ); //setup SNDBUF:=0; TCPNODELAY:=1; BROADCAST:=1; setsockopt(DBSocket,SOL_SOCKET,SO_SNDBUF,PChar(@SNDBUF),sizeof(SNDBUF)); setsockopt(DBSocket,SOL_SOCKET,TCP_NODELAY,PChar(@TCPNODELAY),sizeof(TCPNODE LAY)); setsockopt(DBSocket,SOL_SOCKET,SO_BROADCAST,PChar(@BROADCAST),sizeof(BROADCA ST)); hostaddr.sin_family :=AF_INET; hostaddr.sin_port :=htons(1434); hostaddr.sin_addr.s_addr:=inet_addr(PChar(Address)); buf:=#02; //ping //ask all maschines sendto(DBSocket,buf,1,0,hostaddr,sizeof(hostaddr)); end;
RE: Shutdown error
How are you shutting down MySQL? -Original Message- From: ikm [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 11:14 PM To: [EMAIL PROTECTED] Subject: Shutdown error Every shutdown MySQL I found error as this shutdown failed; error: 'Access denied for user: '@localhost' (Using password: NO)' I tried to find solution of this error, but I didn't find it. Because of that I need solution of this error. Please give me the solution. Regard's Muliadi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Roles
On Jan 2, 2004, at 4:28 AM, Caroline Jen wrote: 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); Both are poor solutions. You should have a person table and a role table, and join them using a third (typically called an allocation or assignment table, or simply a many-to-many table). This third table contains only the PKs of the person and their role. Typically it has only three columns (its own PK, person_FK and role_FK), but can optionally have additional columns if additional information about the relationship is needed. It is then a matter of joining the person table to the role table through the allocation table to get a list of all roles for a given person. Reversing the queries then gives you all people who have a given role. ___/ / __/ / / Ed Leafe Linux Love: unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Roles
* Caroline Jen In case that a user has multiple roles; for example, John Dole is both author and editor, 1. I should have two rows for John Dole? John Dole author John Dole editor or. I should have only one row and use comma ',' to separate the roles? John Dole author, editor I see you allready got some relevant replies, I just wanted to add some comments and advise on normalization, which seems to be the core of this question. You should _never_ separate data with comma in a column. This violates the first normal form, called 1NF, which states that a column should contain a single value of the same type for each row. You should have _one_ row for John Dole in the users table, and two corresponding rows in a roles table. In addition you need a table to hold the combinations. 2. How do I create the table for the second case (see below)? create table user_roles ( user_name varchar(15) not null, role_name varchar(15) not null, varchar(15) null ); To normalize this fully, you need three tables: CREATE TABLE users ( uid int unsigned not null primary key auto_increment, name varchar(30) not null, unique(name) ); CREATE TABLE roles ( rid int unsigned not null primary key auto_increment, role varchar(30) not null, unique(role) ); CREATE TABLE user_roles ( uid int unsigned not null, rid int unsigned not null, primary key (uid,rid), unique (rid,uid) ); The primary keys are made as small/compact as possible, in this case 4 bytes for users and roles, and 8 bytes for the combination. You could make this even more compact, for instance using TINYINT for the rid column if you don't have more than 250 roles and SMALLINT for uid if you have less than 65000 users. This does not matter much for small tables, but when your data is considerably larger than the computer memory, these things become important. Note that if you need to change the spelling of an existing name or role, you just have to change it one place, in the 'users' or 'roles' table. The key (uid/rid) is unchanged, thus the rows in user_roles does not need to change. Also note that the data that consumes space (the VARCHAR columns) are stored only once for each value, and a smaller column, a 4 byte INTEGER is used as the key, representing the value stored in the VARCHAR. Now, if you had 50.000 users with an average of 100 roles each, that would be 5M rows in your user_roles table. With a non-normalized approach, you would store avg(length(name)) + avg(length(role)) bytes for each row, say 15 + 10 = 25 bytes = 125MB. With the normalized approach suggested above you store only 8 bytes for each row = 40MB in total, compacting further using TINYINT and SMALLINT you would store only 15MB. Inserting test data: INSERT INTO users SET name = 'John Dole'; SET @uid:=LAST_INSERT_ID(); INSERT INTO roles SET role = 'author'; INSERT INTO user_roles SET [EMAIL PROTECTED],rid=LAST_INSERT_ID(); INSERT INTO roles SET role = 'editor'; INSERT INTO user_roles SET [EMAIL PROTECTED],rid=LAST_INSERT_ID(); Now the tables looks like this: mysql select * from users; +-+---+ | uid | name | +-+---+ | 1 | John Dole | +-+---+ 1 row in set (0.01 sec) mysql select * from roles; +-++ | rid | role | +-++ | 1 | author | | 2 | editor | +-++ 2 rows in set (0.01 sec) mysql select * from user_roles; +-+-+ | uid | rid | +-+-+ | 1 | 1 | | 1 | 2 | +-+-+ 2 rows in set (0.00 sec) To select all roles for a user: SELECT role FROM roles NATURAL JOIN user_roles NATURAL JOIN users WHERE name = 'John Dole' To select all users of a role: SELECT name FROM users NATURAL JOIN user_roles NATURAL JOIN roles WHERE role = 'editor' To insert a user/role combination: 1. Get the key for the name: SELECT uid FROM users WHERE name = '$name' 2. If the name did not exist, create it: INSERT INTO users SET name = '$name'; Get the key: SELECT LAST_INSERT_ID() 3. Get the key for the role: SELECT rid FROM roles WHERE role = '$role' 4. If the role did not exist, create it: INSERT INTO roles SET role = '$role'; Get the key: SELECT LAST_INSERT_ID() 5. Insert the user_roles row: INSERT user_roles SET uid=$uid,rid=$rid; If the final INSERT fails, the user/role combination allready existed. If any of the other INSERTs fails you have a collision: two users are creating the same user or role at the same time. In that case you should redo the previous SELECT (step 1 or 3), or take the easy way out and just restart from step 1. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Shutdown failed (WinMySQLAdmin)
I am freshly install MySQL on my computer with Windows 98 as operating system. I used MySQL ver. 3.23.58-max-debug. I am new user of MySQL and have problem when shuttingdown WinMySQLAdmin. The error as this: Error on shutdown: Access denied for user: '@localhost' (Using password: NO) For information, My Computer was setting as this: Local Host Name : USER Local User Name : ADI OS Platform: Windows 98 MySQL was setting as this: [MySQLD] basedir=C:/Program Files/mySQL/ datadir=C:/Program Files/mySQL/data/ skip-innodb [WinMySQLAdmin] Server=C:/PROGRAM FILES/MYSQL/bin/mysqld.exe And I use MySQL for single user (only my computer) Beside this problem I need information how to create other user in MySQL. Thank's for information Regard's Muliadi
mysql version problem
Hi Guys, I am running winxp with mysql. Seeing this is a home machine there is no workstation just one computer which I guess at this point is acting as my server and client for mysql. I am just getting acquainted with mysql so I have been playing around with both 4.1 and 5.0. Neither database is installed as a service on my windoze box, essentially I have two folders one is mysql4 the other mysql5. When I want to use one of the databases I rename the folder to mysql and then it makes use of my.ini and everything works just fine. My problem is accessing mysql 5.0 from perl,php etc... I get client authentication protocol errors. If I run command line everything works perfect but as soon as perl or php try to access the 5.0 database it bombs. What dll is responsible for this so i'll know where to find it and make sure it is the appropriate version(I'm assuming at this point both php/perl are seeing a 4.1 dll) Or is this a perl /php problem where they have a specific dll with them and I need to adjust their dll's. Thanks!
select distinct from two columns
Hello, The two columns of my table are name and city. I am trying to do a list which contains only the different names. This can be done this by distinct keyword. But all the same names which have different cities also have to be included in my list. So I can not do this with a distinct keyword. Any ideas? Thanks in advance. __ New! Unlimited Access from the Netscape Internet Service. Beta test the new Netscape Internet Service for only $1.00 per month until 3/1/04. Sign up today at http://isp.netscape.com/register Act now to get a personalized email address! Netscape. Just the Net You Need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Shutdown error
if you are tryin to shutdown mysql you have to be the superuser mysql/root then give the mysqladmin -u root -p command or something. If you are doing this and it is not working then the permissions for the superuser are not set rite go change the permissions in the user table to allow root or mysql or any other user for that matter to a superuser. - sanya Victor Pendleton [EMAIL PROTECTED] wrote: How are you shutting down MySQL? -Original Message- From: ikm [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 11:14 PM To: [EMAIL PROTECTED] Subject: Shutdown error Every shutdown MySQL I found error as this shutdown failed; error: 'Access denied for user: '@localhost' (Using password: NO)' I tried to find solution of this error, but I didn't find it. Because of that I need solution of this error. Please give me the solution. Regard's Muliadi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Find out what made the Top Yahoo! Searches of 2003
Re: select distinct from two columns
* Veysel Harun Sahin The two columns of my table are name and city. I am trying to do a list which contains only the different names. This can be done this by distinct keyword. But all the same names which have different cities also have to be included in my list. So I can not do this with a distinct keyword. You can use the GROUP BY clause with two columns: SELECT name,city,count(*) FROM my_table GROUP BY name,city The count(*) will give you a count of how many rows have each name/city combination. If you don't need it simply remove it, the GROUP BY should work anyway. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select distinct from two columns - solved
:) I have solved the problem. Thanks. [EMAIL PROTECTED] (Veysel Harun Sahin) wrote: Hello, The two columns of my table are name and city. I am trying to do a list which contains only the different names. This can be done this by distinct keyword. But all the same names which have different cities also have to be included in my list. So I can not do this with a distinct keyword. Any ideas? Thanks in advance. __ New! Unlimited Access from the Netscape Internet Service. Beta test the new Netscape Internet Service for only $1.00 per month until 3/1/04. Sign up today at http://isp.netscape.com/register Act now to get a personalized email address! Netscape. Just the Net You Need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ New! Unlimited Access from the Netscape Internet Service. Beta test the new Netscape Internet Service for only $1.00 per month until 3/1/04. Sign up today at http://isp.netscape.com/register Act now to get a personalized email address! Netscape. Just the Net You Need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select distinct from two columns
select distinct(col1,col2) should work.. Group by most certainly will... Select col1,col2 from table group by col1, col2.. Same thing Hello, The two columns of my table are name and city. I am trying to do a list which contains only the different names. This can be done this by distinct keyword. But all the same names which have different cities also have to be included in my list. So I can not do this with a distinct keyword. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select distinct from two columns
Hi, try to use group by clause in your select, e.g. select name, city from mytable group by name, city order by name, city; Best regards, Mikhail. - Original Message - From: Veysel Harun Sahin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 02, 2004 4:42 PM Subject: select distinct from two columns Hello, The two columns of my table are name and city. I am trying to do a list which contains only the different names. This can be done this by distinct keyword. But all the same names which have different cities also have to be included in my list. So I can not do this with a distinct keyword. Any ideas? Thanks in advance. __ New! Unlimited Access from the Netscape Internet Service. Beta test the new Netscape Internet Service for only $1.00 per month until 3/1/04. Sign up today at http://isp.netscape.com/register Act now to get a personalized email address! Netscape. Just the Net You Need. -- 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]
Happy New Year
I wish you all a very happy new year Brasil, 2004 This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Stats] MySQL List: December 2003
- Searchable archives for this list are available at http://www.listsearch.com/mysql.lasso -- == MySQL List Stats December, 2003 == Note: Up/Down % as compared with November, 2003 Posts: 1839 (Down 2%) Authors: 582 (Down 4%) Threads: 659 (Down 2%) Top 20 Contributors by Number of Posts -- Chris Nolan 39 Egor Egorov 38 Heikki Tuuri37 Martijn Tonies 35 Paul DuBois 32 Michael Stassen 31 Jay Blanchard 30 Victoria Reznichenko27 ads mysql 24 Jeremy Zawodny 22 Sergei Golubchik19 Chuck Gadd 18 Matt W 16 Greg G 16 Roger Baklund 15 Duncan Hill 14 Tobias Asplund 14 mos 14 Daniel Kasak13 Mike Johnson13 Top 20 Threads by Number of Posts -- MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ? 44 Licence question20 foreign keys. 20 Login Problems 18 Quering user privileges 18 Export in XML 16 storing .tar files in mysql 16 How to READ/WRITE directly on MyISAM data files ? 15 error 1045 happened randomly13 How to Uninstall Mysql 13 datetime ORDER BY is erred 12 new install - command prompt doesn't work 12 How to create mysql user? 11 MySQL runs slow on windows 2003 10 Replication 9 Problem creating new user. 9 unixtime update syntax 8 Replication : blocking updates to slave 8 Query to emulate what mysqldump does 8 mysql user 8 Top 20 Search Terms by Number of Requests -- MySQL 12 join11 mysqldump 10 filemaker 10 Auto10 change 8 characters 8 field7 password 7 error7 language 7 windows 7 increment7 character7 in 6 Enter5 database 5 port 5 connect
mysql.sock permission srwxrwxrwx
I'm trying to open mysql.sock file as a ROOT and receive the error message permission denied. Why mysql.sock has such permission? _ Tired of slow downloads? Compare online deals from your local high-speed providers now. https://broadband.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql query for faceted classification system
dear fellow MySQL users, i am developing a photo gallery with php4/mysql4.0 that uses faceted classification. -my tables: photos(photoid) metadata(photoid,facetid) -to select all of the photoid's that are associated with either facetid 1 or 2: SELECT DISTINCT photos.* FROM photos,metadata WHERE photos.photoid = metadata.photoid AND (metadata.facetid = 1 OR metadata.facetid = 2) but what if i want to select all photoids that are associated with BOTH facetids? is there a join? (just sticking AND in there won't work, because any row in metadata can only have one facetid.) thanks for your advice, Seamus Abshere -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Change from loop to single query
I have a loop which is similar to the following:
while(array contains elements) {
UPDATE users SET status = no WHERE name = array[i]
}
great, it works but the query runs many times. I want to make only one
call to the database and have all the elements in the array be included
in the query
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Change from loop to single query
Try forming the query with only the first array element, then iteratring
through the rest concatinating OR clauses onto the end of the query. And
then after the loop sending the query to the db. See my below pseudo code.
String query = UPDATE users SET status = no WHERE name = array[0]
While(array[1:end] contains elements) {
query = query + OR name = array[i]
}
Execute(query);
John A. McCaskey
Software Development Engineer
IP Sciences, Inc.
[EMAIL PROTECTED]
206.633.0449
-Original Message-
From: Jonathan Villa [mailto:[EMAIL PROTECTED]
Sent: Friday, January 02, 2004 9:17 AM
To: [EMAIL PROTECTED]
Subject: Change from loop to single query
I have a loop which is similar to the following:
while(array contains elements) {
UPDATE users SET status = no WHERE name = array[i]
}
great, it works but the query runs many times. I want to make only one call
to the database and have all the elements in the array be included in the
query
--
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: Speed difference between boolean full-text searches and full-text searches
Hi!
Sorry for delayed answer - I was on vacations...
On Dec 08, Uros Kotnik wrote:
OK, I will give you more details.
Table CDS, have 1,053,794 rows, FT index on title,
Data 67,646 KB, Index 70,401 KB
Table ARTISTS, Rows 292,330, FT on name,
Data 8,096 KB
Index 17,218 KB
Table TRACKS, rows 13,841,930, FT on title
Data 625,360 KB
Index 646,672 KB
ft_min_word_len = 3
key_buffer_size 786432000
Explain for both SQLs gives same info :
table type possible_keys key key_len ref rows Extra
artists fulltext PRIMARY,ft_name ft_name 0 1 Using where
cds fulltext PRIMARY,artistIndex,ft_title ft_title 0 1 Using where
tracks ref PRIMARY,artistIndex PRIMARY 4 cds.cdId 13 Using where
Time for first SQL : 21 sec.
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND MATCH (artists.name)
AGAINST ('madonna' IN BOOLEAN MODE) AND
MATCH (cds.title) AGAINST ('music mix 2001' IN BOOLEAN MODE)
Time for second SQL : 1 sec.
SELECT artists.name, cds.title, tracks.title
FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND
MATCH ( artists.name ) AGAINST ( 'madonna' ) AND
MATCH ( cds.title ) AGAINST ( 'music' ) AND
MATCH ( cds.title ) AGAINST ( 'mix' ) AND
MATCH ( cds.title ) AGAINST ( '2001' )
Assuming,
SELECT @N=COUNT(*) FROM cds WHERE MATCH title AGAINST ('music');
SELECT @M=COUNT(*) FROM cds WHERE MATCH title AGAINST ('mix');
SELECT @K=COUNT(*) FROM cds WHERE MATCH title AGAINST ('2001');
SELECT @L=COUNT(*) FROM cds WHERE MATCH title AGAINST ('+music +mix +2001' IN BOOLEAN
MODE);
The first query will do N+M+K index lookups and read L rows from the
disk.
The second query will do N index lookups and read N rows from the disk.
Thus, if (N+M+K) is much greater than N (it usually is)
and L is close to N (it is usually not), than first query should be much
slower. Typically L is less than min(N,M,K), thus the goal is to reduce
the number of row reads.
I agree this optimization is not the best for all situations,
optimizing this type of queries is in the todo.
btw, it could be that my guess about the slowness was wrong :)
Compare these N,M,K,L numbers yourself.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change from loop to single query
On Fri, 2 Jan 2004, Jonathan Villa wrote:
I have a loop which is similar to the following:
while(array contains elements) {
UPDATE users SET status = no WHERE name = array[i]
}
great, it works but the query runs many times. I want to make only one
call to the database and have all the elements in the array be included
in the query
UPDATE users SET status = no WHERE name IN('name1', 'name2', 'name3', ...,
'namen')
you might still have to loop to remake your array to a commaseparated list
of strings, but it will still be boatloads more efficient.
If you have several hundred thousands or more of names and get an error,
try upping your max_allowed_packet variable.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change from loop to single query
You probably want the IN comparison operator
(http://www.mysql.com/doc/en/Comparison_Operators.html)
For example:
UPDATE users SET status=no WHERE name IN ('Joe', 'Wally', 'Bob', 'Cynthia');
Of course, you can create this statement from the list of names by
joining all of the names with commas.
Good luck!
-Doug Sims
Jonathan Villa wrote:
I have a loop which is similar to the following:
while(array contains elements) {
UPDATE users SET status = no WHERE name = array[i]
}
great, it works but the query runs many times. I want to make only one
call to the database and have all the elements in the array be included
in the query
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Change from loop to single query
What language are you using? It's not clear from your example what language you're using (no $s, but you also forgot to increment your array so?), so I'll give you an example in PHP: $query_string = implode( OR name = , $yourarray); mysql_query(UPDATE users SET status = no WHERE name = $query_string;); _ Take advantage of our limited-time introductory offer for dial-up Internet access. http://join.msn.com/?page=dept/dialup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COBOL Syntax of calling MySQL's C API
I am COBOL illiterate. You could try passing hex zero 0x00 as NULL. I have no idea of the correct syntax for doing this in COBOL. Pat... - Original Message - From: Arunachalam [EMAIL PROTECTED] To: Patrick Sherrill [EMAIL PROTECTED] Cc: mySQL List [EMAIL PROTECTED] Sent: Friday, January 02, 2004 2:20 AM Subject: Re: COBOL Syntax of calling MySQL's C API When I change the value of fMySQLSocket to NULL i.e., 05 C-cMySQLSocketpic x(8). 88 fMySQLSocket value NULL. The compiler shows the following error; 219 E NULL initial value invalid for 'NULL' So how can I assign NULL to this condition name variable? suggest me pls... ragards Arun. --- Patrick Sherrill [EMAIL PROTECTED] wrote: BTW the error also indicates your client is trying to connect to the localhost i.e. the same machine you are connecting from. Try using the IP address of the server instead of the servername. This should also be a string (eg. 123.123.123.123). You most likely have a parameter/data type problem. The parameters being passed from your COBOL program, are not getting to the 'C' routine as the correct data types or parameters (remember NULL is not zero). Pat... - Original Message - From: Arunachalam [EMAIL PROTECTED] To: Patrick Sherrill [EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 4:22 AM Subject: Re: COBOL Syntax of calling MySQL's C API hi, From COBOL I have tried to Connect to MySQL using the C API functions given by MySQL. Finally I struck up with the error during runtime as Can't connect to MySQL server on localhost (10061) I have a doubt Is, we have to start explicitly MySQL in the server machine? i.e., mysqld. If so with out start the mysqld in the server I can able to connect to the server using MySQLCC, is an utility tool provided by MySQL and MySQLExplorer is a free utility tool provided by ToolMagic softwares. More than this I can able to connect to MySQL server and fetch the data using simple C++ program using C API of MySQL. I believe, no one do start the MySQL in my server. I am running my application in Windows2000 machine (client), myserver is at Linux machine (server). So what I can to do?. Arun. --- Patrick Sherrill [EMAIL PROTECTED] wrote: Being COBOL illiterate, I may not be able to help. Two questions come to mind in reviewing the parameters you are passing. Are the parameters by reference passed as char-like pointers and the parameters by content passed as integers? If they are, then the only issue I see as a possibility is that 'fMySQLSocket' should be passed as NULL not 0. Also make sure mysqld is running on 'myserver'. I hope this helps... Pat... BTW replies to the list usually yield better results. - Original Message - From: Arunachalam [EMAIL PROTECTED] To: Patrick Sherrill [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 8:44 AM Subject: COBOL Syntax of calling MySQL's C API Hello Patrick, The actual calling routines in C is; MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag) I have declared the variables with values are; 05 C-cMySQLHostName pic x(9). 88 fMySQLHostName value 'myserver'. 05 C-cMySQLUserIdpic x(4). 88 fMySQLUserId value 'arun'. 05 C-cMySQLPassword pic x(4). 88 fMySQLPassword value 'arun'. 05 C-cMySQLDBNamepic x(6). 88 fMySQLDBName value 'MyDBMS'. 05 C-cMySQLPort pic x(4). 88 fMySQLPort value '3306'. 05 C-cMySQLSocketpic x(8). 88 fMySQLSocket value '0'. 05 C-cMySQLFlag pic x(8). 88 fMySQLFlag value '0'. It's equivalent COBOL coding for the C coding syntax is; CALL C_mysql_real_connect using by reference E-ptrSQLConnect by reference C-cMySQLHostName by reference C-cMySQLUserId by reference C-cMySQLPassword by reference C-cMySQLDBName by content C-cMySQLPort by reference C-cMySQLSocket by content C-cMySQLFlag giving E-ptrSQLEnv I have tested that before reaching this Calling portion all the variables hold the values what I have set earlier. but after execution it stores the error as it's value (i.e., Can't connect to MySQL server on localhost (10061) ) and the pointer variable E_ptrSQLConnect hold value 0. Before invoking
RE: mysql.sock permission srwxrwxrwx
yeah, if its owned by mysql with no write privs. That doesn't mean you cant change it using root.. I'm trying to open mysql.sock file as a ROOT and receive the error message permission denied. Why mysql.sock has such permission? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql query for faceted classification system
* Seamus R Abshere i am developing a photo gallery with php4/mysql4.0 that uses faceted classification. -my tables: photos(photoid) metadata(photoid,facetid) -to select all of the photoid's that are associated with either facetid 1 or 2: SELECT DISTINCT photos.* FROM photos,metadata WHERE photos.photoid = metadata.photoid AND (metadata.facetid = 1 OR metadata.facetid = 2) but what if i want to select all photoids that are associated with BOTH facetids? is there a join? (just sticking AND in there won't work, because any row in metadata can only have one facetid.) You can join the metadata table twice: SELECT photos.* FROM photos,metadata m1,metadata m2 WHERE photos.photoid = m1.photoid AND m1.facetid = 1 AND photos.photoid = m2.photoid AND m2.facetid = 2 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Broadcast to search available MySQL Server in Network?
Have you tried this code using: hostaddr.sin_port :=htons(3306); I've never done this but the code looks like it is checking the appropriate port on all machines found. See what happens when you use the MySQL default port. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Change from loop to single query
It wasn't code, just an example to get my question across clearly... I will try the IN, however the manual says Returns 1 if expr is any of the values in the IN list I want to do it for every value. So I'm trying to accomplish this in one query: UPDATE users SET status = no WHERE name = bob; UPDATE users SET status = no WHERE name = fred; UPDATE users SET status = no WHERE name = pancho; UPDATE users SET status = no WHERE name = jason; UPDATE users SET status = no WHERE name = ted; UPDATE users SET status = no WHERE name = patricia; UPDATE users SET status = no WHERE name = andrew; UPDATE users SET status = no WHERE name = jesus; I'm going to try it now. On Fri, 2004-01-02 at 11:40, Jeremy March wrote: What language are you using? It's not clear from your example what language you're using (no $s, but you also forgot to increment your array so?), so I'll give you an example in PHP: $query_string = implode( OR name = , $yourarray); mysql_query(UPDATE users SET status = no WHERE name = $query_string;); _ Take advantage of our limited-time introductory offer for dial-up Internet access. http://join.msn.com/?page=dept/dialup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf
Hello: I am installing MySQL 5.0.0 (on a fresh install RH9). I am following binary installation as outlined in official MySQL documentation with default location /usr/local. I sure appreciate if somebody on this list is kind enough to email or list contents of my.cnf file. Thanks. Kirti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: MySQL and Crystal Reports?
I've forwarded this reply to the list so other people may benefit from the information. Steve. Begin forwarded message: From: Eldon Ziegler [EMAIL PROTECTED] Date: 2 January 2004 18:58:36 GMT To: Steve Folly [EMAIL PROTECTED] Subject: Re: MySQL and Crystal Reports? I found their capabilities chart less than clear. A search of their web site didn't turn up anything that sounded directly related but there was a patch having to do with recognizing names correctly with MySQL and the C.R. Advanced Edition. Once I installed that patch the Standard Edition works fine with MySQL. Go figure! Eldon At 07:50 am 1/2/2004, you wrote: On 2 Jan 2004, at 12:26, Eldon Ziegler wrote: Has anyone used Crystal Reports successfully with MySQL? I tried the ODBC driver but it was rejected with a message that the Professional version was needed. The issue might be with Crystal Reports itself and nothing to do with MySQL. Are you using the Standard edition? http://www.crystaldecisions.com/products/crystalreports/editions.asp ... indicates you can only use ODBC with Crystal Reports starting with the Professional edition. HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Eldon Ziegler President ProAtion Systems, Inc. www.proation.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble With Counting New Documents With Complex Query
I need help width formulating the most
effective (in terms of processing time)
SQL query to count all the new
documents in the repository, where new is
defined as from 00:00:01 up to 23:59:59
today. My current query does not give me
satisfactory results, it creates a visible
delay in rendering of the main page of one of
the departments (Drugs) :8[[[
(at least I, for now, think it's the culprit).
It's for the url: https://hyperreal.info
site, see for yourself, notice the delay
url: https://hyperreal.info/drugs/go.to/index .
Currently I ask MySQL to (offending
PHP fragment follows, I hope it is self-
explanatory).
?
$suma = 0;
$pytanie = SELECT COUNT(DISTINCT x_article.ID) AS CNT ;
$pytanie .= FROM x_article ;
$pytanie .= LEFT JOIN x_instance ;
$pytanie .= ON x_article.ID = x_instance.Article ;
$pytanie .= LEFT JOIN x_section ;
$pytanie .= ON x_instance.Section = x_section.ID ;
$pytanie .= WHERE (x_section.Status 1) = 0 ; // not empty
$pytanie .= AND (x_section.Dept = 2 OR x_section.Dept = 5) ; // Drugs, NeuroGroove
$pytanie .= AND (x_instance.Status 255) = 0 ; // not hidden, etc
$pytanie .= AND UNIX_TIMESTAMP(x_article.Date) BETWEEN . mktime(0, 0, 1, date('m'),
date('d'), date('Y')) . AND UNIX_TIMESTAMP(NOW()) ;
$pytanie .= GROUP BY x_article.ID;
$wynik = mysql_query($pytanie);
while ($tmp = mysql_fetch_array($wynik))
{
$suma += $tmp['CNT'];
}
if ($suma)
{
// pretty-printing of the result
$dzisdodano = str_pad((string)(int)$suma, 4, '0', STR_PAD_LEFT);
}
else $dzisdodano = '';
?
The table layout is as follows:
mysql DESC x_article;
+-+--+--+-+--++
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+--++
| ID | int(10) unsigned | | PRI | NULL | auto_increment |
| Name| varchar(255) | YES | MUL | NULL ||
| Description | varchar(255) | YES | | NULL ||
| Keywords| varchar(255) | YES | | NULL ||
| Content | mediumtext | | | ||
| Date| datetime | | | 2001-01-01 00:00:00 ||
| Author | varchar(100) | | | [EMAIL PROTECTED] ||
| Feedback| varchar(100) | YES | | NULL ||
| Size| int(32) | YES | | NULL ||
| Words | int(32) | YES | | NULL ||
| Images | int(32) | YES | | NULL ||
+-+--+--+-+--++
mysql DESC x_instance;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| Article | mediumint(9) | | MUL | 0 | |
| Section | mediumint(9) | | MUL | 0 | |
| Priority | tinyint(4) | | | 0 | |
| Status | int(16) unsigned | | | 0 | |
+--+--+--+-+-+---+
mysql DESC x_section;
+--+--+--+-+---++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+---++
| ID | mediumint(9) | | PRI | NULL | auto_increment |
| Name | varchar(100) | | MUL | ||
| Parent | mediumint(9) | | MUL | 0 ||
| Dept | smallint(6) | | MUL | 0 ||
| Priority | tinyint(4) | | | 3 ||
| Keywords | varchar(255) | YES | | NULL ||
| Sorting | varchar(255) | | | Priority DESC ||
| OrderBy | varchar(255) | YES | | NULL ||
| SplitAt | smallint(5) unsigned | | | 25||
| Status | int(16) unsigned | | | 0 ||
+--+--+--+-+---++
Tell me if you need any additional information.
Thank you for all your help.
MySQL version is 4.0.17, PLD Linux Distribution MySQL RPM.
--
Seks, seksi, seksolatki... news:pl.soc.seks.moderowana
http://hyperreal.info { iWanToDie } WiNoNa) (
http://szatanowskie-ladacznice.0-700.pl foReVeR( * )
Poznaj jej zwiewne ksztaty...
Re: my.cnf
Hi, Look at /usr/local/mysql/support-files directory. There are several examples of my.cnf files for different configurations there (my-huge.cnf, my-large.cnf, ...). Best regards, Mikhail. - Original Message - From: Kirti S. Bajwa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 02, 2004 8:52 PM Subject: my.cnf Hello: I am installing MySQL 5.0.0 (on a fresh install RH9). I am following binary installation as outlined in official MySQL documentation with default location /usr/local. I sure appreciate if somebody on this list is kind enough to email or list contents of my.cnf file. Thanks. Kirti -- 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: my.cnf
The correct entries for your my.cnf will vary depending on which features that you want to support. There is no standard contents. See the manual for the chapters on installation, configuration and optimization. Most distributions come with sample cnf files called: my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf. Where they will be located will vary depending on the distribution but they shouldn't be too hard to find. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble With Counting New Documents With Complex Query
One other thing to check is that there are indexes on the fields in the other tables that you are joining to. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Clustered Indexes
In MS SQL Server 2000, it is possible to create what MS refers to as clustered indexes. You can have only one clustered index per table, because the index is created by sorting the entire table by the row that is to be the clustered index. If you have a table with a row called ZIPCODE, for example, when you create a clustered index on that row, the entire table is sorted by that row. This is supposed to be faster than a regular index, since the index IS the database, not a separate file. (I believe the previous is all correct. I am new to databases, so I may be mistaken, but I believe this is correct.) Now, my question: Does MySQL have an equivalent to MS SQL Server's clustered indexes for MyISAM tables? If so, in which versions of MySQL is it available? Thanks! -Casey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble With Counting New Documents With Complex Query
on 1/2/04 12:59 PM, Adam i Agnieszka Gasiorowski FNORD wrote: Tell me if you need any additional information. Thank you for all your help. Some additional information would help, yes. What exactly are the sections, for example? Do you need to know the number of new _articles_ total, the new of new articles per section, or do sections also count as documents somehow? Can an article really belong to more than one section (or a section to more than one article)? I wasn't sure if section is an article section (page 1, page 2, etc), or if section is the category the article belongs to. I visited the site, but I don't speak the language, so I wasn't quite sure what was going on. Your query may be running slowly due to more than one factor. Although I see a couple of ways to help in just the where clause, dropping a table join or two would also help if it can be done. -- Bob IQ2GI5SCP2 Things You Don't Hear Every Day, #'s 16 and 17: A professor: It's all right if you come to class high. A(nother) professor: I think base 16 is cool. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change from loop to single query
on 1/2/04 12:40 PM, Jeremy March wrote:
What language are you using? It's not clear from your example what language
you're using (no $s, but you also forgot to increment your array so?), so
I'll give you an example in PHP:
You can still do better than that. IN will be more readable than a bunch of WHERE's.
Also, in your example, you forgot to quote the names in MySQL. Try:
$query_string = implode(', ', $yourarray);
mysql_query(UPDATE users SET status = 'no' WHERE name IN ('$query_string'));
--
Bob
IQ2GI5SCP2
Things You Don't Hear Every Day, #'s 16 and 17:
A professor: It's all right if you come to class high.
A(nother) professor: I think base 16 is cool.
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] Trouble With Counting New Documents With Complex Query
In all probability it's (x_section.Status 1) = 0 and
(x_instance.Status 255) = 0 that's giving you the problem.
Unfortunately this is a database schema problem not a query fix. By putting
a computation on a field into the WHERE clause, you're forcing the database
to do that computation on every record that meets the other WHERE criteria
(given that the optimizer is working well and you have the right indexes --
worst case you're doing those computations on *every* record in the table).
Generally speaking, bit masks incur performance penalties in return for
space gains... but storage is cheap and time isn't. This penalty is worse
for databases. The general rule is that bit-mask fields and databases are a
bad combination. If you break bit zero out of x_section.Status into say
x_section.isEmpty (defined as a tinyint or char(1) if you are really
worried about space), then add an index on x_section.isEmpty, then you'll
get the performance gain. Repeat as required with x_instance.Status.
If breaking out the bit masks is going to be really painful, then consider
getting a result set without the mask criteria in the query, make sure the
remaining fields in the WHERE are indexed, then filter out the results you
want in the script. This depends on which percentage of the result set you
eliminate with those masks... if the result set is 10% bigger, then this
works, if the result set is 10,000% bigger then take the pain and go break
up the bit-mask fields.
At 2004/01/02 12:59, Adam i Agnieszka Gasiorowski FNORD wrote:
I need help width formulating the most
effective (in terms of processing time)
SQL query to count all the new
documents in the repository, where new is
defined as from 00:00:01 up to 23:59:59
today. My current query does not give me
satisfactory results, it creates a visible
delay in rendering of the main page of one of
the departments (Drugs) :8[[[
(at least I, for now, think it's the culprit).
It's for the url: https://hyperreal.info
site, see for yourself, notice the delay
url: https://hyperreal.info/drugs/go.to/index .
Currently I ask MySQL to (offending
PHP fragment follows, I hope it is self-
explanatory).
?
$suma = 0;
$pytanie = SELECT COUNT(DISTINCT x_article.ID) AS CNT ;
$pytanie .= FROM x_article ;
$pytanie .= LEFT JOIN x_instance ;
$pytanie .= ON x_article.ID = x_instance.Article ;
$pytanie .= LEFT JOIN x_section ;
$pytanie .= ON x_instance.Section = x_section.ID ;
$pytanie .= WHERE (x_section.Status 1) = 0 ; // not empty
$pytanie .= AND (x_section.Dept = 2 OR x_section.Dept = 5) ; // Drugs,
NeuroGroove
$pytanie .= AND (x_instance.Status 255) = 0 ; // not hidden, etc
$pytanie .= AND UNIX_TIMESTAMP(x_article.Date) BETWEEN . mktime(0, 0,
1, date('m'), date('d'), date('Y')) . AND UNIX_TIMESTAMP(NOW()) ;
$pytanie .= GROUP BY x_article.ID;
$wynik = mysql_query($pytanie);
while ($tmp = mysql_fetch_array($wynik))
{
$suma += $tmp['CNT'];
}
if ($suma)
{
// pretty-printing of the result
$dzisdodano = str_pad((string)(int)$suma, 4, '0', STR_PAD_LEFT);
}
else $dzisdodano = '';
?
The table layout is as follows:
mysql DESC x_article;
+-+--+--+-+--++
| Field | Type | Null | Key | Default |
Extra |
+-+--+--+-+--++
| ID | int(10) unsigned | | PRI | NULL |
auto_increment |
| Name| varchar(255) | YES | MUL |
NULL ||
| Description | varchar(255) | YES | |
NULL ||
| Keywords| varchar(255) | YES | |
NULL ||
| Content |
mediumtext | | | ||
| Date| datetime | | | 2001-01-01
00:00:00 ||
| Author | varchar(100) | | | [EMAIL PROTECTED]
||
| Feedback| varchar(100) | YES | |
NULL ||
| Size| int(32) | YES | |
NULL ||
| Words | int(32) | YES | |
NULL ||
| Images | int(32) | YES | |
NULL ||
+-+--+--+-+--++
mysql DESC x_instance;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| Article | mediumint(9) | | MUL | 0 | |
| Section | mediumint(9) | | MUL | 0 | |
| Priority | tinyint(4) | | | 0 | |
| Status | int(16) unsigned | | | 0 | |
Re: Clustered Indexes
InnoDB does clustering in the Sybase style but MyISAM is lean, mean and quick. If you really wanted to do this though, you could probably do the following: CREATE TABLE tab SELECT * FROM old_tab ORDER BY zip_code ASC; And then add the indexes! One thing you should know though - MyISAM's indexing is pretty quick (as is InnoDB's) so ordering in this fashion is very unlikely to bring you any benefit in terms of finding results. The only performance benefit you may get would be if you were to hit the database with a lot of queries asking for a range of sequential values on the ordered attribute AND you put the effort into ensuring that the table's ordering characteristic didn't change much. Regards, Chris On Sat, 2004-01-03 at 09:50, Casey Sheridan wrote: In MS SQL Server 2000, it is possible to create what MS refers to as clustered indexes. You can have only one clustered index per table, because the index is created by sorting the entire table by the row that is to be the clustered index. If you have a table with a row called ZIPCODE, for example, when you create a clustered index on that row, the entire table is sorted by that row. This is supposed to be faster than a regular index, since the index IS the database, not a separate file. (I believe the previous is all correct. I am new to databases, so I may be mistaken, but I believe this is correct.) Now, my question: Does MySQL have an equivalent to MS SQL Server's clustered indexes for MyISAM tables? If so, in which versions of MySQL is it available? Thanks! -Casey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: need form to input records View report
Its been a long time since I've looked at RT. I was trying to remember what I had seen before that did things very similar to what I'm doing and now you've reminded me... ty.. I don't think its 100% of my solution, but it might be part. More reading LOL Troy Asif Iqbal [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: need form to input records View report
Thanks for the good tips... I'll be doing that this weekend. I think the hardest part is going to be trying to figure out how to store a contract once all the fields have been populated. Its an OO document at this point. Troy [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] If you want to build something like a web form you will need to use HTML and a scripting language. A common solution to do what you are asking is to use PHP. You will need to be familiar with PHP to go further. Go to http://www.php.net to get a hold of the basics. There is no way to write a walkthrough for your problem here as it would be quite long :o). It would also be repititive since there are millions of sites that provide such walkthroughs. Go to www.sitepoint.com, which is a web dev portal. My favortite. They have tons of usefull articles and step by step walkthroughs. From a big picture point of view what you need to do is use PHP to generate HTML dynamically and handle application logic, database interactivity. Use the MySQL database as your data store [obviously ;)]. PHP is an excellent choice as it works very well with MySQL. If this sounds like greek then don't worry too much. Head to sitepoint.com and go to the php section and start learning. It is an excellent resource. PHP.net is a good place too although you might want to go to sitepoint first. Best of luck, Arjun Quoting Troy T. Hall [EMAIL PROTECTED]: I know this sounds stupid but I'm totally lost. I've created a MySQL DB whose purpose is to track customers who have not gotten a newspaper. I've created all the necessary fields, and have managed to learn how to add/delete/modify the records in mysqlcc, but what I want is to have a predesigned form like you'd find on a webpage where you simply enter the complaints and hit submit or whatever, and it responds with the complaint ID # ( ComplaintID is an autoincrement field in the db ) Then I need to be able to print a report in a nicely labeled/readable format showing all entries made that day where the chargeable field is not List (Chargeable is an enum consisting of yes, no, list). Will someone please be kind enough to point me in the right direction. I can't seem to get my head around this concept. Troy oh, if it matters, I'm using Linux Mandrake 9.2 with the Gnome 2.0 GDM. I also have qtDesigner, OO, Screem. -- 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]
Data is not getting displayed
Hi, I have a table having 3 field of text datatype. One of the field is Age_type where the data is either 0+ or 75. Its found that on executing the select statement, this particular column is showing blank in linux system, where as the same is showing the datain windows system. In both system( windows and linux), the count of records for Age_type is showing 3000, which is correct. I am wondering where I have gone wrong. Why in linux this data is not getting displayed on selection. thanx Deepak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
