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]
Connect fails but only from this one file WHY
i am developing a php app. in the beginning of every file there is a require_once(./setup.php); that does the housekeeping, fetches the connection credentials and connects to the database. All that worked fine until this morning I made a new file. Now I get a connection failure when I try to connect but ONLY from this one php file. I can connect to the server fine manually, the server is not unduly loaded, all the other apps that use this db server are fine and even the other files in THIS app using THIS particular db seem to be connecting fine. Well actually I should say that one of the other files *did* get this error once but on a subsequent call it corrected itself. I should also say that the bad file did connect fine the first time I ran it but I haven't been able to make it do it since. Is it possible that the bad new file is doing something bad that boggles subsequent connections? If so what? It is using exactly the same include file that works in all the other files.I'm stumped Thank you... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to get a count from this query
SELECT avg(abs(pmd.value) - (mypmd.value))) as diff, pm.mname, pmd.uid FROM pmdata pmd, mnames pm, pmdata mypmd WHERE mypmd.uid= ? AND pmd.pmid=pm.pmid AND mypmd.pmid=pm.pmid AND pmd.uid != mypmd.uid GROUP BY pmd.pmid, pmd.uid ORDER BY pmd.uid What I would like also to return in this query is a COUNT of the number of pmd.uid of each different value so I know how many values I got from uid#1, uid#2 etc. I tried putting COUNT(pmd.uid) as numdelta but it just gave me a 1 in every row. The problem is I am not doing a straight GROUP BY pmd.uid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suppress table header when using ODBC
Gleb Paharenko wrote: Hello. I am using a desktop program that imports data from a mysql What program? Printbench Pro for one little glitch: it adds one extra row at the beginning of the dataset with the names of the columns in it. I need for that Does you program show column names in the numeric fields? In a manner of speaking, Yes. When you DO the query it shows all the data in spreadsheet fashion. The columns all have headers and you can't edit it. THen when you go to layout, all the field headers are used as placeholders and may be drug around and formatted in the layout. I have also contacted Elkriver tech support about this, but I thought it might be an issue I could solve with a mysql solution. The problem is I want to bar encode one of the fields, and the bar encoding I want to use is numeric only. So it craps out the whole column just because the first row has a non numeric field header. and that is why I need it to go away. Gerald Taylor wrote: Hello, I am using a desktop program that imports data from a mysql database using the ODBC mysql driver and everything works fine except for one little glitch: it adds one extra row at the beginning of the dataset with the names of the columns in it. I need for that column name row to NOT be there. Do I do something to the query to suppress is or is it some setting I set up? I've googled and nothing. MySQL 4.1 if it matters. I know I remember reading somewhere how to suppress this. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Suppress table header when using ODBC
Hello, I am using a desktop program that imports data from a mysql database using the ODBC mysql driver and everything works fine except for one little glitch: it adds one extra row at the beginning of the dataset with the names of the columns in it. I need for that column name row to NOT be there. Do I do something to the query to suppress is or is it some setting I set up? I've googled and nothing. MySQL 4.1 if it matters. I know I remember reading somewhere how to suppress this. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect to mysql server from another windows box
MySQL 5.0.12, P2P Microsoft network I cannot get the manual's instructions (24.1.9.6) to work for connecting from a mysql client on a windows machine to a mysql server running on another windows machine (xp and w2kpro repsectively) The client machine can ping the server machine by name or IP address. One each machine, a local client successfully connects to the local mysql server as localhost. In mysql server on the w2k box, a client row in mysql.user has been created with: user=the exact value used in the ODBC Admin dialog password=the exact value used in the ODBC Admin dialog Host=name of the machine being used as a client 'skip-networking' is not set on either machine. But when ODBCAdmin (3.51) is used to create a DSN on the winxp machine for connecting to the mysql server on the w2k machine, clicking 'Test Data Source' yields this error: Host 'XPBOX' is not allowed to connect to this MySQL server'. (Oddly, the mysql server capitalizes the name of the winxp box. Why does it do that?) Issuing the command telnet w2kbox 3306 on the xpbox also produced this same error message. This ought to be simple. What's the secret? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to mysql server from another windows box
Note that if you set the password by directly editing (UPDATE) the mysql.user table like this, you will have to run FLUSH PRIVILEGES; Thank you. That solved the problem connecting from the mysql client. It changed the problem connecting via the 'Test' button from the ODBC Administrator, to an 'authentication protocol' message. Apparently Connector/ODBC 3.51 uses the old protocol. Gerald Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to mysql server from another windows box
it sounds like you need to look at, and potentially cleanup (or make your mysql access entries match) what's in the inverse-map record for the IPnumbers of your machines. when you connect mysql takes the IPnumber on the inbound connection, looks it up in DNS and then checks the result against the access table(s). so, if you've put xpbox.domain.xxx in the access table but the lookup on the inbound IPnumber only returns xpbox (which is technically an invalid entry anyway) things will fail. so, do lookups on the IPnumbers of the client machines and see what's returned. that's what you want in the mysql access tables. Flushing privileges permitted a connection from the mysql client. To get the ODBC Admin 'Test Data Source' button to work, I had to create a user table entry with a password hashed by Old_Password(). Is ipconfig /all what you mean by lookup on the IPnumbers? That gives the name of the client box without domain.xxx. In any event, the 'technically incorrect' name did work, and adding the domain.xxx part of the name to the user record did not work. Gerald Williams Host=name of the machine being used as a client 'skip-networking' is not set on either machine. But when ODBCAdmin (3.51) is used to create a DSN on the winxp machine for connecting to the mysql server on the w2k machine, clicking 'Test Data Source' yields this error: Host 'XPBOX' is not allowed to connect to this MySQL server'. (Oddly, the mysql server capitalizes the name of the winxp box. Why does it do that?) Issuing the command telnet w2kbox 3306 on the xpbox also produced this same error message. This ought to be simple. What's the secret? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- End Original Message -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
best practices for finding duplicate chunks
I just revived a database that was in a version 3.23 server and moved it to a 4.1 There are big fields of TEXT based data. They have a way of compressing the amount of TEXT data by identifying common subchunks and putting them in a subchunk table and replacing them with a marker inside the main text that will pull in that subchunk whenever the parent chunk is requested. This subchunking seems to have been done kind of ad hoc, because I've noticed the database still has quite a bit of duplicated chunks from one record to another. The client does not want to buy another drive to store data (even tho he really should for other reasons anyway but who cares what I think) , so he wants it compressed, and oh well I look on it as an opportunity for some housecleaning. Now that we have 4.1 what is the best practice for automated looking for common subchunks, factoring them out, and then replacing the original parent text with itself with the chunk cut out and a marker inserted. The hard part is finding them, ovbiously. The rest is easy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best practices for finding duplicate chunks
Thanks for your answer. It would certainly work provided having enough disk space to do that. I thought something like that but was hoping I can leverage fulltext and just record the fulltext result between a each record and each other record. Then I can group all records that highly correlate and maybe do a much smaller scale version of the brute force indexing thing that you are proposing, i.e. only do it on a group of records that we already know have a high correlation, ie a high probability of sharing a chunk in common Then when done I can throw away that data and do another group. What do you think? Processing cycles I have but easy disk space I don't. Alexey Polyakov wrote: There's no easy way to do it I think. But if spending a few hours (days?) programming is ok with you I'd suggest something like this: 1) create a table (let's call it hashes) with three columns: hash, doc_id, pos_id (doc_id is an identifier for records from table with big text chunks) 2) retrieve a record from big table. Calculate hash value for concatenated first 20 words from text. Insert this value into hash/doc_id table, and 1 as value of pos_id. Calculate hash for concatenated 20 words starting from 2-nd word of this text, and also insert it into hash/doc_id table (2 as value of pos_id). Repeat until you reach the end of this text. 3) Repeat 2) for all records of big table 4) Now you have all data needed for identifying those duplicate chunks. select count(doc_id) as c from hashes group by hash where c1; will return all hashes for 20-word chunks that are found in 2 or more documents select doc_id from hashes where hash=some_value; will return documents that contain this chunk. select h1.pos_id, h2.pos_id from hashes h1, hashes h2 where h1.doc_id=doc1 and h2.doc_id=doc2 and h1.hash=h2.hash order by h1.pos_id; will return word positions for duplicate text in two documents. For example last query returns: 156 587 157 588 ... 193 624 It means that you can take words 156-213 from doc1, insert it into subchunks table, and replace words 156-212 at doc1 and words 587-643 at doc2 with a marker. Yeah it looks ugly, and will take a lot of space for temporary data. But in the end you'll have all 20+ words duplicate chunks properly identified. On 8/14/05, Gerald Taylor [EMAIL PROTECTED] wrote: I just revived a database that was in a version 3.23 server and moved it to a 4.1 There are big fields of TEXT based data. They have a way of compressing the amount of TEXT data by identifying common subchunks and putting them in a subchunk table and replacing them with a marker inside the main text that will pull in that subchunk whenever the parent chunk is requested. This subchunking seems to have been done kind of ad hoc, because I've noticed the database still has quite a bit of duplicated chunks from one record to another. The client does not want to buy another drive to store data (even tho he really should for other reasons anyway but who cares what I think) , so he wants it compressed, and oh well I look on it as an opportunity for some housecleaning. Now that we have 4.1 what is the best practice for automated looking for common subchunks, factoring them out, and then replacing the original parent text with itself with the chunk cut out and a marker inserted. The hard part is finding them, ovbiously. The rest is easy. -- 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]
innodb hot backup (ibbackup) error
Hi all, I'm getting the following error when attempting to run a hot backup of a 4.0.18 MySQL database using ibbackup: ibbackup: We wait 10 seconds before starting copying the data files... 050330 14:26:33 ibbackup: Copying /mysqldata/mysqld1/ibdata1 ibbackup: Error: log scan was only able to reach to 0 561225562, ibbackup: but a copied database page was modified at 0 575415967 Any idea what this means? Thanks for any help.
insert was working
Hi!
I was able to insert data with the following:
sub insert_sql {
my ( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax, $total,
$DEBUG ) = @_;
print [EMAIL PROTECTED] if $DEBUG;
( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax, $total ) =
clean_sql( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total, $DEBUG );
print \ninsert_sql -
*$group_name*$me*$daily*$item*$unit*$qty*$amount*$tax*$total*\n if $DEBUG;
my $dbh = DBI-connect( DBI:mysql:club,gjwpp88,password ) || die
\n$DBI::errstr\n;
my $sql = insert into wolfies( group_name, me, daily, item, unit, qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?, ?,
?, ? );
my $sth = $dbh-prepare( $sql ) || die $dbh-errstr;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) || die Cannot execute insert_sql!!\n$DBI::errstr;
}1;
I am no longer able to insert any data at all. No errors, nothing. It acts
like it works. But I am able to select existing data with the following:
sub select_sql {
my ( $me, $daily, $DEBUG ) = @_;
my %data = ();
print [EMAIL PROTECTED] if $DEBUG;
$me =~ s/\s+//;
$daily =~ s/\s+//;
my $dbh = DBI-connect( DBI:mysql:club,gjwpp88,password ) or die
\n$DBI::errstr\n;
my $sql = select group_name, me, daily, item, unit, qty, amount, tax,
total
from wolfies
where me= '$me'
anddaily = '$daily'
;
print sql *$sql*\n if $DEBUG;
my $sth = $dbh-prepare( $sql ) || die $DBI::errstr\n;
my $rv = $sth-execute() || die Cannot execute
select_sql!!br$DBI::errstr;
while( my ( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total ) = $sth-fetchrow() ) {
print \nselect_sql $group_name, $me, $daily, $item, $unit, $qty,
$amount, $tax, $total*\n if $DEBUG;
push @{ $data{ $group_name }}, $item, $unit, $qty, $amount, $tax,
$total;
}
return \%data;
}1;
I have done the following setup options:
GRANT ALL PRIVILEGES ON club.* TO 'gjwpp88'@'localhost';
SET PASSWORD FOR 'gjwpp88'@'localhost' = PASSWORD('password');
UPDATE mysql.user SET Password = OLD_PASSWORD('password')
WHERE Host = 'localhost' AND User = 'gjwpp88';
SET PASSWORD FOR 'gjwpp88'@'localhost' = OLD_PASSWORD('password');
SELECT 'localhost', 'gjwpp88', Password FROM mysql.user
WHERE LENGTH('password') 16;
FLUSH PRIVILEGES;
Any ideas?
Thanks,
Jerry
RE: insert was working
Richard,
Explain auto_increment field please.
Thanks,
Jerry
-Original Message-
From: Richard Whitney [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 17, 2005 10:31 AM
To: Gerald Preston
Cc: mysql@lists.mysql.com
Subject: Re: insert was working
Gerald!
Do you have an auto_increment field?
If so, what's it set as?
Richard
Quoting Gerald Preston [EMAIL PROTECTED]:
Hi!
I was able to insert data with the following:
sub insert_sql {
my ( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total,
$DEBUG ) = @_;
print [EMAIL PROTECTED] if $DEBUG;
( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax, $total )
=
clean_sql( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total, $DEBUG );
print \ninsert_sql -
*$group_name*$me*$daily*$item*$unit*$qty*$amount*$tax*$total*\n if
$DEBUG;
my $dbh = DBI-connect( DBI:mysql:club,gjwpp88,password ) || die
\n$DBI::errstr\n;
my $sql = insert into wolfies( group_name, me, daily, item, unit, qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?,
?,
?, ? );
my $sth = $dbh-prepare( $sql ) || die $dbh-errstr;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) || die Cannot execute insert_sql!!\n$DBI::errstr;
}1;
I am no longer able to insert any data at all. No errors, nothing. It
acts
like it works. But I am able to select existing data with the following:
sub select_sql {
my ( $me, $daily, $DEBUG ) = @_;
my %data = ();
print [EMAIL PROTECTED] if $DEBUG;
$me =~ s/\s+//;
$daily =~ s/\s+//;
my $dbh = DBI-connect( DBI:mysql:club,gjwpp88,password ) or die
\n$DBI::errstr\n;
my $sql = select group_name, me, daily, item, unit, qty, amount, tax,
total
from wolfies
where me= '$me'
anddaily = '$daily'
;
print sql *$sql*\n if $DEBUG;
my $sth = $dbh-prepare( $sql ) || die $DBI::errstr\n;
my $rv = $sth-execute() || die Cannot execute
select_sql!!br$DBI::errstr;
while( my ( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax,
$total ) = $sth-fetchrow() ) {
print \nselect_sql $group_name, $me, $daily, $item, $unit, $qty,
$amount, $tax, $total*\n if $DEBUG;
push @{ $data{ $group_name }}, $item, $unit, $qty, $amount, $tax,
$total;
}
return \%data;
}1;
I have done the following setup options:
GRANT ALL PRIVILEGES ON club.* TO 'gjwpp88'@'localhost';
SET PASSWORD FOR 'gjwpp88'@'localhost' = PASSWORD('password');
UPDATE mysql.user SET Password = OLD_PASSWORD('password')
WHERE Host = 'localhost' AND User = 'gjwpp88';
SET PASSWORD FOR 'gjwpp88'@'localhost' = OLD_PASSWORD('password');
SELECT 'localhost', 'gjwpp88', Password FROM mysql.user
WHERE LENGTH('password') 16;
FLUSH PRIVILEGES;
Any ideas?
Thanks,
Jerry
R. Whitney
Transcend Development
Producing the next phase of your internet presence
http://xend.net
Premium Quality Web Hosting
http://xendhosting.com
rw AT xend.net
Net Binder http://netbinder.net
310-943-6498
602-288-5340
--
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]
migrate from one machine to another
Hi there. I'm an Oracle DBA who know very little about MySQL. I need to migrate MySQL from one box to another. Can anybody point me to some documentation? The platform is Solaris 8, MySQL 4.0. Thanks for any pointers, it's greatly appreciated!
RE: insert data
Michael, John, ALL;
Thank you! Thanks you! My errors of local_host and if $dbh-err fix it.
I am able to insert and select data.
Thank you ALL,
Jerry
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Friday, March 04, 2005 5:41 PM
To: John Trammell
Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]; Gerald Preston
Subject: Re: insert data
Right. First, I think the logic is flawed. We should successfully
prepare() or die. Period. If the call to prepare() failed ($sth is undef),
we should not making dying conditional on yet another value.
More to the point, this line is actually the cause of the problem. (Sorry I
didn't see it earlier.) You've run into the precedence rules:
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
is read as
(my $sth = $dbh-prepare( $sql ) or die $dbh-errstr) if $dbh-err;
That is, it is equivalent to
if ($dbh-err)
{
$sth = $dbh-prepare( $sql ) or die $dbh-errstr;
}
Since the connect succeeded, $dbh-err is undef, so we never even call
prepare! Hence, $sth is undef when we get to execute, and you get the error
message. I expect this is what Joe (John Doe) was trying to tell us
earlier.
The simplest solution would be to drop the if $dbh-err. That is, change
to
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr;
John's suggestion (below) is better still, as it adds helpful detail to the
error message when there is one (though I don't see the need to make it a
separate line of code).
Michael
John Trammell wrote:
Gerald Preston wrote:
[snip]
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
[snip]
Regardless of other problems you may be having, I think you're not
doing what you want to do here. How about instead:
my $sth = $dbh-prepare($sql);
$sth || die Error preparing sth from '$sql': , $dbh-errstr;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert data
David,
I read them and installed 4.1.10 and I am back to square one:
I used the following code:
#!/perl
use warnings;
use strict;
use DBI;
#use DBD-mysql;
my $group_name = beer;
my $me = E;
my $daily = 03032005;
my $item = Bacardi;
my $unit = 2;
my $qty= 3;
my $amount = 6;
my $tax= 0.41;
my $total = 6.41;
my $dbh=DBI-connect( 'dbi:mysql:club', 'gjwpp88', 'x' ) or die
\n$DBI::errstr\n;
my $sql = insert into wolfies( group_name, me, daily, item, unit, qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?, ?,
?, ? ) ;
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
I get DBI connect'club','gjwpp88',.. failed; Client does not support
authentication protocol requested by server
I have done the following with no errors!
GRANT ALL PRIVILEGES ON club.* TO 'gjwpp88'@'local_host';
SET PASSWORD FOR 'gjwpp88'@'local_host' = PASSWORD('x');
UPDATE mysql.user SET Password = OLD_PASSWORD('x')
WHERE Host = 'local_host' AND User = 'gjwpp88';
SET PASSWORD FOR 'gjwpp88'@'local_host' = OLD_PASSWORD('x');
SELECT 'local_host', 'gjwpp88', Password FROM mysql.user
WHERE LENGTH('x') 16;
FLUSH PRIVILEGES;
I am still getting the same error I listed above.
Jerry
-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 6:10 PM
To: Gerald Preston; mysql@lists.mysql.com
Subject: RE: insert data
Hi Gerald,
There are some good tutorials on the web for DBI access via perl to
mysql.
http://www.wbluhm.com/MySQLTut.html
http://perl.about.com/od/installandusemysql/l/aa090803b.htm
http://dev.mysql.com/doc/mysql/en/perl.html
and also
http://search.cpan.org/~timb/DBI-1.47/DBI.pm
You should be able to find several examples of exactly what you are
trying to achieve in one of these. The first one has an almost identical
query to that you are trying to achieve. If you can't select from the
table, then you are unlikely to be able to insert. I would follow the
tutorials, even if they are selects, and make sure they work and then
all you have to do is to change the SELECT to an INSERT statement and
away you go.
These have a very thorough examination of the setting up of the dsn etc.
I would also suggest
http://dev.mysql.com/doc/mysql/en/privilege-system.html
This gives a good explanation of how the GRANT/REVOKE/privileges system
works with MySQL. It is slightly different than Oracle and would be well
worth a read.
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-Original Message-
From: Gerald Preston [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 1 March 2005 10:10 AM
To: 'William R. Mussatto'; mysql@lists.mysql.com
Subject: RE: insert data
William,
I tried GRANT ALL ON *.* and got error 1064 4200: You have an
error
in your SQL syntax ??
Jerry
-Original Message-
From: William R. Mussatto [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 3:25 PM
To: mysql@lists.mysql.com
Subject: RE: insert data
Gerald Preston said:
Michael,
This is the actual code except for the :
my $dbh = DBI-connect( 'DBI:mysql:database=club', '', '', {
PrintError = 0 } ) or die $DBI::errstr;
my $sql = insert into wolfies( group_name, me, daily, item, unit,
qty,
amount, tax, total )
values( ?, ?, ?, ?,?,
?,
?,
?, ? ) ;
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty,
$amount,
$tax, $total ) or warn Cannot execute FIRST
Statement!!\n$DBI::errstr;
Question? When I created the database club, is there anything I
needed
to do concerning permissions or anything?
I am lost here. I have been writing code on a SUN Oracle systems for
over five years.
Regards,
Jerry
Did you 'grant' user access to all the tables in database club?
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 9:29 AM
To: Gerald Preston
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: insert data
From perldoc DBD::mysql
use DBI;
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
So it's not a syntax problem. Even if it were, we should detect the
error long before calling prepare or execute.
Perl is quite clearly telling you what is wrong. Originally, you got
Can't call method prepare on an undefined value.
for the line
my $sth = $dbh-prepare( $sql );
which means that $dbh is undefined at the time of the call to prepare
RE: insert data
Michael,
This is the actual code except for the :
my $dbh = DBI-connect( 'DBI:mysql:database=club', '', '', {
PrintError = 0 } ) or die $DBI::errstr;
my $sql = insert into wolfies( group_name, me, daily, item, unit, qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?, ?,
?, ? ) ;
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
Question? When I created the database club, is there anything I needed to
do concerning permissions or anything?
I am lost here. I have been writing code on a SUN Oracle systems for over
five years.
Regards,
Jerry
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 9:29 AM
To: Gerald Preston
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: insert data
From perldoc DBD::mysql
use DBI;
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
So it's not a syntax problem. Even if it were, we should detect the error
long before calling prepare or execute.
Perl is quite clearly telling you what is wrong. Originally, you got
Can't call method prepare on an undefined value.
for the line
my $sth = $dbh-prepare( $sql );
which means that $dbh is undefined at the time of the call to prepare.
Now, you are getting
Can't call method execute on an un undefined value
for the line
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST
Statement!!\n$DBI::errstr;
which means that $sth is undefined at the time of the call to execute.
Are you showing us select lines of your code, rather than the actual code?
My best guess right now is that you haven't taken into account that my is
a scoping operator in perl, and in the lines you haven't showed us, the
variables in question ($dbh or $sth) go out of scope.
Michael
John Doe wrote:
Am Sonntag, 27. Februar 2005 22.19 schrieb Gerald Preston:
Hi Gerald
The object used:
my $dbh=DBI-connect( 'DBI:mysql:database=club', 'xxx, 'x', {
PrintError = 0} ) or die $DBI::errstr;
I didn't see this part in your first post :-)
Hmm... I've never seen a '=' in the first argument passed to
DBI-connect...
Here's an functional example I'm using:
my $db ='database';
my $host ='hostname';
my $port ='1234';
$dbh=DBI-connect(DBI:mysql:$db:$host:$port,
'a_username',
'a_password',
{RaiseError=1,
AutoCommit=1})
or die $0: $DBI::errstr; }
So, try using club instead of database=club, and a hostname too.
greetings joe
[nothing new below]
-Original Message-
From: John Doe [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 27, 2005 6:37 AM
To: mysql@lists.mysql.com
Subject: Re: insert data
Hi Gerald
I am trying to insert data for the first time using MySQL. In Oracle I
used the following:
# my $sql = insert into bar( group_name, me, daily, item, unit, qty,
amount, tax, total )
# values( ?, ?, ?, ?,?,?, ?,
?, ? ) ;
my $sth = $dbh-prepare( $sql );
die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) || die Cannot execute FIRST Statement!!\n$DBI::errstr;
I keep getting Can't call method prepare on an un undefined value.
All the name listed are correct by looking at MySQLAdmin1.3\4.
Apart from David Logan's answer:
You have to create the $dbh object first (man DBI); the undefined value
in
the error message refers to that.
HTH
joe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: insert data
William,
I tried GRANT ALL ON *.* and got error 1064 4200: You have an error
in your SQL syntax ??
Jerry
-Original Message-
From: William R. Mussatto [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 3:25 PM
To: mysql@lists.mysql.com
Subject: RE: insert data
Gerald Preston said:
Michael,
This is the actual code except for the :
my $dbh = DBI-connect( 'DBI:mysql:database=club', '', '', {
PrintError = 0 } ) or die $DBI::errstr;
my $sql = insert into wolfies( group_name, me, daily, item, unit,
qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?,
?,
?, ? ) ;
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
Question? When I created the database club, is there anything I needed
to do concerning permissions or anything?
I am lost here. I have been writing code on a SUN Oracle systems for
over five years.
Regards,
Jerry
Did you 'grant' user access to all the tables in database club?
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 9:29 AM
To: Gerald Preston
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: insert data
From perldoc DBD::mysql
use DBI;
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
So it's not a syntax problem. Even if it were, we should detect the
error long before calling prepare or execute.
Perl is quite clearly telling you what is wrong. Originally, you got
Can't call method prepare on an undefined value.
for the line
my $sth = $dbh-prepare( $sql );
which means that $dbh is undefined at the time of the call to prepare.
Now, you are getting
Can't call method execute on an un undefined value
for the line
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST
Statement!!\n$DBI::errstr;
which means that $sth is undefined at the time of the call to execute.
Are you showing us select lines of your code, rather than the actual
code? My best guess right now is that you haven't taken into account
that my is a scoping operator in perl, and in the lines you haven't
showed us, the variables in question ($dbh or $sth) go out of scope.
Michael
John Doe wrote:
Am Sonntag, 27. Februar 2005 22.19 schrieb Gerald Preston:
Hi Gerald
The object used:
my $dbh=DBI-connect( 'DBI:mysql:database=club', 'xxx, 'x', {
PrintError = 0} ) or die $DBI::errstr;
I didn't see this part in your first post :-)
Hmm... I've never seen a '=' in the first argument passed to
DBI-connect...
Here's an functional example I'm using:
my $db ='database';
my $host ='hostname';
my $port ='1234';
$dbh=DBI-connect(DBI:mysql:$db:$host:$port,
'a_username',
'a_password',
{RaiseError=1,
AutoCommit=1})
or die $0: $DBI::errstr; }
So, try using club instead of database=club, and a hostname too.
greetings joe
[nothing new below]
-Original Message-
From: John Doe [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 27, 2005 6:37 AM
To: mysql@lists.mysql.com
Subject: Re: insert data
Hi Gerald
I am trying to insert data for the first time using MySQL. In Oracle
I used the following:
# my $sql = insert into bar( group_name, me, daily, item, unit,
qty, amount, tax, total )
# values( ?, ?, ?, ?,?,?,
?, ?, ? ) ;
my $sth = $dbh-prepare( $sql );
die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty,
$amount,
$tax, $total ) || die Cannot execute FIRST
Statement!!\n$DBI::errstr;
I keep getting Can't call method prepare on an un undefined value.
All the name listed are correct by looking at MySQLAdmin1.3\4.
Apart from David Logan's answer:
You have to create the $dbh object first (man DBI); the undefined
value in
the error message refers to that.
HTH
joe
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
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]
insert data
Hi! I am trying to insert data for the first time using MySQL. In Oracle I used the following: # my $sql = insert into bar( group_name, me, daily, item, unit, qty, amount, tax, total ) # values( ?, ?, ?, ?,?,?, ?, ?, ? ) ; my $sth = $dbh-prepare( $sql ); die $dbh-errstr if $dbh-err; $sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax, $total ) || die Cannot execute FIRST Statement!!\n$DBI::errstr; I keep getting Can't call method prepare on an un undefined value. All the name listed are correct by looking at MySQLAdmin1.3\4. Any ideas? Thanks, Jerry
RE: insert data
David, The actual code is uncommented: my $sql = insert into bar( group_name, me, daily, item, unit, qty, amount, tax, total ) values( ?, ?, ?, ?,?,?, ?, ?, ? ) ; Sorry, Jerry -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 5:09 AM To: Gerald Preston; mysql users Subject: RE: insert data Hi Gerald, If this is copied out of your perl code then you haven't put anything into the $sql variable. Try uncommenting the 2 lines above. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Gerald Preston [mailto:[EMAIL PROTECTED] Sent: Sunday, 27 February 2005 9:33 PM To: mysql users Subject: insert data Hi! I am trying to insert data for the first time using MySQL. In Oracle I used the following: # my $sql = insert into bar( group_name, me, daily, item, unit, qty, amount, tax, total ) # values( ?, ?, ?, ?,?,?, ?, ?, ? ) ; my $sth = $dbh-prepare( $sql ); die $dbh-errstr if $dbh-err; $sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount, $tax, $total ) || die Cannot execute FIRST Statement!!\n$DBI::errstr; I keep getting Can't call method prepare on an un undefined value. All the name listed are correct by looking at MySQLAdmin1.3\4. Any ideas? Thanks, Jerry -- 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]
