Re: How to get auto Increment ID of INSERT?
LAST_INSERT_ID() returns the latest ID for the current connection. As long as you yourself can guarantee that no other queries are executed using that connection, you're fine. If another record others is inserted using another connection, that connection will return a different LAST_INSERT_ID(). Best, / Carsten On 08-10-2015 15:48, Richard Reina wrote: If I insert a record into a table with an auto increment ID how can I get that records ID value? I have read about SELECT LAST_INSERT_ID() statement, however, do not two statements introduce the risk that another insert may occur in the interum? Is there a full proof way of getting the ID of the record that you have just inserted? Thanks -- 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
On 28-06-2014 19:11, Tim Dunphy wrote: Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following 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 'after model' at line 1 Try: alter table car_table modify column color varchar(10) after model; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: error 29, file not found (errcode: 13)
Or use the LOCAL diective to have the client send the csv file contents to the server. / Carsten On 23-06-2014 16:59, Scott Helms wrote: I generally drop them into /tmp for easy access and cleanup after the data load, but you can put them any place that the mysql daemon process has access to read. Scott Helms Vice President of Technology ZCorum (678) 507-5000 http://twitter.com/kscotthelms On Mon, Jun 23, 2014 at 10:52 AM, thufir hawat.thu...@gmail.com wrote: Apparently this error is because MySQL can't read my home directory? Fair enough, but I don't quite follow. Where would be a good location for the CSV file, then? thufir@dur:~$ thufir@dur:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 62 Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql mysql LOAD DATA INFILE '/home/thufir/make_year_model.csv' INTO TABLE vehicles.vehicles FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; ERROR 29 (HY000): File '/home/thufir/make_year_model.csv' not found (Errcode: 13) mysql mysql quit Bye thufir@dur:~$ thufir@dur:~$ cat /home/thufir/make_year_model.csv make1,model1,2012,604,buy now make2,model2,2013,780,need to sell make3,model3,2001,780,cheap thufir@dur:~$ thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help with cleaning up data
On 29-03-2014 19:26, william drescher wrote: I am given a table: ICD9X10 which is a maping of ICD9 codes to ICD10 codes. Unfortunately the table contains duplicate entries that I need to remove. ... I just can't think of a way to write a querey to delete the duplicates. Does anyone have a suggestion ? http://bit.ly/1hKCVHi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Questions about building a dedicated MySQL server.
On 17-03-2014 16:21, Mister Vlad wrote: I am looking at building a dedicated MySQL server... was wondering about the downside to using SSD drives? My thoughts was going 2 servers, with 4 drives each in raid 5 (3+1) configuration. Is this a good idea? I was originally thinking about going Raid5(3+1) and Raid 1 (Mirrored) but that might a little overkill? Now, for the CPU, is a single 8core sufficient? what about Ram? 16gb? 32gb? This is going to have a lot of writes, fewer updates, and a lot of searching... the databases are about 2GB each, and they are monthly created (basically for storing stats). Typically, only the current, and past 2-3 months are accessed, and the others are just there for archival purposes. My reasoning for going SSD over 15k drives was speed. The lookups would (should) be faster, or so I would think. What are your thoughts about this? is this a good idea? do you have better idea? The only thing on this server would be MySQL, the stats database, and a couple others that are used daily, but not nearly as active as the stats db. Thanks for any and all help! MV. With this information, with 16GB RAM all your live data will easily fit in RAM at any given time - read speed will not be dependent on the disks once the data is loaded. Write speed might. lots of writing - what is that? 1000 10kB inserts/sec? 100 1MB inserts/sec? Best, / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql into outfile problem
If you're doing this from the cmd-line client, try running it using --quick. Best, / Carsten On 19-02-2014 09:03, Machiel Richards wrote: Hi guys I am hoping that someone might have experienced this before or might know why we are getting this. We regularly need to run some queries and export the results to a csv file. However we seem to be experiencing the following issues: - when we run an explain on the query it shows that it is using indexes and the amount of rows it accesses is about 165000 rows out of a 90mil+ rows table - When we run the query however and output to a file, it takes about 10-15 minutes to start writing to the file, then once it starts, it writes 28Mb to the file, then it waits again for another 10-15 minutes, and writes another 28Mb's and so it continues until it eventually completes. for the amount of records and the fact that it uses indexes, this should be running quite fast, however we cant seem to figure out this behaviour. Can anyone perhaps assist me with this as the help woul dbe greatly appreciated. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql into outfile problem
I don't know what you mean by straight mysql connection. At any rate, the idea is to use --quick or otherwise using a connection which uses mysql_use_result over mysql_store_result. http://dev.mysql.com/doc/refman/5.6/en/mysql.html Best, / Carsten On 19-02-2014 12:02, Machiel Richards wrote: Hi, the queries are done by connecting to the database using mysql workbench or otherwise after ssh to server by using straight mysql connection. regards On 19/02/2014 12:51, Carsten Pedersen wrote: If you're doing this from the cmd-line client, try running it using --quick. th Best, / Carsten On 19-02-2014 09:03, Machiel Richards wrote: Hi guys I am hoping that someone might have experienced this before or might know why we are getting this. We regularly need to run some queries and export the results to a csv file. However we seem to be experiencing the following issues: - when we run an explain on the query it shows that it is using indexes and the amount of rows it accesses is about 165000 rows out of a 90mil+ rows table - When we run the query however and output to a file, it takes about 10-15 minutes to start writing to the file, then once it starts, it writes 28Mb to the file, then it waits again for another 10-15 minutes, and writes another 28Mb's and so it continues until it eventually completes. for the amount of records and the fact that it uses indexes, this should be running quite fast, however we cant seem to figure out this behaviour. Can anyone perhaps assist me with this as the help woul dbe greatly appreciated. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: hypothetical question about data storage
On 30-07-2013 01:16, Rick James wrote: Elevator... If the RAID _controller_ does the Elevator stuff, any OS optimizations are wasted. And there have been benchmarks backing that up. (Sorry, don't have any links handy.) RAID 5/10 ... The testing I have done shows very little difference. ...right up to the day one of the disks fail, and you thought you could just plug in a new spindle and let the system take care of the rest... http://www.miracleas.com/BAARF/ http://www.miracleas.com/BAARF/RAID5_versus_RAID10.txt / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?
This may be a naive question, but I'm not sure I can see you've covered this: Have you tried USE logs before DROP TABLE `#sql-ib203` (without the logs/ bit)? / Carsten On 19-06-2013 21: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`: ERROR 1050: Table 'logs/#sql-ib203' already exists SQL Statement: ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source` DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051. Unknown table 'logs.logs/#sql-ib203', Error Code: 1051. Unknown table 'logs./#sql-ib203', Error Code: 1051. Unknown table 'logs.#sql-ib203' and Error Code: 1051. Unknown table 'logs.sql-ib203'). Interestingly none of these error messages display 'logs/#sql-ib203', which is the table name MySQL complains it exists when I try to do ALTER. I use innodb_file_per_table. There was a mysql_datadir/logs/#sql-ib203.ibd file (or maybe .frm, sorry I forgot) that I deleted. Any idea how to get rid of this ghostly table `logs/#sql-ib203`? I use MySQL 5.6.12-winx64 and InnoDB. Thanks, Franck Dernoncourt fran...@mit.edu http://francky.me -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Audit Table storage for Primary Key(s)
On 30-05-2013 09:27, Neil Tompkins wrote: Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil First you need to ask yourself how you expect to use the table in the future. Will you be looking up the data on a regular basis? Or will lookups only be something you will do in exceptional situtions? What is the intended goal of having a UUID for the primary key rather than, say, an integer - or having no PK at all? My immediate thought when reading this was why even store that data in a table? - if it's a simple log, use a log file. Especially if you don't know how you intend to search for data later on. There are many tools that are far superior to SQL when it comes to searching for text strings. You could even consider having a CSV table, which will give you an SQL interface to said text file. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Audit Table storage for Primary Key(s)
Again: Unless you can give some idea as to the kind of lookups you will be performing (which fields? Temporal values? etc.), it is impossible to give advice on the table structure. I wouldn't blame anyone for not being able to do so; saving data for debugging will always be a moving target and almost by definition you don't know today what you'll be looking for tomorrow. That's why I think that using CSV tables _the contents of which can subsequently be analyzed using any of a number of text file processing tools_ may indeed be your best initial option. On UUIDs vs. INTs: (1) Please do yourself a favor and read up on how UUIDs are generated. If it's the same server that generates all the UUIDs, you won't get a lot of uniqueness for the amount of space you'll be using for your data and index; (2) Please do the math of just how many inserts you can do per second over the next 1.000 years if you use a longint auto-increment field for your PK. / Carsten On 31-05-2013 11:14, Neil Tompkins wrote: Thanks for your response. We expect to use the Audit log when looking into exceptions and/or any need to debug table updates. I don't think a CSV table would be sufficient as we are wanting to use a interface to query this data at least on a daily basis if not weekly. I use UUID because we have currently 54 tables, of which probably 30 will be audited. So a INT PK wouldn't work because of the number of updates we are applying. On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen cars...@bitbybit.dkwrote: On 30-05-2013 09:27, Neil Tompkins wrote: Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil First you need to ask yourself how you expect to use the table in the future. Will you be looking up the data on a regular basis? Or will lookups only be something you will do in exceptional situtions? What is the intended goal of having a UUID for the primary key rather than, say, an integer - or having no PK at all? My immediate thought when reading this was why even store that data in a table? - if it's a simple log, use a log file. Especially if you don't know how you intend to search for data later on. There are many tools that are far superior to SQL when it comes to searching for text strings. You could even consider having a CSV table, which will give you an SQL interface to said text file. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Audit Table storage for Primary Key(s)
Based on the little information available, I would make a lookup field consisting of tablename and primary keys. (although I still believe that storing this information in the database in the first place is probably the wrong approach, but to each his own) / Carsten On 31-05-2013 12:58, Neil Tompkins wrote: The kind of look ups will be trying to diagnose when and by who applied a update. So the primary key of the audit is important. My question is for performance, should the primary key be stored as a indexed field like I mentioned before, or should I have a actual individual field per primary key On Fri, May 31, 2013 at 11:03 AM, Carsten Pedersen cars...@bitbybit.dk mailto:cars...@bitbybit.dk wrote: Again: Unless you can give some idea as to the kind of lookups you will be performing (which fields? Temporal values? etc.), it is impossible to give advice on the table structure. I wouldn't blame anyone for not being able to do so; saving data for debugging will always be a moving target and almost by definition you don't know today what you'll be looking for tomorrow. That's why I think that using CSV tables _the contents of which can subsequently be analyzed using any of a number of text file processing tools_ may indeed be your best initial option. On UUIDs vs. INTs: (1) Please do yourself a favor and read up on how UUIDs are generated. If it's the same server that generates all the UUIDs, you won't get a lot of uniqueness for the amount of space you'll be using for your data and index; (2) Please do the math of just how many inserts you can do per second over the next 1.000 years if you use a longint auto-increment field for your PK. / Carsten On 31-05-2013 11 tel:31-05-2013%2011:14, Neil Tompkins wrote: Thanks for your response. We expect to use the Audit log when looking into exceptions and/or any need to debug table updates. I don't think a CSV table would be sufficient as we are wanting to use a interface to query this data at least on a daily basis if not weekly. I use UUID because we have currently 54 tables, of which probably 30 will be audited. So a INT PK wouldn't work because of the number of updates we are applying. On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen cars...@bitbybit.dk mailto:cars...@bitbybit.dkwrote: On 30-05-2013 09:27, Neil Tompkins wrote: Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil First you need to ask yourself how you expect to use the table in the future. Will you be looking up the data on a regular basis? Or will lookups only be something you will do in exceptional situtions? What is the intended goal of having a UUID for the primary key rather than, say, an integer - or having no PK at all? My immediate thought when reading this was why even store that data in a table? - if it's a simple log, use a log file. Especially if you don't know how you intend to search for data later on. There are many tools that are far superior to SQL when it comes to searching for text strings. You could even consider having a CSV table, which will give you an SQL interface to said text file. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help with mysql db names
On 19.04.2013 06:49, Kapil Karekar wrote: snip Though I would recommend not using such names. Some poor guy working on your application six months down the line is going to wonder why his queries are failing, spend a day trying to figure out and will post the same question again to this list :-) ...not to mention the many query generator tools that might come into use, which will fail because they do not consistently use back-ticks. NEVER use identififers - database or otherwise - that start with a digit. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Best design for a table using variant data
You don't specify how many different types (including min/max values) you expect to be using. If you expect to end up with a few hundred, then you should perhaps consider using an ENUM or SET column directly in the data table. / Carsten On 10.08.2012 10:51, Gaston Gloesener wrote: Hello, I am currently facing a design where a table (virtually) needs to store attributes of a topic (related table). The attributes can be user defined, i.e. not known at development type and depend on other factors. Each attributes value can be one of different types (int, int64, double, string) and may have constraints like min, max or length (string). Thus the data type would be modeled as variant in some programming languages, but this is not an option in SQL (beside the MS SQLserver sql_variant extension). So, how to simulate this in SQL. Basically there would be one table describing the attributes type (Type identifier, min/max,.) and one table for the values itself. The design I am currently thinking of would be to make exactly these two tables, with the attributes having a Dataype column and iMin,iMax for integer, i64Min, i64Max, fMin,fMax for double , sMinLen, sMaxLen for strings. The same applies to the value table which will have iValue, i64Value, fValue, string columns to hold the actual data. Now the columns will be filled according to the data type, columns not matching the type will be NULL. This means that each row in the table will have virtual space for any data type which violates database normalization. However it seems to me to be the best deal for performance and data space as NULL takes virtually no room (4/8 bytes in total for a number of fields in some circumstances) and requires no complex queries. One could also imagine to have the constraints moved to a separate table and interpreted according to the data type. Also a table for each type could be imagined but this will make the queries very complicated working against performance. Note: The model has to work for huge databases Anybody has a better alternative ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Fwd: Query take too long time - please help!
On 10.07.2012 13:16, Darek Maciera wrote:
2012/7/10 Ananda Kumar anan...@gmail.com:
can u show the explain plan for your query
Thanks, for reply!
Sure:
mysql EXPLAIN SELECT * FROM books WHERE LOWER(ksd)=LOWER('4204661375');
That's definitely not the query you showed the first time around. The
query you're showing here will force a table scan to calculate
LOWER(ksd) for every single row.
Also, how do you know that ksd id unique (as stated in your original
post)? You have no index on it to ensure uniqueness.
You'll have to find some other way to query the table.
Best,
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL slowlog - only in file?
Alternatively, you can copy the data into another table easily: http://www.bitbybit.dk/carsten/blog/?p=115 Best, / Carsten On 14.05.2012 09:34, P.R.Karthik wrote: Hi Rafal, If there are more slow queries in your server and logging them into a table will increase the IO of the server. It is better to be in a file. The slow query log file can be processed easily by pt-query-digesthttp://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html . Regards, KarthiK.P.R MySQL DBA On Fri, May 11, 2012 at 2:47 PM, Nilnandan Joshinilnan...@gmail.comwrote: Hi Rafal, If you are using MySQL 5.1 and later version than you can enable the log tables and you can see slow queries in the log tables. Please check this post: http://nilinfobin.com/2012/03/slow_log-and-general_log-tables-in-mysql-5-1/ regards, Nilnandan On Fri, May 11, 2012 at 2:40 PM, Rafał Radeckiradecki.ra...@gmail.com wrote: Hi all. Is there a possibility to see the info from slowlog somewhere in database? I would like to see slow queries using mysql and not by watching the log file. I've searched on google and mysql website but hasn't found the solution. Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mySQL Query and Report Builder
On 30.04.2012 18:53, Don Wieland wrote: Hello, I have a client who needs the ability to do statistical reporting on their mySQL db data. Is there an app that provides an easy UI that will allow my client to build a line item query, specify fields to be include in the result of the query, and then design the way the data will be exported or printed? Graphs would be nice, too. I know I can build this from scratch, but would rather get a hold of something already pre-built (open source or shareware) to save me a bunch of coding time. If the client uses MS Office, there's nothing stopping you from using Excel or Access. Just create a read-only user on the tables they need to view, and set up the client with Connector/ODBC. Best, / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Questions about Mysqldump
On 15-09-2011 10:31, Chris Tate-Davies wrote: Adarsh, 1) When restoring a mysqldump you have the option of which database to restore. mysql database1 backup.sql Admittedly, it's been a few years since I last used mysqldump, but I suspect that it will contain USE commands - as such, it will restore to whatever database data was dumped from. You'll want to have --one-database on the cmd line too. 2) You might be able to use the --ignore-table command. I'm not sure if this would work mysqldump --all-databases -q --single-transaction --ignore-table=databasetoignore.* | gzip /media/disk-1/Server11_MysqlBackup_15September2011/mysql_15sep2011backup.sql.gz or create a short script that asks mysql for all databases, greps away those you don't want to dump, and runs mysqldump on the rest. / Carsten 3) The docs are here for mysqldump, might be worth a read: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html HTH, Chris On 15/09/11 06:29, Adarsh Sharma wrote: Dear all, Today i backup my all databases (25) by using the below command :- mysqldump --all-databases -q --single-transaction | gzip /media/disk-1/Server11_MysqlBackup_15September2011/mysql_15sep2011backup.sql.gz Now I have some doubts or problems that I need to handle in future : 1. Is there any option in restore command ( I use mysql backup.sql ) to store only specific 1 or 2 databases out of this big backup file. 2. While taking mysqldump of all databases , is there any way to leave specific databases , I know there is --databases option , but we have to name other 23 databases then. 3. What are the settings that are need to changed in my.cnf to make backup restore faster. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update on inner join - looks good to me, where did I go wrong?
`userTable.userid` = `userTable`.`userid` / Carsten On 09-09-2011 23:01, Dotan Cohen wrote: Now that I've got the syntax right, MySQL is complaining that a field does not exist, which most certainly does: mysql UPDATE - `userTable` - INNER JOIN `anotherTable` -ON `userTable.userid`=`anotherTable.userid` - SET `userTable.someField`=Jimmy Page - WHERE `userTable.someField`=Jim Morrison -AND `anotherTable.date` NOW(); ERROR 1054 (42S22): Unknown column 'userTable.someField' in 'field list' mysql mysql SELECT count(someField) FROM userTable; +---+ | count(someField) | +---+ | 5076 | +---+ 1 row in set (0.00 sec) mysql What could be the issue here? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE does not work on calculated view field
On 22.04.2011 21:37, Daniel Kraft wrote:
Hi all,
I'm by no means a (My)SQL expert and just getting started working with
VIEWs and stored procedures, and now I'm puzzled by this behaviour:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTEGER UNSIGNED NULL,
PRIMARY KEY (`ID`));
INSERT INTO `mytable` (`Type`) VALUES (NULL);
CREATE TABLE `types`
(`ID` SERIAL,
`Name` TEXT NOT NULL,
PRIMARY KEY (`ID`));
INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
DELIMITER |
CREATE FUNCTION `EMPTY_STRING` (value TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF(value IS NULL, '', value);
END|
DELIMITER ;
CREATE VIEW `myview` AS
SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
FROM `mytable` a
LEFT JOIN `types` b ON a.`Type` = b.`ID`;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
(I tried to simplify my problem as far as possible.) When I run this
against MySQL 5.0.24a, I get three times 0 as output from the SELECTs
at the end -- shouldn't at least one of them match the single row?
(Preferably first and third ones.)
What am I doing wrong here? I have no clue what's going on... Thanks a lot!
Hint: What's the output of SELECT * FROM `myview`?
/ Carsten
--
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 does not work on calculated view field - Found word(s) list error in the Text body
On 22.04.2011 22:41, Larry McGhaw wrote: It does appear to be some type of bug to me. I agree. I was thrown by Daniels first and third comment, which I guess should read second and third I reproduced the behavior in 5.1.53-community on Windows. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment by more than 1
Den 23-02-2011 18:41, Jim McNeely skrev: Is there a way to set the auto-increment for a particular table to increase by some number more than one, like maybe 10? Thanks in advance, Jim McNeely CREATE TABLE t ( ... ) AUTO_INCREMENT=10; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CURRENT insert ID
Seeing from later posts that you're using InnoDB, why don't you simply wrap the INSERT/UPDATE into a transaction? You don't avoid the UPDATE, but I'm not sure I understand the need to mess w/ triggers. BEGIN INSERT INTO t(id) NULL UPDATE t SET xxx=last_insert_id() COMMIT Best, / Carsten Den 21-01-2011 17:41, Jerry Schwartz skrev: Here it is in a nutshell: I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don’t know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE? Here’s a more concrete description of the problem: CREATE TABLE t ( id INT(11) AUTO-INCREMENT PRIMARY, xxx INT(11) ); When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.) Is there anything clever I can do? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail:mailto:je...@gii.co.jp je...@gii.co.jp Web site:http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CURRENT insert ID
ehr... Den 23-01-2011 15:36, Carsten Pedersen skrev: Seeing from later posts that you're using InnoDB, why don't you simply wrap the INSERT/UPDATE into a transaction? You don't avoid the UPDATE, but I'm not sure I understand the need to mess w/ triggers. BEGIN INSERT INTO t(id) NULL UPDATE t SET xxx=last_insert_id() UPDATE t SET xxx=i WHERE i=last_insert_id() obviously. Sorry. Best, / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: unauthenticated user | while load testing
Usually this is caused by DNS-based authentication, where the reverse-DNS lookups are hanging for one reason or another. If you can, switch to IP-based authentication and use --skip-name-resolve. / Carsten On 05.01.2011 08:26, Yogesh Kore wrote: Hi, What is unauthenticated user seen in mysql processlist ? I am doing load testing and at a single time more than 1000 users are coming to mysql server. Do any one have idea about it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqlimport doesn't work for me
It's been a long time sine I used mysqlimport, but you might want to try: - using --fields-terminated-by rather than --fields-terminated - losing (or escaping) the backticks in --columns= - checking my.cnf to see if the client settings are the same for mysql and mysqlimport - checking user privileges (are you using the same account in both instances?) - checking the line delimiter and --lines-terminated-by FWIW, I always prefer tab-delimited files over comma-separated ones. This gets around a lot of i18n issues. / Carsten Den 03-01-2011 19:33, Jerry Schwartz skrev: sigh This works: localhostTRUNCATE t_dmu_history; Query OK, 0 rows affected (0.41 sec) localhostLOAD DATA LOCAL INFILE 't_dmu_history.txt' INTO TABLE t_dmu_history FIELDS TERMINATED BY , (`dm_history_dm_id`,`dm_history_customer_id`); Query OK, 876211 rows affected (25.16 sec) Records: 876211 Deleted: 0 Skipped: 0 Warnings: 0 localhostSELECT * FROM t_dmu_history LIMIT 4; +--+--++ | t_dmu_history_id | DM_History_DM_ID | DM_History_Customer_ID | +--+--++ |1 |13071 | 299519 | |2 |13071 | 299520 | |3 |13071 | 299521 | |4 |13071 | 299522 | +--+--++ 4 rows in set (0.03 sec) This does not work: localhostTRUNCATE t_dmu_history; localhostquit C:\Users\Jerry\Documents\Access MySQL Productionmysqlimport --columns=`dm_history_dm_id`,`dm_history_customer_id` --fields-terminated=',' --local --password=xxx --pipe --user=access --verbose maintable_usa t_dmu_history.txt Connecting to localhost Selecting database maintable_usa Loading data from LOCAL file: C:/Users/Jerry/Documents/Access MySQL Production/t_dmu_history.txt into t_dmu_history maintable_usa.t_dmu_history: Records: 876211 Deleted: 0 Skipped: 0 Warnings: 1752422 Disconnecting from localhost localhostSELECT * FROM t_dmu_history LIMIT 4; +--+--++ | t_dmu_history_id | DM_History_DM_ID | DM_History_Customer_ID | +--+--++ |1 |13071 | NULL | |2 |13071 | NULL | |3 |13071 | NULL | |4 |13071 | NULL | +--+--++ 4 rows in set (0.00 sec) = Before you ask, the mysql CLI is also using a named pipe. Windows Vista 32-bit MySQL version 5.1.31-community Mysqlimport Ver 3.7 Distrib 5.1.31, for Win32 (ia32) What am I missing? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql_upgrade fails with Access denied
Den 28-11-2010 21:02, Grant skrev: I'm trying to run mysql_upgrade but I get: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect FATAL ERROR: Upgrade failed I've also tried specifying my root password with --password and also specifying -u mysql but it still fails with error 1045. My /etc/passwd file looks like there is no password for user mysql. What else should I try? You should use the password for the _MySQL_ root user, which is _not_ stored in /etc/passwd, but within MySQL. / Carsten -- 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 vs postgresql -- is this list accurate?
On Mon, 6 Sep 2010 06:36:02 -0400 (EDT), Robert P. J. Day rpj...@crashcourse.ca wrote: no, i don't want to start a flame war, i just want some feedback on a current list of mysql drawbacks WRT postgresql. in the context of a fully open-source, java based ECM product, there is a FAQ entry that summarizes why the developers would prefer their users to use postgresql as opposed to mysql: http://www.nuxeo.org/xwiki/bin/view/FAQ/WhyAvoidMySQL There are a few odd criticisms, such as the limitation on VARCHAR fields (haven't they discovered TEXT/BLOB types?). Also, some of their expectations seem very specific to their own implementation (I've personally never had to do 15 levels of cascade delete). But within the particular context they describe, I'd say that overall they are being fair. Many, many other CMSs are very succesful with MySQL implementations, so if you already have a marked preference for working with MySQL, you might want to look at other products before making a decision. Having read that page, I know I would. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Dup Key Error Messages
On Mon, 6 Sep 2010 15:02:24 +0200, Thorsten Heymann heym...@macnetix.de wrote: Digging through mysqld source, I found this behaviour handled in sql/handler.cc and changed from printing key_nr to key.name between this versions. :( Is there a possible better, reliable way to detect what key is duplictated as searching in the error message? Not really. And to make matters worse, this isn't just a version issue. You'd have the same type of problems if your app connects to a server installed with a non-Enlish language pack. The only reliable thing I can think of is to detect that you got an error 1062, then do a db search to find out which field or fields have duplicate information. Cumbersome, but OTOH you'd also get a chance to realize if more than one key field is duped. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Altering database size to add more space
On Fri, 25 Jun 2010 06:31:11 -0500, Jim Lyons jlyons4...@gmail.com wrote: I think you're confusing table size with data base size. The original post grouped by schema so it appears the question concerns database size. I don't believe mysql imposes any limits on that. Is there a limit on the number of tables you can have in a schema imposed by mysql? Not by MySQL. On some file systems, there's a practical limit of ~10k tables/database. At that point, looking up directory entries can cause slowdowns. / Carsten -- 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 1064
Brad Scott skrev: Any line beginning with just a number (ie 9, 10, 16) causes a failure. What am I missing? backticks. Use `9`, `10`, etc. Having column names that begin with numbers is a really bad design decision. 9a123 (unquoted, of course) will work, as you've noticed, but e.g. 0x123 is going to give you a nasty surprise. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Array data type
There are SETs and ENUMs, but I've always found that dealing with them is annoying. YMMV / Carsten On Fri, 14 May 2010 13:54:29 +0530, Samrat Kar esam...@barc.gov.in wrote: Hello, How to store multiple values in a single field? Is there any array data type concept in mysql? Regards, Samrat Kar FRD, BARC Tel: 022-25597295 Alternate Email: esam...@yahoo.com !DSPAM:451,4bed08e0408231671817791! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can't create foreign key
Haven't done this in a while, but I'm guessing that you can't create both a constraint and an index with the same name? Type mismatch will in my experience most often generate an errno 150. / Carsten j...@msdlg.com skrev: I'm trying to create a foreign key by executing the following statement: ALTER TABLE `cc`.`takenlessons` ADD CONSTRAINT `fk_lessons` FOREIGN KEY (`LessonID` ) REFERENCES `cc`.`lessons` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION , ADD INDEX `fk_lessons` (`LessonID` ASC) ; I'm using the RC of MySQL workbench to do this. When I execute this statement, I get the following error: Error Code: 1005 Can't create table 'cc.#sql-115c_61' (errno: 121)) In the past when I got a similar error, it was because the fields didn't match exactly. For instance, one may be Int(10) and one Int(11), or one may be Unsigned, and the other not. But, in this case, both match exactly. What other reasons are there for a foreign key creation to fail like that? Thanks, Jesse !DSPAM:451,4bed85fe818443309765824! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Out of range value for column 'datestamp' at row 1
Gving the full error message would be helpful. Check that the sql mode settings for ALLOW_INVALID_DATE, NO_ZERO_DATE and NO_ZERO_IN_DATE are the same on both master and slave. / Carsten Prabhat Kumar skrev: Hi, I have setup replication between 2 servers, on both there is different versions of mysql. Master :5.0.67-log Slave : 5.1.43sp1-enterprise-gpl-advanced-log There is table: CREATE TABLE `myTable_info` ( `id` int(11) NOT NULL auto_increment, `range` varchar(255) NOT NULL, `total_qt` smallint NOT NULL default '0', `qt_correct` smallint NOT NULL default '0', `finish_time` smallint NOT NULL default '0', `username` varchar(100) NOT NULL, * `datestamp` datetime NOT NULL default '-00-00 00:00:00',* PRIMARY KEY (`id`) ) ENGINE=InnoDB ; During replication insert on slave error caused . INSERT INTO myTable_info (id,range, total_qt, qt_correct, finish_time, username, datestamp) VALUES (NULL,'Kumar', '20', '17', '111', 'Prabhat','* NOW()');* Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax I think problem with* `datestamp` datetime NOT NULL default '-00-00 00:00:00',* Can any one please suggest me, how to deal with this error. -- 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
Keith Clark skrev:
I have the following statement:
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 Balance asc;
and I'd like the output to be sorted by the Balance according to the
numberic value, but it is sorting by the string result. I tried
abs(Balance) but I get the following error:
1247 Reference 'Balance' not supported (reference to group function)
I'm not sure I understand the error.
Balance is the result of a string operation (concat), and abs is a
numeric function that won't work on strings.
You should add a field to the result with the numerical value of
Balance, and then sort on that.
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto Increment in InnoDB
On Thu, 22 Apr 2010 13:12:16 +0200, Johan De Meersman vegiv...@tuxera.be wrote: Kudos for managing to drag up such an obscure piece of functionality :-) I can see where it would be useful, though. As to your question, though: given that that page indicates that it will reuse deleted sequence numbers, I think your best bet would be select @id := count(*)+1 from table where cluster='clusterA' AND file='fileA' ; - should be slightly faster than a max(), I think. That in a trigger on your table should emulate the behaviour pretty closely. Wouldn't that strategy cause problems if one or more rows have been deleted in the meantime? (i.e. sequence numbers 1-4 have been created, row 2 has been deleted - new sequence number would be 4). / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Grants for own objects ?
Lentes, Bernd skrev: hello ML, i'm new to MySQL, so i have a very basic question. I have to install a database server for about 15 persons. The server is intended for testing and evaluating. The users should be able to create their own databases and tables.. And they should be able to give grants on their own objects to other users. Following the recommendation in a MySQL-Book, i inserted the following line in the db table: [...] localhost | lentes\_% | lentes | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y| Y | Y | Y | Y [...] That means that the user lentes can create databases, where the name begins with lentes_ . This works. Being logged in as lentes, i'm able to create a databse called lentes_1. But i'm not able to give grants to other users: mysql grant select on lentes_1.* to 'eitz'@'localhost'; ERROR 1044 (42000): Access denied for user 'lentes'@'localhost' to database 'lentes_1' Uer lentes has no global privileges. How can i achieve that users are able to create their own databases/tables and to assign grants for their own databases/tables ? First, don't mess around with the grant tables. Many years ago, that was indeed the way to control user access, but things have progressed since then. How old is that MySQL book? Remove the manual edits you have made to the grant tables, and use only GRANT and REVOKE. I believe this will do what you want: CREATE USER lentes@localhost; GRANT ALL ON lentes_%.* TO lentes@localhost WITH GRANT OPTION; Hth, / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Analysis of a weeks worth of general log
Jim Lyons skrev: Has anyone tried using the log_output option in mysql 5.1 to have the general log put into a table and not a flat file? I used it for a while before having to downgrade back to 5.0 but thought it was a great idea. I'm curious to see if anyone feels it helps analysis. I tried that once, and ran into some problems. Depending on your exact version, you might experience the same. http://www.bitbybit.dk/carsten/blog/?p=115 (also has a number of good comments on analysis tools) And yes, having the data available in a table is a Good Thing for analysis. / Carsten On Tue, Apr 20, 2010 at 6:02 AM, Imran Chaudhry ichaud...@gmail.com wrote: I have 7 days worth of general log data totalling 4.4GB. I want to analyze this data to get: a) queries per second, minute, hour and day b) a count of the number of selects versus write statements (delete, insert, replace and update) c) a variation of the above with select, replace, delete and insert versus update How can I do this? I've looked at mysqlsla which is complex, works well but does not quite get what I want. [1] I looked at MyProfi 0.18 which looks like it will get some of the answers but runs out of memory working on the smallest log file (mysql.log) even with memory_limit in php.ini set to 1024MB [2] -rw-r- 1 imran imran 268M 2010-04-19 13:03 mysql.log -rw-r- 1 imran imran 721M 2010-04-19 12:56 mysql.log.1 -rw-r- 1 imran imran 737M 2010-04-19 13:05 mysql.log.2 -rw-r- 1 imran imran 554M 2010-04-19 13:06 mysql.log.3 -rw-r- 1 imran imran 499M 2010-04-19 13:02 mysql.log.4 -rw-r- 1 imran imran 568M 2010-04-19 12:59 mysql.log.5 -rw-r- 1 imran imran 488M 2010-04-19 13:01 mysql.log.6 Any pointers please? If all else fails, I will prolly write a perl script to munge it. [1] http://hackmysql.com/mysqlsla [2] http://myprofi.sourceforge.net -- GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.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: Analysis of a weeks worth of general log
Carsten Pedersen skrev: Jim Lyons skrev: Has anyone tried using the log_output option in mysql 5.1 to have the general log put into a table and not a flat file? I used it for a while before having to downgrade back to 5.0 but thought it was a great idea. I'm curious to see if anyone feels it helps analysis. I tried that once, and ran into some problems. Depending on your exact version, you might experience the same. http://www.bitbybit.dk/carsten/blog/?p=115 (also has a number of good comments on analysis tools) And yes, having the data available in a table is a Good Thing for analysis. / Carsten Minor correction: The post i point to is about the slow log, but I presume also relevant for the general log. And the good comments I mentioned come in the followup posting at http://www.bitbybit.dk/carsten/blog/?p=116 / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What if the user closes the browser while INSERT INTO MySQL? (PHP/MySQL)
http://php.net/manual/en/function.ignore-user-abort.php
/ Carsten
On Fri, 16 Apr 2010 18:39:07 +0900, Antonio PHP php.anto...@gmail.com
wrote:
This maybe a newbie question.
Consider the following concept,
~/index.php
#1. Fetch data from an external webpage using PHP Curl;
#2. Preg_match/Prepare Data to INSERT from local MySQL; - this may take
a
few secs
#3. While Loop { INSERT data (from #2) into local MySQL } - this may
take
only mili secs.
Suppose this code is run by a random user (say, my website visitor), and
he/she closes the browser while the code was running. The real problem
is
when the browser is closed while #3 is executing. Because only portion
of
data is inserted, ~/index.php, and it doesn't know if it needs to visit
the
site again (i.e. repeat from #1 - over visiting the same webpage /
possibility of inaccurate data in local MySQL).
Has anyone come across with a similar problem? Do I need to use other
programming languages like C to execute the code from #2 in order not
to depend upon users' browser status?
Another general question : Is there a way to make sure all the data is
INSERTED in a while loop once it's triggered?
many thanks in advance,
- Anton
!DSPAM:451,4bc835a5518712071889376!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What if the user closes the browser while INSERT INTO MySQL? (PHP/MySQL)
On Fri, 16 Apr 2010 11:44:42 +0200, Jørn Dahl-Stamnes sq...@dahl-stamnes.net wrote: The server does not know if the browser is closed or not (or if the network connection is losted). It will continue to execute the code until finnished. Not quite true. If it decides to flush its output buffer and notices that there's no-one around to receive the output, the process may well be terminated. As long as you do not echo(), print() or flush() to a closed connection, you're probably going to be fine. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Make delete requests without impact on a database
Been there, done that. It's a maintenance nightmare. Another idea: Have a separate deleted table with the IDs of the rows that you consider deleted. Re-write your queries to do a left-join-not-in-the-other-table agains the delete table. Then, either wait for a maintenance window to delete the rows both the original table and the delete table. Or remove just a few rows at a time. The deleted table can be created with just a read lock on the original table, and since it's going to be fairly small, the impact of stuffing it with data is not going to be great. It's a bit of a hassle to set up, but once done you don't have to worry about creating and deleting tables every day. / Carsten mos skrev: It looks like you only want to keep the current data, perhaps the current day's worth, and delete the old data. I would store the data in separate MySIAM tables, each table would represent a date, like D20100413 and D20100414. Your program will decide which table to insert the data into by creating a current date variable and now all data gets inserted to the table named by that variable. When the older data is no longer needed, just drop the table. If you want to keep the last 7 days of data, create a merge table of the last 7 tables. When you drop the oldest table, redefine the merge table. You can accomplish all this in just milliseconds. Mike At 08:08 AM 4/14/2010, you wrote: Hi, I am using MySQL version 4.1.12-log. All the databases on it are using MyISAM database engine. Every day, I delete almost 9 rows on a table of 3 153 916 rows. To delete the rows, I use a request like this : DELETE QUICK FROM [table] WHERE [column] '2010-04-13 00:00:00' LIMIT 7500. I execute this request until all the rows are delete. This works but when I run the request, I can't access to the database (make INSERT and SELECT requests) during I do the DELETE. How can I do a DELETE without impact on INSERT and SELECT requests done on the same time? Regards, David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: numeric types
Check out the DECIMAL type. / Carsten Sebastien MORETTI skrev: Hello, I have a row which is defined as double unsigned (MySQL 5.0.26-Max, OpenSuse). Values in this row can go from a single digit, like 1, to values like 0.0006872207 or 1.2513e-18. I want to store exact numbers. But I would like also this: 1 stored as 1.0 0.098 stored as 0.09800 0.00707 stored as 0.00707 0.0006872207 stored as 0.0006872207 How could I get this ? MySQL stores at least double(6,5) but increases this automatically if the precision required is longer. Thanks -- Sébastien Moretti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Make delete requests without impact on a database
mos skrev: At 01:20 PM 4/14/2010, Carsten Pedersen wrote: Been there, done that. It's a maintenance nightmare. Why is it a maintenance nightmare? I've been using this technique for a couple of years to store large amounts of data and it has been working just fine. In a previous reply, you mentioned splitting the tables on a daily basis, not yearly. Enormous difference. It's one thing to fiddle with a set of merge table once a year to create a new instance. Quite another when it's to be done every day. If you want to change the table structure, you'll have to do that on every single one of the underlying tables. That might be fine for 5 year-tables, but not fun if you need to do it for hundreds of tables. If your merge table consists of 30 underlying tables*, a search in the table will result in 30 separate searches, one per table. Also, MySQL will need one file descriptor per underlying table *per client accessing that table*. Plus one shared file descriptor per index file. So if 30 clients are accessing a merge table that consists of 30 days worth of data, that's 930 file descriptors for the OS to keep track of. Clearly, this doesn't scale well. *Approx 1 month in your suggested solution, which also fits with OP saying that ~90k of about ~3.2 mio get deleted every day. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: DATE_FORMAT parameter question
If you'll excuse the shameless plug: I once created a tool to help find
the exact parameters to use for PHPs date() and MySQLs DATE_FORMAT().
Please see
http://bitbybit.dk/php/date_format/
(Yes, it looks horrible. But it works)
/ Carsten
Martin Gainty skrev:
Good Afternoon All
following the documentation available at
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
i wish to select a date using DD-MON-YY format but my format of '%y-%b-%d'
appears to be incorrect
mysql select DATE_FORMAT('11-10-09','%y-%b-%d') from DUAL;
++
| DATE_FORMAT('11-10-09','%y-%b-%d') |
++
| 11-Oct-09 |
++
1 row in set (0.00 sec)
mysql show variables like %VERSION%;
+-+--+
| Variable_name | Value|
+-+--+
| protocol_version| 10 |
| version | 5.1.25-rc-community-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | ia32 |
| version_compile_os | Win32|
+-+--+
5 rows in set (0.00 sec)
it seems when i follow the documentation which states %d is used for day and %y
is year i see:
mysql select DATE_FORMAT('11-10-09','%d-%b-%y') from DUAL;
++
| DATE_FORMAT('11-10-09','%d-%b-%y') |
++
| 09-Oct-11 |
but if i switch %y and %d in date_format I get the correct result e.g.
mysql select DATE_FORMAT('11-10-09','%y-%b-%d') from DUAL;
++
| DATE_FORMAT('11-10-09','%y-%b-%d') |
++
| 11-Oct-09 |
++
1 row in set (0.00 sec)
any ideas on what I am doing wrong with format string to produce desired
DD-MON-YY format?
Many Thanks,
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni..
_
Hotmail is redefining busy with tools for the New Busy. Get more from your
inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2
!DSPAM:451,4bbf4251775757489286036!
--
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 - Tables Export to Excel!
The MySQL ODBC driver? / Carsten On Thu, 8 Apr 2010 10:42:28 +0530 (IST), Vikram A vikkiatb...@yahoo.in wrote: Hi, I would like to export my table structure from MYSQL from a particular db. Is there any tool for doing this? Please guide me. Thank you VIKRAM A !DSPAM:451,4bbd65f933049495715525! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with installing MySQL
don't have a space between '-p' and 'password', i.e. -ppassword / Carsten alba.albetti skrev: I've just installed MySQL on Windows 2000. I've opened the MS-DOS windows and I've written C:\Programs\MySQL\...\bin\mysqladmin -u root -p password mysql2010 After the enter the prompt says Enter password: and I've given enter and I get mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' After installing MySQL what do I have to do left? Sorry my this is my firt time with MySQL Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SHOW TABLE STATUS
AFAIR, MySQL 4.x supports LIKE, e.g.
SHOW TABLE STATUS LIKE 'tab_%'
/ Carsten
spacemarc skrev:
hi all,
in MySQL 4.1.x i want to obtain the status of more tables with one only query.
In 5.x i use SHOW TABLE STATUS WHERE Name IN ('tab_1', tab_2, 'tab_3')
In 4.1.x i tried to use but it doesn't works: how to set the query?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Converting String to Text in mysql
You can do some trickery with auto increment fields and multiple-column indexes. Have a look at http://www.bitbybit.dk/carsten/blog/?p=131 Beware that this is apparently a MyISAM-specific trick. / Carsten On Tue, 6 Apr 2010 16:02:48 +0530, Suryanarayanan n...@auromiraenergy.in wrote: I am new to mysql and am trying to develop a package in php using mysql as backend database. I have a table in mysql which has a customer code which is alpha numeric. Eg: J0001 and name and address of the customer. The alphabet signifies the first letter of the name of the customer viz. James. I want to increment this code to J0002 when another customer (Jones) details is entered into the system i.e J0003 and so on. Can any enlightened members help me with this code in mysql. This will be used while coding in php to update the database with the entries made online. Looking forth for early replies from friends all over the world. Suryanaryanan !DSPAM:451,4bbb1148499324901218737! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MyISAM better than innodb for large files?
InnoDB won't give you much in terms of disk crash recovery. That's what backups are for. Where InnoDB does excel is if your database server dies while updating rows. If that happens, your database will come back up with sane data. For both table types, once the data has been flushed to disk, the data will still be there if your db server crashes. It does indeed sound like you will be better off using MyISAM. This will also reduce your disk space usage considerably. / Carsten Mitchell Maltenfort skrev: I'm going to be setting up a MySQL database for a project. My reading indicates that MyISAM (default) is going to be better than InnoDB for the project but I want to be sure I have the trade-offs right. This is going to be a very large data file -- many gigabytes -- only used internally, and once installed perhaps updated once a year, queried much more often. MyISAM apparently has the advantage in memory and time overheads. InnoDB's advantage seems to be better recovery from disk crashes. Should I stick with MyISAM (MySQL default), or does the recovery issue mean I'm better off using InnoDB for an insurance policy? Inexperienced minds want to know -- ideally, from experienced minds. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Variable for row number?
Pavel Gulchouck skrev: Hi! Is there any way to get sequence row number in request? I need row number calculated before having but after group by and order, so select @row := @row+1 unsuitable in my case (it executed before grouping). something along the lines of this: mysql select * from t; +--+--+ | c1 | c2 | +--+--+ | 27 |2 | | 27 |3 | | 35 |3 | | 35 |4 | +--+--+ 4 rows in set (0.00 sec) mysql select c1, sum(c2) as s from t group by c1; +--+--+ | c1 | s| +--+--+ | 27 |5 | | 35 |7 | +--+--+ 2 rows in set (0.00 sec) mysql select @a:=0; select @a:=...@a+1, c1, s from (select c1, sum(c2) as s from t group by c1) _d; +---+ | @a:=0 | +---+ | 0 | +---+ 1 row in set (0.00 sec) +--+--+--+ | @a:=...@a+1 | c1 | s| +--+--+--+ |1 | 27 |5 | |2 | 35 |7 | +--+--+--+ 2 rows in set (0.00 sec) Best, / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: dump time progressively increasing with Innodb
OPTIMIZE TABLE sometimes helps, ymmv. http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html / Carsten Nico Sabbi skrev: Hi, I noticed that over the months the dump of my databases (very subject to modifications, but not subject to increase significantly in size) gets progressively slower: from ~8 minutes to almost 15 in 6 months. How can I avoid this degeneration? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: slow queries not being logged
You might want to read the comments to this posting: http://www.bitbybit.dk/carsten/blog/?p=116 Several tools/methods for controlling and analyzing the slow query log are suggested there. Best, / Carsten On Tue, 23 Feb 2010 14:09:30 +0530, Ananda Kumar anan...@gmail.com wrote: slow query log will also have sql's which are not using indexes(doing full table scan). May be those queries with ZERO SECOND run on small table without using indexes. regards anandkl On Tue, Feb 23, 2010 at 2:02 PM, Machiel Richards machi...@rdc.co.zawrote: Hi All I found my problem and this was kind of a blonde moment for me... When configuring the log_slow_queries parameter, it was configured as follows: log_slow_queries=1 This the file being created is called 1 and the 1 does not mean it is enabled. I have fixed this now but need to wait for a gap to reboot again to have it set properly. (have to live with the filename 1 for the time being.) I did however find something interesting though, while looking at the queries being logged. The slow_query_time is set to 2 (2 seconds i am assuming) however all the queries being logged states that it ran for 0 seconds. I am busy doing explain plans on some of them now but not really sure what to look for yet (Rather new to MySQL and hope google will have some answers J ) Thank you From: John Daisley [mailto:mg_s...@hotmail.com] Sent: 23 February 2010 10:24 AM To: machi...@rdc.co.za; mysql@lists.mysql.com Subject: RE: slow queries not being logged From: machi...@rdc.co.za To: mysql@lists.mysql.com Subject: slow queries not being logged Date: Tue, 23 Feb 2010 09:59:13 +0200 Good day all I hope you can assist me with this one... We have a client where the slow query log was disabled. Slow query log is on the server only. If you are saying you have enabled the slow query log and the servers query log is empty can you post your my.cnf/my.ini file. Also make sure --long-query-time is set appropriately. We noticed that the slow query amount (when viewing global status) has skyrocketed during the last 2 weeks going up to over 2 million (from 160 million queries). We wanted to look at these queries to see if it can be optimised to reduce the amount and went through the whole database restart routine to enable the slow query log again (they are running version 5.0 so had to restart). However, even though the slow query log is enabled, it is not logging the queries to the file specified. Can someone please assist in why this is not being done? I thought that it might be logging to a default filename but there is only one slow queries log file in the directory and it is empty. Checking the global status again, it showed 29 000 slow queries since this morning (3 hours ago) but nothing in the logs. Your help will be appreciated. Regards _ Do you want a Hotmail account? Sign-up http://clk.atdmt.com/UKM/go/19780/direct/01/ now - Free !DSPAM:451,4b839535858212076517642! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Duplicate entries despite group by
Is the CREATE TABLE you show the result of SHOW CREATE TABLE or your own
create statement? If the latter, please show the output of SHOW CREATE.
Does SELECT succeed if you remove the INSERT part of the statement?
You might want to consider adding an index on transactionlogid, this
could bring down query time significantly.
/ Carsten
Yang Zhang skrev:
I have the following table:
CREATE TABLE `graph` (
`tableid1` varchar(20) NOT NULL,
`tupleid1` int(11) NOT NULL,
`tableid2` varchar(20) NOT NULL,
`tupleid2` int(11) NOT NULL,
`node1` int(11) NOT NULL,
`node2` int(11) NOT NULL,
`weight` int(10) NOT NULL,
PRIMARY KEY (`tableid1`,`tupleid1`,`tableid2`,`tupleid2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
and I'm running this query (note the 'group by'):
insert into graph (node1, node2, tableid1, tupleid1, tableid2,
tupleid2, weight)
select 0, 0, a.tableid, a.tupleid, b.tableid, b.tupleid, count(*)
from transactionlog a, transactionlog b
where a.transactionid = b.transactionid
and (a.tableid, a.tupleid) {''} (b.tableid, b.tupleid)
group by a.tableid, a.tupleid, b.tableid, b.tupleid
However, after running for a few hours, the query fails with the
following error:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
Duplicate entry 'new_order-248642-order_line-13126643' for key
'group_key'
How is this possible? There were no concurrently running queries
inserting into 'graph'. I'm using mysql-5.4.3; is this a beta
bug/anyone else happen to know something about this? Thanks in
advance.
--
Yang Zhang
http://www.mit.edu/~y_z/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Any faster building primary/unique indexes after Load Data Infile?
Generally, you should find that removing and re-adding the indexes will speed up your operation. I do not believe that ALTER TABLE with just index additions will require a table rebuild, but even if it does, doing a table copy will be a fairly fast operation (much faster than loading from other sources). Don't forget to set the MyISAM sort buffer size high while you create the indexes. / Carsten mos skrev: I am loading 35 million rows of data into an empty MyISAM table. This table has 1 primary key (AutoInc) and 1 unique index and 2 non-unique indexes. Is it going to be any faster if I remove the indexes from the table before loading the data, load the data, then do an Alter Table .. add index for all of the indexes? Or is it faster to just leave the indexes in place prior to loading the data. I know if the table is empty and optimized, the non-unique indexes will be built AFTER the data is loaded using Load Data Infile, but the unique and primary indexes will be built as the data is being loaded and this is going to slow down the import. There is no point doing a Disable Indexes on the table because this only affects non-unique indexes and that is already taken care of since the table is already empty and optimized. But if I remove the indexes from the empty table then load the data, then execute the Alter Table Add Index ... for all 4 indexes at one time, isn't the Alter Table going to create a copy of the table so it is just going to reload the data all over again? Is there any way to add a primary or unique index without copy the data all over again? Create Index ... can't be used to create a primary index. TIA Mike MySQL 5.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
Using multiple columns to hold essentially the same data is generally a bad idea: Business requirements may change over time, forcing you to change both the schema and your programming logic. Better to use a table consisting of username/changedate/password. One year from now, when your boss/customer decides to up the requirement to six passwords, it will be a simple app change. / Carsten Tompkins Neil skrev: Hi I'm in the process of designing a login system to a secure web page using MySQL. One of the features is we need to record and ensure that the user password is different from any of the last four passwords he/she has used. I was thinking of create four fields called Password1, Password2, Password3 and Password4 to record the old passwords. Is this a preferred method - or does anyone else have any recommendations ? Thanks, Neil !DSPAM:451,4b54a9e956471140923725! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Another Inserting Multiple Values with Set Problem
mysql create table t (sizes
set('Extra-small','Small','Medium','Large','XLarge','XXLarge','XXXLarge'),
colorsShadesNumbersShort
set('blue:99','gray:465945','purple:50404D','navy-blue:CC7722','fuchsia:FF77FF','aqua:7FFFD4','maroon:B03060','black:FF','yellow:9ACD32'));
Query OK, 0 rows affected
(0.00 sec)
mysql insert into t values ('Large,Small', 'aqua:7FFFD4,fuchsia:FF77FF');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+-++
| sizes | colorsShadesNumbersShort |
+-++
| Small,Large | fuchsia:FF77FF,aqua:7FFFD4 |
+-++
1 row in set (0.01 sec)
mysql update t set sizes=
'Extra-small,Large,Small,Medium,XLarge,XXLarge,XXXLarge',
colorsShadesNumbersShort=
'aqua:7FFFD4,blue:99,gray:465945,navy-blue:CC7722,black:FF,maroon:B03060,purple:50404D,yellow:9ACD32,fuchsia:FF77FF';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql select * from t\G
*** 1. row ***
sizes:
Extra-small,Small,Medium,Large,XLarge,XXLarge,XXXLarge
colorsShadesNumbersShort:
blue:99,gray:465945,purple:50404D,navy-blue:CC7722,fuchsia:FF77FF,aqua:7FFFD4,maroon:B03060,black:FF,yellow:9ACD32
1 row in set (0.00 sec)
/ Carsten
Victor Subervi skrev:
On Wed, Jan 6, 2010 at 2:40 PM, Michael Dykman mdyk...@gmail.com wrote:
How about you show us the schema for the table so we know what is
defined as what?
Done in last email.
Also, as the update does succeed, it would be interesting to see what
value actually got stored. After you have accounted for each bit in
the stored value, we might have a clue about what is being truncated.
mysql select sizes, colorsShadesNumbersShort from products;
+-+--+
| sizes | colorsShadesNumbersShort |
+-+--+
| Extra-small | blue:99 |
| Extra-small | aqua:7FFFD4 |
| Extra-small | blue:99 |
| | |
+-+--+
4 rows in set (0.00 sec)
Ain't nothin' getting stored.
One thing I did just note: the hyphen in 'Extra-Small'. Set
identifiers need to be valid mysql identifiers and the hyphen '-' is
not a valid identifier character (as it is an arithmatic operator). I
can't imagine that those colons in the colour list are healthy either.
They all work except in a certain case where I had to pull the hyphen out. I
can enter all of these products individually.
The point of a set identifier to be an easy mnemonic for a particular
bit value. Nothing is gained by trying to represent data with the
identifier itself.
Huh? Please explain.
V
!DSPAM:451,4b44da73427881287616796!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
Is there any particular reason not to use the MySQL ODBC driver to import the data directly into Excel? / Carsten Jim Lyons skrev: A command to convert the table mytab in database mydb into a tab-delimited file mytab.txt might be: mysql -e'select * from mydb.mytab' -sss mytab.txt The -sss is necessary to remove all the formatting stuff that you normally have in the output of a select statement. An alternative, if you have a directory *** that mysql can write to ***: mysqldump --tab=/home/mysql/temp mydb mytab This will create 2 files in /home/mysql/temp: mytab.txt and mytab.sql. The one you'll want is in mytab.txt. Then use what transfer tool you have, ftp, scp, winscp, rsync to transfer the file to another server. On Tue, Jan 5, 2010 at 7:31 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help keep the Internet free
I would encourage everyone on this list to at least read the two articles Fox in the henhouse (http://helpmysql.org/en/theissue/foxinthehenhouse) and GPL is not the answer (http://helpmysql.org/en/theissue/gplisnottheanswer) You may or may not agree with those, but at least then you'll have made an educated decision whether to sign the petition or not. (Disclaimer: I'm a former employee of MySQL AB/Inc, but left before MySQL was bought up by Sun). / Carsten Michael Widenius skrev: Hi! We have just launched a worldwide, multilingual petition at http://helpmysql.org to get signatures to show the regulators in the EU and other places that it's important that MySQL continues to be available and developed as a strong Open Source product for all database needs. If you care about the future of MySQL as an Open Source product, please go and sign the petition and tell others about it! We are also searching for volunteers that can help us with gathering names for the petition. If you have a bit a spare time and think that MySQL is worth saving, please join us on the #helpmysql IRC channel on Freenode and help us with spreading the world and gather names! You can make a real difference, wherever you are in the world! The more names we are able to get, the higher the chance is that we can keep MySQL free and available for all! Don't be fooled by the empty promises Oracle have given about the future of MySQL. I examined them in my previous blog post at http://monty-says.blogspot.com/2009/12/oracle-gives-only-empty-promises-fo r.html and showed that Oracle is not really promising anything; Not even that the Open Source version of MySQL will be developed further. Thanks to everyone that helped us with the previous campaign. It was of great help, but probably not enough, which is why we had to start this new campaign to gather even more names. This time we will be able to use the names to influence decisions worldwide (not just in the EU) and we will get more public attention. This will be far more powerful. During the previous campaign there was a lot of discussions in different medias about my incentives for driving it. I have tried to answer most of these concerns in my latest blogpost: http://monty-says.blogspot.com/2009/12/help-keep-internet-free.html Help us keep the Infrastructure of the Internet free! Regards, Monty Creator of MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help keep the Internet free 6D7-36A
I would encourage everyone on this list to at least read the two articles Fox in the henhouse (http://helpmysql.org/en/theissue/foxinthehenhouse) and GPL is not the answer (http://helpmysql.org/en/theissue/gplisnottheanswer) You may or may not agree with those, but at least then you'll have made an educated decision whether to sign the petition or not. (Disclaimer: I'm a former employee of MySQL AB/Inc, but left before MySQL was bought up by Sun). / Carsten Michael Widenius skrev: Hi! We have just launched a worldwide, multilingual petition at http://helpmysql.org to get signatures to show the regulators in the EU and other places that it's important that MySQL continues to be available and developed as a strong Open Source product for all database needs. If you care about the future of MySQL as an Open Source product, please go and sign the petition and tell others about it! We are also searching for volunteers that can help us with gathering names for the petition. If you have a bit a spare time and think that MySQL is worth saving, please join us on the #helpmysql IRC channel on Freenode and help us with spreading the world and gather names! You can make a real difference, wherever you are in the world! The more names we are able to get, the higher the chance is that we can keep MySQL free and available for all! Don't be fooled by the empty promises Oracle have given about the future of MySQL. I examined them in my previous blog post at http://monty-says.blogspot.com/2009/12/oracle-gives-only-empty-promises-fo r.html and showed that Oracle is not really promising anything; Not even that the Open Source version of MySQL will be developed further. Thanks to everyone that helped us with the previous campaign. It was of great help, but probably not enough, which is why we had to start this new campaign to gather even more names. This time we will be able to use the names to influence decisions worldwide (not just in the EU) and we will get more public attention. This will be far more powerful. During the previous campaign there was a lot of discussions in different medias about my incentives for driving it. I have tried to answer most of these concerns in my latest blogpost: http://monty-says.blogspot.com/2009/12/help-keep-internet-free.html Help us keep the Infrastructure of the Internet free! Regards, Monty Creator of MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is anything ever equal to NULL?
David Giragosian skrev: On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso da...@lorenso.com wrote: Will anything ever be equal to NULL in a SELECT query? ... What's so special about NULL? http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html Should answer some of your questions, Dante. Oddly enough, that page fails to mention the = operator for which NULL does indeed equal NULL. http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
Gary Smith skrev: ... An example of where it wouldn't be: Although ID is auto_increment, you could define a row as, say, '10005583429'. This would be a valid input. Selecting max(id) would return that number. However, auto_increment wouldn't change - it would still be '34' (or whatever) for the next line. Not quite... CREATE TABLE t (id bigint unsigned primary key auto_increment); INSERT INTO t VALUES (10005583429); INSERT INTO t VALUES (null); SELECT * FROM t; +-+ | id | +-+ | 10005583429 | | 10005583430 | +-+ 2 rows in set (0.00 sec) / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Dolphins saved us from shark, lifeguards say
Thought people might get a kick out of this... From the New Zealand Herald - full story on http://tinyurl.com/6m7ul === Dolphins saved us from shark, lifeguards say 24.11.2004 By AINSLEY THOMSON A pod of dolphins is being credited with saving a group of lifeguards from a circling great white shark. Lifeguard Rob Howes, his daughter Niccy, 15, Karina Cooper, 15, and Helen Slade, 16, were swimming 100m out to sea at Ocean Beach, near Whangarei, when seven bottlenose dolphins sped towards them and herded them together. They were behaving really weird, Mr Howes said, turning tight circles on us, and slapping the water with their tails. Mr Howes and Helen Slade had drifted about 20m away from the others when a dolphin swam straight at them and dived a few metres in front of them. I turned in the water to see where it was going to come up, but instead I saw this great big grey fish swim around me, said Mr Howes. The veteran lifeguard said it was undoubtedly a 3m-long great white shark. It glided around in an arc and headed for the other two girls. My heart went into my mouth, because one of them was my daughter. The dolphins were going ballistic. The 47-year-old said the dolphins herded the swimmers - who are all members of the Whangarei Heads Surf Lifesaving Club - back together and circled protectively around them for another 40 minutes, fending off the shark. === The rest of the story is on http://tinyurl.com/6m7ul / Carsten -- Check out the MySQL Certification FAQ: http://www.mysql.com/certification/certfaq.html Carsten Pedersen, Certification Manager MySQL AB, http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Errata in the Study Guide
Hi Patrick, On Tue, 2004-08-10 at 12:16, Patrick Connolly wrote: Is this the most appropriate list to mention misprints? There doesn't seem to be an indication where additional suggestions are to be sent. This could be a good starting place if you want to discuss something in general - if you have specifics that are not already mentioned in the Certification Study Guide Errata - http://www.mysql.com/training/certification/studyguides/ - then please send them to [EMAIL PROTECTED] I found something that, though not exactly incorrect, works for reasons other than what a reader might think, so it's misleading. Posting your thinking to the list might be a good way to raise people's awareness of the issues involved. That is of course said with no knowledge of what kind of issues you have in mind :-) Best regards, / Carsten -- Warning: Certification can seriously increase your wealth! http://www.mysql.com/certification Carsten Pedersen, Certification Manager MySQL AB, http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Professional certification
Hi Brian, On Fri, 2004-06-18 at 19:48, Brian Mansell wrote: I took the certification exam this morning and passed. Congratulations! When should I expect to receive the certificate (and other items) in the mail? It usually takes 4-6 weeks for the certificate to arrive at your address. Best regards, / Carsten -- Warning: Certification can seriously increase your wealth! http://www.mysql.com/certification Carsten Pedersen Coordinator of Development, Certification Manager MySQL AB, http://www.mysql.com Office: +45 56 36 16 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for reviewers: MySQL Certification Study Guide
Hi! In April, the MySQL Certification Study Guide was published by MySQL Press. By now, it has arrived in bookstores across the world. The main parts of the book are written by Paul DuBois, Stefan Hinz and yours truly. Paul needs no introduction; Stefan has translated several MySQL books to German and is a member of the MySQL documentation team. I'm just the Certification Manager here at MySQL. The MySQL Certification Study Guide is a comprehensive study guide to prepare you for the MySQL Core Certification and MySQL Professional Certification Exams. We like to think that it is useful for anyone that wishes to brush up his or her MySQL skills. We are looking for a few brave individuals that are interested in writing an independent review of the book (to be published on the MySQL web site) in exchange for a free copy of the Study Guide. Being in the MySQL Certification program is *not* a requirement for being selected as a reviewer. The book is some 600 pages; we expect you to complete your review no later than June 30th. You can read some more details on the Study Guide, including a free chapter on http://www.mysql.com/training/certification/studyguides/ If you are interested in giving a review in exchange for a book, please email me directly with the below information, which will be used to select the broadest possible range of reviewers. - Name and e-mail address - Work function - How long you have been using MySQL - Age - Whether you've already signed up for or taken a certification exam(s) - if so, which one(s) - Whether you're primary work with MySQL is as administrator or developer. Best regards, Carsten H. Pedersen Certification Manager, MySQL AB -- Warning: Certification Can Seriously Increase Your Wealth! http://www.mysql.com/certification Carsten H. Pedersen Coordinator of Development, Certification Manager MySQL AB, http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Certification Test Questions
On Sun, 2004-01-18 at 20:51, Johannes Franken wrote: * Marc Dver [EMAIL PROTECTED] [2004-01-18 18:30 +0100]: 1. What is the format of the test questions? I.e., are they multiple choice, free answer, essay, etc.? They are multiple-choice (but very tricky) and fill-in-the-gaps. For example: Assume a column in a MyISAM table has type VARCHAR, BLOB, or TEXT. Which of the following specifies this table type correctly to save space? Mark all answers that are wrong: [ ] ROW_FORMAT=Static [ ] ROW_FORMAT=default [ ] ROW_FORMAT=dynamic [ ] ROW_FORMAT=compressed [ ] all of the above [ ] none of the above I think I need to clarify a few details regarding this: First of all, we've done all that we possibly could do to remove double negatives in the exam questions. We very rarely ask which of the following are NOT true. Only when the question or answer texts would become much more complicated without this structure, have we submitted to it. And when we do so, we make it very clear what we are asking. There are indeed All/None of the above answers for a few question items. However, because of the way the exams are structured, I can promise you that if you were to go to the exam today, you would only see one (if any) of this type of questions during the entire exam. Now, I'm not saying all this to put Johannes or his observations down -- going to an exam *is* a stressful experience and people do come out with (amazingly) different accounts of what they thought was going on during the exam. During internal testing of the exams, I had MySQL employees be subjected to the exact same set of questions. Afterward, they gave me completely different accounts of their experience: some felt that more than half the questions had been on import/export (it was in fact around 5%); others were saying that they thought there were too many fill-in the blanks questions (on that particular test, they were subjected to *two* such questions during the exam). Are they theory based or is it fact based, i.e., memorize the books to pass? For the CORE certification, it's balanced. In many cases they show you some tables's contents and you have pick either the statements which do some job, or the result if you rune some statement. For this type of questions, memorizing the books is just not enough. You need to *understand* the facts. For the PROFESSIONAL certification, it's different. Here you don't need SQL, but you should memorize every aspect of performance- and security-tuning. ...and again, you need to *understand* how things work. Mere fact memorization won't see you through (which is not contrary to Johannes' statement; I just think it's important to highlight it). I have heard from some candidates that they regard the Core exam as being more of a memorization exam than Professional. Which only goes to show, once again, that perceptions differ quite a bit as people come out of the testing room. Best regards, / Carsten -- Carsten H. Pedersen Coordinator of Development, Certification Manager MySQL AB, http://www.mysql.com Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Certification Question
On Sat, 2004-01-10 at 12:04, Bernard Clement wrote: Hello Aman, For instructions on taking the exam in India goto the URL: http://www.vue.com/mysql/ and click on test center of To register for exams in India, please contact the test center directly. This will bring you a window containing all the Pearson VUE Test Center in India. You should of course also check up on all the certification material available on the MySQL web site: http://www.mysql.com/certification Most importantly, make sure to read through the Certification Candidate Guide. Best regards and good luck! / Carsten -- Carsten H. Pedersen Coordinator of Development, Certification Manager MySQL AB, http://www.mysql.com Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL certification
On Mon, 2004-01-05 at 19:56, Douglas Sims wrote: Thanks, Stefan. Mike's article was interesting. The test was a bit harder than I anticipated. I should have paid more attention to column types and database name, among other things. But I did pass Congratulations :-) - at least, the preliminary report said pass, but also said that the exam will be reviewed and If you have met the passing score, you will receive your official certificate by mail. This puzzles me, because this exam isn't beta any more? No, it's not. And don't worry, that's just some standard verbiage from the test center. If your score report says passed, you've passed -- and you will receive your certificate in due time. Studying for and taking the exam was a good excercise and I'm glad I did. I learned things I didn't know about MySQL. ... Yes, many candidates are surprised at the high level of the exam; both the knowledge/skill required and the amount of information covered in the Core exam are quite extensive, despite this being our first level exam. I learned things I didn't know about MySQL while studying is a comment we often hear -- which makes me and others here happy, as this is of course one of the main ideas behind having a certification program in the first place. Best regards, / Carsten -- Carsten H. Pedersen Coordinator of Development, Certification Manager MySQL AB, http://www.mysql.com Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL certification [slightly-ot]
Hi Jennifer, On Fri, 2003-09-26 at 20:55, Jennifer Goodie wrote: I have recently re-entered the job market and I was wondering if anyone has found that having certification really helps in landing a position. If so, which cert do you have? I can only answer in general terms, but hope you might still find this insight helpful... I have talked to quite a number of hiring managers, and there seems to be some consensus regarding the following points, when it comes to certifications in hiring: If a manager looks at certifications, it's often the first thing he does to split the candidates, with a view to look at the certified people first. While holding certificate XYZ will often make a difference as to who makes it into the stack of interesting candidates, it rarely makes a difference when deciding on the final runner-ups for the position. At this time, your CV and personality are what makes the difference. The value of a given certification also depends on where you are looking for a job. In large corporations, where a centralized HR department is involved, the people making the initial processing of applicants often don't know (or maybe they don't understand) the details of what's being asked of the candidates. To them, even an introductory certification (like MySQL ABs Core certification) can make a big difference. Smaller companies tend to look only at higher-level or specialized certifications when hiring. I hope you found this input helpful -- you (and anyone else reading this reply) are very welcome to contact me directly if you want further details on our certification program. Best regards, Carsten Pedersen Certification Manager, MySQL AB -- Warning: Certification can seriously increase your wealth! http://www.mysql.com/certification Carsten Pedersen Coordinator of Development, Certification Manager MySQL AB, http://www.mysql.com Office: +45 56 36 16 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL core certification revision - clients
Hello, On Sun, 2003-07-20 at 14:59, Stefan Hinz wrote: Matthew, Hi, I'm clear as to what is meant by The mysql client in the MySQL core certification revision check list. I understand that the mysql database itself is the server. Would client then be any program that talks to the server? Perhaps it is referring to mysql, the command line tool? Just a few small note to qualify Stefan's explanations: The mysql client as written in the list that you speak of in the Candidate Guide on http://www.mysql.com/certification/candguide/sec-core.html does indeed refer specifically to the client program mysql (or mysql.exe on Windows), so your interpretation of this is correct. Client programs are indeed programs that talk with the server. The server, however, is not the database, but rather the program that manages databases (allow/deny access to databases and their tables, modify tables structures, change contained data, etc.). MySQL provides a number of client programs for various purposes, like controlling the server, backing up databases, etc. One of those programs is mysql, which is a command line interface (CLI). The names of programs in a MySQL binary distribution can be somewhat confusing: mysqld is the server, while mysql is the (main) CLI client. I understand client side programs, examples being: mysqladmin, mysqldump, and mysqlimport. These are known as mysql client commands also? Those are client (side) programs, not client commands. You use those programs to issue (client) commands that are sent to the server. Hmm - Stefan, you need to read that particular page more carefully :-) The line that is being discussed is this one, which appears as a subject under The mysql client: * Differentiate between mysql client commands and SQL commands In this context, a client command is one such as quit, rehash, connect (They are listed on http://www.mysql.com/doc/en/mysql.html), which is processed directly by the mysql program. An SQL command, on the other hand, is an e.g. SELECT or INSERT statement that is sent to the server for processing. Note that just because a command is a client command, this does not necessarily infer that no client/server communication takes place. Best regards, Carsten H. Pedersen Certification Manager, MySQL AB -- Warning: Certification can seriously increase your wealth! http://www.mysql.com/certification Carsten Pedersen, Certification Manager MySQL AB, http://www.mysql.com Office: +45 56 36 16 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Practice test
use MySQL. Yes, the test can measure whether or not we know syntax details, but in a real-life situation that's not a very critical piece of knowlege to have. They did ask at least one what does this query DO question. I think those are much better for a certification exam. Exactly. Procedural questions, and questions that probe the depth of understanding, are harder to write for such an exam... but much more useful in evaluating how well an individual will perform on the job. Examples might include: 1) How do you set up replication? 2) How are replication conflicts resolved? 3) How do you recover from a disk failure? 4) To what extent can you recover from data changes introduced by a rogue program? 5) What is the impact of the CHECK constraint? 6) You create a HEAP table. The server is brought down, and then up again. You issue a SELECT against the HEAP. What happens? 7) A database table has become corrupt after a power outage. What should you do? 8) Which of the following queries will not benefit from indexes? 9) Which of the following queries corresponds to the following query with a NOT EXISTS subquery? 10) Match the following filenames to their corresponding table structures. And so on. Heck, the commonly asked questions on this list would make a pretty good test! Bruce Feist (retired database instructor / courseware designer) -- Warning: Certification can seriously increase your wealth! http://www.mysql.com/certification Carsten Pedersen, Certification Manager MySQL AB, http://www.mysql.com Office: +45 56 36 16 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
