Analysis of a weeks worth of general log
I have 7 days worth of general log data totalling 4.4GB. I want to analyze this data to get: a) queries per second, minute, hour and day b) a count of the number of selects versus write statements (delete, insert, replace and update) c) a variation of the above with select, replace, delete and insert versus update How can I do this? I've looked at mysqlsla which is complex, works well but does not quite get what I want. [1] I looked at MyProfi 0.18 which looks like it will get some of the answers but runs out of memory working on the smallest log file (mysql.log) even with memory_limit in php.ini set to 1024MB [2] -rw-r- 1 imran imran 268M 2010-04-19 13:03 mysql.log -rw-r- 1 imran imran 721M 2010-04-19 12:56 mysql.log.1 -rw-r- 1 imran imran 737M 2010-04-19 13:05 mysql.log.2 -rw-r- 1 imran imran 554M 2010-04-19 13:06 mysql.log.3 -rw-r- 1 imran imran 499M 2010-04-19 13:02 mysql.log.4 -rw-r- 1 imran imran 568M 2010-04-19 12:59 mysql.log.5 -rw-r- 1 imran imran 488M 2010-04-19 13:01 mysql.log.6 Any pointers please? If all else fails, I will prolly write a perl script to munge it. [1] http://hackmysql.com/mysqlsla [2] http://myprofi.sourceforge.net -- GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Analysis of a weeks worth of general log
Minor correction: The post i point to is about the slow log, but I presume also relevant for the general log. And the good comments I mentioned come in the followup posting at http://www.bitbybit.dk/carsten/blog/?p=116 / Carsten Thanks Carsten, I read the comments and Sheeri mentions mysqlsla which I have already tried. Back to square one. I might look at munin again and see if someone has written a plug-in that graphs query type but that seems too much hassle for my situation. I have the raw data and I want the appropriate tool to analyze it. Part of the reason is that the data is from a MyISAM based web app and I am writing a report recommending it be moved to a transactional storage engine. AIUI a rule of thumb is that if between 15% - 20% of statements are non SELECT/INSERT then one can obtain equal or better performance with something like InnoDB. That being said, the benefits of InnoDB (good recovery features, transactions, advanced indexes, foreign key contraints) make it a good default choice and I will recommend it anyway. Plus we have order processing stuff going on and it seems right to have atomicity in that process. It would be a bit better though to confidently state that the query-mix skews it towards InnoDB... if I can only prove it :-) -- GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication breakage when Heartbeat Failover occurs
I want to fix a replication issue with a 2-node cluster (one active, one passive) that is using Heartbeat for failover. The nodes are in Master-Master configuration (that is, each is the slave and master of the other). I have several other hosts that are replication slaves from the active node. They connect to MySQL via TCP over an SSH tunnels. When failover occurs, the passive node becomes the active node. However the replication slaves stop replicating. The error from a log on one of the slaves is: Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [Note] Slave I/O thread: conn ected to master 'user@127.0.0.1:3307', replication started in log 'mysql-bin.00 0978' at position 23923243 Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( serve r_errno=1236) Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000978', position 23923243 I do not think this is an SSH tunnel issue. I believe this is because of inconsistent binary log file names and positions between the two nodes. Probably because one of the nodes had been in operation a lot longer than the other. At the moment I have to get replication going by dumping the master databases again, re-import to the slave hosts and bootstrap the slaves. What is the best way to make this consistent and ensure that replication continues smoothly after a failover (and failback) event? Thank you, Imran Chaudhry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[RFH] Full Text search
Dear Users,
I am facing a problem related to full text search. I am trying to
search non latin characters with no success :(.
I am trying the following queries for searching and only the English one works.
SELECT * FROM bangla_test WHERE MATCH(bn_test) AGAINST('নাম নাই' in
boolean mode)
SELECT * FROM bangla_test WHERE MATCH(bn_test) AGAINST('test' in boolean mode)
SELECT * FROM bangla_test WHERE MATCH(bn_test) AGAINST('日系人' in boolean mode)
The dump of the database is following this paragraph. I would be
grateful if someone could be point me what I am doing wrong.
-- MySQL dump 10.11
--
-- Host: localhostDatabase: test
-- --
-- Server version 5.0.38-Ubuntu_0ubuntu1.2-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `honeybase`
--
/*!4 DROP DATABASE IF EXISTS `test`*/;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT
CHARACTER SET latin1 */;
USE `honeybase`;
--
-- Table structure for table `bangla_test`
--
DROP TABLE IF EXISTS `bangla_test`;
CREATE TABLE `bangla_test` (
`id` int(11) NOT NULL auto_increment,
`bn_test` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `bn_full_text` (`bn_test`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `bangla_test`
--
LOCK TABLES `bangla_test` WRITE;
/*!4 ALTER TABLE `bangla_test` DISABLE KEYS */;
set autocommit=0;
INSERT INTO `bangla_test` VALUES (1,'নাম
নাই'),(2,'test'),(3,'日系人'),(4,'another test'),(5,'dingdong');
/*!4 ALTER TABLE `bangla_test` ENABLE KEYS */;
UNLOCK TABLES;
commit;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
-- Dump completed on 2008-02-27 5:41:23
Thank you,
--
Imran M Yousuf
Entrepreneur Software Engineer
Smart IT Engineering
Dhaka, Bangladesh
Email: [EMAIL PROTECTED]
Mobile: +880-1711402557
Help with this query
Hi: I have a table that I need help to summarize the data. I need to be able to create one row of data for custno + prodno + period + weekno combination summarized by Invtot. Any help will be greatly appreciated. Best regards I am attaching the relevant info below: Sales Table CustNo ProdNo Period WeekNo InvTot InvDate Data - CustNo ProdNo Period WeekNoInvTotInvDate 0001 0500 2007-01 01 111.11 2007/01/01 0001 0500 2007-01 01 112.11 2007/01/01 0001 0500 2007-01 01 113.11 2007/01/02 0001 0500 2007-02 01 114.11 2007/02/02 0001 0500 2007-02 01 115.11 2007/02/02 0001 0500 2007-02 01 116.11 2007/02/02 0001 0500 2007-04 21 117.11 2007/04/08 0001 0500 2007-04 21 118.11 2007/04/08 0001 0500 2007-04 21 119.11 2007/04/08 0001 0501 2007-05 25 111.11 2007/05/01 0001 0501 2007-05 25 112.11 2007/05/01 0001 0501 2007-05 25 113.11 2007/05/02 0001 0501 2007-05 25 114.11 2007/05/02 0001 0501 2007-06 30 115.11 2007/06/02 0001 0505 2007-06 30 116.11 2007/06/02 0001 0505 2007-06 30 117.11 2007/06/08 0001 0506 2007-06 30 118.11 2007/06/08 0001 0507 2007-06 30 119.11 2007/06/08
Need help with a sql query
Hello all: I have two tables: 1. MenuAccess: accessId fkMenuId fkGroupid View Execute 2. MenuOptions MenuId MenuName I would like to get all of the records in MenuOptions and any record in MenuAccess with a specific fkGroupid. For example: A. MenuAccess AccessId fkMenuID fkgroupid view execute 1 1 2 1 1 2 2 2 1 0 3 3 2 0 1 4 1 1 1 1 B. MenuOptions Menuid MenuName 1 Order Maintenance 2 Customer Maintenance 3 Reports IIf I run a query for fkgroupid = 1 I should get AccessId fkMenuID fkgroupid view execute MenuId MenuName 1 1 2 1 1 1 Order Maintenence Null Null Null Null Null 2 Customer Maintenence Null Null Null Null Null 3 Reports Any help will be appreciated. Thanks
Re: Interesting SQL Query - Total and Grouped Counts together?
Baron, Thanks very much for that simple but very effective solution. I altered your SQL slightly, the final SQL looks like this: SELECT domain, count(*) AS 'count all', SUM(IF(mime = 'text/html', 1, 0)) AS 'count text', SUM(IF(mime LIKE 'image/%', 1, 0)) AS 'count image' FROM tableA GROUP BY domain ORDER BY domain Thanks again, Imran Chaudhry -- Atom Database A Custom Database Designed for Your Business [EMAIL PROTECTED] http://atomdatabase.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Interesting SQL Query - Total and Grouped Counts together?
I'm wondering if any of you can assist with an interesing SQL query. I have a single table within a database, the relevant fields of which are defined as: CREATE TABLE tableA ( domain text, mime text ); Where domain is a domain, such as: google.com emeraldcity.oohisntitgreen.com teddybears.com An example of rows might be: google.com, text/html google.com, image/gif google.com, image/jpeg google.com, text/html teddybears.com, text/html teddybears.com, image/png google.com, text/html google.com, image/png ... mime is defined as having entries such as: text/html image/png image/jpg image/gif application/x-tar What I am after doing with this table is writing an SQL query which produces a count of all domains where the mime is equal to text/html and next to that, a total count for that domain where the mime type is image/* -- so for example, I might expect the returned resultset to look like: Domain domaincount Mimecountimages - google.com120 12 emeraldcity. 200 40 teddybears.com 11 2 So far, we've considered and tried using a same-table join, various group-by and rollup ideas, but am unable to come up with anything which will produce the above in ONE row for each domain. Any advice and assistance would be great! -- Atom Database A Custom Database Designed for Your Business [EMAIL PROTECTED] http://atomdatabase.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error: Duplicate entry 'xxxx-L' for key 6
Key 6 relates to the 6th key, or index, in the CREATE TABLE statement for this table: PRIMARY KEY (`prod_id`), UNIQUE KEY `prod_no` (`prod_no`), KEY `products_index1` (`prod_status`), KEY `products_index2` (`prod_start_date`,`prod_end_date`), KEY `on_sale` (`on_sale`), FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`), FULLTEXT KEY `prod_no_2` (`prod_no`) Key is another name for Index, so this is about your FULLTEXT index: FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`) I noticed that the INSERT you listed was for the products table and the CREATE TABLE was for the web_products table. Just to be sure, please can you post the CREATE TABLE statement for the products table? Im -- http://www.atomdatabase.com MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to Relational Model a Score
Right guys, I have a score that can be just ONE of several types eg. the score can be a time eg. 01:56.78 (does MYSQL 5.0 now show the fractional part?) or a count eg. 56, 31 etc or a count and a subcategory (just a bit of text) eg. (56, Variety), (31, No Weapon) A score can belong to just one category. A score has one and just one player. What is the best way to model the score? Ideally I want to be able to join score, player and category so I can get a flat list of scores for a player and the category, regardless of how the score is typed. My bodge solution is to have a score table thus: score: score id category id player id type time char(8) - type of 'time' in 5.0.16 does not show the fractional part :-/ count subcategory Thus for the scores where there is just a time, then type=1 and count and subcategory are NULL. Where the score is a count and subcategory, then type=2 and time is NULL. I want to aim for 3NF and this seems ugly solution, there must be an elegant way to do this? Thanks, Imran Chaudhry -- http://www.atomdatabase.com MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIMIT behavior in Stored Procedure
A while back, someone asked if a stored procedure could take a parameter that can be used within a statement as a value for LIMIT. I have a solution using Prepared Statements and placeholders. Prepared Statements with placeholders are available from 5.0.7. Anyway, here goes using the standard MySQL world database as an example: DELIMITER // CREATE PROCEDURE list_cities (IN city_limit INT) BEGIN SET @city_limit = city_limit; PREPARE STMT FROM 'SELECT * FROM City LIMIT ?'; EXECUTE stmt USING @city_limit; END // DELIMITER ; mysql call list_cities(1); ++---+-+--++ | ID | Name | CountryCode | District | Population | ++---+-+--++ | 1 | Kabul | AFG | Kabol|178 | ++---+-+--++ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql call list_cities(5); +++-+---++ | ID | Name | CountryCode | District | Population | +++-+---++ | 1 | Kabul | AFG | Kabol |178 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | +++-+---++ 5 rows in set (0.00 sec) Not sure how useful that is. Maybe it is useful if one is frequently querying a certain table when working on the command line? Imran Chaudhry -- http://www.atomdatabase.com MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy
Good stuff Peter, can you tell us your solution so folks can benefit from your efforts? (I expect the regex has to be POSIX style such as: mydb./'~(expirations|rtt)'/ right?) Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation Issue
On 2/14/06, Ravi Kumar [EMAIL PROTECTED] wrote: Starting mysql with root.I tried withn mysql user account also but still same error. thanks Ravi, Assuming you are starting MySQL with mysqld_safe, then it will invoke the MySQL server as the mysql user. I suspect the cause is that /var/lib/mysql is not owned by mysql If so, as superuser: chown -R mysql:mysql /var/lib/mysql Regards, Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL PDA Synch
Thoughts, comments and advice very appreciated Adrian, just in case your question is out of scope for this crowd... MySQL have a number of forums and one is 'Embedded MySQL' http://forums.mysql.com/list.php?58 Hope that helps, Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data entry GUI
On 2/1/06, THUFIR HAWAT [EMAIL PROTECTED] wrote: Just as there's MySQL Query Browser for queries and table design, is there a similar GUI front end for data entry? I'm not building anything complex, just a simple database to muck with. Is it easier to do data entry on a spreadsheet, then import the spreadsheet? Seems a bit silly, but it seems to be either that or I can't recommend a GUI data entry tool but the way I would do it is to use a spreadsheet and save out as Tab-delimited format. Depending on the data I might also rustle up a Perl program to create the data automatically. I would then use LOAD DATA INFILE statement or mysqlimport to get the data into my tables. These methods are extremely quick for inserts and will disable key updating etc. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sudden Mysql Crashes - table damage?
On 1/31/06, Adrian Bruce [EMAIL PROTECTED] wrote: HI MySQL 5.0 (windows XP) I have a problem where i think some of my tables have somehow been corrupted, if i try to run a check table command against some tables the mysql service crashes and i have to restart it. I am not sure how this damage has occurred but has anyone got any suggestions of what to apart from dropping the tables? everything i have done so far has caused the service to crash which makes it pretty hard to find out what is wrong?!?!?!?! Ade, What ahppens when you perform a mysqldump of the database that contains the corrupts table(s)? Is the process bailing part way through? I've seen people recover from certain corruption issues by dumping the database and recreating from scratch. At any rate, you should take a back-up of either the MyISAM data and .frm files (use BACKUP TABLE) or a mysqldump before starting any repair procedure. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert performance
I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7 millin records. How do I make my insert run faster.The table has three foreign key references and the referencing columns are indexed . Is that impacting the insert statement performance. Just thought I'd add a little to the good advice already given to you. I find an easy way to derive the extended insert syntax is to perform a mysqldump of a small table. From 4.1 onwards this wrapped the table dump in the extended insert syntax. Regards, Imran -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How NOT to log SHOW INNODB STATUS in the query log.
Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. I noticed the same in the logs of a 4.1 test server. I put it down to MySQL Administrator which was monitoring the server at the time. I believe MYSQL Admin issues these SHOW STATUS commands periodically to refresh it's status info screen. Out of curiosity were/are you using MySQL Administrator or something like a monitoring app with your MySQL servers? Im -- http://www.ImranChaudhry.info MySQL Database Management Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: mysql-plain Digest 24 Jan 2006 10:16:15 -0000 Issue 3629
My web hosting server runs PHP 4.4 and MySQL 4.1. But
PHP 4.4 doesn't fully support MySQL 4.1. and i have
problems restoring a utf8 encoded database dumped from
a MySQL 4.0 server to the new 4.1 server. Do i have
any chance to get it working in this setup or should i
persuade the hosting provider to either upgrade to PHP
5 so i can use Connection Character Sets and
Collations in PHP or downgrade to MySQL 4.0?
Lionel,
Please give us more specific information about the nature of the
problem you are having (eg error messages, codes, log file output).
The password hashing algorithm changed in MySQL 4.1 which is possibly
the cause of your problems with PHP client apps.
Try updating the password for your MySQL database account as follows:
SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
Now use these credentials to make a connection at parse some simple
table data. It should work.
Regards,
Im
--
http://www.ImranChaudhry.info
MySQL Database Management Design Services
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help in joining three tables
Hello All: I need to join three tables but I am not sure how to structure the query. I need to join table1 to table2 and then join table3 to this result set. So like (table1 join table2) join table3. Table1 and Table2 will be joined on ProdNo,CustNo and Branch. Table3 will be joined to the result set by CustNo and Branch. Best regards Imran. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lost Connection executing query
The fact that on your Mac installation you re-imported your table data makes me think it's an index efficiency issue. I have seen MySQL just lose the connection if the join becomes massively big. On re-import, the indexes would have been rebuilt and your query would be able to take advantage of the freshest indexes. A bit crude, but on your Production box can may need to do the following on every table in your UPDATE: - drop and create every index - perform ANALYZE TABLE table (this will help if we ask you to perform EXPLAIN later on) Ideally you have a pre-Production environment with the same database (non-replicated). I would recommend trying the re-index tactic there first if you havne't already. Hope that Helps, Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: logging issue
I'm inclined to think this is a database config issue in MailScanner. Double check the Port setting that MailScanner is using and check that against the port that your MySQL server binds to. It is normally 3306, and they should match. If you don't know how to check, can you send me the output of: netstat -tan | grep 3306 Your log output shows an error code of 110 which means 'Connection timed out'. This is different to 'Connection refused'. Is there a possibility of very high database activity from another source while MailScanner is trying to get at the database? How you tried invoking the MailScanner connection at different times of the day? What interface does MailScanner use to connect? It looks like Perl DBI, but you may be using ODBC? Finally, can you tell me your perl version by: perl -v Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REVOKE GRANT OPTION - is that possible?
# mysqlmgr add database www1 # mysqlmgr add user www1 password xxx # mysqlmgr allow user www1 database www1 read write define I suppose in some cases this is OK, and I'm sure you will test thoroughly! :-) One thing to bear in mind is that if you define a user and then revoke his privileges, his account details details will still persist in the user Grant table. This may not be a worry to you since he cannot get at any database resource. To remove any trace of him, you must delete him from the user table. (if you're playing with MySQL 5.0 then DROP USER is the ticket). Final thing, as you're a MySQL admin type writing an access control app, you want to know when the priv changes take effect. You will probably find this info useful: http://dev.mysql.com/doc/refman/5.0/en/privilege-changes.html Hope that helps, Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need to upgrade
Yes, potentially there are gotchcas. It all depends on your current MySQL server version and the version you are upgrading to. Assuming you are upgrading 3.23 to 4.0, what I would do is look at the official documentation regarding upgrades from exactly this scenario: http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html I think you'll find the upgrade pretty painless from a data point of view, but you will need to heed the changes to the GRANT tables. Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installed modules
Not sure what you mean by 'using MySQL to query your installed modules', sorry. phpinfo() called from a HTML document is one way, but from a command line level, you can do this is you have the php CLI installed: PHP: php -m Perl: If you want to test if a specific module is installed, a way is: perl -Mmodule name -e '' (where module name is perlish name like DBI or XML::Simple) It should return silently without errors if it is installed. Hope that helps, Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REVOKE GRANT OPTION - is that possible?
Yes, and from database and table level too, but you must revoke GRANT OPTION seperately from all other privs like this: REVOKE GRANT OPTION ON database.tableFROM user GRANT and REVOKE are essential to your database security, I would think twice before adding a layer on top of them. There is potential for error and you dont want that where security is related. It is probably worth the pain of learning the ins and outs of GRANT/REVOKE. Hope that Helps, Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install trouble, perl DBI
Lewis, As you already have perl installed, an easier way of getting modules is by way of CPAN (www.cpan.org). Give this a try, as superuser: cpan install DBI Regards, Imran -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Thank you ... Help on writing a sql statement
Hi Shawn: Just wanted to publicly thank you for the time you took to help me. I think that it is important that guys like yourself who take time out from your busy work be appreciated when their solution made a huge difference. I was developing an ASP application along with Crystal reports for a customer which was reporting from million of rows of data and my original approach resulted in very poor performance .. reports being VERY sluggish (45 mins to run some). I followed your concept and the same report that took 45 mins to run took 40 seconds Unfortunately I could not implement it using MySql because the current version of MySql ODBC (3.51) did not expose the stored procedures to CR and I could not find an OleDb provider that will work for MySql. However, I ended up using Sql Server but I followed you suggestion and gained tremendous performance improvements. Keep up the good work. Best regards Imran Solution --- Let me see if I can translate what you want in a query into regular language. I think you would like to see, grouped by date, customer, and product, the total cost and total sales for each (date,customer,product) triple along with each product's description ,code, and the customer's number and name. All of that will be limited to activity on or before midnight of a certain date. If I rephrased that correctly, here is how I would build your query. Step 1 is to perform the (date,customer,product) summations. By minimizing the number of rows, columns, and/or tables we need to summarize against, we improve performance. So I do this part of the analysis before I join in the other tables. Note: Date, time, and datetime literals are represented by single-quoted strings. You do not need the DATE() function to create a date literal. CREATE TEMPORARY TABLE tmpTotals ( key(CustNo) , key(ProdNo) ) SELECT PostingDate , CustNo , ProdNo , sum(Cost) as costs , sum(Sales) as sales FROM salesmaster WHERE PostingDate = '2005-09-01 00:00:00' GROUP BY PostingDate, CustNo, ProdNo; Step 2: collect the rest of the information for the report. SELECT CustNo , c.Name as custname , ProdNo , p.Name as prodname , costs , sales , PostingDate FROM tmpTotals tt LEFT JOIN customerintermediate c ON c.CustNo = tt.CustNo LEFT JOIN productintermediate p ON p.ProdNo = tt.ProdNo ORDER BY ... your choice... ; Step 3: The database is not your momma. Always clean up after yourself. DROP TEMPORARY TABLE tmpTotals; And you are done! The only trick to doing a sequence of statements in a row (like this) is that they all have to go through the same connection. As long as you do not close and re-open the connection between statements, any temp tables or @-variables you create or define remain in existence for the life of the connection. Depending on your connection library, you might be able to execute all three statements from a single request. Most likely, you will need to send them in one-at-a-time. Does this help you to organize your thoughts? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Help on writing a sql statement
Hi Shawn: Thank you very much for your solution. It certainly helped me in understanding SQL a whole deal more. I Have one followup question as to the proposed solution, This query will be used in a multi-user situation and the logon to the database will be the same user (a web based app) ... Since I am creating a temp table, will the temp table creation fail for subsequent users prior to the drop i.e. the table will exist already exist best regards Imran. - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 11, 2005 9:49 AM Subject: Re: Help on writing a sql statement (my response bottom-posted. See below - SG) - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 10, 2005 4:17 PM Subject: Re: Help on writing a sql statement Imran [EMAIL PROTECTED] wrote on 10/10/2005 03:52:21 PM: Hi all: I need some help in writing a sql statement. I have three tables (Sales, Cust and Product). The sales table contains a large volume of data and I want to create a sql to group the sales table then join the resultant to both the Cust and Prod and to have additional fields selected from the Cust and Prod. So in effect something like (obviously syntax is wrong) Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales, sm.date from Sales SM where sm.date = date(‘2005-09-01 00:00:00’) Group by sm.prodno, sm.custno, sm.date , (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod) left join sm.prodno = prod.code left join sm.custno=cust.custno) … Any help would be greatly appreciated. OK, you know you need a GROUP BY, that's good. You also recognized you needed to JOIN a few tables together, also good. There are at least two ways to do what you ask. One is a fairly complex query that does it all in one statement (might take a long time to compute) the other is a sequence of two simpler statements. I think the two-statement solution will be easier to understand and maintain so I would prefer to go over that. However, in order to provide an example of either method I will need more information from you. From the CLI (command line client), please provide the output from these three commands: SHOW CREATE TABLE sales\G; SHOW CREATE TABLE cust\G; SHOW CREATE TABLE product\G; That will tell me exactly which columns live on which tables and where you do or do not have any indexes. Good indexes will make or break the performance of your database. You will not be exposing any data, only the design of the tables. Please remember to CC the list on all responses. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Let me see if I can translate what you want in a query into regular language. I think you would like to see, grouped by date, customer, and product, the total cost and total sales for each (date,customer,product) triple along with each product's description ,code, and the customer's number and name. All of that will be limited to activity on or before midnight of a certain date. If I rephrased that correctly, here is how I would build your query. Step 1 is to perform the (date,customer,product) summations. By minimizing the number of rows, columns, and/or tables we need to summarize against, we improve performance. So I do this part of the analysis before I join in the other tables. Note: Date, time, and datetime literals are represented by single-quoted strings. You do not need the DATE() function to create a date literal. CREATE TEMPORARY TABLE tmpTotals ( key(CustNo) , key(ProdNo) ) SELECT PostingDate , CustNo , ProdNo , sum(Cost) as costs , sum(Sales) as sales FROM salesmaster WHERE PostingDate = '2005-09-01 00:00:00' GROUP BY PostingDate, CustNo, ProdNo; Step 2: collect the rest of the information for the report. SELECT CustNo , c.Name as custname , ProdNo , p.Name as prodname , costs , sales , PostingDate FROM tmpTotals tt LEFT JOIN customerintermediate c ON c.CustNo = tt.CustNo LEFT JOIN productintermediate p ON p.ProdNo = tt.ProdNo ORDER BY ... your choice... ; Step 3: The database is not your momma. Always clean up after yourself. DROP TEMPORARY TABLE tmpTotals; And you are done! The only trick to doing a sequence of statements in a row (like this) is that they all have to go through the same connection. As long as you do not close and re-open the connection between statements, any temp tables or @-variables you create or define remain in existence for the life of the connection. Depending on your connection library, you might be able to execute
Re: Help on writing a sql statement
Hi Shawn: I tried to run the sequences as you suggested in MySql Query Browser. I ran the first part with the create temp table then I ran the second select to see the result but when I tried the second SQL to get the created rows I get the message 'Table .tmpTotals doesn't exist' where =schema name. In additon, THANK you for taking the time to clarify the confusion about connections .. you are a wealth of information. best regards Imran - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 11, 2005 11:33 AM Subject: Re: Help on writing a sql statement Will you have name collisions with the same temporary table names used from different connections? Nope. Temporary tables and user-defined (@-variables) are connection-specific. Even if the same username/password combination is used to create several connections, each connection will have its own set of user-defined variables and temporary tables. What may get confusing is if you are using a connection pool manager (like ODBC) and you pick up a connection that you thought was new but was really just recycled. When you request a connection and close one and you are behind a connection pool manager, the manager doesn't actually create and destroy new connections each time. It merely loans you one it already has open and it will open or close the connections as it sees fit. When you try to close the connection, all you are really doing is telling the manager that it is OK for some other thread/process to use it. So long as you do not assume a variable to have a particular value unless you set it to be something (do not assume that a variable you haven't set is still null) and so long as you destroy any temporary tables when you are through using them, you shouldn't run into any inheritance problems from thread to thread. The good thing is that in the case of ODBC (at least on Win32) you can decide for each driver if you want the ODBC connection manager to pool connections or not. Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Imran [EMAIL PROTECTED] wrote on 10/11/2005 11:19:20 AM: Hi Shawn: Thank you very much for your solution. It certainly helped me in understanding SQL a whole deal more. I Have one followup question as to the proposed solution, This query will be used in a multi-user situation and the logon to the database will be the same user (a web based app) ... Since I am creating a temp table, will the temp table creation fail for subsequent users prior to the drop i.e. the table will exist already exist best regards Imran. - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 11, 2005 9:49 AM Subject: Re: Help on writing a sql statement (my response bottom-posted. See below - SG) - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 10, 2005 4:17 PM Subject: Re: Help on writing a sql statement Imran [EMAIL PROTECTED] wrote on 10/10/2005 03:52:21 PM: Hi all: I need some help in writing a sql statement. I have three tables (Sales, Cust and Product). The sales table contains a large volume of data and I want to create a sql to group the sales table then join the resultant to both the Cust and Prod and to have additional fields selected from the Cust and Prod. So in effect something like (obviously syntax is wrong) Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales, sm.date from Sales SM where sm.date = date(‘2005-09-01 00:00:00’) Group by sm.prodno, sm.custno, sm.date , (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod) left join sm.prodno = prod.code left join sm.custno=cust.custno) … Any help would be greatly appreciated. OK, you know you need a GROUP BY, that's good. You also recognized you needed to JOIN a few tables together, also good. There are at least two ways to do what you ask. One is a fairly complex query that does it all in one statement (might take a long time to compute) the other is a sequence of two simpler statements. I think the two-statement solution will be easier to understand and maintain so I would prefer to go over that. However, in order to provide an example of either method I will need more information from you. From the CLI (command line client), please provide the output from these three commands: SHOW CREATE TABLE sales\G; SHOW CREATE TABLE cust\G; SHOW CREATE TABLE product\G; That will tell me exactly which columns live
Help on writing a sql statement
Hi all: I need some help in writing a sql statement. I have three tables (Sales, Cust and Product). The sales table contains a large volume of data and I want to create a sql to group the sales table then join the resultant to both the Cust and Prod and to have additional fields selected from the Cust and Prod. So in effect something like (obviously syntax is wrong) Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales, sm.date from Sales SM where sm.date = date(‘2005-09-01 00:00:00’) Group by sm.prodno, sm.custno, sm.date , (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod) left join sm.prodno = prod.code left join sm.custno=cust.custno) … Any help would be greatly appreciated. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date: 10/10/2005
Re: Help on writing a sql statement
Hi Shawn .. Thanks for the offer. I am attaching the info that you requested. mysql SHOW CREATE TABLE salesmaster\G; *** 1. row *** Table: salesmaster Create Table: CREATE TABLE `salesmaster` ( `ID` int(10) NOT NULL auto_increment, `PostingDate` datetime default NULL, `PostingDateJulian` double(15,5) default NULL, `CustNo` varchar(10) default NULL, `ProdNo` varchar(5) default NULL, `SalesMan` char(2) default NULL, `Branch` int(10) default NULL, `Kilos` decimal(19,4) default '0.', `Sales` decimal(19,4) default '0.', `Cost` decimal(19,4) default '0.', PRIMARY KEY (`ID`), KEY `PostingDate` (`PostingDate`), KEY `CustNo` (`CustNo`), KEY `ProdNo` (`ProdNo`), KEY `Branch` (`Branch`) ) mysql SHOW CREATE TABLE customerintermediate\G; *** 1. row *** Table: customerintermediate Create Table: CREATE TABLE `customerintermediate` ( `ID` int(10) NOT NULL auto_increment, `CustNo` varchar(10) default NULL, `CustName` varchar(120) default NULL, `CustShipAdd1` varchar(50) default NULL, `CustShipAdd2` varchar(50) default NULL, `CustShipAdd3` varchar(50) default NULL, `CustShipPostal` varchar(50) default NULL, `Dept` int(10) default NULL, `Class` int(10) default NULL, `Branch` int(10) default NULL, PRIMARY KEY (`ID`), KEY `CustNo` (`CustNo`), KEY `Branch` (`Branch`), KEY `Dept` (`Dept`) ) mysql SHOW CREATE TABLE productintermediate\G; *** 1. row *** Table: productintermediate Create Table: CREATE TABLE `productintermediate` ( `ID` int(10) NOT NULL auto_increment, `ProdNo` varchar(5) default NULL, `Description` varchar(255) default NULL, `Status` smallint(5) default NULL, `Branch` int(10) default NULL, `Source` int(10) default NULL, `Main` int(10) default NULL, `Report` int(10) default NULL, PRIMARY KEY (`ID`), KEY `ProdNo` (`ProdNo`), KEY `Branch` (`Branch`) ) - Original Message - From: [EMAIL PROTECTED] To: Imran [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, October 10, 2005 4:17 PM Subject: Re: Help on writing a sql statement Imran [EMAIL PROTECTED] wrote on 10/10/2005 03:52:21 PM: Hi all: I need some help in writing a sql statement. I have three tables (Sales, Cust and Product). The sales table contains a large volume of data and I want to create a sql to group the sales table then join the resultant to both the Cust and Prod and to have additional fields selected from the Cust and Prod. So in effect something like (obviously syntax is wrong) Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales, sm.date from Sales SM where sm.date = date(‘2005-09-01 00:00:00’) Group by sm.prodno, sm.custno, sm.date , (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod) left join sm.prodno = prod.code left join sm.custno=cust.custno) … Any help would be greatly appreciated. OK, you know you need a GROUP BY, that's good. You also recognized you needed to JOIN a few tables together, also good. There are at least two ways to do what you ask. One is a fairly complex query that does it all in one statement (might take a long time to compute) the other is a sequence of two simpler statements. I think the two-statement solution will be easier to understand and maintain so I would prefer to go over that. However, in order to provide an example of either method I will need more information from you. From the CLI (command line client), please provide the output from these three commands: SHOW CREATE TABLE sales\G; SHOW CREATE TABLE cust\G; SHOW CREATE TABLE product\G; That will tell me exactly which columns live on which tables and where you do or do not have any indexes. Good indexes will make or break the performance of your database. You will not be exposing any data, only the design of the tables. Please remember to CC the list on all responses. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Segmentation fault while executing Mysql application.
Hello sir, I have written an application by using MySQL API for retrieving result of simple query select * from Table_Name but there are some modification in compilation process for getting desire results. Modification or Alteration are: 1. I have Installed full packages of MySQL-3.23.54-11during Linux RedHat (Shrike) installation. 2. I have written an application by using following MySQL API: i- mysql_init(mysql); ii- mysql_real_connect(mysql,host,user,pass,db,0,NULL,0) iii- mysql_real_query(mysql,query,50) iv- mysql_store_result(mysql) v- mysql_fetch_row(res) vi- mysql_num_fields(res) vii-mysql_free_result(res) viii- mysql_close(mysql); 3. I am using following files: i- mysql.h -- Header file for fucntion prototyping and use for Data-Type declaration. ii-mysql_com.h and mysql_version.h -- These header files are required for mysql.h file iii- libmysqlclient.a -- Use for required API and for compilation iv- Esql.c -- My own Application Note: mysql.h, mysql_com.h and mysql_version.h are belong to MySQL-3.23.54-11 package but libmysqlclient.a is belong to MySQL-4.1.10 package and all mentioned file are contains in a Folder 'Application'. 4. Compilation procedure: i- gcc -o App Esql.c -lz /path/to/Application/libmysqlclient.a 5. Execution procedure: i- Application ./App 6. Execution Error: i- I have retrieved desire result with respect to query. ii- But Following error occur after print desire result on screen at end: I- Segmentation fault. 7. Experiments: i- I have examined application behaviour with libmysqlclient.a file of same package MySQL-3.23.54-11. ii-I have gained all those result what i had expected. 8. Reason: i-Source distribution MySQL-3.23.54-11.tar.gz does not install properly even all requirement are met as describe in mannual. ii-Source distribution MySQL-4.1.10.tar.gz install properly iii- I required all those files which is required for creating libmysqlclient.a file; *.c and *.o of MySQL-3.23.54-11 package which install during linux installation. So, these were fact and figure which being happen in developing application. Kindly, suggest me. I 'll be very greatful to you. IMRAN SHABBIR Software Engineer Vibrant Wits Software Inc. Pakistan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with source distribution in installation
Most respectfully, I would like to get support regarding my problem. Description: I have installed Linux RedHat 9 (Shrike) now i am going to install Mysql-3.23.54a in Linux by compiling source code of respective package which has been downloaded from this site http://downloads.mysql.com/archives.php?p=mysql-3.23v=3.23.54a as well as download pdf format of MySQL manual for consulting about installation of source distribution but could not install in this platform even all requirement is duly met according to MySQL. Linux RedHat 9 (Shrike) GNU gcc 3.2.2 GNU make 2.79.1 MySQL-3.23.54a.src.tar.gz Basically, I donn't want to install this package in form of rpm format because i want to get libmysqlclient.a file by compiling source which is required for my project Porting MySQL on Embedded System. I am using uCsimm DragonballEZ with uClinux for this project. I have completed all thing which essential for a client to communicate with MySQL server. Infact, i would like to compile all source code with respect to the tool of uClinux GNU gcc because uCsimm is using M68EZ328 microprocessor. So, first of all i would like to compile all source code of the package MySQL-3.23.54a on linux RedHat 9 (Shrike) then on uClinux. Kindly tell me the solution of this problem. I am very greatful to you. Contact: vibrant wits software Inc. System Engineer [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULL Text Limitation issue!
Thank you very much Paul and Keith for your response , I guess I will have to use LIKE clause in SQL statement for searching the records then. Imran - Original Message - From: Keith C. Ivey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Imran Aziz [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 5:36 PM Subject: Re: FULL Text Limitation issue! On 23 Sep 2003 at 17:07, Imran Aziz wrote: I have come to know that mySQL FULL TEXT search has the limitation of the search phrase to be more then 3 charators. In order to alter the default behavior one has to alter the variable ft_min_word_len. I am running MySQL 3.23.54 and the FULL Text search works fine , but I am unable to alter the variable ft_min_word_len either using the my.cnf file or by altering the variable value on mysql startup. The ft_min_word_len variable wasn't introduced until MySQL 4. See http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULL Text Limitation issue!
Hello All, I have come to know that mySQL FULL TEXT search has the limitation of the search phrase to be more then 3 charators. In order to alter the default behavior one has to alter the variable ft_min_word_len. I am running MySQL 3.23.54 and the FULL Text search works fine , but I am unable to alter the variable ft_min_word_len either using the my.cnf file or by altering the variable value on mysql startup. In addition when I show all variables used by mySQL the variables with ft_ prefix do not show up. Is this FULL Text alteration limited to later verison of mySQL Server or am I doing something wrong , can anyone please help me with this and let me know how to alter the limit of the input phrase for FULL TEXT search. Imran.
Imran Javed/Kamino is out of the office.
I will be out of the office starting 21/08/2003 and will not return until 08/09/2003. I will respond to your message when I return. If you have any urgent queries please contact Stuart Mclean == Disclaimer Notice This message (including attachments) is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. If you have received this e-mail in error, please notify us immediately by telephone or by e-mail. It is the responsibility of the recipient(s) to ensure that this message is virus free and we accept no liability for any loss or damage arising from its receipt or use. == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
import CSV
can any one help is there a way where i can import a csv file into multiple tables in MySQL database this has to be a run when ever there is a file in a wating directory thanks in advance == Disclaimer Notice This message (including attachments) is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. If you have received this e-mail in error, please notify us immediately by telephone or by e-mail. It is the responsibility of the recipient(s) to ensure that this message is virus free and we accept no liability for any loss or damage arising from its receipt or use. == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport access denied errors
Hi, I'm working on SOlaris 8 with MySQL v3.23.54. I'm trying to give a user 'user1' access to one of my databases 'db1', and specifically one table 'table1'. As root I've run: msql grant all on db1 to user1@localhost -and it appears to work fine. However when as the solaris user 'user1', I run the following from the shell prompt: $:/usr/local/mysql/bin/mysqlimport db1 table1.txt I get: /usr/local/mysql/bin/mysqlimport: Error: Access denied for user: 'user1@localhost' (Using password: NO), when using table: table1 -- I want this user 'user1' to be able to run the mysqlimport command without prompting for a password. Can you help? thanks. _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqladmin access denied error
Hi, I'm using solaris 8 with mysql v 3.23.54. I want to enable a solaris user - 'user1', to use the mysqlimport command without using a password. I've run, as root, the following sql command: mysqlgrant all on db1.* to user1@localhost -this appears to work fine. However as user1 in the solaris shell, when I run: $:/usr/local/mysql/bin/mysqlimport db1 table1.txt I get : /usr/local/mysql/bin/mysqlimport: Error: Access denied for user: 'user1@localhost' (Using password: NO), when using table: table1 So how can I allow a user to run mysqlimport to my db1 database, and without a password? thanks. _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemailxAPID=42PS=47575PI=7324DI=7474SU= http://www.hotmail.msn.com/cgi-bin/getmsgHL=1216hotmailtaglines_addphotos_3mf - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can't access MySQL via PHP
Hi all,
I have a MySQL database 'mydb' and I can perfectly access it via telnet. I
have one database and I have full access to it. I can't create user and
other databases, but its fine.
Problem is that I write a PHP script and I can't access my sql database
using that script! I got 'Access Denied' Error.
I've tried MySQL grant commands e.g.
Grant ALL PRIVILEGES
ON mydb.*
TO imran@%; (replaced % with my hostname etc. too)
command is executed properly.
Here is the PHP script:
include(passwords.php);
$openConn2db = mysql_connect( $hostname:$port, $username,$password );
$dbSelected = mysql_select_db( $dbname, $openConn2db );
$result = mysql_db_query ($dbname,select * from Girls);
while ($row = mysql_fetch_array ($result))
{
$sFace = $row[face];
print(trtdfont face='Arial' size='1'$sFace/font/td/tr);
}
How can I configure mysql so I can access it with my web applications too,
not just telnet?
And anyone knows how to make mysql accessible via GUI clients like mascon or
myadmin etc. Some of my MySQL databases got accessible by them and some
just telnet? depending on different provides?
Merci a bunch!
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
