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: Batch update
On 14.02.2012 10:20, Alex Schaft wrote: Hi, I need to update a table along the lines of the following update table set LastUpdate=now(), UpdateSource='Abc' Where Key1 = 'Def' and Key2 = 'ghi' I need to possible do anywhere from 2 to 20 of these. Would it be better to call an update statement for each of these, What exactly are these? Is 'Abc' constant for all rows, or does it change for each Key1Key2 value? If so, then simply WHERE (Key1=... AND Key2=...) OR (Key1=... AND Key2=...) OR... should suffice or even: WHERE (Key1, Key2) IN (val1, val2),(val3,val4), ... If 'Abc' changes per Key1Key2 values, explore the CASE statement. 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: Mysql Certification exams
Hi Machiel, First of all, obligatory disclaimer: Until 2007, I was in charge of the MySQL certification exams. I'm also co-author of the book I'm about to recommend, but I receive no sales royalties. All of which means that I'm biased and my knowledge may be a bit rusty, but hopefully still useful :-) The best resource for self-study ought to be the MySQL 5 Certification Study Guide, http://www.amazon.com/MySQL-5-0-Certification-Study-Guide/dp/0672328127/ref=sr_1_1?ie=UTF8s=booksqid=1262596558sr=8-1 The book has received very favorable feedback from those doing self-studying for the certification exams. Along with the book you get a number of self-study questions to help you check whether you have understood the material. The questions are open-ended/discussion questions, not multiple-choice like on the exams. Also make sure to read the candidate guide on the mysql web site before you go to the exam. It has a lot of valuable hints. Best of luck on the exam! / Carsten On Mon, 4 Jan 2010 08:06:12 +0200, machiel.richards machiel.richa...@gmail.com wrote: Good day all I hope that someone can help me out with some resources. Due to lack of interest my MySQL training was cancelled in December. However I need to get certified (MySQL DBA) ASAP and need to do this on book knowledge and a bit of experience alone. Due to the cost for the exams I cannot afford to fail any one of the two exams. I am trying to get hold of sample exams, etc... that I can use for preparation to the exams in order to test myself and make sure that I have all the knowledge I need. Does anybody know where I can look for these? Regards Machiel !DSPAM:451,4b41859b3567878853768! -- 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
Re: Update Doesn't Update!
On Fri, 11 Dec 2009 04:38:01 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Warnings: 1
do a SHOW WARNINGS immediately after you execute the stmt.
/ Carsten
mysql select sizes, colorsShadesNumbersShort from products where ID=0;
+---+--+
| sizes | colorsShadesNumbersShort |
+---+--+
| | |
+---+--+
1 row in set (0.00 sec)
Huh?
TIA,
Victor
!DSPAM:451,4b221339930275276717544!
--
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 Doesn't Update!
On Fri, 11 Dec 2009 10:48:59 +0100, Jørn Dahl-Stamnes
sq...@dahl-stamnes.net wrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
Then how did he manage to get a matched row?
Of course you can have ID=0.
/ 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: Update Doesn't Update!
On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
mysql update products set sizes=('Small', 'Large') where
SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)
What do? How do I enter multiple values?
Impossible to say, until you let us know how you defined the column in the
first place...
/ 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: Update Doesn't Update!
On Fri, 11 Dec 2009 05:28:41 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
On Fri, Dec 11, 2009 at 5:13 AM, cars...@bitbybit.dk wrote:
On Fri, 11 Dec 2009 05:09:52 -0500, Victor Subervi
victorsube...@gmail.com
wrote:
mysql update products set sizes=('Small', 'Large') where
SKU='prodSKU1';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'sizes' at row 1 |
+-+--++
1 row in set (0.00 sec)
What do? How do I enter multiple values?
Impossible to say, until you let us know how you defined the column in
the
first place...
Sorry. It's an enum of which the elements I am trying to add into a row
are
elements of the same enum; that is, a subset.
You're using the wrong type. RTFM re. the difference between enums and
sets.
/ 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: different 5.0 and 5.1 about long_query_time
On Fri, 11 Dec 2009 18:43:06 +0800, Yang Wang yw...@lfm-agile.com.hk wrote: Hi,All The version of 5.1 include microtime slow query patch? It would have taken you less time to google mysql long_query_time and getting your answer, than it did to write your mail... / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Out of tablespace when using innodb_file_per_table
Hello, I'm using InnoDB with the innodb_file_per_table option on. But now my .MYD-file has reached the maximum filesize of my filesystem and i have to add a new tablespace. Can someone tell me how? Thanks in advance for your help Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: Last Modified
If all you're looking to do is check the last time the entire table was updated, why don't you just look at the last time the .frm file (if using MyISAM tables, of course) was modified? Thursday, June 10, 2004, 7:19:32 AM, you wrote: PM If your table contains a timestamp field, it will update each time the row PM is altered. Otherwise I don't think it's possible. PM You Wrote - PM Hello I am using PHP, MySQL. How would I query MySQL to display the last PM time a table was last modified/changed/or updated. For example I have a user PM that updates a stats page via the web, and I would like it to say (Current PM up to The last date the stats table was modified). Thanks for your help, PM I've been looking online as well but thought this may be quicker. Take care. PM Thanks again. PM God Bless PM Paul C. McNeil PM Developer in Java, MS-SQL, MySQL, and web technologies. PM GOD BLESS AMERICA! PM To God Be The Glory! -- Best regards, Carsten R. Dreesbach mailto:[EMAIL PROTECTED] Senior Consultant Systar, Inc. 8000 Westpark Dr Suite 450 McLean, VA 22102 USA Tel: (703) 556-8436 Fax: (703) 556-8430 Cel: (571) 213-7904 -- 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: AVG function in order by clause
Pretty simple, declare an alias in your query and use it in the ORDER BY, i.e.: SELECT DB_ESTABLISHMENT_NAME,AVG(DB_GRADE) AS AVGGRADEFROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ONES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_IDGROUP BY ES.DB_ESTABLISHMENT_IDHAVING AVG(DB_GRADE) 2ORDER BYAVGGRADE Hope that helps! Message from Julien Martin [EMAIL PROTECTED] at 2004-05-05 19:41:32 -- Hello,Thanks a lot for the replies. I have changed the query as follows:**SELECT DB_ESTABLISHMENT_NAME,AVG(DB_GRADE)FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ONES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_IDGROUP BY ES.DB_ESTABLISHMENT_IDHAVING AVG(DB_GRADE) 2ORDER BYAVG(GRADE)**Now I am having a problem with the order by clause. How can I have the AVGfunction in the ORDER BY clause or how can I sort by average grade?Thanks in advance,Julien Martin.-- MySQL General Mailing ListFor list archives: http://lists.mysql.com/mysqlTo unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Best regards Carsten R. Dreesbach mailto:[EMAIL PROTECTED] Senior Consultant Systar Inc. 8000 Westpark Dr Suite 450 McLean VA 22102 USA Tel: 703 5568436 Fax: 703 5568430 Cel: 571 2137904 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: backup
Hi Matt, if Steve can accept the limitations, mysqlhotcopy might work for him: http://dev.mysql.com/doc/mysql/en/mysqlhotcopy.html If not, mysqldump with --add-locks could do it fairly easily, I should think... http://dev.mysql.com/doc/mysql/en/mysqldump.html Saturday, April 10, 2004, 5:08:43 AM, you wrote: MW Hi Steve, MW You might want to look at FLUSH TABLES WITH READ LOCK. That's a query MW to run from mysql, but I'm sure you can get it to work in your shell MW script (you need to maintain the MySQL connection while doing the MW backup). I don't know much about that, though. I think you just run MW UNLOCK TABLES when you're finished. MW Matt MW - Original Message - MW From: Steve Sills MW Sent: Tuesday, April 06, 2004 8:17 PM MW Subject: backup MW I want to use rsync to backup my db server, how do i lock all the tables MW for all the db's to read only so i cando my backup, then unlock them MW again. It needs to be done from the command line, not the mysql MW program. Anyone have any ideas? I have looked and couldn't find the MW answer i was looking before. Its running from a shell script, from my MW backup machine. Its currently setup to shut down the server, however i MW don't want to have to do this. Thanks in advance. MW Steve Sills MW Platnum Computers, President MW http://www.platnum.com MW [EMAIL PROTECTED] -- Best regards, Carsten R. Dreesbach mailto:[EMAIL PROTECTED] Senior Consultant Systar, Inc. 8000 Westpark Dr Suite 450 McLean, VA 22102 USA Tel: (703) 556-8436 Fax: (703) 556-8430 Cel: (571) 213-7904 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practice on table design
Hi Ciprian, OK, I'm by no means a DB guru, so a) take this with a grain of salt and b) feel free to tear it apart if I'm completely wrong! ;] If in fact your people and city tables aren't going to change very often, then why don't you just go all the way and keep that information somewhere else in your application and write it straight to your travel_expenditures table, e.g.: travel_expenditures --- | id | date | people| city| per_diem | --- | 1 | 05.08 | John | Glasgow |1.600 | | 2 | 05.09 | Mary | Madrid |2.000 | | 3 | 06.12 | John | Madrid |1.000 | --- This way you completely avoid any JOINs. Of course, this only makes sense if your people and cities information is not likely to change much at all... Thursday, April 8, 2004, 3:29:22 AM, you wrote: CT Hello, CT I have the following structure: CT people CT - CT | id | name | CT - CT | 1 | John | CT | 2 | Mary | CT - CT cities CT CT | id | city| CT CT | 1 | Glasgow | CT | 2 | Madrid | CT | 3 | Berlin | CT CT travel_expenditures CT --- CT | id | date | id_people | id_city | per_diem | CT --- CT | 1 | 05.08 | 1 | 1 |1.600 | CT | 2 | 05.09 | 2 | 3 |2.000 | CT | 3 | 06.12 | 1 | 2 |1.000 | CT --- CT The `people` and `cities` tables aren't going to be very populated, so a CT thought to merge them into something like this: CT central_data CT --- CT | id | name| type | CT --- CT | 1 | John| P| CT | 2 | Glasgow | C| CT | 3 | Mary| P| CT | 4 | Madrid | C| CT | 5 | Berlin | C| CT --- CT where central_data.type is P for people and C for cities. CT Do you think it is a good ideea ? CT -- CT Best regards, CT Ciprian Trofin -- Best regards, Carsten R. Dreesbach mailto:[EMAIL PROTECTED] Senior Consultant Systar, Inc. 8000 Westpark Dr Suite 450 McLean, VA 22102 USA Tel: (703) 556-8436 Fax: (703) 556-8430 Cel: (571) 213-7904 -- 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]
Difficulty with SELECT
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
I'm having trouble composing the right SQL statement for my purpose:
I am building a search interface to a database with research projects. The
research projects (tbl_projects) have assigned keywords in german and
english, which are in a seperate table (tbl_keywords). Because I want to
build a thesaurus from the keywords further on, I try to keep the number of
keywords as small as possible and assign the keywords to the projects via a
third table (tbl_keywords_projects).
When querying the database, I want to receive resultset which contains
projects which have all of the entered keywords assigned.
I started with:
SELECT
`tbl_projects`.`project_id`,
FROM
`tbl_projects`,
`tbl_keywords_projects`,
`tbl_keywords`
WHERE
`tbl_projects`.`project_id` = `tbl_keywords_projects`.`project_fid`
AND
`tbl_keywords_projects`.`keyword_fid` = `tbl_keywords`.`keyword_id`
AND (
`tbl_keywords`.`keyword_de` IN ('keyword_1', 'keyword_2')
OR
`tbl_keywords`.`keyword_en` IN ('keyword_1', 'keyword_2')
)
GROUP BY `tbl_projects`.`project_id`
but this returns all the datasets which contain at least one of the
keywords. I am looking for a SELECT statement which returns only the
datasets which contain all of the entered keywords. Since I am not very
familiar with complex SQL statements, I tried some other expressions which
always returned an empty result set.
I hope that this question is not _too_ stupid so that someone will have an
answer for me,
Carsten
-BEGIN PGP SIGNATURE-
Version: 6.5.8ckt
iQA/AwUBP9cfv7jxzYr/SJ6uEQLdpACfUhB9ON/F8sv0bFqEkVIJXcEyvfEAnjJ4
RRCqfhgx38hfAtqF50fWASm9
=m7D3
-END PGP SIGNATURE-
--
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
Hello, Quick words of introduction before you read on: I'm the MySQL AB Certification Manager :-) As Mike's comments (below) to a large extent states much of what I was planning to say when I sat down to write this, I'll keep it short by adding a few comments to his response. Yes, there are some questions on the practice test that ask just knowledge-based questions rather than testing skills, and while they are over-represented on the practice test, you will also find a good deal of these on the actual exam. As Mike says, it's a trade-off - while low-cost, world-wide, easy-access (add more terms to your liking) delivery certainly is an issue, it's not the only one. Besides the issues that Mike raises, I would like to point out two more reasons why we have chosen to include a fair amount of this type of question: Balance: A good exam will test both skill AND knowledge -- knowing how to change the table type from MyISAM to InnoDB is knowledge -- realizing when and why to do it is skill. And the two complement each other. Breadth of subjects - we want people to go to only one exam per certification level, to keep down costs (for both you and for MySQL AB). This in turn means that we must cover many topics in the 90 minutes we have available for testing. Skill-proving questions have many advantages; letting the candidate answer them quickly is not one of them... Finally, as Mike also points out, the people that I see complaining about the lack of skill testing are obviously long-time users very proficient in MySQL, and you may be forgetting that the Core exam is aimed at people with maybe just a few man-months of experience. Trust me: you'll get a comb that fits your hair (as the Danish expression goes) when you get to the higher-level exams. And just for the record: I of course disagree with Mike's statement that most certification exams are a braindump -- at least when it comes to our exams :-) Should anyone have further questions about the certification program, do have a look on http://www.mysql.com/certification -- if you still have questions, please do not hesitate to contact me directly. Best regards, (and for those of you that decide to get certified: Good luck on the exams!) / Carsten On Tue, 2003-06-10 at 20:33, Mike Hillyer wrote: I think the other thing to remember is that this is only the Core exam, and as far as I can tell a fair amount of the content you suggest will be part of the Pro exam. I remember once seeing stirrings of a DBA exam as well, so I guess the Core is just to get your feet wet and show a knowledge of the basics before moving to the higher level exams. The problem with more complicated exams is that they become difficult to administer and grade automatically. The benefit of MySQL using VUE to administer tests is that the tests can be offered in hundreds of locations around the world thanks to the automated testing process. This means that the lady administering my Core exam didn't have to know MySQL from a cheese sandwich. A more complicated exam means instead of choosing from three convenient testing locations in my city, I have to make an extended trip to a testing location where there will be a professional examiner. This all means more cost to me in both time and money. I think certification exams are a tradeoff. The RedHat Certified Engineer exam is supposed to be practical and hands on (you are asked to install, not how to install), and is supposedly in fair demand in the market, but the tradeoff is that I count maybe 20-30 locations on their website that you can take the exam, at a cost of $749 plus travel to the nearest center. When I check for a MySQL exam center, I see more test centers in California alone that RedHat offers in all of north America, and I pay a lot less for the exam. While the MySQL Core exam may be less challenging than an in-depth hands-on exam, I think the exam is good value for the money. It shows you know the basics without checking the manual and can spot basic syntax problems before running the query. I have to agree that most certification tests are a brain-dump, but hey, most HR people do not have the knowledge to check whether you know what the job descriptions need to know, so you have to have something to show you meet the requirements in a job description. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 12:09 PM To: MySQL List Subject: Re: Practice test Paul DuBois wrote: At 7:51 -0700 6/10/03, Jeremy Zawodny wrote: Is this syntax valid in MySQL? [some query] What's the point of such a question? Anyone with access to MySQL can find out by simply running the query. You don't have access to MySQL during the test. You have to know whether or not the syntax is correct. I think the point is that this isn't representative of how effectively we can
Problems with Berkeley DB support in version 3.23.55
Hallo, we have downloaded the new 3.23.55 mysql DB from you as Source Download Tarball (tar.gz) and want to build them in our environment for tests under SUN OS. We have tared the downloaded version in our Scource Directory environment. (for example directory/mysql/src/mysql-3.23.55) After that we want configure the downloaded version from our bulid and configure environment. (for example directory/mysql/obj/mysql-3.23.55). We take the ./configure from the obj-Directory: directory/mysql/src/mysql-3.23.55/configure --with-berkeley-db to have berkeley DB support. After we take the make command we become some errors at the make output. (Sorry, but I have cancelled the make error output and so I can't write the error output to you.) To fix the problem I look in the config.status file, and there I found entries for the bdb variables: s,@bdb_includes@, s,@bdb_libs@, s,@bdb_libs_with_path@, In the config.status file I found only entries for the src-path: s,@bdb_includes@,-I,directory/mysql/src/mysql-3.23.55/bdb/build_unix,;t t s,@bdb_libs@,-L,directory/mysql/src/mysql-3.23.55/bdb/build_unix,;t t s,@bdb_libs_with_path@,directory/mysql/src/mysql-3.23.55/bdb/build_unix/libdb.a,;t t And so I missed entries for the obj path. After that I take in the config.status file the new entries for the obj-path: s,@bdb_includes@ -Idirectory/mysql/obj/mysql-3.23.55/bdb/build_unix s,@bdb_libs@ -Ldirectory/mysql/obj/mysql-3.23.55/bdb/build_unix s,@bdb_libs_with_path@ directory/mysql/obj/mysql-3.23.55/bdb/build_unix/libdb.a Then I make a new ./configure call with the option --enable-maintainer-mode and after taken make the make output goes without errors an so I can fix the problem. Greetings, Carsten Thoene Carsten Thoene eMail: [EMAIL PROTECTED] Technische Fakultaet Universitaet Bielefeld D-33594 Bielefeld - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problems with Innobase DB support in version 3.23.55
Hallo, we have downloaded the new 3.23.55 mysql DB from you as Source Download Tarball (tar.gz) and want to build them in our environment for tests under SUN OS. We have tared the downloaded version in our Scource Directory environment. (for example directory/mysql/src/mysql-3.23.55) After that we want configure the downloaded version from our bulid and configure environment. (for example directory/mysql/obj/mysql-3.23.55). We take the ./configure from the obj-Directory-Path: directory/mysql/src/mysql-3.23.55/configure --with-innodb (to have Innobase DB support). After we take the make command we become some errors at the make output. For example a little Extract from the make output: directory/mysql/src/mysql-3.23.55/innobase/os/os0proc.c:10:21: os0proc.h: No such file or directory directory/mysql/src/mysql/mysql-3.23.55/innobase/os/os0proc.c:19:20: ut0mem.h: No such file or directory directory/mysql/src/mysql-3.23.55/innobase/os/os0proc.c:28: parse error before n directory/mysql/src/mysql-3.23.55/innobase/os/os0proc.c: In function `os_mem_alloc_nocache': directory/mysql/src/mysql/mysql-3.23.55/innobase/os/os0proc.c:39: `n' undeclared (first use in this function) Then I begin to look in the mysql scources to fix the problem. (for example directory/mysql/src/mysql-3.23.55/innobase) And in the file for example: directory/mysql/src/mysql-3.23.55/innobase/usr/Makefile.in here I think I found the mistake: (extract from the Makefile.in): $(srcdir)/Makefile.in: @MAINTAINER_MODE_TRUE@ Makefile.am $(srcdir)/../include/Makefile.i $(top_srcdir)/configure.in $(ACLOCAL_M4) The same error I found under: directory/mysql/src/mysql-3.23.55/innobase/os/Makefile.in I looked to the whole Scource Code and there I can't find a directory with the name Makefile.i And so for our test's with the 3.23.55 mysql we decide to built the software only in the scource tree directory, and not in the obj tree directory, to expect the mysql version can build there with Innobase and Berkeley DB support without errors. Greetings, Carstn Thoene -- Carsten Thoene eMail: [EMAIL PROTECTED] Technische Fakultaet Universitaet Bielefeld D-33594 Bielefeld - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Announce: Upcoming MySQL Certification program
Dear MySQL user, During 2002, MySQL AB will release two levels of official MySQL certification: MySQL Core Certification and MySQL Professional Certification. This e-mail describes the upcoming program as well as some of your opportunities. Contents 1. The MySQL Certification Program in 2002 - an overview 2. Certifications are for version 4.0 3. Go for Beta testing and get ahead of the crowd 4. The ultimate Certification Preparation: Official MySQL training 1. The MySQL Certification Program in 2002 - an overview MySQL AB is proud to announce the upcoming availability of two MySQL certifications: The MySQL Core Certification and the MySQL Professional Certification. The MySQL Core Certification is aimed at the professional MySQL user who wants proof of his or her prowess in the fields of SQL, data entry and maintenance, data extraction for reporting, etc. This certification will be released in the autumn of 2002. For more information on the MySQL Core Certification, see http://www.mysql.com/training/candguide/sec-core.html The MySQL Professional Certification is for the user who has more experience in the world of MySQL, and wants the credentials that prove his or her knowledge in such areas as database management, installation, security, disaster prevention and optimization. This certification will be released in late 2002. For preliminary information on this exam, see http://www.mysql.com/training/candguide/sec-prof.html The Core certification is a prerequisite for the Professional Certification: You must pass the Core exams before you are able to sign up for the Professional exam. Even though the Professional exam might be what you are eventually aiming for, you should start making plans for taking the Core exam already now. For general information on the MySQL Certification Program, see http://www.mysql.com/training/certification.html 2. Certifications are for Version 4.0 - Both certifications will certify your knowledge and competence for MySQL 4.0. There will not be a MySQL certification for version 3.23 or earlier releases. This should, however, not present any difficulties. The user view of the database has not changed significantly, and the extensions and enhancements in moving from MySQL 3.23 to MySQL 4.0 are clearly presented and documented on our website: http://www.mysql.com/products/mysql-4.0/index.html 3. Go for Beta testing and get ahead of the crowd - You now have the opportunity to sign up for our Beta test program for the MySQL Core Certification. Your benefits of taking part in the beta exam program include: * Beta exams and results are delivered well in advance of the official publication date -- you will hold your certification even before the program is officially launched! * Your MySQL Core Certificate is just as valid as a regular certificate delivered after the day of publication * Beta exams are offered at a considerable discount The downside is: You get to do more work (you must answer 2-3 times as many questions during a Beta exam), and you won't know whether you passed or failed until some weeks later. Once the program is officially launched, candidates get their test results as soon as the exam is completed. Read more about how to be part of our beta program on the MySQL Beta Certification page: http://www.mysql.com/training/beta.html 4. The ultimate Certification Preparation: MySQL training - MySQL Training will be invaluable to you in preparation for the certification exam. Learn more about MySQL than you ever thought possible, and ace the exam! Register for training now and be among the first MySQL users with a MySQL Core Certificate! https://order.mysql.com/ Regards, Carsten H. Pedersen MySQL AB Certification Manager -- MySQL Certifications, http://www.mysql.com/training/certification.html __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Carsten Pedersen [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Certification Manager /_/ /_/\_, /___/\___\_\___/ Haslev, Denmark ___/ www.mysql.com Office: +45 56 36 16 10 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: ADO recordset is not returning rows in ASP page
Have you tried to run the query in MySQL's console? - Carsten -Oprindelig meddelelse- Fra: Rob Galvin [mailto:[EMAIL PROTECTED]] Sendt: 30. maj 2002 20:29 Til: [EMAIL PROTECTED] Emne: ADO recordset is not returning rows in ASP page I have an active server page using an ADO recordset. When the SQL command is a join of two tables with a count/group by there are no records being returned (RS.EOF is true). snip If I do the same query from from MS Access using the same ODBC DSN, I get results. So it is not an ODBC problem. If I do the query without the count(*)/group by joing the two tables then it is ok. If I do a count(*)/group by on a single table it is ok. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: building tree view in mysql?
Well you'll neew to do some coding in your application language. What are you using to connect to MySQL? Perl? PHP? Java? Tell me, and I'll give you an example where you only query the database once (for optimum performance). - Carsten -Oprindelig meddelelse- Fra: Sagi Bashari [mailto:[EMAIL PROTECTED]] Sendt: 27. maj 2002 20:50 Til: olinux; [EMAIL PROTECTED]; [EMAIL PROTECTED] Emne: Re: building tree view in mysql? Yeah, thats exactly what I wanted to do. But how do I make MySQL sort it like that? Show the parent first, and then all its childs, so the application can determite if it's subcategory by checking if the parent is was changed. Sagi From: olinux [EMAIL PROTECTED] create a table like this: id | parent_id | category parent_id points to the parent categories id. Top level categories get parent_id of '0' ex: 1 | 0 | auto 2 | 1 | repair 3 | 2 | body 4 | 2 | windshields 5 | 1 | detailing 6 | 2 | tires Tree would look like: auto repair body windshields tires detailing olinux - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: building tree view in mysql?
Sorry I wasn't really awake, and didn't notice which list you'd posted on
:-) Here's an example in PHP (not tested):
==
$cat_list = array();
$sql = select id, parent_id, category from your_table where order by
parent_id, category ;
$res = mysql_db_query($dbName, $sql) or die(Fejl ved query: $sqlhr .
mysql_error());
while ($row = mysql_fetch_assoc($res))
$cat_list[] = $row;
showcattree(0, 0, $cat_list);
function showcattree($parent_id, $niveau, $cat_list) {
$tabStr = str_repeat(nbsp;, $niveau*3);
for ($i=0; $icount($cat_list); $i++) {
$local_id = $cat_list[$i][id];
$local_parent_id = $cat_list[$i][parent_id];
$local_navn = $cat_list[$i][titel];
if ($local_parent_id == $parent_id) {
echo $tabStr$local_navnbr;
showcattree($local_id, $niveau+1, $cat_list);
$count++;
}
}
}
==
Please note that cat_list is given as a by-reference parameter. Otherwise
you'd be copying the whole array for each recursive function call.
The trick is, that to make a tree you always need to use recursive
functions. The performance is gained by loading the complete list from
database at once (instead of calling a mysql SELECT statement for each
branch of the tree).
- Carsten
-Oprindelig meddelelse-
Fra: Carsten Gehling [mailto:[EMAIL PROTECTED]]
Sendt: 27. maj 2002 20:33
Til: [EMAIL PROTECTED]
Emne: SV: building tree view in mysql?
Well you'll neew to do some coding in your application language.
What are you using to connect to MySQL? Perl? PHP? Java?
Tell me, and I'll give you an example where you only query the
database once (for optimum performance).
- Carsten
-Oprindelig meddelelse-
Fra: Sagi Bashari [mailto:[EMAIL PROTECTED]]
Sendt: 27. maj 2002 20:50
Til: olinux; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Emne: Re: building tree view in mysql?
Yeah, thats exactly what I wanted to do. But how do I make MySQL sort it
like that? Show the parent first, and then all its childs, so the
application can determite if it's subcategory by checking if
the parent is
was changed.
Sagi
From: olinux [EMAIL PROTECTED]
create a table like this:
id | parent_id | category
parent_id points to the parent categories id. Top
level categories get parent_id of '0'
ex:
1 | 0 | auto
2 | 1 | repair
3 | 2 | body
4 | 2 | windshields
5 | 1 | detailing
6 | 2 | tires
Tree would look like:
auto
repair
body
windshields
tires
detailing
olinux
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: 4.0.1 Bugs
Fra: Richard Clarke [mailto:[EMAIL PROTECTED]] Sendt: 18. maj 2002 22:29 Emne: 4.0.1 Bugs List, I wondered if any movement has been made to determine the cause of the following bugs that I have come across using Mysql 4.0.1. 1) selectunion causes a temporary table full type error when the datasets aren't even that large and when there is bags of disk space. 2) Under FreeBSD 4.4-RELEASE I can guarantee my database will crash after roughly 5-7 days of running the same set of queries a squillion times (with no changes made to the queries). Memory usage is fine and swap space is ample. May we see a) your queries and b) the tables involved? Without such information it would just be a guessing game... - Carsten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: Newbie Question
You could use Kylix from Borland. It's a RAD tool - it's actually Delphi ported to Linux. - Carsten -Oprindelig meddelelse- Fra: Yvon Darang [mailto:[EMAIL PROTECTED]] Sendt: 6. maj 2002 15:31 Til: [EMAIL PROTECTED] Emne: Newbie Question Hi everybody, Let's say I do not want to use any microsoft tool on my PC. Is it possible to develop a desktop application using MySQL as database server without having to program in C or C++ to access and display the Data? My question probably should be: If not ODBC, what desktop database application (like MS-Access) can I use in Linux to access MySQL data or to develop and application that can access those data. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: C++ API for Mysql
You can download MySQL++ from www.mysql.com - Carsten -Oprindelig meddelelse- Fra: Hisseine Dj. [mailto:[EMAIL PROTECTED]] Sendt: 6. maj 2002 17:24 Til: [EMAIL PROTECTED] Emne: C++ API for Mysql Hello, Can someone tell me if there is an C++ API that works smooth with MYSQL on linux. So far I was trying msql++ and SQLAPI++. SQLAPI++ seems to be good by there are problems when runing the program. My system is Redhat 7.2 and Mysql 3.23.49 Thanks, Hisseine - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: Strategies for maintaining tables calculated from other tables?
Fra: Nick Arnett [mailto:[EMAIL PROTECTED]] Sendt: 3. maj 2002 04:30 Emne: Strategies for maintaining tables calculated from other tables? I'm finding that it's not quite as simple as I had imagined to maintain a table whose values are calculated by analyzing other tables. The source tables contain time series data, which can is updated several times a day. To calculate totals by day, week, month, etc., I don't want to have to start over from scratch, so I want to keep track of which records in the source tables have already been analyzed. Further complicating things, there are several analyses that take place and I may add others later. I don't always want to have to do all of them at the same time. snip add a datetime field to each of your source tables that shows the date/time when the record was inserted/last updated. Then create another table containing these fields calcs id int unsigned auto_increment primary key calculation varchar(100) last_run datetime For each type of calculation, you insert a record into this new table. You now have a way to register when your calculation was last run. The next time one of your calculations are run, select all source-records with a datetime value newer than your last_run. - Carsten #¤@¤#%#%¤ filter!!! (sql,database,query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: WHERE ignored
-Oprindelig meddelelse-
Fra: Mike Hall [mailto:[EMAIL PROTECTED]]
Sendt: 28. april 2002 12:28
Til: [EMAIL PROTECTED]
Emne: Fw: WHERE ignored
Corrupt index, hmm? I'll check that in a moment - thanks.
After looking at your fault-free script, my best bet would also be a
corrupted index. Do you have myisamchk scheduled to run periodically (eg.
just after 09.00)? Because that would explain, why the error only occurs
once. Consider this scenario
08.55: The mailscript runs fine
Between 08.55 and 09.00: Something corrupts the index (perhaps the script
running af 08.55?)
09.00: The mailscript runs, but because of the faulty index, you send to
many mails
09.02: myisamchk is run and automatically repairs the index.
09.05: The mailscript runs fine
It might sound a bit far off, but I don't know your setup :-)
Probably a sensible idea to limit the query too - I think I'll
order by date
desc too just to make sure that recent mail gets sent.
Don't limit the query, instead do a conditional check in php, eg.
if ($db-recordcount 100) {
// send mail to yourself that something is wrong, log the number of
records and immediately do an extra query: EXPLAIN SELECT MailID, MailFrom,
MailTo, Subject, Body, Headers FROM
Mail WHERE Sent = 0
// Write the EXPLAIN result to a logfile.
exit;
}
This way you're able to diagnose the error when it hits again, which it
probably will.
Ask yourself, what could have caused a corrupt index? Power glitch?
- Carsten
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: WHERE ignored
-Oprindelig meddelelse- Fra: Mike Hall [mailto:[EMAIL PROTECTED]] Sendt: 27. april 2002 00:04 snip No other scripts perform UPDATEs on that table, only INSERTs. But MySQL returned the correct data at 8:55 and again at 9:05. It was only when it ran at 9am that it appeared to ignore the WHERE. According to my binary log, the Mail table wasn't changed at all during this time - so the results of all three queries should have been identical. I think you better show us the script. This might help to figure out the problem. - Carsten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: Max 127 records
Fra: Henning Olsen [mailto:[EMAIL PROTECTED]]
Sendt: 19. april 2002 14:39
Hey - can anyone help?
I have a MySql-db in which I can only have 127 records.
Using phpmyadmin to insert record number 128 (autoincrement) gets
this message:
INSERT INTO `kontakt` (`id`, `navn`, `adresse`, `postnummer`,
`by`, `telefon`, `kommentar`) VALUES ('', 'fsd', 'sfdg', 'sg',
'fdsg', 'sfg', 'sg');
MySQL returnerede:
Duplicate entry '127' for key 1
This is a classic :-)
You've created your primary key (the autoincremented field) as a TINYINT,
which only allows values in the range -128 to 127. auto_increment does not
support negative numbers, so when you've filled the first 127 records, it
cannot add any more values.
Alter the field to a INT UNSIGNED, then you're good to go
- Carsten
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: a query from php doen't work
Fra: savaidis [mailto:[EMAIL PROTECTED]] Sendt: 17. april 2002 22:14 Emne: a query from php doen't work I get a Query failed error on this: What could hapent? Try to alter this line: $result=mysql_query($buffer); to look like this: $result=mysql_query($buffer) or die($buffer.hr.mysql_error()); Then you will a) print the failed query, and b) print MySQL's own error-response. Then you cal probably figure out, what is wrong. - Carsten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: MySQL and stored procedures - v4.1 :(
Fra: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sendt: 16. april 2002 09:52 MySQL AB wants to satisfy as many users as possible. That means there may be more than one language available for writing stored procedures. For example, many mainstream databases currently support both Java and a proprietary language like PL/SQL or Transact-SQL. Well that sure sounds nice. I'm looking forward to this. :-) - Carsten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
