Re: ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
On Wed, 9 Dec 2009 16:39:33 Jeetendra Ranjan wrote: Hi, I just would like to know which version of MySQL support the creation of database triggers with the same action and event for one table. I'm not sure there is any plan to implement support for multiple triggers with the same action and timing. In your case, the solution is simply to combine the two: CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); INSERT INTO DELETED_AUDIT_REPOSITORY(AUDIT_ENTRY_ID,AUDIT_ATTRIBUTE_ID,AFFECTED_CONSUMER_ID,AFFECTED_INTEREST_ID,ACTION_ID,OLD_VALUE_ID,OLD_VALUE,NEW_VALUE_ID,NEW_VALUE,IS_ACTIVE,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.AUDIT_ENTRY_ID,old.AUDIT_ATTRIBUTE_ID,old.AFFECTED_CONSUMER_ID,old.AFFECTED_INTEREST_ID,old.ACTION_ID,old.OLD_VALUE_ID,old.OLD_VALUE,old.NEW_VALUE_ID,old.NEW_VALUE,old.IS_ACTIVE,old.LAST_MOD_TIME,old.LAST_MOD_BY ); END// - Jesper Because if i create the the triggers as below i am getting error. First trigger (Succesfull) ++ CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CON SUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GE NDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP ,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSU MER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_O F_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFI LE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); END Second trigger (generating error) +++ CREATE TRIGGER AUDIT_REPOSITORY_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_AUDIT_REPOSITORY(AUDIT_ENTRY_ID,AUDIT_ATTRIBUTE_ID,AFFECTED_CONSUME R_ID,AFFECTED_INTEREST_ID,ACTION_ID,OLD_VALUE_ID,OLD_VALUE,NEW_VALUE_ID,NEW_ VALUE,IS_ACTIVE,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.AUDIT_ENTRY_ID,old.AUDIT_ATTRIBUTE_ID,old.AFFECTED_CONSUMER_ID,o ld.AFFECTED_INTEREST_ID,old.ACTION_ID,old.OLD_VALUE_ID,old.OLD_VALUE,old.NEW _VALUE_ID,old.NEW_VALUE,old.IS_ACTIVE,old.LAST_MOD_TIME,old.LAST_MOD_BY );END - // ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' Or any other alternate to do the same ? Thanks Regards Jeetendra Ranjan -- Jesper Wisborg Krogh Team Lead Noggin IT • Level 8, 28 Foveaux St, Surry Hills NSW Australia 2010 T: +61 2 9219 8800 • F: +61 2 9280 4669 • E: jes...@noggin.com.au http://www.noggin.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Force index command in sql query
On 09/12/2009, at 5:10 PM, Jeetendra Ranjan wrote: Hi, After analysing slow query log i found that some queries are not using index and so i used the force index command in query and test it and now it starts using index properly.Accordingly i implemented the same query with force index in my application code and regeneratet the slow query log. Now i found that the same queries having force index clause are againg not using index and surprisingly its starting using index without any force index clause. Please suggest how it happened and should i continue with the force index command in that query or remove the force index clause from those queries ? One of the things to be aware of is that force index only forces the index if the optimizer chooses to use an index. That is, if the optimizer decides it is better to do a table scan or the join order changes so the index cannot be used, then it will not use it. It might be worth trying to do an EXPLAIN EXTENDED ... followed by SHOW WARNINGS to see how the optimizer has reorganized the query. Hope that helps. - Jesper Thanks Regards Jeetendra Ranjan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL variables
On 09/12/2009, at 6:56 PM, machiel.richards wrote: Good day guys (and girls if any) I am constantly in a position where variables on a production mysql database needs to be changed. The database runs a 24/7 system and thus to reboot is not preffered and should be the absolute last resort. How can I set variables to be effective immediately? ( I am still a junior in mysql dba and still learning) If you take a look at the manual in the section about server options and variables (http://dev.mysql.com/doc/refman/5.0/en/mysqld-option- tables.html in MySQL 5.0 or http://dev.mysql.com/doc/refman/5.1/en/ mysqld-option-tables.html in MySQL 5.1), then you can see which settings can be changed dynamically. To change a variable dynamically you need to have super privilege, and then set the variable as e.g. SET GLOBAL system_var_name = ... If I set these will it still be effective should the database be restarted somewhere in the future? The change above will not persist when the database is restarted. In order to ensure that, you will have to update the configuration file as well. - Jesper Thanks in advance for your help. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Return row even if nothing found
On Wed, 16 Dec 2009 07:39:09 Cantwell, Bryan wrote: I have a situation where I need to always get a row returned even if no match is in the table (only 1 or many rows are acceptable). I can use: select a, b, c from mytable where a = 'yarp'; and might get 20 rows if there are matches, but I at least need 1 default row back... using : select ifnull(a,'NOTHING') as a, ifnull(b, 'NOTHING') b, ifnull(c, 'NOTHING') c from mytable where a = 'yarp'; just returns nothing... Anything I can add in here to have a recordset of at least (nothing, nothing, nothing) ? You can do something like: SELECT mytable.* FROM (SELECT 1) AS dummy LEFT JOIN mytable ON id = 'something that does not exists'; It's not pretty, but it might do the trick for you. - Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to get the name of primary key ?
On 01/02/2010, at 7:33 PM, 曹凯 wrote: Hi all, if we just know the table name but don't know the name of primary key, is there any variables or constants could instead of the PK? for example: there is a table game_log, and now I have the last inserted_id but don't know what its primary_id is, how can I SELECT * FROM game_log WHERE this_table's_PK = last_inserted_id? You can get the column name from the information schema, however that can't be used directly in another query in the way you've done in your example. E.g. game SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'game' AND TABLE_NAME = 'game_log' AND CONSTRAINT_NAME = 'PRIMARY'; +-+ | COLUMN_NAME | +-+ | GameLogID | +-+ 1 row in set (0.00 sec) where it is assumed the database name is game. Hope that helps. Jesper
Re: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
Try to run SHOW ENGINE INNODB STATUS; Near the top there will be some information on the latest deadlock. That might help you to understand what is deadlocking. Sometimes changing the query or changing the indexes can remove the condition that causes the deadlock. I don't know whether you have triggers on any of your tables? If so that's one place to watch for as well as the deadlock will show up as it is on the original query even if it is a trigger causing it. Jesper On 02/02/2010, at 6:06 AM, Michael Dykman wrote: The SELECT FOR UPDATE is supposed to lock those rows selected.. an operation in another connection attempting to read or modify those rows gets an error on the lock if it is still in place. That is that SELECT FOR UPDATE is supposed to do. If that is not the behaviour you want, then why are you using the lock? - michael dykman On Mon, Feb 1, 2010 at 12:29 PM, D. Dante Lorenso da...@lorenso.com wrote: Michael Dykman wrote: The query is probably fine.. that is just the lock doing it's job. Take that advice literally.. when you fail with that class of exception, delay a milli-second or two and retry. For a large PHP site I designed, we had that behaviour built-in: up to three attempts waits 5, then 10 ms between trys. In spite of 1M+ user/day we rarely made it to the third attempt. Sounds like the answer is that's just the way MySQL is. I don't usually like those kinds of answers. I've written similar queries in another DB and never got these types of errors. Perhaps there is a better way to create a queue system that avoids this problem entirely? I feel like if MySQL is throwing out this wanring to me, that I should be doing to correct it. I have a queue with several states in it: state1 --- processing1 -- state2 --- processing2 --- state3 I want to find a record that is in state1 and reserve the right to process it. After it is done being processed, the code will set it's state to state2 which allows the next application to pick it up and work on it. I am actually using PHP/MySQL and this problem sounds like a job for a message queue. So, in essence, my solution is like a message queue built using MySQL tables to store and manage the queue. Has this problem already been solved in a way I can just leverage the existing solution? ... er, without the deadlock issue. Are you saying I should just ignore the message about deadlock and let the app run as if the message never occurred (since there's not a problem with seeing that message)? -- Dante - michael dykman On Mon, Feb 1, 2010 at 9:33 AM, Dante Lorenso da...@lorenso.com wrote: All, I am trying to create an atomic operation in MySQL that will manage a queue. I want to lock an item from a table for exclusive access by one of my processing threads. I do this by inserting the unique ID of the record I want to reserve into my cli_lock table. The following query is what I am using to lock a record in my queue: INSERT INTO cli_lock (lock_id, object_id, lock_type, expire_dt) SELECT $LOCK_ID, q.queue_id, 'parse', DATE_ADD(NOW(), INTERVAL 1 HOUR) FROM queue q LEFT JOIN cli_lock l ON l.object_id = q.queue_id AND l.lock_type = 'parse' WHERE l.object_id IS NULL AND q.status = 'parse' ORDER BY q.file_size ASC, q.created ASC, q.queue_id ASC LIMIT 1 However, as I execute this query several times each minute from different applications, I frequently get these messages: DB ERROR: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction Am I writing my query wrong or expecting behavior that MySQL doesn't support? -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? unsub=jes...@noggin.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I NEED HELP ON 'SOURCE(\.) MYSQL
How did you type the source command? Did you remember not to quote the file name? E.g. mysql SOURCE schema.sql is the correct way. Jesper On Tue, 2 Feb 2010 10:38:08 kebede teferi wrote: Hi, I'm very new to this and I need help. What I want to do is to execute a source command statement from cmd to create tables and populate them on a data base that I created in Mysql. Here is what I did at CMD: *I changed the directory from c:\ to mysql *At mysql prompt i used -u root -p that prompted me to give my password. *I put in my password and I was allowed to access the mysql server. *I used the 'the use --database' command and I can see the database I created is active and waiting to be worked on. *then to run the script which is on a note pad with the name create and file type: sql I typed the following at the mysql prompt: *source create.sql; I get the error message '2' and '22' whichever way I tried the source command. I appreciate your help Kebede Teferi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: where is the best material handler and cursor
On 06/02/2010, at 5:31 PM, MuraliKrishna wrote: Hi I am in need to know about handlers and cursors.. if any site giving detailed explanation and examples on these. thanks in advance Cursors: http://dev.mysql.com/doc/refman/5.1/en/cursors.html Handlers: http://dev.mysql.com/doc/refman/5.1/en/handler.html Best regards, Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ERROR 1442 (HY000) when delete inside trigger statement
--- Original Message --- From: viraj kali...@gmail.com To: mysql@lists.mysql.com Sent: 19/2/10, 05:48:41 Subject: ERROR 1442 (HY000) when delete inside trigger statement issue: ERROR 1442 (HY000): Can't update table 'T1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. could somebody please confirm this is not possible!!! so i can think about some other workaround :) -- That is correct. There is as far as I know no way in a MySQL trigger to neither to do operations on the table the trigger belongs to (obviously except the row that the trigger is operating on through the NEW variables) nor reject an insert, update, or delete. It is of course possible to do a workaround in a stored procedure and use permissions to ensure that the normal users cannot update the table directly. I don't know whether that will be an acceptable solution in your case though. Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: set max_allowed_packet
Jesper Wisborg Krogh Team Leader • Certified MySQL 5.0 Developer DBA Noggin IT • Level 8, 28 Foveaux St, Surry Hills NSW Australia 2010 T: +61 2 9219 8800 • F: +61 2 9280 4669 • E: jes...@noggin.com.au http://www.noggin.com.au On 21/02/2010, at 4:46 AM, Paul DuBois wrote: On Feb 20, 2010, at 11:22 AM, Vikram A wrote: Hi experts, When I try to set the packet size to some other value, it is not getting updated. show variables; set max_allowed_packet = 2097152; set global max_allowed_packet = 2097152; it resulting, Query is ok, 0 rows afected (0.00 sec) That's okay. What does this query return: mysql select @@global.max_allowed_packet; +-+ | @@global.max_allowed_packet | +-+ | 2097152 | +-+ 1 row in set (0.06 sec) Also remember, when you change a global variable, the change won't effect existing connections, so you will need to log out and back in to see the change. Setting the session variable as well will solve that for the existing connection, but not for other long lived connections: With mysql1 and mysql2 being two different connections: mysql1 select @@session.max_allowed_packet, @@global.max_allowed_packet; +--+-+ | @@session.max_allowed_packet | @@global.max_allowed_packet | +--+-+ | 1048576 | 1048576 | +--+-+ 1 row in set (0.00 sec) mysql2 select @@session.max_allowed_packet, @@global.max_allowed_packet; +--+-+ | @@session.max_allowed_packet | @@global.max_allowed_packet | +--+-+ | 1048576 | 1048576 | +--+-+ 1 row in set (0.00 sec) mysql1 set global max_allowed_packet = 2097152; Query OK, 0 rows affected (0.00 sec) mysql1 select @@session.max_allowed_packet, @@global.max_allowed_packet; +--+-+ | @@session.max_allowed_packet | @@global.max_allowed_packet | +--+-+ | 1048576 | 2097152 | +--+-+ 1 row in set (0.00 sec) mysql2 select @@session.max_allowed_packet, @@global.max_allowed_packet; +--+-+ | @@session.max_allowed_packet | @@global.max_allowed_packet | +--+-+ | 1048576 | 2097152 | +--+-+ 1 row in set (0.00 sec) Jesper Can you suggest me how set this value to higher one. By default it is 1 MB. Thank you VIKRAM A -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? unsub=jes...@noggin.com.au
Re: Dumping table contents to stdout in tab-separated value format
On 21/02/2010, at 2:03 PM, Yang Zhang wrote: Hi, I'm interested in piping out the contents of a mysql table to stdout in tab-separated value format, but: - using 'select * into outfile' can't write to stdout. - mysqldump --fields-... requires --tab, which requires an output file path. - I also tried mkfifo /tmp/pipe and select * into outfile '/tmp/pipe', but mysql complains about the file existing already. Is there any other quick way to do this without having to write a client app? Thanks in advance. You can execute a query from the command line and use the -B (or -- batch) option. mysql mysql -B -e SELECT * FROM time_zone LIMIT 5 Time_zone_idUse_leap_seconds 1 N 2 N 3 N 4 N 5 N
Re: can't dump a database with views: stack smashing detected ***: mysqldump terminated
On 22/02/2010, at 9:20 PM, Tomasz Chmielewski wrote: When I try to dump a database which contains views, mysqldump crashes: # mysqldump -u root --opt --password=secret database dump.sql *** stack smashing detected ***: mysqldump terminated Aborted # I use MySQL 5.0.89-0.glibc23 binaries from MySQL website. Is it a known problem? Are there any solutions to it? I have seen the exact same issue on some (but not all) installations if the view has been declared wrong, e.g. referencing a non-existing column. A SHOW CREATE VIEW ... has been enough in those cases to cause the crash. -- Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Remove - Foreign key constraint in innodb
An example how to delete a foreign key from an InnoDB table: test CREATE TABLE table_1 (id int unsigned NOT NULL auto_increment PRIMARY KEY) ENGINE=InnoDB; Query OK, 0 rows affected (0.56 sec) test CREATE TABLE table_2 (table1_id int unsigned NOT NULL, FOREIGN KEY (table1_id) REFERENCES table_1 (id)) ENGINE=InnoDB; Query OK, 0 rows affected (0.11 sec) test SHOW CREATE TABLE table_2\G *** 1. row *** Table: table_2 Create Table: CREATE TABLE `table_2` ( `table1_id` int(10) unsigned NOT NULL, KEY `table1_id` (`table1_id`), CONSTRAINT `table_2_ibfk_1` FOREIGN KEY (`table1_id`) REFERENCES `table_1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.47 sec) test ALTER TABLE table_2 DROP FOREIGN KEY table_2_ibfk_1; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 test SHOW CREATE TABLE table_2\G *** 1. row *** Table: table_2 Create Table: CREATE TABLE `table_2` ( `table1_id` int(10) unsigned NOT NULL, KEY `table1_id` (`table1_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) On 25/02/2010, at 11:33 PM, Jim Lyons wrote: Yes - you can drop a foreign key constraint, use the 'alter table ... drop foreign key ...' command. If you get an error message, post the error message. On Thu, Feb 25, 2010 at 6:03 AM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I tried to remove foreign key constraint in innodb table. I tried with different ways; but i am unable to drop the constraints. http://lists.mysql.com/mysql/113053 It says that, droping the foreign key constraint it is not possible in innodb engine. Is it so? or any other possibilities? I am using mysql 5.1.32 Please, Can any one you help me? Thank you VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.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: Allowing triggers stored procedures on MySQL
On 07/03/2010, at 3:30 AM, Brent Clark wrote: Hi everyone, Currently we have a policy that prohibit our customers from creating stored procedures and triggers on their DB's which I imagine must be driving them up the walls. It's like having a car with a boot but you are not able to use it. :) Are there any reasons why we would'nt want customers to make use of these built in features and what other means are available. My reading showed that you need the create routine privilege and you *may* require the super privilege if you have binary logging enabled (and then that only becomes a potential issue if you are actually replaying those logs (ie. either for replication or for media recovery). I think I was reading the MySQL 5.1 manual - so maybe this is different with 5.0? In MySQL 5.0 (I get the impression that's the version you are running) it requires SUPER to create triggers, however in 5.1 a new TRIGGER privilege was introduced for that. The requirement on SUPER for binary logging applies is the log_bin_trust_function_creators is not set to 1. The reason for this is to avoid random users creating non-deterministic procedures that then replicate to a slave and causes the slave and master to get out of sync. If binary logging is not enabled, SUPER is never required in order to create a stored procedure. See more in http://dev.mysql.com/ doc/refman/5.0/en/stored-programs-logging.html for MySQL 5.0 or http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html for MySQL 5.1. Best regards, Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Disable innodb status info in err log
On 17/03/2010, at 9:10 PM, Anand kumar wrote: you are right sir , the variables innodb_status_file will write the show innodb status information on to the file innodb_status.PID ... however for me the innodb status is logging into the default error log file... i dont have any clue on how to stop it from writing... Any help would be appreciated Do you happen to have a table called innodb_monitor? If that is present, then that will cause the result of SHOW ENGINE INNODB STATUS to be outputted every 15 seconds. See also http://dev.mysql.com/doc/ refman/5.0/en/innodb-monitors.html or http://dev.mysql.com/doc/refman/ 5.1/en/innodb-monitors.html Cheers, Jesper
Re: upgrade from version 5.0.45
Be aware that if it is an unpatched version of 5.0.77, then there is a bug related to name_const (http://bugs.mysql.com/bug.php?id=42014) that can cause serious problems (infinite server crashes if it happens in a replication thread). Redhat/CentOS have applied the patch, but other sources might still have that bug. Jesper On 05/04/2010, at 2:29 PM, Walter Heck - OlinData.com wrote: Depending on the seriousness of your environment you can read the changelogs and upgrade if you don't see any showstoppers. I have hardly ever seen any problems with minor version upgrades of mysql. Of course what Rob says is true, and it is a good idea to test things out in a test environment first. But I know many environment where it is okay to just run the upgrade, as long as it is a minor version upgrade. I guess it depends on the type of production environment you are running in. be careful though! Walter On Mon, Apr 5, 2010 at 11:17, Rob Wultsch wult...@gmail.com wrote: On Tue, Mar 30, 2010 at 6:36 AM, Marco Baiguera marco.baigu...@gmail.com wrote: Hello everyone, i am quite new to mysql and i recently begin to work with a company who is using mysql 5.0.45 in production. i think this version is too old and would like to upgrade to the most recent 5.0.xx my os is CentOS release 5.3. is it safe to simply use yum upgrade mysql ? are there any important differences i should be aware of between 5.0.45 and 5.0.77 ? any diffferences in password encoding etc. ? the db is properly backed up and replicated on two 5.0.77 slaves. thank you Marco I would not simply upgrade. I would upgrade the test environment first and have the development team sign off that there were no bad effects caused by the upgrade. The first version of 5.0 that I think is particularly useable and not buggy is 5.0.67. I suggest that this is worth the upgrade. In theory there are not significant differences between 5.0 versions after GA other that bug fixes. I *do not* trust this. -- Rob Wultsch wult...@gmail.com -- 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=jes...@noggin.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CLI can't read data from table
On Thu, 15 Apr 2010 09:31:04 Mike Diehl wrote: I just created a new table called lines. I can use Open Office to read the records in it just fine. However, when I type this command at the cli, I get an error: select * from lines; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lines' at line 1 Lines is a reserved keyword (e.g. like in LINES TERMINATED BY), so it must be quoted: test use test; Database changed test CREATE TABLE `lines` (id int unsigned NOT NULL PRIMARY KEY) ENGINE=InnoDB; Query OK, 0 rows affected (0.20 sec) test SELECT * FROM lines; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lines' at line 1 test SELECT * FROM `lines`; Empty set (0.01 sec) Best regards, Jesper
Re: How to compute the min and max of two numbers?
On Tue, 27 Apr 2010 09:08:29 Peng Yu wrote: I don't see there is a function to compute min or max of two numbers in a row. Essentially I want to select all the rows where the minimum of column1 and column2 are bigger than the maximum of col3 and col4 (The following code won't work as I expected, if I am correct). select * from a_table where min(col1, col2) = max(col3,col4) I think what you are looking for is GREATEST and LEAST. See also http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_least and http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_greatest Best regards, Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: order by numeric value
On Wed, 28 Apr 2010 08:53:57 Keith Clark wrote:
But I'd prefer not to see the extra sorting field.
You don't need to select a field in order to be able to order by it.
So
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0),2))
as Balance
from sales_journal_entries
left join sales_journal
on sales_journal.journalID=sales_journal_entries.journalID
left join chart_of_accounts
on chart_of_accounts.accountID=sales_journal_entries.accountID
where sales_journal.date '2008-12-31'
and sales_journal.date '2010-01-01'
group by sales_journal_entries.accountID
order by
coalesce(sum(sales_journal_entries.credit),0)-coalesce(sum(sales_journal_entries.debit),0)
asc;
should do the trick.
Jesper
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
slave-net-timeout
Hi, According to the manual, the slave-net-timeout specifies how long time to wait for data from the master before considering the connection dead. From my experience this actually means that the slave will reconnect if it hasn't received a full event in that period of time. E.g. setting slave_net_timeout to 180 seconds, then an event that takes 240 seconds to transfer will never make it through. The replication will make it 75% through and then start all over. Does anyone know whether that is a bug in the implementation, or whether it is the documentation that could be more clear? Thanks, Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
Hi On 16/10/2010, at 1:47 AM, Suresh Kuna wrote: Hey Daevid, As this time zone table won't change once it is set up. Do a copy of the table data into another database and give grants to it. Copy the data is not a good solution. First of all, time zone data does change. Secondly if you need to use functions such as CONVERT_TZ () I believe you need access to the time zone tables in the mysql database. On Fri, Oct 15, 2010 at 7:57 PM, Johnny Withers joh...@pixelated.netwrote: I think this is one of those times you would update the mysql.user table directly, then flush privileges. You can grant access to the time zone tables just as you would do to any other table. GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; GRANT SELECT ON `mysql`.`time_zone_name` TO '%'@'%'; GRANT SELECT ON `mysql`.`time_zone_name` TO ''@'%'; GRANT SELECT ON `mysql`.`time_zone_name` TO ''; GRANT SELECT ON `mysql`.`time_zone_name`; (this one fails) As mentioned above, granting access to the time zone tables works exactly as it does for all other tables, so. e.g. granting SELECT to '%' will not allow everybody to do a SELECT on the table, but rather allow users logging in as the '%'@'%' user to select from the mysql.time_zone_name table. If the users used in the above GRANT statements don't exist, they will also end up being created. This means that you suddenly might have opened access to the database for a user called '%' from everywhere (although they only can select from the time_zone_name table). Note that the new user can login without using a password. (none) SELECT User, Host FROM mysql.user; +--+---+ | User | Host | +--+---+ | root | localhost | | testuser | localhost | +--+---+ 2 rows in set (0.37 sec) (none) GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; Query OK, 0 rows affected (0.18 sec) (none) SELECT User, Host FROM mysql.user; +--+---+ | User | Host | +--+---+ | %| % | | root | localhost | | testuser | localhost | +--+---+ 3 rows in set (0.00 sec) $ mysql -u % -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. ... Jesper
Re: How do I GRANT SELECT to mysql.time_zone_name for ANYONE?!
Hi, On 16/10/2010, at 8:50 AM, Daevid Vincent wrote: Thanks for the reply Jesper, but either there isn't a solution in your response, or I'm missing it? What I mean is that you have to explicitly give the grant to each user that should be allowed to query the table. You can't run one grant that automatically will apply to all users. So if you have three users use...@localhost, use...@192.168.1.1, and use...@localhost you have to run: GRANT SELECT ON `mysql`.`time_zone_name` TO 'user_a'@localhost; GRANT SELECT ON `mysql`.`time_zone_name` TO 'user_a'@192.168.1.1; GRANT SELECT ON `mysql`.`time_zone_name` TO 'user_b'@localhost; Any user can get into mysql, it's what they can do after that's the interesting part. Sorry, I'm not sure what you mean. Unless a username and host combination matches a record in the mysql.user table, then the user cannot log into the server. $ mysql -u random_user -p Enter password: ERROR 1045 (28000): Access denied for user 'random_user'@'localhost' (using password: NO) $ mysql -u random_user -p Enter password: ERROR 1045 (28000): Access denied for user 'random_user'@'localhost' (using password: YES) I used your GRANT example above and get this... develo...@mypse:~$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2275 Server version: 5.0.51a-3ubuntu5.7 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. (develo...@localhost) [(none)] SELECT * FROM mysql.time_zone_name; ++--+ | Name | Time_zone_id | ++--+ | Africa/Abidjan |1 | | Africa/Accra |2 | | Africa/Addis_Ababa |3 | | Africa/Algiers |4 | ... But then when I try an existing user that I use for all my PHP/DB connections: develo...@mypse:~$ mysql -uOMT_Master -pSOMEPASS -hmypse -P3306 agis_core (omt_mas...@mypse) [agis_core] SELECT * FROM mysql.time_zone_name; ERROR 1142 (42000): SELECT command denied to user 'OMT_Master'@'mydomain.com' for table 'time_zone_name' That is because the GRANT SELECT ON `mysql`.`time_zone_name` TO '%'; statement only gives the privilege to query the time_zone_name table to users who use the '%'@'%' account when logging in. Here's the current user's I have in my VM so far: SELECT `User`, `Host` FROM mysql.user; User Host -- % % % OMT_Master% OMT_Web % View_ReadOnly % developer % diagnostics % diagnostics 10.10.10.% root 127.0.0.1 localhost debian-sys-maint localhost root localhost I will recommend you to drop all the users that can log in from arbitrary hosts or with arbitrary usernames. E.g. the diagnost...@10.10.10.% account is a much better way to create a user rather than the diagnost...@% account. The latter will allow the diagnostics user to login from anywhere, whereas diagnost...@10.10.10.% restricts the login to a small subnet. Hope that helps. Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Access to MySQL
On 17/12/2010, at 9:02 AM, Jerry Schwartz wrote: I have to move the back-end of an Access application to MySQL, and I’ve run into one issue that I haven’t been able to solve yet. The Access database stores dates as text in a “/mm/dd” format. The problem is that the default value is a formula that generates the current date, formatted as text. In Access, it looks like '=Format$(Now(),\/mm/dd\)' This construct is used throughout the table definitions. Is there any alternative to setting the default to something else (NULL, for example) and moving the “default” into the application code? That would be a significant PITA. If a 32-bit date range is enough, then you can use the timestamp data type. That supports having the current time as the default value. See also http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Regards, Jesper
Re: CURRENT insert ID
Hi,
On 22/01/2011, at 11:27 AM, Donovan Brooke wrote:
Just an idear..
Don't auto_increment the main table.. create a unique Id table,
auto_increment that, and grab that value first for use with both fields in
your main table.
This can be wrapped into a trigger, so the main table functions as usual:
CREATE TABLE _sequence (
Name varchar(20) NOT NULL PRIMARY KEY,
Value INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;
CREATE TABLE dupkey (
id INT UNSIGNED NOT NULL DEFAULT 0 PRIMARY KEY,
DupKey INT UNSIGNED NOT NULL DEFAULT 0,
Value VARCHAR(20) NOT NULL DEFAULT ''
) ENGINE=InnoDB;
INSERT INTO _sequence
VALUES ('dupkey', 0);
DELIMITER //
CREATE TRIGGER befins_dupkey BEFORE INSERT ON dupkey FOR EACH ROW
BEGIN
DECLARE v_id INT UNSIGNED;
UPDATE _sequence SET Value = (LAST_INSERT_ID(Value+1)) where name = 'dupkey';
SET NEW.id := LAST_INSERT_ID(),
NEW.DupKey := LAST_INSERT_ID();
END//
DELIMITER ;
INSERT INTO dupkey (Value)
VALUES ('test 1'), ('test 2');
SELECT * FROM dupkey;
++++
| id | DupKey | Value |
++++
| 1 | 1 | test 1 |
| 2 | 2 | test 2 |
++++
2 rows in set (0.00 sec)
Cheers,
Jesper
Donovan
--
D Brooke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@wisborg.dk
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
Hi Frank, On 20/06/2013 05:00, Franck Dernoncourt wrote: Hi all, A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space shortage while deleting some attributes in a table in the `logs` database and adding an index. `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but when trying to `ALTER` the table that was being changed during the crash MySQL complains about the existence of the table `logs/#sql-ib203`: It's a bit of a workaround, but you should be able to get rid of the file using the steps below. I'm using an example where I killed mysqld while it was dropping the to_date column from the salaries table in the employees sample database: mysql SHOW CREATE TABLE salaries\G *** 1. row *** Table: salaries Create Table: CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`), CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql ALTER TABLE salaries DROP COLUMN to_date; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql$ ls -1 employees/#* employees/#sql-36ab_2.frm employees/#sql-ib30.ibd 1. Create a temporary table with the same structure as the salaries table would have looked after the ALTER that failed: mysql CREATE TABLE tmp LIKE salaries; ALTER TABLE tmp DROP COLUMN to_date; 2. Shutdown MySQL. 3. Copy the .frm file from the tmp table to have the same name as the #sql-*.ibd file: mysql$ cp employees/tmp.frm employees/#sql-ib30.frm 4. Start MySQL again. 5. Drop the #sql-ib30.frm table: mysql DROP TABLE `#mysql50##sql-ib30`; Query OK, 0 rows affected (0.01 sec) 6. Do the same for the #sql*.frm file (it'll get removed even though you get an error): mysql DROP TABLE `#mysql50##sql-36ab_2`; ERROR 1051 (42S02): Unknown table 'employees.#mysql50##sql-36ab_2' I know it's not very elegant, but should work. The #mysql50# prefix tells MySQL to not encode the table name when mapping to the file system (https://dev.mysql.com/doc/refman/5.6/en/identifier-mapping.html). Best regards, Jesper Krogh MySQL Support
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: MySQL version 3.23 to 5.x features
Hi Naga, On 21/08/2013 23:43, shawn green wrote: Hello Naga, On 8/21/2013 6:45 AM, Nagaraj S wrote: Hello, Can anyone share the features/comparison from MySQL version 3.23 to 5.x in single document? I can get from Google, however I have to navigate different pages/sites, if it is in single document that will be useful to see the overview of mysql features While not exactly just one single page for all changes, there is a single page of the fine manual within each new major version that describes the big features that are new or changed within that version. Just to add one reference to Shawn's list, there is also a MySQL Server Version Reference at https://dev.mysql.com/doc/mysqld-version-reference/en/index.html. It is lower level such as which options and functions are available in each major version. However it does not start out before version 5.0, so will not help you over the 3.23 - 4.0 - 5.0 hurdle. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql 5.1 union with group by for results
Hi Machiel, On 8/11/2013 20:04, Machiel Richards wrote: Good day all I am hoping someone can assist me in the following. One of our servers were running mysql 5.0 still and as part of a phased upgrade route we have upgraded to version 5.1. However since the upgrade, the query below gives us an error stating that the syntax is incorrect and I simply cant seem to find out what is actually wrong as all tests and changes have been giving us the same. I have tried many suggestions from the net but to no avail. The query is as follows: Using a shorter but equivalent query, you have: (SELECT t.id, t.name, SUM(val) FROM t1 t) UNION (SELECT t.id, t.name, SUM(val) FROM t2 t) GROUP BY t.id, t.name; That does not work in 5.0 either (at least in 5.0.96): ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY t.id, t.name' at line 4 The issue is that you are trying grouping the entire UNION result, but at that point there is no SELECT any longer - there is just the result set. You are also referencing tables that exists inside each of the SELECT statements, but at the time the GROUP BY is reached, there are no tables. Note that as written the two SELECT parts will also give non-deterministic results as you have an aggregate function but no GROUP BY, so the values of id and val1 will be random. What you probably want instead is either: (SELECT t.id, t.name, SUM(val) FROM t1 t GROUP BY t.id, t.name) UNION (SELECT t.id, t.name, SUM(val) FROM t2 t GROUP BY t.id, t.name); or SELECT a.id, a.name, SUM(val) FROM ( (SELECT t.id, t.name, t.val FROM t1 t) UNION (SELECT t.id, t.name, t.val FROM t2 t) ) a GROUP BY a.id, a.name; On a side note: AND SUBSTRING(t.Day,1,7) = '2013-08' AND SUBSTRING(t.Day,1,7) = '2013-11') Assuming t.Day is a date, datetime, or timestamp column, you can rewrite that WHERE clause to something like (depending on the exact data type): t.Day BETWEEN '2013-08-01 00:00:00' AND '2013-11-30 23:59:59' or t.Day = '2013-08-01 00:00:00' AND t.Day '2013-12-01 00:00:00' That way you will be able to use an index for that condition. Best regards, Jesper Krogh MySQL Support
Re: Crash after shutdown/restart
Hi Jørn, On 15/01/2014 04:36, Jørn Dahl-Stamnes wrote: 140114 18:20:08 InnoDB: Error: data file /data/mysql/data/ibdata3 uses page size 1024, 140114 18:20:08 InnoDB: but the only supported page size in this release is=16384 140114 18:20:08 InnoDB: Could not open or create data files. That error is typical for bug http://bugs.mysql.com/bug.php?id=64160 which was present in 5.5.20 and 5.5.21 (see also http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-22.html). So try to upgrade to 5.5.22 or later (I'll recommend 5.5.35) and see if that fixes the issue. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: LIKE sql optimization
On 12/02/2014 13:16, Morgan Tocker wrote: Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance. In addition to what Morgan writes, then with an index scan you will end up doing a lot of random I/O: even if the index scan itself is one sequential scan (which is not guaranteed) then for each match, it will be necessary to look up the actual row. On the other hand a table scan will generally be more of a sequential read as you already have all the data available for each match. Random I/O is more expensive than sequential I/O - particularly on spinning disks - so in general the optimizer will try to reduce the amount of random I/O. In some cases though, you may see the index scan be preferred. Assume you have a query like: SELECT val FROM table WHERE condition LIKE '%abcd'; and you have an index (condition, val) or (val, condition) then the whole query can be satisfied from the index (it's called a covering index). In that case the index scan is usually preferred over the table scan. For the purpose of using an index to do index lookups to find the matching rows rather than doing either a table or index scan for WHERE clauses like LIKE '%abcd' you can do a couple of things: * Duplicate the column used in the WHERE clause, but reverse the string. That way the above WHERE clause becomes: WHERE condition_revers LIKE 'dcba%' This can use an index as it is a left prefix. * If you always look for around the same number of characters at the end in your WHERE clause, you can create a column with just those last characters, e.g. so the WHERE clause becomes: WHERE condition_suffix = 'abcd' Do however be careful that you ensure you have enough selectivity that way. If for example 90% of the rows ends in 'abcd' an index will not do you much good (unless you are looking for the last 10% of the rows). Best regards, Jesper Krogh MySQL Support
RE: alter table modify syntax error
Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 03:45 Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error Hey guys, Sorry to hit you with one more. But I'm trying to use a positional statement in a column move based on what you all just taught me: mysql alter table modify column color varchar(10) sixth; But I am getting this error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column color varchar(10) sixth' at line 1 The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. See also: https://dev.mysql.com/doc/refman/5.5/en/alter-table.html Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: alter table modify syntax error
Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 10:09 To: Jesper Wisborg Krogh Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. Oh thanks. That's actually what I ended up doing after I got frustrated with that error. I was following the book 'Head First SQL' which was suggesting that you could do something like what this user was trying in this stack overflow thread: http://stackoverflow.com/questions/19175240/re-arranging-columns-in- mysql-using-position-keywords-such-as-first-second But the answer in that thread too suggests that this is wrong. So is the Head First SQL book just referring to an outdated syntax that doesn't work anymore? I can't imagine that it never worked if it's in that book. But hey ya never know! ;) Given the title of the book is Head First SQL and not Head First MySQL it probably isn't exclusively using syntax for MySQL. While SQL is a standard the various SQL databases are not completely identical with the syntax they support. This may be due to not completely conforming to the standard, using different versions of the SQL standard, or that there is not standard for that operation. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Avoiding table scans...
Hi Chris, -Original Message- From: Chris Knipe [mailto:sav...@savage.za.org] Sent: Thursday, 24 July 2014 19:18 To: mysql@lists.mysql.com Subject: Avoiding table scans... mysql SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber '2118806'; +---+--- +---+ + | ArtNumber | MessageID | +---+--- +---+ + | 2118807 | | part26of79.GfYzwhqz$ORUpNi3tjsW@camelsystem-powerpost.local | +---+--- +---+ + 1 row in set (22.78 sec) In addition to being slow, the query is probably not what you want. What the query does is finding the minimum ArtNumber greater than 2118806, then is free to choose any MessageID among those rows matching the WHERE clause. This is also why the query has to examine so many rows. If you look at the query with MAX(ArtNumber) ... ArtNumber ..., then it is more likely that you'll see an unexpected result. Using your table definition and inserting random rows: mysql SELECT MAX(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber 28806; +---+---+ | ArtNumber | MessageID | +---+---+ | 28805 | sutlers | +---+---+ 1 row in set (0.12 sec) mysql SELECT ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber = 28805; +---++ | ArtNumber | MessageID | +---++ | 28805 | pearl-bordered | +---++ 1 row in set (0.00 sec) So the MessageID returned for your original query is not the one corresponding to the ArtNumber. If you set sql_mode to include ONLY_FULL_GROUP_BY, you can also see that the query is invalid: mysql SET sql_mode = 'ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber 28806; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause One rewrite is the one suggested by Corrado - first find the ArtNumber, then retrieve the corresponding row. An alternative is to use ORDER BY ArtNumber ASC|DESC LIMIT 1, i.e.: mysql SELECT ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber 28806 ORDER BY ArtNumber ASC LIMIT 1; +---+-+ | ArtNumber | MessageID | +---+-+ | 28807 | groundworks | +---+-+ 1 row in set (0.00 sec) mysql SELECT ArtNumber, MessageID FROM 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber 28806 ORDER BY ArtNumber DESC LIMIT 1; +---++ | ArtNumber | MessageID | +---++ | 28805 | pearl-bordered | +---++ 1 row in set (0.00 sec) Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: MySQL 5.5.33 History list not purging?
Hi Brad, -Original Message- From: Brad Heller [mailto:b...@cloudability.com] Sent: Sunday, 7 September 2014 03:07 To: MySQL General List Subject: MySQL 5.5.33 History list not purging? For some reason, the history list isn't purging on one of my masters. This is causing all kinds of weird issues/behavior with reads. Here's the last 8 or so hours of history list length: http://i.imgur.com/Q4DEeVi.png I would start looking for an old transaction. You can use SHOW ENGINE INNODB STATUS or the information_schema.INNODB_TRX table to look for that. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: can I just encrypt tables? what about the app?
Hi Lejeczek, On 1/03/2016 00:31, lejeczek wrote: hi everybody a novice type of question - having a php + mysql, can one just encrypt (internally in mysql) tables and php will be fine? If not, would it be easy to re-code php to work with this new, internal encryption? Starting with MysQL 5.7.11, there is transparent data encryption (TDE) for InnoDB tables. If you use that, it is as the name suggest transparent for PHP. See also: https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySql Swapping issues
Hi Machiel, On 8/09/2016 05:24, Machiel Richards wrote: ... Biggest issue at the moment is mysql taking all physical memory until nothing left, the starts swapping as well until that is completely used up. Since you are using MySQL 5.7, you can consider enabling memory instrumentation in the Performance Schema: UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%'; That may help to locate where the memory is used. To monitor the memory usage use either the sys.memory_% views (https://dev.mysql.com/doc/refman/5.7/en/sys-schema-views.html) or the Performance Schema summary tables (https://dev.mysql.com/doc/refman/5.7/en/memory-summary-tables.html). Note that there is a little extra overhead instrumenting the memory. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: audit trails
Hi, On 8/12/2016 18:39, mach...@seworx.co.za wrote: ... So to recap what has been done for now : - Triggers to insert a record in audit table to show the table, type of query(insert/update) and who made the relevant change. - Trigger to prevent deletes from tables which will feedback an error to state that deletes are not allowed. What I need to still resolve: -- Trigger for deletes should still log an entry into the audit table to notify which user attempted to do a delete. -- More permanent solutions to be implemented. If your trigger generates an error, the only way to get it to log an entry into a table is to ensure that table is not using a transactional storage engine. Otherwise both the attempted delete and the audit insert will be rolled back. Using a non-transactional storage engine of course has its own problems, but depending on the requirements of the logging, it may be good enough. An alternative as already mentioned is to use an audit log plugin. In MySQL 5.7.13 and later, there are extensive filtering options available to avoid logging everything, e.g. it's possible to limit the audit logging to specific actions and/or tables. See also https://dev.mysql.com/doc/refman/5.7/en/audit-log-filtering.html - Disclaimer: I work for MySQL so will of course be happy to see you choose our audit log plugin. If you intend deletes not to be possible, I will also recommend you to remove the DELETE and DROP privileges to the table for your users. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: rescue Inno tables from an abandoned data directory?
Hi Martin, On 4/12/2016 07:23, Martin Mueller wrote: I abandoned a MySQL 5.22 database that quite suddenly andthat I wasn’t able to start up again. The data directory consists of a mix of ISAM and Inno tables. I was able to copy the ISAM tables into a new 5.6 version, and they work. Assuming you mean 5.5.22 or 5.6.22, then sometimes you can recover a table without partitions with its own .ibd file (file-per-table) using the transportable tablespace features: 1. Install a fresh copy of 5.6 2. Create the table (using a normal CREATE TABLE statement). If you don't know the table definition use mysqlfrm from MySQL Utilities (https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqlfrm.html) 3. Discard the tablespace (ALTER TABLE DISCARD TABLESPACE) 4. Copy the .ibd file (make sure you work with a copy) into the new 5.6 instance (e.g. for the table db1.t1 copy to /db1/t1.ibd) 5. Import the tablespace (ALTER TABLE IMPORT TABLESPACE) There is also an example in https://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html The import in step 5. will complain that there is no .cfg file from a proper tablespace copy, but InnoDB will do a best effort to import it, and I don't think I've seen it fail if the tablespace has been valid. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
