Table Performance - Query
Dear All, I have the following table (MYISSAM or INNODB) Tablename Filed Type size app_colid_Examid registerno varchar 15 app_colid_Examid subcode varchar 15 app_colid_Examid mode varchar 2 app_colid_Examid programmeid Integer app_colid_Examid subjectorder Integer app_colid_Examid collegeid Integer app_colid_Examid dummyno Varchar 15 app_colid_Examid pacno Integer app_colid_Examid cia Varchar 3 app_colid_Examid ival Varchar 3 app_colid_Examid iival Varchar 3 app_colid_Examid iiival Varchar 3 app_colid_Examid sem Varchar 3 app_colid_Examid modre Varchar 3 app_colid_Examid rval Varchar 3 app_colid_Examid res Varchar 3 If i have 6 laks records [approx]; does it cause performance issue in win based (mysql 5.1.x) ? I will not apply any kind of comparison in query (only Select/update eill take place). I will manage the filters in the record set/data set. Other wise i will go for splitting of tables by grouping the records. Any advice will be appreciated. Thank you in advance Viakrm
Query Browser - Hiding View definition
Hi, Is it possible to hide the VIEW definitions from query browser ? Or any other solution to hide the views ? Because I have used views for report purpose; where I have used AES_DECRIPT() function or decipher. When view is visible, this function and KEY also visible. I want to disable this. Any solutions/suggestions. Thank in advance. Vikram.
Next Unique Number - Generation
Hi there, I need a technical help fro you, I have developed a software for college school. Here we have concept called register number/admission number. These are two unique umber for each student. My application resides Client/server model. These numbers will be generated (some defined format) to each student when they are admitted at first time. This admission process is taken place at different nodes at a time. In this scenario, I am facing difficulty that, the same number is generated at time in two machines. (Logic is newest number will be displayed in the node before admission). How can I stop this logical issue? Can i have solution for this? Thank you in advance.
Re: Next Unique Number - Generation
Hi Neil, thank you for your response, The admission number/register number will contain text/symbol(-,/,#). Is it possible to keep this as increment ? From: Neil Martins neil.mart...@exactuscorp.co.in To: Vikram A vikkiatb...@yahoo.in; MY SQL Mailing list mysql@lists.mysql.com Sent: Thursday, 21 July 2011 12:06 PM Subject: Re: Next Unique Number - Generation Hi in ur database define the 2 numbers as auto increment. Neil Martins Exactus Corporation Pvt. Limited ISO 9001:2000 certified 1st floor, Raheja Plaza, LBS Marg, Ghatkopar (West), Mumbai 400 086 India T: (9122) 66505900 F: (9122) 22040826 Url:www.exactuscorp.com DISCLAIMER and CONFIDENTIALITY CAUTION This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Unauthorized reading, dissemination, distribution or copying of this communication is prohibited. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by telephone at (9122) 66505900 or email us at i...@exactuscorp.com and promptly destroy the original communication. Thank you for your cooperation. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. Communicating through email is not secure and capable of interception, corruption and delays. Anyone communicating with Exactus Corporation Limited by email accepts the risks involved and their consequences. The recipient should check this email and any attachments for the presence of viruses. Exactus accepts no liability for any damage caused by any virus transmitted by this email. - Original Message - From: Vikram A vikkiatb...@yahoo.in To: MY SQL Mailing list mysql@lists.mysql.com Sent: Thursday, July 21, 2011 11:48 AM Subject: Next Unique Number - Generation Hi there, I need a technical help fro you, I have developed a software for college school. Here we have concept called register number/admission number. These are two unique umber for each student. My application resides Client/server model. These numbers will be generated (some defined format) to each student when they are admitted at first time. This admission process is taken place at different nodes at a time. In this scenario, I am facing difficulty that, the same number is generated at time in two machines. (Logic is newest number will be displayed in the node before admission). How can I stop this logical issue? Can i have solution for this? Thank you in advance.
Re: Encoding Table Name and Filed Name
Mr. Jerry/and all, I am drooping this idea of making schema encryption . As it leads to lots of confusion as Mr. John said. I am following the data level encription. Thank you for the comments. Vikram From: Jerry Schwartz je...@gii.co.jp To: 'Vikram A' vikkiatb...@yahoo.in; 'Johan De Meersman' vegiv...@tuxera.be Cc: 'MY SQL Mailing list' mysql@lists.mysql.com Sent: Friday, 17 June 2011 7:41 PM Subject: RE: Encoding Table Name and Filed Name -Original Message- From: Vikram A [mailto:vikkiatb...@yahoo.in] Sent: Friday, June 17, 2011 2:54 AM To: Johan De Meersman Cc: MY SQL Mailing list Subject: Re: Encoding Table Name and Filed Name Sir, I agree, its impossible to do manual look ups. But our aim is to avoid the use of DB with out code. Also we have ensured, 'secret data is encrypted using some functions with key'. [JS] A lot depends upon your ultimate goal. Do you need to keep anyone from seeing the data, or do you need to prevent sabotage? Even if your DB manager can't tell what the real table names are, he could still sabotage your system simply by deleting the entire database. I shall follow both encode i.e, 1) filed and table name, 2) data level? Or only data level is enough by having accounts as you suggested? [JS] Encrypting at the data level will prevent anyone from seeing the real data values, but even that requires more than just encryption. Ideally, you would have two people work on the encryption algorithms separately and you must make sure that they cannot communicate with each other -- ever! You will need those two people (or companies) forever if you ever want to change the application. There are books on this. You always have to trust somebody, and that somebody is always the weak link. Somebody must be using the application, and that someone is a bigger risk that your DB manager. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com Thanks You. Vikram From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Friday, 17 June 2011 11:50 AM Subject: Re: Encoding Table Name and Filed Name - Original Message - From: Vikram A vikkiatb...@yahoo.in My question is, DO i face any negative project management problems by doing this? Pleas share your experience on this aspect and commend our idea. Seems... a bit pointless, no? If someone has access to the database, they can still see the data. If someone has access to the code, they can still figure out the naming scheme. If, for some reason, you have to do manual lookups (think debugging, custom reporting, ...) you're making your own life hard. If you don't want people to see your data, manage your accounts. If people leave, delete their accounts. Make sure your code prevents SQL injections (use bind variables and so on). Firewall off your server from everything but the application server. Et cetera ad nauseam. There's plenty of security recommendations, but I've never heard of anyone encrypting their table and field names. It sounds like something upper management would come up with :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vikkiatb...@yahoo.in
Re: Encoding Table Name and Filed Name
Sir, I agree, its impossible to do manual look ups. But our aim is to avoid the use of DB with out code. Also we have ensured, 'secret data is encrypted using some functions with key'. I shall follow both encode i.e, 1) filed and table name, 2) data level? Or only data level is enough by having accounts as you suggested? Thanks You. Vikram From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Friday, 17 June 2011 11:50 AM Subject: Re: Encoding Table Name and Filed Name - Original Message - From: Vikram A vikkiatb...@yahoo.in My question is, DO i face any negative project management problems by doing this? Pleas share your experience on this aspect and commend our idea. Seems... a bit pointless, no? If someone has access to the database, they can still see the data. If someone has access to the code, they can still figure out the naming scheme. If, for some reason, you have to do manual lookups (think debugging, custom reporting, ...) you're making your own life hard. If you don't want people to see your data, manage your accounts. If people leave, delete their accounts. Make sure your code prevents SQL injections (use bind variables and so on). Firewall off your server from everything but the application server. Et cetera ad nauseam. There's plenty of security recommendations, but I've never heard of anyone encrypting their table and field names. It sounds like something upper management would come up with :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vikkiatb...@yahoo.in
Encoding Table Name and Filed Name
Hello there, I request you to give your commends on the following, 1. We have planned to cipher our table name and filed name [the necessary data will be ciphered with predefined functions with 'key'] 2. This cipher functions will be our own code 3. Using these functions we can mange the applications We are expecting following advantages through this, Unauthorized use either by the 1. DB administrator 2. ex-developer Or 3. Any body My question is, DO i face any negative project management problems by doing this? Pleas share your experience on this aspect and commend our idea. Thank you Vikram A
Re: Table Records Deleted by anonymous user!
Sir, Ok sir, thank you for your advise. We shall keep the logs in another Disk/Drive. From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Fri, 11 March, 2011 2:43:33 PM Subject: Re: Table Records Deleted by anonymous user! From: Vikram A vikkiatb...@yahoo.in Thank you for info. Now we enabled the logs. The DB administrator itself made a mistake that he restored the back up This may be obvious, but keep your logs on separate disks if you can - full query logs take quite a bit of I/O away, so if you have them on the same disks as your data they may have a significant impact on performance. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Table Records Deleted by anonymous user!
Dear Experts! Our institution has a centralized MySQL server which is accessed by several clients. Today we found that a particular table's records are missing. We could say that it is done intentionally but could not point anyone because we did not enable the logging feature in MySQL. I want to know whether it is possible to find the time of the query execution and the user/node name who executed it. All the data lost are very confidential and cannot be compromised. Kindly give me a solution to restore the lost data or the particulars of the person who made it(intentionally). Thank You. Vikram
AES_DECRYPT - Returens Blob[empty]
Hi, MySQL (version 5.1.45 , windows based) AES_DECRYPT () function is not working properly, If I removed Order by clause from the following query it works Fine. If I add order by it resulting blob[empty] value. select S.semyear as semester, S.part as part, S.code as code, S.subject as Title, AES_DECRYPT(R.cia, 'TEMPSTR') as cia, AES_DECRYPT(R.sem, 'TEMPSTR') as sem, AES_DECRYPT(R.result, 'TEMPSTR') as result, R.exam, R.regarr from examresults as R, g_subject as S where S.code = R.code and R.regno = 'A08BBD03' Order By S.semyear, R.rank, S.code, R.regarr , R.exam Can i have advise Please? Thank you. Vikram
Re: Best encription method?
Sir, We have done a encryption by using our own algorithm. it works fine. But during the report analysis we are facing the performance issue. Which is not appreciated by our users. We need only one column to be encrypted and the column size will never exceed 3 chars. We have planed to use the AES_ENCRYPT and AES_DECRYPT by using the key string as a parameter. We can keep this key string as our security point. We found it is giving better performance in all cases. Can i have your advise please? Thank you in advance! Vikram A From: Vikram A vikkiatb...@yahoo.in To: Johan De Meersman vegiv...@tuxera.be Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Wed, 29 September, 2010 11:46:18 AM Subject: Re: Best encription method? Sir, I must adopt the encryption. I shall bear the cost of the memory. Thank you for the guidance. Vikram A From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Tue, 28 September, 2010 2:08:57 PM Subject: Re: Best encription method? On Mon, Sep 27, 2010 at 3:43 PM, Vikram A vikkiatb...@yahoo.in wrote: Normally, If i need to store an integer value i have to define it as int, If I encrypt this, i must define its type as string of different size[it depend upon the encryption output] than its original size. It increases the db size. I am known that if it is secure i have to choose encryption. but when i choose encryption it leads the more memory usage. Well, yes :-) That's part of the price you're going to pay for the security. As the programmer said; I can make it better, faster and cheaper. Pick any two. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Best encription method?
Dear Sir, As you advised we will keep the key as well documented. Thank you Vikram A. From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Tue, 9 November, 2010 7:05:39 PM Subject: Re: Best encription method? Seems like a better plan than simply rolling your own, yes. Do make sure that the key string is well-documented :-p On Tue, Nov 9, 2010 at 2:30 PM, Vikram A vikkiatb...@yahoo.in wrote: Sir, We have done a encryption by using our own algorithm. it works fine. But during the report analysis we are facing the performance issue. Which is not appreciated by our users. We need only one column to be encrypted and the column size will never exceed 3 chars. We have planed to use the AES_ENCRYPT and AES_DECRYPT by using the key string as a parameter. We can keep this key string as our security point. We found it is giving better performance in all cases. Can i have your advise please? Thank you in advance! Vikram A From: Vikram A vikkiatb...@yahoo.in To: Johan De Meersman vegiv...@tuxera.be Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Wed, 29 September, 2010 11:46:18 AM Subject: Re: Best encription method? Sir, I must adopt the encryption. I shall bear the cost of the memory. Thank you for the guidance. Vikram A From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Tue, 28 September, 2010 2:08:57 PM Subject: Re: Best encription method? On Mon, Sep 27, 2010 at 3:43 PM, Vikram A vikkiatb...@yahoo.in wrote: Normally, If i need to store an integer value i have to define it as int, If I encrypt this, i must define its type as string of different size[it depend upon the encryption output] than its original size. It increases the db size. I am known that if it is secure i have to choose encryption. but when i choose encryption it leads the more memory usage. Well, yes :-) That's part of the price you're going to pay for the security. As the programmer said; I can make it better, faster and cheaper. Pick any two. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Best encription method?
Sir, I must adopt the encryption. I shall bear the cost of the memory. Thank you for the guidance. Vikram A From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Tue, 28 September, 2010 2:08:57 PM Subject: Re: Best encription method? On Mon, Sep 27, 2010 at 3:43 PM, Vikram A vikkiatb...@yahoo.in wrote: Normally, If i need to store an integer value i have to define it as int, If I encrypt this, i must define its type as string of different size[it depend upon the encryption output] than its original size. It increases the db size. I am known that if it is secure i have to choose encryption. but when i choose encryption it leads the more memory usage. Well, yes :-) That's part of the price you're going to pay for the security. As the programmer said; I can make it better, faster and cheaper. Pick any two. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Best encription method?
Hello experts! Can i have your valuable suggestion on the following? I would like to encrypt a particular table of records. I hope can choose two ways,number one, application level encryption method choosing our own encryption algorithm and an another is database level encryption. my question is, which is portable, which is consistent? or altogether the encryption should not degrades the performance. Note: The application is going to be across an intranet Suggestions are appreciated! Thank you in advance. Vikram A
Re: Best encription method?
Dear Sir, Thank you for the suggestion, as you suggested i shall go for application level. I have another query too, please answer, Normally, If i need to store an integer value i have to define it as int, If I encrypt this, i must define its type as string of different size[it depend upon the encryption output] than its original size. It increases the db size. I am known that if it is secure i have to choose encryption. but when i choose encryption it leads the more memory usage. Any comment on this? Thank you Vikram From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Mon, 27 September, 2010 6:55:04 PM Subject: Re: Best encription method? Both have benefits. Application level: * data is encrypted during transmit, too * processing is offloaded from your hard-to-scale database server * decrypt keys don't pass your database, so dba or other users can't peekDB * Guaranteed consistent implementation regardless of client Can't think of other benefits of DB-side encryption, offhand :-) I would go for the app-side every time. Let the database worry about managing the data, don't bother it with application stuff. On Mon, Sep 27, 2010 at 3:15 PM, Vikram A vikkiatb...@yahoo.in wrote: Hello experts! Can i have your valuable suggestion on the following? I would like to encrypt a particular table of records. I hope can choose two ways,number one, application level encryption method choosing our own encryption algorithm and an another is database level encryption. my question is, which is portable, which is consistent? or altogether the encryption should not degrades the performance. Note: The application is going to be across an intranet Suggestions are appreciated! Thank you in advance. Vikram A -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Storage engine
Hi, I have one doubt; Please can any one give your view? I have MYSQL version 5.1.x. I have some existing Databases in which the tables are created using MYISSAM storage engine. Now i have another database which will use INNODB engine. My question is, If i set default engine as INNODB Whether it will make any misconduct on other MYISSAM databases? Thank you! Vikram A
Execution Time
Hi, I have doubt on the execution time. Can any one help me! Which operation will take much time INSERT or UPDATE ? Thank you Vikram
Re: MySQL For Huge Collections
Hi All, In this case how the images of a book will be stored, a chapter may contain number of images with different size. Or It deals only text? Thanks. Vikram A From: Jerry Schwartz je...@gii.co.jp To: Andy listan...@gmail.com; mysql@lists.mysql.com Sent: Fri, 11 June, 2010 9:05:26 PM Subject: RE: MySQL For Huge Collections -Original Message- From: Andy [mailto:listan...@gmail.com] Sent: Friday, June 11, 2010 8:09 AM To: mysql@lists.mysql.com Subject: Re: MySQL For Huge Collections Hello all, Thanks much for your replies. OK, so I realized that I may not have explained the problem clearly enough. I will try to do it now. I am a researcher in computational linguistics, and I am trying to research language usage and writing styles across different genres of books over the years. The system I am developing is not just to serve up e-book content (that will happen later possibly) but to help me analyze at micro-level the different constituent elements of a book ( say at chapter level or paragraph level). As part of this work, I need to break-up, store and repeatedly run queries across multiple e-books. Here are several additional sample queries: * give me books that use the word ABC * give me the first 10 pages of e-book XYZ * give me chapter 1 of all e-books [JS] You pose an interesting challenge. Normally, my choice is to store big things as normal files and maintain the index (with accompanying descriptive information) in the database. You've probably seen systems like this, where you assign tags to pictures. That would certainly handle the second two cases (with some ancillary programming, of course). Your first example is a bigger challenge. MySQL can do full text searches, but from what I've read they can get painfully slow. I never encountered that problem, but my databases are rather small (~10 rows). For this technique, you would want to store all of your text in LONGTEXT columns. I've also read that there are plug-ins that do the same thing, only faster. I'm not sure how you would define a page of an e-book, and I suspect you would also deal with individual paragraphs or lines. My suggestion for that would be to have a book table, with such things as the title and author and perhaps ISBN; a page table identifying which paragraphs are on which page (for a given book); a paragraph table identifying which lines are in which paragraph; and then a lines table that contains the actual text of each line. [book1, title, ...] - [book1, para1] - [para1, line1, linetext] [book2, title, ...] [book1, para2] [para1, line2, linetext] [book3, title, ...] [book1, para3] [para1, line3, linetext] ... [book1, para4] [para1, line4, linetext] ...[para1, line5, linetext] ... This would let you have a full text index on the titles, and another on the linetext, with a number of ways to limit your searches. Because the linetext field would be relatively short, the search should be relatively fast even though there might be a relatively large number of records returned if you wanted to search entire books. NOTE: Small test cases might yield surprising results because of the way full text searches determine relevancy! This has bitten me more than once. This was fun, I hope my suggestions make sense. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vikkiatb...@yahoo.in
Best Bin log - type in MYSQL
Hi, I am using win-server and xp for my master, slave setup. when i do the beginTrans with bin-log = STATEMENT it is causing error. If i defined this with any one of the following, its working quite fine. MIXED, ROW. Which is the best one in the above two? Or how it differs? Can I have your explanations? Thank you VIKRAM
Re: Best Bin log - type in MYSQL
Sir, I am Sorry, My request is related to binlog_format variable. Thank you From: ewen fortune ewen.fort...@gmail.com To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Tue, 4 May, 2010 3:31:44 PM Subject: Re: Best Bin log - type in MYSQL Vikram, I am using win-server and xp for my master, slave setup. when i do the beginTrans with bin-log = STATEMENT it is causing error. If i defined this with any one of the following, its working quite fine. MIXED, ROW. Which is the best one in the above two? Or how it differs? Can I have your explanations? I think you are mixing up your log-bin and binlog_format variables. The first of which turns on binary logging and optionally takes a filename as an argument, the second controls the binary log format which can be STATEMENT, ROW or MIXED. http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#option_mysqld_log-bin http://dev.mysql.com/doc/refman/5.1/en/binary-log-formats.html Ewen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vikkiatb...@yahoo.in
My sql Security
Hi all, I have some security issues. I would like to have your suggestions/solutions. I have winserver2003 with mysql 5.1.45. We have client serve application that allows multi-login system with various people. I am getting user name, password for database login when the try to use login [which is for Application]. By using DB the user name and the password, people who know the mysql are opening the DB using some GUI tools. How this can be avoided; because it is major issue right now in my work place. Please Can any one can help me? Thank you VIKRAM A
Re: My sql Security
Sir, We dropped the Idea of getting db user name and passwd during the login; because we have more than 1000 users and we can not give the user name and the password. We will set the connection details in the encrypted format in some config file. So that the user name, and the password will not be given to user. Early, We planned to maintain the audit information[who done the change, when it has done, what kind of change and so on], for this purpose only we have given individual user name and password. Now we will remove the DB login part. Any other way to avoid the DB connection from the other GUI/connecting tools though they have given a access to db? Thank you for the information. Vikram From: nwood nw...@plus.net To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Wed, 28 April, 2010 3:39:23 PM Subject: Re: My sql Security On Wed, 2010-04-28 at 13:58 +0530, Vikram A wrote: Hi all, I have some security issues. I would like to have your suggestions/solutions. I have winserver2003 with mysql 5.1.45. We have client serve application that allows multi-login system with various people. I am getting user name, password for database login when the try to use login [which is for Application]. By using DB the user name and the password, people who know the mysql are opening the DB using some GUI tools. How this can be avoided; because it is major issue right now in my work place. Please Can any one can help me? Thank you VIKRAM A 1.) Use MySQL connection limits to restrict the ways a client may impact performance. In the longer term look to limit table access with stored procedures or (when efficient) views. 2.) Restrict the database usernames/passwords by IP address and/or SSL client certificates and restrict access to the authorised client machines from the people causing a problem. 3.) If the clients and their credentials can't be restricted from the problem group, use MySQL proxy or its equivalent to filter exactly which queries may be applied so that only the actions already taken by the application may be performed by people using it's login credentials. 4.) If the problem is being caused by people on the authorised clients performing reasonable actions for those clients, then your problem can't be solved techincally aside from by seperating the application authentication credentails from the MySQL ones, or by scaling to allow the clients usage levels. Like Johan De Meersman I think the real problem you have is probably that the application uses MySQL access credentials as enduser credentials. Per-application user database users are unusual in my industry. If you need per-user access right granularity in database access it should still be disconnected from application login credentials. I'd probably do: field | application | mysql === username | current username | current username password | current password stored as hash | unique password stored encrypted by hash of (current password + salt) In this way only an application working on the user's behalf to which the user had submitted their password would be able to obtain their unique database password but wouldn't need to store the plaintext password in the user's session. HTH Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vikkiatb...@yahoo.in
Re: My sql Security
Sir, I will drop the authentication part. If stored in the code, the db password is subject to change when needed. In this case i can not change my part of the code. So I will go for the config file for the credentials. Thank you for the solutions/suggestions. Vikram From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Wed, 28 April, 2010 9:55:38 PM Subject: Re: My sql Security Rip out the DB authentication part, and store those credentials in-code, in some config file or the registry, or some remote mechanism like LDAP. If your users need to access multiple servers, just give them an option for each server, but don't let them enter DB credentials themselves. Users are not to be trusted with direct data access; they're way too devious for their own good :-) On Wed, Apr 28, 2010 at 11:05 AM, Vikram A vikkiatb...@yahoo.in wrote: Sir, Yes; As per your mail, i understood that the authentication must be separated for both app and the db. Let me send my login Authentication screen; I request you guide me how can handle this. Thank you Vikram -- *From:* Johan De Meersman vegiv...@tuxera.be *To:* Vikram A vikkiatb...@yahoo.in *Cc:* MY SQL Mailing list mysql@lists.mysql.com *Sent:* Wed, 28 April, 2010 2:10:45 PM *Subject:* Re: My sql Security I'm afraid you can't discern between clients and applications on the MySQL level. Your application authentication should be separate from the MySQL one. On Wed, Apr 28, 2010 at 10:28 AM, Vikram A vikkiatb...@yahoo.in wrote: Hi all, I have some security issues. I would like to have your suggestions/solutions. I have winserver2003 with mysql 5.1.45. We have client serve application that allows multi-login system with various people. I am getting user name, password for database login when the try to use login [which is for Application]. By using DB the user name and the password, people who know the mysql are opening the DB using some GUI tools. How this can be avoided; because it is major issue right now in my work place. Please Can any one can help me? Thank you VIKRAM A -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Fw: BIN LOG Error when use Begin Trans in Replication.
Hi Max Bube, The following are the variables related to the binlog. mysqlshow variables varibale_name : Value . . . binlog_cache_size : 32768 binlog_direct_non_transactional_updates : OFF binlog_format : STATEMENT . . . By default I found the statement based[it is better than row based?] format for bin log. I left it as such. I hope it is enough. As you said, in the my.ini file which part i suppose to add this binlog-format = statement ? Thank you Vikram From: Max Bube maxb...@gmail.com To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Thu, 22 April, 2010 2:17:29 AM Subject: Re: Fw: BIN LOG Error when use Begin Trans in Replication. Hi Vikram, Add binlog_format=row to your my.ini master's conf file 2010/4/21 Vikram A vikkiatb...@yahoo.in Hi Hao Ding, I attached in my request mail it self. Please find the attachment. Thank you From: hao ding fire9di...@gmail.com To: Vikram A vikkiatb...@yahoo.in Sent: Wed, 21 April, 2010 7:25:05 PM Subject: Re: BIN LOG Error when use Begin Trans in Replication. Vikram, I don't find my.ini. On Wed, Apr 21, 2010 at 9:19 PM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I have installed mysql 5.1.45 version on windows server 2003[standard edition] and windows xp. I am using WIN SERVER as MASTER and WIN XP as slave. And the default engine in both severs is INNODB. I am using visual basic for my front end. The replication system is running quite well. When i am using the rs.BeginTrans I am facing the following error when i am pointing my master, [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' If i point my slave as server[I have done for checking beginTrans]; it is running quite well. Can I have help from any one. It will be useful to me. I am attaching the my.ini for your reference. Thank you Vikram A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
Re: Fw: BIN LOG Error when use Begin Trans in Replication.
hi, I tried with ROW and MIXED tyoe it is working fine. When i go for statement based, it is causing the error. mysql SET GLOBAL binlog_format = 'STATEMENT'; mysql SET GLOBAL binlog_format = 'ROW'; mysql SET GLOBAL binlog_format = 'MIXED'; can you suggest Which is the best format? Thank you VIKRAM From: Max Bube maxb...@gmail.com To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Thu, 22 April, 2010 2:17:29 AM Subject: Re: Fw: BIN LOG Error when use Begin Trans in Replication. Hi Vikram, Add binlog_format=row to your my.ini master's conf file 2010/4/21 Vikram A vikkiatb...@yahoo.in Hi Hao Ding, I attached in my request mail it self. Please find the attachment. Thank you From: hao ding fire9di...@gmail.com To: Vikram A vikkiatb...@yahoo.in Sent: Wed, 21 April, 2010 7:25:05 PM Subject: Re: BIN LOG Error when use Begin Trans in Replication. Vikram, I don't find my.ini. On Wed, Apr 21, 2010 at 9:19 PM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I have installed mysql 5.1.45 version on windows server 2003[standard edition] and windows xp. I am using WIN SERVER as MASTER and WIN XP as slave. And the default engine in both severs is INNODB. I am using visual basic for my front end. The replication system is running quite well. When i am using the rs.BeginTrans I am facing the following error when i am pointing my master, [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' If i point my slave as server[I have done for checking beginTrans]; it is running quite well. Can I have help from any one. It will be useful to me. I am attaching the my.ini for your reference. Thank you Vikram A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
BIN LOG Error when use Begin Trans in Replication.
Hi, I have installed mysql 5.1.45 version on windows server 2003[standard edition] and windows xp. I am using WIN SERVER as MASTER and WIN XP as slave.And the default engine in both severs is INNODB. I am using visual basic for my front end. The replication system is running quite well. When i am using the rs.BeginTrans I am facing the following error when i am pointing my master, [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' If i point my slave as server[I have done for checking beginTrans]; it is running quite well. Can I have help from any one. It will be useful to me. I am attaching the my.ini for your reference. Thank you Vikram A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fw: BIN LOG Error when use Begin Trans in Replication.
Hi Hao Ding, I attached in my request mail it self. Please find the attachment. Thank you From: hao ding fire9di...@gmail.com To: Vikram A vikkiatb...@yahoo.in Sent: Wed, 21 April, 2010 7:25:05 PM Subject: Re: BIN LOG Error when use Begin Trans in Replication. Vikram, I don't find my.ini. On Wed, Apr 21, 2010 at 9:19 PM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I have installed mysql 5.1.45 version on windows server 2003[standard edition] and windows xp. I am using WIN SERVER as MASTER and WIN XP as slave. And the default engine in both severs is INNODB. I am using visual basic for my front end. The replication system is running quite well. When i am using the rs.BeginTrans I am facing the following error when i am pointing my master, [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' If i point my slave as server[I have done for checking beginTrans]; it is running quite well. Can I have help from any one. It will be useful to me. I am attaching the my.ini for your reference. Thank you Vikram A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql - Tables Export to Excel!
Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? Please guide me. Thank you VIKRAM A
Replication - LINUX to WIN
Hi, I have done replication with Win to Win servers with mysql version 5.0.41-community-nt. Now, We have Enterprise Linux(Red Hat 4.1.2-14) and windows server2003. Is it possible do the replication LINUX(Master) to WINDOWS SERVER2003(Slave) ? It will be great help to me. Thank you. VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: Replication - LINUX to WIN
Hello Ian Simpson, Thank you for reply. We defined table names, fields in lower cases except the KEYS . I hope the Key will not taken into the account[all in upper case]. Regarding the version we will keep same version as you said. Thank you --- On Thu, 18/3/10, Ian Simpson i...@it.myjobgroup.co.uk wrote: From: Ian Simpson i...@it.myjobgroup.co.uk Subject: Re: Replication - LINUX to WIN To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Date: Thursday, 18 March, 2010, 9:33 AM Hi Vikram, There are a couple of potential problems with Linux to Windows replication, but they are easily avoidable. 1) Table names: There are case-sensitivity differences between Linux and Windows: Linux is case-sensitive as regards table names, whereas Windows is not. Given that you are replicating from a Linux master to a Windows slave, you shouldn't hit too many problems with this, but if you ever reverse the order for some reason there could be problems. 2) MySQL versions: try to make sure that you always use the same version of MySQL on client and slave (this is generally good replication advice in all cases). There have been some bugs in replication that cause problems when the master and slave are on different versions. On Thu, 2010-03-18 at 14:03 +0530, Vikram A wrote: Hi, I have done replication with Win to Win servers with mysql version 5.0.41-community-nt. Now, We have Enterprise Linux(Red Hat 4.1.2-14) and windows server2003. Is it possible do the replication LINUX(Master) to WINDOWS SERVER2003(Slave) ? It will be great help to me. Thank you. VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Ian Simpson System Administrator MyJobGroup The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Pb: auto_increment - insert zero value
Hi, I have problem in the insertion of 0 in auto_increment. I have set in my.ini file as follows, sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO Even now the auto increment filed is not allows to insert a zero; If i insert zero, 1 is getting inserted. I have attached my.ini file for your reference. Please help me. Thank you VIKRAM Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Remove - Foreign key constraint in innodb
Hi, I tried to remove foreign key constraint in innodb table. I tried with different ways; but i am unable to drop the constraints. http://lists.mysql.com/mysql/113053 It says that, droping the foreign key constraint it is not possible in innodb engine. Is it so? or any other possibilities? I am using mysql 5.1.32 Please, Can any one you help me? Thank you VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: how things get messed up
Sirs, Because one table will hold the large amount of data, only the recent data will be used for transactions; so rest of the old records are remain same with out any transaction. So we have decided to go for year based storage; here even old records can be taken out by join queries. I hope you experts will agree with this. Or your comments and suggestions are welcome for the better design. Thank you VIKRAM A From: Jerry Schwartz jschwa...@the-infoshop.com To: Vikram A vikkiatb...@yahoo.in Cc: Johan De Meersman vegiv...@tuxera.be; MY SQL Mailing list mysql@lists.mysql.com Sent: Tue, 23 February, 2010 3:53:38 AM Subject: RE: how things get messed up I thought I had replied publicly to Johan’s suggestion, with some personal experience. He’s absolutely right, that would give you a solution that would be completely transparent to your application and therefore much easier to implement. You could keep re-arranging your partitions as necessary. I, myself, have never used portioning so I hope someone with experience will chime in here. One disadvantage is that all of your data would be in one database, making your backups bigger and bigger. If you used a separate database as an archive, the archive database wouldn’t have to be backed up very often. I never did get a good feel for how big your database will be. Even if you’re talking about IIT, and assuming 10 students, 6 classes per semester, three semesters, per year, 20 years of history, you’re going to have 36 million class records. I think there are much bigger databases running quite well. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com From:Vikram A [mailto:vikkiatb...@yahoo.in] Sent: Friday, February 19, 2010 11:17 PM To: Jerry Schwartz Cc: Johan De Meersman Subject: Re: how things get messed up Dear Sir, I agree with the solution proposed. But one of the member[Johan De Meersmanvegiv...@tuxera.be] of this list has commented it. Do you have any opposition/Suggestions? Thank you VIKRAM A From:Jerry Schwartz jschwa...@the-infoshop.com To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Thu, 18 February, 2010 9:54:57 PM Subject: RE: how things get messed up From:Vikram A [mailto:vikkiatb...@yahoo.in] Sent: Wednesday, February 17, 2010 11:41 PM To: Jerry Schwartz Cc: MY SQL Mailing list Subject: Re: how things get messed up Dear Jerry Schwartz We have applications for colleges in India. The same idea of having single table for manipulating students records. but we are not following archiving concept. Ex stupersonal. and stuclass these tables are playing wide role in our application. After 7 years now there are 9000 records[postgresql backend] are there in the table. Because of this the entire application [ Fees, attendance, exams etc] performance is getting down. For the remedy of this I proposed this year wise architecture for our new version [mysql]. [JS] You have 9000 records? That should not slow down any application. I must not understand you. I have problem in year wise also, i have number of mutual related tables for students such as stu_last_studies, stu_family_details, stu_address, stu_extracurri and so on. If i go for year basisis i have to make all the above tables also year basis. Hence, I feel it difficult have such number of tables after few years. [JS] I did not mean that you should have tables for each year. I was suggesting that you have tables for recent data and tables for archived data. As you said the archive system, can you the idea about the archive system[If needed i will give the table structures]. [JS] This is best described with a picture. Here is a small example of what I meant: `student_master_table` (all years) /\ / \ `grades_current` `grades_archive` | / `class_master_table` The structures of the two grades tables should be almost the same, something like grade_id autoincrement in grades_current only student_id index class_id index class_start_date grade_received You would add new grade records to the `grades_current` table. Now, suppose that you don’t usually need data more than five years old. Once a year you would run these queries: INSERT INTO `grades_archive` SELECT * FROM `grades_current` WHERE `class_start_date` YEAR(DATE_SUB(NOW(), INTERVAL 4 YEAR)); DELETE FROM `grades_current` WHERE `class_start_date` YEAR(DATE_SUB(NOW(), INTERVAL 4 YEAR)); That would keep the `grades_current` table small. If you want to find a student’s recent grade history, you would use a query like SELECT * FROM `grades_current` WHERE `student_id` = 12345; If you decide that you need a student’s complete history, you could do SELECT * FROM `grades_current` WHERE
set max_allowed_packet
Hi experts, When I try to set the packet size to some other value, it is not getting updated. show variables; set max_allowed_packet = 2097152; set global max_allowed_packet = 2097152; it resulting, Query is ok, 0 rows afected (0.00 sec) Can you suggest me how set this value to higher one. By default it is 1 MB. Thank you VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: how things get messed up
Dear Jerry Schwartz We have applications for colleges in India. The same idea of having single table for manipulating students records. but we are not following archiving concept. Ex stupersonal. and stuclass these tables are playing wide role in our application. After 7 years now there are 9000 records[postgresql backend] are there in the table. Because of this the entire application [ Fees, attendance, exams etc] performance is getting down. For the remedy of this I proposed this year wise architecture for our new version [mysql]. I have problem in year wise also, i have number of mutual related tables for students such as stu_last_studies, stu_family_details, stu_address, stu_extracurri and so on. If i go for year basisis i have to make all the above tables also year basis. Hence, I feel it difficult have such number of tables after few years. As you said the archive system, can you the idea about the archive system[If needed i will give the table structures]. It will be grate help to me. Thank you VIKRAM A From: Jerry Schwartz jschwa...@the-infoshop.com To: Vikram A vikkiatb...@yahoo.in; Johan De Meersman vegiv...@tuxera.be Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Tue, 16 February, 2010 9:32:22 PM Subject: RE: how things get messed up -Original Message- From: Vikram A [mailto:vikkiatb...@yahoo.in] Sent: Friday, February 12, 2010 4:13 AM To: Johan De Meersman Cc: MY SQL Mailing list Subject: Re: how things get messed up Sir, Thanks for your suggestion, I will go for blob storage, because our application will maintain the data on yearly basis[stupersonal2008, stupersonal2009 etc.]. So i feel we may not face such kind of performance issue in our application. [JS] It sounds like you are planning to have one table per year. Regardless of where you put your blobs, I think that is a bad idea from a design standpoint. It will make it harder to find historical information. If your database is relatively small, then I'd just keep everything in one table. If it is big, then roll data that is five years old into an archive table. That will give you only two places, and an easy-to-follow rule to tell you where to look. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vikkiatb...@yahoo.in The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
My SQl Master -Slave Setup
Dear Sirs, I would like to have the MYSQL master and slave on my application setup. I am using windows vista and mysql 5.1.x what are the needed things for this master salve setup? So that, I can proceed further with manuals available. Thank you VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: MySQL application development LIBS
Hi Miguel Cardenas! For you second question I can describe my experiance, If your OS is windows vista, this may be applied. I too had installed the MYSQL 5.X at my system, for making master-slave setup, i happened to uninstall the current setup. I tried the same version immediately, it was not successful. I tried deleting the folders related to MYSQL, all registry values. Even it was not sucessful[installation is done but the service start up was failure]. Then I found myself, I seared the MYSQL term where ever it is presented. I found there is another place where the MYSQL data folder has kept. C:\ProgramData\MYSQL - this i have removed after another uninstallation. Then I tried once again the service has started successfully. Thank you I hope it ll help you VIKRAM A From: Miguel Cardenas renit...@gmail.com To: Mysql mysql@lists.mysql.com Sent: Sun, 14 February, 2010 6:52:48 AM Subject: MySQL application development LIBS Hello I have a small Windows application linked to the MySQL development libraries and have to run in on another computer, do I have to copy the DLL/LIB files or are they already static linked to the application? The other computer has no MySQL installed, and the application will run only as client to access my web server to perform some simple tasks. I have the Win32 binary .ZIP (not compiled)... so it has the already built libraries as they came in the package... And a second question... currently I have MySQL 6.0 on my laptop, since it is deprecated, how can I uninstall version 6 and then install latest 5.x? Some day in the past I tried simply to uninstall and reinstall another version, but the previous files didn't get removed and was unable to make it work again since the new installation failed due the older remaining files... what is the correct way to completely remove MySQL to install a different and clean version? Note that in the past when I tried the new installation it aborted, or it succeeded but never could install it as service again and had to reinstall the wole system to make a brand new installation... Thanks for your help!!! Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
Re: how things get messed up
Sir, I am in the situation to storing student and staff images. every year 2000 new photos has to be added in our application. Can i have your suggestion, which is the best one, storing as a blob Or using NFS? It will be great help to me, because such experts are sharing your own experience on this binary storage issue. Thank you. VIKRAM A From: Johan De Meersman vegiv...@tuxera.be To: Martijn Tonies m.ton...@upscene.com Cc: mysql@lists.mysql.com Sent: Fri, 12 February, 2010 1:09:32 PM Subject: Re: how things get messed up On Fri, Feb 12, 2010 at 8:27 AM, Martijn Tonies m.ton...@upscene.comwrote: Sounds logical, what's also nice to see, is that even though people here tend to say don't put binaries in the database, apparently Facebook thought it would be nice to do so (for all sorts of reasons) and even took the time to write their own blob storage mechanism ;-) The whole point is that they *aren't*' putting blobs in their database - that has way too much overhead. They're using a custom service that does nothing but read from byte X to byte Y. No concepts of tablespaces, integrity, indices, whatever. The only thing they store in their database, is the start- and end-byte of each image. I doubt they even took it as far as to write a plugin engine - that would again bring too much overhead. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
Re: how things get messed up
Sir, Thanks for your suggestion, I will go for blob storage, because our application will maintain the data on yearly basis[stupersonal2008, stupersonal2009 etc.]. So i feel we may not face such kind of performance issue in our application. Thank you VIKRAM A From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Fri, 12 February, 2010 2:23:01 PM Subject: Re: how things get messed up On Fri, Feb 12, 2010 at 9:19 AM, Vikram A vikkiatb...@yahoo.in wrote: I am in the situation to storing student and staff images. every year 2000 new photos has to be added in our application. Can i have your suggestion, which is the best one, storing as a blob Or using NFS? It will be great help to me, because such experts are sharing your own experience on this binary storage issue. I never said you had to grovel, though :-p This whole thread has been a discussion of just that. My personal opinion is that it's better to store binary objects (like images) out-of-band, for instance on an NFS system like you suggest. Other people on the list have made their own arguments for BLOB storage. In the end, it's down to your own situation and decisions, but I will keep defending the position that filesystems are made for storing files, and databases are made for storing data - it saves you on both database and PHP requests, as (from a web point of view) you can't return the image data inside your HTML - it requires a second HTTP call. Filesystem image serving, however, could perfectly be offloaded to a subdomain that runs a lightweight, threaded HTTP server that need not run the heavy PHP processes. You could even run that on your NFS server, if you want. If you do go for BLOBs, though, for god's sake keep them in a separate table, lest you fragment your datafiles. Split records are a disaster for performance. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
BLOB Filed - Image Store
Hi, As stated http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html I have stored image using the following. CREATE TABLE files( file_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, file_name VARCHAR(64) NOT NULL, file_size MEDIUMINT UNSIGNED NOT NULL, file MEDIUMBLOB NOT NULL); I can store and i can retrieve the image to local machine as a file. I NEED TO SHOW ALL THE FIELDS IN THE CRYSTAL REPORT. Thank you VIKRAM A Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
MyISAM to InnoDB
Hi, What is the basic functionality of the MyISAM, InnoDB etc ? Vikram A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: BLOB Filed - Image Store [THANKS]
Dear Sir, Thank you for advising me generally, how to approach/in what cause we need to approach! I will follow your advise very strictly here after. I will try to find out solutions my self first and if not, i ll write to the experts. Thank you so much for making me to understand the helping time of the experts. Sorry for wasting such experts time by posting simple queries. Thank you VIKRAM A From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Sent: Wed, 10 February, 2010 7:22:18 PM Subject: Re: BLOB Filed - Image Store On Wed, Feb 10, 2010 at 2:08 PM, Vikram A vikkiatb...@yahoo.in wrote: Sir, I am new to MY SQL, that's why i am expecting help from the experts. Yes, that much was clear. So far, I've seen you post questions about Visual Basic and Crystal Reports, on a MySQL mailinglist. Wat makes you think this is the right place ? Also, albeit slightly unrelated, do you have other masochistic tendencies ? As a rule of thumb, people are generally happy to help you with problems that you've researched and can't find the solution to. On the other hand, repeated questions that could easily have been avoided by reading the fine manual, tend to be looked upon with slightly less enthusiasm. Google is everyone's best friend, and the very extensive MySQL documentation is a very close second if you want to learn about it. You don't have to be an expert to ask questions, but you're more likely to get an answer if you: 1. are nice and polite, 2. frame your question with sufficient detail and background so that we get a good idea what you're trying to do and what's going wrong, and 3. have done your homework before coming here.People here are voluntarily spending some of their usually sparse free time to help others. Wasting that time with questions that are either irrelevant or could have been solved with a quick google is, frankly, rather insulting. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Image - Crystal Report - VB6.0
Hi experts, I have blob filed, which contains the image. I am using BV6.0 as my front end application. And i have some pre-defined [defined by me] .rpt file. Along with the details, i would like to add the photo on the report. Can you help how to do this? Thank you Regards, VIKRAM A Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
Re: MY SQL Slave Server
I tried to install once again mysql at vista? but deducts the previous installation. and it is ask for the modify , repire and remove options You said that we can install any number of setups in a same system. Is there any other way to install? Please help me thank you From: Thiyaghu CK theyaho...@gmail.com To: Vikram A vikkiatb...@yahoo.in Sent: Sat, 6 February, 2010 3:05:25 PM Subject: Re: MY SQL Slave Server Hi Vikram, Ya sure, slave can be in vista. Yes, you can install 2 or more setups in same system but port has to be different, and if its linux platform change the sock name too. Regards, Thiyaghu CK www.mafiree.com On Sat, Feb 6, 2010 at 2:09 PM, Vikram A vikkiatb...@yahoo.in wrote: Dear Thiyagu! Thank you for the information. I will do the experiment. and let you know the further details. Is it possible making slave at windows vista? As per your information, th emysql has to be installed once again another port; Is it possible to install 2 setups in a same system? Thank you VIKRAM A From: Thiyaghu CK theyaho...@gmail.com To: Vikram A vikkiatb...@yahoo.in Cc: mysql@lists.mysql.com Sent: Sat, 6 February, 2010 1:03:34 PM Subject: Re: MY SQL Slave Server Hi Vikram, So as my understanding you need to have a master slave setup in a single machine(FEDORA 11). 1. Make the already running mysql instance as Master 2. Install a new mysql in the same machine in different port which will be your slave 3. Replicate 4.You can also add more slave in same machine or in different machine and make more copy. Note: Slave should be of same version or higher. For replication steps you can refer http://www.mafiree.com/docs.html or http://www.howtoforge.com/mysql_database_replication Let me know for more details. Regards, Thiyaghu CK www.mafiree.com On Sat, Feb 6, 2010 at 12:33 PM, Vikram A vikkiatb...@yahoo.in wrote: Dear Experts, I would like to configure the slave for my main server. My server is running in the FEDORA 11. I would like to make another mirror of the DB in the same server/ The copy of the DB can be kept at win server / another Fedora server. Can you suggest how to do the above? Thank you VIKKI A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: max() can't work
hi It is not working, select * from table_name group by movid having max(movid) but it is working fine select * from table_name order by movid desc limit 1 From: Roland Kaber roland.ka...@education.lu To: armando armand...@gmail.com Cc: tech list bluetm...@gmail.com; mysql@lists.mysql.com Sent: Sat, 6 February, 2010 8:28:06 PM Subject: Re: max() can't work The max() function is an aggregate function which can be used in conjunction with GROUP BY in the SELECT or HAVING clause: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html . This code should work: select * from table_name group by movid having max(movid). However, there is a simpler and more efficient solution: select * from table_name order by movid desc limit 1. I hope this helps. Best regards Roland Kaber armando wrote: the field movid is type integer or varchar ? 2010/2/6 tech list bluetm...@gmail.com select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=armand...@gmail.com The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
MY SQL Slave Server
Dear Experts, I would like to configure the slave for my main server. My server is running in the FEDORA 11. I would like to make another mirror of the DB in the same server/ The copy of the DB can be kept at win server / another Fedora server. Can you suggest how to do the above? Thank you VIKKI A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Image Store @ My SQL
Hi, I am using MYSQL with VB 6.0. We have fedora11 my sql (version 5.1.32). We have an client server application, server is located at another end of an intranet and the nodes are connected from different ends of intranet. In this modal, I need to store image of the student from one node that has to be accessed / Changed / by other nodes. If I have only one system I can store the path of the image. But it is a remote server, i can not store the PATH simply. Because of this i would like to go for an another modal of storing image itself into the DB I would like to know how VB 6.0 will handle this modal. Please give your valuable input on this issue. Thank you VIKKI A Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
Re: Image Store @ My SQL
Dear Walter Heck, I am happy to receive immediate response from you. I am not clear with your answer, can you please explain it? Thank you From: Walter Heck wal...@openquery.com To: Vikram A vikkiatb...@yahoo.in Cc: mysql@lists.mysql.com Sent: Sat, 30 January, 2010 2:29:43 PM Subject: Re: Image Store @ My SQL Bad idea. Sotring images is what a file system is for. Create an NFS or other networked storage and store your images there. I don't see the problem? good luck, Walter Heck Engineer @ Open Query (http://openquery.com) On Sat, Jan 30, 2010 at 18:54, Vikram A vikkiatb...@yahoo.in wrote: Hi, I am using MYSQL with VB 6.0. We have fedora11 my sql (version 5.1.32). We have an client server application, server is located at another end of an intranet and the nodes are connected from different ends of intranet. In this modal, I need to store image of the student from one node that has to be accessed / Changed / by other nodes. If I have only one system I can store the path of the image. But it is a remote server, i can not store the PATH simply. Because of this i would like to go for an another modal of storing image itself into the DB I would like to know how VB 6.0 will handle this modal. Please give your valuable input on this issue. Thank you VIKKI A Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/ Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
Re: Image Store @ My SQL
Dear Sir, Let me explain what i understood, 1. I have to create a shareable folder among the network. 2. The image files has to be kept at share machine 3. Then the path (share machine) of the file has to be stored in the DB 4. When we want it has to be taken from the share Is it So? then i may have the following, when ever i want the image of a i need look for the share machine! If it is not available it may cause some difficulties. Please share your knowledge Thank you From: Walter Heck wal...@openquery.com To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Sat, 30 January, 2010 3:16:11 PM Subject: Re: Image Store @ My SQL On Sat, Jan 30, 2010 at 19:03, Vikram A vikkiatb...@yahoo.in wrote: I am not clear with your answer, can you please explain it? Well, MySQL (as most databases) is not really suited for storing images. If you create a networked storage that you can share between both locations, that would solve your problem, right? cheers, Walter Heck Engineer @ Open Query (http://openquery.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vikkiatb...@yahoo.in The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
IMAGE STORE @ FEDORA 11
Dear One, I found nice solution on the following citation http://dev.mysql.com/tech-resources/articles/vb-cursors-and-locks.html It was really helpful to me. Thank you VIKKI A Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
Fw: IMAGE STORE @ FEDORA 11
sorry the link i given is wrong. Please follow this http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html - Forwarded Message From: Vikram A vikkiatb...@yahoo.in To: MY SQL Mailing list mysql@lists.mysql.com Sent: Sat, 30 January, 2010 7:04:01 PM Subject: IMAGE STORE @ FEDORA 11 Dear One, I found nice solution on the following citation http://dev.mysql.com/tech-resources/articles/vb-cursors-and-locks.html It was really helpful to me. Thank you VIKKI A Your Mail works best with the New Yahoo Optimized IE8. Get it NOW!. Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
I would like to post on lists.mysql.com
Dear Admin, I would like to share and get inputs from experts on MYSQL Db. I request you to grant access to me. Thank you Regards, Vikki A Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
Question on replication terminology
Hi I'm new to replication and looking through some docs on how to use it. Could someone please tell me if the following terms mean the same thing or, if not, what is the difference: master-master replication dual-master replication bidirectional replication TIA -BT
Help with formatting of 1:n relationship
Hello all I have a database with a 1:n relationship as follows: Item (1) fld1 fld2 ... Item_info (n) - fld1 fld2 ... My client wants me to export these linked records into an Excel file in the following format. item.fld1 item.fld2 ... item_info.fld1 item_info.fld2 ... item_info.fld1 item_info.fld2 .. ie. each item and all the records that make up its info together in a single Excel row. I'm using an excel library that accepts a SELECT as input and generates an XLS file with the records as output. I'd like to use this where possible. However I don't know if it's possible to write a SELECT that compresses a 1:n relationship into a single row. Is this possible, and if yes, could someone show me how? Or could you suggest another way in which I could achieve the above required output? Thanks, Vikram -- Operator: So what do you need? Besides a miracle. Neo: Guns. Lots of guns. -- The Matrix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with formatting of 1:n relationship
Hi Jerry Thanks for your input on this! I'm using an excel library that accepts a SELECT as input and generates an XLS file with the records as output. I'd like to use this where possible. However I don't know if it's possible to write a SELECT that compresses a 1:n relationship into a single row. Is this possible, and if yes, could someone show me how? Or could you suggest another way in which I could achieve the above required output? Does whatever tool you are using have any place where you can manipulate the data between the SELECT and the creation of the XLS? If not,I think you need a user-defined function for this, or perhaps you can do it with a user-defined procedure. Unfortunately the tool doesn't let me manipulate the data. It simply reads the result set and pops each field into a separate column in the XLS. So any formatting I do has to be part of the SELECT. I did consider a procedure but the problem is that the client is still using MySQL 4.x, which afaik doesn't support stored procedures. An upgrade is not something they can do at this point, as they're using s shared host so the server isn't really under their control. Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with formatting of 1:n relationship
Hi, I did consider a procedure but the problem is that the client is still using MySQL 4.x, which afaik doesn't support stored procedures. An upgrade is not something they can do at this point, as they're using s shared host so the server isn't really under their control. I think what you're really looking for is a pivot table or crosstab report in SQL itself, right? I have used pivot tables before but I'm far from an expert on them. I wasn't able to understand how I could apply a pivot table structure to this problem. Can you give me some more details on what you had in mind? Thanks, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Tips for optimizing stored procedures
Hi all I am new to stored procedures and have just started using them. Is there any Web site you could suggest which discusses common optimization tips for MySQL SPs? Vikram -- Operator: So what do you need? Besides a miracle. Neo: Guns. Lots of guns. -- The Matrix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question on default database for stored functions
Hi According to the MySQL manual, By default, a routine is associated with the default database.When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates) However, when I tried accessing a stored routine from another database, I received an error. Output below: mysql USE test mysql DELIMITER // mysql CREATE FUNCTION get_area(radius INT) - RETURNS FLOAT - BEGIN - RETURN PI() * radius * radius; - END - // Query OK, 0 rows affected (0.13 sec) mysql DELIMITER ; mysql USE test2 Database changed mysql select get_area(11); ERROR 1305 (42000): FUNCTION test2.get_area does not exist Can someone tell me what I'm doing wrong? Thanks. Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question on returning multiple rows from a stored procedure into a session variable
Hi Is there a way to write a stored procedure that returns a result set containing multiple rows? More specifically, I'm trying to return the multi-row result set as an OUT parameter, which I can then access via a session variable. If this is possible, could someone direct me to the appropriate documentation or post an example. Thanks. Vikram -- Operator: So what do you need? Besides a miracle. Neo: Guns. Lots of guns. -- The Matrix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Need help with permissions between host, db and user tables
Hi,
I'm trying to customize permissions for a particular database and have
run into an odd problem. I'm hoping someone on this list can suggest
what I'm doing wrong. The setup is as follows:
Database: property
User '[EMAIL PROTECTED]' should be able to run SELECTs on this database
User '[EMAIL PROTECTED]' should be able to run SELECTs and INSERTs
on this database
I read in the MySQL manual that between the 'db' and 'host' tables, if
the 'db.host' field is empty, MySQL will look up the 'host' table for a
list of allowed hosts and set privileges as the intersection of the two
tables. http://dev.mysql.com/doc/refman/5.1/en/request-access.html
So I ran the following queries:
insert into user (host, user, password) values ('%.example.com', 'bill',
'');
insert into db (host, db, user, select_priv, insert_priv) values ('',
'property', 'bill', 'Y', 'Y');
insert into host (host, db, select_priv, insert_priv) values
('home.example.com', 'property', 'y', 'n');
insert into host (host, db, select_priv, insert_priv) values
('office.example.com', 'property', 'y', 'y');
flush privileges;
Per my understanding of what the manual page says, this should
accomplish what I am trying to do. However, when I log in using the
specified user/host combination (from either host), I cannot see the
'property' database. Also, SHOW GRANTS does not display the privileges
set above.
When I do the same thing using the GRANT command, it works. On
investigation, I see this is because it is inserting a separate record
for each host into the 'db' table, and not writing anything to the
'host' table.
So my question is: why does the 'if empty db.host field/look up in host
table' method suggested in the manual not work? What step am I missing?
Thanks in advance for your help. I'd appreciate it if you could reply to
me directly as well as the list.
Vikram
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help needed with MySQL C API-based client (segfault)
Hello,
I need to write a simple C client for a project. I am using the MySQL C
API. Attached is the code. It occassionally segfaults with no visible
pattern. Could someone help me figure out why? Or any other comments on the
code to help me make it better?
/* client.c */
#include stdio.h
#include mysql.h
int main()
{
/* declare
structures and variables */
char query[255];
int i, j, count;
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD
*field;
/* initialize MYSQL structure */
mysql_init(mysql);
/* connect to database */
if
(!(mysql_real_connect(mysql, NULL, root, , db1, 0, NULL, 0)))
{
fprintf(stderr, Error in connection: %s\n,
mysql_error(mysql));
}
for( ;; )
{
printf(query? );
gets(query);
if (strcmp(query,exit)
== 0)
{
break;
}
/* execute query
*/
/* if error, display error message */
/* else check the type of
query and handle appropriately */
if (mysql_query(mysql, query) != 0)
{
fprintf(stderr, Error in query: %s\n, mysql_error(mysql));
}
else
{
if (result = mysql_store_result(mysql))
{
/* SELECT
query */
/* retrieve result set */
int numRecords =
mysql_num_rows(result);
int numFields = mysql_num_fields(result);
for (i = 0; i numRecords; i++)
{
row =
mysql_fetch_row(result);
for (j = 0; j numFields; j++)
{
//field= mysql_fetch_field(result);
fprintf(stdout, %s, row[j]);
j != (numFields-1) ? printf(, ) : printf(\n);
}
}
fprintf(stdout, ** Query successful, %d rows retrieved **\n,
numRecords);
}
else
{
if (mysql_field_count(mysql) == 0)
{
/* non-SELECT query */
fprintf(stdout, ** Query successful, %d
rows affected **\n, mysql_affected_rows(mysql));
}
else
{
fprintf(stderr, Error in reading result set: %s\n,
mysql_error(mysql));
}
}
}
/* clean up */
mysql_free_result(result);
}
mysql_close(mysql);
}
--
I wouldn't recommend sex, drugs, and insanity for everyone, but it works
for me.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using a foreign character set in MYSQL
Hello all. I am working with MySQL 4.0. I have a requirement to create a data-driven Web page to display Chinese text from a MySQL table. I'm completely new to this, can someone tell me exactly what I need to do to make this happen? 1. For example, how do I insert the Chinese text from my source (a Word doc) into a MySQL table without corrupting it? When I try copying and pasting it into the mysql client command-line, the data gets trashed. 2. Once it's in, how do I get it back out into my application without corrupting it? I'm using PHP 4.3 for the Web site. 3. If I need to make changes to the data from the command-line client, how can I do it, especially if the query involves using a Chinese-language string? For example, update langdata set menutitle='SOME_MENU_TITLE_IN_CHINESE' where menutitle='SOME_OLD_MENU_TITLE_IN_CHINESE' Looked at the online manual but am sorry to say it didn't really help much. I tried starting the server with --character-set=big5 but it didn't seem to make much difference... Thanks! Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using a foreign character set in MYSQL
When I try copying and pasting it into the mysql client command-line, the data gets trashed. Not necessarily. May just be that the command-line window doesn't know to display Chinese unless you tell it to. What OS are you working on? Working on Windoze with a telnet window open to a Linux box (which has the MySQL client/server) Thanks, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary/foreign keys and indexes
Hello all, I am working on a book on MySQL, and I need some assistance with deciding where some concepts should be dealt with. 1. I have dealt (briefly) with primary keys and indexes in a chapter called MySQL DML. I have not discussed foreign keys in this chapter. 2. I would like to discuss foreign keys and indexes in detail further down in the book. I am confused as to how to break up this information. Is it better to deal with primary keys, foreign keys and indexes all at once, in a single chapter, or break it up into two, one dealing with primary/foreign keys and the other with indexes. The issue is confounded further (at least for me) by the fact that mySQL seems to require an index on both primary and foreign keys, and the manual suggests KEY as a synonym for INDEX. Are the concepts of key and index distinct or not? Any thoughts would be most welcome :) Thanks, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with DELETE and a subquery
Hey, Thanks for the help. I dont think this is possible, because MySQL will not let you delete from the same table you are reading. Is there an alternative way to do this using a subquery, you think? ignore both previous posts. Both don't work as wanted. I just realized that and I will come back to you after I created the tables and made it sure. Sorry for the confusion. Best regards Nils Valentin Tokyo/Japan 2003年 7月 25日 金曜日 14:42、Nils Valentin さんは書きました: Hi Vikram, just read the post once more. I made a mistake. You want to delete the clients with no branches you said, so the command should look like mysql delete from clients where cid = (select clients.cid from clients left join branches using (cid) WHERE ISNULL(clients.cid); Note that cid itself is ambigous, because in both tables. Best regards Nils Valentin 2003年 7月 25日 金曜日 14:31、Nils Valentin さんは書きました: Hi Vikram, NULL is a special data type and requires special procedures. Try this: mysql delete from clients where cid = (select clients.cid from clients left join branches using (cid) WHERE ISNULL(bid); Please make NO SPACE betwen ISNULL and (bid) as otherwise wit will give you an syntax error. Hope that ends the problems you had. Best regards Nils Valentin Tokyo/Japan 2003年 7月 25日 金曜日 12:58、Vikram Vaswani さんは書きました: Hi all, I have the following two tables: mysql SELECT * FROM clients; +-+-+ | cid | cname | +-+-+ | 101 | JV Real Estate | | 102 | ABC Talent Agency | | 103 | DMW Trading | | 104 | Rabbit Foods Inc| | 110 | Sharp Eyes Detective Agency | +-+-+ 5 rows in set (0.00 sec) mysql SELECT * FROM branches; +--+-++--+ | bid | cid | bdesc | bloc | +--+-++--+ | 1011 | 101 | Corporate HQ | CA | | 1012 | 101 | Accounting Department | NY | | 1013 | 101 | Customer Grievances Department | KA | | 1041 | 104 | Branch Office (East) | MA | | 1042 | 104 | Branch Office (West) | CA | | 1101 | 110 | Head Office| CA | | 1031 | 103 | N Region HO| ME | | 1032 | 103 | NE Region HO | CT | | 1033 | 103 | NW Region HO | NY | +--+-++--+ 9 rows in set (0.01 sec) I need to delete all clients with no branches. I need to use a subquery to do this. Given these constraints, I came up with the following: mysql delete from clients where cid = (select clients.cid from clients left join branches using (cid) WHERE bid is null); MySQL says: ERROR 1093: You can't specify target table 'clients' for update in FROM clause Does any one know why I am getting this error (MySQL 4.1)? Can you help me rewrite this operation *using a subquery only*? TIA, Vikram -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with DELETE and a subquery
Hi all, I have the following two tables: mysql SELECT * FROM clients; +-+-+ | cid | cname | +-+-+ | 101 | JV Real Estate | | 102 | ABC Talent Agency | | 103 | DMW Trading | | 104 | Rabbit Foods Inc| | 110 | Sharp Eyes Detective Agency | +-+-+ 5 rows in set (0.00 sec) mysql SELECT * FROM branches; +--+-++--+ | bid | cid | bdesc | bloc | +--+-++--+ | 1011 | 101 | Corporate HQ | CA | | 1012 | 101 | Accounting Department | NY | | 1013 | 101 | Customer Grievances Department | KA | | 1041 | 104 | Branch Office (East) | MA | | 1042 | 104 | Branch Office (West) | CA | | 1101 | 110 | Head Office| CA | | 1031 | 103 | N Region HO| ME | | 1032 | 103 | NE Region HO | CT | | 1033 | 103 | NW Region HO | NY | +--+-++--+ 9 rows in set (0.01 sec) I need to delete all clients with no branches. I need to use a subquery to do this. Given these constraints, I came up with the following: mysql delete from clients where cid = (select clients.cid from clients left join branches using (cid) WHERE bid is null); MySQL says: ERROR 1093: You can't specify target table 'clients' for update in FROM clause Does any one know why I am getting this error (MySQL 4.1)? Can you help me rewrite this operation *using a subquery only*? TIA, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updating a record with MAX(value)
Hi all, Is there a way to update the record with maximum value in a table with another value? I am trying this: mysql update services set sfee = 1 where sfee = max(sfee); Query OK, 0 rows affected (0.06 sec) Rows matched: 0 Changed: 0 Warnings: 0 But it doesn't make any changes to the data. Any ideas why? TIA, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting columns into a single row
Hi guys, I need some help with this. I have the following 3 tables. +--+---+ | uid | name | +--+---+ | 100 | sue | | 102 | harry | | 104 | louis | | 107 | sam | | 110 | james | | 111 | mark | | 112 | rita | +--+---+ +--+---+ | gid | name | +--+---+ | 501 | authors | | 502 | actors| | 503 | musicians | | 504 | chefs | +--+---+ +--+--+ | uid | gid | +--+--+ | 11 | 502 | | 107 | 502 | | 100 | 503 | | 110 | 501 | | 112 | 501 | | 100 | 501 | | 102 | 501 | | 104 | 502 | | 100 | 502 | +--+--+ I'm looking for the following output: 1. Group members group name users authors sue,harry,james,mark actors ..., musicians ..., chefs NULL 2. User memberships user name groups user1 group1,group3, etc user 2 group2, group3, etc and so on Any idea how to do this? TIA, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on choosing a MySQL API
Hi, The MySQL API is available in a number of languages - PHP, Perl, C, Java, etc. I was wondering if anyone here had any thoughts on the decision criteria to be kept in mind when choosing which language to use when programming with the MySQL API? A brief list of these would be very helpful to me. Or if you could point me to a link, that would be great too! TIA, Vikram - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
C API
Hi, Does anyone know which version of MySQL first exposed an API for developers? Is there a changelog somewhere that might have this info? I'm interested in seeing how the API has evolved over time. TIA, Vikram -- Luke: I don't believe it! Yoda: That, is why you fail. --Star Wars: The Empire Strikes Back - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL book TOC
Jeremy - the first one ;) Any comments on the TOC? Vikram At 02:46 PM 2/8/03 -0800, Jeremy Zawodny wrote: On Fri, Feb 07, 2003 at 03:20:47PM +0500, Vikram Vaswani wrote: My name is Vikram Vaswani, and I'm currently working on the outline for a MySQL reference book. This is supposed to be a comprehensive reference to MySQL 4, covering all aspects of the software, including the new transaction/subquery features. Call me biased, but that and this... Since I'm not that experienced with MySQL Don't seem like the right combination. Did you mean not that experienced writing about MySQL? Or are you really new to MySQL and trying to write a book about it? Jeremy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL book TOC
Hi Nasser, Usage?? well whoever goes to buy your book, must have some ideas in head for the usage.. I don't think you should waste trees on telling people things that don't help them. One Question: What makes this book different from the MySQL manual? Well, it's not supposed to replace the manual by any means - it's just supposed to offer novice-intermediate users a starting point to MySQL usage Another Question: Who is your intended audience? Novice to intermediate users A suggestion: Think of substantial examples How about performance? and Performance tuning? I am planning to cover this, but in the latter sections of the book. Fundamentally, my problem is with the Section 2 - I am not sure what level of detail to go to in this section. Any assistance you or other list members could provide would be very helpful :) Thanks for your response, Vikram - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL book TOC
Hello all, My name is Vikram Vaswani, and I'm currently working on the outline for a MySQL reference book. This is supposed to be a comprehensive reference to MySQL 4, covering all aspects of the software, including the new transaction/subquery features. I have put together a draft table of contents (TOC), but am a little hesitant about some aspects of it. I have a nagging feeling that I'm missing out on a lot of things here, and also that it might bs structured better. Specifically, I was hoping that this list would help me in evaluating the structure and content of Section 2 below. Since I'm not that experienced with MySQL, I thought I'd open this up to the list and see if you guys could help me figure out what's bugging me :) I'd appreciate as much feedback as possible, since my aim here is to produce something that would hopefully be useful to you in your usage of MySQL (and also stand out from the crowd of other MySQL books in the market) Thanks! I look forward to hearing back. Vikram Vaswani -- TOC Section 1: Installation 1.Introduction to MySQL What is MySQL? History Features Future development 2. Installing Linux Windows Section 2: Usage 3.Introduction to Databases and SQL What is SQL? SQL variants SQL features SQL syntax A sample SQL session 4.MySQL Data Types Basic data types String and string variants Integer and integer variants Date and time types Boolean types Null type Advanced data types Enum (choice) type Binary object type 5.MySQL Syntax (Working With Databases) - CREATE, DROP, SHOW Understanding how databases are stored on the file system Creating a database Viewing available databases Deleting a database Do I need to add info on the InnoDB/Berkelet/MyISAM types here? And how/when to select each? 6.MySQL Syntax (Working With Tables) - DESCRIBE, SELECT, INSERT, UPDATE, DELETE, APPEND, joins Understanding how tables are stored on the file system MySQL table types Creating a table Viewing available tables Creating table columns Inserting records Viewing table records Editing (updating) table records Joining tables Erasing records Altering columns Erasing columns Erasing tables What have I missed here? 7.MySQL Syntax (Working With Tables - Advanced) - Indices, transactions and sub-queries Advanced joins Sub-queries Indices Transactions Stored procedures Do you think I need a separate chapter for each of the above? What have I missed here? Section 3: Administration Section 4: Development -- -- Luke: I don't believe it! Yoda: That, is why you fail. --Star Wars: The Empire Strikes Back - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Difference between MySQL versions
Hi there, Can someone point me to amn (exhaustive) list of features in MySQL Max that are not present in MySQL Standard? Or any other documentation that lists the differences between the two? Thanks in advance, Vikram -- Luke: I don't believe it! Yoda: That, is why you fail. --Star Wars: The Empire Strikes Back - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem compiling 4.0.9-gamma on Windows
Hi all, I get this error when compiling MySQL 4.0.9-gamma on Windows .\debug\Strings.obj LINK : fatal error LNK1181: cannot open input file .\debug\Strings.obj Error executing link.exe. Am using VC++ 6.0 Any help you can offer would be appreciated. Thanks! Vikram - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL history
Is there anyone on this list who can point me to a link that talks about MySQL history - how it began, original creators and vision, evolution over time to 4.x, etc? TIA, Vikram - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySql Server Specification!
Dear friend , I want to connect to MySql server thru telnet ...it ask for Handshake command so any budy can tell me what r the commands for communicating with server and where can i find list of command ...actually i want to write mysql gui client so i need to know what command to send to server for login and other things pleaz mail me at [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with DBUG . Help Quick!
Hi there I have MySQL 3.21.29a-gamma., PHP 3 and PHP 4 (both installed), Apache 1.3.9, and win98 Of lately, When I run PHP files, the MSQL server shuts down with error 10038. Sometimes I get a funny Windows Message Box titled 'DBUG' that has the text 'Test Signal'. Then Mysql crashes. Sometimes Norton Crashguard catches the crash. May be this is happening because of a long SQL SELECT query, that extracts two fields, from 4 tables, with 8 equality clauses in the WHERE section. please help. Tell me if neone had this problem and what u did or any particular things about installing PHP 3 and 4 at the same time. Please Be Quick Vikram Get free email and a permanent address at http://www.netaddress.com/?N=1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
