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
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: 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 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: 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 | > | > | > +---+--- > +---+ > + > 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: 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 " where you replace 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: 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 " where you replace 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: LIKE sql optimization
On 12/02/2014 13:16, Morgan Tocker wrote: > Hi Zhigang, > > On Feb 11, 2014, at 8:48 PM, Zhigang Zhang 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: 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: 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: 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: 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. 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: 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-updates>and|--replicate-wild-ignore-table| <http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-wild-ignore-table>options 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: 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: 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: 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: 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: 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 wrote: 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
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: 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
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: 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: 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 wrote: On Tue, Mar 30, 2010 at 6:36 AM, Marco Baiguera 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: 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: 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: 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 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: 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: 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: 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: ERROR 1442 (HY000) when delete inside trigger statement
--- Original Message --- > From: viraj > 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: 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: 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: 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 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 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: 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: 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: 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: 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: 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
how to make question that check the last hour
Hello again, I relly new with databases and writing sql-questions. But in my db want I to check what new rows have come the last hour. the db-table (table name is SUBSCRIBER) have the following columns as follows: ID email created updated 001 [EMAIL PROTECTED] 20050215131034 20050215133401 063 [EMAIL PROTECTED] 20050215141034 20050215141201 076 [EMAIL PROTECTED] 20050215134500 20050215134556 The data type of the columns are: ID =VARCHAR(14) email =VARCHAR(255) created =VARCHAR(14) updated =VARCHAR(14) Now I would like to make a sql-question that show which new users have come the last hour, without that I need to edit the question each time I want to ask. (please, donr't blame me that the "date columns" (created & updated) not are in date format, I have not done the DB from the beginning, I am only tries to solve some problem in it), I don't think it is possible to change these 2 columns to "DATETIME" format because then will the web program stop working, but I would be greatfull for a work around solution that solve my problem. Thanks Alec, for your quick answer but it dosn't helped me much, it list every row any way. I have done my question like this (after your suggestion (or you have to blame me that I'm stupid if it is not correct ;-))). select CREATED from SUBSCRIBER where date_sub(now(), interval 1 hour) <= created; What do I wrong?? /J. > > "Jesper" wrote on 15/02/2005 13:15:43: > > > Hello, > > > > I relly new with databases and writing sql-questions. But in my > > db want I to check what have new rows have come the last hour. > > > > the db have I as follow: > > > > ID email created updated 001 > > [EMAIL PROTECTED] 20050215131034 20050215133401 > > 063 [EMAIL PROTECTED] 20050215141034 20050215141201 > > 76 [EMAIL PROTECTED] 20050215134500 20050215134556 > > > > Now I would like to make a sql-question that show which new users > > have come the last hour, without that I need to edit the question > > each time I want to ask. > > select from where date_sub(now(), interval 1 hour) <= > created ; > > Alec -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to make question that check the last hour
Hello, I relly new with databases and writing sql-questions. But in my db want I to check what have new rows have come the last hour. the db have I as follow: ID email created updated 001 [EMAIL PROTECTED] 20050215131034 20050215133401 063 [EMAIL PROTECTED] 20050215141034 20050215141201 76 [EMAIL PROTECTED] 20050215134500 20050215134556 Now I would like to make a sql-question that show which new users have come the last hour, without that I need to edit the question each time I want to ask. Please help! -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
union and group by
Hi I'm having problems with a union and group by... My query looks like this... (select count(*) as numberOfC,Customer.country,Customer.creationdate FROM Customer,VIP where Customer.id=VIP.customer and VIP.brand=2 and Customer.creationdate > '1999-31-01' group by country) union (select count(*) as numberOfC,Customer.country,Customer.creationdate FROM Customer,Catalog where Customer.id=Catalog.customer and Catalog.brand=2 and Customer.creationdate > '1999-31-01' group by country) order by numberOfC desc; My problem is, that the two gruop by's each give me one "numberOfC". Can I just add those? My goal is to get the destinct number of cutomers in each country... regars jesper
Unexpected insert/update behaviour makes backup import impossible.
>Description: On a table with a auto_increment/primary constraint is it not possible to insert an id with value 0 but it is possible to insert it with another id and use update to set it to 0, thereby making the backup import later fail due to the use of "insert" statements. >How-To-Repeat: mysql> DROP TABLE test; Query OK, 0 rows affected (0.07 sec) mysql> CREATE TABLE `test` ( -> `id` int(10) unsigned NOT NULL auto_increment, -> PRIMARY KEY (`id`) -> ) TYPE=InnoDB PACK_KEYS=1 ; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(0); Query OK, 1 row affected (0.00 sec) mysql> select * from test; ++ | id | ++ | 1 | ++ 1 row in set (0.00 sec) mysql> update test set id = 0; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> select * from test; ++ | id | ++ | 0 | ++ 1 row in set (0.00 sec) >Fix: >Submitter-Id: >Originator: >Organization: >MySQL support: none >Category: mysql >Class: sw-bug >Release: mysql-4.0.18-standard (Official MySQL RPM) >C compiler:2.95.3 >C++ compiler: 2.95.3 >Environment: System: Linux 2.4.20-8bigmem #1 SMP Thu Mar 13 17:32:29 EST 2003 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Thread model: posix gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' CXX='gcc' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce-felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Oct 3 2003 /lib/libc.so.6 -> libc-2.3.2.so -rwxr-xr-x1 root root 1549556 Mar 14 2003 /lib/libc-2.3.2.so -rw-r--r--1 root root 2321376 Mar 14 2003 /usr/lib/libc.a -rw-r--r--1 root root 204 Mar 13 2003 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Oct 3 2003 /usr/lib/libc-client.a -> c-client.a Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-server-suffix=-standard' '--without-embedded-server' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O2 -mcpu=i486 -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimal InnoDB datafile size? 150GB data.
I gmane.comp.db.mysql.general, skrev Kurt Haegeman: > >What is the optimal InnoDB datafile size? Is 75 * 2GB prefered over 1 * > >1G:autoextend? The filesystem (ext3) has no problems handling the > >filesizes, so that should not be an issue. > > If your one large datafile suffers irrepairable block corruption, you'll > have to prepare for a restore of a XXX Gb file, which will take much > longer than replacing a damaged 2Gb datafile. We suffered from such a > loss on our Oracle database, and since then we no longer use datafiles > larger than 2Gb. So MySQL dont care wether it's a block of 100GB or several smaller, regarding speed? > Concerning your lob question: if the lobs are in often queried tables > with other relational data, and you only use the other data for the > query, then yes, the speed of the database would be better without them > (smaller tables -> more speed). I see. Thanks. -- ./Jesper Krogh, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimal InnoDB datafile size? 150GB data.
Hi. I need some recommadations. What is the optimal InnoDB datafile size? Is 75 * 2GB prefered over 1 * 1G:autoextend? The filesystem (ext3) has no problems handling the filesizes, so that should not be an issue. Of the 150GB is about 100GB BlOB's, would it have any impact on the overall speed of the database to store this data in the filesystem instead? Thanks. -- ./Jesper Krogh, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to combine two selects into one ???
> -Original Message-
> From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]]
> Sent: sábado, 25 de enero de 2003 21:38
> To: Jesper Frank Nemholt
> Cc: [EMAIL PROTECTED]
> Subject: Re: How to combine two selects into one ???
>
[clip]
> If I understand you correcty, the following should do what
> you want (untested):
>
> SELECT timecode,
> SUM(IF(username='jfn',cpuusage,0)),
> SUM(IF(username='root',cpuusage,0))
> FROM process
> WHEREsystemid = 2 AND
>timecode > now() - INTERVAL 1 DAY AND
> username in ('jfn', 'root')
> GROUP BY timecode
> ORDER BY timecode
>
> Note that it will be somewhat slower than two seperate
> queries, I think.
Thanks, it did exactly what I wanted :-)
The other example you listed has the problem that my application, or
rather the PHP script that generates the graphics, only works if the
output is organized as one timecode followed by a number of values for
each row.
/Jesper
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to combine two selects into one ???
Hi! I need to make a select which is the combined result of 2 selects. I assume I could do this in v4 using UNION or in v3 by running 2 "select into" statements, however I'd prefer to do it in v3.23 and if possible with just one select and just read permissions. The table is like this : mysql> desc process; ++---+--+-+- +---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+- +---+ | timecode | datetime | | | -00-00 00:00:00 | | | systemid | smallint(5) unsigned | | MUL | 0 | | | username | varchar(20) | | | | | | cpuusage | float(4,2)| YES | | NULL | | ++---+--+-+- +---+ It's a table containing unix users and their cpu usage. The table contain more info about processnames memory usage per process etc. etc.. I first make a select to find the 2 users with the most CPU usage : SELECT username, sum(cpuusage) AS sum_cpuusage FROM process WHERE systemid = '2' AND timecode > now() - INTERVAL 1 DAY GROUP BY username ORDER BY sum_cpuusage DESC LIMIT 2 I then use the result from this select to make a new select with the format timecode, user1, user2, user3 etc... where user1, user2 etc. is the sum() of these users cpu usage at any given timecode. I use sum because every user can have more than one process active at any given time, and I store all active (cpu using) processes in the database. What I want is the combined output of selects like these (example with 2 users) : SELECT timecode, sum(cpuusage) AS jfn FROM process WHERE systemid = '2' AND username = 'jfn' AND timecode > now() - INTERVAL 1 DAY GROUP BY timecode ORDER BY timecode SELECT timecode, sum(cpuusage) AS root FROM process WHERE systemid = '2' AND username = 'root' AND timecode > now() - INTERVAL 1 DAY GROUP BY timecode ORDER BY timecode With combined I mean that I want it in the form "timecode, jfn, root". How do I rewrite this into one select, and can it be done without having to use features only present in v4, or features that require write access to the database ??? For people who want to test/try the real database, there's a read only web interface at http://statdb.dassic.com/sql.php The 2 queries from the example that I want to combine into one produce useful data, so the question is just to merge them into one, since the goal is to later, using another interface, ask a PHP script to draw a graph with a top 5 of CPU usage among users. To do this I need a output like this : timecode, user1, user2, user3 etc. /Jesper - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Subselect translation?
Hi, I have a little problem making a query with only one SELECT phrase;
I wan't to solve this with a JOIN since sub-selects are not supported in
MySQL:
select table1.id from table1 where table1.id not in(select table2.t1id
from table2 where table2.id = 9)
I can't imagine this not being possible in any other way.
/Jesper Öman, Sweden
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Enabling LOCKing on Linux Red Hat 7.1
I have a standard installation of Mysql on a red hat 7.1 box. I am confused as to where I can disable the SKIP_LOCKING feature. There is no skip_locking-option in /etc/my.cnf. I removed the string "--skip_locking" from /usr/bin/safe_mysqld. I can't find it anywhere, still when I do mysqladmin variables it reports sklip_locking ON. Where do I turn it off? ...Jesper _ MSN Photos är det enklaste sättet att dela ut och skriva ut foton: http://photos.msn.se/Support/WorldWide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to reduce data by averaging ???
Hi! I have various tables more or less similar to this : mysql> desc disk; +--+---+--+-+-+- --+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+- --+ | timecode | datetime | | | -00-00 00:00:00 | | | system_id| smallint(5) unsigned | | MUL | 0 | | | devicename | char(8) | | | | | | device_id| char(8) | | | | | | read_count | mediumint(8) unsigned | YES | | NULL| | | read_kb | mediumint(8) unsigned | YES | | NULL| | | write_count | mediumint(8) unsigned | YES | | NULL| | | write_kb | mediumint(8) unsigned | YES | | NULL| | | servicetime | float(4,2)| YES | | NULL| | | waittime | float(4,2)| YES | | NULL| | | active_queue | float(4,2)| YES | | NULL| | | wait_queue | float(4,2)| YES | | NULL| | | busy | float(4,2)| YES | | NULL| | +--+---+--+-+-+- --+ 13 rows in set (0.00 sec) Lots of data is inserted into these tables and allways (well, more or less) later selected with a ordering by timecode. I would like to reduce the size of old data by averaging the values by hour instead of the current where I usually have 12 samples per hour. The goal is to end up with high resolution data for the last couple of monts, while older and more unimportant data is averaged by hour. If I do a select like this : select substring_index(timecode,':',1),system_id,devicename,device_id,avg(read_coun t),avg(read_kb),avg(write_count),avg(write_kb),avg(servicetime),avg(waittime ),avg(active_queue),avg(wait_queue),avg(busy) from disk where system_id='1' group by substring_index(timecode,':',1) order by substring_index(timecode,':',1) ...I will get all the data averaged by hour, but how do I get it back into a table ??? In Oracle my guess is that I can do this with a combination of a select and insert, but I don't think this is supported in MySQL. As far as I know this is more a less a subselect thing, and MySQL doesn't support subselects. Do I need to make some external scripting (Perl with DBI) or can everything be done inside MySQL ??? -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: CPU usage running mysql
> -Original Message- > From: David Allen [mailto:[EMAIL PROTECTED]] > Sent: lunes, 21 de mayo de 2001 14:36 > To: Simon Green; [EMAIL PROTECTED] > Subject: Re: CPU usage running mysql > > > Hello again! > > Do you know if there is any way I can see what the mysqld is > doing? If it is > taking up 99% of the processor time, it is very busy doing > something! I > installed the binary version and consequently only have the > tools available > which came with that distribution. First of all, compare with supported tools such as vmstat or ps to ensure that top is correct. vmstat will not show the process itself but just total usertime, systemtime and idletime. You can also use Symbel ( www.setoolkit.com ), but I've experienced a bug in this too related to cpu/process info on Solaris 6, 7 and possible also 8. This is in all recent releases (3.0 -> 3.2) and is present in toptool.se, aw.se and infotool.se. Tools in Symbel that will work and give you some info about mysqld is /opt/RICHPse/examples/pea.se this however tends to loop if you pipe the output to awk or another program. That's another bug ;-) For default debugging tools shipped with a standard Solaris go to /usr/proc/bin : # ls -al /usr/proc/bin total 30 drwxr-xr-x 2 root bin 512 Dec 31 20:25 . drwxr-xr-x 3 root bin 512 Dec 31 20:25 .. lrwxrwxrwx 1 root root 15 Dec 31 20:25 pcred -> ../../bin/pcred lrwxrwxrwx 1 root root 16 Dec 31 20:25 pfiles -> ../../bin/pfiles lrwxrwxrwx 1 root root 16 Dec 31 20:25 pflags -> ../../bin/pflags lrwxrwxrwx 1 root root 14 Dec 31 20:25 pldd -> ../../bin/pldd lrwxrwxrwx 1 root root 14 Dec 31 20:25 pmap -> ../../bin/pmap lrwxrwxrwx 1 root root 14 Dec 31 20:25 prun -> ../../bin/prun lrwxrwxrwx 1 root root 14 Dec 31 20:25 psig -> ../../bin/psig lrwxrwxrwx 1 root root 16 Dec 31 20:25 pstack -> ../../bin/pstack lrwxrwxrwx 1 root root 15 Dec 31 20:25 pstop -> ../../bin/pstop lrwxrwxrwx 1 root root 15 Dec 31 20:25 ptime -> ../../bin/ptime lrwxrwxrwx 1 root root 15 Dec 31 20:25 ptree -> ../../bin/ptree lrwxrwxrwx 1 root root 15 Dec 31 20:25 pwait -> ../../bin/pwait lrwxrwxrwx 1 root root 14 Dec 31 20:25 pwdx -> ../../bin/pwdx There are man pages for all of them. They all give useful information about what a given process is doing. You can also use truss. ...ofcourse you can also get some info from MySQL itself with "show processlist" -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Having success with 20 million rows
> -Original Message- > From: Paul DuBois [mailto:[EMAIL PROTECTED]] > Sent: jueves, 10 de mayo de 2001 6:00 > To: Christian Jaeger; David J. Potter; [EMAIL PROTECTED] > Subject: Re: Having success with 20 million rows > > > At 11:00 PM +0200 5/9/01, Christian Jaeger wrote: > >Was the table inaccessible during these 4 days? Or is mysql able to > >still write to the table while it's being altered? > > Those are not mutually exclusive possibilities. :-) > > According to the manual, reads can be done on the original table while > the new table is being constructed. Updates are stalled and > then applied > to the new table after the alteration. > > http://www.mysql.com/doc/A/L/ALTER_TABLE.html > Yep, and that stalling is (IMHO) one of the primary problems with MyISAM & ISAM tables. Ofcourse it's possible to design oneself out of these problems, but the only real good solution is locking on row level rather than the whole table. InnoDB tables has row level locking AFAIR. My databases have around 52 insert requests per second, so even a small alter build up a huge amout of pending requests. Regarding the time spent. 4 days seems to be alot to me. what kind of hardware is it running on ? I have MySQL databases with more than 100 million rows and they take only about 4 hours to alter on a 700 MHz, 8 MB cache, EV67 Alpha (with lots of RAM). Even though I run on SMP machines with lots of CPUs, altering is a singlethreaded task (or so it seems), so I don't have any benefit out of it. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 64MB Limit on MyISAM indexes?
> -Original Message- > From: Jeff Brewer [mailto:[EMAIL PROTECTED]] > Sent: viernes, 20 de abril de 2001 18:24 > To: MySQL Mail List > Subject: 64MB Limit on MyISAM indexes? > > > I've got tables in ISAM format whose indexes are way over 64 > MB. However, > when I try to create a MyISAM table with an index that big I > get errors and > confirming that with myisamchk I get: > > myisamchk: warning: Keyfile is almost full, 67107839 of > 67107839 used > > Which is essentially 64MB. Are MyISAM tables able to handle > large indexes? I've had ISAM and now have MyISAM indexes larger than 2 GB, so yes. > I hope I don't have to go back to ISAM tables... > Any suggestions? Haven't seen that error before, so I can't help you. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Unaligned address crash on Tru64
Hi! I have tested a bit today. Various logfiles are at : http://www.dassic.dk/~jfn/mysql/ I found that after compiling with full debugging, I got the error that one of the my.cnf parameters was too little and should be increased on the server. The default is 1 MB. I tried to increase it to 16 M, then 32 MB, then 512 MB, then 5 GB. No go at all. I tried both slave & server (I have 6 GB on the machine and a unlimited ulimit and full access to all memory (no sysconfigtab limitations), so I'm not low on memory). The configure and make options for this crashing debug version is in the make_bad.sh, and the error in O4_error.txt and the mysqld.trace in mysqld.trace.txt I then decided to strip down the compiler options to what you find in make_good.sh. Did a recompile (output from this in make_good.log.txt (stdout) and make_good.typescript.txt (stderr)). As the name say, this version works. I compiled with no optimization, and arch set to generic since the machine I compile on is a EV67 and the machine I test on is EV56, and I don't want any EV67 instructions to be emulated by the kernel. I deleted master.info and let the slave update about 1 GB of data. This worked without problems and I got no errors. So it appears that the recommended optimization settings from INSTALL-SOURCE does something bad to MySQL in my environment. I've tried -O3 and -O2. No go, only -O0 works. Haven't tried playing with some of the other parameters. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Unaligned address crash on Tru64
> -Original Message- > From: Sasha Pachev [mailto:[EMAIL PROTECTED]] > Sent: Saturday, April 07, 2001 6:54 PM > To: Nemholt, Jesper Frank > Cc: [EMAIL PROTECTED] > Subject: Unaligned address crash on Tru64 > > > Jesper: > > The problem is apparently a bug that under some circumstances > tries to store > an integer at an unaligned address with movl instruction. > Slave code does a > lot of address magic, so there is a lot of room for alignment > bugs. Aha. I've also recieved unaligned access errors, allthoug without running into a crash, in other situations. I remember recieving it every time I access one of the following : mysqladmin status mysqladmin processlist mysqladmin extended-status mysqladmin variables I haven't checked if it is all of them or just one of them, but I get one "unaligned access" error every time I access a PHP page containing these as systemcalls. > Any > chance you could run the slave in a debugger and find the > line where this > happens? > I'll try debugging next week. I'll debug the Compaq CC/C++ compiled version, since this is the one I hope to get working (it generally produces faster & smaller binaries on Tru64 than GCC). One thing btw. for the MySQL INSTALL-SOURCE Tru64 documentation : Might be a good idea to add that in order to compile recent MySQL versions (> 3.22.x) one has to upgrade the make utility. The one present by default on all Tru64 versions until 5.1 is not able to make MySQL. I haven't checked if the one present on 5.1 or 5.1A is better. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Signal 11 crash when running 3.23.36 in slave mode on Tru64
Hi! I've run into a problem with a replication setup. I have a 4 CPU EV5.6 AS4100 running a master database. MySQL is 3.23.36, OS is Tru64 4.0F patchkit 4. MySQL is compiled with Compaq CC & C++ with the recommended compiler options mentioned in INSTALL-SOURCE. This master database runs without any problems. Today I setup a slave server on a 12 CPU EV67 GS140, still 4.0F patchkit 4 and MySQL 3.23.36, and still compiled with Compaq CC & C++. When starting up the slave I immediately get this : Unaligned access pid=892 va=0x12021d4e4 pc=0x3ff800e8a24 ra=0x3ff8013df90 inst=0xa6100028 010406 15:47:23 mysqld restarted Unaligned access pid=875 va=0x12021d4e4 pc=0x3ff800e8a24 ra=0x3ff8013df90 inst=0xa6100028 010406 15:47:23 mysqld restarted Unaligned access pid=881 va=0x12021d4e4 pc=0x3ff800e8a24 ra=0x3ff8013df90 inst=0xa6100028 010406 15:47:24 mysqld restarted And in the error log : 010406 15:36:16 mysqld restarted /usr/users/un19102/mysql/libexec/mysqld: ready for connections mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. I start it with safe_mysqld and this script try to restart it over and over again. I have also tried starting it manually. Same result. Sometimes however it does startup, but crashes within 5-10 minutes with the same error. I tried compiling with GCC instead of Compaq CC/C++. Same result, same error. I've also tried various mysqld parameters to turn off dangerous settings and/or add more debugging info. I didn't try yet to compile MySQL with more debugging options etc. As soon as I remove the slave settings from my.cnf and start it as a normal server, everything runs normally. Is it compiler related problems, or is there something wrong with the slave option ? -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Dual Processor and MySQL !
> -Original Message- > From: Hamid Hashemi Golpayegani [mailto:[EMAIL PROTECTED]] > Sent: domingo, 01 de abril de 2001 15:07 > To: Nemholt, Jesper Frank; [EMAIL PROTECTED] > Subject: RE: Dual Processor and MySQL ! > > > Hi , > > Thank you very much for your compelete description about > MySQL and multi > processor platforms . > According to your mail if I have several connection to my SQL > server and I > have more than 1 CPU and installed the SMP on my machine the MySQL > performance will be better than a single CPU and If I have > only 1 connection > to my SQL server then single and dual CPU are not import for > this situation Correct. > . > So it is ok ! cause I have about 200 concurrence connection to my SQL > machine and it seems that be better to have more that one CPU . Yes, with more than one concurrent query running, you'll benefit from SMP. Exactly how much depends on many things (table locking is one critical thing with MySQL), but with 200 concurrent connections, you'll get pretty close to 100% benefit, which means if you go from one 500 MHz to two 500 MHz CPUs, you'll get close to twice the performance. ...but the execution time of one singlethreaded SQL on a idle machine will not get faster. To get this you need a faster CPU, not more CPUs. Try to run a "show processlist" every now & then and see how many connections you have, and how many of those are active or sleeping/waiting for a table to be unlocked. Depending on database design, you might find, with MySQL, that most of the time is spent waiting for a table to unlock. There are some guidelines in the MySQL manual on how to avoid this (by inserting into temporary tables and the likes), but most of these solutions will get rather ugly. Other SQL servers ike Oracle lock on row level which is _much_ better. I hope this will come in MySQL soon. > About other SQL servers that you said support multi > processing alone without > depending on OS and SMP . Can you tell me them ?! > I thinks that Oracle would be one of them . All of them (well, those I know) rely on the SMP support in the operating system. In fact, the SQL server doesn't need to know about SMP (or how many CPUs the machine have). The only place I know of where the SMP support is built into the application is on MacOS nad applications like Adobe Photoshop. This is a very ugly hack and only made because Adobe needed the speed, there were SMP hardware, but the OS didn't support SMP. What is important is whether the SQL server is multithreaded or not (and support the thread implementation on the operating system it runs on). If it is multithreaded and dedicate a thread to every query, or maybe is even able to split a query into several threads, then it'll utilize whatever SMP is offered by the OS. Beware that many OSes don't support SMP or have pathetic thread support. You'll find fairly good SMP & thread support in most commercial unix versions (Solaris, HP-UX, Tru64, AIX etc.), Windows NT/2000 and recent versions of Linux. Most of the *BSD implementations, and Windows 9x doesn't support SMP, and some not even threads. What I don't know about MySQL is how well (if at all) it is able to split up a query into several threads. On a SQL server like Oracle I know that complex queries with several subselects and things like that are divided into several threads which improve performance alot on a SMP machine. The DBAs where I work use this extensively when they create their SQL statements in order to get the most performance out of the machine (and the query result as fast as possible). Most of your production machines have more than 8 CPUs, so there is alot to win on them. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Dual Processor and MySQL !
> -Original Message- > From: Hamid Hashemi Golpayegani [mailto:[EMAIL PROTECTED]] > Sent: sábado, 31 de marzo de 2001 17:09 > To: [EMAIL PROTECTED] > Subject: Dual Processor and MySQL ! > > > Hi , > > Sorry for this question may be this question asked before but > I can't find > it . > I want to know that an SMP Dual CPU machine for a MySQL > server is better > that a single CPU machine ?! > I mean is the MySQl use the both CPU's for processing or not ? > and how much affect on the responding and which parameters > are going better > in this case ?! As said by other, if the OS supports SMP, MySQL will benefit from this, since MySQL is a threaded application. However, a singlethreaded SQL (ie. a SQL MySQL is unable to break up into several parts) will only run on one CPU at a time, and this is the situation you will see most of the time. In fact I'm not even sure MySQL does try to break up SQLs into >1 threads, but I know other database servers do. A SQL with several subselects is a good example. Every subselect can run as a seperate thread, but as you probably know, MySQL doesn't support subselects, so there... So, you should not expect to get better single SQL performance from a SMP machine. Where you will get better performance is if you have >1 concurrent user or rather >1 concurrent SQL (who don't lock each others tables), because MySQL (or rather the OS) then will balance the SQLs between the available CPUs. You will allways get better performance out of a single CPU machine rather than a SMP machine if the CPU type is the same and the single processor is about the same clock frequency as the SMP processors combined. A single Pentium III 1 GHz will in single threaded tasks be a little more than twice as fast as a dual Pentium III 500 MHz, and in every case allways faster. If you serve a single threaded task to a dual Pentium III 500 MHz, it perform like a single Pentium III 500 MHz. If you serve a multithreaded task to it, it'll perform somewhere between 500 MHz and close to 1 GHz. SMP should only be considered when you can't get any faster with a single CPU machine, and if you need that extra performance, you should still use the fastest clocked CPUs for SMP, because if you don't it'll feel slow in singlethreaded performance. I've run MySQL on machines with between 2 & 12 CPUs running either Linux, Solaris or Tru64. With one SQL active at any given time, I've never seen MySQL use more than one CPU. With two SQLs active at the same time, it uses 2 CPUs and so forth, unless the SQLs lock each others tables, which in fact, for me, is a major problem in MySQL. You need a recent MySQL to fully benefit from SMP, since older 3.22.x doesn't have any configure options to control SMP behavior. Secondly, on some platforms it is quite important to compile MySQL with the right flags in order to get the best SMP/thread performance. -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to index this properly (to make it go faster) ?
> -Original Message- > From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] > Sent: martes, 27 de marzo de 2001 10:00 > To: Nemholt, Jesper Frank > Cc: '[EMAIL PROTECTED]' > Subject: Re: How to index this properly (to make it go faster) ? > [clip] > > Why that, usertime & systemtime are data values, they are > never used in any > > where clause. As far as I can see it's complete waste to > put index on those. > > If all columns of the SELECT clause are part of the index, MySQL will > solve the query only with reading from the index file and never touch > the data file itself. > > Since the index is quite big already and usertime+systemtime only add > 8 bytes per row, this may pay off regarding the speed win / size > increase. Aha. I'll think about that, but I'll have to look in the logfiles to see if there's a pattern regarding what the users select. I don't think it's just usertime & systemtime every time. [clip] > > > Depending on the Cardinality of the different columns you > may want to > > > change the order to (username,processname,system_id,timecode) > > > or alike. > > > > Aha, I'll have to test more with the individual order of > combined indexes to > > see what I can get from it. > > Well, normally that is not really important, but in your case, > processname has a low cardinality and therefore one value has a lot of > matches in the index and it is worth a try to put it to the right. So the lower the cardinality the more to the right in a combined index ? [clip] > May I bother you to add the following indexes and post the EXPLAIN > after adding them:. > > (system_id, processname, timecode) > (system_id, processname, timecode, username) > (system_id, processname, timecode, username, usertime, systemtime) I'll try. Tonight I'm going for system_id, processname, timecode I will probably make the next tests on the disk table, since recreating indexes and analyzing/optimizing on the process table takes too many hours. The disk table only takes 20 minutes. > > Every explain ends up saying "where used" and the showing > the full number of > > rows. This is in 3.22.32. My test setup is the latest, it > might be different > > there. > > Well, using two different systems isn't a good idea, except if you > plan to upgrade your 3.22.32 system, because if we find a solution on > your test setup (3.23.?), it won't behave the same on the older > MySQL. In most cases, 3.23 is faster than 3.22 (which is to be > expected). I plan to upgrade asap, but had a few problems building the latest stable under 4.0f ev56. On a side note, with 3.22.32 I have some weird memory allocation problems. The database runs on a 4 CPU machine with 4 GB memory, and most of these 4 GB are free (or rather used for filesystem cache by the OS). The memory is free if any process want it. With the default settings (ie. no custom my.cnf) everything runs fine, but MySQL use allmost no memory which is not optimal given the size of the database & indexes. If I instead use the my-huge.cnf that comes with MySQL which makes a key buffer of 384 MB, a big select makes MySQL crash with a "Out of memory" error. I have ulimit set to unlimited, and no memory allocation errors are logged to /var/adm/messages (Tru64 allways logs to that file if some user hit the limit of some sysconfigtab/kernel parameter). This means that the "Out of memory" error from MySQL was not caused but the OS disallowing mysqld to allocate more memory. I have tried this several times to be sure it's reproduceable. I get a crash every time. Is there a memory allocation bug in MySQL on Tru64 ? > Anothing thing: If you cannot get it work with combined indexes, can > you post some testcase (i.e. the SQL commands to build a sample > database), so that we can play around and see if we have more luck? Sure. Everything will in fact be put on a web page soon, since it's a OpenSource solution. The database is used to store performance info for Tru64, Solaris & Linux (and others soon). A bit like MRTG/RDDTool/Cricket but much more dynamic & detailed. l8r/Jspr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql in a 4-way / 2 GByte server / raid 5
> -Original Message- > From: Rafael Martinez [mailto:[EMAIL PROTECTED]] > Sent: martes, 27 de marzo de 2001 15:28 > To: [EMAIL PROTECTED] > Subject: Mysql in a 4-way / 2 GByte server / raid 5 > > > Hello > > I am thinking to install our DB server in a 4-way machine with > Intel Xeon processors/1MB cache. I can see in the mysql > documentation that mysql > works OK in a 2-way machine but that in a 4-way system has to > be tested. > > Is this true? Or the documentation is not updated? > > Anybody out there with a 4-way machine that runs mysql > without problems? > (kernel 2.4.x glibc 2.2.12) > > Our DB has around 120.000.000 "questions" per week and our > 2-way/1Gb system works OK, > but the trafic is growing very fast and I would like to have a backup > plan incase I need it. > > Thanks for your help. > Rafael Martinez I've had MySQL running on a 12 CPU 16 GB machine running Tru64. Runs OK. I normally run it on a 4 CPU 4 GB machine and another one with 3 CPUs and only 512 MB. Both Tru64 Alpha. In recent (3.23) versions there are some my.cnf parameters related to SMP machines. You might want to take a look at them : set-variable= thread_cache=8 set-variable= thread_concurrency=8 # Try number of CPU's*2 The only problem I have is that there appears to be a problem with memory allocation when running MySQL on Tru64. I haven't found out exactly what the problem is, but the result is a crashed MySQL with a "Out of memory" error. Which is rather misleading since there is plenty of free memory and no ulimit or any other limiting kernel parameters. l8r/Jspr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to index this properly (to make it go faster) ?
> -Original Message- > From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] > Sent: sabado, 24 de marzo de 2001 23:31 > To: Nemholt, Jesper Frank > Cc: '[EMAIL PROTECTED]' > Subject: Re: How to index this properly (to make it go faster) ? > > First, you should run an analyize on your indexes (e.g. with > (my-)isamchk). This will give MySQL a value for "Cardinality" and > enable the optimizer to do its work better. Allready doing that. It was missing from the first mail since I'd just created the indexes. Here's from the real one : mysql> show index from proc; +---++-+--+-+--- +-+--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++-+--+-+--- +-+--++-+ | proc | 1 | timecode|1 | timecode| A |4220 | NULL | NULL | | | proc | 1 | system_id |1 | system_id | A | 1 | NULL | NULL | | | proc | 1 | processname |1 | processname | A | 88 | NULL | NULL | | +---++-+--+-+--- +-+--++-+ > A good index would probably be > (system_id,username,processname,timecode). The users hardly use 'username' in their where clause. That's why it's left out (the index started to get rather big also). > Maybe even > (system_id,username,processname,timecode,usertime,systemtime), > which should enable "using index" only. Why that, usertime & systemtime are data values, they are never used in any where clause. As far as I can see it's complete waste to put index on those. > You may want to use partial keys in order to reduce space > consumption, e.g. > Maybe even (system_id,username(5),processname(5),timecode), This is a no go since alot of processes are identical on the first 5 characters. The username could be used, but I decided not to, since Oracle (which the solution also has to be compatible with) is smart enough to only store the characters in use. I actually think MySQL does the same. > Depending on the Cardinality of the different columns you may want to > change the order to (username,processname,system_id,timecode) > or alike. Aha, I'll have to test more with the individual order of combined indexes to see what I can get from it. [clip] > No index is chosen, because you obviouly SELECTed a major part of the > table. This probably won't happen, if you got these 100 million rows > are talking about below (because you won't select about 20.000.000 > rows, will you?). Well, that's the problem. I will ;-( The database contain performance info for a collection of very large unix servers. Most of them have more than 1000 concurrent processes, more than 100 disk devices and so on. For 34 machines the amount of data in a day is more than 100 MB, when sampled every 10 minute. I currently keep 3 months of history. The database is used to study performance on the machines, like memory use, disk use and so on. The interface is either Microsoft Excel or a PHP/GD based web interface I've made. Most selects go only a few days back, since it's mostly a matter of finding out what caused the machines to be slow, out of memory or whatever the night before, but sometimes we need more than a month to find out about processes leaking memory slowly, development of memory usage, disk I/O development and so on. Performance has been pretty good until now except on the disk table (due to > 100 unique disk devices on most machines). Performance for process went all bad when I recently changed from collecting only top 20 processes to collecting all (those 500 - 2000 processes). I could ofcourse go back to top20, but the benefit of having all processes makes me want to avoid that, and the stuff running in Oracle is not hurt due to the partitioning. > Reasonable indexes would be the same as above, which should reduce the > number of rows to read far enough to prevent a full table scan. > > [...] > > Now, the problem is that I have about 30 system_id's in the > database. Each > > system insert about 500 rows into the proc table every 10 minute. > > ...so after a month or so I have alot of rows (> 100 million), and a > > performance problem. > > In Oracle I have fixed this by partitioning on system_id > and/or timecode, > > but this is not possible to do transparently in MySQL, > > You could probably use the MERGE table type. Aha, I'll take a look on that in the manual. [
How to index this properly (to make it go faster) ?
o I have alot of rows (> 100 million), and a performance problem. In Oracle I have fixed this by partitioning on system_id and/or timecode, but this is not possible to do transparently in MySQL, and redesigning the database structure with one set of tables or one database for each system will get very ugly. ...so the result now is that MySQL is pathetically slow and Oracle extremely fast, in fact more than 30 times faster. I have tried to combine the indexes, but then MySQL fails using them properly, so the best I've been able to come up with is seperate index on timecode,system_id and processname. Then it seems that MySQL decides to use whatever is best for the selected timescope. I've also had isamchk to sort the data tables according to timecode, since the result is allways ordered by timecode. Have I forgotten something, or are the current indexes as good as it gets ? -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Comment
When I have created a comment on a table with "create table . comment = 'this is a comment' How do I view the comment afterwards? ...Jesper - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Memory problems/bug ?
> -Original Message- > From: Nemholt, Jesper Frank > Sent: viernes, 09 de marzo de 2001 19:52 > To: '[EMAIL PROTECTED]' > Subject: Memory problems/bug ? > > > Hej! > > Using MySQL 3.22.32 on Tru64 4.0F patchkit 4. Compiled with Compaq CC. > > Ran optimize on a table, and after 10 minutes : > > 010307 17:00:00 Out of memory; Check if mysqld or some > other process uses > all available memory. If not you may have to use 'ulimit' to > allow mysqld to > use more memory or you can add more swap space > mysqld ended on Wed Mar 7 17:00:21 MET 2001 > One other question about this error : If I try out the latest stable version, will I be able to use the 3.22.32 database files directly, or should I rather dump the existing and import them to the new ? I'd ofcourse still like some answers regarding my original question... -- Un saludo / Venlig hilsen / Regards Jesper Frank Nemholt Unix System Manager Compaq Computer Corporation Phone : +34 699 419 171 E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Memory problems/bug ?
Hej! Using MySQL 3.22.32 on Tru64 4.0F patchkit 4. Compiled with Compaq CC. Ran optimize on a table, and after 10 minutes : 010307 17:00:00 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space mysqld ended on Wed Mar 7 17:00:21 MET 2001 I checked : iratxe.tm.es> ulimit unlimited iratxe.tm.es> /usr/sbin/swapon -sv Swap partition /dev/rz8b (default swap): Allocated space:48768 pages (381MB) In-use space: 316 pages ( 0%) Free space: 48452 pages ( 99%) Swap partition /dev/rzb11c: Allocated space: 256841 pages (2006MB) In-use space: 312 pages ( 0%) Free space:256529 pages ( 99%) Total swap allocation: Allocated space: 305609 pages (2387MB) Reserved space: 47242 pages ( 15%) In-use space: 628 pages ( 0%) Available space: 258367 pages ( 84%) No ulimit and more than 2 GB free swap and no active swap usage. Before the crash I'd succesfully optimized other (and smaller) tables. I tried once again on the same table, and MySQL crashed again. Same thing happened when I tried to put a new index on the table. I checked the memory status : http://www.dassic.dk/iratxe_memory.png As seen, the optimize (around 17:00) causes the UBC filesystem buffer to allocate more memory, and when it has used all the free memory, MySQL crashes. I don't understand this, since the memory used by the UBC is available for any program that might need it. The operating system will only use memory for UBC if it's not used for anything else, and release UBC memory if a application needs more. Secondly, MySQL crashed even before the machine started to swap, and as seen the active memory increases only because of the UBC, not because MySQL allocate more memory. Does MySQL 3.22.32 have a bug in this area, and if so, is it fixed in later versions ? I've compiled MySQL with large file 64-bit support, and the filsystem (AdvFS) has no problems with large files. l8r/Jspr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
tab in mysqlshell
Hey I just upgraded to mysql 3.23.32 but now i can't use the tab in the mysqlshell (like in you do in various unix shells to get a list of valid names, etc.). How do I enable it in again ? // Jesper
Installing MySQL on a WIN ME/WIN2K system
Hi Mailing-list. I'm about to kill myselfplease help. My problem: I'm tryin to install mysql-3.23.30-gamma-win on a dualboot system. Actually I want to install the db as a service under WIN2K running IIS5, but when I run mysqld-nt --install from d:\mysql\bin I get the message "Failed to install service". I'm logged on as admin. I picked d:\mysql as my installation dir. My system: OS: Win Me on c-partition Win2K on d-partition Thunderbird 900 mhz 128 mb Ram G400 WD Expert 9.1 gb Any comment is very much appreciated Kindly Jesper Moeller, Denmark
