Re: Issue with timestamp columns while working with MySQL load data in file
On 2015/04/12 08:52, Pothanaboyina Trimurthy wrote: The problem is , as mentioned the load data is taking around 2 hours, I have 2 timestamp columns for one column I am passing the input through load data, and for the column DB_MODIFIED_DATETIME no input is provided, At the end of the load data I could see only one timestamp value for both the columns, though the load data takes 2 hours to load the data. Can any one explain how exactly the load data infile works, and why only a single timestamp is inserting for all 1 million records though the load data taking around 2 hours. Look up function SYSDATE: all other times timestamps are kept in step, beginning at the time when the transaction begins. Your described effect is intended. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Issue with timestamp columns while working with MySQL load data in file
Hi All, I am facing an issue with timestamp columns while working with MySQL load data in file, I am loading around a million records which is taking around 2 hours to complete the load data. Before get into more details about the problem, first let me share the table structure. CREATE TABLE `test_load_data` ( `id1` int(11) DEFAULT NULL, `col10` varchar(255) DEFAULT NULL, `DB_CREATED_DATETIME` datetime DEFAULT NULL, `DB_MODIFIED_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; LOAD DATA LOCAL INFILE '/x.dat' INTO TABLE test_load_data FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (id1,col10,@DB_CREATED_DATETIME) SET DB_CREATED_DATETIME = NOW() ; mysql select DB_CREATED_DATETIME,DB_MODIFIED_DATETIME,count(1) from test_load_data group by DB_CREATED_DATETIME,DB_MODIFIED_DATETIME; +-+--+--+ | DB_CREATED_DATETIME | DB_MODIFIED_DATETIME | count(1) | +-+--+--+ | 2015-04-07 10:08:09 | 2015-04-07 10:08:09 | 100 | +-+--+--+ 1 row in set (2.14 sec) The problem is , as mentioned the load data is taking around 2 hours, I have 2 timestamp columns for one column I am passing the input through load data, and for the column DB_MODIFIED_DATETIME no input is provided, At the end of the load data I could see only one timestamp value for both the columns, though the load data takes 2 hours to load the data. Can any one explain how exactly the load data infile works, and why only a single timestamp is inserting for all 1 million records though the load data taking around 2 hours. Thank you in advance. -- Thanks, Trimurthy P Mobile : +91 97397 64298 http://mysqlinternals.blogspot.in/ https://www.linkedin.com/pub/trimurthy-pothanaboyina/5a/9a9/96b
DISTINCT not working inside a CASE statement.
Here is my simple table MariaDB [tutorial] select * from prices; ++--+--+ | id | name | cost | ++--+--+ | 1 | A| 1200 | | 2 | A| 2500 | | 3 | A| 3000 | | 4 | B| 5000 | | 5 | B| 7000 | | 6 | C| NULL | ++--+--+ 6 rows in set (0.00 sec) I want it to give me data as name cost A 6700 B 12000 C NULL But my query is not working - MariaDB [tutorial] select name, CASE WHEN ISNULL(DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum - from prices group by name; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum from prices group by name' at line 1 -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: DISTINCT not working inside a CASE statement.
Am 19.07.2014 13:45, schrieb Arup Rakshit: Here is my simple table MariaDB [tutorial] select * from prices; ++--+--+ | id | name | cost | ++--+--+ | 1 | A| 1200 | | 2 | A| 2500 | | 3 | A| 3000 | | 4 | B| 5000 | | 5 | B| 7000 | | 6 | C| NULL | ++--+--+ 6 rows in set (0.00 sec) I want it to give me data as name cost A 6700 B 12000 C NULL But my query is not working - MariaDB [tutorial] select name, CASE WHEN ISNULL(DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum - from prices group by name; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum from prices group by name' at line 1 how do you imagine that to work? what is the distinct in that context supposed to do? signature.asc Description: OpenPGP digital signature
Re: DISTINCT not working inside a CASE statement.
On Saturday, July 19, 2014 02:56:24 PM Reindl Harald wrote: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum from prices group by name' at line 1 how do you imagine that to work? what is the distinct in that context supposed to do? Ohh. God. So simple it is - MariaDB [tutorial] select name, sum(cost) AS cost_sum from prices group by name; +--+--+ | name | cost_sum | +--+--+ | A| 6700 | | B|12000 | | C| NULL | +--+--+ 3 rows in set (0.01 sec) Thanks. -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
User-defined variables not working ONLY on first query in chained replication
I've noticed a weird issue in our chained replication environment where when setting user-defined variables, the first time the variable is used in a session the value is NULL, but all uses thereafter work correctly. The environment is such: we have a master (master1), which has a slave which is also a master (master3), which itself has slaves (master3-slave1), i.e.: master1 - master3 - master3-slave1 I can replicate my issue with a very simple setup. I simply create a test table with one TEXT column, and I set a user-defined variable: CREATE TABLE test_table (id INT(10) PRIMARY KEY AUTO_INCREMENT, result TEXT) ENGINE=InnoDB; SET @mynewvariable = testvalue And then insert the variable into the test table: INSERT INTO test_table VALUES (NULL, @mynewvariable); The first time I run this insert, the value is correctly inserted in to master1 and its slave, master3 (as you'd expect). However, a NULL value is inserted into master3-slave1. However, if I run the INSERT a second time (just the insert, no re-declaration of the user-defined variable), the value is correctly inserted in to all three servers, so that the contents of test_table on the three servers looks as follows: master1 master3 master3-slave1 - - -- testvalue testvalue NULL testvalue testvalue testvalue I don't believe this is related to replication delay, because even if I leave a while between setting the variable and running the first INSERT, the result is always the same. The problem is agnostic of table format or how complex the table is, we can reproduce it exactly like this all of the time. Is this a known issue in MySQL with chained replication like this, or have I discovered a bug? Server version information: master1: Percona Server 5.5.28-29.1 master3: Percona Server 5.5.28-29.3 master3-slave1: Percona Server 5.5.20-55 -- Global Personals is a limited company registered in England and Wales. Registered number: 04880697. Registered office: Minton Place, Victoria Street, Windsor, Berkshire, SL4 1EG, United Kingdom.
Re: User-defined variables not working ONLY on first query in chained replication
Hi Matthew, On 16/07/2013 21:21, Matthew Ward wrote: I've noticed a weird issue in our chained replication environment where when setting user-defined variables, the first time the variable is used in a session the value is NULL, but all uses thereafter work correctly. snip The first time I run this insert, the value is correctly inserted in to master1 and its slave, master3 (as you'd expect). However, a NULL value is inserted into master3-slave1. However, if I run the INSERT a second time (just the insert, no re-declaration of the user-defined variable), the value is correctly inserted in to all three servers, so that the contents of test_table on the three servers looks as follows: snip Is this a known issue in MySQL with chained replication like this, or have I discovered a bug? Do you happen to have any table level replication filters? If so it sounds like you are affected by a bug that was fixed in 5.5.32 (https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-32.html): *Replication:*Running the server with both the|--log-slave-updates| http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_log-slave-updatesand|--replicate-wild-ignore-table| http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-wild-ignore-tableoptions in some cases caused updates to user variables not to be logged. (Note: at least replicate-ignore-table is also triggering that bug). Best regards, Jesper Krogh MySQL Support
Re: logging in using host alias not working
Found the problem. There was cached DNS info on the host. Restarted nscd and then it worked. On Thu, Apr 18, 2013 at 10:59 AM, Larry Martell larry.mart...@gmail.com wrote: We use host aliases to connect to MySQL all the time, never had an issue before. Today we added a new alias, and we cannot connect to the server using that one alias but only when we are on the local machine. Here is the NIS entry for this host: # ypmatch -k ubshp2 hosts ubshp2 192.132.2.143ubshp2.predict.com ubshp2 ti-us-dev intradb-au-qa intradb-us-alpha intradb-us-dev ti-test-dr intradb-test-dr I can connect from all these aliases, except intradb-test-dr when I am on ubshp2, e.g.: From ubshp2 using the intradb-us-dev alias, works: ti-test-dr@ubshp2: mysql -h intradb-us-dev -u tradeinfra -p intradb_test_dr Welcome to the MySQL monitor. Commands end with ; or \g. From ubshp2 using the intradb-au-qa alias, works: ti-test-dr@ubshp2: mysql -h intradb-au-qa -u tradeinfra -p intradb_test_dr Welcome to the MySQL monitor. Commands end with ; or \g. From ubshp2 using the intradb-test-dr alias, does not work: ti-test-dr@ubshp2: mysql -h intradb-test-dr -u tradeinfra -p intradb_test_dr ERROR 1045 (28000): Access denied for user 'tradeinfra'@'ubshp2.predict.com' (using password: YES) But from a different host using the intradb-test-dr alias, works: ti-test-dr@hades: mysql -h intradb-test-dr -u tradeinfra -p intradb_test_dr Welcome to the MySQL monitor. Commands end with ; or \g. Anyone have any ideas as to why this one is not working? I've been messing with this for 2 days. Nothing in the error log. I've dropped and re-added the user, I've bounced the server, I've removed and re-added the alias. I've googled and googled and found nothing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
logging in using host alias not working
We use host aliases to connect to MySQL all the time, never had an issue before. Today we added a new alias, and we cannot connect to the server using that one alias but only when we are on the local machine. Here is the NIS entry for this host: # ypmatch -k ubshp2 hosts ubshp2 192.132.2.143ubshp2.predict.com ubshp2 ti-us-dev intradb-au-qa intradb-us-alpha intradb-us-dev ti-test-dr intradb-test-dr I can connect from all these aliases, except intradb-test-dr when I am on ubshp2, e.g.: From ubshp2 using the intradb-us-dev alias, works: ti-test-dr@ubshp2: mysql -h intradb-us-dev -u tradeinfra -p intradb_test_dr Welcome to the MySQL monitor. Commands end with ; or \g. From ubshp2 using the intradb-au-qa alias, works: ti-test-dr@ubshp2: mysql -h intradb-au-qa -u tradeinfra -p intradb_test_dr Welcome to the MySQL monitor. Commands end with ; or \g. From ubshp2 using the intradb-test-dr alias, does not work: ti-test-dr@ubshp2: mysql -h intradb-test-dr -u tradeinfra -p intradb_test_dr ERROR 1045 (28000): Access denied for user 'tradeinfra'@'ubshp2.predict.com' (using password: YES) But from a different host using the intradb-test-dr alias, works: ti-test-dr@hades: mysql -h intradb-test-dr -u tradeinfra -p intradb_test_dr Welcome to the MySQL monitor. Commands end with ; or \g. Anyone have any ideas as to why this one is not working? I've been messing with this for 2 days. Nothing in the error log. I've dropped and re-added the user, I've bounced the server, I've removed and re-added the alias. I've googled and googled and found nothing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MUltiple value in single insert is not working in mysql procedure
2012/12/03 19:10 +0530, amit Problem mysql call mobile_series1('(99889988),(12334565)'); You are expecting MySQL to turn one string operand into twain number operands. That does not happen, unless you use PREPARE, which, I suspect, is not part of your homework. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MUltiple value in single insert is not working in mysql procedure
On 2012-12-03 7:40 AM, amit wrote: Hi Team, I am not able to use multi value insert via argument in mysql stored procedure, Please help where am I wrong. Thanks in Advance ! mysql insert into input_data1 values(),(),(),(); Query OK, 4 rows affected (0.00 sec) *Problem* mysql call mobile_series1('(99889988),(12334565)'); +---+ | mobile| +---+ | (99889988),(12334565) | +---+ 1 row in set (0.01 sec) instead of 2 rows in table its only one row. *Function Code:* |DELIMITER| DROP PROCEDURE IF EXISTS mobile_series1; CREATE PROCEDURE mobile_series1(IN str text) LANGUAGE SQL READS SQL DATA BEGIN DROP TABLE IF EXISTS input_data1; CREATE TEMPORARYTABLE input_data1(mobile varchar(1000)) engine=memory; INSERT INTO input_data1(mobile) VALUES (str); SELECT * FROM input_data1; END | DELIMITER;| |Dynamic SQL requires PREPARE, see the manual page for that cmd. PB | | | -- Best Regards Amit Jain www.netcore.co.in Ext - 4109 #9833777592 Banner plane netCORE wins GOLD in 1^st Smarties India 2012 Awards Organized by Mobile Marketing Association (MMA) Cross Media Integration for 'Colgate Active Salt Taste Challenge 2012' Campaign *Campaigns nominated for other categories * *Brand Awareness* Active Wheel Mobile Marketing Campaign *Innovation* Active Wheel Mobile Marketing Campaign
Re: Autostart not working for me in 5.5.8 version
Hi Noel, all! Noel Butler wrote: On Thu, 2010-12-16 at 10:08 -0300, Alejandro Bednarik wrote: You are right. In previous version init script have a default value, now is empty. mysql 5.1..53 - basedir=/usr/local/mysql mysql-5.5.8 - basedir=. /me ponders at who the IDIOT is that decided that cmake is the way of mysql now. I mean WTF... It was the ones who realized that two different build mechanisms (autotools for Unix, cmake for Windows) will always cause divergence, when one is maintained with a change and the other isn't. Being database people, we know the importance of consistency ;) [[...]] /me sticks with 5.1 Do as you like, but realize that 5.1 won't see major development in the future and will be put on extended support only in due time. So if anybody encounters differences between 5.1 and 5.5 in such settings which aren't documented (= by intention, or unavoidable), please file bugs to get them fixed. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Autostart not working for me in 5.5.8 version
Hi Joerg, On Mon, 2010-12-20 at 18:59 +0100, Joerg Bruehe wrote: Hi Noel, all! Noel Butler wrote: On Thu, 2010-12-16 at 10:08 -0300, Alejandro Bednarik wrote: You are right. In previous version init script have a default value, now is empty. mysql 5.1..53 - basedir=/usr/local/mysql mysql-5.5.8 - basedir=. /me ponders at who the IDIOT is that decided that cmake is the way of mysql now. I mean WTF... It was the ones who realized that two different build mechanisms (autotools for Unix, cmake for Windows) will always cause divergence, when one is maintained with a change and the other isn't. Being database people, we know the importance of consistency ;) I can tell its done by windowsy people, it has pretty colours :) seriously though , it's almost as bad as trying to figure out what to give postifx hehe And in the real world, many sysadmins have to build the database servers for the database admins, I've made my opinion known about cmake so I'll leave my cursing at that. /me sticks with 5.1 Do as you like, but realize that 5.1 won't see major development in the future and will be put on extended support only in due time. For what we use it for, it's likely fine, (user auth/radius/web/mail etc etc etc) nothing complex. So if anybody encounters differences between 5.1 and 5.5 in such settings which aren't documented (= by intention, or unavoidable), please file bugs to get them fixed. Well, the documentation could be a little more in depth, if you remove method A, and if method B is completely compatible, then there must be detailed information, it is not very good for time management to spend hours looking over the website, yes forge.mysql.com has a handy reference, but the bit about replacing, for instance plugins=max, it is not clear what we need to include, we know what is default, and some examples of to add given the example given, but no reference to the max server, without time consuming research to see what plugins=max includes, compare, then find equivalent DINSTALL_blah=1's to add, I think it is a backwards step, and you're likely to see more cussing as more go to upgrade. Nice thing though, my original my.cnf didnt cause it to bail out upon restart, however I only installed it on one very light use server. want to lay with it a bit before it goes on anything too serious. Cheers attachment: face-smile.png signature.asc Description: This is a digitally signed message part
Re: Autostart not working for me in 5.5.8 version
You are right. In previous version init script have a default value, now is empty. mysql 5.1..53 - basedir=/usr/local/mysql mysql-5.5.8 - basedir=. Cheers. On Thu, Dec 16, 2010 at 12:42 AM, Yangontha Maung yangontha5...@yahoo.comwrote: Hi, I will answer my own question. I have to add a line in (/etc/my.cnf) as follows:- basedir=/usr/local/mysql I have installed mysql in /usr/local/mysql. But the same was done in previous versions and I do not need to add this. Anyway, now that it is working. rgds, --- On Thu, 12/16/10, Yangontha Maung yangontha5...@yahoo.com wrote: From: Yangontha Maung yangontha5...@yahoo.com Subject: Autostart not working for me in 5.5.8 version To: mysql@lists.mysql.com Date: Thursday, December 16, 2010, 11:14 AM Hi, Please help to set up the autostart of mysql when the Linux server starts. It is not working for me after installing to version 5.5.8. I have been using different versions of mysql for quite some time with different servers, Fedora versions. All these are working for autostart. But for this 5.5.8 I tried on two servers and both are not working for autostart. Please help. The following are my details:=- Installed on Fedora 12 Linux Previously mysql 5.1.48 is working and autorestart is also working. Now installed 5.5.8 Using manual starting is working /usr/local/mysql/bin/mysqld_safe But the auto start is not working. I have done this $ cp /usr/local/mysql/bin/support-files/mysql.server /etc/init.d/mysql $ chmod +x /etc/init.d/mysql $ ln –s /etc/init.d/mysql /etc/rc3.d/S99mysql $ ln –s /etc/init.d/mysql /etc/rc5.d/S99mysql $ ln –s /etc/init.d/mysql /etc/rc0.d/K01mysql thanks and rgds, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yangontha5...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=alejand...@olx.com -- Infrastructure Team OLX Inc. Buenos Aires - Argentina Phone : 54.11.4775.6696 Mobile : 54.911.50436059 Email: alejand...@olx.com
Autostart not working for me in 5.5.8 version
Hi, Please help to set up the autostart of mysql when the Linux server starts. It is not working for me after installing to version 5.5.8. I have been using different versions of mysql for quite some time with different servers, Fedora versions. All these are working for autostart. But for this 5.5.8 I tried on two servers and both are not working for autostart. Please help. The following are my details:=- Installed on Fedora 12 Linux Previously mysql 5.1.48 is working and autorestart is also working. Now installed 5.5.8 Using manual starting is working /usr/local/mysql/bin/mysqld_safe But the auto start is not working. I have done this $ cp /usr/local/mysql/bin/support-files/mysql.server /etc/init.d/mysql $ chmod +x /etc/init.d/mysql $ ln –s /etc/init.d/mysql /etc/rc3.d/S99mysql $ ln –s /etc/init.d/mysql /etc/rc5.d/S99mysql $ ln –s /etc/init.d/mysql /etc/rc0.d/K01mysql thanks and rgds, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Autostart not working for me in 5.5.8 version
Hi, I will answer my own question. I have to add a line in (/etc/my.cnf) as follows:- basedir=/usr/local/mysql I have installed mysql in /usr/local/mysql. But the same was done in previous versions and I do not need to add this. Anyway, now that it is working. rgds, --- On Thu, 12/16/10, Yangontha Maung yangontha5...@yahoo.com wrote: From: Yangontha Maung yangontha5...@yahoo.com Subject: Autostart not working for me in 5.5.8 version To: mysql@lists.mysql.com Date: Thursday, December 16, 2010, 11:14 AM Hi, Please help to set up the autostart of mysql when the Linux server starts. It is not working for me after installing to version 5.5.8. I have been using different versions of mysql for quite some time with different servers, Fedora versions. All these are working for autostart. But for this 5.5.8 I tried on two servers and both are not working for autostart. Please help. The following are my details:=- Installed on Fedora 12 Linux Previously mysql 5.1.48 is working and autorestart is also working. Now installed 5.5.8 Using manual starting is working /usr/local/mysql/bin/mysqld_safe But the auto start is not working. I have done this $ cp /usr/local/mysql/bin/support-files/mysql.server /etc/init.d/mysql $ chmod +x /etc/init.d/mysql $ ln –s /etc/init.d/mysql /etc/rc3.d/S99mysql $ ln –s /etc/init.d/mysql /etc/rc5.d/S99mysql $ ln –s /etc/init.d/mysql /etc/rc0.d/K01mysql thanks and rgds, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yangontha5...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query working on 5.1.43 and not 5.0.77
Hi, My hosting company are only running MySQL version 5.0.77 and at this current time are not planning on upgrading to at least my required version of 5.1.43. My query in which I have some problems is shown below : SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT teams_id ,players_id ,rating ,@team ,IF(@team teams_id, @row := 1, @row := @row + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id ,players.players_id ,players_master.rating FROM players JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 AND players.teams_id 0 ORDER BY players.teams_id, players_master.rating DESC) s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating DESC Basically the part of the statement WHERE rank = 11 has no affect in version 5.0.77, but gives the desired results in version 5.1.43. Does anyone have any suggestions on how to overcome this problem, since at the current time I don't have the budget to move to another hosting company and this is the only query which I have a problem with. Thanks for any help. Cheers Neil
Re: Logs not working
Thansk, Will try to turn on log_warnings. -Mon From: Nitin Mehta ntn...@yahoo.com To: monloi perez mlp_fol...@yahoo.com; Ananda Kumar anan...@gmail.com Cc: mysql mailing list mysql@lists.mysql.com Sent: Sun, September 5, 2010 12:03:45 PM Subject: Re: Logs not working I believe that will not be logged unless you have enabled log_warnings. Too many connections would normally mean that the number is going beyond the configured limit and denying new sessions is an expected behavior and should not be an error really. Regards, Nitin From: monloi perez mlp_fol...@yahoo.com To: Ananda Kumar anan...@gmail.com Cc: mysql mailing list mysql@lists.mysql.com Sent: Sat, September 4, 2010 11:37:42 AM Subject: Re: Logs not working on the db server? meaning the mysqld log right? THere is really no data for teh current error. The last error they said was too much connections and shouldnt that be logged in mysqld.log? Thanks, Mon From: Ananda Kumar anan...@gmail.com To: monloi perez mlp_fol...@yahoo.com Cc: mysql mailing list mysql@lists.mysql.com Sent: Thu, September 2, 2010 6:14:26 PM Subject: Re: Logs not working Did u check the logs on the db server, to see what the issue was. regards anandkl On Thu, Sep 2, 2010 at 6:25 AM, monloi perez mlp_fol...@yahoo.com wrote: All, I'm not sure if this is the right mailing list since the specific mailing lists doesn't seem to meet my concern. For some reason mysql client stops logging to mysqd.log. We had an issue on Aug 20. But when I checked the log, the latest was on Aug 2. Any idea on how to resolve this or what caused it? Thanks, Mon
Re: Logs not working
on the db server? meaning the mysqld log right? THere is really no data for teh current error. The last error they said was too much connections and shouldnt that be logged in mysqld.log? Thanks, Mon From: Ananda Kumar anan...@gmail.com To: monloi perez mlp_fol...@yahoo.com Cc: mysql mailing list mysql@lists.mysql.com Sent: Thu, September 2, 2010 6:14:26 PM Subject: Re: Logs not working Did u check the logs on the db server, to see what the issue was. regards anandkl On Thu, Sep 2, 2010 at 6:25 AM, monloi perez mlp_fol...@yahoo.com wrote: All, I'm not sure if this is the right mailing list since the specific mailing lists doesn't seem to meet my concern. For some reason mysql client stops logging to mysqd.log. We had an issue on Aug 20. But when I checked the log, the latest was on Aug 2. Any idea on how to resolve this or what caused it? Thanks, Mon
Re: Logs not working
I believe that will not be logged unless you have enabled log_warnings. Too many connections would normally mean that the number is going beyond the configured limit and denying new sessions is an expected behavior and should not be an error really. Regards, Nitin From: monloi perez mlp_fol...@yahoo.com To: Ananda Kumar anan...@gmail.com Cc: mysql mailing list mysql@lists.mysql.com Sent: Sat, September 4, 2010 11:37:42 AM Subject: Re: Logs not working on the db server? meaning the mysqld log right? THere is really no data for teh current error. The last error they said was too much connections and shouldnt that be logged in mysqld.log? Thanks, Mon From: Ananda Kumar anan...@gmail.com To: monloi perez mlp_fol...@yahoo.com Cc: mysql mailing list mysql@lists.mysql.com Sent: Thu, September 2, 2010 6:14:26 PM Subject: Re: Logs not working Did u check the logs on the db server, to see what the issue was. regards anandkl On Thu, Sep 2, 2010 at 6:25 AM, monloi perez mlp_fol...@yahoo.com wrote: All, I'm not sure if this is the right mailing list since the specific mailing lists doesn't seem to meet my concern. For some reason mysql client stops logging to mysqd.log. We had an issue on Aug 20. But when I checked the log, the latest was on Aug 2. Any idea on how to resolve this or what caused it? Thanks, Mon
Re: Logs not working
Did u check the logs on the db server, to see what the issue was. regards anandkl On Thu, Sep 2, 2010 at 6:25 AM, monloi perez mlp_fol...@yahoo.com wrote: All, I'm not sure if this is the right mailing list since the specific mailing lists doesn't seem to meet my concern. For some reason mysql client stops logging to mysqd.log. We had an issue on Aug 20. But when I checked the log, the latest was on Aug 2. Any idea on how to resolve this or what caused it? Thanks, Mon
Symlinks not working when pointing to another table.
Hello, I've done lot of researches and tests but can't find any answer. I need to share one table between two db, those two db are in the same path ( /var/lib/mysql/db1 db2 ). I created symbolic links for db2 pointing to the table in db1. When I query the table from db2 I get this error : 'ERROR 1030 (HY000): Got error 140 from storage engine' I'm sure it was working in previous version of MySQL, but from the last version it's no longer working. This is how it looks : test-lan:/var/lib/mysql/test3# ls -alh drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 . drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 .. lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm - /var/lib/mysql/test/blbl.frm lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD - /var/lib/mysql/test/blbl.MYD lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI - /var/lib/mysql/test/blbl.MYI -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt I really need those symlinks, is there a way to make them working like before ? ( old MySQL-server is fine ) Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Symlinks not working when pointing to another table.
-Original Message- From: Julien Lory [mailto:julien.l...@gmail.com] Sent: Thursday, September 02, 2010 12:31 PM To: mysql@lists.mysql.com Subject: Symlinks not working when pointing to another table. Hello, I've done lot of researches and tests but can't find any answer. I need to share one table between two db, those two db are in the same path ( /var/lib/mysql/db1 db2 ). I created symbolic links for db2 pointing to the table in db1. When I query the table from db2 I get this error : 'ERROR 1030 (HY000): Got error 140 from storage engine' I'm sure it was working in previous version of MySQL, but from the last version it's no longer working. This is how it looks : test-lan:/var/lib/mysql/test3# ls -alh drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 . drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 .. lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm - /var/lib/mysql/test/blbl.frm lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD - /var/lib/mysql/test/blbl.MYD lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI - /var/lib/mysql/test/blbl.MYI -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt I really need those symlinks, is there a way to make them working like before ? ( old MySQL-server is fine ) Just out of curiosity. If you're using apparmor and this new directory is on another partion or mount point or anything, you might have to add a rule in apparmor's config for them... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Symlinks not working when pointing to another table.
I'm using Debian Lenny ( up to date ), so there is no app armor / selinux / grsec :/ ii mysql-client-5.0 5.0.51a-24+lenny4 MySQL database client binaries ii mysql-common 5.0.51a-24+lenny4 MySQL database common files ii mysql-server-5.0 5.0.51a-24+lenny4 MySQL database server binaries On 02/09/2010 16:18, Daevid Vincent wrote: -Original Message- From: Julien Lory [mailto:julien.l...@gmail.com] Sent: Thursday, September 02, 2010 12:31 PM To: mysql@lists.mysql.com Subject: Symlinks not working when pointing to another table. Hello, I've done lot of researches and tests but can't find any answer. I need to share one table between two db, those two db are in the same path ( /var/lib/mysql/db1 db2 ). I created symbolic links for db2 pointing to the table in db1. When I query the table from db2 I get this error : 'ERROR 1030 (HY000): Got error 140 from storage engine' I'm sure it was working in previous version of MySQL, but from the last version it's no longer working. This is how it looks : test-lan:/var/lib/mysql/test3# ls -alh drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 . drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 .. lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm - /var/lib/mysql/test/blbl.frm lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD - /var/lib/mysql/test/blbl.MYD lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI - /var/lib/mysql/test/blbl.MYI -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt I really need those symlinks, is there a way to make them working like before ? ( old MySQL-server is fine ) Just out of curiosity. If you're using apparmor and this new directory is on another partion or mount point or anything, you might have to add a rule in apparmor's config for them... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Symlinks not working when pointing to another table.
On 9/2/2010 3:31 PM, Julien Lory wrote: Hello, I've done lot of researches and tests but can't find any answer. I need to share one table between two db, those two db are in the same path ( /var/lib/mysql/db1 db2 ). I created symbolic links for db2 pointing to the table in db1. When I query the table from db2 I get this error : 'ERROR 1030 (HY000): Got error 140 from storage engine' I'm sure it was working in previous version of MySQL, but from the last version it's no longer working. This is how it looks : test-lan:/var/lib/mysql/test3# ls -alh drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 . drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 .. lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm - /var/lib/mysql/test/blbl.frm lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD - /var/lib/mysql/test/blbl.MYD lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI - /var/lib/mysql/test/blbl.MYI -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt I really need those symlinks, is there a way to make them working like before ? ( old MySQL-server is fine ) You cannot share one set of files (one table) between two different MySQL instances. That also means that you cannot share a table between the same instance by pretending it's something else by a symlink. The storage engines are designed with the premise that they have exclusive domain over the files they manage. Sharing is not part of their code. If you need to expose a table from within a different database, you must use a view. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Logs not working
All, I'm not sure if this is the right mailing list since the specific mailing lists doesn't seem to meet my concern. For some reason mysql client stops logging to mysqd.log. We had an issue on Aug 20. But when I checked the log, the latest was on Aug 2. Any idea on how to resolve this or what caused it? Thanks, Mon
Re: Event feature already working in Server 5.1.37
Hi friends, This isn't rarelly to see...its common on these days. Get this: http://dev.mysql.com/tech-resources/articles/mysql_on_windows.html WB 2010/1/27 Shawn Green shawn.gr...@sun.com Daevid Vincent wrote: -Original Message- From: Dan Nelson [mailto:dnel...@allantgroup.com] Sent: Monday, January 25, 2010 11:42 PM To: Daevid Vincent ...snipped ... People really use Windows for a mySQL server? Weird. ... Yes, they do. Not only is MySQL as cheap as the free version of MS SQL but it doesn't suffer from the hard limits the free version of MS SQL imposes and it works across all of your servers, regardless of platform. MS products are limited to Windows boxes. You cannot assemble a new Linux box and get MS-anything to run on it natively. With C, C++, .NET, JAVA, and ODBC connection options available, it's very easy to make a connection to MySQL from practically any MS development language. Some connectors will even integrate themselves into Visual Studio. Windows, as foreign as it may seem, is actually a very viable MySQL development platform. I encourage you to try it out and let us know what you think. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: Event feature already working in Server 5.1.37
On Tue, Jan 26, 2010 at 10:43 PM, Daevid Vincent dae...@daevid.com wrote: 12+ notebooks, netbooks, desktops, etc. But for any LAMP dev work, I either have a dedicated Linux box setup or a Virtual Machine. Well... It wouldn't be *Linux*-Apache-Mysql-PHP otherwise, would it :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Event feature already working in Server 5.1.37
Johan De Meersman wrote: On Tue, Jan 26, 2010 at 10:43 PM, Daevid Vincent dae...@daevid.com wrote: 12+ notebooks, netbooks, desktops, etc. But for any LAMP dev work, I either have a dedicated Linux box setup or a Virtual Machine. Well... It wouldn't be *Linux*-Apache-Mysql-PHP otherwise, would it :-) It would have to change to WIMP. It's not the best for marketing but we can't win them all. Windows - IIS - MySQL - PHP ;-) Some other production alternatives include: WIMA - Win, IIS, MySQL, ASP WAMP - Win, Apache, MySQL, PHP WWSHM - Win, Windows Scripting Host, MySQL WTM - Win, Tomcat, MySQL ... Are there other stacks on MS that you have heard of or are using? -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Event feature already working in Server 5.1.37
-Original Message- From: shawn.gr...@sun.com [mailto:shawn.gr...@sun.com] Sent: Wednesday, January 27, 2010 12:21 AM To: Daevid Vincent Cc: 'Dan Nelson'; mysql@lists.mysql.com Subject: Re: Event feature already working in Server 5.1.37 Daevid Vincent wrote: -Original Message- From: Dan Nelson [mailto:dnel...@allantgroup.com] Sent: Monday, January 25, 2010 11:42 PM To: Daevid Vincent ...snipped ... People really use Windows for a mySQL server? Weird. ... Yes, they do. Not only is MySQL as cheap as the free version of MS SQL but it doesn't suffer from the hard limits the free version of MS SQL imposes and it works across all of your servers, regardless of platform. MS products are limited to Windows boxes. You cannot assemble a new Linux box and get MS-anything to run on it natively. [JS] I second this. Instead of using a LAMP development environment, I went with WAMP -- even though our production environment was LAMP. It was a lot easier than setting up LAMP in a virtual machine. When we shut down our LAMP site for cost reasons, I moved it to a WAMP environment that I bought off the shelf for $800. For that money I got 8GB of RAM, four cores, and a RAID controller. Another $90 for a second drive, and I've got mirroring going. Granted, it's a low-traffic site used for internal administration; but I think this box could handle a lot more traffic than it does. It seems to be loafing all of the time. It's a home/SOHO/gamer system, so it probably isn't as physically robust as a server grade machine at twice the price; but if it dies, I can be up and running on a newer, bigger, cheaper machine in little more than the time it takes me to run to the nearest big-box store. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Event feature already working in Server 5.1.37
In the last episode (Jan 27), Shawn Green said: It would have to change to WIMP. It's not the best for marketing but we can't win them all. Windows - IIS - MySQL - PHP ;-) Some other production alternatives include: WIMA - Win, IIS, MySQL, ASP WAMP - Win, Apache, MySQL, PHP WWSHM - Win, Windows Scripting Host, MySQL WTM - Win, Tomcat, MySQL ... Are there other stacks on MS that you have heard of or are using? WWJD - Windows, Websphere, Java, Derby? :) -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Event feature already working in Server 5.1.37
On Wed, Jan 27, 2010 at 5:52 PM, Jerry Schwartz jschwa...@the-infoshop.comwrote: [JS] I second this. Instead of using a LAMP development environment, I went with WAMP -- even though our production environment was LAMP. Generally a bad idea - you keep running into annoying minor differences between the systems. File paths, for example :-) It was a lot easier than setting up LAMP in a virtual machine. I'll set up up in under an hour, if you want :-) When we shut down our LAMP site for cost reasons, I moved it to a WAMP environment that I bought off the Wait. You shut down machines for cost reasons, and then go buy new ones ? shelf for $800. For that money I got 8GB of RAM, four cores, and a RAID controller. Another $90 for a second drive, and I've got mirroring going. Granted, it's a low-traffic site used for internal administration; but I think this box could handle a lot more traffic than it does. It seems to be loafing all of the time. Oh, probably. Webserving isn't all that hard of a job, if the site is reasonably well-designed. If you're implying that the LAMP setup you had earlier didn't perform quite as well, though, I'll go out on a leg and say that it probably wasn't managed very well. It's a home/SOHO/gamer system, so it probably isn't as physically robust as a server grade machine at twice the price; but if it dies, I can be up and running on a newer, bigger, cheaper machine in little more than the time it takes me to run to the nearest big-box store. True. Me and my server grade machine, however, will not have had that downtime, because I'll have been notified that a redundant component has failed, and will have replaced it while the machine was running. It's ultimately a matter of how much your uptime is worth to you, and keep in mind that on a saturday evening you may not even find a new machine until monday morning, and then you still have to start installing everything, not to mention find the latest backups of your data. Me, I'll go for the expensive server ones for my professional needs, thanks :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Event feature already working in Server 5.1.37
On Wed, Jan 27, 2010 at 6:10 PM, Dan Nelson dnel...@allantgroup.com wrote: WWJD - Windows, Websphere, Java, Derby? :) You are an evil, *evil* little man. I just spilled juice all over my keyboard. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Event feature already working in Server 5.1.37
Daevid, all, Daevid Vincent wrote: I don't get it... I mean, I get the concept -- it's a crontab; but why would someone opt to put these events here instead of in the God-given CRONTAB as everything else in the system uses? This just seems like one more place to forget about a query/code and have unexpected things happen. There's more than one way to do it. (Perl slogan, I didn't look up the author) We already have a plethora of 'cron-like' tools: * [[...]] Agreed - but this is Unix/Linux only (not Windows), and this multitude doesn't make things easier for people working on several systems. IMNSHO, the question is whether you are viewing some to-be-scheduled task an aspect of the system as a whole or rather as a database aspect. In the first case, use your system scheduler, like cron; in the latter case, it makes sense to handle it internal to the database. The advantage of scheduling database tasks in the database is that this allows database backups and migrations to include it. If you handle that scheduling via cron (or some other scheduler), you need to handle it as a separate thing in backups and migrations. In addition, Dan's points are of course valid ones. Unless I'm missing some killer functionality this provides (and from that URL, I'm not seeing any), then I wish the Sun/mySQL team would have spent their precious time on more pressing features and or bug-fixes [[...]] My answer above is to explain why this is seen useful by several people, not to claim any relative priority of this and other changes. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Event feature already working in Server 5.1.37
On Tue, Jan 26, 2010 at 12:21 AM, Daevid Vincent dae...@daevid.com wrote: * at * /etc/crontab * /var/spool/crontabs/root * /var/spool/crontabs/joeblow * /etc/cron.d/ * /etc/cron.daily/ * /etc/cron.hourly/ * /etc/cron.monthly/ That entire list represents exactly two tools: at and crontab. Allright, three if you wanna be anal about it: anacron. At and Cron are also different tools: at does one-time scheduling, while cron handled repeated scheduling. Unless I'm missing some killer functionality this provides (and from that URL, I'm not seeing any), then I wish the Sun/mySQL team would have spent their precious time on more pressing features and or bug-fixes such as this one that is now FOUR YEARS old... (that is erroneously marked as 'feature request'!) Except for the very very very useful is automagically included in backup (and possibly replication) that Joerg already mentioned (that was the second thing I thought of), there is also the ability to schedule by the second, whereas cron can only schedule by the minute, and at doesn't handle repeating events. Yes, I can definitely see the use in this. If you can't, then just ignore it. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Event feature already working in Server 5.1.37
Philipp Maske [Location Bretagne] wrote: Hi, I just wanted to remark, that the Event feature is already working in server version 5.1.37 (installed on Debian). In tech resources is mentioned that this feature would be available since version 5.1.6 (see http://dev.mysql.com/tech-resources/articles/event-feature.html). So I wanted to give up using it- but fortunately I tried it on my 5.1.37 server and it works fine.. So I you have a MySQL server version prior 5.1.6 an need the Event feature - I suggest just give it a try . Perhaps you are confused by the numbering sequence? Version 5.1.37 is 29 releases *later* than 5.1.6 . Those versions prior to 5.1.6 (the ones that would not have this feature) would be 5.1.0, 5.1.1, 5.1.2, 5.1.3, 5.1.4, and 5.1.5 . Sorry for the confusion. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Event feature already working in Server 5.1.37
-Original Message- From: Dan Nelson [mailto:dnel...@allantgroup.com] Sent: Monday, January 25, 2010 11:42 PM To: Daevid Vincent For a hosted environment (or a restricted corporate environment), it means you don't have to give your users shell accounts; they can schedule table cleanup operations, summary table refreshes, and other operations completely within MySQL. Fair enough. I can't imagine a host these days that doesn't give you a dedicated VM sandbox though. There are hosts out there that put all the fish in the same pond?! Wow. I certainly wouldn't trust that scenario with my data/site. The events will also fire the same whether the server is running Unix or Windows. People really use Windows for a mySQL server? Weird. It just seems so clunky. Don't get me wrong. I love XP. I run it on all my 12+ notebooks, netbooks, desktops, etc. But for any LAMP dev work, I either have a dedicated Linux box setup or a Virtual Machine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Event feature already working in Server 5.1.37
-Original Message- From: Daevid Vincent dae...@daevid.com Sent: 26 January 2010 21:50 To: dnel...@allantgroup.com Cc: mysql@lists.mysql.com Subject: RE: Event feature already working in Server 5.1.37 People really use Windows for a mySQL server? Weird. I'm seem to remember reading somewhere that Windows is currently the most popular platform for MySQL! It just seems so clunky. Don't get me wrong. I love XP. I run it on all my 12+ notebooks, netbooks, desktops, etc. But for any LAMP dev work, I either have a dedicated Linux box setup or a Virtual Machine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk === John Daisley MySQL 5.0 Certified Database Administrator (CMDBA) MySQL 5.0 Certified Developer Cognos BI Developer Telephone: +44(0)1283 537111 Mobile: +44(0)7812 451238 Email: john.dais...@butterflysystems.co.uk === Sent via HP IPAQ mobile device -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Event feature already working in Server 5.1.37
Daevid Vincent wrote: -Original Message- From: Dan Nelson [mailto:dnel...@allantgroup.com] Sent: Monday, January 25, 2010 11:42 PM To: Daevid Vincent ...snipped ... People really use Windows for a mySQL server? Weird. ... Yes, they do. Not only is MySQL as cheap as the free version of MS SQL but it doesn't suffer from the hard limits the free version of MS SQL imposes and it works across all of your servers, regardless of platform. MS products are limited to Windows boxes. You cannot assemble a new Linux box and get MS-anything to run on it natively. With C, C++, .NET, JAVA, and ODBC connection options available, it's very easy to make a connection to MySQL from practically any MS development language. Some connectors will even integrate themselves into Visual Studio. Windows, as foreign as it may seem, is actually a very viable MySQL development platform. I encourage you to try it out and let us know what you think. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Event feature already working in Server 5.1.37
I don't get it... I mean, I get the concept -- it's a crontab; but why would someone opt to put these events here instead of in the God-given CRONTAB as everything else in the system uses? This just seems like one more place to forget about a query/code and have unexpected things happen. We already have a plethora of 'cron-like' tools: * at * /etc/crontab * /var/spool/crontabs/root * /var/spool/crontabs/joeblow * /etc/cron.d/ * /etc/cron.daily/ * /etc/cron.hourly/ * /etc/cron.monthly/ Unless I'm missing some killer functionality this provides (and from that URL, I'm not seeing any), then I wish the Sun/mySQL team would have spent their precious time on more pressing features and or bug-fixes such as this one that is now FOUR YEARS old... (that is erroneously marked as 'feature request'!) http://bugs.mysql.com/bug.php?id=21641 http://bugs.mysql.com/bug.php?id=21641 -Original Message- From: Philipp Maske [Location Bretagne] [mailto:philipp.ma...@location-bretagne.de] Sent: Saturday, January 23, 2010 7:43 PM To: mysql@lists.mysql.com Subject: Event feature already working in Server 5.1.37 Hi, I just wanted to remark, that the Event feature is already working in server version 5.1.37 (installed on Debian). In tech resources is mentioned that this feature would be available since version 5.1.6 (see http://dev.mysql.com/tech-resources/articles/event-feature.html). So I wanted to give up using it- but fortunately I tried it on my 5.1.37 server and it works fine.. So I you have a MySQL server version prior 5.1.6 an need the Event feature - I suggest just give it a try . Friendly Philipp Maske Software Developer Dipl.-Oec. Philipp Maske Location Bretagne Maske Maske GbR Ferienhausvermittlung Am Wasserturm 13 31303 Burgdorf b. Hannover Deutschland / Allemagne Email: mailto:i...@location-bretagne.de i...@location-bretagne.de WWW: http://www.location-bretagne.de www.location-bretagne.de Mobile: +49 (0)172 4523977 OpenBC: http://www.openbc.com/hp/Philipp_Maske http://www.openbc.com/hp/Philipp_Maske -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Event feature already working in Server 5.1.37
In the last episode (Jan 25), Daevid Vincent said: I don't get it... I mean, I get the concept -- it's a crontab; but why would someone opt to put these events here instead of in the God-given CRONTAB as everything else in the system uses? This just seems like one more place to forget about a query/code and have unexpected things happen. For a hosted environment (or a restricted corporate environment), it means you don't have to give your users shell accounts; they can schedule table cleanup operations, summary table refreshes, and other operations completely within MySQL. The events will also fire the same whether the server is running Unix or Windows. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Event feature already working in Server 5.1.37
Hi, I just wanted to remark, that the Event feature is already working in server version 5.1.37 (installed on Debian). In tech resources is mentioned that this feature would be available since version 5.1.6 (see http://dev.mysql.com/tech-resources/articles/event-feature.html). So I wanted to give up using it- but fortunately I tried it on my 5.1.37 server and it works fine.. So I you have a MySQL server version prior 5.1.6 an need the Event feature - I suggest just give it a try . Friendly Philipp Maske Software Developer Dipl.-Oec. Philipp Maske Location Bretagne Maske Maske GbR Ferienhausvermittlung Am Wasserturm 13 31303 Burgdorf b. Hannover Deutschland / Allemagne Email: mailto:i...@location-bretagne.de i...@location-bretagne.de WWW: http://www.location-bretagne.de www.location-bretagne.de Mobile: +49 (0)172 4523977 OpenBC: http://www.openbc.com/hp/Philipp_Maske http://www.openbc.com/hp/Philipp_Maske
Re: Show Tables not working
Hey Nobody any ideas? -Ursprüngliche Nachricht- Von: Intell! Soft [mailto:intellis...@fachoptiker.net] Bereitgestellt: Freitag, 08. Jänner 2010 11:06 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Show Tables not working Hey I try to use: Show Tables from My Design Where My Design is the name of the Database. - But it is not working Even not with: Show Tables from 'My Design' How can I use a Database name wit 2 words in it!? THX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Show Tables not working
Try backticks. On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft h.ba...@intelli-soft.atwrote: Hey Nobody any ideas? -Ursprüngliche Nachricht- Von: Intell! Soft [mailto:intellis...@fachoptiker.net] Bereitgestellt: Freitag, 08. Jänner 2010 11:06 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Show Tables not working Hey I try to use: Show Tables from My Design Where My Design is the name of the Database. - But it is not working Even not with: Show Tables from 'My Design' How can I use a Database name wit 2 words in it!? THX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Show Tables not working
i dont think my design is valid. There should not be any SPACE for a db name. On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman vegiv...@tuxera.bewrote: Try backticks. On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft h.ba...@intelli-soft.at wrote: Hey Nobody any ideas? -Ursprüngliche Nachricht- Von: Intell! Soft [mailto:intellis...@fachoptiker.net] Bereitgestellt: Freitag, 08. Jänner 2010 11:06 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Show Tables not working Hey I try to use: Show Tables from My Design Where My Design is the name of the Database. - But it is not working Even not with: Show Tables from 'My Design' How can I use a Database name wit 2 words in it!? THX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Show Tables not working
As I told, Backticks don't work. But it is valid to use spaces in DB's because, mySQL Administrator can handle it and my third party tool MySQL Maestro can also handle it. So, when those two programs get the tables, then there has to be a solution for it!? THX -Ursprüngliche Nachricht- Von: Ananda Kumar [mailto:anan...@gmail.com] Bereitgestellt: Mittwoch, 13. Jänner 2010 15:07 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Re: Show Tables not working i dont think my design is valid. There should not be any SPACE for a db name. On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman vegiv...@tuxera.bewrote: Try backticks. On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft h.ba...@intelli-soft.at wrote: Hey Nobody any ideas? -Ursprüngliche Nachricht- Von: Intell! Soft [mailto:intellis...@fachoptiker.net] Bereitgestellt: Freitag, 08. Jänner 2010 11:06 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Show Tables not working Hey I try to use: Show Tables from My Design Where My Design is the name of the Database. - But it is not working Even not with: Show Tables from 'My Design' How can I use a Database name wit 2 words in it!? THX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Show Tables not working
It is valid, it's just not a very smart thing to do, as various tools may not be very happy about it. On Wed, Jan 13, 2010 at 3:07 PM, Ananda Kumar anan...@gmail.com wrote: i dont think my design is valid. There should not be any SPACE for a db name. On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman vegiv...@tuxera.be wrote: Try backticks. On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft h.ba...@intelli-soft.at wrote: Hey Nobody any ideas? -Ursprüngliche Nachricht- Von: Intell! Soft [mailto:intellis...@fachoptiker.net] Bereitgestellt: Freitag, 08. Jänner 2010 11:06 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Show Tables not working Hey I try to use: Show Tables from My Design Where My Design is the name of the Database. - But it is not working Even not with: Show Tables from 'My Design' How can I use a Database name wit 2 words in it!? THX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Show Tables not working
Then you possibly aren't using backticks, because I just tested them :-) On Wed, Jan 13, 2010 at 3:36 PM, Intell! Soft h.ba...@intelli-soft.atwrote: As I told, Backticks don't work. But it is valid to use spaces in DB's because, mySQL Administrator can handle it and my third party tool MySQL Maestro can also handle it. So, when those two programs get the tables, then there has to be a solution for it!? THX -Ursprüngliche Nachricht- Von: Ananda Kumar [mailto:anan...@gmail.com] Bereitgestellt: Mittwoch, 13. Jänner 2010 15:07 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Re: Show Tables not working i dont think my design is valid. There should not be any SPACE for a db name. On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman vegiv...@tuxera.bewrote: Try backticks. On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft h.ba...@intelli-soft.at wrote: Hey Nobody any ideas? -Ursprüngliche Nachricht- Von: Intell! Soft [mailto:intellis...@fachoptiker.net] Bereitgestellt: Freitag, 08. Jänner 2010 11:06 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Show Tables not working Hey I try to use: Show Tables from My Design Where My Design is the name of the Database. - But it is not working Even not with: Show Tables from 'My Design' How can I use a Database name wit 2 words in it!? THX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Show Tables not working
Yes, I am sorry, I didn't use Backticks.. - I used ' not ` THX - now it works! -Ursprüngliche Nachricht- Von: Johan De Meersman [mailto:vegiv...@tuxera.be] Bereitgestellt: Mittwoch, 13. Jänner 2010 17:11 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Re: Show Tables not working Then you possibly aren't using backticks, because I just tested them :-) On Wed, Jan 13, 2010 at 3:36 PM, Intell! Soft h.ba...@intelli-soft.atwrote: As I told, Backticks don't work. But it is valid to use spaces in DB's because, mySQL Administrator can handle it and my third party tool MySQL Maestro can also handle it. So, when those two programs get the tables, then there has to be a solution for it!? THX -Ursprüngliche Nachricht- Von: Ananda Kumar [mailto:anan...@gmail.com] Bereitgestellt: Mittwoch, 13. Jänner 2010 15:07 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Re: Show Tables not working i dont think my design is valid. There should not be any SPACE for a db name. On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman vegiv...@tuxera.bewrote: Try backticks. On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft h.ba...@intelli-soft.at wrote: Hey Nobody any ideas? -Ursprüngliche Nachricht- Von: Intell! Soft [mailto:intellis...@fachoptiker.net] Bereitgestellt: Freitag, 08. Jänner 2010 11:06 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Show Tables not working Hey I try to use: Show Tables from My Design Where My Design is the name of the Database. - But it is not working Even not with: Show Tables from 'My Design' How can I use a Database name wit 2 words in it!? THX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Show Tables not working
At 08:36 AM 1/13/2010, Intell! Soft wrote: As I told, Backticks don't work. But it is valid to use spaces in DB's because, mySQL Administrator can handle it and my third party tool MySQL Maestro can also handle it. So, when those two programs get the tables, then there has to be a solution for it!? THX Replace the space in the database name with an underscore and save yourself a lot of grief. You can go to the data directory and see if you can browse the directory. Maybe the OS has a problem with spaces or maybe it isn't really a space in the database name. Maybe it is a special character or two spaces? Mike -Ursprüngliche Nachricht- Von: Ananda Kumar [mailto:anan...@gmail.com] Bereitgestellt: Mittwoch, 13. Jänner 2010 15:07 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Re: Show Tables not working i dont think my design is valid. There should not be any SPACE for a db name. On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman vegiv...@tuxera.bewrote: Try backticks. On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft h.ba...@intelli-soft.at wrote: Hey Nobody any ideas? -Ursprüngliche Nachricht- Von: Intell! Soft [mailto:intellis...@fachoptiker.net] Bereitgestellt: Freitag, 08. Jänner 2010 11:06 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Show Tables not working Betreff: Show Tables not working Hey I try to use: Show Tables from My Design Where My Design is the name of the Database. - But it is not working Even not with: Show Tables from 'My Design' How can I use a Database name wit 2 words in it!? THX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
AW: InnoDB Corrupted databases (innodb_force_recovery not working)
Baron, Thanks alot for your reply - I am trying out these tools today. Lukas Lukas C. C. Hempel CEO Delux Group - Approaching future. www.delux.me Postfach 10 02 10 D-48051 Münster Mail: lu...@delux.me This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. Delux Credit: www.delux-credit.com Delux Host: www.delux-host.com Delux Software: www.delux.me Admin panel: http://admin.delux-host.com Paypal: pay...@delux-host.com Delux is a company of Lukas Hempel, Tax ID: 337/5105/2023 -Ursprüngliche Nachricht- Von: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] Im Auftrag von Baron Schwartz Gesendet: Montag, 14. Dezember 2009 22:57 An: Lukas C. C. Hempel Cc: mysql@lists.mysql.com Betreff: Re: InnoDB Corrupted databases (innodb_force_recovery not working) Lukas, If you can't get innodb_force_recovery to work, then you might have to try to recover the data with these tools: http://code.google.com/p/innodb-tools/ Regards Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=lu...@delux-host.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
InnoDB Corrupted databases (innodb_force_recovery not working)
Hey there, I have recently imported the database files from a crashed server and I am currently trying to get the new server running with the old data. However, after starting the MySQL Server, I only get the following error message: 091214 20:51:46 mysqld started InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on InnoDB: Skipping log redo InnoDB: Error: page n:o stored in the page read in is 538976288, should be 3! InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 3. InnoDB: You may have to recover from a backup. 091214 20:51:47 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 2020202020202020202020202020202020202020202020202020202020202020202020202020 20202020202020202020202020202020202020202020202020202020202$ 091214 20:51:47 InnoDB: Page checksum 1605920614, prior-to-4.0.14-form checksum 3161919040 InnoDB: stored checksum 538976288, prior-to-4.0.14-form stored checksum 236985105 InnoDB: Page lsn 538976288 538976288, low 4 bytes of lsn at page end 10651 InnoDB: Page number (if stored to page already) 538976288, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 538976288 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 3. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. After that, the following error message is looped: 091214 20:51:47 InnoDB: Error: trying to access tablespace 538976288 page no. 538976288, InnoDB: but the tablespace does not exist or is just being dropped. The MySQL server then is unusable and is taking 99% CPU ressources. I tried to use the innodb_force_recovery option in the my.cfg file, but that did not change this behavior. Does anyone have an idea for me to restore the databases? I have no backup and rely on getting this files back to work. Any help is appreciated, thanks in advance, Lukas Lukas C. C. Hempel Delux Group - Approaching future. http://www.delux.me/ www.delux.me Postfach 10 02 10 D-48051 Münster This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. Delux Credit: http://www.delux-credit.com/ www.delux-credit.com Delux Host: http://www.delux-host.com/ www.delux-host.com Delux Software: http://www.delux.me/ www.delux.me Admin panel: http://admin.delux-host.com/ http://admin.delux-host.com Paypal: mailto:pay...@delux-host.com pay...@delux-host.com Delux is a company of Lukas Hempel, Tax ID: 337/5105/2023
Re: InnoDB Corrupted databases (innodb_force_recovery not working)
Lukas, If you can't get innodb_force_recovery to work, then you might have to try to recover the data with these tools: http://code.google.com/p/innodb-tools/ Regards Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Passwords not working
On Mon, Oct 19, 2009 at 07:45:11PM -0400, Michael Dykman wrote: The type of password instability you are talking about is pretty much unheard of in MySQL.. Yeah, well, I can have a real black thumb for this sort of thing :-) I'm sure I read about at least two different ways to add passwords. I don't know... usually, it works, but on this particular machine... I just tried to connect asa root again, and it's rejecting the password that I KNOW I set... it's written down. I am not mistyping it. I'm copy-and-pasting from the same text that I used to set the password. I just don't know how to get MySQL to tell me exactly what it's unhappy about. I'm going to go and reset the root password again, and it'll work for a while... but tomorrow, it almost certainly will not work again, and I'll have to go back and reset it *again*. however, reverse DNS resolution is always messing up depending on the network setup. From a console on your database host, how easily can you resolve the hostnames that your The web server and database server (both VMs under VMware ESXi) each have two network interfaces... one public, and one private. The private interfaces are connected to a private VLAN on a virtual switch that is only for these two servers. MySQL only listens on 172.16.1.1, and the web server connects to that IP. On each host, I have a hosts entry for the other. -- *** * John Oliver http://www.john-oliver.net/ * * * *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Passwords not working
I have a problem with MySQL passwords... I set them, write them down... and they stop working. I have to go in and manually reset them. Right now, I have a database that, even after resetting the password, I still cannot access it. /var/log/mysql.log doesn't give me any useful information. How can I get MySQL to tell me what it's unhappy about, or get more information from the client other than it just didn't work? I also have problems with MySQL resolving names, or not resolving names, or ??? I usually add 'user'@'ip.address' and 'user'@'host.name' But, more and more often, I've had to put skip-name-resolve in my.cnf, but with my current problem, I'm still seeing that 'user'@'host-name' is being rejected, even when I use -h ip.address on the command line And when I add those two users, and go to reset passwords, it doesn't want to let me specify 'user'@'ip.address' or 'user'@'host.name' but just 'user' I *think* it's resetting the password for both... the hashes are always the same. But I just don't know. What am I missing? -- *** * John Oliver http://www.john-oliver.net/ * * * *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Passwords not working
The type of password instability you are talking about is pretty much unheard of in MySQL.. however, reverse DNS resolution is always messing up depending on the network setup. From a console on your database host, how easily can you resolve the hostnames that your client is presenting? What is your network setup? Not the safest of practices, but for dev accounts, I usually create one for user@'%' and sometimes one one for u...@localhost if needed and that works very well for me.. - michael dykman On Mon, Oct 19, 2009 at 7:30 PM, John Oliver joli...@john-oliver.net wrote: I have a problem with MySQL passwords... I set them, write them down... and they stop working. I have to go in and manually reset them. Right now, I have a database that, even after resetting the password, I still cannot access it. /var/log/mysql.log doesn't give me any useful information. How can I get MySQL to tell me what it's unhappy about, or get more information from the client other than it just didn't work? I also have problems with MySQL resolving names, or not resolving names, or ??? I usually add 'user'@'ip.address' and 'user'@'host.name' But, more and more often, I've had to put skip-name-resolve in my.cnf, but with my current problem, I'm still seeing that 'user'@'host-name' is being rejected, even when I use -h ip.address on the command line And when I add those two users, and go to reset passwords, it doesn't want to let me specify 'user'@'ip.address' or 'user'@'host.name' but just 'user' I *think* it's resetting the password for both... the hashes are always the same. But I just don't know. What am I missing? -- *** * John Oliver http://www.john-oliver.net/ * * * *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Passwords not working
On Mon, 19 Oct 2009 16:30:47 -0700 John Oliver joli...@john-oliver.net wrote: I have a problem with MySQL passwords... I set them, write them down... and they stop working. I have to go in and manually reset them. Right now, I have a database that, even after resetting the password, I still cannot access it. /var/log/mysql.log doesn't give me any useful information. How can I get MySQL to tell me what it's unhappy about, or get more information from the client other than it just didn't work? I also have problems with MySQL resolving names, or not resolving names, or ??? I usually add 'user'@'ip.address' and 'user'@'host.name' But, more and more often, I've had to put skip-name-resolve in my.cnf, but with my current problem, I'm still seeing that 'user'@'host-name' is being rejected, even when I use -h ip.address on the command line And when I add those two users, and go to reset passwords, it doesn't want to let me specify 'user'@'ip.address' or 'user'@'host.name' but just 'user' I *think* it's resetting the password for both... the hashes are always the same. But I just don't know. What am I missing? Are you accessing MySQL from the same host? If so, you don't need the -h option unless that's the only entry in your grant table under that username (i.e. 'user'@'ip-address'). Can you give us an example of how you're setting the username and their permissions? Here's a typical example that gives access to an entire database to a single user provided they're accessing it on the same host: GRANT ALL on database-name.* to 'user'@'localhost' identified by 'foobar'; The username, password AND hostname have to match up for authentication to be successful: 'user'@'localhost' may be different than 'user'@'ip-address' even if they're intended to be the same person. -- Greg Maruszeczka Office: 250.412.9651 || Mobile: 250.886.4577 Skype: websage.ca || GTalk IM: gmarus http://websage.ca GnuPG-ID: 0x4309323E, http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Passwords not working
someone probably installed mysql for DHCP address e.g 192.168.fu.bar then as luck would have it the IP address changed if you pull all network connections everyone on that box should be able to access mysql Salutations de l'état du chômage Martin Gainty __ Note de déni et de confidentialité Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Mon, 19 Oct 2009 16:48:36 -0700 From: listm...@websage.ca To: mysql@lists.mysql.com Subject: Re: Passwords not working On Mon, 19 Oct 2009 16:30:47 -0700 John Oliver joli...@john-oliver.net wrote: I have a problem with MySQL passwords... I set them, write them down... and they stop working. I have to go in and manually reset them. Right now, I have a database that, even after resetting the password, I still cannot access it. /var/log/mysql.log doesn't give me any useful information. How can I get MySQL to tell me what it's unhappy about, or get more information from the client other than it just didn't work? I also have problems with MySQL resolving names, or not resolving names, or ??? I usually add 'user'@'ip.address' and 'user'@'host.name' But, more and more often, I've had to put skip-name-resolve in my.cnf, but with my current problem, I'm still seeing that 'user'@'host-name' is being rejected, even when I use -h ip.address on the command line And when I add those two users, and go to reset passwords, it doesn't want to let me specify 'user'@'ip.address' or 'user'@'host.name' but just 'user' I *think* it's resetting the password for both... the hashes are always the same. But I just don't know. What am I missing? Are you accessing MySQL from the same host? If so, you don't need the -h option unless that's the only entry in your grant table under that username (i.e. 'user'@'ip-address'). Can you give us an example of how you're setting the username and their permissions? Here's a typical example that gives access to an entire database to a single user provided they're accessing it on the same host: GRANT ALL on database-name.* to 'user'@'localhost' identified by 'foobar'; The username, password AND hostname have to match up for authentication to be successful: 'user'@'localhost' may be different than 'user'@'ip-address' even if they're intended to be the same person. -- Greg Maruszeczka Office: 250.412.9651 || Mobile: 250.886.4577 Skype: websage.ca || GTalk IM: gmarus http://websage.ca GnuPG-ID: 0x4309323E, http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Hotmail: Trusted email with Microsoft’s powerful SPAM protection. http://clk.atdmt.com/GBL/go/177141664/direct/01/
MySQL query working directly but not through .NET connection
Here's the query: INSERT INTO USERS(USER_ID,USER_NAME,USER_SCREENNAME,USER_DESCRIPTION,USER_FOLLOWERS,USER_IMAGE,USER_FRIENDS,USER_LOCATION,USER_CREATEDAT) VALUES('31264066','Justin Wienkers','BabyVegaz','I’m your secondhand news/yeah. That and an (aspiring) screenwriter, trained journalist, blogger, and sometime (Answer B!tch) podcast co-host.',207,'http://s3.amazonaws.com/twitter_production/profile_images/139858445/2401_560553579174_20308516_34658042_5292221_n_normal.jpg',160,'Hollywood, CA',str_to_date('Tue Apr 14 23:29:15 + 2009','%a %b %e %H:%i:%s + %Y')); It works when I input it directly through the query browser, but not through my VB.NET code. Dim sbInsert As New StringBuilder(INSERT INTO USERS(USER_ID,USER_NAME,USER_SCREENNAME,USER_DESCRIPTION,USER_FOLLOWERS,USER_IMAGE,USER_FRIENDS,USER_LOCATION,USER_CREATEDAT) VALUES() sbInsert.Append(' frnd.ID ',) sbInsert.Append(' frnd.name ',) sbInsert.Append(' frnd.screenname ',) 'strReplaceString = frnd.description.Replace(', \') 'Debug.Print(Asc(“)) 'strReplaceString = strReplaceString.Replace(, ') 'strReplaceString = strReplaceString.Replace(Chr(147), \') 'strReplaceString = strReplaceString.Replace(Chr(148), \') strReplaceString = frnd.description strReplaceString = strReplaceString.Replace(', ) strReplaceString = strReplaceString.Replace(, ) strReplaceString = strReplaceString.Replace(Chr(147), ) strReplaceString = strReplaceString.Replace(Chr(148), ) sbInsert.Append(' strReplaceString ',) sbInsert.Append(frnd.followers ,) sbInsert.Append(' frnd.profimage ',) sbInsert.Append(frnd.friends ,) sbInsert.Append(' frnd.location ',) sbInsert.Append(str_to_date(' frnd.createdate ','%a %b %e %H:%i:%s + %Y'));) Clipboard.SetText(sbInsert.ToString()) Dim myInsertNewUser As New MySqlClient.MySqlCommand(sbInsert.ToString, myTweetConn) myInsertNewUser.ExecuteNonQuery() Keep in mind that I have other queries that are working just fine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
Can someone please explain why I have 3 entries for root or if this is normal behavior for MySQL? I thought after a installation of MySQL, you normally have 2: 1 - localhost 2 - host.domain.com For some reason I had a 3rd entry: 3 - 127.0.0.1 I don't know if I did the right thing but I ran the following command: update user set host=localhost where host='127.0.0.1'; This basically changed the 127.0.0.1 entry in mysql databased, host section to localhost. I am not sure if MySQL needs to have the 3rd host entry for root that equals 127.0.0.1 or if it generally should just have the localhost and fqdn. Can anyone please clear this up for me? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
It is important to understand deeply mysql client access control. Basically you need only one root account from the localhost for administration purposes. Keep in mind that when you login specifying 'localhost' (either by the -h flag or implicit) MySQL will use the mysql client socket interface, if you specify '127.0.0.1' it will use the TCP/IP port (3306). 'localhost' is a sort of keyword telling the client to use the unix socket file, '127.0.0.1' is bound to the tcp/ip port. Host matching is always done BEFORE user matching. MySQL sorts HOSTS with more specific entries on top and less ones on bottom ('%' wildcarded entries) MySQL sorts USERS with more specific entries on top and less ones on bottom ('' empty user) Bottom line, you need a triplet of USER-HOST-PASS for each account. having localhost AND 127.0.0.1 it is only related to the different connection method (socket , tcpip) for any other (remote) account tcp-ip will be used. I don't know if you are now more ore less confused! Claudio Carlos Williams wrote: Can someone please explain why I have 3 entries for root or if this is normal behavior for MySQL? I thought after a installation of MySQL, you normally have 2: 1 - localhost 2 - host.domain.com For some reason I had a 3rd entry: 3 - 127.0.0.1 I don't know if I did the right thing but I ran the following command: update user set host=localhost where host='127.0.0.1'; This basically changed the 127.0.0.1 entry in mysql databased, host section to localhost. I am not sure if MySQL needs to have the 3rd host entry for root that equals 127.0.0.1 or if it generally should just have the localhost and fqdn. Can anyone please clear this up for me? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
On Fri, May 15, 2009 at 3:57 PM, Claudio Nanni claudio.na...@gmail.com wrote: I don't know if you are now more ore less confused! Claudio I would say less because you basically explained that I need to have localhost 127.0.0.1. Now my problem is that no longer have this and would like to know what I can do to resolve this by re-adding the 127.0.0.1 host / root user parameter back into MySQL. mysql use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select user, host, password from user; +---++--+ | user | host | password | +---++--+ | root | host.domain.com | 032c41e8435273a7 | | root | localhost | 032c41e8435273a7 | | roundcube | localhost | 032c41e8435273a7 | | mrbs | localhost | 6322a1af59897de4 | | phpbb| localhost | 5d2e19393cc5ef67 | +---++--+ 5 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
Exactly, you need as many rows as many combination user/host we can also say that an account in MySQL is not the username BUT the username AND host combination. If you want to duplicate any account (also the root/localhost) do this: mysql show grants for 'root'@'localhost'; then have fun! Claudio Carlos Williams wrote: On Fri, May 15, 2009 at 3:57 PM, Claudio Nanni claudio.na...@gmail.com wrote: I don't know if you are now more ore less confused! Claudio I would say less because you basically explained that I need to have localhost 127.0.0.1. Now my problem is that no longer have this and would like to know what I can do to resolve this by re-adding the 127.0.0.1 host / root user parameter back into MySQL. mysql use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select user, host, password from user; +---++--+ | user | host | password | +---++--+ | root | host.domain.com | 032c41e8435273a7 | | root | localhost | 032c41e8435273a7 | | roundcube | localhost | 032c41e8435273a7 | | mrbs | localhost | 6322a1af59897de4 | | phpbb| localhost | 5d2e19393cc5ef67 | +---++--+ 5 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Password Reset Not Working
I noticed today that I strangely was unable to login to MySQL as root. I just assumed I forgot the password and decided to reset my root password: 1 - /etc/init.d/mysqld stop 2 - mysqld_safe --skip-grant-tables 3 - mysql -u root 4 - mysql use mysql; mysql mysql update user set password='letmein' where user='root'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql flush privileges; mysql quit 5 - /etc/init.d/mysqld restart *Now after I do all that, I get the following:* mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) I have done this over and over and can't login so I am wondering if something is wrong with MySQL or am I just not properly resetting the password... Someone please help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
Hi Carlos Try this mysql update user set password=password('letmein') where user='root'; This way the password is saved encrypted, thats the way is compared when you try to log in. Carlos On 5/14/2009 5:28 PM, Carlos Williams wrote: I noticed today that I strangely was unable to login to MySQL as root. I just assumed I forgot the password and decided to reset my root password: 1 - /etc/init.d/mysqld stop 2 - mysqld_safe --skip-grant-tables 3 - mysql -u root 4 - mysql use mysql; mysql mysql update user set password='letmein' where user='root'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql flush privileges; mysql quit 5 - /etc/init.d/mysqld restart *Now after I do all that, I get the following:* mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) I have done this over and over and can't login so I am wondering if something is wrong with MySQL or am I just not properly resetting the password... Someone please help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
On Thu, May 14, 2009 at 6:31 PM, Carlos Proal carlos.pr...@gmail.com wrote: Hi Carlos Try this mysql update user set password=password('letmein') where user='root'; This way the password is saved encrypted, thats the way is compared when you try to log in. Thanks for the reply! I followed both methods in the following URL / link: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix After doing both successfully, I was unable to login over and over. I think something is wrong with MySQL. I have never seen this before :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. Carlos On 5/14/2009 5:39 PM, Carlos Williams wrote: On Thu, May 14, 2009 at 6:31 PM, Carlos Proal carlos.pr...@gmail.com wrote: Hi Carlos Try this mysql update user set password=password('letmein') where user='root'; This way the password is saved encrypted, thats the way is compared when you try to log in. Thanks for the reply! I followed both methods in the following URL / link: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix After doing both successfully, I was unable to login over and over. I think something is wrong with MySQL. I have never seen this before :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
On Thu, May 14, 2009 at 6:44 PM, Carlos Proal carlos.pr...@gmail.com wrote: Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. I checked and when I ran the command you suggested: mysql use mysql; Database changed mysql select * from user where user='root'; I get a bunch of gibberish on the screen but the only thing I can make out are two entries for root: | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y | 127.0.0.1| root | 6d21bd9609b168e4 | Y | Y | Y So what does this mean and how can I resolve this? I am trying this from the machine locally so I would assume localhost works fine... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
try running the command like this select * from user where user='root' \G Capital G is a must. thanks Doug Carlos Williams wrote: On Thu, May 14, 2009 at 6:44 PM, Carlos Proal carlos.pr...@gmail.com wrote: Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. I checked and when I ran the command you suggested: mysql use mysql; Database changed mysql select * from user where user='root'; I get a bunch of gibberish on the screen but the only thing I can make out are two entries for root: | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y | 127.0.0.1| root | 6d21bd9609b168e4 | Y | Y | Y So what does this mean and how can I resolve this? I am trying this from the machine locally so I would assume localhost works fine... -- http://sfp.central.sun.com/ * Douglas Nelson * Senior Technical Consultant *Need Sun JES Help?* Software Field Practice Phone +1 877-234-2879/x51438 Mobile 919-259-3719 Email sfp-h...@sun.com http://blogs.sfbay/roller/page/sfp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
The machine mysql.unixslut.com is not the same than localhost, right ??, If you only need root access from localhost you can delete the first row (delete from user where user='root' and host='mysql.unixslut.com';) Carlos On 5/14/2009 5:55 PM, Carlos Williams wrote: On Thu, May 14, 2009 at 6:44 PM, Carlos Proal carlos.pr...@gmail.com wrote: Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. I checked and when I ran the command you suggested: mysql use mysql; Database changed mysql select * from user where user='root'; I get a bunch of gibberish on the screen but the only thing I can make out are two entries for root: | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y | 127.0.0.1| root | 6d21bd9609b168e4 | Y | Y | Y So what does this mean and how can I resolve this? I am trying this from the machine locally so I would assume localhost works fine... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
Carlos Proal wrote: The machine mysql.unixslut.com is not the same than localhost, right ??, If you only need root access from localhost you can delete the first row (delete from user where user='root' and host='mysql.unixslut.com';) Carlos On 5/14/2009 5:55 PM, Carlos Williams wrote: On Thu, May 14, 2009 at 6:44 PM, Carlos Proal carlos.pr...@gmail.com wrote: Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. I checked and when I ran the command you suggested: mysql use mysql; Database changed mysql select * from user where user='root'; I get a bunch of gibberish on the screen but the only thing I can make out are two entries for root: | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y | 127.0.0.1| root | 6d21bd9609b168e4 | Y | Y | Y So what does this mean and how can I resolve this? I am trying this from the machine locally so I would assume localhost works fine... Pardon, for butting in, but are you seriou? unix slut ? My first impression based on that would be, man you've been hacked! :-D -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
On Thu, May 14, 2009 at 7:06 PM, Carlos Proal carlos.pr...@gmail.com wrote: The machine mysql.unixslut.com is not the same than localhost, right ??, If you only need root access from localhost you can delete the first row (delete from user where user='root' and host='mysql.unixslut.com';) Carlos, Yes, my machine mysql.unixslut.com is localhost / 127.0.0.1/8. They're both the same machine. It's just that I was told MySQL manages connection for users on a local / domain basis so that is the reason for both entries. In my opinion, both entries are valid. I could be wrong. **No, the server has not been hacked** Thanks for your continued support!] - Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
On Thu, May 14, 2009 at 7:01 PM, Douglas Nelson douglas.nel...@sun.com wrote: try running the command like this select * from user where user='root' \G Capital G is a must. I did the following: [r...@mysql ~]# /etc/init.d/mysqld stop Stopping MySQL:[ OK ] [r...@mysql ~]# mysqld_safe --skip-grant-tables [1] 3072 Starting mysqld daemon with databases from /var/lib/mysql [r...@mysql ~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select * from user where user='root' \G *** 1. row *** Host: mysql.unixslut.com User: root Password: 6d24bd789879jhs Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 *** 2. row *** Host: 127.0.0.1 User: root Password: 6d24bd789879jhs Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 2 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Trigger working with server 5.0.51 but not 5.0.22
Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I tried to move this to a production server (version 5.0.22 fedora) I ran into 2 problems: 1. I try to install the trigger from a text file, and the first lines were: DELIMITER $$ DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos; CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of course I use this text file at my development environment without problem. And at production server I need to delete the line DROP TRIGGER ... to create the trigger. 2. This trigger create a temporary table and then call a stored procedure wich does some arithmetic and put the result in the temporary table. When the trigger get fired at the production server, I got ***SQL Error:* 1146: Table 'filasPOS.tmp_calculos_res' doesn't exist Is there a big change in handling triggers from version 5.0.22 to 5.0.51? Or I missing something? Thanks in advance -- Mauricio Tellez
Re: Trigger working with server 5.0.51 but not 5.0.22
2009/5/13 Mauricio Tellez mauricio.tel...@gmail.com: Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I tried to move this to a production server (version 5.0.22 fedora) I ran into [...] but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of http://dev.mysql.com/doc/refman/5.0/en/drop-trigger.html The IF EXISTS clause was added in MySQL 5.0.32. Greetings, mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Trigger working with server 5.0.51 but not 5.0.22
The DROP TRIGGER command is in the wrong place. You changed the delimiter to $$ but still tried to use the semicolon(;) with DROP TRIGGER. Your code should read like this: DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos; DELIMITER $$ CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas Or change the ; to $$ on the DROP TRIGGER command like this: DELIMITER $$ DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos $$ CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas Give it a try !!! Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM : RolandoLogicWorx Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Mauricio Tellez [mailto:mauricio.tel...@gmail.com] Sent: Wednesday, May 13, 2009 4:35 AM To: mysql@lists.mysql.com Subject: Trigger working with server 5.0.51 but not 5.0.22 Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I tried to move this to a production server (version 5.0.22 fedora) I ran into 2 problems: 1. I try to install the trigger from a text file, and the first lines were: DELIMITER $$ DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos; CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of course I use this text file at my development environment without problem. And at production server I need to delete the line DROP TRIGGER ... to create the trigger. 2. This trigger create a temporary table and then call a stored procedure wich does some arithmetic and put the result in the temporary table. When the trigger get fired at the production server, I got ***SQL Error:* 1146: Table 'filasPOS.tmp_calculos_res' doesn't exist Is there a big change in handling triggers from version 5.0.22 to 5.0.51? Or I missing something? Thanks in advance -- Mauricio Tellez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Installation Not Working
Hi, I've tried several times now to get a local installation of MySQL going on my iMac (w/ OS X ver. 10.5.6). I've carefully tried everything I could comprehend to do per the mysql.com resource pages/ manual. I have also tried several procedures I discovered via Google searches that seemed to be promising/authoritative. Throughout this process I have encountered so many obstacles I've lost count. I have deleted and reinstalled the whole MySQL package (mysql-5.1.32-osx10.5- x86.dmg) 4 times while trying to follow the documentation with excruciating attention to the detail and nuances. I think I have encountered every version of 'permission denied', 'unable to open socket', unable to find 'my_print_defaults', 'unable to bind on port 3306', 'unable to access pid file' type of error possible. I am absolutely drowning in barely comprehended documentation, tips and tricks that have taken me down one dead end after another. Is there any way an ordinary person who doesn't walk on water with UNIX and is not able to manufacture a supercomputer with his pocketknife can get a simple 'local' MySQL installation going on his Mac? This is an appeal for help from someone who has wrestled with the official documentation to the point that it's liable to become a textbook case of 'terminal computer- frustration'. I wasn't able to figure out exactly how to comply with your 'MUST use the ./bin/mysqlbug script!' directive so I've copied and pasted the results of running that script below (please accept my apologies if these results were supposed to be transmitted in a different way). SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: DPD To: mysql@lists.mysql.com Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator: Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-5.1.32 (MySQL Community Server (GPL)) C compiler:i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465) C++ compiler: i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465) Environment: machine, os, target, libraries (multiple lines) System: Darwin DPD-Mac.local 9.6.0 Darwin Kernel Version 9.6.0: Mon Nov 24 17:37\ :00 PST 2008; root:xnu-1228.9.59~1/RELEASE_I386 i386 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: i686-apple-darwin9 Configured with: /var/tmp/gcc/gcc-5490~1/src/configure --disable- checking -enabl\ e-werror --prefix=/usr --mandir=/share/man --enable-languages=c,objc,c+ +,obj-c++\ --program-transform-name=/^[cg][^.-]*$/s/$/-4.0/ --with-gxx-include- dir=/includ\ e/c++/4.0.0 --with-slibdir=/usr/lib --build=i686-apple-darwin9 --with- arch=apple\ --with-tune=generic --host=i686-apple-darwin9 --target=i686-apple- darwin9 Thread model: posix gcc version 4.0.1 (Apple Inc. build 5490) ** Regards, Dennis
Install problem: configure --prefix not working
I am trying to install mysql in a non-standard directory - specifically /app/mysql Subsequently, I ran the following configure command: ./configure --prefix=/app/mysql --with-plugins=innobase,myisam There were no problems with the compile, but mysql failed to start. Furthermore, the following output was produced in the nohup.out file -bash-3.2$ cat nohup.out 090211 09:40:35 mysqld_safe Logging to '/var/log/mysqld.log'. mkdir: cannot create directory `/var/lib/mysql': Permission denied chown: cannot access `/var/lib/mysql': No such file or directory chmod: cannot access `/var/lib/mysql': No such file or directory 090211 09:40:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied /app/mysql/bin/mysqld_safe: line 133: /var/log/mysqld.log: Permission denied 090211 09:40:35 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied For some reason, the mysql install is looking to /var even though I specificied /app/mysql in the compile. How do I fix this? Thanks! Colin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: configure --prefix not working
/var/lib/mysql is the standard loation for the data_file_path, the data directory. change it in my.cnf. var/log is the default location for log files, also changeable in the config file. Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Wed, Feb 11, 2009 at 5:35 PM, csego...@gmail.com csego...@gmail.com wrote: I am trying to install mysql in a non-standard directory - specifically /app/mysql Subsequently, I ran the following configure command: ./configure --prefix=/app/mysql --with-plugins=innobase,myisam There were no problems with the compile, but mysql failed to start. Furthermore, the following output was produced in the nohup.out file -bash-3.2$ cat nohup.out 090211 09:40:35 mysqld_safe Logging to '/var/log/mysqld.log'. mkdir: cannot create directory `/var/lib/mysql': Permission denied chown: cannot access `/var/lib/mysql': No such file or directory chmod: cannot access `/var/lib/mysql': No such file or directory 090211 09:40:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied /app/mysql/bin/mysqld_safe: line 133: /var/log/mysqld.log: Permission denied 090211 09:40:35 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied For some reason, the mysql install is looking to /var even though I specificied /app/mysql in the compile. How do I fix this? Thanks! Colin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: % wildcard host permission not working
I'm also having this strange problem just as stated here, anybody find a fix for this yet? Again, using wildcards (%) for the Host don't work while the FQDN does from the exact same remote machine, the exact same username, etc. There is something about the wildcard ACL that is not working properly for this or any other user. I have proper forward and reverse DNS entries for this machine I'm connecting from. Any ideas why this might be and where I ought to look? Yes, I did FLUSH PRIVILEGES and I think I only have one username/host entry for this user. Adam Erik Giberti wrote: Did you FLUSH PRIVILEGES? I'd also check that the username and passwords are the same for each host entry, I've had problems if passwords were different for a shared username from different hosts. On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote: I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=1 -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- Joe Auty NetMusician: web publishing software for musicians http://www.netmusician.org [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: % wildcard host permission not working
Would you mind posting what you tried? Did you put single quotes around the wildcard like '%' ? For example: GRANT ALL ON somedb.* TO 'someuser'@'%'; Also see http://dev.mysql.com/doc/refman/5.0/en/grant.html CheersFish On Sat, Oct 25, 2008 at 1:40 PM, Joe Auty [EMAIL PROTECTED] wrote: I'm also having this strange problem just as stated here, anybody find a fix for this yet? Again, using wildcards (%) for the Host don't work while the FQDN does from the exact same remote machine, the exact same username, etc. There is something about the wildcard ACL that is not working properly for this or any other user. I have proper forward and reverse DNS entries for this machine I'm connecting from. Any ideas why this might be and where I ought to look? Yes, I did FLUSH PRIVILEGES and I think I only have one username/host entry for this user. Adam Erik Giberti wrote: Did you FLUSH PRIVILEGES? I'd also check that the username and passwords are the same for each host entry, I've had problems if passwords were different for a shared username from different hosts. On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote: I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=1 -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- Joe Auty NetMusician: web publishing software for musicians http://www.netmusician.org [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: % wildcard host permission not working
Fish Kungfu wrote: Would you mind posting what you tried? Did you put single quotes around the wildcard like '%' ? For example: GRANT ALL ON somedb.* TO 'someuser'@'%'; Also see http://dev.mysql.com/doc/refman/5.0/en/grant.html CheersFish Sure! On what I'll call mynewserver CREATE USER 'testuser'@'%' IDENTIFIED BY '***'; GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'%' IDENTIFIED BY '***' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ; FLUSH PRIVILEGES ; $ mysql -h mynewserver -u testuser -p Enter password: I entered the password ERROR 1045 (28000): Access denied for user 'testuser'@'myoldserver' (using password: YES) On Sat, Oct 25, 2008 at 1:40 PM, Joe Auty [EMAIL PROTECTED] wrote: I'm also having this strange problem just as stated here, anybody find a fix for this yet? Again, using wildcards (%) for the Host don't work while the FQDN does from the exact same remote machine, the exact same username, etc. There is something about the wildcard ACL that is not working properly for this or any other user. I have proper forward and reverse DNS entries for this machine I'm connecting from. Any ideas why this might be and where I ought to look? Yes, I did FLUSH PRIVILEGES and I think I only have one username/host entry for this user. Adam Erik Giberti wrote: Did you FLUSH PRIVILEGES? I'd also check that the username and passwords are the same for each host entry, I've had problems if passwords were different for a shared username from different hosts. On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote: I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=1 -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- Joe Auty NetMusician: web publishing software for musicians http://www.netmusician.org [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Joe Auty NetMusician: web publishing software for musicians http://www.netmusician.org [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: % wildcard host permission not working
I think I see the typo. If that's an exact copy paste of your GRANT statement, then the problem is the spaces in the database designation: You have: GRANT ALL PRIVILEGES ON * . * TOetc But it should be: GRANT ALL PRIVILEGES ON *.* TOetc There shouldn't be any spaces in the *.* part. I hope this fixes it for you. CheersFish ~ Second Life addict On Sat, Oct 25, 2008 at 3:15 PM, Joe Auty [EMAIL PROTECTED] wrote: Fish Kungfu wrote: Would you mind posting what you tried? Did you put single quotes around the wildcard like '%' ? For example: GRANT ALL ON somedb.* TO 'someuser'@'%'; Also see http://dev.mysql.com/doc/refman/5.0/en/grant.html CheersFish Sure! On what I'll call mynewserver CREATE USER 'testuser'@'%' IDENTIFIED BY '***'; GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'%' IDENTIFIED BY '***' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ; FLUSH PRIVILEGES ; $ mysql -h mynewserver -u testuser -p Enter password: I entered the password ERROR 1045 (28000): Access denied for user 'testuser'@'myoldserver' (using password: YES) On Sat, Oct 25, 2008 at 1:40 PM, Joe Auty [EMAIL PROTECTED] wrote: I'm also having this strange problem just as stated here, anybody find a fix for this yet? Again, using wildcards (%) for the Host don't work while the FQDN does from the exact same remote machine, the exact same username, etc. There is something about the wildcard ACL that is not working properly for this or any other user. I have proper forward and reverse DNS entries for this machine I'm connecting from. Any ideas why this might be and where I ought to look? Yes, I did FLUSH PRIVILEGES and I think I only have one username/host entry for this user. Adam Erik Giberti wrote: Did you FLUSH PRIVILEGES? I'd also check that the username and passwords are the same for each host entry, I've had problems if passwords were different for a shared username from different hosts. On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote: I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=1 -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- Joe Auty NetMusician: web publishing software for musicians http://www.netmusician.org [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Joe Auty NetMusician: web publishing software for musicians http://www.netmusician.org [EMAIL PROTECTED]
Re: % wildcard host permission not working
I think I see the typo. If that's an exact copy paste of your GRANT statement, then the problem is the spaces in the database designation: You have: GRANT ALL PRIVILEGES ON * . * TOetc But it should be: GRANT ALL PRIVILEGES ON *.* TOetc There shouldn't be any spaces in the *.* part. I hope this fixes it for you. CheersFish ~ Second Life addict On Sat, Oct 25, 2008 at 3:15 PM, Joe Auty [EMAIL PROTECTED] wrote: Fish Kungfu wrote: Would you mind posting what you tried? Did you put single quotes around the wildcard like '%' ? For example: GRANT ALL ON somedb.* TO 'someuser'@'%'; Also see http://dev.mysql.com/doc/refman/5.0/en/grant.html CheersFish Sure! On what I'll call mynewserver CREATE USER 'testuser'@'%' IDENTIFIED BY '***'; GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'%' IDENTIFIED BY '***' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ; FLUSH PRIVILEGES ; $ mysql -h mynewserver -u testuser -p Enter password: I entered the password ERROR 1045 (28000): Access denied for user 'testuser'@'myoldserver' (using password: YES) On Sat, Oct 25, 2008 at 1:40 PM, Joe Auty [EMAIL PROTECTED] wrote: I'm also having this strange problem just as stated here, anybody find a fix for this yet? Again, using wildcards (%) for the Host don't work while the FQDN does from the exact same remote machine, the exact same username, etc. There is something about the wildcard ACL that is not working properly for this or any other user. I have proper forward and reverse DNS entries for this machine I'm connecting from. Any ideas why this might be and where I ought to look? Yes, I did FLUSH PRIVILEGES and I think I only have one username/host entry for this user. Adam Erik Giberti wrote: Did you FLUSH PRIVILEGES? I'd also check that the username and passwords are the same for each host entry, I've had problems if passwords were different for a shared username from different hosts. On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote: I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=1 -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- Joe Auty NetMusician: web publishing software for musicians http://www.netmusician.org [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Joe Auty NetMusician: web publishing software for musicians http://www.netmusician.org [EMAIL PROTECTED]
Re: % wildcard host permission not working
Hmmm, that is strange indeed. Hopefully one of the Level 70+ MySQL Wizards on the list can explain why this might happen. ~~Fish~~ On Sat, Oct 25, 2008 at 4:44 PM, Joe Auty [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Fish Kungfu wrote: | I think I see the typo. If that's an exact copy paste of your GRANT | statement, then the problem is the spaces in the database designation: | | You have: GRANT ALL PRIVILEGES ON * . * TOetc | But it should be: GRANT ALL PRIVILEGES ON *.* TOetc | | There shouldn't be any spaces in the *.* part. I hope this fixes it for | you. | I just setup the MySQL client on another machine, and was able to connect just fine... Very odd. So: - - local connections as that user: fine - - connections from mydomain.com as that user: broken - - connections from mydomain.com as that user where is a specific MySQL user permitting the FQDN of mydomain.com: fine - - connections from newserver.mydomain.com (the aforementioned machine I just setup): fine It's almost as if the local privileges data on mydomain.com is conflicting or something? There was no data at all on newserver.mydomain.com - it was a complete virgin install. | CheersFish | | ~ Second Life addict | | | On Sat, Oct 25, 2008 at 3:15 PM, Joe Auty [EMAIL PROTECTED] wrote: | | Fish Kungfu wrote: | | Would you mind posting what you tried? Did you put single quotes around | the | wildcard like '%' ? | |For example: GRANT ALL ON somedb.* TO 'someuser'@'%'; | | Also see http://dev.mysql.com/doc/refman/5.0/en/grant.html | | CheersFish | | | Sure! | | On what I'll call mynewserver | | CREATE USER 'testuser'@'%' IDENTIFIED BY '***'; | | GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'%' IDENTIFIED BY '***' WITH | GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 | MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ; | | | FLUSH PRIVILEGES ; | | | $ mysql -h mynewserver -u testuser -p | Enter password: I entered the password | ERROR 1045 (28000): Access denied for user 'testuser'@'myoldserver' (using | password: YES) | | | | | | | On Sat, Oct 25, 2008 at 1:40 PM, Joe Auty [EMAIL PROTECTED] wrote: | | I'm also having this strange problem just as stated here, anybody find a | fix for this yet? | | Again, using wildcards (%) for the Host don't work while the FQDN does | from | the exact same remote machine, the exact same username, etc. There is | something about the wildcard ACL that is not working properly for this or | any other user. I have proper forward and reverse DNS entries for this | machine I'm connecting from. | | Any ideas why this might be and where I ought to look? | | | Yes, I did FLUSH PRIVILEGES and I think I only have one username/host | | entry for this user. | | Adam | | | Erik Giberti wrote: | | Did you FLUSH PRIVILEGES? | I'd also check that the username and passwords are the same for each | host | entry, I've had problems if passwords were different for a shared | username | from different hosts. | | On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote: | | I set up a user and entered % for the host. I am not able to connect. | | However, if I change the host value to my FQDN it works fine. | Shouldn't the | wildcard allow me to connect from any host? | | Thanks, | Adam | | | -- | Adam Gerson | Assistant Director of Technology | Apple Certified System Administrator (ACSA) | Columbia Grammar and Prep School | phone. 212-749-6200 ex. 321 | fax. 212-428-6806 | [EMAIL PROTECTED] | http://www.cgps.org | Public key - subkeys.pgp.net | | | -- | MySQL General Mailing List | For list archives: http://lists.mysql.com/mysql | To unsubscribe:http://lists.mysql.com/mysql?unsub=1 | | | | -- | Adam Gerson | Assistant Director of Technology | Apple Certified System Administrator (ACSA) | Columbia Grammar and Prep School | phone. 212-749-6200 ex. 321 | fax. 212-428-6806 | [EMAIL PROTECTED] | http://www.cgps.org | Public key - subkeys.pgp.net | | | -- | Joe Auty | NetMusician: web publishing software for musicians | http://www.netmusician.org | [EMAIL PROTECTED] | | -- | MySQL General Mailing List | For list archives: http://lists.mysql.com/mysql | To unsubscribe: | http://lists.mysql.com/[EMAIL PROTECTED] | | | | -- | Joe Auty | NetMusician: web publishing software for musicians | http://www.netmusician.org | [EMAIL PROTECTED] | | - -- Joe Auty NetMusician: web publishing software for musicians http://www.netmusician.org [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (Darwin) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJA4UjCgdfeCwsL5ERArpbAJ9dS6SEc8WOc38fyh+xdcj5B14pjgCghUCm 1wK5q5CoIKk6A0eigyy5HzE= =Hum0 -END PGP SIGNATURE-
Re: create view not working
Aaron, why doesn't this work? the select works perfectly Define doesn't work? What is the error message? create view cost_report as SELECT c_name, d_manuf as Manufacturer, d_model as Model, count(1) as Number Enrolled, d_price as Monthly Price, count(1)*d_price as Cost FROM `b_devices` A left join b_device_types B on A.d_id = B.d_id left join b_prices C on A.d_id = C.d_id AND A.c_id = C.c_id left join b_company D on A.c_id = D.c_id group by A.d_id, A.c_id order by c_name Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create view not working
why doesn't this work? the select works perfectly create view cost_report as SELECT c_name, d_manuf as Manufacturer, d_model as Model, count(1) as Number Enrolled, d_price as Monthly Price, count(1)*d_price as Cost FROM `b_devices` A left join b_device_types B on A.d_id = B.d_id left join b_prices C on A.d_id = C.d_id AND A.c_id = C.c_id left join b_company D on A.c_id = D.c_id group by A.d_id, A.c_id order by c_name -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
you not ADD Binary, you need to remove BINARY ... ;-) Sorry, I misunderstood. convert the string to latin1 or utf8 o.s.s. LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a non-binary string did you tried? Well, I thought I had tried this, but with all the other things that I'd tried, I guess I had everything mixed up. I started with a clean routine, used Convert(... using UTF8), and it works perfectly now. That's what it was. Thanks for the help! Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function Still Not Working
-Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 1:40 PM To: Martijn Tonies; MySQL List Subject: Re: Function Still Not Working Any difference in default collation? Not sure what that is. I'm using a visual tool (EMS) to create my function, and it doesn't offer that option. I could update it using the command prompt, however. I may try that later. I think what he means is... in one instance of the function, the data is collated as latin-iso-blahblah, perhaps, and a different collation (one without case sensitivity, eh?) in the other table... As I don't deal with letters/characters outside of the 'standard' Latin-iso-asdfasdf collation, I'm afraid there's not much else I can explain using my limited knowledge. Hopefully, though, that helped to give you an idea of what he was driving at. ;) Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Function Still Not Working
Sorry for posting this again, but I got only one response last time, and I'm still having the problem. I spent HOURS the other day manually going through the data and Properizing these things by hand. I don't want to do that again if I can avoid it. If anyone has any clues on this one, I would appreciate it. The only difference in this and what I have now is that someone suggested changing it to Deterministic, which I did, and that didn't change the output. I also changed SQL SECURITY DEFINER to SQL SECURITY INVOKER, and that didn't make a difference either. I have the following function on two servers: CREATE FUNCTION `ProperCase`(cInput TEXT) RETURNS text NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN Declare cReturn Text; Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput FROM 2))); RETURN cReturn; END; It's a very simple function used to properize a string sent to it. When I do a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that is running 5.0.17-nt-log. On another server that I've got, running 5.0.51a-community-nt, this function returns Jesse as it should. The only difference that I can think of is the version. Is there a problem with the older version that would cause this function not to work properly? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Hey, Sorry for posting this again, but I got only one response last time, and I'm still having the problem. I spent HOURS the other day manually going through the data and Properizing these things by hand. I don't want to do that again if I can avoid it. If anyone has any clues on this one, I would appreciate it. The only difference in this and what I have now is that someone suggested changing it to Deterministic, which I did, and that didn't change the output. I also changed SQL SECURITY DEFINER to SQL SECURITY INVOKER, and that didn't make a difference either. I have the following function on two servers: CREATE FUNCTION `ProperCase`(cInput TEXT) RETURNS text NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN Declare cReturn Text; Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput FROM 2))); RETURN cReturn; END; It's a very simple function used to properize a string sent to it. When I do a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that is running 5.0.17-nt-log. On another server that I've got, running 5.0.51a-community-nt, this function returns Jesse as it should. The only difference that I can think of is the version. Is there a problem with the older version that would cause this function not to work properly? Any difference in default collation? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Jesse schrieb: Sorry for posting this again, but I got only one response last time, and I'm still having the problem. I spent HOURS the other day manually going through the data and Properizing these things by hand. I don't want to do that again if I can avoid it. If anyone has any clues on this one, I would appreciate it. The only difference in this and what I have now is that someone suggested changing it to Deterministic, which I did, and that didn't change the output. I also changed SQL SECURITY DEFINER to SQL SECURITY INVOKER, and that didn't make a difference either. I have the following function on two servers: CREATE FUNCTION `ProperCase`(cInput TEXT) RETURNS text NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN Declare cReturn Text; Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput FROM 2))); RETURN cReturn; END; It's a very simple function used to properize a string sent to it. When I do a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that is running 5.0.17-nt-log. On another server that I've got, running 5.0.51a-community-nt, this function returns Jesse as it should. does it work outside the function? did you tried SUBSTRING(cInput, 2)? did you tried with converting? from the manual: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a non-binary string: mysql SET @str = BINARY 'New York'; mysql SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1)); -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function Still Not Working
-Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 6:23 AM To: MySQL List Subject: Re: Function Still Not Working Hey, Sorry for posting this again, but I got only one response last time, and I'm still having the problem. I spent HOURS the other day manually going through the data and Properizing these things by hand. I don't want to do that again if I can avoid it. If anyone has any clues on this one, I would appreciate it. ---8--- snip It's a very simple function used to properize a string sent to it. When I do a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that is running 5.0.17-nt-log. On another server that I've got, running 5.0.51a-community-nt, this function returns Jesse as it should. The only difference that I can think of is the version. Is there a problem with the older version that would cause this function not to work properly? Any difference in default collation? I am curious about that, as well. It brings to mind a discussion that happened on this list last week (I believe) about case sensitive/insensitive use of LIKE. I believe the synopsis was that tables are either created as case-insensitive, or the search needs to be specified as case sensitive (with BINARY). Could this be a similar issue, perhaps? One table is specifically case-insensitive with regard to the function, and the other is not? Just spit-balling... Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
I am curious about that, as well. It brings to mind a discussion that happened on this list last week (I believe) about case sensitive/insensitive use of LIKE. I believe the synopsis was that tables are either created as case-insensitive, or the search needs to be specified as case sensitive (with BINARY). Could this be a similar issue, perhaps? One table is specifically case-insensitive with regard to the function, and the other is not? In this particular case, we're not dealing with any tables. I have also tried adding BINARY to the mix as well, and it didn't make any difference. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Any difference in default collation? Not sure what that is. I'm using a visual tool (EMS) to create my function, and it doesn't offer that option. I could update it using the command prompt, however. I may try that later. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
does it work outside the function? Yes, If I run: select CONCAT(UPPER(SUBSTRING('JESSE',1,1)),LOWER(SUBSTRING('JESSE',2))); replacing cInput with 'JESSE', it returns Jesse as it should. did you tried SUBSTRING(cInput, 2)? Tried replacing SUBSTRING(cInput FROM 2) with SUBSTRING(cInput, 2) and it didn't make any difference. did you tried with converting? I have had issues with this in other areas before, but didn't think about it this time. However, I tred CONVERT with UTF8 and latin1 as you suggested. LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a non-binary string: mysql SET @str = BINARY 'New York'; mysql SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1)); I converted the function over to use a variable, and got the same results. Here's the new function: CREATE FUNCTION `ProperCase`(cInput TEXT) RETURNS text DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER COMMENT '' BEGIN Declare str Text; Declare cReturn Text; Set @str=BINARY cInput; Set @cReturn = CONCAT(UPPER(SUBSTRING(@str,1,1)),LOWER(SUBSTRING(@str,2))); RETURN @cReturn; END; Still doesn't work. This is driving me NUTz 8-p Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Function Not Working
I have the following function on two servers: CREATE FUNCTION `ProperCase`(cInput TEXT) RETURNS text NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN Declare cReturn Text; Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput FROM 2))); RETURN cReturn; END; It's a very simple function used to properize a string sent to it. When I do a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that is running 5.0.17-nt-log. On another server that I've got, running 5.0.51a-community-nt, this function returns Jesse as it should. The only difference that I can think of is the version. Is there a problem with the older version that would cause this function not to work properly? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: % wildcard host permission not working
Yes, I did FLUSH PRIVILEGES and I think I only have one username/host entry for this user. Adam Erik Giberti wrote: Did you FLUSH PRIVILEGES? I'd also check that the username and passwords are the same for each host entry, I've had problems if passwords were different for a shared username from different hosts. On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote: I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
% wildcard host permission not working
I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: % wildcard host permission not working
Did you FLUSH PRIVILEGES? I'd also check that the username and passwords are the same for each host entry, I've had problems if passwords were different for a shared username from different hosts. On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote: I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- 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]