XAMMP and Mysql error
Hi, From yesterday 2009-08-26 i have an error message when my computer start. The mysqld.exe crash blablabla I search on g**gle 006B8853mysqld.exe and i find somebody in this mailing with this problem!! I don't change anything in my mysql config file, cause i didn't use mysql during the last month. I make some tests : 1- I lanch the mysql service --- ERROR the service command launched : C:\Program Files\Quiz\mysql\bin\mysqld --defaults-file=C:\Program Files\Quiz\mysql\bin\my.cnf mysql The files mysqld and my.cnf are used bellow so the 2 files work fine. 2- I lanch the mysql in console : mysql\bin\mysqld --defaults-file=mysql\bin\my.cnf --standalone --console --- OK I have the following version : MySQL 5.1.30 (Community Server) Here the err file : 090827 10:26:54 - mysqld got exception 0xc005 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=262144 max_used_connections=0 max_threads=151 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 133305 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... 006B8853mysqld.exe!??? The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. Thanks for any help. -- Regards. Gabriel TESSIER LOGOSAPIENCE 3, Rue Pierre et Marie CURIE 49070 ST JEAN DE LINIERES +33 (0)2.41.36.81.41 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Call a routine on mysqld startup
Hi, I must call a routine when mysqld start (to populate a heap table). I did not find any related options in mysqld --help --verbose. Is there a way to achieve this, without modifying the startup script ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Call a routine on mysqld startup
On Mon, 15 Jun 2009 10:00:00 -0400 Jim Lyons jlyons4...@gmail.com wrote: There's an option called init-file that will invoke an sql script on start up. That would probably work for you. Thanks you, that is what I was searching for. I guess I should clean my glasses :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Oracle , what else ?
-Original Message- After MySQL bought by the java maker, and now Sun bought by Oracle, How did I miss this!? It seems like the little fish are getting eaten by the bigger fish. I understand Microsoft is now going to buy Oracle. :-) (Sorry, just kidding) The real question is whether they will let MySQL wither and die by not providing updates for it? Well, MySQL is open source, right? And the source is available? I'm sure a team of devs will come to the rescue. As for MySQL, as a company, they don't make even close to the potential money they can. People do not really go to MySQL for support, which is the model RedHat uses. For MySQL, it's different, because the MySQL userbase by their very nature, solve problems for a living. They have the attitude of how can I fix things? How do I make things work the way I want? This has a serious adverse effect on MySQL as a company, because the number one revenue stream for any company whos main 'product' or 'service' is open source based, is the support contract. Is Oracle is too big to make MySQL updates any kind of priority? The updates are not going to be a priority, granted - but compatibility might be their goal. If they can produce an upgrade path straight to Oracle, for all the current users of MySQL, the price paid for Sun, will be like peanuts, an investment for a better future. But let's not forget, Sun have some pretty kick ass systems on the go. I've seen their thin client setup, for things like presentations, and just being able to work at any terminal in the building/small group of close proximity buildings/across the entire city . *sweet!* It seems that the larger the company and the more products they have, the less interest they have in their lower revenue making products. I hope this is not the case with Oracle, but the updates in the next year will determine where MySQL is headed. Just one guy's opinion. Mike It's a good opinion Mike :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL replication status plugin
Good morning guys, (and girls), I hope all is well. I've been given the task to, and I quote - Write a Nagios plugin to test the replication status of two servers by comparing the position on the master to that on the slave To save myself a lot of work, I'd like to know if anything has been done in this arena already, I would be over the moon, if someone has beaten me to it, but I am so not in the mood to write one! Any hints, recommendations, and ideas are wholly welcome! --- Kind Regards, Mr Gabriel
Re: renaming database
Or if RENAME DATABASE is not implemented yet: CREATE newDatabase; then for each table in the oldDatabase issue: ALTER TABLE oldDatabase.tblX RENAME TO newDatabase.tblX After all is done: DROP oldDatabase -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Certification
Thanx for all the messages but I wasn't talking about the list mysql@lists.mysql.com... I get all those mails... I was talking about the speficic certification list [EMAIL PROTECTED]. (Sorry for not being that clear !) And still no words about maximum space between DEV1 and DEV2 !!! Thanx -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Certification
As I understood to attain MySQL Developer you must pass 2 exams... DEV1 and DEV2... What I did not understood, yet... if there is any limit in the timespan between DEV1 and DEV2. Let's say I'll take DEV1 in late March... how many months can pass so that when i'll take DEV2 the results for DEV1 are still valid ? And is the list still ON ? ... I'm subscribed to the list... but didn't got any messages... Thanx for the answers ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB fixed file size, how much is left?
For what you described... you will not get a fixed size... If you have set file_per_table flag in my.cnf you might want to know that the .ibd files in the database directory are by default auto-extending... so those files WILL grow... along with your data... The shared tablespaces that you talked about (10 * 100MB) are still used by InnoDB for transactions and foreign keys reference (at least these two things)... Although I don't get why would you use 10 files of 100MB... why not 20 of 50MB... unless they are on different disks and partitions... I don't understand... I would personally go with at most 2 files arround 500MB... keeping in mind that you have file_per_table on !!! How big transactions are you expecting... how many clients are you expecting ? Answers to these questions can help you tweak the server... -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Request problem (with \\)
Hi,
It works, thanks you for your help :-)
ViSolve DB Team wrote:
Hi Gabriel,
Try as:
mysql select * from forum where topoc like %%;
To search for '\', specify it as ''; this is because the
backslashes are
stripped once by the parser and again when the pattern match is made,
leaving a single backslash to be matched against. (Exception: At the
end of
the pattern string, backslash can be specified as '\\'. At the end of the
string, backslash stands for itself because there is nothing following to
escape.)
Ref:
http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html
Thanks
ViSolve DB Team.
- Original Message - From: Gabriel Linder
[EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Friday, January 19, 2007 9:43 PM
Subject: Request problem (with \\)
Hello list,
I am currently trying to fix a bug in a search function with a
request like this one :
select * from forum where topic like '%[...]%' ;
where [...] is a string escaped by mysql_real_escape_string (C API)
and topic is a varchar field (not null).
It works, but there is a bug if someone is searching the character
backslash only ('\'), so the request become :
select * from forum where topic like '%\\%' ;
and it returns only topics who have a '%' in them, not a '\'. It is
the same result as if I were doing :
select * from forum where topic like '%\%' ;
To get the topics with a '\' (but it returns only the topics that
ends with a '\'), I must do :
select * from forum where topic like '%\\' ;
So it seems to me that the ending % is escaped even with '\\'. Is
this a normal behaviour ? Or am I missing something ?
Here are some infos about the server version, might be useful :
version = 4.0.20-standard
version_comment = Official MySQL-standard binary
version_compile_os = linux
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Request problem (with \\)
Hello list,
I am currently trying to fix a bug in a search function with a request
like this one :
select * from forum where topic like '%[...]%' ;
where [...] is a string escaped by mysql_real_escape_string (C API) and
topic is a varchar field (not null).
It works, but there is a bug if someone is searching the character
backslash only ('\'), so the request become :
select * from forum where topic like '%\\%' ;
and it returns only topics who have a '%' in them, not a '\'. It is the
same result as if I were doing :
select * from forum where topic like '%\%' ;
To get the topics with a '\' (but it returns only the topics that ends
with a '\'), I must do :
select * from forum where topic like '%\\' ;
So it seems to me that the ending % is escaped even with '\\'. Is this a
normal behaviour ? Or am I missing something ?
Here are some infos about the server version, might be useful :
version = 4.0.20-standard
version_comment = Official MySQL-standard binary
version_compile_os = linux
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM issues for UTF-8?
Why is MyISAM problematic... MyISAM is a storage engine with some features... InnoDB is another storage engine with other features... and so on... As far as I know MyISAM is default storage engine... unless you specify by hand another storage engine: CREATE TABLE xyz (colX INT NULL) ENGINE=_STORAGE_ENGINE_ I used UTF8 with MyISAM... and with InnoDB for Romanian characters and Chinese characters... and any combination worked well... And as far as I know... instead of all those commands you can issue: SET NAMES utf8; and it will suffice... I know it does for me ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM issues for UTF-8?
Read here: http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html have fun ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Varchar limit warning
Complementary to what Donna said, You can issue a SHOW WARNINGS sql after some of these queries... the cutt-off will be listed there ! You will get a mysql_result in the form: Level - Code - Message Warning - 1265 - Data truncated for column 'column_name' at row X. Gabriel PREDA On 1/5/07, Olaf Stein [EMAIL PROTECTED] wrote: Hi all If I insert a value to great for a field (e.g. '123456' into a varchar(5) field), mysql runs the insert without warning or error and cuts of what doesn't fit. How can I tell it to launch an error and abort the insert? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data types and space needs
Hi Olaf, For soon to be a decade I'm fighting with this question... !!! It's not a silly one... it's quite a tricky one... As many have noticed all INTEGER types (SMALLINT, MEDIUMINT, INT, BIGINT) have a LENGTH... by all means I can assure you that it has nothing to do with the maximum number you can store in a column or the size on the disk ! The only situation, known to me, when the length has a value is with the ZEROFILL atribute. Let's say you have 2 numbers to be stored: 4567 456789 If you insert then in a table in a column with the definition: INT(6) NOT NULL DEFAULT 0 You will get the same values and any value will take 4 bytes on the disk. If you insert then in a table in a column with the definition: INT(6) ZEROFILL NOT NULL DEFAULT 0 You will get : 004567 456789 as values and any value will also take 4 bytes on the disk. The only notable difference... as you can see... is in prepending 0 to the number to reach the desired length... if the number is greater than or equal to the declared length it has no effect. So AFAIK this is the purpose of the length...in INTEGER columns. -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Front
MySQL AB asked Hoyer... the lead developer to remove the MySQL part from the name of the application... and he used that as an excuse to stop the project. His option ! Now you can try MySQL AB's tools... http://dev.mysql.com/downloads/gui-tools/5.0.html Or you can go to the original developer of MySQL-Front... remember the old 2.5 gui... the developer is back... renamed the products... more on the product page and the forum... please go to: http://www.heidisql.com/ Good luck ! On 11/9/06, PBS Usenet [EMAIL PROTECTED] wrote: Can anyone tell me what's wrong: http://www.mysqlfront.de/ It's my favorite toll now it's gone -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API - Mysql 5
Hello,
Did you try to convert the .lib file to a GCC .a library ? See
http://mingw.org/mingwfaq.shtml#faq-msvcdll
If reimp doesn't work, you can try pexports and dlltool. I have an old
batch file I used for older MySQL versions at
http://athanatos.free.fr/EXE/implib.bat
[EMAIL PROTECTED] a écrit :
Hello,
I'm trying to compile some C code with the simple following code
(dev-c++):
#include stdio.h
#include stdlib.h
#include windows.h
#include mysql.h
int main(int argc, char *argv[])
{
MYSQL * mysql_con = NULL;
mysql_con = mysql_init(mysql_con);
return 0;
}
As the result, i obtain this :
gcc.exe -D__DEBUG__ main.o -o my_try.exe -LC:/Dev-Cpp/lib
C:/Program Files/MySQL/MySQL Server 5.0/lib/debug/mysqlclient.lib -g3
C:/Program Files/MySQL/MySQL Server
5.0/lib/debug/mysqlclient.lib(./debug/client.obj)(.debug$S+0x49cb):\build\mysql-5.0.2:
variable '_iob' can't be auto-imported. Please read the documentation
for ld's --enable-auto-import for details.
C:/Program Files/MySQL/MySQL Server
5.0/lib/debug/mysqlclient.lib(./debug/client.obj)(.text+0x192e): In
function `mysql_read_default_options':
e:\build\mysql-5.0:1026: variable '_iob' can't be auto-imported.
Please read the documentation for ld's --enable-auto-import for details.
C:/Program Files/MySQL/MySQL Server
5.0/lib/debug/mysqlclient.lib(./debug/dbug.obj)(.debug$S+0x24c1):\build\mysql-5.0.2:
variable '_iob' can't be auto-imported. Please read the documentation
for ld's --enable-auto-import for details.
C:/Program Files/MySQL/MySQL Server
5.0/lib/debug/mysqlclient.lib(./debug/dbug.obj)(.text+0x15): In
function `_db_push_':
I've compiled C code with a previous version of mysql lib client
without any trouble. I've tried with the --enable-auto-import as well
as the disable one.
Anyone have an idea?
Thank's in advance
Vincent Badier
--
Cordialement
Gabriel LINDER / JEUXVIDEO.COM
---
http://www.jeuxvideo.com : Le site numéro 1 des jeux video en français
http://boutique.jeuxvideo.com : Pour acheter tous ses jeux sur le net
---
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE Inv_Id
I would try: CREATE TABLE Inv_Id ( ID INT(12) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY, MID INT NOT NULL, FOREIGN (MID) REFERENCES 'Model' (ID) ); Note the UNSIGNED and ZEROFILL flags ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning a Value from a Stored Procedure
Let's suppose you have a table `t` with these columns: id - auto increment, primary key, not null a b c N If you do any of these: INSERT INTO `t` (a, b, c ..., K) VALUES(va, vab, vc, ... vK); INSERT INTO `t` (id, a, b, c ..., K) VALUES(0, va, vab, vc, ... vK); INSERT INTO `t` (id, a, b, c ..., K) VALUES(NULL, va, vab, vc, ... vK); In the above cases MySQL generates the ID for you... and you may retrieve it immediately after the query that generates it with: SELECT @LastGeneratedId := LAST_INSERT_ID(); Or you can use it in a 2 contigous INSERTS: INSERT INTO `t` (id, a, b, c ..., K) VALUES(NULL, va, vab, vc, ... vK); INSERT INTO `t2` VALUES(LAST_INSERT_ID(), f, g, h); Have fun ! But not that LAST_INSERT_ID will not be updated if you insert an explicit value (except: 0) INSERT INTO `t` (id, a, b, c ..., K) VALUES(145899, va, vab, vc, ... vK); this will not affect LAST_INSERT_ID() value. And another thing... LAST_INSERT_ID() is kept on a per connection basis... so it will not mix with other users LAST_INSERT_IDs -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple primary keys on one table?
You will have to UPDATE to NULL those fields, modify the aplication to enter NULL instead of empty-string... Then add the UNIQUE INDEX... In MySQL (unless modified) the dafault is that NULL values are incomparable thus allowing the creation of a UNIQUE INDEX. -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speed up index creation on existing tables?
For this table this is to late... leave it running... If you want to do this on another table(s)... and in general on huge loaded MySQL servers I recomend the following... Create a directory let's say /mnt/mem_fs Mount in it /dev/shm use tmpfs as filesystem... Now you have a directory that stores all the info in memory... if the available alocated memory in consumed then it will start swaping... but compute all values so that it dosen't... In my.cnf set a MySQL directive like: tmpdir = /mnt/mem_fs This way MySQL will create temporary tables in memory rather than creating them on disk !!! I'm pretty sure you can figure out the speed improvment ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer On 10/5/06, David Sparks [EMAIL PROTECTED] wrote: Its already been running 2 days. I probably need to index some more columns in another table -- is there anything that can be done to speed this up? dump and re-import is impractical. Server is decent -- 4xcpu, 16GB RAM... Thanks, ds -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question
It's possible that mysql couldn't create the SOCKet file... For emergency connection use: mysql -u root -h 127.0.0.1 -p Do not use localhost as this instructs the client to go through the socket... but if you say 127.0.0.1 the client will use TCP... Next... make sure that mysql can indeed create the socket under /tmp... check the permisions... and I must say this location is strange I have /var/lib/mysql/mysql.sock ... also check this out... To set it to another location use my.cnf file Good luck ! On 10/3/06, Feliks Shvartsburd [EMAIL PROTECTED] wrote: Hi I have several problems. I'm using MySql 5 and it is running on Linux. When I'm trying to execute mysql -u root -p I get the following: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) I'm also not able to stop the server. When I run mysql.server stop it gives me some garbage. Please help. Thanks -Original Message- From: George Law [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 11:25 AM To: Feliks Shvartsburd; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Question show processlist gives you an abbreviated list of queries. show full processlist gives you the full queries. -Original Message- From: Feliks Shvartsburd [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:57 PM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Question Does anybody know how can I see what queries are currently being executed? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get size of biggest blob (for max_allowed_packet)?
SELECT LENGTH(`CnText`), CHAR_LENGTH(`CnText`), BIT_LENGTH(`CnText`) FROM `Translations` Where: - LENGTH or OCTET_LENGTH - length in bytes - CHAR_LENGTH or CHARACTER_LENGTH - length of the string in characters - BIT_LENGTH - it's LENGTH * 8 You will see a difference betwen LENGTH and CHAR_LENGTH only if you use multi-byte strings ! ... I use CN and others... So... try: SELECT MAX(LENGTH(`CnText`)) FROM `Translations` -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer On 9/14/06, Dominik Klein [EMAIL PROTECTED] wrote: For adjusting max allowed packet value, I need to know the maximum size of my blob fields. How can I get that? Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
On 9/7/06, Paul McCullagh [EMAIL PROTECTED] wrote: It sounds like you program allows ad-hoc queries, so why don't you just limit the number of rows returned by a select? For example you could limit the number of rows to 1001. If the server returns 1001, then display 1000 and tell the user there are actually more rows. The user should then apply further conditions. Some things worth mentioning when using LIMIT: In MySQL the LIMIT clause is applied just before sending the result to the client... so a SELECT col1, col2, ... , colN FROM tableName LIMIT x, y will be performed as SELECT col1, col2, ... , colN FROM tableName and before sending the result to the client the LIMIT will be applied... There are some things to consider... if you have an ORDER BY clause MySQL will stop sorting after LIMIT clause is satisfied... To skit the COUNT(*) query you must use: SELECT SQL_CALC_FOUND_ROWS col1, col2, ... , colN FROM tableName LIMIT x, y This way MySQL will store internally the number of rows that would have been returned without the LIMIT clause [The drawback is that if you have an ORDER BY clause MySQL will not stop after sorting LIMIT x,y rows... as I mentioned above] But the gain is that the second query that will return the number of rows without the LIMIT clause: SELECT FOUND_ROWS() will return instantly. -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why size of table c united from table a and b are bigger than a+b ?
The default charset of c is utf8, and that of a and b is latin1. Maybe charset cause size of table increased? -- Thanks Regards Chylli Of course this is it ! Latin1 needss one byte per character ... while utf needs from 1 to 3 bytes per character. Still the rate of growing 1,9 can only be explained (as far as i know) if you have CHAR columns. On utf8 if you have a column defined as CHAR (20) CHARSET utf8... to keep the table fixed-size (whether you store in it all 1byte characters or 3bytes characters) MySQL will allocate 3 bytes for every character so that column will be stored in 60bytes. If a and b are VARCHARs and c are CHARs that's it ! If you have all VARCHARs I'm in the dark ! Hope it helps ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with WHERE clause
You must specify explicitly what 'fee' to use... so if you wand every p.fee to be greater than zero then you must do: SELECT s.id, s.name, SUM(p.fee) AS fee FROM serie AS s INNER JOIN race_serie AS rs ON rs.serie_id = s.id INNER JOIN races AS r ON r.id = rs.race_id INNER JOIN participants AS p ON p.race_id = r.id WHERE s.receipt = 1 AND p.rider_id = 236 AND p.fee 0 GROUP BY s.id ORDER BY s.f_date; If you want the sum to be larger that zero then you would have to do: SELECT s.id, s.name, SUM(p.fee) AS fee FROM serie AS s INNER JOIN race_serie AS rs ON rs.serie_id = s.id INNER JOIN races AS r ON r.id = rs.race_id INNER JOIN participants AS p ON p.race_id = r.id WHERE s.receipt = 1 AND p.rider_id = 236 AND SUM(p.fee) 0 GROUP BY s.id ORDER BY s.f_date; -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to disable foreign_key_checks when using mysqlimport?
Try: ALTER TABLE `tbl_name` DISABLE KEYS; -- now insert in the TXT file ALTER TABLE `tbl_name` ENABLE KEYS; I think this is what you were looking for ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to understand why Select running out of memory if table not used
The JOIN criteria was there: 'event.cid=data.cid' His query was fine: Select event.cid, event.timestamp from event, data Where ( event.timestamp between '2006-05-01' AND '2006-05-15' ) and event.cid=data.cid; It may be rewritten into: SELECT event.cid, event.timestamp FROM event JOIN data ON event.cid=data.cid WHERE event.timestamp BETWEEN '2006-05-01' AND '2006-05-15'; ... Or ... SELECT event.cid, event.timestamp FROM event JOIN data USING(cid) WHERE event.timestamp BETWEEN '2006-05-01' AND '2006-05-15; Although that was not the issue... Maybe you ran out of memory into one of: max_allowed_packet net_buffer_length max_join_size Try enlarging those values... on the server also... But first try to run with '--compress' maybe this will fix... -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAX_JOIN_SIZE
Try issuing select @@sql_max_join_size And also make a product from the number of rows from all the tables involved in the join (with WHERE clause applied) and if it exceeds the number given from the select I gave you above... then that's your reason ! Also bare in mind that although you only request 30 rows with LIMIT... MySQL will still JOIN the tables... the LIMIT clause is applied just before sending the resultset to the client ! If you can't do it otherwise try setting a higher sql_max_join_size... or try using some temporary tables ! Good fortune ! -- Gabriel PREDA Senior Web Developer #1104 - The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cumulative Totals
cumulative total index ... SELECT SUM(amount) , DATE_FORMAT( `paymentDate` , '%Y-%m' ) FROM `payments` GROUP BY DATE_FORMAT( payments . date , '%Y-%m' ) . Is there any way to get a running cumulative total directly from mysql? Something like: amount | paymentDate 200| 2005-01 258| 2005-02 Will WITH ROLLUP do what you want ? SELECT SUM(amount) , DATE_FORMAT( `paymentDate` , '%Y-%m' ) FROM `payments` GROUP BY DATE_FORMAT( payments . date , '%Y-%m' ) WITH ROLLUP This will give you something like: amount | paymentDate 200 | 2005-01 58 | 2005-02 258 | NULL Will it do ? -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
See lock table
Friend, I need to see if a table is locked by some application or some user. Do you know some tools for this? (gpl license better). Or some sentence? When a table is locked, how can i unlock this table? My problem is that some applications cause an error and the user closes it with the task manager then the table remains locked. Regards -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with: MySQL 5.0.21 - 64bit
Spoken too soon... Yesterday MySQL died again... there is nothing in the log files... (be it mysqld.log, .err, or /var/log/messages). The hosting provider is running an application called 'big-brother' and a lot of sef-faults appear in the logs from this. I don't know if thins is the cause... if this application is faulting mysql also... The behaviour: the whole machine freezes... all that can be done is to hit the RESET button on the machine ! Friday we downgraded from 5.0 to 4.1. At first we compiled the MySQL server ourselvs... next we deleted it and installed a RPM version... none worked... It appears we hit bug 15815... (http://bugs.mysql.com/bug.php?id=15815)... So be aware with innodb_thread_concurency on 64bit machines... Below are some values as an orientation... for innodb_thread_concurency value per machine workload (at arround 600 simultaneous connections... all used): innodb_thread_concurency / CPU-workload 8 / 90% 4 / 75 - 85% 3 / 60 - 70% 2 / 25% So set it lower... lower... lower... when I left the office friday my colleagues were still working at this... I'll keep you all updated ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to share data between servers
1. One-Way-Replication: server2 gets data from server1, if server2 does not write in the database... if it writes: 1.a 2-Way-Replication: server2 gets data from server1 AND server1 gets data from server2... :) ... 2. FEDERATED Storage Engine: the actual data is stored on server1, the tables created on server2 do not reside on it... but on server1... the queries are actualy sent to server1 processed and the result is returned to server2... From these 2 i'll put my money on the first one ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merging two fields; references to fields
You have at least 2 options (witch came instantly in my mind):
CONCAT_WS - concatenation with separator
SELECT CONCAT_WS('/', firstname, lastname) AS name FROM tablename
CONCAT - concatenation of arguments
SELECT CONCAT(firstname, SPACE(1), lastname) AS name FROM tablename
ATTN: SPACE(n) - generates a string with a space in it !
--
Gabriel PREDA
Senior Web Developer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB obeyance of PRIMARY KEY constraint - confirmation
I did read in the manual that the field level REFERENCES constraints on InnoDB tables do not work as expected and one has to first define a table level index and then create a table-level FOREIGN KEY constraint for the field to make it work. I believe this is just a hack to keep things faster... if you have a FOREIGN KEY constraint on some columns that do not have an index the FOREIGN KEY check will take longer... if you do... then the check will be faster... this was the reason for this ! Starting with MySQL 4.1.2, these indexes are created automatically... you only need to make the FOREIGN KEY constraint. I just would like to know if that's the case with the field-level PRIMARY KEY constraints as well. Do I have to define table-level PRIMARY KEY constraints as well? No... not on PK ! (as far as i know) I also did read that InnoDB is very good at long PRIMARY KEYs. Does long primary keys means keys having more than one field? or keys having a greater character length? Under contrary in many articles about optimizations i've sen keep those primary keys small on InnoDB... (yeap found it: http://jeremy.zawodny.com/mysql/mysql-optimization.html - slide:37/80) -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with: MySQL 5.0.21 - 64bit
Yup... For now the problem stopped... These humongos values were because they were initialized at MAX_INT on that 64bit machine... In the my.cnf file they were not mentioned at all ! 2^64 - 1 == 18446744073709551615 Now look at the values below ! -- Gabriel PREDA Senior Web Developer On 6/22/06, Dan Buettner [EMAIL PROTECTED] wrote: Gabriel, in your SHOW VARIABLES, I see a handful of settings that are much, much larger than normal: | max_binlog_cache_size | 18446744073709551615 | max_join_size | 18446744073709551615 | max_seeks_for_key | 18446744073709551615 | max_write_lock_count| 18446744073709551615 | myisam_max_sort_file_size | 9223372036854775807 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multi-column indexes on InnoDB tables
Yes they're all right ! Database is the last thing on your problem list... I don't know how you thought the system... if it's WEB based... more problems arrise... In a potential scenario... I miself would power up the database server with an UPS... because it contains DATA... the application... you'd probably have a backup... in case of a power failure the DATA survives... you dont' care about the stations In case of network failure... you don't care about the stations because the data is in one place... Now for disaster recovery you may have the server in a part of the headquarter... and a REPLICA (use MySQL replication...) in the other part of the headquarter. ( I did this in the past... the domain controller that kept all the info from the company had the UPS, all stations saved all data on the domain controller (even Windows profiles...) in case of a power failure the domain controller is up and running and holding all the data up to the last SAVE given by any user Now imagine instead of domain controller is MySQL... in your case... ) -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with: MySQL 5.0.21 - 64bit
Hi list, Since we bought a better hardware for our dedicated MySQL Server we have been running into some problems. We are using: - Fedora Core 3 - 64bit version - Kernel: 2.6.9-1.667smp - x86_64 - MySQL 5.0.21-standard - for 64bit - RAM: 4 GB - RAID 5 matrix with 3 SCSI disks at 15k rotations We are using InnoDB tables (with one or 2 exceptions... for some FullText indexes)... We are not using transactions... yet ! I'll drop config. details lower... Still at given moments MySQL hangs... it does not accept connections anymore... We can't kill the process... with KILL command... the only thing we can do is ask the hosting provider to do a HARDWARE reset... and someone goes to the machine and pushes the reset button... this hppens at least once a week... Does this happened to someone else ? What was the problem ? Thanx in advance ! -- Gabriel PREDA Senior Web Developer - --- CONFIG DATA -- - - PROCESORS from 0 to 7: processor : 0 (up to processor 7 the specs. are the same) vendor_id : GenuineIntel cpu family : 15 model : 4 model name : Intel(R) Xeon(TM) CPU 2.80GHz stepping : 8 cpu MHz : 2793.261 cache size : 16 KB physical id : 0 siblings : 4 fpu : yes fpu_exception : yes cpuid level : 5 wp : yes flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm pni monitor ds_cpl est cid cx16 xtpr ts bogomips : 5537.79 clflush size : 64 cache_alignment : 128 address sizes : 36 bits physical, 48 bits virtual TOP output: top - 00:59:44 up 10:14, 1 user, load average: 0.46, 0.54, 0.64 Tasks: 82 total, 1 running, 81 sleeping, 0 stopped, 0 zombie Cpu0 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu1 : 0.3% us, 0.3% sy, 0.0% ni, 99.3% id, 0.0% wa, 0.0% hi, 0.0% si Cpu2 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu3 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu4 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu5 : 6.3% us, 1.7% sy, 0.0% ni, 91.4% id, 0.0% wa, 0.3% hi, 0.3% si Cpu6 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu7 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Mem: 4038412k total, 1614864k used, 242 Mem: 4038412k total, 1617176k used, 2421236k free, 135060k buffers Swap: 2048276k total,0k used, 2048276k free, 367664k cached MY.CNF: [mysqld] set-variable = max_connections=900 safe-show-database set-variable = max_heap_table_size=64M set-variable = tmp_table_size=64M set-variable = query-cache-size=500M set-variable = query_cache_limit=30M set-variable = long_query_time=3 set-variable = table_cache=600 set-variable = thread_cache_size=32 set-variable = thread_concurrency=8 set-variable = key_buffer_size=32M set-variable = interactive_timeout=60 set-variable = wait_timeout=60 set-variable = max_allowed_packet=3M set-variable = sort_buffer_size=6M set-variable = ft_min_word_len=3 set-variable = binlog_cache_size=0 set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_thread_concurrency=8 set-variable = innodb_buffer_pool_size=400M set-variable = innodb_flush_log_at_trx_commit=0 set-variable = innodb_autoextend_increment=50M set-variable = innodb_fast_shutdown=0 set-variable = innodb_log_buffer_size=4M set-variable = innodb_max_dirty_pages_pct=75 set-variable = innodb_status_file SHOW VARIABLES: +-+---+ | Variable_name | Value | +-+---+ | automatic_sp_privileges | ON | | back_log| 50 | | basedir | / | | binlog_cache_size | 4096 | | bulk_insert_buffer_size | 8388608 | | completion_type | 0 | | concurrent_insert | 1 | | connect_timeout | 5 | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | engine_condition_pushdown | OFF | | expire_logs_days| 0 | | flush | OFF | | flush_time | 0 | | group_concat_max_len| 1024 | | have_archive| YES | | have_compress | YES
Re: About the leftmost index prefixes using nounique index
Basically it says that if you have an index let's say INDEX_1 on columns: INDEX_1 : a, b, c, d MySQL will act as if you had setup indexes on: INDEX_1_1 : a, b, c INDEX_1_2 : a, b INDEX_1_1 : a A query like: SELECT a FROM table_name WHERE a 9; - will use the index SELECT a, b, c FROM table_name WHERE d 9; - will use the index Hope this clears up things ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About the leftmost index prefixes using nounique index
MySQL wil only use one index per table in a query... this is why in most cases a composite index will do better that a single column index. And for the second is true... this is leftmost rule... You have an index on: a, b, c You gain indexes on: a, b a But you will need to set up yourself an index on: a, c or c, a Try variations... of indexes toghether with EXPLAIN SQL... Also have a look at MySQL Optimization by Jeremy Zawodny at http://jeremy.zawodny.com/mysql/mysql-optimization.html -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with altering a table
When creating the InnoDB table the InnoDB engine asigns to the FOREIGN KEY you defined a symbol. On my server it generated dbmail_messageblks_ibfk_1... and if in the ALTER statement I entered: DROP FOREIGN KEY dbmail_messageblks_ibfk_1 Then the ALTER table worked fine... If you want to continue with this you should add a symbol name manually like this in the create table statement: CONSTRAINT `fk_message_idnr_manually_set` FOREIGN KEY (`message_idnr`) REFERENCES `messages` (`message_idnr`) Now in the ALTER statement you will have to write before you change the name of the column: DROP FOREIGN KEY `fk_message_idnr_manually_set` If you DROP an index a FOREIGN KEY based on that index will not be dropped automaticaly... Hope this helps ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyMonitor - A novel SQL monitor tool for MySQL release 1
Anders I want this for Windows? how do I do it? Anders Karlsson escribió: The first version of MyMonitor is now available in source form on sourceforge and can be downloaded from: http://sourceforge.net/projects/mymonitor The reason for this tool is simple, and it's also a bit different from other similar tools (at least the ones I've looked at). The tool gets the output from SHOW PROCESSLIST repeatedly and does some magic to this to count the number of executions and execution time. The difference is in how it handles the SQL statement text. Before somparing the SQL text of a statment to the statements in the previous run of SHOW PROCESSLIST or to the SQL in an internal list of frequently accessed SQL statements, it removes any references to literal values. So the these statements: SELECT * FROM customer WHERE cust_id = 123; and SELECT * FROM customer WHERE cust_id = 123; Which in SHOW PROCESSLIST is shown as 2 distinct statements are handled as two executions of the same statement: SELECT * FROM customer WHERE cust_id = ?, which is usually how it works anyway. The output through ncurses, a bit like top. The collected statements may also be written to file, and there are a bunch of other settings, and there is also documentation in shape of a User Guide in PDF format. The current version is considered a beta, so comments on porting are more than welcome. I have so far only run it on a couple of Linux'es, but there is more to come. ncurses library is required, as well as the MySQL Client library of course. Enjoy, and comments are welcome, I hope this tool will turn out useful -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Search and Replace a word in memo field
Hello friends. In need your help. I have a table with a field Name: Varchar(255). In this field Name I have a detail and I need replace in all records a word for another. For example: I have vhs and I want to change the word vhs for the word dvd in all records. How can I do this? -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search and Replace a word in memo field
but I have a problem with this solution, because I have the words in the middle of the phrase. For example: Movie Kill Bill Vol 1 in vhs format, and I want to replace IT with Movie Kill Bill Vol 1 in dvd format. In Another case I have: VHS Trade Konami. . and I want to replace with DVD Trade Konami. so basically the word that I need to replace is located in the middle of the phrase. João Cândido de Souza Neto escribió: update table set field=REPLACE(field, 'vhs', 'dvd'); Gabriel Mahiques [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hello friends. In need your help. I have a table with a field Name: Varchar(255). In this field Name I have a detail and I need replace in all records a word for another. For example: I have vhs and I want to change the word vhs for the word dvd in all records. How can I do this? -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search and Replace a word in memo field
Thanks friends. Duncan Hill escribió: On Monday 12 June 2006 14:16, Gabriel Mahiques wrote: but I have a problem with this solution, because I have the words in the middle of the phrase. For example: Movie Kill Bill Vol 1 in vhs format, and I want to replace IT with Movie Kill Bill Vol 1 in dvd format. In Another case I have: VHS Trade Konami. . and I want to replace with DVD Trade Konami. so basically the word that I need to replace is located in the middle of the phrase. That's what the string function replace() does. Scanned by mailCritical. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How To Pronounce MySQL
the real pronuntiatios is.. MAIESEKUELE spanish, spanish... the rest is a dream... when I start up the system, it say... wellcome.. I'm the best...my name is MAIESEKUELE and I'm free oh yea... I want to f... me to MS SQLServer.. ohhh yeh Melvin Zamora escribió: Or you can name it by your name JesseSql as long as you are in control :-) ...then no one would attempt claim Hey! thats my S-Q-L don't touch it. Its all yours. -Melvin Jesse [EMAIL PROTECTED] wrote: I guess perfectionism in a lot of what I do is a problem I've got to deal with. But I hate saying it one way and looking stupid when talking with real guru's. :-) Thanks, Jesse - Original Message - From: Peter Lauri To: 'Jesse' ; 'MySQL List' Sent: Thursday, June 08, 2006 3:34 AM Subject: RE: How To Pronounce MySQL Btw, better to ask and look stupid, then not to ask and be stupid... But this question does not give you a stupid look, more a look of a person seeking perfection :) -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Thursday, June 08, 2006 7:31 PM To: MySQL List Subject: How To Pronounce MySQL This may be a really stupid question, but I hate looking stupid if I can avoid it. :-) I have been using Microsoft SQL Server for a while, and I'm now trying to switch all our applications over to use MySQL. Microsoft SQL Server is pronounced Sequel Server. Is MySQL pronounced My Sequel, or is it pronounced My S-Q-L? I mean, generally speaking? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How To Pronounce MySQL
yo digo My eSe Qu eLe, but spanish aren't usefull for you for SQL Server I say Mierda (shit in english...) Good by friends.. Open source for everybody... the knowledge belongs to the humanity.. .. Chris Sansom escribió: At 7:38 -0500 8/6/06, Jimmy Guerrero wrote: The official way to pronounce MySQL is My Ess Que Ell (not my sequel), but we don't mind if you pronounce it as my sequel or in some other localized way. Miss Quill? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL-on delete no action
Barry, You Give Ass (en castellano basico seria Das ocote, para escribir eso mejor no escribas nada) Barry escribió: Nenad Bosanac schrieb: Hi all I made mistake i n my last post so now i make it true. I have problem in MYSQL query tool. Well i put on foreign key on delete no action but next time when i edit that table it said that is on delete is restrict. Why is that ? I use MYSQL Query tool and my database is INNODB. Is there some kind of way to make it work or it is some kind of default value for innodb type of database? Thanks Sorry that english is very hard toi understand. Please post your queries probably that helps a bit more. Sorry no idea on what you want ^_^ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to rename a DB
I think the correct way... as planned for the 5.1 release is as follows: -- -- Start renaming database named old_db into new_db CREATE DATABASE new_db; ALTER TABLE old_db.table_1 RENAME new_db.table1; ALTER TABLE old_db.table_2 RENAME new_db.table2; ALTER TABLE old_db.table_N RENAME new_db.tableN; DROP DATABASE old_db; -- -- End renaming database -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer
Table so slow to read
Hi frieds. I have the next problem. I have a dedicated server with tables and I have a program that read some tables of this server. But when the program consults over 1 table (the query's result are a few records) it is very slow. But if I execute the same program in other server with a database copy (exactly the same tables) the program give me the result fast. The two servers are exactly the same (mysql 5.0.15), the application was developed in Delphi with Zeos componentes What could it be the problem? Over the server there are 75 connections, but we have max_connections=200; Thanks. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table so slow to read
My name is Gabriel, Saludos Cordiales is the same than Best Regard in spanish. The server explanin is the same. The table structure is the same, the application is the same (redirect the data source only), the quantity of record is the same. All is the same, I copy the database from one server to other Prasad escribió: Hi Saludos cordiales., For both the server the explain plan is same? -Prasad Sify. - Original Message - From: Gabriel Mahiques [EMAIL PROTECTED] To: MySQL Lista mysql@lists.mysql.com Sent: Wednesday, May 03, 2006 7:03 PM Subject: Table so slow to read Hi frieds. I have the next problem. I have a dedicated server with tables and I have a program that read some tables of this server. But when the program consults over 1 table (the query's result are a few records) it is very slow. But if I execute the same program in other server with a database copy (exactly the same tables) the program give me the result fast. The two servers are exactly the same (mysql 5.0.15), the application was developed in Delphi with Zeos componentes What could it be the problem? Over the server there are 75 connections, but we have max_connections=200; Thanks. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch India vs. England LIVE, Hot videos and more only on Sify Max! Click Here. www.sifymax.com Get to see what's happening in your favourite City on Bangalore Live! www.bangalorelive.in -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql add multiple index
If you can afford a lock on the table to last a little longer you can go with creating the 2 indexes at once. If not create the one by one... i'm sure some other queries will be honoured between those ALTER statements. From MySQL 4.0 we have: ALTER TABLE ... DISABLE KEYS and ALTER TABLE ... ENABLE KEYS Using the above when you have many inserts the missing index entries are created only once... maybe the same is with adding 2 indexes at a time instead of adding one at a time... So it is possible (although i din't found anything in the manual) that adding 2 indexes at a time will be faster ! [I'll get back if i'll find something in the Certification Study Guide... a pretty nice book !] -- Gabriel PREDA Senior Web Developer
Re: Mysql add multiple index
Me again... in the Certification Study Guide it is writen that is more efficient to add 2 (or many) indexes at a time then adding them individualy... but they don't say why ! I stand by my initial advice: *If you can afford a lock on the table to last a little longer you can go with creating the 2 indexes at once.* *If not create the one by one... i'm sure some other queries will be honoured between those ALTER statements.* -- Gabriel PREDA Senior Web Developer
Re: Query Help
SELECT id, count(*) AS cnt FROM `table_name` GROUP BY id ORDER BY cnt DESC [ LIMIT 1 ] -- Gabriel PREDA Senior Web Developer
is not not valid floating point for field
Hi friends. I have another problems with database migration from MySQL 4 to MySQL 5. We have many applications developments in Visual Basic 5. Under Mysql 4 if I put , (comma) for decimal value (we are in Argentina and we use comma for decimal separation) and war a valid value. When we migrated to MySQL 5, the application give us the error is not not valid floating point for field. They acept only . (point) for decimal value, but we need that to accept comma. Where do I have to configure this point? Mysql 5.20 Operating Systema: Linux Mandrake -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with Mysql 5 and Visual Basic 5
I migrated to mysql 5 but the applications with Visual Basic 5 return error in data. We have installed ODBC 3.51 -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error wiht VB 5 and MySQL
Hi. A Brief description about my problem. We have many applications development in Visual Basic 5. These applications read data from MySQL 4.0 installed on Linux (and odbc 3.51.06). Many fields in our tables are in decimal format. Last weekend we installed MySQL 5 on Linux When we ran the applications they had an error: all data in decimal format return ??? (for example: if the data must be 1345,68 the form show ). Can anybory help me. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error wiht VB 5 and MySQL
but, when the fields are float type, this error don't happen. When the fileds are float the result is the correct (if I have 1.2569 in the table, in the application I see 1.2569.) Daniel da Veiga escribió: Check the way your forms deal with the data from the resultset, any implicit conversion? Maybe a declaration of type that is casting another type for the value returned from the resultset. Also check this: http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html On 4/24/06, Gabriel Mahiques [EMAIL PROTECTED] wrote: Hi. A Brief description about my problem. We have many applications development in Visual Basic 5. These applications read data from MySQL 4.0 installed on Linux (and odbc 3.51.06). Many fields in our tables are in decimal format. Last weekend we installed MySQL 5 on Linux When we ran the applications they had an error: all data in decimal format return ??? (for example: if the data must be 1345,68 the form show ). Can anybory help me. -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- Saludos cordiales. Ing. Gabriel Mahiques Dto. Control de Gestión ELECTROINGENIERA S.A. Telefono: 474 1414 Uspallata 1461. Barrio San Martín. CP: X5008HSH Córdoba. República Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext wildcards
Try: http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html -- Gabriel PREDA Senior Web Developer
Re: describe table : improvement
Sorry forgot to mention MySQL version 4.1.X This option is operational as of MySQL 4.1. (It is allowed but ignored in earlier versions.) -- Gabriel PREDA Senior Web Developer
Re: describe table : improvement
COLUMN COMMENTs are not the same as TABLE COMMENT... For TABLE COMMENT you should use: SHOW TABLE STATUS LIKE 'table_name' *Gilles *(the starter of the thread) wanted COLUMN COMMENTs. -- Gabriel PREDA Senior Web Developer
Query Cache configuration
Hi friends, I need your help. I want to configure the Query Cache, but it dosn't work. I have installed MySQL 5.0.19 in Mandrake Linux. The server has 2GB memory ram, 150 gb hard disk, exclusive for dbms. I configure my system how say the home page. I follow step by step the instructions. (http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html) If I execute *show variable like 'have_query_cache'*, the result is YES. The follow image show you my status into the global varaibles. mysql show global variables like 'query%'; +--+--+ | Variable_name| Value| +--+--+ | query_alloc_block_size | 8192 | | query_cache_limit| 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 52428800 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | +--+--+ 7 rows in set (0.00 sec) After execute the same query (select * from recursos) and run the command show status like 'Qcache%' I obtain the next result (second image): mysql show status like 'qc%'; +-+--+ | Variable_name | Value| +-+--+ | Qcache_free_blocks | 1| | Qcache_free_memory | 52419952 | | Qcache_hits | 0| | Qcache_inserts | 0| | Qcache_lowmem_prunes| 0| | Qcache_not_cached | 4| | Qcache_queries_in_cache | 0| | Qcache_total_blocks | 1| +-+--+ 8 rows in set (0.00 sec) Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: describe table : improvement
It is: SHOW FULL COLUMNS FROM a_table You will get 2 extra columns: - Privileges (showing the privileges of the user for that column) - Comment (showing a per column comment) When creating a table you can add a comment using COMMENT keyword: CREATE TABLE a_table ( a_column CHAR(30) CHARSET utf8 COMMENT 'Some comment' ); Is this... what you needed ? -- Gabriel PREDA Senior Web Developer
Re: Do if and elseif and other calculations
You can do something like this: SET @diff = 0; SELECT count(*) AS number_of_holes, tour_player_score.strokes - tour_scorecard_hole.par AS overpar, IF(overpar0, @diff-(overpar*number_of_holes), ELSEVALUE) FROM `tour_player_score`, tour_scorecard_hole WHERE tour_player_id=175 AND tour_scorecard_hole.id=tour_player_score.scorecard_hole_id GROUP BY overpar; And instead of ELSEVALUE you can insert another IF... There is also a CASE in MySQL... you can also use that one... What i don't see here is a DEFAULT value... do you have any ? -- Gabriel PREDA Senior Web Developer
Re: mysql on tmpfs
If there is plenty of free memory... Can't you completly disable SWAP ? -- Gabriel PREDA Senior Web Developer On 4/7/06, Atle Veka [EMAIL PROTECTED] wrote: However, even though there is plenty of free memory linux makes weird decisions from time to time, temporarily killing performance, swapping in/out to make room.
Re: better way of doing 1800 sequential updates?
Because you have no indexes on that table... MySQL will open and search within the entire table. If you would have had an index MySQL would know how to go directly to the row you want to update. You are always looking for rows with: WHERE id =? So there you have... you must add an index on the column named id ! Run in MySQL client: ALTER TABLE ultimas_respuestas_snmp ADD INDEX someNameForTheInde4x(id); It will take a while... but it will get you faster. -- Gabriel PREDA Senior Web Developer
Re: Help Needed
If you do an INSERT and you generate an new number in an AUTO_INCREMENT field the new value generated can be retrieved using: *SELECT LAST_INSERT_ID();* You are not required to retrieve into the application and then use it back in another SQL statement... you can use user variables: *SELECT @lastGenerated:=LAST_INSERT_ID();* And then use: the variable in another query: *INSERT INTO someTbl (id, someCol, somCol2) VALUES (NULL, @lastGenerated, 'OutsideValue'); * Be warned that LAST_INSERT_ID() is functional only if you did not specified the AUTO_INCREMENT field's value... only if you did not specified the AUTO_INCREMENT field at all... or in the insert statement you used NULL or 0 as a value... those values are considered magic by MySQL in case of an AUTO_INCREMENT field. Now part 2... if you didn't created an AUTO_INCREMENT field... and only want to use the last value from that table: Either you use a MAX() request: *SELECT @lastGenerated:=MAX(columnName) FROM tableName* Or: *SHOW TABLE STATUS LIKE 'tableName'* and somewhere in the result is the AUTO_INCREMENT value. Good luck ! -- Gabriel PREDA Senior Web Developer
Re: On Duplicate Key....
Try: INSERT *IGNORE* INTO table (a,b) VALUES (1,2) -- Gabriel PREDA Senior Web Developer
Re: new password will not be effective for connection immediately
Only if you run this SQL statement: *FLUSH PRIVILEGES* That will make the new password effective. -- Gabriel PREDA Senior Web Developer
Re: Way of declaring variables?
First of all... yes... in MySQL variables are declared and used with @ Now for the : ... there is no particular role... they're there because otherwise the SQL parser will be confused... *SELECT column1, @neededValue=column2 FROM table_name LIMIT 1* *SELECT column1, @neededValue:=column2 FROM table_name LIMIT 1* In the first SQL the parser would test whether @neededValue is equal tot the value in column2 instead of assigning to @neededValue the value from column2 This confusion can happen only in SELECT statements... so you are required to add : only in SELECT statements. In SET statements you don't need that... you can write: *SET @last = last_insert_id()* Or with values from outside MySQL: *SET @iNeedThis = 'someText';* Then use both in an insert statement: *INSERT INTO table (lid, txt) VALUES (@last, @iNeedThis);* Hope this cleared up things ! -- Gabriel PREDA Senior Web Developer On 4/3/06, Yemi Obembe [EMAIL PROTECTED] wrote: Got the snippet from the mysql website: select @last := last_insert_id() i av the hunch that is to assign the variable 'last' to the last_insert_id(). Im i right? is placing @ before a word mysql's way of declaring variables? what's the work of the colon preceeding the equal sign?
Re: Fultext search issues
You ought to use the *Boolean Full-Text Searches.*
You would then do a:
SELECT title, Comment FROM table_name WHERE MATCH (Comment) AGAINST ('+foo
+bar' IN BOOLEAN MODE);
This way the rows that contain both words have higher relevance... those
that have only one... will have lower relevance.
Or you could use:
SELECT title, Comment FROM table_name WHERE MATCH (Comment) AGAINST ('foo
bar' IN BOOLEAN MODE);
Note the double quotes inside single quotes... ' foo bar '... needless to
say what it does... everybody had googled that way at least one time !!!
--
Gabriel PREDA
Senior Web Developer
Re: Customer Recommendation Query
You haven't told how many members are there. Anyway... i've worked out something with a colleague at work... Given your tables: *CREATE TABLE `list_problem_members` ( `id` int(11) NOT NULL auto_increment, `member` varchar(11) NOT NULL default '', PRIMARY KEY (`id`) );* ** *CREATE TABLE `list_problem_actions` ( `id` int(11) NOT NULL auto_increment, `action` varchar(11) NOT NULL default '', PRIMARY KEY (`id`) );* ** *CREATE TABLE `list_problem_ma` ( `id` int(11) NOT NULL auto_increment, `member` int(11) unsigned NOT NULL default '0', `action` int(11) unsigned NOT NULL default '0', PRIMARY KEY (`id`) );* We came up with this: *SELECT b.`action`, count(b.`action`) FROM `list_problem_ma` AS a RIGHT JOIN `list_problem_ma` AS b ON b.`member`=a.`member` WHERE a.`action`=3 AND b.`action`!=3 GROUP BY b.`action`* ** This will give you a result as: *action - count(b.`action`) 1 - 3 2 - 1* This shows that action 1 was performed 3 times and action 2 one time ! *Is this what you wanted ?* In the area of optimizations... a key in *`list_problem_ma`* table on the columns *`member` *AND* `action`* will do fine because MySQL will not scan NR_OF_ROWS(`list_problem_ma`) * NR_OF_ROWS(`list_problem_ma`) but only NR_OF_ROWS(`list_problem_ma`) * 1 You will still get: Using where; Using index; Using temporary; Using filesort ... but i believe that's no way arround that ! -- Gabriel PREDA Senior Web Developer
Re: auto_increment and the value 0
You can override MySQL behaviour of generating a new value if you insert a 0 into an auton_increment field. Quoting from the manual: NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. This mode can be useful if 0 has been stored in a table's AUTO_INCREMENTcolumn. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with *mysqldump* and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. *mysqldump* now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem. So you must alter the SQL-mode: Issue : mysqlSET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO; For altering the sessions sql_mode... now you will have to insert the dump with: mysqlSOURCE /path/to/dump.sql You can change it globally mysqlSET @@global.sql_mode = NO_AUTO_VALUE_ON_ZERO; But it's not recommend ... because until you change it back... MySQL will not generate auto increment values for your inserts if you use 0... only if you use NULL !!! The advantage is that you can import the dump from the command line. Another way is to add the statement: SET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO; into the dump itself... but... i think it's not that small... I believe this will do for you... but keep in mind that a value of 0 in an auto_increment column is not a good thing ... as everybody said before ! Good luck ! -- Gabriel PREDA Senior Web Developer
Re: Problems with UTF and MySQL
Yes you are right it works... but if the documentation is wrong I will be
wrong in the following also...
Majority of SET statements are documented without quotes:
AUTOCOMMIT = {0 | 1}
BIG_TABLES = {0 | 1}
FOREIGN_KEY_CHECKS = {0 | 1}
IDENTITY = value
INSERT_ID = value
LAST_INSERT_ID = value
SQL_AUTO_IS_NULL = {0 | 1}
SQL_BIG_SELECTS = {0 | 1}
SQL_BUFFER_RESULT = {0 | 1}
SQL_LOG_BIN = {0 | 1}
SQL_LOG_OFF = {0 | 1}
SQL_LOG_UPDATE = {0 | 1}
SQL_QUOTE_SHOW_CREATE = {0 | 1}
SQL_SAFE_UPDATES = {0 | 1}
SQL_SELECT_LIMIT = {value | DEFAULT}
SQL_WARNINGS = {0 | 1}
TIMESTAMP = {timestamp_value | DEFAULT}
UNIQUE_CHECKS = {0 | 1}
Even the:
CHARACTER SET {charset_name | DEFAULT}
I'm not talking here about setting a string user variable without quotes...
that would be absurd... but reaching to
SET NAMES this one is the only one documented *WITH quotes*:
NAMES {'charset_name' | DEFAULT}
And also its pointed that with a note:
Note that the syntax for SET NAMES differs from that for setting most
other options.
Also in the manual on chapter: *10.4. Connection Character Sets and
Collations*
there is written:
There are two statements that affect the connection character sets:
SET NAMES '*charset_name*'
SET CHARACTER SET *charset_name*
Again the SET NAMES with quotes anything else without !
So... dear writers of MySQL... or DOCs... what's the catch ?
And... of course I'll use it with quotes ... until further notice !
--
Gabriel PREDA
Senior Web Developer
On 3/28/06, Adam i Agnieszka Gąsiorowski FNORD [EMAIL PROTECTED] wrote:
One must issue immediately after connection:
SET NAMES 'utf8'
IMO, it is OK to say that without the quotes :-} {-:
Re: Complicated select query
About the first problem I think you need to give us more data ! As for the seccond... I haven't sen such loops yet... But you can go arround them and do something like: SELECT * FROM db WHERE (id BETWEEN 5 AND 50) AND id%5=0 -- Gabriel PREDA Senior Web Developer
Re: best way to handle two timestamp times
You can make the first timestamp without autoupdating and a default value
with no importance... and the second with
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
When you insert a new row.. .you'll use:
Asuming the table has columns: col1, ., colN, col_timestamp1,
col_timestamp2
INSERT INTO table_name (col1, ., colN, col_timestamp1) VALUES
('value_col1', ... 'value_colN', NOW())
This way the first TIMESTAMP column is registered with the
CURRENT_TIMESTAMP so is the second because the default value is also
CURRENT_TIMESTAMP.
But ... from now on for every change in the row only the second column will
change values !
--
Gabriel PREDA
Senior Web Developer
On 3/28/06, jonathan [EMAIL PROTECTED] wrote:
Basically, I want to have a content row that has two times, the time
that an insert was done and the time that it was last updated if any.
I keep getting an error when I try to create a table with two
timestamp values (#1293 - Incorrect table definition; there can be
only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON
UPDATE clause).
If I can have only one timestamp in the table, what is the ideal way
to do this?
thanks,
jon
Re: mysql query and version problem .... Help!
You don't need to drop a TEMPORARY table... it is dropped at connection-close ! You don't need to wory about different names for TEMPORARY tables... Manual says: A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. Good luck !
Re: Problems with UTF and MySQL
One must issue immediately after connection: SET NAMES 'utf8' Also look at: SHOW VARIABLES LIKE 'collation_%'; SHOW VARIABLES LIKE 'character_set_%'; Server must know what you are assking for... and they ALL have to talk the same language !!! -- Gabriel PREDA Senior Web Developer
Re: Very large from
Hmmm... Let me say some thoughts... First *fbsd_user* said that he has *100 input fields plus 40 different drop downs.* And everybody is arguing that they prefer *one single insert*. Is it just me... or are you thinking at a table with 140 columns ? Thinking at such a monster... all the above discussion is ok ! But who does a table with 140 columns... It's not good practice... it's no good at all... If we're not working with a monster like that... all discusion falls down... On the other hand if I have to insert all that info... in let's say... I don't know... 10 tables... what's the point of using *one single insert* how ca one use a one single insert to put data in 10 tables ? So... the design of the application follows in at least 50% of the cases the design of the DATABASE ! Give us a little more details about your database ! -- Gabriel PREDA Senior Web Developer
Re: Charset questions
Yes Ryan, Shawn is wright... this is dark art... and few are experimenting it ! I did some dark art because of my need to get into *latin2 (latin2_general_ci)*. Even though is adark art I do still there are people who know to answer you... but i believe you were to vague... Please be *more* specific on your problem. What is the server default charset and collation ? What is the database default charset and collation ? What charsets and collations are the tables and/ or even the columns you were talking about ? Finally I didn't found any ColdFusion secific connector... so I assume ODBC ! Have you searched for ODBC related issues ? Maybe is ODBC's fault. What server version do you use ? 4 or 5 ? Please specify minor also ! What about connection charset and/or collation ? Have you set them ? so... ?:) -- Gabriel PREDA Senior Web Developer
Webminars
Up until now I have missed all 3 webminars I registered... because of good various reasons... I was wondering weather the presentations from those webminars can appear online at: http://dev.mysql.com/tech-resources/presentations ??? That would be great... a lot of people can access them that way ! -- Gabriel PREDA Senior Web Developer
Re: How can I observe mysqld?
mytop measures the overall performance of the server... it's not daatabase bound ! For itself it uses the test database... but that has no meaning... it's not measuring that database performance... You can see queries that take to long aprearing in the list... It uses SHOW STATUS - to get and/or compute the statistics you see on the top part of the screen ! SHOW FULL PROCESSLIST - to get a list of running proceses ( SELECT, INSERT, UPDATE, CREATE ... ) So it cannot be database bound ! -- Gabriel PREDA Senior Web Developer
Re: Boolean searches on InnoDB tables?
*MySQL Manual - Chapter 12.7.4. Full-Text Restrictions* says: *Full-text searches are supported for MyISAM tables only. * You could try to do what i did... with some overhead... I also had InnoDB tables for an application and also was in a great need of Full-Text Searches. I made a mirror MyISAM table but with the full text index. The full text searches were performed on the MyISAM tables. The inserts were done on both tables. Now depending on the size of the tables you have to do a sync. of the tables or complete reconstruction of the MyISAM table. Despite the fact that inserts were done on the both tables I also did a sync. every Sunday ( I had the smallest traffic on Sundays) and a complete reconstruction of the table every 2 months... ( MyISAM table - is now 750 MB ) The system is working fine for about 16 months now ! -- Gabriel PREDA Senior Web Developer
Re: Problem with UNION
The UNION Syntax is: SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...] Lower in the manual it says: * A DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALLkeyword. * ** So the thing is DISTINCT is implicit ! You will need to add after UNION the keyword ALL ! Good luck ! -- Gabriel PREDA Senior Web Developer
Re: MySQL query gifted book !!!
If you want to find out the content of the CD you should buy the book ! I'm sure it's illegal to make a copy of the CD available online... and also to have a copy without having the book ! With the respect of the content let me tell you that it contains the book itself in PDF and some PDFs with exercises that cover the entire book ! THEY'RE GREAT EXERCISES !!! And I'm sure I'll get the exam ! I'm just waiting for the exam to get out of BETA ! You can take a peak at MySQL website www.mysql.com/training/certification/studyguides/sample-chapter50.pdf . As about how relevant is the book... consider that... I found a question on MySQL website about a VARCHAR(15) column that was created with UTF8 charset... and the question was What is the max nr. of bytes this column will use The first reaction is to say 16 (L+1) but the correct answer is 46 (3*L+1) because UTF uses at most 3 bytes per character... Maybe is not much... but this made me want the book... ALMOST OFF TOPIC: I myself am worried because I did not bought the book it was send to me as a gift (almost gift... let's say it was a favour... he sent me the book.. I did domething for him !)... The fact is that I don't have the bill... does this make me in a less legal position... will I still get 25% off at the exam ? Should I ask for some kind of a deposition from my friend in USA !??? -- Gabriel PREDA Senior Web Developer On 2/27/06, Anago Chima [EMAIL PROTECTED] wrote: Please does anybody knows the content of the CD that comes with MySQL 5 Certification Study Guide and how relevant it's to passing the exams? Can someone out there provide me with a link to his copy for download?
inquiry
Yes... count me in for this question also... I didn't figured that out... I just bought MySQL 5.0 Cerrt. Study Guide and I wanna know also... I also saw that the exams for the 5.0 branch are in BETA... when are we going to expect a final exam ? -- Gabriel PREDA Senior Web Developer On 2/22/06, Anago Chima [EMAIL PROTECTED] wrote: Q: What do the exams cost? A: Both exams are offered at the local equivalent of US$200 / EUR 170*. This question and answer was copied from certification FAQ. Please can somebody tell me 'both' means in the answer here. Does it mean that the price for both MySQL Developer Exam I II are US $200? ie US $200 cover the two exams
Re: describe table : improvement ?
U can use instead of *DESCRIBE a_table* ** Another syntax: *show [full] columns from `a_table`* Without FULL it will act like *DESCRIBE a_table *but with FULL option you will get the comments on the column... and also another beautiful column witch will show you the privileges you have for each column ! Good luck ! -- Gabriel PREDA Senior Web Developer ** On 2/17/06, Gilles MISSONNIER [EMAIL PROTECTED] wrote: when I do a describe a_table, it displays : | Field | Type | Null | Key | Default | Extra | I would like to have a Comment that would show the meaning of a field.
Re: Converting database and its tables to UTF-8
Hi Peter, That will be a lot of work ! *1.* First make a back-up... it's always a good ideea ! *2.* For every table in the database alter String Types into BINARY string types that means: - *(VAR)CHAR(M)* will become *(VAR)**CHAR(M) BINARY* or *(VAR)**BINARY(M)* - *TINYTEXT, TEXT, MEDIUMTEXT, *and* **LONGTEXT* will become respectively *TINYBLOB, BLOB, MEDIUMBLOB, *and* LONGBLOB* *3.* Alter the database isuing *ALTER DATABASE `database_name` DEFAULT CHARACTER SET utf8;* *4.* Alter each table issuing: *ALTER TABLE `table_name` DEFAULT CHARSET=utf8;* *5.* Alter back the column types. This should do it ! And because you issued the ALTER DATABASE from now on all tables in that database will be in utf8 ... and falling down any column will be utf8 if you don't specify explicitly anything else... Yes you can have a charset on the database in that database tables with different chartsets and even in a table you can have columns with other chartsets... Gabriel PREDA Senior Web Developer On 2/14/06, Peter Lauri [EMAIL PROTECTED] wrote: Hi, I have a database with around 40 tables that needs to be converted to UTF-8 to support multi languages. What is the best procedure to do this? And is it any way to change the default charset to UFT-8 so tables by default will become UFT-8? And can I have one table with different fields with different charset? Best regards, Peter Lauri
Re: 5.0.16. Bug in union?
Interesting... maybe this is because the fill is not actualy stored in the database... and being sorted/compared as a number MySQL removes the ZEROFILL ! You can go and do: select BINARY * from a union select BINARY * from a; -- Gabriel PREDA Senior Web Developer On 2/14/06, Juri Shimon [EMAIL PROTECTED] wrote: Hello mysql, Union on zerofilled fields eats 'zerofilling'. How to repeat: create table a (id integer zerofill); insert into a values(1),(2),(3); select * from a; ++ | id | ++ | 01 | | 02 | | 03 | ++ select * from a union select * from a; +--+ | id | +--+ |1 | |2 | |3 | +--+ Where are my leading zeroes? :( Is this a known bug? PS. On 4.1.* all works as expected. -- Best regards, Juri mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext searches
Hi Octavian,
Yes indeed... IT is a stop word... despite being the acronym for
Information Technology... *It* is a third-person neuter
pronounhttp://en.wikipedia.org/wiki/It_(pronoun)in the English
language.
You can make it IT a normal word by excluding it from the build-in stop
word list !
In the build-in stop word list there are around 540 words... you should get
the source code of the MySQL version you use and look in the *myisam* folder
for a file called *ft_static.c *as that file contains (among other C
code) all the words considered stop-words.
Also there would be an extra if you would build a file tailored for the
language of the texts you store in the databases... in romanian *it* has
no meaning except of being the acronym for Information Technology... in a
romanian stop word list this should never appear...
This would also be a plus to any hosting service that offers MySQL databases
equipped with language dependent stop word lists...
- If I'm offering hosting in Romania the stop-word-list should contain words
that have no semantic value in romanian...
- If I'm offering hosting in France... the same thing... the stop-word-list
should contain french words...
- ... so on !
Or maybe some kind of a mix... should do the trick !
So... going back:
1. build the file... make it a single word a line...
2. save it in a path accessible to MySQL... read permissions will do just
fine
3. modify in my.ini or my.cnf adding
ft_stopword_file=/var/lib/mysql/stop_file
4. rebuild all FULL-TEXT indexes in all databases... if not only new indexes
will take benefit from the new stop-words-file
Hope it helps... !!!
---
Gabriel PREDA
Senior Web Developer
PS: If interested in building a stop-word-list for RO... mail me !
On 2/10/06, Octavian Rasnita [EMAIL PROTECTED] wrote:
Hi,
I have tried:
select title from table where match(title, body) against('IT' in boolean
mode);
The result was 0 records.
I have checked the min word lenght which is allowed with:
mysql show variables like '%ft_min_word_len%';
+-+---+
Variable_name | Value |
+-+---+
ft_min_word_len | 2 |
So the word IT should be found, because if I search using ... like '% IT
%'... there are found some records.
Is IT a stop word? If yes, how can I make it be a normal word?
Thank you.
Teddy
Re: I need Query Help
UPDATE `table1` SET `gender` = IF('f'=`gender`, 'm', 'f');
If you have NULL columns you might want to make another sublevel in IF to
leave it NULL !
I believe this should do it... you might also take into consideraion
removing the possibility of a NULL in the `gender` column... because it
allows the `gender` not to be specified... to be null !
--
Gabriel PREDA
Senior Web Developer
On 2/10/06, Veerabhadrarao Narra [EMAIL PROTECTED]
wrote:
I have a table named table1 structure is
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| name | varchar(50) | NO | PRI | | |
| gender | enum('f','m') | YES | | NULL| |
++---+--+-+-+---+
And Values like
+--++
| name | gender |
+--++
| 1| m |
| 2| m |
| 3| m |
| 4| m |
| 5| m |
| 6| m |
| 7| m |
| 8| m |
| 9| m |
| 91 | f |
| 92 | f |
| 93 | f |
| 94 | f |
| 95 | f |
| 96 | f |
| 97 | f |
| 98 | f |
| 99 | f |
+--++
Now i want to change the values in gender column
from 'm' to 'f' as well as 'f' to 'm' in single UPDATE statement. How can
we write this. And i have check constraint it accept only 'f' or 'm'.
(Means name 1 to 9 gender have to change 'f' and 91 to 99 'm')
Re: Migration
Not the whole... maybe it's humongous... some lines above line 20 and some beneath... -- Gabriel PREDA Senior Web Developer On 2/10/06, sheeri kritzer [EMAIL PROTECTED] wrote: James, You're going to need to show us the contents of olddbname.sql. -Sheeri On 2/10/06, James Dey [EMAIL PROTECTED] wrote: I am migrating a database from mySQL 4.0 to 4.1.16 and have the error ERROR 1064 at line 21: 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 'ALTER TABLE `jos_banner` */' at line 20
Innodb Settings - repost
** I am reposting. ** I am looking for some general rule to determine the innodb_buffer_pool_size and innodb_log_file_size based on number of innodb tables, transactions, etc. Setting these values based on how much of the server resources I am allotted is not adequate. Thanks ORIGINAL POST Hello I am looking to determine the best Innodb Settings for our MySQL Instances. Specifically, the settings for innodb_buffer_pool_size and innodb_log_file_size. I have read the manual and understand how they work. I am looking for additional information that would help me determine their values instance by instance. In other words, if I only have 2 Innodb tables with low data and xaction rates, then I should have them set to x. If there is some additional determining factors beside the number of tables, rows and expected transaction rate, I would like to know them as well. Thanks in advance! Gabe /ORIGINAL POST -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb Settings
Hello I am looking to determine the best Innodb Settings for our MySQL Instances. Specifically, the settings for innodb_buffer_pool_size and innodb_log_file_size. I have read the manual and understand how they work. I am looking for additional information that would help me determine their values instance by instance. In other words, if I only have 2 Innodb tables with low data and xaction rates, then I should have them set to x. If there is some additional determining factors beside the number of tables, rows and expected transaction rate, I would like to know them as well. Thanks in advance! Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
alter only an enum label
If i have a table with about 800M records. and one of the fields is a enum(a, b, c) and i want to change it to enum(a,b,x) will it fall into some optimization and be instant? and what if previously i've never used the c value? isn't there any optimization for that? ...leaving blank labels on a enum? or another command to add new labels to a enum? thanks, Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter only an enum label
hum... clever. i liked that solution.
but do have experience on how long it will take with milions of records?
all records havin only a int(11) as unique key and the enum field..
suposing now i have enum(a, b) only, and did a ALTER TABLE
tablename CHANGE columnname columnname ENUM('a','b','c');
thanks,
Gabriel
2005/6/16, Gordon Bruce [EMAIL PROTECTED]:
If you have c values in the table currently you can just do an
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','c','x')
DEFAULT a NOT NULL
then
UPDATE tablename SET columname = 'x' WHERE columname = 'c'
Then
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','x')
DEFAULT a NOT NULL
-Original Message-
From: Gabriel B. [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 16, 2005 12:54 PM
To: LISTA mysql
Subject: alter only an enum label
If i have a table with about 800M records. and one of the fields is a
enum(a, b, c) and i want to change it to enum(a,b,x) will
it fall into some optimization and be instant?
and what if previously i've never used the c value? isn't there any
optimization for that? ...leaving blank labels on a enum? or another
command to add new labels to a enum?
thanks,
Gabriel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Test - please ignore
Just a test - thanks There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No Longer Receiving Emails
Hi I have not received anything from this list for a couple of days. I just tried to re-register, and that did not help. If anyone reads this, please reply to ME so I can determine if the problem is with my account or with the list. Thanks Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: No Longer Receiving Emails
Thanks - now I am getting emails from the list.. Gabe -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Friday, May 27, 2005 2:39 PM To: Tucker, Gabriel Subject: Re: No Longer Receiving Emails You are sending messages without error, it would appear. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Underline or minus sign ?
No one ? Please help me with this. Gabriel - Original Message - From: Gabriel PREDA [EMAIL PROTECTED] Sent: Wednesday, May 04, 2005 4:36 PM Subject: Underline or minus sign ? I'm going to start the InnoDB engine... and I want to know if the syntax for the CNF file unified at last ? Can I use: innodb-file-per-table instead of innodb_file_per_table ? [This is just an example] Can I use only minus sign in the whole CNF file instead of underline ? Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
