Re: libexec/mysqld: unknown option '--skip-bdb'
Kris wrote: Hello, I am trying to produce an automated, reliable, and repeatable way of testing mysql releases, and I am finding an error that makes this impossible to accomplish. I am trying to compile mysql and install it in a non-standard directory. I do notwant mysql installed in /usr or /usr/local. For example, I am trying to install it in /tmp/msource with the configure line: Do not install software in /tmp in Linux. /tmp is not guaranteed to survive a reboot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why can't I kill the query cache?
Little, Timothy wrote: Also titled, I want this to run slow ALL the time... I have a group of dreadful queries that I have to optimize. Some take 20-30 seconds each -- the first time that I run them. But then they never seem to take that long after the first time (taking less than a second then). If I change the keywords searched for in the where clauses, then they take a long time again... so it's the query-cache or something just like it. BUT, I am doing this each time : flush tables; reset query cache; set global query_cache_size=0; SELECT SQL_NO_CACHE DISTINCT ca.conceptid AS headingid, And still it's not avoiding the cache. Is there a cache I'm missing? Tim... Disk cache, but I don't know how to clear it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: selecting the 'best' match
blackwater dev wrote: I have a hold car data such as color, model, make, year, etc. I want to allow the user to answer some questions and I'll present them with the car that 'best' matches their criteria. How do I do this? I still want to return ones that don't match exactly but want the closer matches ordered at the top: Table:cars columns: car_id, make, model, year, color, condition So if the user enterrs: model: Toyota year: 1998 condition:great color: blue I would show them a blue 1998 good conditioned camry first but farther down in the list might still have a blue good condition 98 Honda. Thanks! Perhaps: SELECT * FROM cars order by model!='Toyota',model; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql problem
AZZOPARDI Konrad wrote:
Hello people,
I do not know if this the right listI am migrating a very basic application from an older mysql version 4.1.9-standard to a new mysql version 5.0.45 {RedHat default package}. I have migrated DB data from one to the other and all data seems to be there including the structureMy problem is that I run a query like this :
SELECT x.application_name, y.role_name FROM application x, role y JOIN logical_app_role_link l ON x.application_id = l.application_id AND y.role_id = l.role_id WHERE l.logical_id = 15;
It works for the old mysql version but for the new mysql version I receive the
following error :
ERROR 1054 (42S22): Unknown column 'x.application_id' in 'on clause'
and I am sure that application_id exists in table application.
Thanks
konrad
Don't mix implicit and explicit joins.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can't create table
Octavian Râºniþã wrote: Hello, I've tried to create these 4 test tables, but when I try to create the last one, MySQL gives the following error: ERROR 1005 (HY000) at line 20: Can't create table '.\z\favorite_link.frm' (errno : 150) It seems that the foreign keys are not created well in the fourth table, and I don't know why. Does anyone have any idea? Here are the SQL statements for creating these tables: create table if not exists user( id int unsigned not null auto_increment primary key ) engine=InnoDB; create table if not exists favorite( id bigint unsigned not null auto_increment primary key, user int unsigned not null, foreign key(user) references user(id) on update cascade on delete cascade ) engine=InnoDB; create table if not exists link( id bigint unsigned not null auto_increment primary key, user int unsigned not null, foreign key(user) references user(id) on update cascade on delete cascade ) engine=InnoDB; create table if not exists favorite_link( favorite bigint unsigned not null, link int unsigned not null, primary key pri(favorite, link), foreign key(favorite) references favorite(id) on update cascade on delete cascade, foreign key(link) references link(id) on update cascade on delete cascade ) engine=InnoDB; Thank you very much. link int unsigned not null, id bigint unsigned not null auto_increment primary key These are not the same type. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: failed-mysql-bugreport
Bob wrote: mysql.sock doesn't exist on the hard drive. Start the server. -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Basic information: MySQL Table crash?
Roman Eberle wrote: hi, someone else had a similar question, but didn't get any useful reply here on the list - I'll give it another try. :-) I need some basic understanding of MySQL table crashes. Maybe you can help me, I think it's so basic, no one ever wrote about it. (Until now! ;)) The mysql manual just says 'if you have a crashed table you can repair it.' Well... 1. What IS a table crash? What file/relation/... gets corrupted? And how does table recovery basically work? 2. If you had a crashed table: Where would you start looking for the reason? And where would you look next? 3. What precautions can one take to avoid a table crash? Are there any symptoms that show up when a table crash is about to happen? (like: out of swap memory, disk full, too much system load, ...?) Any comments/hints/links appreciated, thanks in advance, regards Roman #1 reason is powering off the computer without a proper shutdown. Get a UPS. -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
Chris W wrote: I have two tables, one is a list of users and the other is a list of events for each user. It is a one to many relationship. The event table is pretty simple just an event type and a the date and time of the event in a datetime field. I need a query that shows all events of a certain type for each user, very simple so far. In fact the query I use now is simply, SELECT u.FName, u.LName, e.EventType, DATE(e.DateTime) FROM user u JOIN event e USING(UserID) ORDER BY u.LName, u.FName, e.EventType, e.DateTime The twist comes in that there can be several records for a given user and event type all on the same day, in a case like that, I only want the query to show one record. So I need one record per user per event type per day. The query will strip the time part off of the date time field and only display the date. We don't really care if that event happened 1 or 10 times in one day just that it happened at least once on that day for a user. Chris W try DISTINCT. -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding gaps
Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; SELECT a.id+1 AS `Missing_From`, MIN(b.id) - 1 AS `To` FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id `To` -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Totaling from several tables away
Brian Dunning wrote: How do I query How many calories did Brian eat on 2009-09-04? Table:People +---+---+ + Person_ID + Name | +---+---+ | 5 | Brian | +---+---+ Table:Meals +-+---+---++ | Meal_ID | Person_ID | Meal_Name | Date | +-+---+---++ | 3 | 5 | Breakfast | 2009-09-04 | | 4 | 5 | Lunch | 2009-09-04 | +-+---+---++ Table:Meal_Items +-+-+---+--+ | MealItem_ID | Meal_ID | Item_Name | Calories | +-+-+---+--+ | 16 | 3 | Banana| 100 | | 17 | 3 | Milk | 150 | | 18 | 4 | Cookie| 200 | +-+-+---+--+ SELECT sum(calories) from People INNER JOIN Meals ON People.Name=Meals.Name INNER JOIN Meal_Items on Meals.Meal_ID=Meal_Items.Meal_ID WHERE Name='Brian' AND Date='2009-09-04'; -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL won't start with external bind-adress in my.cnf
Skip Evans wrote: Hey all, I'm trying to get my MySQL server configured so outside connections can access it with OpenOffice.org 2.4. My server is running on FreeBSD 6.0 and the my.cnf file looks like this currently. [mysqld] user = mysql pid-file = /usr/local/mysql/bigskypenguin.com.pid socket = /tmp/mysql.sock port = 3306 basedir = /usr datadir = /usr/local/mysql tmpdir = /tmp language = /usr/local/share/mysql/english/ bind-address = 192.168.xxx.xxx log = /var/log/mysql.log # skip-networking My server has two nics, one for internal access at the 192.168.xxx.xxx address, and another for external access at 76.343.xxx.xxx It is this second IP, the external one, I would like to implement so that the database server is accessible externally to people through OpenOffice.org 2.4. To accomplish this I changed the file to: [mysqld] user = mysql pid-file = /usr/local/mysql/bigskypenguin.com.pid socket = /tmp/mysql.sock port = 3306 basedir = /usr datadir = /usr/local/mysql tmpdir = /tmp language = /usr/local/share/mysql/english/ bind-address = 76.343.xxx.xxx log = /var/log/mysql.log # skip-networking But configured in this way, the MySQL server will not restart. I attempt a /usr/local/etc/rc.d/mysql-server.sh restart But the server does not start until I change the address back to the LAN address. Any help would be appreciated. I have not found via Google anyone else having this issue. Does that IP address resolve to your hostname? -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filesort on query
Phil wrote: I have a table as follows containing approx 1.5M rows. I pull data from it based on the CPID and insert into an 'overall' table. After breaking down the statement somewhat, an explain still gives a filesort. Is there any way to avoid a filesort for this query ? mysql explain select a.cpid ,sum(a.metric1) ,sum(a.metric2),0,0,a.country,min(a.create_date),min(a.create_time),a.nick from boinc_user a group by a.cpid order by a.cpid; ++-+---+--+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+---+--+-+--+-+-+ | 1 | SIMPLE | a | ALL | NULL | NULL | NULL| NULL | 1443130 | Using temporary; Using filesort | ++-+---+--+---+--+-+--+-+-+ 1 row in set (0.00 sec) CREATE TABLE `boinc_user` ( `proj` char(6) NOT NULL, `id` int(11) NOT NULL default '0', `stat_date` date NOT NULL default '-00-00', `nick` varchar(50) character set latin1 collate latin1_bin NOT NULL default '', `country` varchar(50) NOT NULL default '', `cpid` varchar(50) NOT NULL default '', `url` varchar(50) default NULL, `create_date` int(11) NOT NULL, `create_time` bigint(20) NOT NULL, `has_profile` char(1) NOT NULL, `team0` int(11) default NULL, `team1` int(11) default NULL, `metric1` double NOT NULL default '0', `metric2` double NOT NULL default '0', `metric3` double NOT NULL default '0', `metric4` double default NULL, `today` double default '0' PRIMARY KEY (`proj`,`id`), KEY `trank` (`proj`,`team0`,`metric1`,`id`), KEY `forstats` (`proj`,`metric1`,`id`), KEY `name` (`proj`,`id`), KEY `racrank` (`proj`,`metric2`,`id`), KEY `cpid` (`cpid`,`proj`), KEY `today` (`proj`,`today`,`id`), KEY `prank` (`proj`,`projrank0`,`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Regards Phil With no where clause, and aggregate functions, it is faster to do a full table read, and the sort the aggregated results. -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why are tmp tables being created on disk?
Ofer Inbar wrote: I've been running this for a few hours and it consistently shows lots of writes but no reads at all on sdb1, the partition where I have my binary logs and tmpdir. Is MySQL writing lots of tmp files and not reading them? Or, how else can I interpret this? -- Cos Perhaps the data is still in cache, so no read is actually made from the disc. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld works but not mysqld_safe
Chris Africa wrote: - 070717 22:44:12 mysqld started 070717 22:44:12 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive 070717 22:44:12 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=/usr/local/mysql/data/me-web2-bin' to avoid this problem. 070717 22:44:12 InnoDB: Started; log sequence number 0 335107 /usr/local/mysql/bin/mysqld: File '/usr/local/mysql-5.0.41-osx10.4- i686/data/me-web2-bin.27' not found (Errcode: 13) 070717 22:44:12 [ERROR] Failed to open log (file '/usr/local/ mysql-5.0.41-osx10.4-i686/data/me-web2-bin.27', errno 13) 070717 22:44:12 [ERROR] Could not open log file 070717 22:44:12 [ERROR] Can't init tc log 070717 22:44:12 [ERROR] Aborting 070717 22:44:12 InnoDB: Starting shutdown... 070717 22:44:14 InnoDB: Shutdown completed; log sequence number 0 335107 070717 22:44:14 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 070717 22:44:15 mysqld ended - I'm not using replication, but I am using mirrored drives, and this is the first time I've ever done that. Still, it didn't seem to be a problem until I screwed up the passwords. I'm just setting up a new server, so the tables and data are not dear to me. Right now, I just want MySQL to work -- with logging!! -- Chris Africa Web Project Manager Department of Mechanical Engineering University of Michigan 734-764-8482 Fridays: 734-355-6577 AIM/iChat/Skype ID: baiewola mysql does not have write privilege in /usr/local/mysql-5.0.41-osx10.4-i686/data. Is this owned by root too? -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld works but not mysqld_safe
Chris Africa wrote: Hi Gerald. Actually, mysql should have access, as it owns the entire directory. drwxr-xr-x 19 mysql wheel 646 May 30 09:41 mysql-5.0.41-osx10.4- i686 and nested inside that is drwxr-x--- 27 mysql wheel918 Jul 18 12:53 data Thanks! Error 13 is a permissions error. Check all directories from / to the data directory. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error in login
Héctor S. Mendoza O. wrote: Hello all… I’ve scratched my head for a while on this one, so if someone could shed some Light on it I would appreciate it very much I have an account setup, and know about the password settings regarding hosts. The account is named web19_u1, and has listed ‘%’ and ‘localhost’ as posible hosts to be able to log in On remote machines I do a “mysql -u web19_u1 -p -h10.10.10.2 –p web19_db1” and have no problems on querying the tables On the local host (where the mysqld resides) I do the same command and get a “ERROR 1045 (28000): Access denied for user 'web19_u1'@'db1.intranet' (using password: YES)”. If I change this to –hlocalhost I can login correctly. My problem is that the application (php based) resides both on local intranet and in internet (two different servers), remote machine is in intranet and needs ip 10.10.10.2 to access database, and local host (mysqld resides in) can only use localhost. Development and testing of application is done in intranet so basically I need two different configuration files depending on where the application is running to access the database. I’ve tried removing the ‘localhost’ from the user and only leaving ‘%’ with no difference at all. I’ve also tried the –protocol=TCP option with no luck either. Any pointers? TIA Hector S. Mendoza O PS. mysqld Ver 5.0.27 Use a my.cnf file in the user's home directory. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: peformance help: preventing 'using temporary; using filesort'
Rich Brant wrote: Hello all. I'm looking for help with the query below. Is there anyway to prevent the temporary and filesort? I've tried about as many combinations as I could think of, but can't seem to prevent it. I'm sure that's the reason, when run on a table of around 750k records, it takes in excess of 20 seconds. There are indexes on sourceID in both tables as well as the date field in the first table. Thanks for any ideas. SELECT t1.sourceID as sourceID, count(t1.sourceID) as clicks, sum(t1.converted) as conversions, (sum(t1.converted)/count(t1.sourceID)) * 100 as conv_rate, count(t1.sourceID) * t2.cost as cost, sum(t1.revenue) as revenue, (sum(t1.revenue)) - (count(t1.sourceID) * ifnull(t2.cost,0)) as margin, ( ((sum(t1.revenue)) - (count(t1.sourceID) * t2.cost)) / sum(t1.revenue) ) * 100 as gm, (count(t1.sourceID) * t2.cost) / sum(t1.converted) as cpl, (sum(t1.revenue)) / sum(t1.converted) as rpl, t2.cost as cpc FROM source_site_quality as t1 LEFT JOIN rpt_cpc as t2 ON (t1.sourceID = t2.sourceID) WHERE t1.date = '2007-06-26' AND t1.date = '2007-06-28' GROUP BY sourceID ORDER BY clicks desc, conversions desc; When using EXPLAIN: ++-+---+---+---+--+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---+---+---+--+-+--+--+--+ | 1 | SIMPLE | t1| range | idx_date | idx_date | 3 | NULL | 4612 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t2| ALL | NULL | NULL | NULL | NULL | 1351 | | ++-+---+---+---+--+-+--+--+--+ -- rich rpt_cpc needs an index on sourceID. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
John Mancuso wrote: If I have 2 large tables A and B and I need to join them: select * from A,B where A.col1=B.col1 If A.col1 is an index and B.col1 is not, if I create an index on B.col1 will this index be used? In other words how are indexes used on table joins? Thanks John Mancuso Linux Administrator/MySQL DBA IT Infrastructure American Home Mortgage w: 631-622-6382 c: 516-652-2475 Use explain, and find out. MySQL may reverse the two and join A to B inorder to use A's index. Since there is no WHERE clause, only one of the tables needs an index. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading databases?
Seth Seeger wrote: Hello, I'm having trouble copying a database from MySQL 4.1.22 to 5.1.19- beta. Both are FreeBSD i386-based machines. I have run the following commands: mysqlcheck --check-upgrade --all-databases --auto-repair mysql_fix_privilege_tables Both executed with no problems. (mysqlcheck reported OK for all tables.) When I try to access any of the tables, I get this: mysql select * from users; ERROR 1034 (HY000): Incorrect key file for table 'users'; try to repair it So I tried to repair it: mysql repair table users; +++-- ++ | Table | Op | Msg_type | Msg_text | +++-- ++ | seth_icsx_mands_live.users | repair | error| Incorrect key file for table 'users'; try to repair it | +++-- ++ 1 row in set, 1 warning (0.10 sec) Running repair table users doesn't seem to have any effect on it because the problem persists. I have tried to run mysql_upgrade, with no success: # mysql_upgrade --basedir=/usr/local --datadir=/var/db --verbose Looking for 'mysql' in: mysql FATAL ERROR: Can't find 'mysql' I have tried it with all different combinations for the two directory options with no luck. All tables are MyISAM. Can anyone shed some light on what I'm supposed to do? Thanks, Seth Shut the server down and run myisamchk on users.MYI -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: master status is null
Ananda Kumar wrote: Hi All, We have setup replication. Mysql version 5.0.40. On master db, if i execute the below command it showing show master status\G; Empty set (0.01 sec) You do not have replication setup. ERROR: No query specified Use \G or ; but not both. what could be the reason, and how do i fix it. Please help regards anandkl -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: zabbix mysql problem
sizo nsibande wrote: I am trying to install zabbix, and at the third step I get this error: [EMAIL PROTECTED] etc]# mysql -u zabbix -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 5.0.27 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create database zabbix; ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'zabbix' mysql I am reading the mysql in 21 days book and hope to become more knowledgeable on this subject, please point me in the right direction? You are not looged in to mysql as a user with create privileges. You might need to backup a few chapters. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Host through Heartbeat
Ben Clewett wrote: Dear MySQL, I'm running 5.0.26 through Heartbeat. Which seems to work well, even as a replication slave and Heartbeat continously stopping and starting the server. The Heartbeat moves MySQL around from server to server when a failure occures. I am trying to find a way for MySQL to report the server host name on which it's currently sitting. Without any luck. Would any kind members know of a way of getting this information from MySQL? Many thanks, Ben Heartbeat moves the IP address around as well as the services. Your hostname should not change. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Host through Heartbeat
Baron Schwartz wrote: Gerald L. Clark wrote: Ben Clewett wrote: Dear MySQL, I'm running 5.0.26 through Heartbeat. Which seems to work well, even as a replication slave and Heartbeat continously stopping and starting the server. The Heartbeat moves MySQL around from server to server when a failure occures. I am trying to find a way for MySQL to report the server host name on which it's currently sitting. Without any luck. Would any kind members know of a way of getting this information from MySQL? Many thanks, Ben Heartbeat moves the IP address around as well as the services. Your hostname should not change. DNS won't change, but the server's /etc/hostname will, right? Disclaimer: I'm no expert on this... I didn't even know the IP address moved too. I should read about Heartbeat. Baron Do you actually have /etc/hostname? RHEL and Centos do not. They do have an entry in /etc/sysconfig/network -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access Denied When Trying to Create Database
untz wrote: Ananda, Thank you for responding! I just tried this and got the following: mysql grant super *.* to 'untz'@'localhost' identified by 'password'; ERROR 1064 (42000): 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 '*.* to [EMAIL PROTECTED] identified by password' at line 1 Can anyone please help me? I am not a DBA Kindest regards, untz On Jun 11, 2007, at 4:36 AM, Ananda Kumar wrote: Is this a user untz going to do even the create procedure, if yes then you need to grant super previliege to this user. grant super on *.* to 'untz'@'localhost' identified by 'password'; regards anandkl On 6/11/07, untz [EMAIL PROTECTED] wrote: Baron Prathima, Thank for the information! What happened is that I hadn't used MySQL for a long time and looked up on the Internet on how to change my root and individual users' passwords. The last command the URL had me type was flush privileges; and once I did that, I think it created a problem for everything else. Here's what I got when I ran SHOW GRANTS: mysql show GRANTS; + --- --+ | Grants for [EMAIL PROTECTED] | + --- --+ | GRANT USAGE ON *.* TO 'untz'@'localhost' IDENTIFIED BY PASSWORD '*55C1BF0D7E49AB5343925CDD17F2F5F923B5248C' | GRANT ALL PRIVILEGES ON `depot_development`.* TO 'untz'@'localhost' | GRANT ALL PRIVILEGES ON `depot_test`.* TO 'untz'@'localhost' | GRANT ALL PRIVILEGES ON `depot_production`.* TO 'untz'@'localhost' | GRANT ALL PRIVILEGES ON `music_development`.* TO 'untz'@'localhost' | GRANT ALL PRIVILEGES ON `music_test`.* TO 'untz'@'localhost' | GRANT ALL PRIVILEGES ON `music_production`.* TO 'untz'@'localhost' + --- --+ 7 rows in set (0.00 sec) What am I supposed to do? I can not run queries or even create tables... I also tried using a MySQL client (CocoaMySQL) and it displayed the same error as in the previous e-mail (see below). Am really stuck on this so any help, suggestions, etc. would be greatly appreciated! Sincerely yours, untz On Jun 10, 2007, at 5:44 AM, Baron Schwartz wrote: Hi untz, untz wrote: Hello there, I am using MySQL 5 on OS X Tiger... After starting the server, I tried to create a sample database and this is what what happened: $ mysql -u untz -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 5.0.16-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create database people_development; ERROR 1044 (42000): Access denied for user 'untz'@'localhost' to database 'people_development' mysql Try running SHOW GRANTS while logged in, and see what privileges you have. You probably need to grant your user some additional privileges. Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] The show grants you ran shows you the proper syntax. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access Denied When Trying to Create Database
untz wrote: Gerald, I tried this and still got an error! mysql grant all privileges on *.* to 'untz'@'localhost' identified by 'maddog'; ERROR 1045 (28000): Access denied for user 'untz'@'localhost' (using password: YES) mysql Can anyone please help me? I am unable to use MySQL at all, at this point... -untz You have to be logged-in to mysql as a privileged user to grant permissions to others. Logout, and login as root. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
David T. Ashley wrote: I decided to go with a simple paradigm for my web-based database. Rather than transactions, each process locks the entire database while it is changing something, then unlocks it. This just serializes access (all other processes will block until the one modifying the database has finished). The method I was using is something like: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; I probably botched the syntax above. Rather than enumerate every table in the database (which could get to be a long list), I wondered if it is possible to just lock one table, with the gentleman's agreement that in order to modify the database, every process must first lock that particular table. You could use a string lock for this. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
David T. Ashley wrote: On 6/4/07, Jerry Schwartz [EMAIL PROTECTED] wrote: Whatever you do, make sure that every bit of code that locks multiple resources locks them in the same order. That's the only way to avoid deadlocks. Hi Jerry, I really appreciate the good advice. However, my original question is still unanswered. My original question is whether I can lock one table (as a gentleman's rule followed by all processes) to serialize access to the database consisting of many tables. LOCK TABLE x; Manipulate many tables, perhaps not even including table x; UNLOCK TABLES; My question is really whether MySQL might do some strange optimizations ... or somehow buffer the middle query so that it completes after the UNLOCK. Thanks, Dave. Once you issue a LOCK TABLES command, you may not access any tables not in the LOCK statement. You must lock *ALL* tables you will use, perform your updates, and then UNLOCK TABLES. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Frequently MyISAM TABLE corruption.....Pls help
Nilnandan wrote: Hello all, I have one server which has mysql 5.0.27 installed. There is one table named table1. that table has 122000 records..It has 114 fields and 22 indexes. Now this table always been corrupt. I have try to found the solution but i couldn't. Pls help me ASAP. I have used CHECK and REPAIR option I have given here the output. 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Table 'table1' is marked as crashed and should be repaired 070509 4:06:17 [ERROR] /usr/sbin/mysqld: Sort aborted How big is the index file? the data file? Has either reached the file size limit of your filesystem, or the default maximum MyISAM size? -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs...
Ashley M. Kirchner wrote: Probably a silly question, but if I have my logs set to binary, how can I read them and check on a query that's failing? I don't necessarily want to switch to text logs because we'll want to do replication soon here, so I want to keep it as a binary log. My problem is I have a query that's failing and I can't figure it, I can't read the log file. Suggestions anyone? Use the mysqlbinlog command. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
Kevin Hunter wrote: At 9:06p -0400 on 30 Apr 2007 John Kebbel wrote: I'm having problems understanding NULL. I grasp what a NULL value is, A NULL value is rather an oxymoron. It'd be more accurate to say that NULL means absence of a value. but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) Caveat emptor: I haven't tested this in about a year. Are you perchance using a table type of MyISAM? I seem to recall that MyISAM has a hard time actually representing NULL internally. [ ... Thinks for a minute ... ] I remember something about spaces, like, I think I did INSERT ( val1, val2, NULL ) INTO myTable; and was only able to get the tuples back when I did SELECT * FROM myTable WHERE col3 = ' '; -- a space character If this is the case, you might consider using a different table type, such as InnoDB. HTH, Kevin MyISAM has no problem representing NULL. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should the db be shutdown before backing up?
Mathieu Bruneau wrote: murthy gandikota a écrit : Hi I did a hot backup a while ago. When I had to restore from backup there were several error messages. So I ran mysqlcheck for every table with --auto-repair option. Is this the best way? Thanks Murthy - Ahhh...imagining that irresistible new car smell? Check outnew cars at Yahoo! Autos. Yes you should shutdown the database before doing a mysqlhotcopy. However you could use the flush tables with read lock so that your tables are consistent (and will still allow for select to go through the db) Best luck mysqlhotcopy does not require a database shutdown. If it did, it would not be a hot copy. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Permissions and Into Outfile
Kebbel, John wrote: I was trying to write the output of a select statement to a tab-delimited text file. I could not write the file to a folder inside /var/www or to my home file because of permission problems. After a moment's reflection, I realized /tmp had stuff written to it all the time, so its permissions must be wide open. I tried writing the file to /tmp and it worked fine. Since you cannot write to an existing file, you cannot create the file in advance and set its permissions. What is the trick to getting the file to write successfully anywhere you want it to be written? This is the query I was using... mysql select sched_students.id, firstName, lastName, grade, race, gender, dob, school, phone, program, hsMath, hsEnglish, hsScience, major, 9YBand, 9YChorus, 9YGifted, 9YTV, 9YROTC, 9YSpanishI, 9YSpanish2, 9YFrenchI, 9SAerobics, 9SArt, 9SBasketball, 9SDrama, 9SDriverEd1, 9SDriverEd2, 9SRecreation, 9STeamSports1, 9STeamSports2, 9SWeights into outfile '/tmp/srhs9.txt' from sched_students, SCHED_COURSES where (sched_students.id = SCHED_COURSES.id) and (grade = 8) order by lastname, firstname; The user 'mysql' must have write permission in the target directory. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Permissions and Into Outfile
Kebbel, John wrote: cat /etc/passwd on my Macintosh 10.3 at work (I'm using Linux at home) shows me this for mysql ... mysql:*:74:74:MySQL Server:/var/empty:/usr/bin/false The home directory for mysql is /var/empty, which does exist. Should I write ~into file~ output to this folder or should I change the mysql home directory to some other folder? Neither. Just make sure that mysql has permission to write in the directory you want to contain the output file. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what kind of indices to set up
James Tu wrote: I was a little to quick with the send button. Can you do a query like this: (I know that the * syntax is not correct, but is there something equivalent to it? SELECT from cars WHERE make=5 AND model=* AND body_color=7 AND tire_type = * AND hub_caps_type = 1 If you could perform a query like the one above, would MySQL still use the multi-column index that I set up? -James Select * from cars WHERE make=5 AND model LIKE '%' AND body_color=7 AND tyre_type LIKE '%' AND hub_caps_type = 1 If your multi-column index starts with model or tyre_type, then no. The index can be used down to, but not including the first column that is compared against '%'. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stopping Replication
Jesse wrote: I'm trying to stop replication between two servers that I've got here. I set it up originally, to play with it and see how it works. However, I now want to use these two servers separately. I have edited the my.ini file on both the server and slave machines, and removed the server-id from them. I have also executed a stop slave on the slave server. However, it appears that when I stop and re-start the server that was the slave, it tries to re-start replication again, because I get the error, Failed to create slave threads in the event log. What else do I need to do to stop the replication process? Thanks, Jesse You have to remove the master information from the slave's my.ini file. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to open a big sql script
Joerg Bruehe wrote: Hi! Duncan Hill wrote: On Thursday 19 April 2007 15:53:54 molemenacer wrote: I am trying to change all the names of the database from mthosp to another name, is this possible? Assuming you mean tables, not database (as mysqldump doesn't store the database name in the dump file [or at least never has for me]): sed -e 's/mthosp/another_name/' source.sql dest.sql 1) This is risky, because it will also change (for example) govmthospital to govanother_nameital which may be a bit more than is intended. Sure, you can add conditions that prevent some such issues, but it will get complicated. The regular expressions the sed can handle are somewhat limited, when you compare them to what Perl can do. (Sorry, I won't give a Perl command - this is still too risky.) mysql rename table mthosp_1 to another_name_1, mthosp_2 to another_name_2 (Check the manual for syntax) 2) If you want multiple changes in a line, add the g modifier at the command end: sed -e 's/mthosp/another_name/g' source.sql dest.sql 3) Most likely, you should first run a grep on the file, to check where the string occurs and which effects your commands have. 4) *If* you decide to use sed, then a cheap way to see just the changes is this: sed -n -e 's/mthosp/another_name/gp' source.sql verify.sql This will output *only* the changed lines, not the unchanged ones. However, it will not show the context - if you want to get that as well, then you will need a more complicated sed command or (easier, IMHO) a pipe of grep (providing the context, see the -A and -B options) and sed (above). Regards, Joerg That reminds me of the story of the mailing list maintainer who decided to replace 'and' with ''. He ended up sending mail to Sy Serson. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suddenly : mysqld: Can't create/write to file PID
Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: I am suddenly seeing this in my error log: 070418 08:43:57 mysqld started 070418 8:43:57 [ERROR] /usr/contrib/libexec/mysqld: Can't create/write to file '/usr/contrib/var/doctor.nl2k.ab.ca.pid' (Errcode: 13) 070418 8:43:57 [ERROR] Can't start server: can't create PID file: Undefined error: 0 070418 08:43:57 mysqld ended HOw is this fixed? The mysql user must have execute and write privilege on /usr/contrib/var and all subdirectories. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does casing of Table names matter on Linux
abhishek jain wrote: On 3/5/07, abhishek jain [EMAIL PROTECTED] wrote: On 3/1/07, abhishek jain [EMAIL PROTECTED] wrote: On 2/28/07, Ow Mun Heng [EMAIL PROTECTED] wrote: On Wed, 2007-02-28 at 15:05 +0530, abhishek jain wrote: Hi Friends, I have been developing one application over Windows and now i want to move/port that into linux, i want to know will the casing of the table name matter on linux ie if i have table name as tab1 and i execute query like select * from TAb1 ,will it make an effect. I have found that this is not a problem on windows but it is on linux, i want a sol. for that a i can not change all the table names as in some places it is in small case and in other places it is in capital case. Yes.. There is a difference. Casing Matters in *nix. However there is an option for turning if off. # http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html lower_case_table_names = 1 Hi Friends, Thanks for the mails. Now i have made a mistake.In mine code i have somwhere capital case and in other place the small case for the table names. I cannot change the case in mine code and cannot resist the change to the Linux . What is the remedy now i mean can something be done now to make linux be case insensitive, Pl. help me, Thanks, Abhishek jain So friends , If anyone know abt the sol. pl. let me know Thanks, Abhishek jain The solution is in the link given above! -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and FOREIGN KEY
Micol lupen wrote: Hi to all, I am a student, i am studing Mysql FOREIGN KEY for to do a little progect, but i have this problem: i create this table: CREATE TABLE Cliente( codcliente VARCHAR(6) NOT NULL, nome VARCHAR(10) NOT NULL,cognome VARCHAR(20) NOT NULL, citta VARCHAR(10), indirizzo VARCHAR(20), tel VARCHAR(7) NOT NULL, PRIMARY KEY (codcliente))ENGINE=INNODB; CREATE TABLE villaggio (idvillaggio INT AUTO_INCREMENT, nome VARCHAR(15) NOT NULL, stato VARCHAR(3) NOT NULL, descrizione VARCHAR(30),PRIMARY KEY(idvillaggio)) ENGINE=INNODB; but when i create this table: CREATE TABLE offerta(idofferta INT AUTO_INCREMENT,of_idvillaggio INT NOT NULL, INDEX(of_idvillaggio),FOREIGN KEY(of_idvillaggio),REFERENCES villaggio(idvillaggio) ON UPDATE CASCADE ON DELETE RESTRICT)ENGINE=INNODB; i have this error: REFERENCES error control manual p.s. table villaggio is father and table offerta is children i see the manual but i don't see error, help me !!! excuse me for my bad english Best Regads Antonio _ L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: http://it.docs.yahoo.com/nowyoucan.html of_idvillaggio INT NOT NULL idvillaggio INT These are not the same type. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: row count inconsistency
Marty Landman wrote: The table was created and then loaded and not modified in any way I'm aware of afterwards. It's on a local, only accessible by me server. Really weird thing about it is that I wrote/ran a program specifically to find any gaps in the id sequence - because of the size of the table it took days to run but the result was 1-100537311 IOW it confirms the max id that Mysql gave, but also indicates that there are no gaps in the row id's all the way through. This doesn't make sense to me in light of Mysql reporting the count as posted previously i.e. mysql select count(*) from fidcid; +---+ | count(*) | +---+ | 100480507 | +---+ 1 row in set (0.09 sec) If this table is InnoDB, then count(*) is just an approximation. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on Freebsd 5 -- out of memory error
Graham Dunn wrote: The error: /usr/local/bin/mysqldump: Error 5: Out of memory (Needed 14154840 bytes) when dumping table `Attachments` at row: 24285 Platform details: Client: mysqldump Ver 10.9 Distrib 4.1.12, for portbld-freebsd5.3 (i386) # /usr/local/bin/mysqldump -u dba -h seisei -p rt3 --set-variable=max_allowed_packet=256M Attachments Attachments.sql Try adding the -q option. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Safe DB Distribution
[EMAIL PROTECTED] wrote: Something like distributing files with encrypted data in most columns can be done, with the encryption key being a combination of some hardware-related stuff such as MAC address that the user will provide to you, and the key you generate and provide to the user. Of course it creates a problem when the target computer crashes. Everything else is probably either hackable or would be based on the assumption that you'll be in the business forever but anyway there must be specialists out there in some security related groups that know how to make it hard to hack. On the surface of it if you provide this sort of protection you'll be able to distribute it as open source since you are protecting data and not the code but I'm sure bigger lycensing minds will easily prove me wrong and explain that there is such a thing as a GPL for data and copyrighting data is bad, which would mean that copyrighting anything is bad, or that copyrighting data is OK but the essence of your application is data so if you charge for it you can't distribute mysql with it under GPL etc. Actually it would be interesting to hear the opinion of mysql legal department on this. Thanks, Michael -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Check Out the new free AIM(R) Mail -- 2 GB of storage and industry-leading spam and email virus protection. You will not be able to search on encrypted fields. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Olaf Stein wrote: Thanks... My issue is not storage, it is confidentiality. I am not allowed to store the day of birth as it is considered identifying information (in medical records). I do not even have the day, I want to pass a date in format (-MM) to a date field if possible. Pass the date in format (-MM-00). -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT} How to pronounce GIF
Brian Dunning wrote: On Jan 7, 2007, at 4:23 PM, TK wrote: In short, the original inventors of the GIF format (CompuServe, 1987) have always defined the pronunciation to be like JIF. So, that has always been the correct pronunciation. Sure, so I'll start pronouncing graphics as jraphics. How do you pronounce giraffe? -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Instalation issues
Agarwal, Abhishek wrote: Hello All, I am trying to install the mysql server 5.1 on fedora 4. please find below the output [EMAIL PROTECTED] downloads]# rpm -i MySQL-server-5.1.12-0.glibc23.i386.rpm PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h fedora password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com Starting MySQLCouldn't find MySQL manager or server[FAILED] Please advice how I can debug this error. Thanks, --Abhishek What error? -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: +1MB query gets Out of memory
nocturnal wrote: Hi A client used to get Out of memory when trying to send a query that was a bit more than 1MB. The problem was solved when i increased query_cache to 2MB but now the problem is back and this time the line reported by the Out of memory error has a query that is just a bit over 1MB again. The only other value i could find that was 1MB was packet_size but that didn't help. What could i do to run this query? You have to change the maximum packet size on both the server and the client. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql on a NAS
[EMAIL PROTECTED] wrote: Hello, We recently moved to a new cluster plattform, setup by one external IT company at present (early stage): 2 XEON computers with a fibre channel link to a Network Storage. The mysql directories are located on the Network storage and mounted into /var/lib/mysql on each machine. So, every machine running an own mysql server, but sharing the directory. The used file system is OCFS (Oracle Cluster File System) You cannot do this. When I played with the two servers yesterday (we are not really happy with the performance) I noticed, that the stats of each mysql server are the same. Even if I restart one server, the stats are not amended in any way - if I restart both servers the stats were reset. Now my questions: 1) is this installtion a common practise to share a mysql db with several servers (we might increase the number of servers) Never. 2) is there a better, more performant way, to install mysql in a cluster ? See 'MySQL Clustering' BY Alex Davies and Harrison Fisk MySQL Press 3) Just your general idea about this setup. Thanks Stefan Stonki Onken -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up large dbs with tar
Van wrote: Greetings: I have a 600M data file that never gets backed up. The following error occurs in the cron job: tar: /data/mysql/my_db_name/my_large_table_name.MYI: file changed as we read it Is there a way I can set this one table to read-only prior to the backup without affecting other db writes during this operation? Thanks, Van Look at mysqlhotcopy. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't create database
Mário Gamito wrote: Hi, I've installed MySQL 5.0.27, but can't create databases. I get ERROR 1006 (HY000): Can't create database 'contineo' (errno: 28) In the log file i have this: InnoDB: Error: auto-extending data file ./ibdata1 is of a different size InnoDB: 0 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 640 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! Any ideas on how to solve this issue ? Any help would be appreciated. Best Regards, MG perror 28 Error code 28: No space left on device You need more disk space. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Manually Inserted Data
Keith Spiller wrote: Hi Rolando, Thanks for your help. I have reduced the query to the bare essensials to try to test the concept, but it still fails... ( SELECT ID, Start, End ( SELECT ID, Start, End as z FROM site_calendar_v2 as c WHERE Global='1' ) UNION ( SELECT '9', '2006-11-01', '-00-00' as z ) ORDER BY z, Start ASC, Status DESC, Time ASC a Does anyone see my mistake? Keith -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql -e Select Fields Enclosed By and NULL values
Ow Mun Heng wrote: Hi, I'm trying to load some data from a primary MySQL DB into a VMware image for RD purposes. Instead of doing a mysqldump of nearly 10G of data, I would like to just select a subset of it and load it into the VMware image. man mysqldump You will find it supports a where clause. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: £ (Great Britain Pound) breaks MySQL insert and update - disastrous overwrit e
Paul Warner wrote:
Ok, here is an update. I have now switched everything to UTF-8, database,
application, jsp page. I have added a tomcat filter that sets the request and
response encoding to UTF-8 presumably before anything else sees the request
(it seems to write to the log 10 times for each request). I added the
following code that processes all the fields before submitting to the database
for update or insert.
Charset utfCharset = Charset.forName(UTF-8);
CharsetDecoder decoder = utfCharset.newDecoder();
CharsetEncoder encoder = utfCharset.newEncoder();
try {
ByteBuffer bbuf = encoder.encode(CharBuffer.wrap(advertArr[i]));
CharBuffer cbuf = decoder.decode(bbuf);
advertArr[i] = cbuf.toString();
//log.info( after conversion: + advertArr[i]);
} catch ( CharacterCodingException cce ) {
log.info(character coding exception: + cce.getMessage());
}
I tested as I added each of these elements. The problem persists. Same
errors. Same massive, horrific overwrite of the table on every update. AND
the data displayed in the browser has a garbage character in front of the £
sign. Why All the books and internet pages I've looked at say this stuff
should work.
By fiddling with the encoding settings I did achieve something. Before
switching everything to UTF-8, I explicitly set everything to ISO-8859-1 (since
the £ sign is definitely included in ISO-8859-1), and for a while the update
refused to do any rows at all, rather than overwriting them all.
Again, the update and insert statements work when pasted into the mysql command
line utility. A perl script brings up the data nicely, and does an update
including a £ sign to just one row without a problem.
So, has anyone ever encountered this?? I'm going mad.
Turn on the querylog, and see what query mysql is actually getting.
--
Gerald L. Clark
Supplier Systems Corporation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE Inv_Id
Scott Hamm wrote: I'm trying to create a table as follows: CREATE TABLE Inv_Id ( ID INT(12) AUTO_INCREMENT PRIMARY KEY, MID INT NOT NULL, FOREIGN (MID) REFERENCES 'Model' (ID) ); How do I make ID to start out as '0001' for UPC barcode assignment? UPC barcodes are not sequential numbers. They are composed of a casecode prefix, a manufacturers id, a partnumber, and a check digit. You cannot use an autoincrement number as a UPC. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: updating table but afterwards there are duplicate entries that violate a key..
Peter Van Dijck wrote: Hi all, I have a table like this TABLE -- tagid taggerid objectid There is a primary key on (tagid, taggerid, objectid). First I remove that key, then I am changing the object id (because I've changed my objects), but what happens now is that I suddenly have duplicate (tagid, taggerid, objectid) rows. Is there a way to remove duplicate rows like this? Thanks for any pointers!! Peter Use ALTER TABLE IGNORE and put the key back. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick way to determine existence of an index?
Mark wrote: Dear MySQL-ers, Using MySQL 5.0.24a, is there a quick way to determine which columns on a table don't have an index? I want to do sort of s recursive loop, and add indices for all columns which don't have an index yet (and that over many databases). Thanks, - Mark Sounds like an extremely bad idea to me. You don't want to have any indicies you don't actually need. They will slow down all inserts and updates. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running a Staging and Development DB on the same server?
Jay Paulson wrote: I'm setting up our one server for staging and development. I am need to set up MySQL to have 2 copies of the same database on the same server. Could anyone direct me to some documentation or tell me how to do this? Thanks. What's wrong with having a test and production database? Just connect to the proper database when you start your application. mysqladmin create testdata mysqldump production | mysql testdata -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditional copy of values
Ravi Kumar. wrote: Dear All, I have 2 tables Table1 and Table2. In Table1 rowid is primary key and in Table2 rowid is primary key as well as foreign key (referring to rowid in Table1). There are several other columns in Table1, but Table2 has only one col - rowid. Then what is the reason for table2 ? I see no value for a single element table unless it is used to hold valid entries for another table. You are populating it from Table1, so it now has no reason to exist. Use Table1 instead. I want to insert rowid into Table2 conditionally: insert into Table2 (rowid) select T1.rowid from Table1 T1, Table2 T2 where T1.somecolumn = 'some condition' What is T2 doing in this select ? This is not a join, but a Cartesian product. But I want to insert only those rowid values which do not exist in T2. Use insert ignore. You already have a primary key on rowid, so you cannot add a rowid that already exists. Please help. I am using MySQL version 4.1.14-standard-log. Hence subqueries are not a solution. Thanks, Ravi. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What happened to left join under version 5?
Mailing List Receiver wrote:
Worked under version 4. Does not work under version 5.
$Qstr = select distinct replace(reftraffic.refurl,'http://',''),\
count(*)\
from reftraffic,site\
left join links\
on
left(replace(reftraffic.refurl,'http://',''),locate('/',replace(reftraffic.refurl,'http://',''))-1)\
= links.url\
where links.url is null\
and reftraffic.web = site.siteurl\
and site.vend_addr = \$Vendor\\
group by replace(reftraffic.refurl,'http://','');
DBD::mysql::st execute failed: Unknown column 'reftraffic.refurl' in 'on
clause' at /home/common/proj/bin/reftraffic.pl line 59.
Use from reftraffic inner join site on . instead
of an implicit join. The precedence of joins has been corrected
in version 5.
This has been asked and answered about a hunderd times.
For more details, you may search the archives
mysql describe reftraffic;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| web| varchar(40) | NO | | NULL| |
| datim | datetime | NO | | -00-00 00:00:00 | |
| refurl | varchar(255) | NO | | NULL| |
| target | varchar(255) | YES | | NULL| |
++--+--+-+-+---+
mysql describe links;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| url | varchar(100) | NO | MUL | NULL||
| linktype | varchar(16) | YES | | NULL||
| rank | double(10,0) | YES | | NULL||
| email| varchar(40) | YES | | NULL||
| dateadd | date | YES | | NULL||
| verified | enum('N','Y')| YES | | N ||
| vendor | varchar(8) | NO | | GWA ||
| deleted | enum('Y','N')| YES | | N ||
| info | text | YES | | NULL||
| image| varchar(100) | YES | | NULL||
| username | varchar(32) | YES | | NULL||
| password | varchar(32) | YES | | NULL||
| city | varchar(40) | YES | | NULL||
| state| varchar(40) | YES | | NULL||
| country | varchar(40) | YES | | NULL||
| rowid| int(11) unsigned | NO | PRI | NULL| auto_increment |
+--+--+--+-+-++
Server version 5.0.22
--
Gerald L. Clark
Supplier Systems Corporation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL operators
molemenacer wrote: I have a query that searches on a number of criteria and would like help on the last line I have included my code below: where jobs.statusid in (6) and ifnull(jobs.currworkerid,'') like '%' and jobs.dictatorid like '%' and jobs.custcode like '%' and jobs.deptcode like '%' and jobs.specialitycode like '%' and ifnull(jobs.prevworkerid,'') like '%' and jobs.jobid like '%' and jobs.supplierid like '%' and Approveddate between '2006-09-14' and '2006-09-21' or Approveddate Is Null This retrurns all jobs whether they are status 6 or not, i need to have a comparison on approveddate, it is either between the two dates or null, but is status 6. ( Approveddate between '2006-09-14' and '2006-09-21' or Approveddate Is Null ) Your query returned all rows where approveddate is NULL. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 versions of mysql on Unix: problem with the socket
Duhaime Johanne wrote: I am running mysql 4.1.7 on solaris 9. I want to install 2 versions (versions 4.1.7 and 5) on the server. I am reconfiguring my scripts in that perspective. I have a script that start the mysql v4 so that I finally have: 7002 pts/4S 0:00 /bin/sh /seqweb/mysql4/bin/mysqld_safe --defaults-file=/seqweb/mysqldata/my.cnf --port=3306 --socket=/tmp/mysql4.sock --pid-file=/seqweb/mysqldata/mysql4.pid --user=mysql --datadir=/seqweb/mysqldata 7023 pts/4S 0:13 /seqweb/mysql-4.1.7/libexec/mysqld --defaults-file=/seqweb/mysqldata/my.cnf --basedir=/seqweb/mysql-4.1.7 --datadir=/seqweb/mysqldata --pid-file=/seqweb/mysqldata/mysql4.pid --port=3306 --socket=/tmp/mysql4 The problem is that I always have to give the --socket to all the clients, the port number is not enough. Ex: put a .my.cnf file in the client's home directory that specifies the socket. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal versus Float Point Type
Renato Golin wrote: Jerry Schwartz wrote: The difference is that, for example, .01 can be represented exactly in decimal; but float types are binary, so .01 cannot be represented exactly. This can lead to all kinds of trouble when doing arithmetic, the errors accumulate. Yes! but that can also lead to some other problems... ;) Check this example: myslq create table numbers (a decimal(10,2), b float); myslq insert into numbers values (100, 100); mysql select @a := (a/3), @b := (b/3), @a * 3, @b * 3 from numbers \G *** 1. row *** @a := (a/3): 33.3 @b := (b/3): 33. @a + @a + @a: 99.90 @b + @b + @b: 100 The decimal did exactly what's supposed to do on this cases, it truncated the rest, thus loosing the 1/3 part. So for sums the decimal is better, but for divisions the float is better, up to some point, of course. I mean, using DECIMAL will not give you a fail proof arithmetic in any means. It's one reason why most people write their loops with x + 1 rather than = x. That gets past the problem, but if you are adding together many values the final answer may be wrong. I'd rather use a better algorithm instead of a work around in those cases... ;) cheers, --renato How do you expect to split a dollar 3 ways? It is not the math you do that determins whether you use float or decimal, it is what you are modeling that is important. Dollars are decimal, and dollar calculations must be rounded to the nearest cent, or mill. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 to many relationships
Brian E Boothe wrote: hey guys ; it's not Customers Per issue it's the other way around issues per customer i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click Add issue the a form Comes up and i add the issue and Click Add issue, i need that issue to associate with that customer, every Customer Refrences the same issue table Then it is many to many. Many customers are associated with many issues. i have Customers /Issues as tables, -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile and newlines
Mark Nienberg wrote: I'm trying to use LOAD DATA INFILE to read a csv formatted file into a mysql 5.0.22 table. Some of the fields contain text that has newline characters in it. After reading the manual to learn how special characters are treated, I altered the csv file so newlines are represented by '\\n', that is two backslashes followed by a lowercase n. After loading the file, I find that the text fields do indeed contain '\n' as I hoped (one of the backslashes was stripped during import). But for some reason these newlines aren't treated as such by mysql or php. For example, the php function nl2br does not recognize and substitute them. I have other similar tables in the same database that I successfully constructed using the same method, but that was with a previous version of mysql (4.something). Something is different now. The command I used was: mysql load data infile '/share/store/library.csv' into table library fields terminated by ',' enclosed by '' lines terminated by '\r\n'; Any help appreciated. Mark You need to quote the actual linefeed character, not a backslash n. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky query
Morten wrote: Hi, Given the table keyval(key int(11), val int(11)), I would like to be able to retrieve the keys for which a row exist for given X values. Example: key value 18 19 110 28 38 310 48 49 410 411 Given values 8, 9, 10 the query should thus return 1 and 4. The possible number of values is variable. Can this be expressed somewhat more elegantly than multiple EXISTS subqueries? SELECT DISTINCT key FROM keyval outer WHERE EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 8) AND EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 9) ... Br, Morten How about: SELECT DISTINCT `key`, COUNT(`key`) AS c FROM `table` WHERE `value` in (8,9,10) HAVING c=3; -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky query
Morten wrote: Hi, Given the table keyval(key int(11), val int(11)), I would like to be able to retrieve the keys for which a row exist for given X values. Example: key value 18 19 110 28 38 310 48 49 410 411 Given values 8, 9, 10 the query should thus return 1 and 4. The possible number of values is variable. Can this be expressed somewhat more elegantly than multiple EXISTS subqueries? SELECT DISTINCT key FROM keyval outer WHERE EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 8) AND EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 9) ... Br, Morten I forgot the GROUP BY How about: SELECT DISTINCT `key`, COUNT(`key`) AS c FROM `table` WHERE `value` in (8,9,10) GROUP BY `key` HAVING c=3; -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Check out this Free software I found to document your IT infrastruct
Will L wrote: Rob Munsch, I am a member of the Nabble project. This is regarding a bad post by user itguy321. Just want to explain a few things. Nabble is a free mailing list archive/gateway that works like Gmane. Users can browse, search, and post via Nabble's web interface and the post will be forwarded to the mailing list as an email. A user will need to register with Nabble first, then he will need to subscribe to the mailing list to become a subscriber, only then can he post to that mailing list. You seem to be holding Nabble responsible for this bad post. Is it fair? Is it fair to blacklist Gmail just because a bad user sent a spam? A mailing list usually has a moderation mechanism to deal with bad posts. If you are a mailing list owner, you can simply ban that user. Nabble hates spam as much as you do. We are currently monitoring this user and we will take necessary action just like a mailing list owner will do. Feel free to send me a private email if you have more concerns. Regards, Will L Nabble.com Well, he also spammed the Centos Forums. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5.0] Left Join Problem
Stephen P. Fracek, Jr. wrote: I'm trying to upgrade from MySQL 4.1 to MySQL 5.0. A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss as to how to proceed. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; The above query now generates this error: Unknown column 'tab_a.id in 'on clause'. Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON (tab_b.id = tab_a.id) does not work. However, splitting the original query apart and grouping the Left Joins in one query and the regular joins in another query does NOT generates any errors: SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; - this is ok AND SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; - this is ok Why doesn't the original query work in MySQL 5.0? What do I need to do to make it work? TIA Stephen P. Fracek, Jr. [EMAIL PROTECTED] It is a join precedence issue. Use INNER Join instead of a comma. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a INNER JOIN db.tab_e ON tab_a.id2 = tab_e.id2 INNER JOIN db.tab_f ON tab_e.id3 = tab_f.id3 LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct problem
Tanner Postert wrote: I actually solved my own problem... SELECT t1.item_id, t1.dt, t1.text ,t3.* FROM table AS t1, table3 as t3 LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt t2.dt WHERE t2.item_id IS NULL; becomes SELECT t1.item_id, t1.dt, t1.text FROM (table AS t1, table3 as t3) LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt t2.dt WHERE t2.item_id IS NULL; amazing what a little set of parenthesis will do. thanks anyways. t3 is not joined at all. Re-write this using inner joins to see your problem. SELECT t1.item_id, t1.dt, t1.text FROM table AS t1 INNER JOIN table3 AS t3 ON ??? LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt t2.dt WHERE t2.item_id IS NULL; -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql hangs on connection attempt
Steve Strong wrote: we are using php to connect to our mysql database and when we call the php function with this line: $dbh = mysql_connect(localhost, george_full, passwd) or die (formatSQLError()); george_full has full priveleges on localhost with his password. localhost resolves to 127.0.0.1 using the host command pinging localhost returns packets as expected localhost is for a socket connection. If you want 127.0.0.1 than use 127.0.0.1. the execution of the script terminates. from the command line i've done the following: 1. connected to the database with the same credentials as above 2. selected a database 3. querried the database with appropriate results. i work for a school district and the central office people have just built a new LAN that involved changing the addresses of our dns servers. the servers are behind a NAT, and only their external addresses changed. this is a really tough bug, so any help would be GREATLY appreciated. thanks, steve connec -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime issue on MySQL 4.x
Willy wrote:
Hello,
I have a MyISAM table:
CREATE TABLE `dlr` (
`msisdn` varchar(20) NOT NULL default '',
`source` varchar(20) NOT NULL default '',
`operator_id` varchar(20) NOT NULL default '',
`sms_message` longtext NOT NULL,
`smsc_id` varchar(20) NOT NULL default '',
`sms_id` varchar(250) NOT NULL default '',
`dlr_smsc` text NOT NULL,
`dlr` tinyint(4) NOT NULL default '0',
`date_time` datetime NOT NULL default '-00-00 00:00:00'
) TYPE=MyISAM
I have a case here, on July 7 I imported some data into the table with this
query:
INSERT INTO dlr (source, msisdn, operator_id, sms_message, smsc_id, sms_id, dlr_smsc, dlr, date_time)
VALUES
('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW()),
('366508', '1234', 4, 'id:0943381383 sub:001 dlvrd:000 submit date:0606280317
done date:0606290317 stat:EXPIRED err:999 text:, 'IM33665', MD5(RAND()),
'RE:id:0943381383 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317
stat:EXPIRED err:999 text:', '2', '2006-06-28 03:17:00'),
('366508', '1234', 4, 'id:1226442088 sub:001 dlvrd:000 submit date:0606280317
done date:0606290317 stat:EXPIRED err:999 text:', 'IM33665', MD5(RAND()),
'RE:id:1226442088 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317
stat:EXPIRED err:999 text:Silahkan tawar: XDA ', '2', '2006-06-28 03:17:00'),
('366508', '1234', 4, 'id:1861980498 sub:001 dlvrd:000 submit date:0606280317
done date:0606290317 stat:EXPIRED err:999 text:', 'IM33665', MD5(RAND()),
'RE:id:1861980498 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317
stat:EXPIRED err:999 text:', '2', '2006-06-28 03:17:00'),
('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW())...
And when I try to fetch the data using PHP with this query:
SELECT operator_id, DATE_FORMAT(date_time,'%d-%m-%Y') AS tanggal, COUNT(*) AS
TOTAL FROM dlr WHERE 1=1 AND dlr = 8 AND MONTH(date_time) = MONTH(NOW()) GROUP
BY tanggal, operator_id ORDER BY date_time
The weird thing happened. Why does all the rows are shown or treated as July
7th data? If we see the imported data, there should be June 28, June
29.until July 7. Please help and many thanks for any reply.
Regards
Willy
Because you specified only for the month of July.
AND MONTH(date_time) = MONTH(NOW())
--
Gerald L. Clark
Supplier Systems Corporation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to understand why Select running out of memory if table not used
Gabriel PREDA wrote: The JOIN criteria was there: 'event.cid=data.cid' It was not there in the upper example he gave where he stated the problem. It was there in the later query he said he also tried. His query was fine: Select event.cid, event.timestamp from event, data Where ( event.timestamp between '2006-05-01' AND '2006-05-15' ) and event.cid=data.cid; It may be rewritten into: SELECT event.cid, event.timestamp FROM event JOIN data ON event.cid=data.cid WHERE event.timestamp BETWEEN '2006-05-01' AND '2006-05-15'; ... Or ... SELECT event.cid, event.timestamp FROM event JOIN data USING(cid) WHERE event.timestamp BETWEEN '2006-05-01' AND '2006-05-15; Although that was not the issue... Maybe you ran out of memory into one of: max_allowed_packet net_buffer_length max_join_size Try enlarging those values... on the server also... But first try to run with '--compress' maybe this will fix... -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Allow user to create databases, but limit all privileges to other databases
Isaac Davis-King wrote: I would like to create a user that has the ability to create databases. I would also like the user to be able to have all privileges including grant over the databases they create. However, I do not want them to have any access to other databases on the server. I've been digging through the documentation for a while and have not been able to figure out a solution, even though it seems like this would be a pretty standard requirement in shared hosting environments. Any help would be greatly appreciated! PS. On the Databse Administration support forum there is a post regarding this exact same issue, and I was originally going to add a 'me too!' response to that post, but the registration system for the forums seems to be broken and I was never able to successfully log in to post. You asked the same question 2 hours ago. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple joins
Steffan A. Cline wrote: What am I missing here? select m.*, d.discussion, d.discussion_id, u.user_id, t.topic_id from forums_messages left join forums_members m, forums_discussions d, users u, forums_topics t on m.topic_id = t.topic_id, t.discussion_id = d.discussion_id where m.topic_id = 1; Is it legal to do multiple joins like this? Thanks Steffan forum_messages does not take part in any selection or any where clause. You have 5 tables listed, with only 4 of them appearing in 2 disjointed, and improperly formed joins. From the comma separated table list after a LEFT JOIN, I am not sure whether you intend them to be LEFT JOINs or INNER JOINs. Try: SELECT fields FROM file1 LEFT JOIN file2 ON something INNER JOIN file3 ON something INNER JOIN file4 ON something INNER JOIN file5 ON something WHERE somecondition -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recommended backup scripts for mysql databases
Tim Lucia wrote: I am in the process of designing the backup procedures for a soon-to-be production DB. I have gone back and forth on mysqldump and mysqlhotcopy. As I see it (for MyISAM tables), the hot copy backs up faster, restores faster, but does not allow for selective restores (Hello, Support? I accidentally deleted student Tim Lucia... can you get him back). Mysqldump is slower to back up, slower to restore, but allows for selective restores. Yes it does. Copy the table you want to a separate database ( directory ). INSERT INTO active.student SELECT * from backup.student WHERE name='Tim ucia'; Are those the only things to consider? I know that piping mysqldump into gzip resultThis on condition does not include the table being joined.s in disk space savings of the dump file, whereas mysqlhotcopy requires n * 2 free bytes on your system. But at least you know in advance exactly how much space it will take... Tim -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why didn't I insert small number into table?
Xiaobo Chen wrote: Hi, all I have a table like this: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | id_freq | int(11) | | PRI | 0 | | | id_theta | int(11) | | PRI | 0 | | | es | double | | | 0 | | +--+-+--+-+-+---+ the values for 'es' is very small, like 10^-8. When I checked the result after running my code, the column for 'es' are still '0's. Could anybody give a hint why this occured and how to avoid this? Thanks in advance. Xiaobo Double defaults to double(16,4) and you have more than 4 decimal places. Try double(16,10). -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error loading data from file ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
Ferindo Middleton wrote: I'm trying to load data into a table from a file but I get an error message: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed This error message isn't very specific as to what is going wrong and I have no idea what it is about the data file that is wrong. Of course, I know that there is in fact data in the file and that it is proper data types matching the table structure so I don't know why this error message is occuring or what it is about the file that's stopping it from being loaded. Since you did not show us the data, nor the command you used to load the data, we can't tell you what is wrong. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left Join Help
Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage These 5 tables are not joined on anything. LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system load? Thank you, -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left Join Help
I ammend my previous post. Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage Use INNER_JOIN and use ON clauses. tvc, tvax, and tvage are not joined at all, producing Cartesian Products. LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) This on condition does not include the table being joined. WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system load? Thank you, -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4 Kernel Panic-ing
John May wrote: I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is restarting itself every 2-3 days. It appears that it is due to a kernel panic, though I don't have direct access to the machine (colocated) to verify when it occurs. There are no crash.logs from MySQL, and watchdog and system.logs show nothing. I have culled the following panic.log - can anyone tell if MySQL is the cause of such? Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have tried two totally different servers, and the problems continue. - John * Mon Jun 12 06:53:02 2006 Unresolved kernel trap(cpu 0): 0x300 - Data access DAR=0xFF864A5C PC=0x00056218 Latest crash info for cpu 0: Exception state (sv=0x3E6ABC80) PC=0x00056218; MSR=0x9030; DAR=0xFF864A5C; DSISR=0x4000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x000C (0x300 - Data access) Backtrace: 0x 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4 Proceeding back via exception chain: Exception state (sv=0x3E6ABC80) previously dumped as Latest state. skipping... Exception state (sv=0x0091F280) PC=0x; MSR=0xD030; DAR=0x; DSISR=0x; LR=0x; R1=0x; XCP=0x (Unknown) Kernel version: Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC panic(cpu 0): copyin/out has no recovery point Latest stack backtrace for cpu 0: Backtrace: 0x00083498 0x0008397C 0x0001EDA4 0x00090C38 0x0009402C Proceeding back via exception chain: Exception state (sv=0x3E6ABC80) PC=0x00056218; MSR=0x9030; DAR=0xFF864A5C; DSISR=0x4000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x000C (0x300 - Data access) Backtrace: 0x 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4 Exception state (sv=0x0091F280) PC=0x; MSR=0xD030; DAR=0x; DSISR=0x; LR=0x; R1=0x; XCP=0x (Unknown) Kernel version: Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC How could a user space program like mysqld possibly cause a kernel panic? Sounds like bad kernel or bad hardware. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on full text search scores, different content same score
Mark Steudel wrote:
I was doing a full text search and had a question on why two different
entries got the same score:
Here is my select statement
SELECT
id,
pubyear,
MATCH ( title ) AGAINST ( 'Nursing home federal requirements, guidelines to
surveyors, and survey protocols (MLM)' ) AS score,
title
FROM tblbook
WHERE MATCH ( title ) AGAINST ( 'Nursing home federal requirements,
guidelines to surveyors, and survey protocols (MLM)' )
Here are the two results that come back:
title: Nursing home federal requirements, guidelines to surveyors, and
survey protocols
score: 15.230528838754
title: Nursing home federal requirements, guidelines to surveyors, and
survey protocols (MLM)
score: 15.230528838754
I'm sure its work as expected, but I was hoping some could enlighten me as
to why these scores are the same. There are 35 entries in the database and
these are the only two titles that are similar. I also tried IN BOOLEAN MODE
as well.
Thanks, Mark
Perhaps because '(' and ')' are ignored, and 'MLM' is a three letter
word, and not indexed.
--
Gerald L. Clark
Supplier Systems Corporation
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: correct syntax
Mike Blezien wrote: Hello, we need to query one of database tables that sore usernames, and many of them have spaces in them that need to be removed. what would be the correct function to identify each username column value that has a space(s) in them? TIA, Mike(mickalo)Blezien How about LIKE % % -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ADVICE: Best way to store multi values
Assuming you have table staff with an id and name, and title with id and title, You create a join table. Staff_id Title_id 1 1 1 2 2 1 2 3 Then: Select name,title FROM staff s , title t, jointable j WHERE s.id=j.staff_id AND j.title_id=t.id; Mark Worsdall wrote: Hi, I have a table called staff which currently has a column called subjectTitles_id which is the type INTEGER. I have another table called subjectTitles which contains 12 subject titles. So that is how a member of staff is assigned a subject. Trouble is I need to be able to assign a multiple number of subjects to anyone member of staff. The method I thought to use was change staff.subjectTitles_id from INTEGER type to CHAR type and store a string of CSV numbers which correspond to the id's of the table subjectTitles, e.g.:- 1;2;7;10 Is this the way? It seems quite ungraceful. M. -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Sending data status
You are joining 5 tables with an OR on only two relations. Give it a year !! Surat Singh Bhati wrote: If I run the following query , mysql server process hangs forever and it shows the "Sending data" status. select Count(*) from Registration,Candidates,CompanyJobs,Resumes,CandQual where CompanyJobs.qualcode=Candidates.qualcode or CompanyJobs.qualcode=CandQual.qualcode Below the explain: EXPLAIN select Count(*) from Registration,Candidates,CompanyJobs,Resumes,CandQual where CompanyJobs.qualcode=Candidates.qualcode or CompanyJobs.qualcode=CandQual.qualcode LIMIT 0, 30 table typepossible_keys key key_len ref rows Extra Registrationindex RIMARY 8 5056 Using index Resumes index SpCode 2 5087 Using index CompanyJobs ALL Qualcode268 Candidates ALL QualCode4718 CandQualALL Qualcode9027where used Server version 3.23.33-log My talbes having indexed on "Qualcode" but EXPLAIN shows that it is not using the same. I can run the query select Count(*) from Registration,Candidates,CompanyJobs,Resumes,CandQual without where clause. Please suggest me the solution, I have enogh free disk and RAM. Please reply me on my ID, I have not subscribed to the list. Thanks -Surat Singh Bhati - 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL data structure
Asaf Maruf Ali wrote: Hello. I am interested to know the structure of MySQL. My ref book "MySQL and mSQL" by O'Reilly states that for a table named book under a database Test following is the structure: book.ISM book.ISD and book. frm On my system MySQL 3.23.27 beta the structure is different. book.MYI book.MYD book.frm Any suggestions would be welcome. Suggestions on backing up the MySQL db as well. Anyone has experience of hosting MySQL/PHP ? Any recommended server/host/company? Thanks in advance Apparently you have yet to read the manual that came with your MySQL. You are describing isam vs. Myisam tables. Oh, I think "MySQL" by DuBois ( New Riders ) is better than "MySQL and mSQL". I own both. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Read/write table lock
Fabio Galarraga wrote:
Yes, I think this is the problem. But how can I make a read/write lock with
only one lock?
Best regards,
Fabio.
--
De: Gerald L. Clark[SMTP:[EMAIL PROTECTED]]
Enviado el: Martes, Marzo 20, 2001 04:33 PM
Para: Fabio Galarraga
CC: 'Mysql mailing list'
Asunto: Re: Read/write table lock
Fabio Galarraga wrote:
If you issue a READ lock, nobody can write to the table.
If you Issue a Write lock, only you may read or write to the table.
Nobody can read a file you have locked for write.
Hi to all:
Somebody knows how to make a read/writa table lock?
I'm trying with java source:
stmt.execute("LOCK TABLES table-x READ, table-x WRITE");
stmt.execute("UNLOCK TABLES");
but it fails.
Best regards,
Fabio Galarraga
[EMAIL PROTECTED]
Well you are locking table-x twice in the same lock.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: selecting null values
Title will never be = NULL. NULL is not equal to anything, not even NULL. SELECT * FROM table WHERE title IS NULL LIMIT 1; Peter Szekszardi wrote: Hi, I have a database which has a text field (let's name it title). The field is NULL initially. Now I have about a hundred thousand records in the db, where the title field is NULL. I have to fill these NULL title fields with the appropriate data, so I have to select them one-by-one, calculate the title field, and update the data. So my query would be: SELECT * FROM table WHERE title = NULL LIMIT 1; But it does not work. One of you wrote, that NULL is not equal with anything, not even with NULL. Could you please help me, what kind of select should I write in situations like this? In this particular case, I will do find a workaround, but I am curious, how to handle NULL values. Regards, Peter Szekszardi PortoLogic Ltd. Portal building, prime number generating, script hacking and more... The prime number of the day is 262559183 - 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replace qeustion
That depends. Just the one 9, or all instances of 9 in 'C'? This is a basic SQL question, not a MySQL question. I would suggest finding a book on SQL. You could try: update Table_1 set c=20 where a=10 and b=18 and c=9; This will guarantee that only the first line ( O one just like it ) will be updated. soon chee keong wrote: Table_1 - A B C - 1018 9 4 3 1 -- how can i change column C's "9" to say "20" without inserting a new record and columns A and B remain unchanged? do i use REPLACE or what?please advice. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)
Well, since PHP is trying to use a socket in the wrong directory, you might check the PHP list to see if they can help you. This is not, strictly speaking a MySQL problem, so waiting here for the correct answer may not be the quickest route to the answer. I have seen several answers indicating that you need to set the socket option in the PHP ini file. denis mettler wrote: Hi, I'm really tired of this problem... Here's the error message: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) in /usr/local/httpd/htdocs/dbopen.php3 on line 5 I have Suse 7.1 Standard installation with apache1.3.14 and php3.0.17 Apache is running Simple PHP Scripts without MYSQL CONNECTION, too. Mysql 3.23 is okay. Mysqld is running i started mysql and created a db. But the only thing i could not do is to make mysql_connect with a php script. i have the php3.ini and the /etc/httpd/ directory and there is an entry: mysql.default_socket= /tmp/mysql.sock the mysql.sock is there! I DON'T HAVE ANY IDEA WHY THIS PROBLEM OCCURS! If u need more Information please let me know Please help Regards Denis - 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: (yet another) sock issue
Charles Mgnin wrote: Although the my.cnf file specifies socket = /tmp/mysql.sock the server expects mysql.sock to reside in /var/lib/mysql [mysql@localhost]$ /usr/bin/mysqladmin -u root -p password Enter password: /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists! Any thoughts ? /etc/my.cnf should comtain at least: [mysqld] socket=/tmp/mysql.sock user=mysql [client] socket=/tmp/mysql.sock Make sure /etc/my.cnf is world readable, and that mysql owns all of its directories and files. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [Fwd: Re: problems with linking]
edit /etc/ld.so.conf add a line for the directory that contains your library run ldconfig Alonso Guarisma wrote: Dear MySQL Sirs (or Andrew Schmidt) Please see attachament for error messages. Thaks in advance for any help. Alonso Guarisma Original Message Subject: Re: problems with linking Date: Mon, 19 Mar 2001 19:02:47 -0500 From: Alonso Guarisma [EMAIL PROTECTED] To: Andrew Schmidt [EMAIL PROTECTED] CC: Keith Rambo [EMAIL PROTECTED] References: [EMAIL PROTECTED] 00cd01c0b0c5$151f69c0$[EMAIL PROTECTED] Dear Andrew Schmidt, thanks for your quick response. Unfortunatelly, I still get the same error and your instructions seem not to work at all. Please tell me if I am doing something else wrong and refer to the attachment where I show what i did, regards Alonso Guarisma Andrew Schmidt wrote: make sure you have -L/usr/local/mysql/lib/mysql/ in your compile and make sure that /usr/local/mysql/lib/mysql/ is in your shared object cache. ldconfig -r will list your current cache entries. if /usr/local/mysql/lib/mysql/libmysqlclient.so.10 is not in there then add the path: /sbin/ldconfig -m /usr/local/mysql/lib/mysql/ to add the directory to the cache. regards, -- Andrew - Original Message - From: "Alonso Guarisma" To: Cc: "Keith Rambo" Sent: Monday, March 19, 2001 5:26 PM Subject: problems with linking Dear MySQL Sirs, I have been trying to run a simple client program using the C API and unfortunatelly I am still getting the same error over and over again: ./client1: error in loading shared libraries: libmysqlclient.so.10: cannot open shared object file: No such file or directory Attached is the output of the libraries directory when I execute ls -l I cannot understand why the program does not find the library, I would really appreciate any help from you regarding this matter, because I already tried the options you provide in the MySQL manual (Section 20.3 Problems when linking the MySQL client library). I also think the problems related to this library is causing me failures when I try to install the GUI for MySQL. I hope to hear from you as soon as possible, sincerely Alonso Guarisma alonso@linux:~ ls -l /usr/local/mysql/lib/mysql/ total 972 -rw-r--r-- 1 mysqladm mysqlgrp11822 Feb 11 16:02 libdbug.a -rw-r--r-- 1 mysqladm mysqlgrp34846 Feb 11 16:02 libheap.a -rw-r--r-- 1 mysqladm mysqlgrp15246 Feb 11 16:02 libmerge.a -rw-r--r-- 1 mysqladm mysqlgrp 230180 Feb 11 16:02 libmyisam.a -rw-r--r-- 1 mysqladm mysqlgrp23312 Feb 11 16:02 libmyisammrg.a -rw-r--r-- 1 mysqladm mysqlgrp 182284 Feb 11 16:02 libmysqlclient.a -rwxr-xr-x 1 mysqladm mysqlgrp 721 Feb 11 16:02 libmysqlclient.la lrwxrwxrwx 1 mysqladm root 24 Feb 11 16:02 libmysqlclient.so - libmysqlclient.so.10.0.0 lrwxrwxrwx 1 mysqladm root 24 Feb 11 16:02 libmysqlclient.so.10 - libmysqlclient.so.10.0.0 -rwxr-xr-x 1 mysqladm mysqlgrp 134367 Feb 11 16:02 libmysqlclient.so.10.0.0 -rw-r--r-- 1 mysqladm mysqlgrp31540 Feb 11 16:02 libmystrings.a -rw-r--r-- 1 mysqladm mysqlgrp 190742 Feb 11 16:02 libmysys.a -rw-r--r-- 1 mysqladm mysqlgrp 102344 Feb 11 16:02 libnisam.a - 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 To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Dear MySQL Sirs or Andrew: Here I pasted the output of what I did, I still get the same error... alonso@linux:~ gcc -c -I/usr/local/mysql/include/mysql client1.c alonso@linux:~ gcc -o client1 client1.o -L/usr/local/mysql/lib/mysql -lmysqlclient ls -l client1 -rwxr-xr-x 1 alonso users 12118 Mar 19 18:36 client1 alonso@linux:~ client1 client1: error in loading shared libraries: libmysqlclient.so.10: cannot open shared object file: No such file or directory alonso@linux:~ ldconfig -r ldconfig: option requires an argument -- r alonso@linux:~ ldconfig -m /usr/local/mysql/lib/mysql/ ldconfig: invalid option -- m Try `ldconfig --help' or `ldconfig --usage' for more information. alonso@linux:~ ldconfig -l /usr/local/mysql/lib/mysql/ ldconfig: Ignored file /usr/local/mysql/lib/mysql/ since it is not a regular file. Although the executable is created, I cannot run it because I obtain the same error (regarding the libmysqlclient.so.10) Thanks in advance for any other help regarding
Re: select ... having bug?
Having works on the result table, not the source tables. Since you did not select cnt, having can not examine it. Maybe you ment 'where' instead of 'having' ? \"Piotr Gapinski\" wrote: small problems with query below... my fault? mysql desc poll_answer; +-+-+--+-+-+---+ | Field | Type|Null | Key | Default | Extra | +-+-+--+-+-+---+ | poll_id | int(10) | | | 0 | | | cnt | int(10) | | | 0 | | | answer | varchar(60) | | | | | +-+-+--+-+-+---+ 3 rows in set (0.00 sec) mysql select answer from poll_answer having cnt=1; ERROR 1054: Unknown column 'cnt' in 'having clause' mysqladmin Ver 8.18 Distrib 3.23.35, for pc-linux-gnu on i686 -- Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.35 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 3 days 10 hours 24 min 20 sec Threads: 1 Questions: 4192 Slow queries: 0 Opens: 116 Flush tables: 1 Open tables: 23 Queries per second avg: 0.014 -- Piotr Gapinski mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 3.22.32 and RedHat 7.0
Add execute permission to mysql.server. It is compiled without it. Tim Thorburn wrote: Hello, I'm a MySQL newbie (hopefully, there's a few others of me around so I won't feel like I stick out so much). I've setup a Linux box running Red Hat 7.0 with Apache 1.3.12, PHP 3.0.18, and MySQL 3.22.32 (I know its an older version, but our web hosting service has not upgraded their version and it does not sound as though they plan to do so any time soon). All seems to be running well, except for the unfortunate problem that I cannot get mysql to start. I have followed the steps in the documentation provided with the source code from mysql.com I have MySQL installed at /usr/local/mysql-3.22.32/ when I type mysql.server start I am told that either the file does not exist or I have no permission to use it. I'm logged on as root, and I've setup the user and group to mysql as the manual states. Has anyone else had any difficulties getting MySQL and RedHat 7.0 to work? As I said, I'm rather new at this so I'm grabbing at straws. Thank you -Tim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql 3.22.32
houda chraibi wrote: Hello everybody, I have some pb to administrate mysql, I can't create a new database error: you are an anonymous localhost I just can create a new tables in database already exist. what can I do , please help m!!! Read chapter 6sss!!! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question for the mysql install..
You mus use GNU tar. wrote: Hello!! I have a question for the mysql install. Type of mysqlsource distribution Mysql Versionmysql-3.22.32.tar.gz My System SunOS stachyonbse01 5.7 Generic sun4u sparc SUNW,Ultra-60 After upload gunzip mysql-3.22.34.tar.gz | tar -xvf - Result of the above command(gunzip,tar) follows: . . x mysql-3.23.35/sql-bench/Results/wisconsin-solid-Linux_2.0.36_i686-cmp-mysql,pg,solid, 676 bytes, 2 (tape block) x mysql-3.23.35/sql-bench/Results-win32, 0 bytes, 0 (tape block) x mysql-3.23.35/sql-bench/Results-win32/ATIS-access_odbc-win98-cmp-access,mysql, 978 bytes, 2 (tape block) tar: (Directory) Checksum (error) Because of this directory's checksum error, I confused whether I continued installing or not. In spite of this error I continued installing this program , So the following message is displayed. make make[2]: Leaving directory `/data1/kkablue/mysql/mysql-3.22.32/bench' Making all in support-files make[2]: Entering directory `/data1/kkablue/mysql/mysql-3.22.32/support-files' make[2]: *** No rule to make target `all'. Stop. make[2]: Leaving directory `/data1/kkablue/mysql/mysql-3.22.32/support-files' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/data1/kkablue/mysql/mysql-3.22.32' make: *** [all-recursive-am] Error 2 make install cp: ./limits make[2]: *** [install-data-local] Error 2 make[2]: Leaving directory `/data1/kkablue/mysql/mysql-3.22.32/bench' make[1]: *** [install-am] Error 2 make[1]: Leaving directory `/data1/kkablue/mysql/mysql-3.22.32/bench' make: *** [install-recursive] Error 1 What can i do for it, Please I will thank you to tell me how to install this program successful. Thank you for your attention !! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't connect server through socket /var/lib/mysql/mysql.sock(111)
It is more likely /tmp/mysql.sock tmp has no 'e'. denis mettler wrote: Hi, I did. but the problem is the same. regards denis On Tuesday 20 March 2001 17:45, you wrote: try: [mysql] mysql.default_socket = /temp/mysql.sock Cal http://www.calevans.com -Original Message- From: denis mettler [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 20, 2001 10:40 AM To: [EMAIL PROTECTED] Subject: Re: Can't connect server through socket /var/lib/mysql/mysql.sock(111) Hi, i deleted mysql in /var and make re-install in /usr/local/ but received the same error. so i make an entry in the php3.ini [mysql] mysql.default_socket = /temp where i found the mysql.sock the error message is still there? Other ideas? regards denis On Tuesday 20 March 2001 14:52, you wrote: it looks like that: [MySQL] mysql.default_socket= /path/to/mysql/socket but I think thats not the problem. has your apache enough rights to access the directory /var/lib/mysql ? if not edit /etc/group an add "apache" to group "mysql". - "mysql:x:27:apache" or something similar, then make "chmod g+rx /var/lib/mysql", this should work. if not try to make a "chmod o+rx /var/lib/mysql", this should always work, but I think it isnt a good idea to leave it that way. Egon --- URL: http://www.snb.at mailto:[EMAIL PROTECTED] --- http://www.grusskarte.at denis mettler [EMAIL PROTECTED] 20.03.01 15:02 An: [EMAIL PROTECTED] Kopie: Thema: Can't connect server through socket /var/lib/mysql/mysql.sock(111) Hi, I get the following message if i try to connect to server: Can't connect server through socket /var/lib/mysql/mysql.sock(111) Cal said, i should modify my php.ini and should give the exact definition for the mysql socket. but i don't have an entry in the php.ini for mysql socket. any ideas? T.i.a. Regards denis - Before posting, please check: http://www.mys ql.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 - 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 - 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 - 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to put and get gziped file to/from mysql db
Did you quote the data before inserting it? [EMAIL PROTECTED] wrote: Hello I am looking for help how to put gziped plain data file to mysql DB and then get it from there. Every time I try OUTFILE to file and then gzip -d file name I got file corrupted Any help will be appreciated Leo ** The information contained herein is confidential and is intended solely for the addressee(s). It shall not be construed as a recommendation to buy or sell any security. Any unauthorized access, use, reproduction, disclosure or dissemination is prohibited. Neither SOCIETE GENERALE nor any of its subsidiaries or affiliates shall assume any legal liability or responsibility for any incorrect, misleading or altered information contained herein. ** - 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Read/write table lock
Fabio Galarraga wrote:
Hi to all:
Somebody knows how to make a read/writa table lock?
I'm trying with java source:
stmt.execute("LOCK TABLES table-x READ, table-x WRITE");
stmt.execute("UNLOCK TABLES");
but it fails.
Best regards,
Fabio Galarraga
[EMAIL PROTECTED]
Well you are locking table-x twice in the same lock.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Best column type for an IP address
Jason Bell wrote: Hello all! Does MySQL have a function that converts an IP address from dotted decimal to binary, and back again? Jason Bell INET_NTOA INET_ATON I'll leave it as an exercise to you to read Manual Chapter 7.4.12. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: error with mysql configuration.
Make sure all directories in the path to your mysql.sock file are world readable and searchable. Abid Ghufran wrote: Dear All, I am trying to setup a web based email setup on RedHat Linux 7 platform. I am using Horde 2.2, Imp 1.2, Php 4, Apache 1.3 alongwith the following rpms required for the MySql setup: (I am running the entire setup on my single host for test purposes) 1) mysqlclient9-3.23.22-3 2) php-mysql-4.0.3pl1-1 3) mysql-server-3.23.22-6 4) horde-mysql-1.2.4-1rh7 5) mysql-3.23.22-6 Although the required version was 3.23.24 for mysql and mysql-server, these are not available at all the three ftp sites within my reach and mentioned in the "horde-latest.README": (ftp.horde.org, ftp.sunsite.org.net and ftp.sourceforge.net) Now the problem is as follows: 1) When i try to check the test.php3 i get the following status of the installed modules (alongwith other information): IMAP Support: Yes. LDAP Support: No. MySql Support: Yes. PostgreSQL Support: No. (I have not yet configured LDAP and PostgreSQL.) But when i access "Click here to test PHPLIB for Horde" link i get this warning: Warning: Cant connect to local MySql server through socket '/var/lib/mysql/mysql.sock' (111) in /var/www/horde-phplib/db_mysql.inc on line 73 I checked this code (at line 73) and found mysql_pconnect() thus i assume (on the basis of my limited knowledge) that the problem might be with the database server connection. The MySql server is running. File mysql.sock exists. The database for the horde-phplib and imp is created. The command "mysql mysql_create.sql" runs. I have the entire setup on a single machine for test purposes (i.e. MySql server, Apache Server and etc.). I have yet to run the cinfiguration file "setup.php3" on the horde directory of my web site. When i run "mysqladmin version" in the output i get that the localhost is connected via Unix sock, and when i run "mysqladmin -h `hostname` version variables" i get that the localhost is connected via TCP/IP port. I think that either the socket or the TCP/PI port should have been used, as given with the mysql configuration. I have given all the details intentionally to give you an idea of the the entire setup. All kind of thoughts are appreciated and anticipated. Thank you very much. Abid Ghufran. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Add password to a existing database/table
Read chapter 6 of the MySQL manual. It is explained there. Nyon wrote: Hi, I am a newbie in Mysql and is confused about setting login/passwords. Just want a simple security measure as I am the only user. Here's the scenario: I managed to connect using PHP to mysql via the mysql_connect without using any hostname/password. What do I change to impose this security measure. The hostname and password will be keep in a *.inc file outside the web root directory. Thanks Nyon - 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 - 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
