Re: threads in cleaning up mode
Hi, It sounds like a bad case of transactions handling by the application. If you have transactions/statements open for very long time, the list which contains the unflushed changes, grows. These unflushed changes will slow down the purge and in extreme cases, will also affect the DMLs. So, IMO, the easiest and best way is to make sure the application cleans up after itself and closes the transactions as soon as it can. I hope this helps! Regards,Nitin On Saturday, April 2, 2016 8:07 PM, geetanjali mehra <mailtogeetanj...@gmail.com> wrote: Hi to all, For many days, I am struggling with the problem of increasing history list length on mysql 5.6.29. Application that this server is running IOT based. Also, this server has so many threads running in sleeping mode. SHOW ENGINE INNODB STATUS shows all these threads in *cleaning up* mode. I tried all the options to reduce history list length. But it is constantly increasing. Below are the current settings of purge related threads: innodb_max_purge_lag | 100 | | innodb_max_purge_lag_delay | 0 | | innodb_purge_batch_size | 1 | | innodb_purge_threads | 8 | Also, please let me know that whether cleaning up mode of threads and history list length are correlated. Anty help? Best Regards, Geetanjali Mehra Senior Database Administrator
Re: MySQL on RHEL4
Hi Keith, Thanks for your response. However, i'm not sure which case it is because in the downloads, it says Platform: Oracle and Redhat 45. Isn't it supposed to work? Regards From: Keith Keller kkel...@wombat.san-francisco.ca.us To: mysql@lists.mysql.com Sent: Friday, April 5, 2013 11:26 AM Subject: Re: MySQL on RHEL4 On 2013-04-05, Nitin Mehta ntn...@yahoo.com wrote: We're trying to upgrade our existing MySQL 5.1.26 to MySQL 5.1.68 but the installation gives error:libc.so.6(GLIBC_2.4) is needed by MySQL-server-community-5.1.68-1.rhel5.i386 rtld(GNU_HASH) is needed by MySQL-server-community-5.1.68-1.rhel5.i386 Now, GLIBS2.4 is not available for RHEL4 and MySQL 5.1 and 5.5 are supported on RHEL4 as per this: http://www.mysql.com/support/supportedplatforms/database.html Any ideas? You have apparently taken an RPM for RHEL5 and attempted to use it in RHEL4. It should not be a surprise that it doesn't work. To get it working, either use the correct RPMs (which may not be available), compile from source yourself, or upgrade to RHEL5. --keith -- kkel...@wombat.san-francisco.ca.us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
MySQL on RHEL4
Hi, We're trying to upgrade our existing MySQL 5.1.26 to MySQL 5.1.68 but the installation gives error: libc.so.6(GLIBC_2.4) is needed by MySQL-server-community-5.1.68-1.rhel5.i386 rtld(GNU_HASH) is needed by MySQL-server-community-5.1.68-1.rhel5.i386 Now, GLIBS2.4 is not available for RHEL4 and MySQL 5.1 and 5.5 are supported on RHEL4 as per this: http://www.mysql.com/support/supportedplatforms/database.html Any ideas? Regards, Nitin
Re: Relay log Question
Hi, There was sort of a bug which was fixed in MySQL 5.5 with replication heartbeat. Before the replication heartbeat, a new relay log file would be created after every slave_net_timeout. It doesn't have any negative impact though. Hope that helps. From: Akshay Suryavanshi akshay.suryavansh...@gmail.com To: Wayne Leutwyler wleut...@columbus.rr.com Cc: mysql@lists.mysql.com Sent: Wednesday, January 9, 2013 1:42 AM Subject: Re: Relay log Question Also, you may want to see, if at all new file is really getting every hour exactly, if any cron'd script runs, which executes flush logs on the slave server. That will also rotate relay log. Cheers On Wed, Jan 9, 2013 at 1:35 AM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hi, Please re-phrase your question. The relay logs are created as and when required by the Slave_SQL thread. Once all the events in the relay logs are executed the relay log would be purged by the Slave_SQL thread. By setting relay_log_purge=0 you are disabling this automatic purge option. So the new relay log files will be created however the older ones would not be deleted. Creation or rotation of relay or binary logs is not time-based, for some situations it might create in one hour, however for many others it might create after 1 day, week, or even months. Usually the relay logs in your case should be of definite size, check out this setting max_relay_log_size and max_binlog_size, the latter would come into picture if the prior one is disabled. Thanks, Akshay S On Wed, Jan 9, 2013 at 1:21 AM, Wayne Leutwyler wleut...@columbus.rr.comwrote: Hello List, Quick question. I am running MySQL 5.1.66, and I noticed that every hour, my slave is creating a new relay log. I added this: relay_log_purge = 0 to the my.cnf and it did not solve the issue. What am I missing? Never seen this issue before. Thanks, Walter Courage is being scared to death, but saddling up anyway. --John Wayne
RE: MySQL password issue
Hi Tim, It looks like your '.sql backup file' has changed the password for root user and why it is persisting is perhaps you have data directory outside the install directory. How you correct the problem: Stop the service, start the service with option '--skip-grant-tables', login with root user and change the password from inside mysql. Hope that helps. -Original Message- From: Tim Thorburn [mailto:webmas...@athydro.com] Sent: Thursday, October 25, 2012 12:08 PM To: mysql@lists.mysql.com Subject: MySQL password issue Hi All, Today I ran into an interesting problem with my MySQL installation. I'll start off with the usual suspects: this is my development laptop running Windows 7 Ultimate 64-bit this is a fresh install, fully updated from Windows Update. I downloaded the Windows Installer version of MySQL which is MySQL 5.5.28 Community Server. I ran the setup choosing Developer install, made a root password, then created two users for Backup and Replication. The install completed successfully, and I was able to start MySQL Workbench logging in as root once. I made a user in Server Administration and then imported a .sql backup file. I then went to SQL Development to review the result of the restore. I wasn't able to view the imported tables, Workbench seemed stuck on Retrieving Tables. This database is driving a website I'm working on, and I was able to load the site in a browser on my laptop - again once. Next, I went to add another user, and then tried to import another .sql backup file. This time, when I clicked Start Import, Workbench prompted me to enter my root password again. Odd I thought, as I'd originally told Workbench to save the password in its vault. Unfortunately, Workbench won't accept the root password. I receive the following message in a popup window: Cannot Connect to Database Server Your connection attempt failed for user 'root' from your host to server at localhost:3306: Access denied for user 'root'@'localhost' (using password: YES) The MySQL Service is still running, I've tried stopping, starting, and just restarting the service. I've tried rebooting. All with the same result. What could have happened to this install to make MySQL no longer accept my password? I have already tried uninstalling via the MySQL Installer, confirming that C:\ProgramData\MySQL was removed, and then shutting Windows down completely before trying to re-install. The same result happens - I'm able to log in once, then several minutes later it will no longer accept a password. This is a fresh install again, so there's not a lot of pain in uninstalling and reinstalling. Any thoughts on why this is happening, or more importantly, how to correct the problem? TIA, -Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Table crashed error
Looks like a MYISAM table... MYISAM is not a crash safe storage engine and an instance crash could corrupt the tables. You should run CHECK TABLE table name; and REPAIR TABLE table name; to repair this. On 10/19/2012 11:52 AM, a bv wrote: Hi on a log management server from the web interface i get the below error . What can be the reason and how can i repair it ? Regards Mysql_error: Table ./x/y ' is marked as crashed and should be repaired -- Regards, /Nitin Mehta/
Re: Mysql cluster installation error
Hi Aastha, I'm not 100% sure but you could try defining the full connectstring using: ndb-connectstring = localhost:1186 See if that helps. Regards, From: Aastha aast...@gmail.com To: mysql@lists.mysql.com Sent: Sunday, September 23, 2012 7:51 AM Subject: Mysql cluster installation error Hello, I am trying to install MySQL cluster on three physical machines. Management Node on one machine. Data Node on two machines. SQL node on the same machine as Management Node. Management node started Data Nodes started *SQL node started but not connected to Management NOde and it gives no error * C:\mysql\binndb_mgm -e show Connected to Management Server at: localhost:1186 Cluster Configuration - [ndbd(NDB)] 2 node(s) id=8 @172.16.56.8 (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0, Master) id=9 @172.16.56.9 (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=6 @172.16.56.7 (mysql-5.5.25 ndb-7.2.7) [mysqld(API)] 1 node(s) id=7 (not connected, accepting connect from 172.16.56.7) *config.ini* [ndbd default] # Options affecting ndbd processes on all data nodes: NoOfReplicas=2 # Number of replicas DataDir=C:/mysql/bin/cluster-data # Directory for each data node's data files DataMemory=80M # Memory allocated to data storage IndexMemory=18M # Memory allocated to index storage [ndb_mgmd] # Management process options: HostName=172.16.56.7 # Hostname or IP address of management node DataDir=C:/mysql/bin/cluster-logs # Directory for management node log files NodeId=5 [ndbd] # Options for data node A: HostName=172.16.56.8 # Hostname or IP address NodeId=8 MaxNoOfOrderedIndexes=1024 MaxNoOfAttributes=3000 # added 2012.8.08 [ndbd] # Options for data node B: HostName=172.16.56.9 # Hostname or IP address NodeId=9 MaxNoOfOrderedIndexes=1024 MaxNoOfAttributes=3000 # added 2012.8.08 [mysqld] # SQL node options: HostName=172.16.56.7 # Hostname or IP address NodeId=7 *my.ini* * * [mysql_cluster] # Options for management node process config-file=c:/mysql/bin/config.ini configdir=c:/mysql/bin/cluster-cache/ [mysqld] # Options for mysqld process: ndbcluster # run NDB storage engine ndb-connectstring=172.16.56.7 # location of management server ndb-nodeid=7 server-id=7 default-storage-engine=ndbcluster Could anyone help to identify/ Aastha Gupta
Re: Having trouble with SQL query
I'm more of an hit and try guy and do good only with a data set available. Still I think making a little change might do the trick. SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid AND m.name = 'name obtained from user's click') INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid) Hope that works. Regards, From: rich gray r...@richgray.com To: mysql@lists.mysql.com Sent: Monday, August 27, 2012 2:46 AM Subject: Having trouble with SQL query I have a MySQL database with a menu table and a product table linked to the menus *(each product can be linked to more than menu row)* and the menus are nested. The query is that when a user clicks on a menu entry then all products linked to that entry *(there may be none)* will get displayed as well as all products linked to child menus... below are describe tables for the 2 main tables in question (there is a 3rd table that holds product descriptions which I won't show as I don't think it is relevant) CREATE TABLE `menu` ( `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_menuid` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`menuid`) ) ENGINE=MyISAM AUTO_INCREMENT=225 DEFAULT CHARSET=utf8 CREATE TABLE `menu_product` ( `menuid` int(11) unsigned NOT NULL, `productid` int(11) unsigned NOT NULL, PRIMARY KEY (`menuid`,`productid`), KEY `prodidx` (`productid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 Anyway for the sake of this question lets say there is only 2 levels of nesting so a parent menu can only have children so no grandkids+ this is the query I came up with:- SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid) INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE (m.name = 'name obtained from user's click' OR p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid) Anyway the above query returns many many entries from menus that are totally unrelated... I have been staring too hard at this for too long - I am sure it will be a forehead slapper! I hope I have explained this sufficiently and I TYIA for any guidance Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: master BIN-LOG maintenace
Hi Charles, I guess your application doesn't generate too much of binary logs. The parameter expire-logs-days kicks in at the flush but does not necessarily require a manual flush logs command. You can reduce the value of max_binlog_size to make sure that at least one new file is created daily and this will force mysql to check and delete old bin-logs. The default (and maximum) value for this variable is 1G. If I'm not wrong, it is a dynamic variable, which means you can try different values without a restart of the database until you find a suitable one. Hope that helps! Regards, From: Brown, Charles cbr...@bmi.com To: Nitin Mehta ntn...@yahoo.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Sent: Thursday, May 3, 2012 4:24 PM Subject: master BIN-LOG maintenace Hi Gurus, I would like to set BIN-LOG maintenance procedure for my master. The master is on a windows platform. I’m all for make it simple and clean therefore I’ve been leaning toward the automatic BIN-LOG removal “expire-logs-days=7”. The problem is for this option to work, it should be preceded by a “BIN-LOG FLUSH” command. Okay, where do I go from here in order to make this work. Please help me. Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you.
Re: using the bin-log approach on the master side, how can I accomplish my replication objectives
Thanks for your kind words, Charles! It comes easily to you when you have experienced it. I'm glad it worked for you. From: Brown, Charles cbr...@bmi.com To: Nitin Mehta ntn...@yahoo.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Sent: Thursday, May 3, 2012 3:17 PM Subject: RE: using the bin-log approach on the master side, how can I accomplish my replication objectives Hello Nitin, Please give Nitin a prize. What a quiet genius she is. Now, I get it. Now, I can see clearly. I’ve tried it and it worked. Thanks so much. From: Nitin Mehta [mailto:ntn...@yahoo.com] Sent: Wednesday, May 02, 2012 11:25 PM To: Brown, Charles Cc: mysql@lists.mysql.com Subject: Re: using the bin-log approach on the master side, how can I accomplish my replication objectives Hi Charles, I believe you would already have bin-log configured, is that right? If not, you need to. Secondly, If you think the bin-log generated for the entire stack of databases/schemas is too big, you may want to restrict it using binlog-do-db BUT that may create problem if you have any DMLs which run in the below manner: mysql use db1; mysql update db2.tb2 You may be surprised to find that this update will not be recorded in master's bin-log if you have set binlog-do-db=db2. To sum it up, if you're not very sure, simply enable bin-log on master and use replicate-do-table on slave. So in one word response to your question, no! Hope that helps! Also, please reply to all so the people, helping you, know about the status of your problem. Regards, From: Brown, Charles cbr...@bmi.commailto:cbr...@bmi.com To: Nitin Mehta ntn...@yahoo.commailto:ntn...@yahoo.com Sent: Thursday, May 3, 2012 9:27 AM Subject: RE: using the bin-log approach on the master side, how can I accomplish my replication objectives Hello Nitin, Help Me! Using your approach, do I have to put anything on the master “my.cnf”. Thanks From: Nitin Mehta [mailto:ntn...@yahoo.com] Sent: Wednesday, May 02, 2012 10:51 PM To: Brown, Charles; Rick James; a.sm...@ukgrid.netmailto:a.sm...@ukgrid.net; mysql@lists.mysql.commailto:mysql@lists.mysql.com Subject: Re: using the bin-log approach on the master side, how can I accomplish my replication objectives I guess, it only makes things even more simple. You should use replicate-do-table=db1.tb1 and so one in slave's cnf to ensure that only this particular table gets replicated. However, all the databases on master will get bin-logged and you may or may not want to do that. So making it even simpler: on the slave: replicate-do-table=db1.tb1 replicate-do-table=db2.tb2 replicate-do-table=db3.tb3 You might want to have a look at : http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-do-table http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html Hope this helps! From: Brown, Charles cbr...@bmi.commailto:cbr...@bmi.com To: Rick James rja...@yahoo-inc.commailto:rja...@yahoo-inc.com; a.sm...@ukgrid.netmailto:a.sm...@ukgrid.net a.sm...@ukgrid.netmailto:a.sm...@ukgrid.net; mysql@lists.mysql.commailto:mysql@lists.mysql.com mysql@lists.mysql.commailto:mysql@lists.mysql.com Sent: Thursday, May 3, 2012 8:51 AM Subject: using the bin-log approach on the master side, how can I accomplish my replication objectives Hello Rick, I think I'll go with your recommendations - but help me here a little bit. Because all look fuzzy like a brain surgery. So make it simple and clean for me. For the sake of simplicity, here is what my master has: Database: db1 Tables: db1tb1, db1tb2, db1tb3 Database: db2 Tables: db2tb1, db2tb2, db2tb3 Database: db3 Tables: db3tb1, db3tb2, db3tb3 Now, I would like to replicate only these tables that belong to respective databases: db1tb1, db2tb2, and db3tb3 My question is: using the bin-log approach on the master side, how can I accomplish my replication objectives? Help me Best regards and thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you
Re: using the bin-log approach on the master side, how can I accomplish my replication objectives
I guess, it only makes things even more simple. You should use replicate-do-table=db1.tb1 and so one in slave's cnf to ensure that only this particular table gets replicated. However, all the databases on master will get bin-logged and you may or may not want to do that. So making it even simpler: on the slave: replicate-do-table=db1.tb1 replicate-do-table=db2.tb2 replicate-do-table=db3.tb3 You might want to have a look at : http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-do-table http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html Hope this helps! From: Brown, Charles cbr...@bmi.com To: Rick James rja...@yahoo-inc.com; a.sm...@ukgrid.net a.sm...@ukgrid.net; mysql@lists.mysql.com mysql@lists.mysql.com Sent: Thursday, May 3, 2012 8:51 AM Subject: using the bin-log approach on the master side, how can I accomplish my replication objectives Hello Rick, I think I'll go with your recommendations - but help me here a little bit. Because all look fuzzy like a brain surgery. So make it simple and clean for me. For the sake of simplicity, here is what my master has: Database: db1 Tables: db1tb1, db1tb2, db1tb3 Database: db2 Tables: db2tb1, db2tb2, db2tb3 Database: db3 Tables: db3tb1, db3tb2, db3tb3 Now, I would like to replicate only these tables that belong to respective databases: db1tb1, db2tb2, and db3tb3 My question is: using the bin-log approach on the master side, how can I accomplish my replication objectives? Help me Best regards and thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you.
Re: using the bin-log approach on the master side, how can I accomplish my replication objectives
Hi Charles, I believe you would already have bin-log configured, is that right? If not, you need to. Secondly, If you think the bin-log generated for the entire stack of databases/schemas is too big, you may want to restrict it using binlog-do-db BUT that may create problem if you have any DMLs which run in the below manner: mysql use db1; mysql update db2.tb2 You may be surprised to find that this update will not be recorded in master's bin-log if you have set binlog-do-db=db2. To sum it up, if you're not very sure, simply enable bin-log on master and use replicate-do-table on slave. So in one word response to your question, no! Hope that helps! Also, please reply to all so the people, helping you, know about the status of your problem. Regards, From: Brown, Charles cbr...@bmi.com To: Nitin Mehta ntn...@yahoo.com Sent: Thursday, May 3, 2012 9:27 AM Subject: RE: using the bin-log approach on the master side, how can I accomplish my replication objectives Hello Nitin, Help Me! Using your approach, do I have to put anything on the master “my.cnf”. Thanks From:Nitin Mehta [mailto:ntn...@yahoo.com] Sent: Wednesday, May 02, 2012 10:51 PM To: Brown, Charles; Rick James; a.sm...@ukgrid.net; mysql@lists.mysql.com Subject: Re: using the bin-log approach on the master side, how can I accomplish my replication objectives I guess, it only makes things even more simple. You should use replicate-do-table=db1.tb1 and so one in slave's cnf to ensure that only this particular table gets replicated. However, all the databases on master will get bin-logged and you may or may not want to do that. So making it even simpler: on the slave: replicate-do-table=db1.tb1 replicate-do-table=db2.tb2 replicate-do-table=db3.tb3 You might want to have a look at : http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-do-table http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html Hope this helps! From:Brown, Charles cbr...@bmi.com To: Rick James rja...@yahoo-inc.com; a.sm...@ukgrid.net a.sm...@ukgrid.net; mysql@lists.mysql.com mysql@lists.mysql.com Sent: Thursday, May 3, 2012 8:51 AM Subject: using the bin-log approach on the master side, how can I accomplish my replication objectives Hello Rick, I think I'll go with your recommendations - but help me here a little bit. Because all look fuzzy like a brain surgery. So make it simple and clean for me. For the sake of simplicity, here is what my master has: Database: db1 Tables: db1tb1, db1tb2, db1tb3 Database: db2 Tables: db2tb1, db2tb2, db2tb3 Database: db3 Tables: db3tb1, db3tb2, db3tb3 Now, I would like to replicate only these tables that belong to respective databases: db1tb1, db2tb2, and db3tb3 My question is: using the bin-log approach on the master side, how can I accomplish my replication objectives? Help me Best regards and thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you.
Re: thread connected and thread cached
Hi Jeff, threads_connected tells you total number of connections created since server startup and threads_cached says how many stay alive (connection pooling) even if there are no clients connected. This means that because the value of threads_cached was reduced on your server, it is having to fork more and more new connections every time a request for connection comes in. Hope that helps... Regards, From: Jeff Pang jeffrp...@gmail.com To: mysql@lists.mysql.com Sent: Tuesday, December 27, 2011 8:45 AM Subject: thread connected and thread cached Hello, I got the two picutures attached from mycheckpoint's web graph. They are showing the threads_connected have been increasing from 24th. But threads_cached have been decreasing from the same day. What does this mean for my mysql? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: mysqldump error 1064 for database Use command
I'm not sure but you might try with sql_mode set to ANSI. Otherwise try the dump remotely from a Linux box. Regards, Nitin From: Manasi Save manasi.s...@artificialmachines.com To: win.a win@gmail.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Sent: Wed, September 8, 2010 3:57:40 PM Subject: Re: mysqldump error 1064 for database Use command Hi win.a, I tried with another user the problem still persists. I am getting same error for another user as well. -- Regards, Manasi Save On Tue, 7 Sep 2010 11:41:35 0800, win.a wrote: use another user and dump the data ,eg the root . mysqldump -uroot -p --al-databases AllNew_Databases_20100904.sql All you best What we are struggling for ? The life or the life ? On Mon, Sep 6, 2010 at 8:33 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi win.a, How am I suppose put that command. Could you help me with the syntax: If I am giving it this way:- mysqldump --all-databases AllNew_Databases_20100904.sql then it says it will not work with my system user. nor with this it is working :- mysqldump -u --all-databases AllNew_Databases_20100904.sql -- Regards, Manasi Save On Mon, 6 Sep 2010 20:12:26 0800, win.a wrote: try it without username and password in command line,type it it prompts. All you best What we are struggling for ? The life or the life ? On Mon, Sep 6, 2010 at 3:28 PM, Manasi Save manasi.s...@artificialmachines.com wrote: No it does not. But when I dump database name mydb it does. but not the database with name 1. -- Regards, Manasi Save On Mon, 06 Sep 2010 09:23:21 0200, Jangita wrote: On 06/09/2010 6:54 a, Manasi Save wrote: Dear Nitin, I have newly installed mysql on this server. mysql Select version(); - | version() | - | 5.1.22-rc-Debian_2~ppa5-log | - Earlier I use to run the same command on Fedora-with same mysql version. I could not possibly change the database name. There are quite a few databases I have on the system. -- Regards, Manasi Save Does it work when you dump only the database `1`? -- Jangita | 256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=manasi.s...@artificialmachines.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=win@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ntn...@yahoo.com
Re: mysqldump error 1064 for database Use command
Hi, Have you recently upgraded your MySQL installation? 1064 in earlier days used to mean use of reserved word. Few clients have reported this error in last couple of months after they upgraded from 5.1.41 to higher versions. As of now, I don't have a solution other than changing the name (of database in your case). Hope that helps. Regards, Nitin From: Manasi Save manasi.s...@artificialmachines.com To: mysql@lists.mysql.com mysql@lists.mysql.com Sent: Sat, September 4, 2010 12:06:27 PM Subject: mysqldump error 1064 for database Use command Hi All, I have 10 mysql databases all the tables use MyIsAm mysql storage engine. Database names are 1,2,3,4,...10. When I use mysqldump command with --all-databases option. This gives me following error: mysqldump -u myuser -p --all-databases AllNew_Databases_20100904.sql ERROR MESSAGE :- mysqldump: Couldn't execute 'use 1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 (1064) Can anyone provide any input on this. I have never got this error before. The backups uptill now was happening properly. Please let me know if I am missing out any information which should be provided to get more clear idea about this error. Any input will be a great help. Thanks in advance. -- Regards, Manasi Save
Re: Logs not working
I believe that will not be logged unless you have enabled log_warnings. Too many connections would normally mean that the number is going beyond the configured limit and denying new sessions is an expected behavior and should not be an error really. Regards, Nitin From: monloi perez mlp_fol...@yahoo.com To: Ananda Kumar anan...@gmail.com Cc: mysql mailing list mysql@lists.mysql.com Sent: Sat, September 4, 2010 11:37:42 AM Subject: Re: Logs not working on the db server? meaning the mysqld log right? THere is really no data for teh current error. The last error they said was too much connections and shouldnt that be logged in mysqld.log? Thanks, Mon From: Ananda Kumar anan...@gmail.com To: monloi perez mlp_fol...@yahoo.com Cc: mysql mailing list mysql@lists.mysql.com Sent: Thu, September 2, 2010 6:14:26 PM Subject: Re: Logs not working Did u check the logs on the db server, to see what the issue was. regards anandkl On Thu, Sep 2, 2010 at 6:25 AM, monloi perez mlp_fol...@yahoo.com wrote: All, I'm not sure if this is the right mailing list since the specific mailing lists doesn't seem to meet my concern. For some reason mysql client stops logging to mysqd.log. We had an issue on Aug 20. But when I checked the log, the latest was on Aug 2. Any idea on how to resolve this or what caused it? Thanks, Mon
Re: MySQL data get and set problem
Manasi, Your table structure doesn't show that the column TestID is unique. I believe what Michael also suggested was that unless this column contains unique values, you never know which row your procedure is reading. I hope I'm making myself understood. Regards, Nitin From: Manasi Save manasi.s...@artificialmachines.com To: Michael Dykman mdyk...@gmail.com Cc: mysql@lists.mysql.com mysql@lists.mysql.com Sent: Tue, August 10, 2010 10:34:37 AM Subject: Re: MySQL data get and set problem Hi Michale, I have made that parameter column name change. but I am unable to understand how even after execution of update statement and commit after that, data is getting reverted. Any inputs on this will be a great help. -- Regards, Manasi Save On Mon, 9 Aug 2010 14:11:19 -0400, Michael Dykman wrote: You might have better results if you use unique identifiers: Select MyID From Test Where TestID = TestID; I'm not sure how mysql is supposed to tell the param 'TestID' from the column name. - michael dykman On Mon, Aug 9, 2010 at 3:10 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Dear All, Table values are getting rollback without calling rollback. I have a table Test with some columns in it. Here's the Table Structure:- Create Table TestID ( TestID int not null, MyID int ); I am calling two procedures one is get the value of MyID column and one is to set the value after that. FirstProc :- Create procedure SP_GetMyID(TestID int) Begin Select MyID From Test Where TestID = TestID; End; Call SP_GetMyID(1); [OUTPUT = 1] If suppose the above proc returns me 1 as MyID then below proc will set MyID as 2 (incrementing by one) Call SP_SetMyID(1,2); Create Procedure SP_SetMyID(TestID int, NewMyID int) Begin Update Test Set MyID = NewMyID Where TestID = TestID; End; After setting the value I am again calling get procedure Call SP_GetMyID(1); [OUTPUT = 2] and if after some time say after 10 seconds if I m calling SP_GetMyID again. It gives me OUTPUT as 1. -- Regards, Manasi Save Artificial Machines Private Limited manasi.s...@artificialmachines.com Ph:-9833537392 -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ntn...@yahoo.com
Help with flush logs
Hi, This is probably very simple for someone who has encountered the problem before but I'm struggling to find how I can contain the MySQL error log from being flushed at FLUSH LOGS command. This command is executed as part of the database backup every night and simply moves the old error log to -old file. This way, I can only have maximum 2 days of error logs while I need to keep the logs for at least 60 days (for auditing as well as debugging purposes). I understand that I can probably achieve this using log rotate or similar things but just wondering if it is possible to achieve this within MySQL conf itself. Thanks in advance! Regards, Nitin
Re: Innodb buffer pool size
Hi, First thing that comes to my mind is that it is probably the best time to put your application server and database server on different hosts. Having said that, in this case increasing buffer pool size is still advisable as per my understanding. Your swap consumption will go up in that case which is not very good either. But giving only 4 GB to Innodb is even worse for the performance. It is subjective though. You should first check if MySQL is actually using the allotted 4GB or not. If not, increasing the value will not help. Maybe someone can correct me if I'm wrong. :-) Hope that helps! Regards, Nitin From: Machiel Richards machi...@rdc.co.za To: mysql@lists.mysql.com Sent: Thu, May 20, 2010 1:07:43 PM Subject: Innodb buffer pool size Hi Guys I just have a quick question. I have done some research into how to determine the size of your Innodb buffer pool. All of the sources I used, specified that the Innodb buffer pool size should be the same size as your database + 10%. However, as far as I understand it, the buffer size also relies on that amount of memory being available. Thus if you increase the buffer size, the amount of memory used will be increased. My thinking however, is what happens when the database size grows bigger than the amount of memory available to the hardware. Say for instance, a server with MySQL also runs other applications. The amount of memory on the server is 32Gb and about 31Gb is already in use. The current Innodb buffer pool size is at 4Gb for instance, and the innodb tables then grow to be about 8Gb in size. What would be the appropriate actions for this to ensure the buffers are set to the size to best suit the database needs?
Re: starting multiple instances with mysqld_multi
Thanks, Prabhat! You're very right, but did you read the questions I asked?
From: Prabhat Kumar aim.prab...@gmail.com
To: Nitin Mehta ntn...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Tue, May 11, 2010 11:54:22 AM
Subject: Re: starting multiple instances with mysqld_multi
Hi,
You can run multiple instances on the same host and specify configuration
options either by adding the options as arguments on the command line, or by
storing them in configuration files.
for example, from the command line:
shell mysqld --port 3306 --log=/path/to/server1/log
shell mysqld --port 3307 --log=/path/to/server2/log
So, when MySQL starts up it reads various config files in sequence. This means
that you can set global options for all instances in one file and then specific
options for individual instances in separate files.
The files it reads by default are (on linux)
/etc/my.cnf
the_mysql_data_dir/my.cnf
~/.my.cnf
You can direct the server to read specific config files after it reads the
default /etc/my.cnf file with the following directive.
--defaults-extra-file=/path/to/extra/config/file
So, if you wanted 2 instances running with some shared options and some
specific options, you could create additional configuration files in /etc say:
/etc/my.server1.cnf
/etc/my.server2.cnf
where my.server1.cnf has the following lines:
port=3306
log=/path/to/log/file/for/server1
(other options here)
and my.server2.cnf is:
port=3307
log=/path/to/log/file/for/server2
(other options here)
then you could start the separate instances of mysqld as follows:
shell mysqld --defaults-extra-file=/etc/my.server1.cnf
shell mysqld --defaults-extra-file=/etc/my.server2.cnf
about mysqld_multi
mysqld_multi
mysqld_multi [ options] {start| stop| report} [ server_id]
Use this to run multiple MySQL servers on different socket files and ports. To
set up
multiple servers, a different section of server options must be entered into a
configuration file (e.g., /etc/my.cnf). The naming scheme for each section
must be [mysqldn]
where n is a different number for each server. Options must be entered
separately for
each server in its own section, even when servers use the same options. At a
minimum,
each server should use a different socket file and a different TCP/IP port. To
see an
example of how a configuration file might be set up for multiple servers,
enter the
following from the command line:
mysqld_multi --example
Once multiple servers have been configured, to start a server, you can enter
something
like the following from the command line:
mysqld_multi start 3
This line would start server number 3 listed in the configuration file as
[mysqld3]. By
entering report for the first argument, you can obtain the status on the
server. For
starting and stopping the server, this script uses the mysqladmin utility.
Here is an
alphabetical list of options specific to mysqld_multi that you can enter from
the
command line, along with a brief explanation of each:
--config-file=filename
Specifies the path and name of the server’s configuration file if it is
different from
the default.
--example
Displays a sample configuration file.
--help
Displays basic help information.
--log=filename
Sets the path and name of the logfile. The default is /tmp/mysqld_multi.log.
--mysqladmin=path
Sets the path to the mysqladmin utility.
--mysqld=path
Specifies the path to mysqld.
--no-log
Instructs the utility not to save messages to a log, but to send them to
stdout
instead.
--password=password
Provides the password for using mysqladmin.
--tcp-ip
Enables TCP/IP communication with the server instead of using a Unix-domain
socket.
--user=user
Provides the username for using mysqladmin. The same user must be used for all
servers.
--version
Displays the version of the utility.
Thanks,
On Tue, May 11, 2010 at 9:54 AM, Nitin Mehta ntn...@yahoo.com wrote:
Hi,
I am exploring multiple instances with MySQL which will be running using the
same set of binaries (single MySQL base). Two things which I'm not able to
work out are -
1. Why mysqld_multi stop grp_id doesn't work? Well, I know why it
doesn't because there is nothing in the script to handle a 'stop' call; the
actual question would be - why it doesn't work when it is mentioned in the
MySQL reference manual?
2. how to (auto) restart the instances after a server restart? do I
need to write my own script for this or is there anything available which I'm
not aware of?
If you have resolved these issues, please let me know.
Best Regards,
Nitin
--
Best Regards,
Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com
My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat
starting multiple instances with mysqld_multi
Hi, I am exploring multiple instances with MySQL which will be running using the same set of binaries (single MySQL base). Two things which I'm not able to work out are - 1. Why mysqld_multi stop grp_id doesn't work? Well, I know why it doesn't because there is nothing in the script to handle a 'stop' call; the actual question would be - why it doesn't work when it is mentioned in the MySQL reference manual? 2. how to (auto) restart the instances after a server restart? do I need to write my own script for this or is there anything available which I'm not aware of? If you have resolved these issues, please let me know. Best Regards, Nitin
Re: mysql transaction log
I think you've got it a little wrong. In MySQL, transaction log is different from bin-log. Transaction logs are used only for Innodb while bin-logs are optional and capture data related to all the storage engines. I believe, you cannot read thru the transaction logs and these logs files (used in cyclic fashion) are only for the purpose of Instance recovery other than providing transaction support. Bin logs, on the other hand are used mainly for two purposes - replication and PTR (point-in-time recovery). A small utility called mysqlbinlog can read through the bin logs and generate SQL file which is very handy if you need to take up PTR or just want to verify any details. Hope that helps! Regards, Nitin - Original Message From: Angelina Paul arshup...@gmail.com To: mysql@lists.mysql.com Sent: Tue, May 4, 2010 3:49:35 AM Subject: mysql transaction log Please help me to understand more about the mysql transaction log ( mysqlbinlog) file and its contents. Will it support only the innodb or all the storage engine types like MyISAM, InnoDB? Thanks, Arsh Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Is mysql the owner of the directories? - Original Message From: Manasi Save manasi.s...@artificialmachines.com To: Johan De Meersman vegiv...@tuxera.be Cc: Waynn Lue waynn...@gmail.com; mysql mysql@lists.mysql.com Sent: Wed, November 25, 2009 8:12:25 PM Subject: Re: question regarding mysql database location Dear Johan, Need your help again in understanding How mysql reads symlink. As you said below, I have created symlinks in default mysql directory. and try to read that symlink file as a database. But mysql is not reading that file as Database. Is there any settings which I need to change. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ntn...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
insert into text field
Hi all,
There's a problem. When I try to inert into table with
insert into schedule (owner, desc, sch_for, status) values ('a', 'b', 'c', 'd')
I get error
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL
server version for the right syntax to use near 'desc, sch_for, status) values'
The desc field is of 'text' datatype. If that's the reason, what is the right syntax
to insert into text field?
Please help me
Thanks in advance
Re: insert into text field [Solved]
Solved.
- Original Message -
From: [EMAIL PROTECTED]
To: Nitin [EMAIL PROTECTED]
Sent: Saturday, June 26, 2004 4:33 PM
Subject: Re: insert into text field
look *carefully* at the insert syntax, paying attention to spaces and
the like:
INSERT INTO tbl_name () VALUES()
the error message gives a good hint too.
-- Original Message --
From: Nitin [EMAIL PROTECTED]
To: MySQL Mailing List [EMAIL PROTECTED]
Date: Saturday, June 26, 2004 04:19:37 PM +0530
Subject: insert into text field
Hi all,
There's a problem. When I try to inert into table with
insert into schedule (owner, desc, sch_for, status) values ('a',
'b', 'c', 'd')
I get error
You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to
use near 'desc, sch_for, status) values'
The desc field is of 'text' datatype. If that's the reason, what is
the right syntax to insert into text field?
Please help me
Thanks in advance
-- End Original Message --
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
autoincrement problem
Hi all, What will be the best way to implement auto increment field. I dont want to use auto increment feature, as it only appends the numbers but doesn't check for the values deleted. Suppose, following are the values in table 1abc 2bcd 3cde .. .. .. 9xyz now if 2nd and 3rd rows are deleted, an autoincrement field will still assign 10, 11, 12 to the new values, while I want it to assign the values deleted from the table first. What will be the best way to implement it? Thanks in advance Nitin
Re: autoincrement problem
First of all, thanks for replying. but, u didnt get my point. I want to use numeric field only as the key field, but not the normal auto increment.. - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: Nitin [EMAIL PROTECTED]; MySQL Mailing List [EMAIL PROTECTED] Sent: Saturday, June 12, 2004 3:53 PM Subject: Re: autoincrement problem Nitin wrote: Hi all, What will be the best way to implement auto increment field. I dont want to use auto increment feature, as it only appends the numbers but doesn't check for the values deleted. Suppose, following are the values in table 1abc 2bcd 3cde .. .. .. 9xyz now if 2nd and 3rd rows are deleted, an autoincrement field will still assign 10, 11, 12 to the new values, while I want it to assign the values deleted from the table first. What will be the best way to implement it? Thanks in advance Nitin You shouldn't use text fields as primary keys. It's much faster and more efficient to use a numeric field. You're also asking for trouble trying to re-use keys. What happens if someone has a key 'bcd' and then their record gets deleted, and someone else gets the key 'bcd'. You go back over data and see reference to 'bcd', and have no idea what it's referring to unless you get the transaction logs out and check what data the key was referring to on that particular day. It will be an absolute nightmare to debug, and you destroy any sane way of auditing your data. The best way to implement it, therefore, is to *not* implement it. Use MySQL's auto_increment field as the primary key. If you absolutely *must* reuse the text 'keys' you have above, then that's up to your application to handle. Make a char / varchar field and have your code scan the table for the next available key and then use that in your 'insert' statement. But it's a *really* bad idea. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
I hope it should work: Select table1.ID from table1 left join table2 on table1.value=table2.value where table2.value is null OR if you want distinct IDs Select distinct table1.ID from table1 left join table2 on table1.value=table2.value where table2.value is null though I didn't test it, if it does (or doesn't) let me know Regards Nitin - Original Message - From: [EMAIL PROTECTED] To: Michael Stassen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, April 29, 2004 1:15 PM Subject: Re: SQL SELECT HELP Hi, Sorry. My english is not so good. :( I try to explain. I have table1 : ID value -- 1 100 1 101 1 102 1 200 2 100 2 300--- 2 310 | 3 100 | | and table2: | | value | --- | 300 - The result of the query should be from IDs of table1 (In this case 1,3) . The ID 2 is not allowed, because the table2 is the exception table wich is containing the value 300. Michael Stassen [EMAIL PROTECTED] 2004-04-28 06:13 PM To: Zoltan Gyurasits/GYO/COMP/[EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: SQL SELECT HELP Classification: I'm afraid I don't understand. From your first message, it appears you want a list of rows from table1 whose ids do not appear in table2. The query Egor sent you does just that. Did you try it? If, as you say here, that isn't what you want, could you please describe what you do want? Michael [EMAIL PROTECTED] wrote: Thanks your help, but I would like to do the following: If I have is ONE same ID between the two tables, than the result it must be empty. Egor Egorov [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: snip I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CURDATE() bug?
Hi, - Original Message - From: Alan Williamson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 5:17 PM Subject: CURDATE() bug? Could anyone tell me if this is a bug or not. SQL: SELECT CURDATE()+0; RESULT: 20040331 Thats good. However consider this: SQL: SELECT CURDATE()+1; RESULT: 20040332 Not so good. Infact with this version any WHERE clauses you would put this in, fails to bring back the right result. Does CURDATE() support numeric addition like this? Or is the +0 purely a casting-hack to get the right format. Its not meant as pure addition. Yes, hav a look at http://www.mysql.com/doc/en/Date_and_time_functions.html for explaination further for addition, use date_add(curdate(), interval 1 day) or watever Thoughts? thanks alan -- Alan Williamson, City Planner w: http://www.BLOG-CITY.com/ e: [EMAIL PROTECTED] b: http://alan.blog-city.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] HTH... Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access to MySQL query problem
please include the query, you are using with no success and also specify the version of your mysql. Nitin - Original Message - From: Ed Reed [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 29, 2004 1:27 PM Subject: Access to MySQL query problem I'm coverting an Access database to MySQL 4.1.1 and I need help with a near impossible query. In the Access DB I used a stored procedure; I think I should be able to solve this problem with a subselect in MySQL but so far I'm not having any luck. Here's the problem; In my stored precedure query I get results that look like this Item OpUser 2751 2 Dude 2751 3 Aguy 4785 1 Dude 4785 2 Gus 5623 1 Dude 5623 2 Gus 5654 1 Gus 5654 2 Aguy I then query these results to get only the Items for a user when the specified user is the first person for that Item ID, for example. If I query the results for Dude Items 2751, 4785 5623 are returned. If I query the results for Gus Item 5654 are return but not Items 4785 or 5623. If I query the results for Aguy no Items are returned. Does anyone have any thoughts on this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Libmysqlclient.so.14 on Solaris 2.8 (SPARC)
Hello buddy, try installing MySQL-shared-compat for your platform HTH Nitin - Original Message - From: Timothy Venn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 29, 2004 7:13 PM Subject: Libmysqlclient.so.14 on Solaris 2.8 (SPARC) Hello, Please can someone tell me where I can get / compile the libmysqlclient.so.14 file for Solaris 2.8 (SPARC) as an application that I am installing requires this library file ? Regards Tim South Africa --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.511 / Virus Database: 308 - Release Date: 2003/08/18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple-table Update
which ver of mysql are you using? multiple table update is possible only
with ver starting from 4.0.4.
HTH..
Nitin
- Original Message -
From: Laercio Xisto Braga Cavalcanti [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Friday, March 26, 2004 11:39 PM
Subject: Multiple-table Update
'm using Multiple-table Update with the above sintax:
mysql desc italica;
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| ita_empresa | decimal(10,0) | | | 0 | |
| ita_matricula | decimal(10,0) | | PRI | 0 | |
| ita_dv | decimal(1,0) | | PRI | 0 | |
| ita_nome | varchar(50) | YES | | NULL | |
| ita_fone | varchar(19) | YES | | NULL | |
| ita_nascimento | date | YES | | NULL | |
| ita_adesao | date | YES | | NULL | |
| ita_ultpag | date | YES | | NULL | |
++---+--+-+-+---+
8 rows in set (0.00 sec)
mysql desc paciente
- ;
+-+---+--+-++---
-+
| Field | Type | Null | Key | Default | Extra
|
+-+---+--+-++---
-+
| pac_codigo | int(11) | | PRI | NULL |
auto_increment |
| pac_nome | varchar(100) | YES | | NULL |
|
| pac_rg | int(11) | | | 0 |
|
| pac_nascimento | date | | | -00-00 |
|
| pac_sexo | enum('M','F') | | | M |
|
| pac_endereco | varchar(50) | | | |
|
| pac_numero | int(11) | | | 0 |
|
| pac_complemento | varchar(50) | YES | | NULL |
|
| pac_celular | varchar(9) | YES | | NULL |
|
| pac_fone | varchar(9) | YES | | NULL |
|
| pac_fone_com | varchar(9) | YES | | NULL |
|
| pac_convenio | enum('S','N') | | | S |
|
| pac_matricula | int(11) | YES | | NULL |
|
| pac_adesao | date | YES | | NULL |
|
| pac_email | varchar(50) | YES | | NULL |
|
| pac_dv | int(1) | YES | | NULL |
|
| pac_bairro | varchar(50) | YES | | NULL |
|
| pac_cep | varchar(10) | YES | | NULL |
|
| pac_cidade | varchar(50) | YES | | NULL |
|
| pac_ultimopag | date | YES | | NULL |
|
+-+---+--+-++---
-+
20 rows in set (0.00 sec)
update paciente, italica
SET pac_nome = ita_nome,
pac_fone = ita_fone,
pac_nascimento = ita_nascimento,
pac_ultimopag = ita_ultpag
WHERE ita_matricula = pac_matricula
AND ita_dv = pac_dv;
and I got the following error:
ERROR 1064: You have an error in your SQL syntax near ' italica
SET pac_nome = ita_nome,
pac_f' at line 1
What can I do?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - dependencies Same problem --Plz giv the solution !!
You need to download Dynamic client libraries - (including 3.23.x libraries) to satisfy these dependencies. This is available on mysql download page. Choose for your particular version. And if that helps, read the installation procedure, next time before installation. Hope that helps Nitin - Original Message - From: rajesh k [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, March 23, 2004 8:05 PM Subject: Newbie - dependencies Same problem --Plz giv the solution !! Hi Robert / Victor /. ! Tried your method and got: error : Failed dependencies MySQL-server conflicts with mysql-server-3.23.54a-11 mysql conflicts with mysql-3.23.54a-11 rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm I tried 'locate' but could not find either file, so then tried 'rpm -e' of both. I got 'not installed' as an answer. So tried your line once more and got the same output. Robb + ( ME TOO )- still in jail :-( Plz get me out of the jail !!! Thanx, bii __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using old frm files
you can very well use those files, though, im not sure, if you can check the table structure bafore restoring it. HTH Nitin - Original Message - From: dan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 23, 2004 9:34 PM Subject: using old frm files we had a database blown away (oops) and we still have the old .frm files, is there any way to get this structure back? is there a way to view the contents of the .frm files to see how the tables/indexes were setup? I can't find anything in the docs, faqs, etc. Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Downloading Data
mysql doesn't write any files to any directory other than its data directory, so try: SELECT * INTO OUTFILE 'Results.txt' FROM Answer; and then copy it whereever you want. HTH Nitin - Original Message - From: Rick Duley [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Geoff Roy [EMAIL PROTECTED] Sent: Tuesday, March 23, 2004 8:52 AM Subject: Downloading Data Hi folks I am running MySQL 4..0.7-gamma on Red Hat Linux 7.2. I wish to download data from my database. I have a directory called 'Results' with permissions set to: drwxrwxrwx I enter the command: SELECT * INTO OUTFILE '/home/rick/Results/Results.txt' FROM Answer; and I get: ERROR 1045: Access denied for user '[EMAIL PROTECTED]' (Using password: YES) The command without the INTO OUTFILE '/home/rick/Results/Results.txt' clause works just fine. Where have I missed the bus? Thanks --- Rick Duley School of Engineering Science Murdoch University, Perth, Western Australia aussie :0409 106 049 o'seas : +61 409 106 049 .-_|\ The Main Thing is / \ to keep the Main Thing perth *_.-._/ the Main Thingv (Stephen Covey, 1994) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum for time
i dont think its possible. you can do it by using time_to_sec() function of mysql and then sec_to_time after addition Hope that helps Nitin - Original Message - From: Elly Wisata [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 19, 2004 10:08 AM Subject: Sum for time Hi *, Can somebody show me how to sum for time, my format is hh:mm:ss, need to do sum for the time as we usually sum a field with integer type. But I am not sure sum can use for time format. Thanks in advance ~Elle~ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: *very* strange...
Hi,
- Original Message -
From: Chris Knipe [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, March 20, 2004 12:33 PM
Subject: *very* strange...
Lo everyone,
I'm *baffled* completely I've never seen something like this
before.
: I tried this exact query from PHP, Perl, as well as the MySQL
thingy... They ALL give the same result - it must therefore be my
table
mysql SELECT VERSION();
++
| VERSION() |
++
| 4.0.14-log |
++
1 row in set (0.00 sec)
mysql SHOW TABLES LIKE 'Accounts';
++
| Tables_in_SAV001 (DSLAccounts) |
++
| Accounts |
++
1 row in set (0.00 sec)
mysql SHOW TABLE STATUS LIKE 'Accounts';
+-+++--++-+-
+--+---++-
--
--+-+++---
+
| Name| Type | Row_format | Rows | Avg_row_length | Data_length
|
Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Create_options | Comment |
+-+++--++-+-
+--+---++-
--
--+-+++---
+
| Accounts| MyISAM | Dynamic| 42 | 44 |1888
|
4294967295 | 7168 | 0 | 49 | 2004-03-20
08:53:01
| 2004-03-20 08:54:42 | NULL || Accounts |
+-+++--++-+-
+--+---++-
--
--+-+++---
+
1 row in set (0.00 sec)
mysql SHOW KEYS FROM `Accounts`;
+-++--+--+-+
---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
|
+-++--+--+-+
---+-+--++--++-+
| Accounts| 0 | PRIMARY |1 | EntryID | A
| 42 | NULL | NULL | | BTREE | |
| Accounts| 0 | Username |1 | Username| A
| 42 | NULL | NULL | | BTREE | |
| Accounts| 1 | isActive |1 | isActive| A
|NULL | NULL | NULL | | BTREE | |
| Accounts| 1 | isCapped |1 | isCapped| A
|NULL | NULL | NULL | | BTREE | |
+-++--+--+-+
---+-+--++--++-+
4 rows in set (0.00 sec)
mysql SHOW FULL FIELDS FROM `Accounts`;
+--+--+--+-+-++-
+
| Field| Type | Null | Key | Default | Extra
|
Privileges |
+--+--+--+-+-++-
+
| EntryID | smallint(6) unsigned | | PRI | NULL| auto_increment
|
select,insert,update,references |
| Username | varchar(150) | | UNI | |
|
select,insert,update,references |
| Password | varchar(150) | | | |
|
select,insert,update,references |
| isActive | enum('y','n')| | MUL | y |
|
select,insert,update,references |
| isCapped | enum('y','n')| | MUL | n |
|
select,insert,update,references |
+--+--+--+-+-++-
+
5 rows in set (0.00 sec)
mysql SHOW FIELDS FROM `Accounts` FROM `SAV001`;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra
|
+--+--+--+-+-++
| EntryID | smallint(6) unsigned | | PRI | NULL| auto_increment
|
| Username | varchar(150) | | UNI | |
|
| Password | varchar(150) | | | |
|
| isActive | enum('y','n')| | MUL | y |
|
| isCapped | enum('y','n')| | MUL | n |
|
+--+--+--+-+-++
5 rows in set (0.00 sec)
mysql INSERT INTO Accounts (Username, Password) VALUES
(Username='[EMAIL
Re: Update one field with more fields from another table
UPDATE `Table A`,`Table B` SET `Table A`.`text`=concat_ws('', `Table
B`.`B-num`, from ,`Table B`.`date`) WHERE `Table A`.`A-num` = `Table
B`.`A-num`
Hope it works
Nitin
- Original Message -
From: Wouter Coppieters [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, March 13, 2004 12:55 PM
Subject: Update one field with more fields from another table
Dear mysql user
I found this example in the doc of mysql. I have a similar problem. Is
there any solution to this?
Thanks
Update one field with more fields from another table
Table A
++---+
| A-num | text |
|1 | |
|2 | |
|3 | |
|4 | |
|5 | |
++---+
Table B:
+--+--+--+
| B-num| date| A-num |
| 22 | 01.08.2003 | 2 |
| 23 | 02.08.2003 | 2 |
| 24 | 03.08.2003 | 1 |
| 25 | 04.08.2003 | 4 |
| 26 | 05.03.2003 | 4 |
I will update field text in table A
with
UPDATE `Table A`,`Table B`
SET `Table A`.`text`=concat_ws('',`Table A`.`text`,`Table B`.`B-num`,
from
,`Table B`.`date`,'/')
WHERE `Table A`.`A-num` = `Table B`.`A-num`
and come to this result
Table A
+++
| A-num | text |
|1 | 24 from 03 08 2003 / |
|2 | 22 from 01 08 2003 / |
|3 ||
|4 | 25 from 04 08 2003 / |
|5 ||
+-+
(only one field from Table B is accepted)
But i will come to this result
Table A
+++
| A-num | text |
|1 | 24 from 03 08 2003|
|2 | 22 from 01 08 2003 / 23 from 02 08 2003 / |
|3 ||
|4 | 25 from 04 08 2003 / 26 from 05 03 2003 / |
|5 ||
+++
Department of Genetics
Faculty of Veterinary Medicine
University of Liege
Bd de Colonster B43
B4000 Liege, Belgium
32-43-66.41.59
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update one field with more fields from another table
UPDATE `Table A`,`Table B` SET `Table A`.`text`=concat_ws('', `Table
B`.`B-num`, from ,`Table B`.`date`) WHERE `Table A`.`A-num` = `Table
B`.`A-num`
Hope it works
Nitin
- Original Message -
From: Wouter Coppieters [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, March 13, 2004 12:55 PM
Subject: Update one field with more fields from another table
Dear mysql user
I found this example in the doc of mysql. I have a similar problem. Is
there any solution to this?
Thanks
Update one field with more fields from another table
Table A
++---+
| A-num | text |
|1 | |
|2 | |
|3 | |
|4 | |
|5 | |
++---+
Table B:
+--+--+--+
| B-num| date| A-num |
| 22 | 01.08.2003 | 2 |
| 23 | 02.08.2003 | 2 |
| 24 | 03.08.2003 | 1 |
| 25 | 04.08.2003 | 4 |
| 26 | 05.03.2003 | 4 |
I will update field text in table A
with
UPDATE `Table A`,`Table B`
SET `Table A`.`text`=concat_ws('',`Table A`.`text`,`Table B`.`B-num`,
from
,`Table B`.`date`,'/')
WHERE `Table A`.`A-num` = `Table B`.`A-num`
and come to this result
Table A
+++
| A-num | text |
|1 | 24 from 03 08 2003 / |
|2 | 22 from 01 08 2003 / |
|3 ||
|4 | 25 from 04 08 2003 / |
|5 ||
+-+
(only one field from Table B is accepted)
But i will come to this result
Table A
+++
| A-num | text |
|1 | 24 from 03 08 2003|
|2 | 22 from 01 08 2003 / 23 from 02 08 2003 / |
|3 ||
|4 | 25 from 04 08 2003 / 26 from 05 03 2003 / |
|5 ||
+++
Department of Genetics
Faculty of Veterinary Medicine
University of Liege
Bd de Colonster B43
B4000 Liege, Belgium
32-43-66.41.59
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help on PHP code
WHERE (nickname = '$nickname') and (password = '$password'), $connection);
remember to place quotes around the value
nickname = $nickname wont work but
nickname = '$nickname' will do
Hope that helps
Nitin
- Original Message -
From: electroteque [EMAIL PROTECTED]
To: Togochog Enhebatu [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Saturday, February 28, 2004 2:54 AM
Subject: RE: help on PHP code
well dude u never showed what the parser returned, i am pretty sure u need
curly brackets if u are including more than one line in an if statement
-Original Message-
From: Togochog Enhebatu [mailto:[EMAIL PROTECTED]
Sent: Saturday, February 28, 2004 2:53 PM
To: [EMAIL PROTECTED]
Subject: help on PHP code
hi,
Can any one help find the bug of the following code for me? I am new in
MySQL and PHP, and trying to varify the user ID and password.
help is appreciated.
batu,
!DOCTYPE HTML PUBLIC
-//W3C//DTD HTML 4.0 Transitional//EN
http://www.w3.org/TR/html4/loose.dtd;
html
headtitleCustomer Details/title/head
body bgcolor=green
h1Customer Details/h1
?php
$nickname = $HTTP_POST_VARS['nickname'];
if(empty($nickname))
echo 'brnickname field should not be blank.';
$password = $HTTP_POST_VARS['password'];
if(empty($password))
echo 'brpassword field should not be blank.';
?
?php
$connection=mysql_connect(localhost,csc3223,abdasa);
mysql_select_db(csc3223,$connection);
$result=mysql_query(SELECT nickname,password,status FROM Exam_Provider
WHERE (n
ickname = $nickname) and (password = $password), $connection);
while ($row = mysql_fetch_array($result))
{
echo you have not been approved yet;
}
mysql_close($connection);
?
a href=form.htmlbrbrReturn to the customer form/a
/body
/html
_
Watch high-quality video with fast playback at MSN Video. Free!
http://click.atdmt.com/AVE/go/onm00200365ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: pls help ...............
there must be some problem in concatenation of strings and number variables. If you can send the lines, we'll be able to suggest the exact change. Hope that helps Nitin - Original Message - From: CurlyBraces Technologies ( Pvt ) Ltd To: [EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 12:08 PM Subject: Fw: pls help ... - Original Message - From: CurlyBraces Technologies ( Pvt ) Ltd To: [EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 12:05 PM Subject: pls help ... hi , when i try to get mysql data to the web browser via php , always it is showing Parse error: parse error, unexpected T_LNUMBER, expecting ',' or ';' in /var/www/html/smsc/test1.php on line 14 error message. but i tried to rectify the essage , i couldn't. can some body help me to solve the problem ? thanx in advance curlys -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: run query second time
that's called, cache-hit - Original Message - From: Matt W [EMAIL PROTECTED] To: Mike Mapsnac [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 5:25 AM Subject: Re: run query second time Hi Mike, - Original Message - From: Mike Mapsnac Sent: Monday, February 23, 2004 5:49 PM Subject: run query second time Hello Today I run large query. It took more than 1 minute to start printing the results. The output was about 5 rows. However, when I run the query second time it took a couple of seconds before printing the results. Why is that? Because data still in the RAM? Thanks Yep, that's usually why. :-) Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install problem
are you using MySql 4.x? try only mysql Hope that helps Nitin - Original Message - From: Davut Topcan [EMAIL PROTECTED] To: Maria Yndefors [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 3:37 PM Subject: Fw: install problem Hi, From: Maria Yndefors [EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 11:54 AM I have not set the root password and I get the same result hmm.. You should set root password :) Regards, Jack.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
any ideas about it
Hi all, I m looking for any function or a work around to fetch numerical data without its sign (-10 -- 10, 10 -- 10). Any ideas? Thanx
Re: subqurey syntax error
subqueries are introduced in 4.1 only - Original Message - From: Emmanuel Ohannessian [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 26, 2004 12:10 PM Subject: subqurey syntax error I am getting the error message: mysql SELECT s1 FROM t1 WHERE s1 IN(SELECT s1 FROM t2); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'SELECT s1 FR OM t2)' at line 1 mysql I am using MySQL version 4.0.17, on windows xp professional. Has anyone encountered this problem before? Please help. Emmanuel Ohannessian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function in Where clause
function can b used, but value parameter seems syntactically wrong, check that. '#session.month#' to '$month' which is the language,u r using anyway? - Original Message - From: Yves Arsenault [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 6:37 PM Subject: Function in Where clause Hello, I have a client using a already made calendar component on my server... The problem seems to be the SQL code. This code works (i believe) with an MS Access or MS SQL server DB... MySQL version is 3.23.58 The code is: SELECT * FROM events WHERE day(theDate) = '#dayVal#' and month(theDate) = '#session.month#' and year(theDate) = '#session.year#' Can the day() function be used in this way in the WHERE statement? Is there a better way to achieve this? Thanks, Yves -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dynamic no of columns
Hi everybody, I dont know, if it's been asked before, I was looking for the syntax to fetch dynamic no of column, which are actually the record values. I dont know, if it's possible with mysql as it doesn't yet provide sub-query feature (prod ver). I need to select a column's value as different columns and other fields grouped by those values. Below is the table str., I'll appreciate any suggestions or links to look for. Str. CREATE TABLE presignup ( packno varchar(6) NOT NULL default '', signid varchar(12) NOT NULL default '', signpass varchar(12) NOT NULL default '', plancode varchar(20) NOT NULL default '', reseller varchar(20) NOT NULL default '', PRIMARY KEY (packno) ) TYPE=MyISAM; I want to select distinct values of reseller column as individual columns and other data grouped on these values. Thanx in advance Nitin
Re: is this possible?
thanx for reply but not a good idea. Though i did it through PHP script Nitin - Original Message - From: Mechain Marc [EMAIL PROTECTED] To: Nitin Mehta [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 16, 2004 1:38 PM Subject: RE: is this possible? To drop all tables, yes do a drop database. To drop all with something like a exclude, no. What you can do is an export of the tables you don't want to drop, then do a drop database + create database + import Marc. -Message d'origine- De : Nitin Mehta [mailto:[EMAIL PROTECTED] Envoyé : vendredi 16 janvier 2004 06:28 À : [EMAIL PROTECTED] Objet : is this possible? hello everybody, i was wondering, if there's a single command to drop all or selected tables from the database, something like, where i can give the table names (1-2-3) not to be deleted. Thanx in advance Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table without primary key
it cannot b related with primary key. i do not hav any idea how u update it, but it's supported to update any dat in any table with or without PK - Original Message - From: Hans van Dalen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 15, 2004 6:20 PM Subject: table without primary key Hi group, I had created a table without a primary key (for some reasons). This give some strange errors. When I edit a field trough ODBC he said: no transactions supported. If I edit trough direct access components (DAC for MySQL = native access) nothing is editted at all. With the mysql shell updating trough a Query is no problem. In DAC for MySQL I use a TTable. Does somebody know about problems with tables without primary index? Thanks Hans -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is this possible?
hello everybody, i was wondering, if there's a single command to drop all or selected tables from the database, something like, where i can give the table names (1-2-3) not to be deleted. Thanx in advance Nitin
Re: New grant tables
i dont think that, cauz 'Password' field in 'user' table was always 16 char long and still is. In fact, check for the no. of privs in 'user' table. if it is 21 or 14, as in 3.23.x it was 14 and in 4.0.17 is 21. Hope that helps Nitin - Original Message - From: Director General: NEFACOMP [EMAIL PROTECTED] To: Scott Haneda [EMAIL PROTECTED]; MySql [EMAIL PROTECTED] Sent: Monday, January 12, 2004 2:43 PM Subject: Re: New grant tables Check the User `table` of the `mysql` database If the Password column is 45 characters long, it has changed it. Hope I remember it correctly. Thanks Emery - Original Message - From: Scott Haneda [EMAIL PROTECTED] To: MySql [EMAIL PROTECTED] Sent: Sunday, January 04, 2004 03:37 Subject: New grant tables I just updated from 3.x to 4.x. I moved my data files from one server to another. Srated mysql, and ran the permissions fixer script, which seemed to have worked just fine. However, I am not sure what it changed, how can I be sure that it did in fact update the tables? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which one is better: CHAR or VARCHAR?
If the data is 10-char in length always, CHAR is better performance-wise. - Original Message - From: Hassan Shaikh [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, January 11, 2004 6:34 PM Subject: Which one is better: CHAR or VARCHAR? Hi, I've a column of type VARCHAR(10) where I know the data would be 10-char in length - always. Apart from the fact that VARCHAR saves space as compared to CHAR, is there any performance benefit? If yes, is it significant enough? Thanks. Hassan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Passwords query
these are definitely MySQL connection identities. Each with specific (different) privileges. You can connect to the database directly with these. - Original Message - From: Matthew Stuart [EMAIL PROTECTED] To: MySQL email support [EMAIL PROTECTED] Sent: Monday, January 12, 2004 6:32 PM Subject: Passwords query I have been given these passwords for a MySQL db on a remote server. I have to dump the records to this volume on the remote and I am not sure what these passwords mean to me. SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER username_master password SELECT,INSERT,UPDATE,DELETE username_runtime password SELECT username_read password Do these relate to the connection between the database the website calling on it, or is it for editing directly in MySQL, such as the admin window? Do I have to state these usernames and passwords in the website? I am trying to find info on it, but am not doing too well at present. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting an Access Table to MySQL
you need not write any scripts, just use MySQL Front to Import/Export dat from M$
Access
zzapper [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]
Hi Ya,
How to this has always been somewhat fuzzy in my head. I looked around
for tools unsuccessfully . So I thought I'd share my solution.
I've just written a simple Perl script to do this, this uses DBI:mysql
to write to mysql and DBI:ODBC to read from Access. I suppose it could
be rewritten in PHP as well.
The tables map practically one to one, but the script could easily be
adapted for a more complex conversion. I presume it could be adapted
to do a reverse conversion as well.
Any suggestions/improvements welcome
#!/usr/local/bin/perl
# convert.pl
# description : Copy Access Database via DBI:ODBC to MySQL
# V1.0 07/01/2004
my $dsn=group;
my $dsn_mysql=group;
my $table=group;
my $table_dest=ytbl_agents;
my $db; # database handle
my $db_dest; # database handle
$delete_sql=qq|DROP TABLE IF EXISTS ytbl_agents|;
$recreate_table =qq|
CREATE TABLE ytbl_agents (
intID mediumint(9) unsigned NOT NULL auto_increment,
txtRegion varchar(60) default NULL,
txtCompany varchar(60) default NULL,
txtContact varchar(60) default NULL,
txtAddress text,
txtTown varchar(60) default NULL,
txtCounty varchar(60) default NULL,
txtPostCode varchar(20) default NULL,
txtPhone varchar(60) default NULL,
txtFax varchar(50) default NULL,
txtEmail varchar(60) default NULL,
txtWeb varchar(60) default NULL,
dtmDate timestamp(14) default NULL,
PRIMARY KEY (intID)
) TYPE=MyISAM;
|;
# MMDDHHMMSS
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
localtime(time);
$year+=1900; $mon++;
$mysql_timestamp = sprintf(
%04d%02d%02d%02d%02d%02d,$year,$mon,$mday,$hour,$min,$sec);
#-#
open_access_db();
open_mysql_db();
$cnt=fn_read_access_db();
fn_write_to_mysql();
print \n$cnt records imported to mysql database $dsn_mysql (table
$table_dest)\n;
$db-disconnect();
$db_dest-disconnect();
#-#
sub open_access_db()
{
use DBI;
use DBD::ODBC;
my $emsg=Could not access the Database\n Could not open DSN $dsn;
$db = DBI-connect( dbi:ODBC:$dsn, , ,
{RaiseError = 1,
PrintError = 1,
AutoCommit = 1,
LongReadLen = 4000} ) or
do
{
print ($emsg: . $DBI::errstr .__LINE__.\n);
exit;
}
}
#-#
sub open_mysql_db()
{
use DBI;
use DBD::MYSQL;
my $emsg=Could not access the Database\n Could not open DSN
$dsn_mysql;
$db_dest = DBI-connect( dbi:mysql:$dsn_mysql, , ,
{RaiseError = 1,
PrintError = 1,
AutoCommit = 1,
LongReadLen = 4000} ) or
do
{
print ($emsg: . $DBI::errstr .__LINE__.\n);
exit;
};
### delete table
$sel_dest = $db_dest-prepare( $delete_sql );
$sel_dest-execute() or webdie(\n$delete_sql \n: .$DBI::errstr.
line .__LINE__);
### recreate table
my $sel_dest = $db_dest-prepare( $recreate_table );
$sel_dest-execute() or webdie(\n$recreate_table \n:
.$DBI::errstr. line .__LINE__);
}
#-#
sub fn_read_access_db
{
my $sql=qq|select * from $table |;
my $emsg=$sql;
my $sel = $db-prepare( $sql );
$sel-execute() or webdie($emsg : .$DBI::errstr. line
.__LINE__);
@results=();
while (my $ref=$sel-fetchrow_hashref)
{
push @results, {%$ref}; # array of hashes
undef $ref;
}
return $#results+1;
}
#-#
sub fn_write_to_mysql()
{
my $cols =qq|txtRegion, txtCompany, txtContact, txtAddress, txtTown,
txtCounty, txtPostCode|;
$cols.=qq|, txtPhone, txtFax, txtEmail, txtWeb, dtmDate|;
for my $resid (0 .. $#results)
{
my $ID= $results[$resid]{ID};
my $region=escsql($results[$resid]{region});
my $company=escsql($results[$resid]{company});
my $contact=escsql($results[$resid]{contact});
my $address=escsql($results[$resid]{address});
my $town=escsql($results[$resid]{town});
my $county=escsql($results[$resid]{county});
my $postcode=escsql($results[$resid]{postcode});
my $tel=escsql($results[$resid]{tel});
my $fax=escsql($results[$resid]{fax});
my $email=escsql($results[$resid]{email});
my $web=escsql($results[$resid]{web});
my $vals
=qq|'$region','$company','$contact','$address','$town','$county','$postcode','$tel','$fax','$email','$web'|;
$vals.=qq|,'$mysql_timestamp'|;
#print $cols; print \n.$vals; exit;
my $sql=qq|insert into $table_dest |;
$sql.=qq| ($cols) values ($vals) |;
my $sel_dest = $db_dest-prepare( $sql );
$sel_dest-execute() or webdie(\n$sql \n: .$DBI::errstr. line
.__LINE__);
}
}
#-#
sub escsql()
{
my ($inputstr) = @_ ;
return if
Re: MySQL 4.1 Installation
first download and install Dynamic client libraries (including 3.23.x libraries) available on same page to satisfy the dependencies Enjoy Nitin - Original Message - From: Nikos Gatsis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 6:01 PM Subject: MySQL 4.1 Installation Hello list I'm trying to install MySQL-server-4.1.1-1.i386.rpm on a RH-8 Linux. System after systme check says than need LIBMYSQLCLIENT.SO.10 file to continue. Can somebody help me with this situation please? Thanx in advance Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: odbc
You can just install MyODBC on the client (running MS Access) then you can link tables stored anywhere. have a look at: http://forums.devshed.com/t56700/s.html for more info Hope that helps Nitin - Original Message - From: Matthew McNicol [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 09, 2004 7:04 AM Subject: odbc I have a PHP/MySQL database application running on linux hosted webspace (via webfusion.co.uk). I'd like to make the information accessible from a MS Access database which sits on my clients office server (microsoft os), either by using odbc, or an alternative. This is because the client would like to be able to use MS Access for reporting purposes. I don't think odbc is available via the hosted webspace so I may have to export the data periodically to the MS Access database using cron/scheduling. Any recommendations? Matthew McNicol - yellowmarker.co.uk [information technology] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql by itself?
You dont need Apache to run MySQL. Apache is a web server, which is required to publish web pages. So the answer is yes. Enjoy Nitin - Original Message - From: EP [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 09, 2004 9:53 AM Subject: Mysql by itself? OK, I need to ask a stupid question. I normally run MySQL on Apache. I have a second machine [a laptop] which I can't get Apache to run on [the ports are restricted/closed]. Is there a way to run MySQL without a server like Apache underneath it? I see MySQL coined a database server; have I overlooked that it really is a server by itself? EP asking for a friend, I'm much smarter than this -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: odbc
Oh, if that's the case, I'm sorry for false info. Of course, if server is not accessible, then you cannot connect to it anyway, directly or through Access. Sorry again Nitin - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: Nitin Mehta [EMAIL PROTECTED]; Matthew McNicol [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 09, 2004 10:02 AM Subject: Re: odbc Nitin Mehta wrote: You can just install MyODBC on the client (running MS Access) then you can link tables stored anywhere. have a look at: http://forums.devshed.com/t56700/s.html for more info Hope that helps Nitin I think Matthew meant that his ISPs MySQL installation is firewalled, and so access via MyODBC would be impossible. This makes sense for an ISP to do - I would be a little surprised if I found that my ISP allowed incoming ODBC connections to their database server. If this is in fact the case, Matthew's best bet would be to use mysqldump to dump the data to a comma delimited file, and import that into Access. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to find if the value returned is numeric
Hi all, as the subject of the mail says, is there any function to apply in a query, that finds out if the value returned from the database is numeric? thanx for help in advance Nitin
Re: Table: NULL (yes or no)
- Original Message - From: Mike [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 05, 2004 10:53 AM Subject: Re: Table: NULL (yes or no) I create two tables. And when I do desc table_name; NULL value can be 'Yes' or 'No'. What the difference when NULL value is 'Yes' or 'No NULL Yes, means NULL value is allowed NULL No, means NULL value is not allowed Enjoy Not sure what you are asking, but if you have an ENUM column that can be Yes or No and you do not set the column to NOT NULL then there are three possible values: Yes, No, and NULL. NULL simply means no value. Sometimes it is appropriate, sometimes it is not. In the case of a Yes/No ENUM column I would say that in most cases the column should be set NOT NULL. Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete logs: not enough space on /var/lib/mysql
move the data files to another directory (partition) and create symlinks in original directory - Original Message - From: Mike Mapsnac [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 03, 2004 7:42 PM Subject: Delete logs: not enough space on /var/lib/mysql I don't have enough space on /var/lib/mysql partition. What logs can be deleted from the partition? Is it possible don't delete last logs? Thanks _ Worried about inbox overload? Get MSN Extra Storage now! http://join.msn.com/?PAGE=features/es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is It Possible To Change the Value of A Particular Field Manually?
you can optionally use MySQL Front to view and edit data in GUI mode get it free of cost athttp://www.mysqlfront.de/ Enjoy Nitin - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Caroline Jen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 11:28 AM Subject: Re: Is It Possible To Change the Value of A Particular Field Manually? Caroline Jen wrote: Hi, I am not a database person. I have a database called members. One of the fields in this database is user_name. There are a number of records in this database. Under the field user_name, I would like to make some changes manually; for example, I want to change John Doe to john_doe Is it possible to do it? How do I do it? Thank you very much in advance. Yes. You can specify the records you want with a WHERE clause. First try a SELECT to test your WHERE clause to make sure you get just the record(s) you want: SELECT * FROM members WHERE user_name = 'John Doe'; As long as that looks right, then use UPDATE to modify the record(s): UPDATE members SET user_name='john_doe' WHERE user_name = 'John Doe'; See http://www.mysql.com/doc/en/UPDATE.html in the manual for more. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to create mysql user?
dear buddy,
when u specify permissions on a particular database, you need to give that
database name to connect to db.
user created fine, while connecting, try this:
mysql -u abc -p abc
where last abc is ur database name.
Nitin
- Original Message -
From: ads mysql [EMAIL PROTECTED]
To: Nitin [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 11:51 AM
Subject: Re: How to create mysql user?
O K.
I accessed to mysql as root user and tried to created user 'abc' as folows
:
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
I want to give all permission to user abc to handle database 'abc'.
Then I tried to connect as user 'abc' to mysql as follows :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Some problem.
Help appreciated.
Nitin [EMAIL PROTECTED] wrote:
right now, u r trying to connect t omyql with uer abc, not creating it.
to create user abc
grant
on dbname.tablename to 'abc' identified by 'abc';
you can view the list of privileges available on myql website at
http://www.mysql.com/doc/en/GRANT.html
Enjoy
Nitin
- Original Message -
From: ads mysql
To:
Sent: Tuesday, December 16, 2003 11:09 AM
Subject: How to create mysql user?
Hi,
I have installed mysql. As per documentation with user as 'root'.
I can create Database, table. I can enter data into table.
I have created user 'abc' with passowrd 'abc' on my Linux 8.0 server.
by using command :
adduser abc -p 'abc'
Now I want to create mysql database user 'abc' with command :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password: (entered abc)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Is it granting permission to abc. I refered documentation but could not
get it clearly Neet sample code.
Then I connected mysql as 'root' user. and tried.
mysql mysql -u abc -p;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresonds to your MySQL server version for the right syntax to use near
'mysql -u ab -p' at line 1.
Syntax I got from Documentation for GRANT as follows:
###
4.4.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
###
Please guide me how to create users for mysql with password.
Thanks for support.
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to create mysql user?
did u do a
flush privilege
- Original Message -
From: ads mysql [EMAIL PROTECTED]
To: Nitin [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 1:00 PM
Subject: Re: How to create mysql user?
O K. I have tried following as advised.
#
[EMAIL PROTECTED] mysql]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
##
Then once again I accessed mysql as root user and Granted permission as
follows :
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 69 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON abc.* TO
[EMAIL PROTECTED] IDENTIFIED BY 'abc';
Query OK, 0 rows affected (0.01 sec)
mysql USE abc
Database changed
mysql exit
Bye
[EMAIL PROTECTED] mysql]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Don't know what is wrong.
Thanks for support.
Nitin [EMAIL PROTECTED] wrote:
dear buddy,
when u specify permissions on a particular database, you need to give that
database name to connect to db.
user created fine, while connecting, try this:
mysql -u abc -p abc
where last abc is ur database name.
Nitin
- Original Message -
From: ads mysql
To: Nitin ;
Sent: Tuesday, December 16, 2003 11:51 AM
Subject: Re: How to create mysql user?
O K.
I accessed to mysql as root user and tried to created user 'abc' as
folows
:
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
I want to give all permission to user abc to handle database 'abc'.
Then I tried to connect as user 'abc' to mysql as follows :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Some problem.
Help appreciated.
Nitin wrote:
right now, u r trying to connect t omyql with uer abc, not creating it.
to create user abc
grant
on dbname.tablename to 'abc' identified by 'abc';
you can view the list of privileges available on myql website at
http://www.mysql.com/doc/en/GRANT.html
Enjoy
Nitin
- Original Message -
From: ads mysql
To:
Sent: Tuesday, December 16, 2003 11:09 AM
Subject: How to create mysql user?
Hi,
I have installed mysql. As per documentation with user as 'root'.
I can create Database, table. I can enter data into table.
I have created user 'abc' with passowrd 'abc' on my Linux 8.0 server.
by using command :
adduser abc -p 'abc'
Now I want to create mysql database user 'abc' with command :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password: (entered abc)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Is it granting permission to abc. I refered documentation but could
not
get it clearly Neet sample code.
Then I connected mysql as 'root' user. and tried.
mysql mysql -u abc -p;
ERROR 1064: You have an error in your SQL syntax. Check the manual
that
corresonds to your MySQL server version for the right syntax to use near
'mysql -u ab -p' at line 1.
Syntax I got from Documentation for GRANT as follows:
###
4.4.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
###
Please guide me how to create users for mysql with password.
Thanks for support.
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: How to create mysql user?
- Original Message -
From: Nitin [EMAIL PROTECTED]
To: ads mysql [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 2:01 PM
Subject: Re: How to create mysql user?
did u do a
flush privilege
- Original Message -
From: ads mysql [EMAIL PROTECTED]
To: Nitin [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 1:00 PM
Subject: Re: How to create mysql user?
O K. I have tried following as advised.
#
[EMAIL PROTECTED] mysql]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
##
Then once again I accessed mysql as root user and Granted permission as
follows :
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 69 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON abc.* TO
[EMAIL PROTECTED] IDENTIFIED BY 'abc';
Query OK, 0 rows affected (0.01 sec)
mysql USE abc
Database changed
mysql exit
Bye
[EMAIL PROTECTED] mysql]# mysql -u abc -p abc
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Don't know what is wrong.
Thanks for support.
Nitin [EMAIL PROTECTED] wrote:
dear buddy,
when u specify permissions on a particular database, you need to give
that
database name to connect to db.
user created fine, while connecting, try this:
mysql -u abc -p abc
where last abc is ur database name.
Nitin
- Original Message -
From: ads mysql
To: Nitin ;
Sent: Tuesday, December 16, 2003 11:51 AM
Subject: Re: How to create mysql user?
O K.
I accessed to mysql as root user and tried to created user 'abc' as
folows
:
[EMAIL PROTECTED] mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61 to server version: 4.0.16-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
I have created database 'abc' .
mysql grant ALL on abc.table to 'abc' identified by 'abc';
Query OK, 0 rows affected (0.00 sec)
I want to give all permission to user abc to handle database 'abc'.
Then I tried to connect as user 'abc' to mysql as follows :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Some problem.
Help appreciated.
Nitin wrote:
right now, u r trying to connect t omyql with uer abc, not creating
it.
to create user abc
grant
on dbname.tablename to 'abc' identified by 'abc';
you can view the list of privileges available on myql website at
http://www.mysql.com/doc/en/GRANT.html
Enjoy
Nitin
- Original Message -
From: ads mysql
To:
Sent: Tuesday, December 16, 2003 11:09 AM
Subject: How to create mysql user?
Hi,
I have installed mysql. As per documentation with user as 'root'.
I can create Database, table. I can enter data into table.
I have created user 'abc' with passowrd 'abc' on my Linux 8.0
server.
by using command :
adduser abc -p 'abc'
Now I want to create mysql database user 'abc' with command :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password: (entered abc)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
Is it granting permission to abc. I refered documentation but could
not
get it clearly Neet sample code.
Then I connected mysql as 'root' user. and tried.
mysql mysql -u abc -p;
ERROR 1064: You have an error in your SQL syntax. Check the manual
that
corresonds to your MySQL server version for the right syntax to use
near
'mysql -u ab -p' at line 1.
Syntax I got from Documentation for GRANT as follows:
###
4.4.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
###
Please guide me how to create users for mysql with password.
Thanks for support.
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED
Re: How to create mysql user?
right now, u r trying to connect t omyql with uer abc, not creating it.
to create user abc
grant privs on dbname.tablename to 'abc' identified by 'abc';
you can view the list of privileges available on myql website at
http://www.mysql.com/doc/en/GRANT.html
Enjoy
Nitin
- Original Message -
From: ads mysql [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 11:09 AM
Subject: How to create mysql user?
Hi,
I have installed mysql. As per documentation with user as 'root'.
I can create Database, table. I can enter data into table.
I have created user 'abc' with passowrd 'abc' on my Linux 8.0 server.
by using command :
adduser abc -p 'abc'
Now I want to create mysql database user 'abc' with command :
[EMAIL PROTECTED] mysql]# mysql -u abc -p
Enter password: (entered abc)
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
Is it granting permission to abc. I refered documentation but could not
get it clearly Neet sample code.
Then I connected mysql as 'root' user. and tried.
mysql mysql -u abc -p;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresonds to your MySQL server version for the right syntax to use near
'mysql -u ab -p' at line 1.
Syntax I got from Documentation for GRANT as follows:
###
4.4.1 GRANT and REVOKE Syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
###
Please guide me how to create users for mysql with password.
Thanks for support.
-
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where to find MySQL-shared-compat-VERSION.i386.rpm ?
Look at http://www.mysql.com/downloads/mysql-4.0.html for Dynamic client libraries for your platform. Enjoy Nitin - Original Message - From: Franz Edler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 05, 2003 6:25 PM Subject: Where to find MySQL-shared-compat-VERSION.i386.rpm ? Hello, Please forgive me my newbee-question: Where can I find MySQL-shared-compat-VERSION.i386.rpm ? I have just installed MySQL 4.0 and an application requests libmysqlclient.so.10 Franz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Date column type
no problems that i see. - Original Message - From: p shah [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 12:23 PM Subject: Mysql Date column type Hi, I have one question regarding date column type in MYSQL. Can I use the column type for date as Integer instead of DateTime or Date to store the date as mmddhhmmss or mmdd? As I know MYSQL accepts this format. But I am not too sure about the column type. What are the pros and cons for using Integer column type for the date? Please reply at the earliest. Regards, __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client does not support authentication protocol requested by server
r u getting this problem while coonecting through MySql Front or directly to the server? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 3:23 PM Subject: Client does not support authentication protocol requested by server Hello there Could anyone help me with this error message ?? Warning: mysql_pconnect(): Client does not support authentication protocol requested by server. Consider upgrading MySQL client What could be wrong ?? My preferences: FreeBSD 5.1 mysql-client-4.1.0 mysql-server-4.1.0_1 php4-4.3.4.r1 apache-1.3.29 Regards Bård Tommy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Records From the Database
is this declared in php or javascript?? depends on the syntax. - Original Message - From: Caroline Jen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 9:38 AM Subject: Select Records From the Database I got null after selecting records from a table in the database. The table is created by me. I know how many records will be selected. Just cannot be null. I cannot help wonder if my SELECT statement is correct - I want records to be selected if user_role is equal to the userrole I supplied AND if journal_category is equal to the category I supplied. My statement is: String query = SELECT user_name FROM members WHERE user_role = ' + userrole + ' AND journal_category = ' + category + '; Do anybody see any mistake? __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date data type issue
now() gives current date and time. use current_date() instead. hope it helps Nitin - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 9:32 PM Subject: Date data type issue Having a problem with a Date data type. Here's the query: SELECT distinct date_format(auditdate, %Y%M ) as listUrl, date_format( auditdate, %Y%m ) as blank FROM quality_history WHERE auditdate Now() ORDER BY listUrl desc LIMIT 6 auditdate is a DATE datatype When I run this query the result datatype for both listUrl and blank is a BINARY CHAR! Is that correct? Why a BINARY? Also, when I take out the DISTINCT I get back a TIMESTAMP (pretty weird, huh?). I'm obviously missing something. Any ideas on how I can return just a regular CHAR (or TEXT, or VARCHAR, or DATE, or anything that looks like a DATE with the format of %Y%M)? By the way, I'm running 3.23.51 on Windows 2000 Thanks in advance! T.J. Kuhn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: administering database on server
In my opinion MySql front is best and the best thing is further development of already enriched software started again. Nitin - Original Message - From: Colleen Dick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 8:42 PM Subject: Re: administering database on server phpmyadmin is not reliable enough? joffrey leevy wrote: Hi all: Anyone know a RELIABLE software program for administering to a mysql database on a webhosting unix server? Thanks __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how exactly can i use unicode in mysql 4.0.14
hi all, I've searched a bit, but didn't find any documentation on how to use unicode in mysql 4.0.14 or 4.0.X. Any ideas or links? Thanx in advance Nitin
Re: NEWBIE!! need help with nested select query
Subqueries aren't supported in MySql prior to ver 4.1. - Original Message - From: Becky OGrady [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 15, 2003 12:28 PM Subject: NEWBIE!! need help with nested select query Hi- I have 2 queries that I know work in SQL Server, but I can't figure out why they won't work with mySQL. SELECT email, comments, (select count(*) from guests where decision=3) as total, (select count(*) from guests where decision=2) as secondtotal FROM guests AND SELECT email, comments, NULL, NULL FROM guests UNION SELECT NULL, NULL, (SELECT count(*) from guests where decision=3) as total, (SELECT count(*) from guests where decision=2) as secondtotal FROM guests The guests table structure: guest_id int, PK, autoincrement email varchar(100) comments varchar(250) decision int Any help would be greatly appreciated!! Thanks, Becky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BRU Help
Hi all, I'm sorry, I know this is not the list, to ask this question, but I couldn't find the right one. I'm having trouble with BRU. I'm running it for quite a time but it has started throwing errors. Main problem is, though there's plenty of free space on my tape media, it says, Insert vol 2 and press enter Insert vol 3 and press enter ... Dont know, what to do. I googled on it, but no specific answer. If you have a solution or direct me to some helpful info, I'll be very gratefull. It's really important, so sorry again for querying at wrong place. Regards Nitin
Does MySQL support hindi language
Hi all, I have a project in hand, where I need to accept and show data in Hindi language, storage doesn't matter. Is that possible with MySQL, if yes, what do I need to configure? How do I do it, any help or ideas? Regards Nitin
Re: minus operation
In oracle, intersect will work for you better than minus. as it shows data from both tables which doesn't exist in another one. anyway in mysql, you can try this: select A.* from A left join B on (f1, f2, f3, f4) where B.f1 is null and B.f2 is null and B.f3 is null and B.f4 is null; hope it helps Nitin - Original Message - From: fab [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 1:58 PM Subject: minus operation Hi to all, My question is rather about sql langage than mysql. Here it is: i have 2 tables (A and B) with the same structure (4 fields called f1,f2,f3,f4). Table A counts x records and table B counts y records (and x y). Then, table A has records that do not belong to table B and table B has records that do not belong to table A. My question is: how to get the records that belongs to A and not to B and the records that belongs to B and not to A. If i was using oracle, i guess i could use the minus sql command. More over, i don't think i can use a simple left join because i have to verify the 4 fields and not only the first one. If anyone could answer, it would be great. Actualy, i lost lots of records and i try to rebuild my table with the help of the savings of this table. f. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? The most important thing is I deleted a 300M database, but the ibdata1 remains the same size. MyPHPAdmin says 330,000KB free. How can I make the data file smaller? I will be really appreciated if someone can briefly describe what's happening to those files or point me to some articles. Thanks a lot, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
You're right, it wont decrease the physical size, but only free up the space within file to optimize the tablespace, in case, you want to check the size of this data file, you can remove autoextend from: innodb_data_file_path = ibdata1:10M:autoextend and specify the size limit in the place of 10M, but i guess, if you specify the size to less than 790M (which is the current size of your datafile), to say 500M and the space is free in that file, it will resize it. That's the behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it, and let me know as i dont have my database on innodb yet. 'Tablespace is part of your database. database consists of at least one tablespace. it's basically used to restrict users from seeing other user's data. like, you can assign a tablespace to a user and none else (ofcourse other than root) can see the data. For more info, have a look at: http://www.mysql.com/doc/en/InnoDB_File_space.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 8:00 PM Subject: Re: InnoDB Questions -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you very much for your reply, Nitin. I did read the Adding_and_removing in the manual, but it says Currently you cannot remove a datafile from InnoDB. To decrease the size of your database you have to use `mysqldump' to dump all your tables, create a new database, and import your tables to the new database. It does reduce the size of the database(similar to optimize for MyISAM), but it doesn't reduce the size of the file. Could you explain a bit what is a tablespace? Thanks, Leo /etc/my.cnf: [mysqld] datadir=/var/lib/mysql set-variable=max_connections=300 innodb_data_file_path = ibdata1:10M:autoextend default-table-type=InnoDB set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_log_file_size=10M set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 [mysql.server] user=mysql basedir=/usr [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - - Original Message - From: Nitin [EMAIL PROTECTED] To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 1:01 AM Subject: Re: InnoDB Questions Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? The most important thing is I deleted a 300M database, but the ibdata1 remains the same size. MyPHPAdmin says 330,000KB free. How can I make the data file smaller? I will be really appreciated if someone can briefly describe what's happening to those files or point me to some articles. Thanks a lot, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: PGP 8.0.2 iQA/AwUBP6e4GMJH0J7PNsMYEQIXIQCdGgQEyxFfJ3Vk8wZBNIz7FT7ilF8AoIDN h21IQZ8ozOUeELhvWSpznyTI =H/2E
Re: InnoDB Questions
not all the log files will work together, i mean, these files work in cyclic fashion, one fills up, second one gets used, it fills up, second one, and then back to first one. so it wont keep getting bigger, it will overwrite the previous data when it comes back to that file. in oracle, if you have data more than the size you're specifying, it'll through error and wont do nothing. Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: Nitin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 5:12 AM Subject: Re: InnoDB Questions Hello Nitin, From the timestamp of the log files, it seems that the first two files works together while the last one seems just sitting there, doesn't do anything. Also, will the log files getting bigger and bigger in the future?? If so how should I deal with them? For your last suggestion, what will Oracle do if I specify a datafile size less than the database size, e.g. I specify 20M in the my.cnf while there are actually 400M of data in my database? Leo Nitin wrote: Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? The most important thing is I deleted a 300M database, but the ibdata1 remains the same size. MyPHPAdmin says 330,000KB free. How can I make the data file smaller? I will be really appreciated if someone can briefly describe what's happening to those files or point me to some articles. Thanks a lot, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Questions
yea, he's right, it may be error log file Nitin - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: Leo Huang [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 5:53 AM Subject: Re: InnoDB Questions The last one you're referring to - could it be the error log? The log files will only grow to a pre-determined limit. These log files are used to ensure that transactions maintain their durability. With Oracle, you'd want to be careful. Oracle gets very, very picky about the stuff underneath it when it's running. If it gets to a configuration limit, the results can be very, very depressing. Having to extend tablespaces by hand is a very common Oracle DBA task. Regards, Chris Leo Huang wrote: Hello Nitin, From the timestamp of the log files, it seems that the first two files works together while the last one seems just sitting there, doesn't do anything. Also, will the log files getting bigger and bigger in the future?? If so how should I deal with them? For your last suggestion, what will Oracle do if I specify a datafile size less than the database size, e.g. I specify 20M in the my.cnf while there are actually 400M of data in my database? Leo Nitin wrote: Hello, first things first, you cann't resize your datafiles without shutting down your database. if it's ok with you, have a look at http://www.mysql.com/doc/en/Adding_and_removing.html you may want to have a look at you my.cnf file, stored in mysql data dir or in /etc dir, for the default options specified there fo the datafile with: innodb_data_file_path Yes, you can add data file, just add another entry to above option. option entry is self-explainatory. At last, ibdata1, ibdata2 are actual data files used to store actual data. one or more of these files are attached to one tablespace and one file cant span across tablespaces. ib_logfile0, ib_logfile1 are log files, which are used to log sql statements applied to database. these files are used to restore data in case of any crash or mishap. for further info, have a look at: http://www.mysql.com/doc/en/InnoDB_start.html Enjoy Nitin - Original Message - From: Leo Huang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 6:28 PM Subject: InnoDB Questions Hello, I have a few questions about InnoDB. I am new to InnoDB, and just converted my MyISAM tables into InnoDB a few days ago. I notice that it generates these files -rw-rw1 mysqlmysql2560 Nov 2 13:07 ib_arch_log_00 -rw-rw1 mysqlmysql790626304 Nov 4 20:44 ibdata1 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile0 -rw-rw1 mysqlmysql10485760 Nov 4 20:44 ib_logfile1 -rw-rw1 mysqlmysql10485760 Nov 3 00:02 ib_logfile2 But how can those files work together? In InnoDB documentation, it suggests to add another file ibdata2 to get higher performance. Can I do that now, after I have created the ibdata1 and used it for a while? The most important thing is I deleted a 300M database, but the ibdata1 remains the same size. MyPHPAdmin says 330,000KB free. How can I make the data file smaller? I will be really appreciated if someone can briefly describe what's happening to those files or point me to some articles. Thanks a lot, Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inserting values via a SELECT statement
yes, syntax is bit diff. INSERT into table1(column list) select a, b, c, d from table2 where condition); Enjoy Nitin - Original Message - From: Lay Hoon Tan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 11:36 AM Subject: Inserting values via a SELECT statement Is there any way that SQL can accomplish the insertion of values through a select statement, eg, INSERT into table1 VALUES ( select a, b, c, d from table2 where condition); Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adapting a Select statement to MySQL
query seems perfect, what error is this giving? Nitin - Original Message - From: Jim Bartram [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 10:07 AM Subject: Adapting a Select statement to MySQL I've got the following select statement that is defeating me! select title_id, ytd_sales from titles where ytd_sales between 4095 and 12000; The WHERE clause is not working... How should this be done in MySQL? Thanks for any help, -Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving databases
that will be recognized very well, just check the file ownership after copying directory and files. all the data directory and files should be owned by mysql only Nitin - Original Message - From: [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 2:23 PM Subject: moving databases Ok, I've set my root password and have found (by creating a db) that the databases are stored (Mac OSX Server 10.3) in /private/var/mysql. I have my old system on a separate hard disk; however, mysql is not functioning there, but I do have my databases there. Can I copy these database to their new home? How will MySQL recognize them? (What is the best way in my situation to accomplish this move? (Again, MySQL is not available to 'run' where they are.) Thanks in Advance! TR -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help out
Easy, use:'select * INTO OUTFILE full path and file name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY \n from user where race='chinese' and state='US'Nitin- Original Message - From: Pey Ling [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:51 PM Subject: please help out Dear all, Hi, i have a database called 'admin' and a table called 'user'. Inside table 'user', there are a lot of fields, i.e: name, email, race, state, and etc. There are totally 10 records. When I wrote the sql query 'select * from user where race='chinese' and state='US''. It shows me more than 1000 results. Now, how can I save only these 1000++ data into a file in my pc? Please help out. Thanks. best regards, Pey Ling -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alternate syntax for UNION?
you can use temporary tables if need to work on the resultset else use two different queries. I think, that's the only option. Nitin - Original Message - From: Victor Spng Arthursson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 2:59 PM Subject: Re: alternate syntax for UNION? 2003-10-26 kl. 16.56 skrev Victor Spng Arthursson: Desperately need some way to get around the fact that UNION is not possible to use on mysql prior to version 4 Was just about to launch a site when it showed up that the hosting provider was running version 3. Help! Come on now - don't make me change to postgreSQL ;) /.v -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help out
no, it'll save it in the data directory of the database only, reason is mysql doesn't have permission to write to any other directory. full path and file name could be just file name like data.txt or full path of that directory like /var/lib/mysql/db_name/data.txt Nitin - Original Message - From: Pey Ling [EMAIL PROTECTED] To: Nitin [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 3:31 PM Subject: Re: please help out Hi, how to write this :full path and file name? can i copy it to my local pc rather than the server? best regards, Pey Ling From: Nitin [EMAIL PROTECTED] To: Pey Ling [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 5:09 PM Subject: Re: please help out Easy, use:'select * INTO OUTFILE full path and file name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY \n from user where race='chinese' and state='US'Nitin- Original Message - From: Pey Ling [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 1:51 PM Subject: please help out Dear all, Hi, i have a database called 'admin' and a table called 'user'. Inside table 'user', there are a lot of fields, i.e: name, email, race, state, and etc. There are totally 10 records. When I wrote the sql query 'select * from user where race='chinese' and state='US''. It shows me more than 1000 results. Now, how can I save only these 1000++ data into a file in my pc? Please help out. Thanks. best regards, Pey Ling -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User and permissions/grants - HELP!
I accept, that i was wrong, but you need to have a better look too. ALL is as good as ALL PRIVILEGES. - Original Message - From: Patrik Fimml [EMAIL PROTECTED] To: Nitin [EMAIL PROTECTED]; Victoria Reznichenko [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 6:23 PM Subject: Re: User and permissions/grants - HELP! i dont think there's any privilege called ALL PRIVILEGES. You need to say just ALL, like: GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY '12345678' WITH GRANT OPTION; GRANT ALL ON *.* TO newuser@% IDENTIFIED BY '12345678' WITH GRANT OPTION; Maybe you should read the documentation before saying such nonsense? There's no ALL, but of course the ALL PRIVILEGES. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ancestry program
Thanks for all your help, but my main problem now is, how to show the relationships. I mean, OK, I trace the names in my database, but how to show the relationship to the user, searching for it? Should I draw a complete tree that means all the people involved between the actual two, or I dont really understand it. Any ideas? Anyway thanx again for your great help. Nitin - Original Message - From: Brad Teale [EMAIL PROTECTED] To: 'Dan Greene' [EMAIL PROTECTED]; Nitin [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 1:39 AM Subject: RE: Ancestry program It has been a while since I have looked, but I believe the National Genealogical Society has a data model for family tree software. The following links are to the NGS and GEDCOM is the file format standard. I think it should be an easy conversion to a database structure. If you do something that exports the data, it should probably export in the GEDCOM format because that is what most software packages will import. http://www.ngsgenealogy.org/ http://www.gentech.org/ngsgentech/main/Home.asp GEDCOM seems to be the standard file format: http://www.gendex.com/gedcom55/55gctoc.htm Brad -Original Message- From: Dan Greene [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:49 PM To: Nitin; [EMAIL PROTECTED] Subject: RE: Ancestry program well... when I do db design, I tend to start with the objects of my system. The one that comes to mind in your case is people. so you'll need a people table. well what are the details of a person? first_name Last_name Middle_name1 Middle_name2 Maiden_name [any other basic bio data] so you'll need those columns Well to keep track of each person, each one will need an ID... id's are usually numbers, so now you add a: person_id field. This field would likely have an auto_increment attribute to help number them for you ok... now that we have people, what else do we need? relationships between them well... in terms of human beings, everyone has one biological mother and one biological father, so we add in mother_id father_id leaving the values of these as null would be equivalent of being 'unknown' and we now have, data-wise, a system that can trace biological heritage, can handle siblings and half-siblings. Other ideas for objects: Marrages - this one would be tricky/interesting, as marrages can change over time, and people can have multiple marrages (although usually not two at a time, unless bigamy is allowed in your user's state/country). Strictly speaking, marrages are not necessary to trace heritage, but are good info... --From: Nitin [mailto:[EMAIL PROTECTED] --Sent: Monday, October 27, 2003 10:46 PM --To: [EMAIL PROTECTED] --Subject: Ancestry program -- --Hi all, -- --I'm developing a web based ancestry program. The user wants it to be --static, that means, it isn't for all to use, but his family. Better to --say, it'll contain only his family tree. -- --Now, I cant think of the proper db design, which will help any user to --find his or her relationship with any other person in the tree. Though, I --can design a simple database, where everything will have to be done --through queries and scripts, but I want those queries to keep as simple --as possible. -- --Any help will be appreciated, as I'm new to such a problem. -- --Thanx in advance --Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User and permissions/grants - HELP!
i dont think there's any privilege called ALL PRIVILEGES. You need to say just ALL, like: GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY '12345678' WITH GRANT OPTION; GRANT ALL ON *.* TO newuser@% IDENTIFIED BY '12345678' WITH GRANT OPTION; Enjoy Nitin - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 7:43 PM Subject: Re: User and permissions/grants - HELP! C. Reeve [EMAIL PROTECTED] wrote: I just installed MySQL version 4 and as usual, trying to set up users and grant permissions is a nightmare. I followed the instructions in the MySQL manual and granted permissions to a user, like below: GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY '12345678' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO newuser@% IDENTIFIED BY '12345678' WITH GRANT OPTION; I can log in with this user, but I can't do anything (i.e. create a database). There is also no mysql database (there is though if I log in as root). Could you connect to the MySQL server? What exactly error messages did you get? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing rpm installation to binary version
you can either erase the previous installation and then install the newer one or simply upgrade the previous one. - Original Message - From: Manjit Patel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:02 PM Subject: Changing rpm installation to binary version Hello, I have been testing with an RPM distribution of MySQL version 4.0.15. Now that I have bought a licenced version how do I change over to use the binary (version 4.0.16) instead of the previously installed rpm version? Operating system: Linux Red Hat 7.3 Thanks Manjit This message is confidential and for use by the addressee only. If you are not the correct addressee, then kindly return the message to the sender and delete it from your computer. Internet communications are not secure and TraderMade accepts no responsibility for changes made to this message after it was sent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert ... Select question
of course, the syntax is: insert into db3.table2 (column list) select column list from db1.table1 for more information have a look at http://www.mysql.com/doc/en/INSERT_SELECT.html Enjoy Nitin - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:28 PM Subject: Insert ... Select question Hello list I want to insert ... select data from table1 of db1 to table2 of db3. Is that possible? Thank in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing table properties
u mean, in MySQLFront or other program - Original Message - From: Krystan Daxner [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:46 PM Subject: Changing table properties I have a program running with MySQL as the database. I have MySQLFront running as my viewer and I want to change something in the database that will allow me to view more items in a drop down table within my program. Not having created this program but being a user on this system, I am not sure what would I look for to change this set up. Right now I am only allowed to view 40 odd items before it doesn't scroll down anymore. I have more items in the specific area but am unable to acess them. Could you offer any info to me as to where or what to look for? Any assistance is helpful as I am new and teaching myself as I go along. Thanks in advance! Krystan _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unexpected results from query between tables
You are using the wrong syntax, try SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; The query, you are using produces cross join while you need to implement left join for your problem. Enjoy Nitin - Original Message - From: Larry Brown [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:54 PM Subject: unexpected results from query between tables I apparently am misunderstanding how the select works by referencing data in two different tables. I have used a similar statement to the one that follows with success, but there must be something different here that reveals a lack of fundamental understanding as to how it works. If someone could help, please check the following... I have two tables. One table has entries controlnum,referencenum,fname,lname,inputtime,outputtime the second table has controlnum,referencenum. In table one referencenum can have and does have duplicates. The second table is populated with a subset of data from the first table but referencenum is unique. For instance... 1234 677 'bob' 'smith' '10:00:00' '11:00:00' 1234 677 1235 677 'mike' 'williams' '10:00:00' '11:00:00' 1236 5554447 1236 5554447 'debra' 'stone' '10:30:00' '11:30:00' 1237 446 1237 446 'ken' 'marwood' '11:00:00' '12:00:00' 1238 5585888 1238 5585888 'bill' 'shireton' '11:15:00' '11:15:00' 1239 5585888 'laura' 'acree' '11:15:00' '12:15:00' 1240 5585888 'dora' 'lindsey' '11:15:00' '12:15:00' ok, now I want to run a query that results in all of the controlnum's in table one that are not in table two. The query I ran was select f.controlnum,f.referencenum,f.fname,f.lname from first f,second s where f.controlnum != s.controlnum and f.inputtime '07:00:00' the results I get back are such as... 1234 677 'bob' 'smith' 1234 677 'bob' 'smith' 1235 677 'mike' 'williams' 1235 677 'mike' 'williams' 1235 677 'mike' 'williams' 1235 677 'mike' 'williams' 1236 5554447 'debra' 'stone' and so on... I apparently, ignorantly, thought I would get only those records to which the controlnum was not in both tables and which had an inputtime that is greater than 7 which would not filter any more out in this example. Also, this is for explination purposes. The actual tables are much larger, the only fields that actually exist on the first table to the actual table are control,ref,lname,fname and the second table has quite a few other fields that do not exist in the first table. I just simplified things to find out where my understanding fails for the logic behind the query. Thanks for any help. Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ancestry program
Hi all, I'm developing a web based ancestry program. The user wants it to be static, that means, it isn't for all to use, but his family. Better to say, it'll contain only his family tree. Now, I cant think of the proper db design, which will help any user to find his or her relationship with any other person in the tree. Though, I can design a simple database, where everything will have to be done through queries and scripts, but I want those queries to keep as simple as possible. Any help will be appreciated, as I'm new to such a problem. Thanx in advance Nitin
Re: error message
Starting from MySQL 4.0.., you have two new privileges named
Create_tmp_table_priv AND Lock_tables_priv, that's the reason, why you
aren't able to enter the values without specifying column name.
You can either specify column names like Rob told you or give two more
values in your syntax like:
INSERT INTO host
VALUES('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','
Y','Y');
Enjoy
Nitin
- Original Message -
From: Rob [EMAIL PROTECTED]
To: 'Gregory Norman' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, October 17, 2003 2:26 PM
Subject: RE: error message
Try naming the columns in the insert explicitly, like so:
insert into `mysql`.`host`
(Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv,
Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv
)
values
('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
-Original Message-
From: Gregory Norman [mailto:[EMAIL PROTECTED]
Sent: Friday, October 17, 2003 10:32 AM
To: [EMAIL PROTECTED]
Subject: error message
Hello,
I am new to mysql trying to learn by following the instructions from a
trainning book. While following the instructions from the book that I am
using I got the following error message, without the quotes, Column
count doesn't match value count at row 1. I entered the following
command,
INSERT INTO host
VALUES('localhost','database_name','Y','Y','Y','Y','Y','Y','Y','Y','Y','
Y');
According to the book that I am using this command should add the
hostname/database combination to the MySQL RDBMS.
I have searched for this error message unsuccessfully at MySQL's
website. If someone would explain to me what I am doing wrong, or where
I can find information that will help me get past this point I would
appreciate it. BTW I am using version 40.0.14.
Gregory Norman
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OR in MySQL statement?
it could better be like
SELECT * FROM list WHERE name in ('$this1', '$this2', '$that1')
Nitin
- Original Message -
From: Colleen Dick [EMAIL PROTECTED]
To: Mike At Spy [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 10:14 AM
Subject: Re: OR in MySQL statement?
Mike At Spy wrote:
How could I write a statement that uses something like 'OR' as a case.
Like
this:
SELECT * FROM list WHERE name = '$this1' OR name = '$this2' OR name =
'$that1'
?
Thanks for any help,
-Mike
u just like that looks good to me.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What provides libmysqlclient.so?
Mysql-shared-compat available at http://www.mysql.com/downloads/mysql-4.0.html for more information take a look at http://www.mysql.com/doc/en/Linux-RPM.html Enjoy Nitin - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Randy Chrismon [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, October 16, 2003 9:07 AM Subject: Re: What provides libmysqlclient.so? On Wed, Oct 15, 2003 at 10:46:59PM -0400, Randy Chrismon wrote: I'm trying to upgrade my KDE setup but it fails on a dependency for libmysqlclient.so. I've looked at all the MySQL rpm files and none of them seem to provide this file. Does anyone know where this one comes from? Probably a MySQL client package. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 32 days, processed 1,192,662,580 queries (429/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nasty Time Upgrading to 4.0 RPM dependencies - libmysqlclient.so.10 and php
Dear Mr. Singh, You need to install Mysql-shared-compat available at http://www.mysql.com/downloads/mysql-4.0.html for more information take a look at http://www.mysql.com/doc/en/Linux-RPM.html Enjoy Nitin - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 16, 2003 1:12 PM Subject: Nasty Time Upgrading to 4.0 RPM dependencies - libmysqlclient.so.10 and php Hello All! I really need a hand here. I'm volunteering at this NGO (non-profit org) in New Delhi. I and another developer built a .NET/MySQL app for managing the communications department. The app worked sweet on our machines, but when we moved it to the server and ran a UNION query (one of many) BANG! So We need to upgrade from 3.something to 4.0. It's a Cobolt Server, which I have no experience with, but everything was installed with RPM. I got the client and devel packages installed okay using rpm -Uvh --replacefiles but the server won't install because of 2 dependencies: libmysqlclient.so.10 is needed by mod_auth_mysql-1.11-1 libmysqlclient.so.10 is needed by php-mysql-4.1.2-7 Now I've done my research and it seems that MySQL4 has a lib file called libmysqlclient.so (no 10) and this needs to be used, so I've got to upgrade these packages somehow. I have no idea how to do this without building source RPMS (and even that I'm not sure about). Unfortunately, I don't have RPM build on this puter and it requires 15 major dependencies and I'm on a 2k line. This tape will self destruct in 3 seconds. BOOM PLEASE we're a really cool org and we need your help! address: http://deepalaya.org Jacob Singh mail2web - Check your email from the web at http://mail2web.com/ . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PASSWORD() function problem
all encryption functions are one way only
Nitin
- Original Message -
From: Manisha Sathe [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 6:46 PM
Subject: Re: PASSWORD() function problem
thanks all, it works (i just increase it to 20)
but one more thing, now if i want to get this password (e.g for option
forget password), can we retrieve, i believe we can not- just want to
confirm
manisha
- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: Manisha Sathe [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, October 15, 2003 11:05 PM
Subject: Re: PASSWORD() function problem
At 17:26 +0800 10/15/03, Manisha Sathe wrote:
Hi,
I inserted one record thr PHPMyAdmin - mem_pass field of member
table set to xyz using function 'PASSWORD'
Then trying to select the same - select * from member where mem_pass
= PASSWORD('xyz') - then it is not getting selected
I do not know why I am not getting the result. please help me.
what's going wrong ?
Thanks in advance
Manisha
Please consider using something other than PASSWORD(), as noted in the
description for that function at:
http://www.mysql.com/doc/en/Miscellaneous_functions.html
PASSWORD() should not be used for your own applications.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
